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.

Copy tables from PDF for further processing

You ever tried to extract data part of a table from PDF to further process it in MS Excel or any other Spreadsheet application? That can be a painful job.

You might use one of the many PDF to Excel conversion tools - but most of them cannot be used without submitting your PDF to an online service or buying a commercial license. In addition you have to evaluate the quality of the results especially for data part of tables.

Few days ago I stumbled upon the tool Tabula. Still marked as experimental but the results are still pretty useful at least for data oriented tables.

Example

 

The PDF "The Mobile Economy 2013" contains a table on page 56 which you want to process in Excel:

  

You have to follow the following steps to extract as csv:

• Download the file to your local disk
• install and start the tool follow the instructions on the homepage
• upload the PDF and select "Submit"
• navigate to the table and select the table:

• choose "Repeat this selection" if you want to select the following tables as well using the same coordinates.
• choose "Download all data" and you get:

• choose "Download data" to get a csv file with the extracted tables. This file can be open with MS Excel or any other application which can read the csv format for further processing.

The results are very useful and it works for those kind of data as well:

Won't work with data provided as graphic as part of the PDF this is an topic for another story.

Not Open Source but Free CCMS (2)

The already mentioned XML based CCMS "Calenco XML CMS" still available (see also my post "Open Source CCMS").

There is one more system which lately offers an already existing DITA based CCMS without any license cost: "SiberSafe DITA CMS". Read the EULA carefully but in case you need something to play with....

Both are no more open source. Their goal is not to get an open and shared development. They simple heading for lowering the barrier for customer entry.

What you see is that both cases are the company driving the implementation want to get in tough with you and both companies offers additional features with dedicated license costs.

I personal expect more product in this domain following the same approach. Why?

The specific domain of "technical documentation" is pretty small and there are many different and small companies out there which providing specific products to support this domain.

Even in huge installations the amount of licenses required to support the users dealing with technical information isn't very huge - this means the opportunity to sell a huge amount of licenses is limited. In addition most of the available tools are similar to each other - with individual advantages but with no structural differences.
 This means this business model does not really scale and the amount required to sale the license is high.

On the other hand having a tool does not improves your information process and therefore does not add any business value to your organization. At the best case it supports your process with automated tasks. But first of all you need a optimized methods and processes (information process) at all before any tool can assist you as best as possible.

This means - the future is not to create and develop products looks like the today's CCMS system available on the market. The future is to create either information process driven productions where technical information is just one use-case OR focus on integration services to get the value out of existing information.

What are the limitations of todays CCMS system. And how more future oriented designs will look like? More to come in future blog posts....

Open Source: data management and transformation library

Sumbled upon the following post http://flowingdata.com/2012/04/23/miso-an-open-source-toolkit-for-data-visualisation/

Relational data (or data can be stored in a table or matrix) is "old style" but still an an common use-case in todays web applications.

A new JavaScript library called "Miso Project" starts to implement components that simplifies the management and transformation of this kind of data (and will be extended with visualization use-case). This means that you are easy manage relational data on client side which can be very handy in certain use-cases. So its like a client side database with corresponding query syntax.

One of the most common patterns we've found while building JavaScript-based interactive content is the need to handle a variety of data sources such as JSON files, CSVs, remote APIs and Google Spreadsheets. Dataset simplifies this part of the process by providing a set of powerful tools to import those sources and work with the data. Once data is in a Dataset, it becomes simple to select, group, and calculate properties of, the data. Additionally, Dataset makes it easy to work with real-time and changing data, which pose one of the more complex challenges to data visualization work.

In case you have to develop a e.g. simple, standalone HTML application without a permanent server backend this library will help you without adding too much complexity in your (implementation) infrastructure.

QR Codes in your documents

QR codes are one method to ease the information exchange between classical medias and mobile devices (common usage direct link to the corresponding web page in paper based catalogs, manuals, ....).

But how to create those QR codes without too much complexity?

Google Chart API provides:

• chart wizard to create QR codes and corresponding styling
• infographics API to create static images based on posted chart definition (URLs)

viola. You now have a easy to use backend for creation of QR codes using e.g XSL-T.

