Big news ...

It’s very easy to make your own data profiling logic – but wrong!

In the world of data quality I often see that people tend to think that a profiling application is that important because the user can easily analyze his data using SQL and similar methods already. It can actually make it quite difficult to convince users to try out a tool built for the job.

Let me tell you about a situation that I have been in quite a few times: If I am to do a quick demonstration of my favourite data profiling application then I will begin with something like a Value distribution chart. The responding watching the demo would then reply:

"... But I could easily do this with just a simple GROUP BY query."

And my answer would be YES, you can.

So then I will proceed to demonstrate various other metrics such as null counts, empty string counts, length maximum/minimum etc. An SQL-hefty respondent might reply:

"... Sure, but I can also use AVG, WHERE x IS NULL, length functions etc. in SQL".

And my answer would be YES, you can.

If at this point I dive into the discussion on these terms, then I would demonstrate hard-to-query measures such as diacritic count, word count, non-letter chars and case distribution. But ultimately I will also myself have misunderstood the point of using a data profiling application because the point isn’t (so much) that it contains measures that are not possible to express as queries.

The point is that the measures and their impact to your data's profile are not always known to you in advance. If you know just what you’re looking for, then you’ll find it and nothing more.

So while it’s quite easy (or at least possible) to design your own profiling plan using SQL and various other querying extensions, it’s not going to help you much in terms of profiling, because profiling should be an exploratory, analytical and interactive discipline.

To scare you off, I think I’ll try creating a few SQL queries that could compensate for most of the functionality in DataCleaner. I imagine they’ll be quite amusing, so hold on for an update...

YADC2S

Yet Another DataCleaner 2.0 Screenshot :) This is basically just an addition to my previous post about richer reporting and charts in DataCleaner 2.0.

What you see is our new Date Gap Analyzer which can be used to plot a timeline based on FROM and TO dates in a dataset. The analyzer will display gaps in the timeline and overlaps (periods where more than one record exist). This should be pretty useful for finding errors in datasets that contain continuous activities.

The chart is zoomable and scrollable so it is able of displaying quite a lot of data without harming the visual appearance.

Match! Boardgame about the heuristics in data matching(?)

This morning I was enjoying a bit of Good Clean Family Christmas TV You Can Trust and one of the subjects covered was a new Danish board game that you could spend your Christmas vacation playing. It's called Match! and here I will try to outline the rules as I understand them:

• Each player has 3 game cards with a picture of something on their hand.
• A picture is shown to all players and they now has to match that picture with one of the pictures on their hand.
• In the example shown on the TV there was a picture of some sausages on a grill. The example matches of the three players where:
  • Danish politician Pia Kjærsgaard - both she and the sausages represent something very danish and something they'd like to put on a grill!
  • A used roll of toilet paper - related to a different kind of "sausage"!
  • A crowd at a musical festival - a place where you'd love to eat a grilled sausage.

The matches themselves where not the best I've seen but they do point out an important feature of a good matching engine: Using simple similarity checks is not enough. You need to understand not only the spelling, phonetics etc. of the things you are trying to match, but also the semantics. Of course a good example of this in Denmark is our country's biggest company: "Maersk", which can rather easily be matched with "Mærsk" but it's more difficult to get the synonym "A.P. Møller" into the matching rules except if you hardcode it somehow. And if matching goes beyond just names other associative matching rules might apply.

Well... Can't wait to play Match! It sounds like a fun game and it will definately be in the back of my head to try and record some of the interesting heuristics applied there.

Richer reporting and charts in DataCleaner 2

One of the important new features of DataCleaner 2 will be a much richer reporting module than the old one. In DataCleaner 2 the result of an analysis is not limited to the crosstabular view that a lot of you know from DataCleaner 1.x. In this blog post I will provide you with a preview of some of the exciting reports that have been added lately.

Charts in Value distribution
The Value distribution component is well-known to most DataCleaner users. It provides a simple but crucial look into the distribution of values for a column. In DataCleaner 2.0 we are enhancing the experience of working with the Value Distribution by applying visually pleasant charts as well as grouping of values with similar frequencies. Take a look at this example result on a country-column:

Now you might think: "Looks nice, but that's going to be messy for columns with very oddly distributed values". And you're right. Except that we have applied a rather intelligent grouping mechanism that will make sure we never above a certain amount of slices in a chart. To accomplish this we may need to group together some values by their frequencies which will communicate another important fact: When repeated values occur, how many times do they occur. Take a look at this next example of the value distribution of a customer number column:

As you can see, even though there's a very high amount of customer numbers we are grouping them together by frequency. This is a principle that is actually already known from the <unique> group, except that we now also apply it to further frequencies: <group=2>, <group=3> etc.

Notice also the green arrows in the table to the right. Using this button (or by clicking the slices of the pie-chart) you will be able to drill to detail to view the actual values that make up a given group.

