The small scale approach: How to cleanup messy data, standardize enrich data

You ever faced to the following challanges:

• You  have a huge amount of master data needs to be cleaned up. Over time the same terms are used differently, buildup of the names using different styles, etc.
• Have to split data currently part of one column into different one, e.g. a product name contains dimension which should be processed in a different column in the future, etc.
• common faults in the data needs to be harmonized (multiple whitespaces, upper vs. lower case letters, etc.)
•  Transform well structured data into a table for relational usage of the data, e.g.
• The data needs to be enriched based on public available or internal services, e.g. you have the address of your customer available and want to add latitude, longitude to display amount of customer on a map.
• You have to merge two sets of data from two different systems and want to identify same entries even if the both lists does not share a common key.
• ....

For those tasks you have several ways to work with.

One interesting tool which is very useful in those kind of tasks is "OpenRefine" (formerly known as Google Refine).
You can easily download the latest version and install it on your local client (or server) and start it just by following the easy installation instruction provided on the homepage.

A good overview of the main functions is shown in : https://github.com/OpenRefine/OpenRefine/wiki/Screencasts

The tool is very easy to use. It works for huge amount of data and for the most tasks it is pretty fast  (especially compared to doing the same with spreadsheet software). It is more likely used for ad-hoc tasks compared to fully automated and repeated tasks in the enterprise - but it provides  handy functions to reapply and reuse already created rules.

For more sophisticated analyses goes out of the scope of one columns and rows of one table tools like RapidMiner are more sufficient. But those kind of tools require a bit more upfront invest to get the first problem solved.

Wiki: solves collaboration & information sharing?

i often hear statements that using a wiki platform will solve our company problem in collaboration and information sharing.
based on my personal experience most of the wiki project's seen in reality failing silent, means they start with more or less enthusiasm but end up in either

• content silos with outdated, bad findable information chunks
  or
• unused part of the companies intranet / IT infrastructure
  or
• derived by only a handful contributers and users
  

recently published article http://www.cmswatch.com/Feature/190-Wiki-Myths?source=RSS showing the reason and rational for that behavior.

by the way there are wiki projects out there (internet -> wikipedia, intranet) which are successful.

what makes them successful?

in my personal point of view, each successful "information process" requires at least

• definition of common information lifecycle
  - who has to create which kind of information?
  - which criteries must be fulfilled to define a information object as usable?
  - which kind of subject matter expert must a involved for which kind of information
  ....
  
• and common information taxonomie
  - what kind of information must be maintained
  - what kind of common classification do we use
  - best practices for structuring the information
  ....
• and people who create, maintain and use the information
  - training is required
  - advantages and usage of information must be part of common understanding => people must see personal benefit in using and maintaining the information
  ....
  

based on spirit and purpose of the wiki within an organisation those guidelines must be more or less detailed but in any extend they must be available and somehow trained / reviewed.

the most successfull wiki project Wikipedia provides the mentioned guidlines all in an open and collaborative way (http://en.wikipedia.org/wiki/Wikipedia:About#Contributing_to_Wikipedia)

one thing does not work is to setup a wiki platform and post a link to all potential users without any additional hard work.

always remember: providing information not more but not less than hard work. the more value a information must provide the more hard work is required to create them.