The following code is taken from "QR Codes in DTIA Output" which shows how to create QR codes for PDF output of the DITA-OT using XSL-FO:

<!-- Insert QR code -->
<xsl:template match="*[contains(@class,' topic/xref ')]
                      [contains(@outputclass, 'qrcode')]">
<fo:external-graphic>
<xsl:attribute name="src"><xsl:value-of select="concat('https://chart.
googleapis.com/chart?cht=qr&amp;chs=100x100&amp;chl=', .)"/>
</xsl:attribute>
</fo:external-graphic>
</xsl:template>

 see: http://ditanauts.org/2012/03/14/qr-codes-in-dita-ouput/

Sample (code: <img src="http://chart.apis.google.com/chart?chs=200x100&cht=qr&chl=http%3A%2F%2Ftrent-intovalue.blogspot.de%2F2006%2F05%2Ftrent-definition.html" width="200" height="100" alt="" />):

how to preserve the value of big data over time....

Today the buzzword "Big data" getting more and more popular. Nice label for a common statement "the amount of data and the valuable usage getting important".

There are millions of information and products out there which promise to help you storing and analyzing those data. But one of the major issues with data is not current usage it is the maintenance of the information over time.

The "Web of Data" is one common example. It is the biggest data store we currently faced with. Pretty simple to access and analyze. So far so good. But there is one maintenance of this data (required?). Collect 100 links to resources on the web today. than 24 month later try access them...how many of those links still work, and if they work the resulting information still using the same semantic as it was once you build up the link?