Navigation tree in Phonetic similarity finder

Another application of richer reporting in DataCleaner is for the new Phonetic similarity finder. In short this analyzer will apply a mix of well-known algorithms for similarity checking such as Soundex, Metaphone and Levenshtein distance to produce a set of groups of similar sounding values. What you get is a tree of groups from where you can see the rows that are similar or maybe even identical:

The big news here is of course that this kind of result would be practically impossible to display in a crosstabular result of DataCleaner 1.x - which is also why DataCleaner 1.x doesn't have this feature. I hope that my message with this is clear: DataCleaner 2 will not only be a substantial improvement to the existing data profiling tool, but it will also open up a lot of new doors for more interactive (and interesting) analyses.

Pluggability

The last thing that I would like to point out in this blog entry is the fact that the rendering mechanism in DataCleaner 2.0 is pluggable. This means that you can very easily, using modular Java code, enhance the existing result renderers or implement your own, and simply plug it into the application. Just remember to contribute it back to the community :)

Java 6 compilation problems

If you're trying to build DataCleaner 2 and get problems like this ...

...src\main\java\org\eobjects\datacleaner\widgets\properties\ChangeRequirementButton.java:[94,6] inconvertible types
found : org.eobjects.analyzer.job.builder.AbstractBeanWithInputColumnsBuilder<capture#10 of ?,capture#279 of ?,capture#140 of ?>
required: org.eobjects.analyzer.job.builder.FilterJobBuilder<?,?>

...src\main\java\org\eobjects\datacleaner\widgets\properties\MultipleInputColumnsPropertyWidget.java:[116,61] inconvertible types
found : org.eobjects.analyzer.job.builder.AbstractBeanJobBuilder<capture#0 of ?,capture#51 of ?,capture#202 of ?>
required: org.eobjects.analyzer.job.builder.TransformerJobBuilder<?>

...src\main\java\org\eobjects\datacleaner\widgets\properties\SingleInputColumnPropertyWidget.java:[73,61] inconvertible types
found : org.eobjects.analyzer.job.builder.AbstractBeanJobBuilder<capture#29 of ?,capture#564 of ?,capture#109 of ?>
required: org.eobjects.analyzer.job.builder.TransformerJobBuilder<?>

... Then I just wanted to let you know that it's actually a compiler error, not a source code error. The Java 6 compiler (pre update 18 I believe) seems to not be able to cope with subtypes of generic interfaces which contain different type parameters than the interface. Of course this should be (and is in newer compiler versions) possible because a subtype may implement an interface with certain type parameters and define a new set of type parameters. So ... Update to the newest JDK please :)

Preview of the UI in DataCleaner 2

Lately I've been blogging a lot about AnalyzerBeans, which is the name of the new engine of DataCleaner from version 2.0 and onwards. As AnalyzerBeans is nearing a state where it is usable and maturing I have now also taken the first steps of development on the roadmap for DataCleaner 2. As a techie I would like to attribute as much emphasis on the technical capabilities of AnalyzerBeans as possible but honestly it doesn't do much good without a good user interface also. So just as AnalyzerBeans was/is an attempt to rewrite the functional/logical part of DataCleaner, the new UI will be an attempt to deliver an user experience that feels new, exciting, more responsive and interactive. The "sketches" for the new UI is being drawn these days - I'll take you through a few examples.

In the two screenshots below you can see the source data selection and a transformation of this source data. The source selection is pretty similar to the existing DataCleaner UI but notice the new transformation-oriented features. In the example below I want to use a "Name standardizer" transformation which will turn my "real_name" column into four (virtual) columns: First name, Last name, Middle name and Titulation. Similarly I can convert data types, concatenate, tokenize, parse etc.

Another thing that is much needed in the existing DataCleaner UI is more elaborate configuration options for the various profiles. In the screenshot below you'll see the new and improved version of the Pattern finder which includes a new set of configuration options. Notice that both my physical columns (real_name) and my virtual columns (as mentioned before) are available for the Pattern finder.

There are a lot of other exciting things going into the new DataCleaner version but I will safe some news for later :) For now, I can only invite everyone to try it out. All you have to do is:

> mkdir datacleaner_dev
> cd datacleaner_dev
> svn co http://eobjects.org/svn/AnalyzerBeans/trunk AnalyzerBeans
> cd AnalyzerBeans
> mvn install
> cd ..
> svn co http://eobjects.org/svn/DataCleaner/trunk DataCleaner
> cd DataCleaner
> mvn install
> java -jar target/DataCleaner-2.0-SNAPSHOT.jar

Good luck and let us know what you think :-)

PS: Maybe I should not that even though the new version is usable there are still a lot of things NOT working. If you're wondering if something odd is a bug or a feature that has simply not yet been implemented yet - don't hesitate to ask.