The "Web of Data" currently decided not to maintain data just provide them now, enrich them and just replace them with different semantic...The Web Wayback machine (http://archive.org/web/web.php) is an approach to help individual users to keep their individual value of data for some scenarios.

Now think about your cooperate information you collect right now. The speed and adaption rate of this data will increase and new demands to enrich the data  will appear. Do you ever thought about how you ensure that all that data can be adapt to new needs? Based on my personal experience at least more than 60 % of the over all project costs are related to data migration in IT project dealing with information in a certain domain of the organization. Those costs are related to adapting data to the new tools which maintains the data, converting data between different data models and formats and ensure the quality of the data and their usage in existing business processes.

What does this mean for each IT project dealing with data?

• Initial load is important
  You always have to define how to get the data you need for the initial start (and not only during the regular operation of your business process) and how to verify that this data is valid for your future need.
• Expandability of your data might be important
  You can use static data models and tools (e.g. classical relational data models) compared to more flexible approaches like typed graphs of data where content using different models can simpler coexist.
• Adaptability of your IT systems might be important
  What happens to your existing data once the model will be extended, changed. Do not only take care of the data itself also take into account the relation to the data. Today you only access a specific level of your data few years later some use-case requires you to access the individual step or introduce an additional level not yet exists.
• Ensure the maintenance of your data.
  Do not "use" any data which you do not have any value in your primary business process. The usage of information requires the correctness of data. Your data will never be correct if the process creating this data does not have any value out of the data itself. This means that the data will be simple partially incorrect, incomplete.

It is and will be the most expensive IT task in your organization "how to preserve the value of big data over time...."

Data-Driven Documents

Data-Driven Documents. What's that?

D3.js is a small, free JavaScript library for manipulating documents based on data.

You have a data source requires visualization and a chart library is not enough for that purpose. This little library helps you to develop the visualization using the DOM model and data driven transformation.

A good example of whats can be done is available here: http://www.visualizing.org/full-screen/16266

XQuery - rare used but ramp-up gets easier

A very nice overview of what XQuery is, what are the relation to other XML based standards and why it is still very rare used is summarized here http://grtjn.blogspot.com/2011/10/xquery-novelties-revisited.html.

Once you want to dive into XQuery a bit deeper, get a feeling if the approach is sufficient for your use case you should try out BaseX. A XML database, open source. The main advantage for tryouts which are not available on alternative solution is a lightweight but very useful UI fronted for the content stored in the database.

Adding XML documents, try out queries and see how / what they match and last but not least a noticeable view of how information in a XML store looks like (from a conceptional point of view) comes aligned with easy install and ramp-up costs.

Alternatives (commercial and open source) are collected here: http://trent-intovalue.blogspot.com/2010/08/xquery-design-patterns.html

Kevin Slavin: How algorithms shape our world

http://www.ted.com/talks/kevin_slavin_how_algorithms_shape_our_world.html

Writing code in most cases does not mean that you can ever control the usage and implication of the results.....

XQuery Design Patterns

Nice summary of XQuery Design Patterns (see http://patterns.28msec.com/). This side also contains a link to a online XQuery engine based on Zorba (see http://try.zorba-xquery.com/).

The real power of XQuery comes with a database hosting the data for dynamic retrieval and processing. Today there are several databases out there supporting XQuery processing, even as open source.

Open Source

• eXist (http://exist.sourceforge.net/)
  XQuery sandbox here http://demo.exist-db.org/exist/sandbox/sandbox.xql
• sedna (http://modis.ispras.ru/sedna/)
  XQuery sandbox here http://wikixmldb.org/xq/
• BaseX (http://www.inf.uni-konstanz.de/dbis/basex/)
  XQuery sandbox here http://phobos101.inf.uni-konstanz.de/basex/demo
• brandnew TNTBase (http://tntbase.org/)
  XQuery sandbox here http://alpha.tntbase.mathweb.org:8080/tntbase/ores/Try.html
  uses Subversion and the Berkley DB XML. This implementation adds more XML processing infrastructure which intends to be a XML Pipelining infrastructure including a XML database.
• Oracle Berkeley DB XML (http://www.oracle.com/us/products/database/berkeley-db/index-066571.html)
  There is a commercial version available as well.

Commercial

• The big 3 relational db vendors (MS, Oracle, IBM) provide XQuery support. This approach combines relational storage of xml fragments and is therefore only suitable in certain use-cases
• MarkLogic (http://www.marklogic.com/)
  the most powerful XML DB currently available in my point of view.
  no online sandbox available, but a free developer edition can be downloaded from http://developer.marklogic.com/products
• ....

open usage of sequence of data points

lot of important data out there are simple "Time series" data, means a sequence of one or more data points change over time.

a service to share and use such data is Timetric. currently the amount of user and useful time series are small but in general such pretty platform can deploy common time series from many different domains.

problem

who takes care that the shared data is correct and therefore is valuable to use? all services based on public contribution and usage are faced with the same issue. do you trust the data you see? do you trust wikipedia? in general you should not. you have to double check at least 2 different sources before you use the provided data.

in addition once you double checked your data you have to make sure that the quality of data is guaranteed over time. that is much more difficult.

solution?

if the data is mission critical you should not use data before validating them. in case of non static data you have to validate the data each time they change. this means that you either need more than one data source as service which are not based on same data source or you have to look and buy commercial services takes care of the provided data or you request the service from the organization owns / collecting the data. each of those solution requires special handling for the particular domain.

summary

availability of public data service are promising but i currently do not see a available model to trust in. therefore usage is pretty limited only for some kind of "outline view"

data processing workflows

if you are faced with data processing workflow which requires to process / transform a huge amount of data in a limited amount of time this can end up in pretty complex implementations. if you have enough hardware to do the job you need a infrastructure makes use of the hardware.

using hardware for a limited time today isn't a big issue. the cloud infrastructure out there (e.g Amazon ec2) is perfect if you have to process a huge amount of data in limited amount of time for a limited duration. you are able to scale the usage of the required hardware for the time they are required and just pay for the required duration.

now you also need a ready to use software infrastructure to implement the processing workflow. MapReduce is a software infrastructure for such kind of problems.

Apache hadoop implement this MapReduce but it lacks of easy of use means is a pretty low level infrastructure and of course lacks of higher level workflows which is not defined by MapReduce.

Cascading closes the gap. based on "stream processing" the MapReduce pattern are applied and used. it is not too complicated within one day i was able to create a simple applicaton which convert 20 TB of svg data to jpg and doing some transformation in between using batik and 20 concurrent hardware nodes.

for some use cases the power of cloud is easy to tell....