kasper's source

Now you can build your own DQ monitoring solution with DataCleaner

2012-01-23T21:42:00.000+01:00

In the cover of night we've released a new version of DataCleaner today (version 2.4.2). Officially it's a minor release because for the User Interface very few things have changed, only a few bugfixes and minor enhancements have been introduced. But one potentially major feature have been added in the inner workings of DataCleaner: The ability to persist the results of your DQ analysis jobs. Although this feature still has very limited User Interface support, it has full support in the command line interface, which I would argue is actually sufficient for the purposes of establishing a data quality monitoring solution. Later on I do expect there to be full (and backwards compatible) support in the UI as well.

So what is it, and how does it work?
Well basically it is simply two new parameters to the command line interface:

 -of (--output-file) FILE                          : File in which to save the result of the job
 -ot (--output-type) [TEXT | HTML | SERIALIZED]    : How to represent the result of the job

Here's an example of how to use it. Notice that I use the file extension .analysis.result.dat, which is the one thing that is currently implemented and recognized in the UI as a result file.


> DataCleaner-console.exe -job examples\employees.analysis.xml\
 -ot SERIALIZED\
 -of employees.analysis.result.dat

Now start up DataCleaner's UI, and select "File -> Open analysis job..." - you'll suddenly see that the produced file can be opened:

And when you open the file, the result will be displayed just like a job you've run inside the application:

Since files like this are generally easy to archive and to append eg. timestamps etc., it should be really easy to build a DIY data quality monitoring solution based scheduled jobs and this approach to execution. Or you can get in contact with Human Inference if you want something more sophisticated ;-)

Notice also that there's a HTML output type, which is also quite neat and easy to parse with an XML parser. The SERIALIZED format is more rich though, and includes information needed for more refined, programmatic access to the results. For instance, you might deserialize the whole file using the regular Java serialization API and access it, as an AnalysisResult instance. Thereby you could eg. create a timeline of a particular metric and track changes to the data that you are monitoring.

Push down query optimization in DataCleaner

2011-12-23T16:11:00.000+01:00

As a follow-up to my previous post about how we make DataCleaner super-fast by applying some nice multi-threading tricks, The DataCleaner engine explained, I would now like to touch upon another performance booster: Push down query optimization.

To my knowledge "push down query optimization" is a trick that only very few tools support, since it requires a flow model that was actually built for it. The idea is that by inspecting an execution flow the tool might be able to identify steps in the beginning or in the end of the flow that can be replaced by query modifications.

For example, if your data flow begins with a filtering action that removes all records of a given type or restricts the further processing to only be the first 1000 records or something like that. Most tools simply require you to write some SQL yourself, which is also doable, but as I've said before on this blog, I think writing SQL is a barrier to productivity, creativity and good data quality results. So in DataCleaner we do not offer this option, because we have something that is much, much nicer. That solution is push down query optimization!

Let me illustrate. I will be using the Sakila example database for MySQL:

Say you want to do a simple pattern finding of film titles in the Sakila database, you would select the title column and you would get a result like this:

In the DataCleaner logs we can see what queries are actually fired to the database. Open up the log file (in the logs folder) and inspect datacleaner.log. You will find a line like this:

Executing query: SELECT `nicer_but_slower_film_list`.`title` FROM sakila.`nicer_but_slower_film_list`

That's fine. You can inspect the results closer, but that's not what this topic is about, so I'll carry on... Now let's say you want to refine your job. Let's instead see how the pattern distribution is if we want to only look at a few categories of films. So I add a 'Equals' filter to only select horror, sports and action movies and apply it to my pattern finder:

If we run the job, and inspect the log file again, we see now this entry:

Executing query: SELECT `nicer_but_slower_film_list`.`title`, `nicer_but_slower_film_list`.`category` FROM sakila.`nicer_but_slower_film_list` WHERE (`nicer_but_slower_film_list`.`category` = 'Horror' OR `nicer_but_slower_film_list`.`category` = 'Action' OR `nicer_but_slower_film_list`.`category` = 'Sports')

What's surprising here is that the filter actually got query optimized. Not all filters have this ability, since some of them have richer functionality than can be expressed as a query modification. But some of them do, and typically these are the small functions that make a big difference.

Let's also apply a Max rows filter that limits the analysis for only 20 records and chain it so that it depends on the Equals filter:

If we now run the job, both filters will have been applied to the query:

Executing query: SELECT `nicer_but_slower_film_list`.`title`, `nicer_but_slower_film_list`.`category` FROM sakila.`nicer_but_slower_film_list` WHERE (`nicer_but_slower_film_list`.`category` = 'Horror' OR `nicer_but_slower_film_list`.`category` = 'Action' OR `nicer_but_slower_film_list`.`category` = 'Sports') LIMIT 20

That means that we do as much as we can to optimize the query, without ever having to ask the user to help us. So if you modify the logical job, the physical queries are automatically adapted! This is why push down query optimization is a superior optimization technique to raw SQL. Happy data cleaning!

Additional information for developers: If you're developing plugins to DataCleaner and want to make a query optimized filter, then simply make sure you implement the QueryOptimizedFilter interface! Happy coding!

The DataCleaner engine explained

2011-11-29T13:35:00.001+01:00

For this blog entry I have decided to record a short video instead of write till my fingers fall off :) So I present to you: My videoblog entry about DataCleaner's data quality processing engine, and how it compares to traditional ETL engines.

The DataCleaner engine was created from the ground-up to be optimized for Data Quality projects. It performs superiorly to any other engine that we've looked at, which I think is a pretty nice archievement. In the video I try to explain what makes it different!

The Camtasia Studio video content presented here requires JavaScript to be enabled and the latest version of the Adobe Flash Player. If you are using a browser with JavaScript disabled please enable it now. Otherwise, please update your version of the free Adobe Flash Player by downloading here.

Enjoy using DataCleaner :)

Standardize the date formats in your data

2011-10-30T10:33:00.000+01:00

One of the things that I see sometimes is that web forms cause unstandardized data in your database. For example, text fields in web forms do not have a native way to specify the type of the data. So what if you have a field that is supposed to be a date? For example the birthdate of your web users? A lot of web applications are not performing real validations of the format and content of the data entered into such fields. I think this typically occurs because it was not thought of as important at the time of designing the initial web page. But maybe it will become important at a point in time if eg. you want to analyze the age groups of your users! The trouble is that later on in the applications lifecycle, a state of unchangeability enters because you're stuck with a bunch of unstandardized data that you cannot conform to a new standardized data format. This is because you will have a lot of different date formats represented. For example:

2011-10-30
20111030
30th of October, 2011
30/10/11

And maybe some even more exotic ones...

In this blog entry I will show you how to solve that migration issue with the use of DataCleaner.

1. Date mask matching

The first thing we should do is to analyze which date patterns are present in the data. To do this you need to combine two components: The Date mask matcher and the Boolean analyzer. Here are the steps involved.

First set up you datastore in the welcome screen of DataCleaner.
Click the "Analyze!" button to begin composing your job.
In the tree to the left, select the columns of interest - in our example at least the birthdate column.
Click "Add transformer -> Matching and standardization -> Date mask matcher".

Your screen will now look something like this:

In the middle of the screen you see a list of date masks. Each of these produce a boolean output column (seen below). The idea of the Date mask matcher is that it creates the boolean columns so that you can even assert if a particular date is parseable by using several date masks. That's because a single date string like "080910" can be understood in many ways!

2. Analyzing matches

Moving on, we want to see how well our dates match against the date masks. Since all the matches are now stored in boolean columns, we can apply the Boolean analyzer. Here are the steps involved:

Click "Add analyzer -> Boolean analyzer".
Make sure all the transformed boolean columns are checked.
Click the "Run analysis" button.
Wait for the analysis to run.

Your screen will now contain an analysis result like this:

The result has two parts: The Column statistics and the Frequency of combinations.

In the column statistics you can see how much individual date masks have been matched. In our example we can see that 4 of our date masks (no. 2, 3, 5 and 6) are not matched at all, so we may consider removing them from the Date mask matcher.

In the frequency of combinations we get a view of the rows and which match combinations are frequent and less frequent. The most frequent combination is that our date mask no. 1 is the only valid mask. The second most frequent combination (Combination 1) is that none of the date masks apply. If you click the green arrow to the right of the combination you will see which records fall into that category. In our example that looks like this:

This gives us a good hint about which date masks we need to add to our date mask matcher.

The "1982.03.21" date is a simple case - we should simply create a date mask like this: YYYY.MM.dd

The "11th of march, 1982" date is a bit more complex. We need to allow the date mask to have a literal string part (the "th of" part) and it needs to recognize the month by name ("march"), not by number. Fortunately this is still possible, the date mask looks like this: dd'th of' MMMMM, YYYY

3. Converting to dates

While we could continue to refine the analysis, this is a blog, not a reference manual and I want to cut to the chase - the actual migration to standardized dates!

So let us look at how you can convert your date strings to actual date fields which you can then choose to format using a standardized format. To do this, click "Add transformer -> Conversion -> Convert to date". You will now see a configuration panel like this:

In here you also see a list of example date masks. Click the plus-button to add additional date masks to convert by. The converter will try from the top to convert if it can, so in case you have cases like "091011" then you have to make your choice here (I would recommend based on your analysis).

We add the few masks that are relevant for our example:

And we verify that there are no immediate unrecognized dates, by clicking the "Preview data" button:

If a date is not recognized and converted, then the output column will have a null instead of a date. Therefore a good practice would be to look for null values and eg. save them to an error handling file. To do this, here's what we do:

Go to the Filters tab.
Click "Add filter -> Not null".
Select only your converted column (in our example "birthdate (as date)").
Click "INVALID -> Write to CSV file".
Select the desired columns for the error handling file.
Optionally right click the "Write to CSV file" tab and select "Rename component" to give it a name like "write errors".
Go back to the Filters tab and click the VALID button to write the valid records to a CSV file or a spreadsheet.

After these steps, you should be able to inspect your job flow by clicking the Visualize button:

Now your date standardization job is ready to run again and again to enforce standardized dates!

Data Profiling SQLized. Uh oh...

2011-09-26T20:37:00.000+02:00

What does 'Scrooge' and 'Kasper' have in common? Not much according to my SQL data profiler.

Some months back (admittedly, more than a couple) I was explaining how I think people tend do "home made data profiling" too often because it apparently seems easy to do in SQL. I went on to promise that I would also try to play the devil's advocate and show a few examples of "copy paste queries" that you can use for such a tool. In this blog post I will try to do so. But let me first say:

Don't do this at home kids!

But let's start with the first query that I would add to my home baked profiling SQL script. We'll do what anyone who hasn't really understood what profiling is all about will tell you to do: Do a column analysis based on the metrics that are easily available through all SQL implementations:

SELECT MAX(column), MIN(column), COUNT(column), COUNT(*)
FROM table;

This is a good query, especially for number columns. Here I would typically look of the MIN value is below zero or not. Is the COUNT(column) equal to the COUNT(*)? If not, it means that there are nulls in the column. Why not just do two separate queries, that would be more readable? Yes, but it also makes my script larger and I will have more stuff to maintain. But let's try it, we can actually improve it also by adding a few metrics:

SELECT MAX(column) AS highest_value FROM table;
SELECT MIN(column) AS lowest_positive_value FROM table WHERE column > 0;
SELECT MIN(column) AS lowest_negative_value FROM table WHERE column < 0;
SELECT COUNT(*) AS num_values FROM table WHERE column IS NOT NULL;
SELECT COUNT(*) AS num_nulls FROM table WHERE column IS NULL;

Now let's continue with some string columns, because I think more often than not, this is where data profiling turns out to be really valuable. Something that I often see as an inconsistency in structured string data is case differences. Such inconsistencies makes reporting and analysis of the data cumbersome and error prone because grouping and filtering will ultimately be inprecise. So let's do a case analysis:

SELECT COUNT(*) AS num_lowercase FROM table WHERE LCASE(column) = column;
SELECT COUNT(*) AS num_uppercase FROM table WHERE UCASE(column) = column;
SELECT COUNT(*) AS num_mixed_case FROM table WHERE LCASE(column) <> column AND UCASE(column) <> column;

And then on to query the always popular "first letter is capitalized" type of strings. This one really depends on the database, because substring functions have not been standardized across major SQL implementations. I'll show a few:

INITCAP-based approach (eg. PostgreSQL and Oracle):

SELECT COUNT(*) AS num_first_letter_capitalized FROM table WHERE INITCAP(column) = column;

SUBSTRING-based approach (eg. Microsoft SQL Server):

SELECT COUNT(*) AS num_first_letter_capitalized FROM table
WHERE UCASE(SUBSTR(column FROM 0 FOR 1)) = SUBSTR(column FROM 0 FOR 1)
AND LCASE(SUBSTR(column FROM 1)) = SUBSTR(column FROM 1)

A bit cumbersome, but get's the job done. Being the devil's advocate, I'm still not convinced that I should throw out my home baked SQL just yet. So I'm ready for another challenge!

Let's have a look at pattern finding through SQL. Again this is perfectly possible. I've even heard many people telling me that we should rewrite DataCleaner's Pattern Finder to make it SQL optimized. Read on and judge for yourself :-)

To match tokens by pattern we apply the simplest possible configuration in DataCleaner's pattern finder: All letters are replaced by 'A' or 'a' and all numbers are replaced by '9'. This makes for a nice pattern based matcher, like this:

Mickey Mouse -> 'Aaaaaa Aaaaa'
Minnie Mouse -> 'Aaaaaa Aaaaa'
Joachim von And -> 'Aaaaaaa aaa Aaa'
kasper@eobjects.dk -> 'aaaaaa@aaaaaaa.aa'

(Random fact: 'Joachim von And' is the Danish name for Scrooge McDuck)
As you can see from the patterns, this is a good preliminary way to determine if string values have the same form and syntax - we immediately see that the email address is odd and that although all other values look like valid names, som have lowercase tokens (prefixes) inbetween.

In PostgreSQL for example, this would look like:

SELECT regexp_replace(regexp_replace(regexp_replace(column, '[a-z]','a','g'), '[A-Z]','A','g'), '[0-9]','9','g') as pattern, COUNT(*) as pattern_count from table GROUP BY pattern;

This actually works like a charm and returns:

pattern	pattern_count
Aaaaaa Aaaaa	2
Aaaaaaa aaa Aaa	1
aaaaaa@aaaaaaaa.aa	1

So why even use a profiling tool for finding patterns? All this seems to be possible through raw SQL?

I will now stop playing the devil's advocate... Cuz' seriously... This is nonsense! Having worked for some years on a pretty good data quality analysis tool, this approach absolutely disgusts me. Here's just a few random reasons why, off the top of my head:

We still haven't scratched the surface when it comes to supporting eg. non-ASCII characters in patterns.
Some tokens in patterns should be matched regardless of string length, some shouldn't. In our case we never matched strings with unequal lengths (eg. Scrooge and Mickey). This is a setting that you will want to play around with! (For examples, check out our Pattern Finder documentation)
Each metric in the previous analyses required their own query. This means that if you want to analyze a hundred metrics, you would need to query (at least) a hundred times.
A lot of metrics are simply not possible to express in SQL. Some examples: Diacritic character count, max/min amount of words, matches against reference data and more.
Often you will want to preprocess data before (or actually I would argue, as a part of) your profiling. This can be for example to extract information from composite values or to replace known inconsistencies with standardized values.
All the examples offer no drill-to-detail behaviour, so further analysis is more or less impossible. And drill-to-detail is not offered through SQL, so there is for example no way to express in our pattern finder SQL that we want to keep some samples of various pattern matches for later inspection.
All in all, using SQL for data profiling makes for a terribly unexplorative approach. It's a pain having to write and modify such an amount of SQL to get simple things done, so don't rely on it, because it will make you lazy and then you'll not investigate properly!
And of course, SQL only applies to databases that support SQL! If you're looking to profile data in other formats, then you're out of luck with this approach.

Get your data right... First Time Right!

2011-08-15T15:19:00.000+02:00

In my blog I mostly talk about data quality tools like DataCleaner that are diagnostic and treating, rather than preventive. Such tools have a lot of merit and strengths, but for a total view on data quality it is crucial that you also include tools that are preventive of poor data ever entering your system. In this blog post I want to talk a bit about a project that I have been involved with at Human Inference which is just that - our First Time Right JavaScript solution.

The idea is that we provide a subscription-based JavaScript API where you can easily decorate any HTML contact form with a lot of rich features for on-the-fly verification, validation, auto correction and helpful features for automatic filling of derived fields.

For example, the API allows you to enter (or copy/paste) a full name, including titulation, salutation, initials and more - and get these items parsed and placed into corresponding fields on a detailed contact form. It will even automatically detect what the gender of the contact is, and apply this in gender fields. We have similar data entry aids for address input, email input, phone numbers and contact duplicate checking.

Take a look at the video below, which demonstrate most of the features:

Now this is quite exciting functionality, but this is also a technical blog, so I'll talk a bit about the technology involved.

We built the project based on Google Web Toolkit (GWT). GWT enables us to build a very rich application, entirely in JavaScript, so that it can be embedded on any website - no matter if it's PHP based, ASP.NET based, Java based or whatever. Of course we do have a server-side piece that the JavaScript communicates with, but that is all hosted at Human Inferences cloud platform. So in other words: The deployment of our First Time Right principle is a breeze!

Since AJAX applications require locality of the server that it is communicating with, we've had to overcome quite some issues to allow the JavaScript to be external from the deployment sites. This is crucial as we want upgrades and improvements to be performed on our premises, not at individual customer sites. This way we can really leverage the cloud- and subscription-based approach to data quality. Our solution to the locality problem has been the JSONP approach, which is an alternative protocol for implementing AJAX behaviour. JSONP is a rather clever construct where instead of issuing actual HTTP requests, you insert new <script> elements into the HTML DOM at runtime! This means that the browser will perform a new request simply because the <script> element refers a new JavaScript source. It's not "pretty" to tackle errorhandling and the asynchronicity that this approach brings on, but we've done a lot of work to get it right, and it works like a charm! I hope to share some of our design patterns later, to demonstrate how it works.

Another challenge was of security. Obviously you will want to make sure that the JavaScript is only available for subscribers. And only for the websites that they've subscribed to (because otherwise the JavaScript can simply be copied to another website). Our way around this resembles how for example Google manages their subscriptions to Google Maps and other subscription services, where you need a site-specific API key. Very clever.

A few optional features may require some local add-on deployment. In particular, deduplication requires us to know the contact data to use as the source for detecting if a new contact is a duplicate. Here we have two options: On-premise installation of the deduplication engine or hooking up with our cloud-based deduplication engine, which can be configured to sync with your datastores.

All in all I am quite enthusiastic about the FTR solution and the technology behind the solution. I also think that our FTR API is an example of a lightweight approach to implementing Data Quality, which complements DataCleaner very well. Both tools are extremely useful for ensuring a high level of data quality, and both tools are very intuitive and flexible in the way you can deploy them.

Eye candy in Java 7: New javadoc style!

2011-08-04T13:46:00.008+02:00

By now most of you've probably heard that Java 7 is out and there's a lot of discussions about new features, the loop optimization bug and general adoption.

But one of the things in Java 7 which has escaped most people attention (I think) is the new javadoc style.

Check it out:

And see it live - we've just published an updated API documentation for MetaModel.

Unit test your data

2011-08-01T21:59:00.013+02:00

In modern software development unit testing is widely used as a way to check the quality of your code. For those of you who are not software developers, the idea in unit testing is that you define rules for your code that you check again and again, to verify that your code works, and keep on working.

Unit testing and data quality has quite a lot in common in my oppinion. Both code and data change over time, so there is a constant need to keep checking that you code/data has the desired characteristics. This was something that I was recently reminded of by a DataCleaner user on our forums.

I am happy to see that data stewards and the like are picking up this idea, as it has been maturing for quite some time in the software development industry. It also got me thinking: In software development we have a lot of related methods and practices around unit testing. Let me try to list a few, which are very important, and which we can perhaps also apply to data?

Code	Data
Compile-time checking (Ensuring correct syntax)	Database constraints
Unit testing (Checking a single unit of code)	Validating data profiling?
Continuous integration (Running all tests periodically)	Data Quality monitoring?
Bug tracking (Maintaining records of all code issues)	?
Static code analysis (a la FindBugs)	Explorative data profiling?
Refactoring (Changing code without breaking functionality)	ETL with applied DQ rules?

For explanation of the various data profiling and monitoring types, please refer to my previous post, Two types of data profiling.

Of course not all metaphors here map one-to-one, but in my oppinion it is a pretty good metaphor. For me, as a software product developer, I think it also points out some of the weak and strong points of current Data Quality tools. In software development the tool support for unit testing, continuous integration, bug tracking and more is incredible. In the data world I feel that many tools focus only on one or two of the above areas of quality control. Of course you can combine tools, but as I've argued before, switching tools also comes at a large price.

So what do I suggest? Well, fellow product developers, let's make better tools that integrate more disciplines of data quality! I know that this has been and still will be my aim for DataCleaner.

A colorful value distribution

2011-07-14T21:59:00.003+02:00

A few weeks ago I was dealing a bit of attention to the charts in DataCleaner. Of special interest are the value distribution charts which has caused some discussions...

Anyways, here's a proposal which includes nicer (IMO) coloring, a "distinct count measure", a dedicated "<blank>" keyword and a few other niceties.

Value distribution chart proposals

You can expect to see this live in DataCleaner 2.3 which is expected in august.

Proposal for writing data in MetaModel 2.0

2011-07-08T17:56:00.017+02:00

Hi everyone,

For a long time we've had a lot of people asking "can I use MetaModel to not only read varying data formats, but also to write data?". So far the answer has been "no, MetaModel is a read-only API". But lately we've been working at Human Inference on a proposal for an API to write to the same DataContexts as you read from, in MetaModel.

Here's a glimpse of the API, by example. Currently we have fluent API's for creating tables and inserting rows:

UpdateableDataContext dc = ...
Schema schema = dc.getDefaultSchema();
Table table = dc.createTable(schema, "my_table") 
   .withColumn("id").ofType(INTEGER)
   .withColumn("name").ofType("VARCHAR").ofSize(255)
   .execute();
dc.insertInto(table).value("id",1).value("name","john doe").execute();
dc.insertInto(table).value("id",2).value("name","jane doe").execute();

This API has so far been implemented succesfully for Excel spreadsheets, CSV files and JDBC databases - our 3 most used datastore types.

You can find the work-in-progress of the proposal in SVN at:
http://eobjects.org/svn/MetaModel/branches/2.0-writable-datacontext/

We would like to get your reactions on the API proposal. Does it suit your needs and do you like the approach? Will it be acceptible to launch 2.0 with just these "CREATE TABLE" and "INSERT" operations, or will other operations (such as DELETE, UPDATE, DROP, ALTER) be needed before it makes up a valid solution for you guys?

Best regards,
Kasper

Update 2011-07-11
A few people have provided feedback (thank you for that) and also some performance tests on our side revealed that we need to apply a more batch-friendly approach, which also has better encapsulation and isolation properties for multiple and large updates. So, we've instead applied a pattern similar to Spring's template or Akka's atomic STM pattern. The idea is that the user supplies an UpdateScript which will be executed in isolation, like this:

UpdateableDataContext dc = ...
final Schema schema = dc.getDefaultSchema();
dc.executeUpdate(new UpdateScript() {
  public void run(UpdateCallback callback) {
    Table table = callback.createTable(schema, "my_table") 
      .withColumn("id").ofType(INTEGER)
      .withColumn("name").ofType("VARCHAR").ofSize(255)
      .execute();
    callback.insertInto(table).value("id",1).value("name","john doe").execute();
    callback.insertInto(table).value("id",2).value("name","jane doe").execute();
  }
});

On first sight it might not look quite as elegant, but I think that in the big picture this pattern is actually a lot nicer. First of all because it gives you a very clear understanding of exactly where in your code you modify your data. It also makes it a lot easier to write eg. fallback-scripts in case something goes wrong with your update. For datastore types that support transactions (eg. JDBC databases) it also makes it possible for us to easily demarcate the transactional boundaries.

... Please keep posting feedback!

Developing DataCleaner extensions

2011-06-27T22:05:00.011+02:00

Today has been all about the release of DataCleaner 2.2. This is a significant release of our Data Quality Analysis product which I think is becoming more and more mature and capable.

One of the really neat things in DataCleaner 2.2 is it's extensibility. Lots of applications are extensible, but few are in my oppinion as easy to approach as DataCleaner. We expose a limited API which is extremely flexible though. This makes it easy for developers to explore the opportunities and the architecture.

Another great strengths of DataCleaner's extension architecture is the ExtensionSwap. With a click in the browser you can install an extension onto a running application. Personally I think it's a quite jaw-dropping effect when you see the seamlessnes of the integration here.

I've recorded this webcast demonstration for developers who want to get started, or just feel curious on how our developer API works.

Also, a few nice resources for you to investigate further:

Our reference documentation now contains a "developers guide" with lots of nice info on extension packaging and more.
The Develop page on the website contains links to various previous blog entries and instructions. These are still valid even though the 2.2 API has been elaborated.
And of course, check out the javadoc API documentation for DataCleaner.

Lastly I want to point out that not only is DataCleaner 2.2 extensible, it is also embeddable. You can now grab DataCleaner in the central Maven repo's and you can bootstrap the application in a really easy fashion:

BootstrapOptions bootstrapOptions = new DefaultBootstrapOptions(args);
Bootstrap bootstrap = new Bootstrap(bootstrapOptions);
bootstrap.run();

For more info, check out the chapter "Embedding DataCleaner" in the reference documentation.

SassyReader - Open Source reader of SAS data sets for Java

2011-06-20T20:27:00.001+02:00

I'm quite excited to announce the first release of a brand new eobjects.org project: SassyReader. SassyReader is in my oppinion in deed something sassy as it fills a gap that has long existed in open source applications that deals with data management (ETL tools, tools like DataCleaner and the like). SassyReader is a library for reading data in the sas7bdat format, aka. the format that the SAS statistical software use! It is written entirely in Java and reads the files from their binary format (eg. it's not a connector to the SAS system, but a reader of the raw data).

So why is this important? Well first of all because it is very difficult to create systems that interoperate with SAS. SAS does ship a JDBC driver but it's compliancy with JDBC is actually very limited. Even creating a connection will typically require use of SAS's proprietary classes, so you cannot go the standards JDBC way. There is also no JDBC metadata support and you need to set up a server-side SAS/SHARE option to even expose the connection. Furthermore this is an add-on product from SAS which costs additional money if you're just a base SAS user. So doing trivial things like connecting and querying a data set requires a lot of work and money. In my oppinion this is poor practice - a legacy way of trying to lock people in to using only a particular brand of software, simply because interoperability is a big pain.

All in all I see a great benefit in a project like SassyReader for those who simply want a way of reading the data that is stored in SAS files.

I cannot take a whole lot of credit for this project though. Most of the really challenging stuff was created by Matt Shotwell, aka. BioStatMatt, who founded the sas7bdat project which is written in R. My contribution was to port it to Java and fix a few issues on the way. Matt put together a lot of fractioned works that describe various findings about the sas7bdat format. In other words this is a completely reverse engineered library, based on analysis of actual sas7bdat files. During the last months we've had a good conversation going and actually fixing some of the remaining issues in parallel and bringing additions to each other's code.

Today we've released version 0.1 of SassyReader. It's not yet ready for mission critical use as there are still quirks in the format that we haven't figured out. Also there are different shapes and sizes within the format that vary apparently depending on (I'm a bit guessing here) the amount of columns and the operating system that the file was written with. The good thing is that we have a quite extensive test set and for at least the files that I had lying around that I wanted to work with the reader managed to read all but one (11 out of 12)!

Please visit the SassyReader website for more details, and let me know your feedback!

The value of data quality

2011-05-31T22:03:00.000+02:00

The other day I stumbled into an interesting blog post about software quality, and this brilliant quote (by Alan Weiss) kept flashing on my internal billboard over and over again...

Quality [...] is not the absence of something in management's eyes, that is, defects, but the presence of something in the consumer's eyes, that is, value.

What struck me was an explanation for one of the findings that I quite often get when people tell me that they are using DataCleaner or competing tools, and they find value in even some of the simplest functionalities in there - That even the simplest of features can provide a fortune of value. Coming from the world of tools and product development we tend to look at feature comparisons and technical capabilities a lot. And even customers also use such arguments for choosing a tool. And of course it makes a lot of sense because quality, as in the amount of value provided for the consumer, is very hard to measure and compare.

So how can a software product deliver high quality, in the sense of consumer value? I believe it is a mix of making the product fairly easy to use as well as solving concrete problems for the consumer. In DataCleaner we've done a lot of work to make the tool work as a generic Data Quality Analysis (DQA) tool for a wide variety of data types. But maybe we should also consider building more domain targeted packages where you can easily do a "data value assesment" (to twist the words of Alan Weiss a little) for particular domains, eg. customer data, product data, geographic data and more.

What do you think? Should data profiling and DQA stay generic, or should it target specific domains? Can it do both?

Speed up your JDBC queries with MetaModel

2011-05-13T19:12:00.002+02:00

Although the recent MetaModel version 1.7.1 was a minor release there is something about this release that excites me very much! That thing is our new FETCH_SIZE calculator. So what is that you might say? Well, here’s a little background.

In JDBC there is an option to set the so-called FETCH_SIZE of a query statement, like this:

Statement st = connection.createStatement();
st.setFetchSize(10000);
ResultSet rs = st.executeQuery(...);

What this does is that it tells the database how many rows should be fetched from the database when more rows are needed. In other words: A buffer size, measured in the amount of rows.

Cool – so this means you should set a rather high fetch size if you have plenty of memory? Say 20.000 records? Well, sometimes yes, but not quite at all times!

The guys over at Oracle has done an exemplary work on documenting the memory consumption of their latest JDBC driver. From this you can learn that if you put a too high fetch size, you might run out of memory! But on the other hand you would really like to optimize the buffering of your result sets! The trouble is that the row size in memory is hugely different if you select 1, 2, 10 or 300 columns in your query! Furthermore the size of a VARCHAR(4000) value in the buffer is about 8000 times larger than a BIT value! These findings lead to a very nice new feature in MetaModel: Dynamically setting an appropriate fetch size, depending on the query you are executing...

How does this work? Well basically MetaModel has all the needed meta-information for doing a strictly mathematical calculation. MetaModel knows the data types of all your columns, it knows their column sizes and most importantly – it knows which columns you are querying! So if we want to allow eg. up to 8 megs of memory to be used for the query buffer, then it’s very easy for us to figure out the needed FETCH_SIZE – in principle it’s just to divide the available memory with the size of a single row in the buffer.

So far I’ve tested it out together with some friends of DataCleaner that have both small tables and 200-300 column tables and they see a dramatic improvement in performance as well as it prevents a few OutOfMemory issues!

A final remark – I haven’t seen any other Java frameworks that do something like this. Even Hibernate only has the option to specify a constant FETCH_SIZE:

<property name="hibernate.jdbc.fetch_size">10000</property>

But as explained in this blog entry you will hopefully agree that such an approach is not optimal unless your application only executes a single query again and again!

For more information on MetaModel, please visit the website.

Two types of data profiling

2011-04-13T21:32:00.000+02:00

Recently I've been blogging about how I see that DataCleaner is what I would dub a 'Data Quality Analysis (DQA) tool' more than anything else. This leads me to an explanation of what I mean by DQA tool, profiling tool and more.

So...

Data profiling is in my worldview the activity of extracting (and possibly refining) a set of analysis metrics from your data. As such it is a quite boring and trivial tasks that you can even automate rather easily.

The interesting question is not what data profiling is, but why it is! I see two main reasons and they have quite different characteristics as to how you would use a profiling tool!

The (chronologically) first reason that you would apply data profiling is to perform an analysis. Not a technical analysis, but an analysis where you apply your human reasoning. You investigate the metrics to discover your data. If you're a good analyst you will also continuously refine your analysis, challenge it and change settings to see what happens. For this a profiling tool enables you to go below "the tip of the iceberg" (a common phrase about profiling) in your datastores.

The second reason is for monitoring your data quality. A profiling tool has the power to extract the metrics so you will often see that people use profiling tools to perform a set of data quality validation tasks. It is used as a way to retain a quality level. In this case of data profiling you execute the same analysis again and again - only the data changes (over time).

Do you see other applications of data profiling tools?

Coming up in DataCleaner: Single window UI

2011-03-19T19:57:00.000+01:00

Lately I've been working on an fundamental improvement to the UI of DataCleaner that I think is quite important, and I would really like to present it to gather some reactions and ideas for further development.

The idea was to change the UI metaphor with regards to windows. Currently you might say that the left hand window is the "environment" window - it contains management of datastores and reference data (dictionaries, synonyms, patterns) as well as the file menu which includes links to various global dialogs etc. The issue with this window is that it is an additional window that you always have to carry with you, even though you want to focus on a particular job. My aim was to find a way to remove the window, but retain all it's abilities, but make the job creation the main focus of the UI.

So here's what we've done so far:

Moved datastore selection (and management) to the workbench (analysis job) window.
Moved the file menu to the workbench window.
Created a separate dialog for management of reference data, accessible through the file menu.

Here's a screenshot of the first concept, datastore selection and management. This is what you'll see when the app starts up (the cursor is hovering the MySQL icon, which is why it has an "information bubble"):

Once a datastore has been selected and the "Analyze!" button is clicked, the schema tree will become visible and will correspond to the analysis job window as you already know it.

In the next screenshots you can see the contents of the new "Reference data" menu in the top of the screen:

... and the dialog that it links to:

So what do you think? I hope that it's a bit more intuitive of course. The metaphor was to make something similar to a typical office application, where you begin with an empty document which you can build, or you can choose to open a saved document if you want to resume previous work.

If you want to try it out, simply check out the trunk of AnalyzerBeans and DataCleaner and build it! ... Here's the command line summary:

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
cd target
java -jar DataCleaner.jar

Enjoy and please provide feedback :)

The complexity of cleansing a name

2011-03-03T18:45:00.001+01:00

Following up on Winfried van Holland's blog post on having to cleanse 180 million names I thought I'd share a cool screenshot from a funny moment at the Human Inference office today, when we where experimenting with good ways to visualize our execution flow in DataCleaner...

The diagram shows 1) the steps that we go through in our filtering process, 2) the dependencies between them, 3) and a circle layout that we tried out and dismissed ;-)

Anyways, a funny moment and quite a nice view of just how much execution power you can extract from a tool like DataCleaner.

Pre-processing in DataCleaner 2: Why?

2011-02-17T11:46:00.001+01:00

Last monday we released the new DataCleaner 2.0 and one of the major new features in it is the ability to transform and filter your data using the tool. Previously the common answer for someone asking about transformation in DataCleaner has been to tell them that "there are already good tools for this available elsewhere, so use them". So why did we choose to focus on data processing in DataCleaner 2.0? And what are the capabilities of DataCleaner in terms of transformations? Is DataCleaner over time going to evolve into a full-fledged ETL tool? I'll try to answer these questions.

All your data quality functions in one place

The obvious answer is that we want to provide more data quality functionality and that transformations is something that a lot of people need. While doing data profiling it is often needed to do data adjustments, eg. to tokenize values, extract certain information, filter which rows gets profiled etc. You can also do all this also by applying an ETL tool or maybe even by creating database VIEWs. The problem with such an approach is that it will eventually get in your way because you're trying to get 2-3 independent tools to work nicely together, instead of just having these functions available where you need them.

Transformations for the DQ domain

Possibly even more important is that the transformations that you want to employ in Data Quality analysis are typically quite different than those that come out-of-the-box in database scripts and ETL tools. Such tools are typically quite generic and will provide general purpose tokenizers etc., but will typically lack transformations pertaining to the DQ-domain, such as date mask matchers, dictionary lookups and synonym replacements, standardization of email adresses, names and URL's.

Non-persistent transformations

When you do pre-processing in separate tools, you also need to persist your transformed datasets. In Data Quality analysis this is just a waste of resources and provides poor performance. If you need to perform transformations, apply filtering etc. for the purpose of analysis, profiling and exploring your data it is much more feasible to just perform these transformations when needed in stead of storing them up front. This also allows for a much more free user experience where you can actually experiment with your data and you analysis in stead of having to overthink it.

Is DataCleaner's validator gone? No, it's just filtering with added output handling!

DataCleaner 1.x was known to have a strict separation between the activities "profile" and "validate". Since this separation is gone in 2.0, one might ask, "Is DataCleaner's validator gone? I can only see analysis jobs!". But the answer is no, we just consider validation as a type of analysis (thus, analysis is a broader term, comprising both profiling, validation and more). You can easily perform all the validation operations of DataCleaner 1.x, but the approach is a bit different because you basically apply filters in stead of "validators". There is even an example of this in the "Introduction to analyzing, cleansing and filtering data" webcast, available on DataCleaner's website.

DataCleaner 2 released, Human Inference acquires eobjects.org, I have a new job and dataqualitypro.com even publishes a nice article about it

2011-02-14T11:42:00.010+01:00

Earlier I've promised you some "Big news" today and here it is... Hmm, where to start, so much to say.

OK, let's start with the software, after all that's what I think most of my blog readers care most about:

DataCleaner 2.0 was released!

To me this is the biggest news for the DataCleaner community in a LONG time. DataCleaner 2.0 is a major release that I and my new employer (read more below) have put a lot of effort into. I just had a look at some source code statistics and actually the 2.0 release is larger (in terms of lines of code, source code commits, contributions etc.) than all previous DataCleaner releases together. I don't want to say a lot about the new functionality here, because it's all presented quite well at the DataCleaner website.

More information: Watch out, dirty data! DataCleaner 2.0 is in town!

MetaModel 1.5 was released!

My other lovechild, MetaModel, have also just been released in a version 1.5! MetaModel 1.5 is also a quite big improvement on the previous 1.2 version. The release contains a lot of exciting new features for doing querying and datastore exploration as well as a lot of maturity bugfixes.

More information: What's new in MetaModel 1.5?

And then let's move on to a major announcement that I definately think will affect the eobjects.org community positively:

Human Inference acquires eobjects.org

This might come as a surprise to quite a lot of you, so let me explain a bit. For some years DataCleaner and the other eobjects.org projects have been independent open source projects that I've invested a lot of time in. The projects have grown nicely in terms of users and the ideas have been manyfold. My ambitions for the projects have always been high, but they suffered from the fact that I was mostly working on them in my free time. One of the many fun things about doing these projects was that I've gotten to meet up with a lot of exciting people that thought my projects where interesting. At one time I met some people from the data quality vendor Human Inference, who thought DataCleaner was great and they wanted to know if they could in some way use it in collaboration with their commercial offerings. From my end of the table I was on the other hand thinking that their products offered some features that would be an excellent addition to DataCleaner's functionality. So what we did was a deal to try and raise the value for both parties. And with that in mind, here's the press release about it:

More information: Human Inference completes acquisition of DataCleaner and eobjects.org

I have a new job

I now work with Human Inference to actively grow the DataCleaner project, MetaModel as well as Human Inference's commercial products. We're building really exciting cloud-based data quality services that I think will complement the open source offering nicely. Of course it's not all going to be free, but I promise that even for the users who don't want to pay for the additional services, the acquisition and my new job will be beneficial anyway, because we're adding a lot of new resources to the projects that are improving on both the open source parts and the commercial plug-ins.

And in the end I just also want to mention that Data Quality Pro has a great article about a lot of these news, including an interview with me and Sabine Palinckx, the CEO of Human Inference.

More information: Open Source DataCleaner gets a major update, Human Inference enters the Open Source Data Quality Market

Big news ...

2011-02-04T12:42:00.002+01:00

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

2011-01-18T15:59:00.002+01:00

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

2010-12-29T19:25:00.002+01:00

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(?)

2010-12-27T09:51:00.003+01:00

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

2010-12-14T15:27:00.000+01:00

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

2010-11-16T11:12:00.000+01:00

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

2010-10-26T21:07:00.001+02:00

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.

Developing an analyzer using the AnalyzerBeans Java API

2010-09-26T11:24:00.007+02:00

Previously I've posted about developing a value transformer using the AnalyzerBeans Java API. Now it's time to see how to develop an analyzer, which is a component for consuming data and turning it into a result that is humanly readable and hopefully useful. The Javadocs for the Java API are located here. There are lots of different analyzers in AnalyzerBeans already, which could be interesting to have a look at when you decide that you want to develop your own:

For typical measures there are analyzers like the Number analyzer and String analyzer. These analyzers calculate standardized measures for these data types.
There's the Value distribution analyzer which is interesting because it uses a backing database (using the @Provided annotation) for counting unique values if the values succeeds the amount of free memory.
The Date gap analyzer is also a good example because it has named input columns, used for building a timeline of from- and to-dates.
The Pattern finder analyzer which you can read a lot more about in one of my previous blog posts.

So let's begin with a simple example. Say you want to build a very simple analyzer that consumes date or time based values and determines the value distribution based on day-of-week (ie. how is the distribution of values grouped on monday, tuesday, wednesday etc.). While this is a rather naive example of an analyzer, it will work well as just that - an example.
We'll begin with the requirements for building an analyzer:

You need to define a class that implements either the RowProcessingAnalyzer<R> or ExploringAnalyzer<R> interface. The latter is an advanced (and less frequent) type of analyzer, so we'll stick with the first: A row processing analyzer. <R> defines the result type of the analyzer. We can reuse a built-in result-type or write our own.
The class needs to be annotated with the @AnalyzerBean annotation. This annotation takes an argument: The display name of the analyzer.
You need to inject one or more InputColumn<E>'s using the @Configured annotation in order to consume the incoming data. The <E> type-parameter defines the datatype of interest, which is also used to determine which kinds of data types the analyzer supports. In our case we'll use Date as the InputColumn type, because we want our analyzer to consume date values.

So here is our class when it has been created in accordance with the requirements above:

@AnalyzerBean("Average date analyzer")
public class AverageDateAnalyzer implements RowProcessingAnalyzer<CrosstabResult> {

      @Configured
      InputColumn<Date> dateColumn;

      public void run(InputRow row, int distinctCount) { ... }
      public CrosstabResult getResult() { ... }
}

Notice that we're using the built-in result type CrosstabResult, which represents a result consisting of a dimensional crosstab. We could have used other built-in result types or we could have created our own result-class - the only requirement is that it implements the AnalyzerResult interface.

The rest of the Analyzer should be "plain old Java" but of course using the API's that are available in AnalyzerBeans. I've explained most of these things before, but I'll go through it again.

So now to consider how to implement the concrete analyzer logic. We'll use a regular map to hold the distribution values. We'll map the weekday numbers to counts in this map. But we'll need to keep a count for each column that we're analyzing, so it's going to be a nested map:

private Map<InputColumn<Date>, Map<Integer, Integer>> distributionMap;

To initialize the map we need to have the InputColumn's injected first, so the constructor won't do. In stead we can annotate a method with the @Initialize annotation, which will make AnalyzerBeans invoke the method when the bean has been properly initialized.

@Initialize
public void init() {
  distributionMap = new HashMap<InputColumn<Date>, Map<Integer, Integer>>();
  for (InputColumn<Date> col : dateColumns) {
    Map<Integer, Integer> countMap = new HashMap<Integer, Integer>(7);
    for (int i = Calendar.SUNDAY; i <= Calendar.SATURDAY; i++) {
      // put a count of 0 for each day of the week
      countMap.put(i, 0);
    }
    distributionMap.put(col, countMap);
  }
}

Now that the map has been initialized we can proceed to implement the run(...) method:

@Override
public void run(InputRow row, int distinctCount) {
  for (InputColumn<Date> col : dateColumns) {
    Date value = row.getValue(col);
    if (value != null) {
      Calendar c = Calendar.getInstance();
      c.setTime(value);
      int dayOfWeek = c.get(Calendar.DAY_OF_WEEK);
      Map<Integer, Integer> countMap = distributionMap.get(col);
      int count = countMap.get(dayOfWeek);
      count += distinctCount;
      countMap.put(dayOfWeek, count);
    }
  }
}

This should be pretty much "Java as usual". The only thing that should be new to you if you're an experienced Java developer is the way you extract values from the InputRow using the InputColumns as qualifiers:

Date value = row.getValue(col);

Notice that the value variable has the Date type. The AnalyzerBeans API takes advantage of type-safety to a large extent. Since the injected InputColumn's are defined as Date-columns this means that we can safely assume that the values in the incoming row is also of the Date-type. Furthermore the Date-column will be used to verify the configuration of AnalyzerBeans jobs and early error messages to the user if he tries to configure this particular Analyzer with a non-Date column. Now on to creating the result. As stated earlier we will use the CrosstabResult for this. The crosstab result is a pretty dynamic result type that can be used for a lot of purposes. It's metaphor is similar to DataCleaners result matrices but with added features. Here's how we build our crosstab:

@Override
public CrosstabResult getResult() {
  CrosstabDimension columnDimension = new CrosstabDimension("Column");
  CrosstabDimension weekdayDimension = new CrosstabDimension("Weekday");
  weekdayDimension.addCategory("Sunday").addCategory("Monday")
    .addCategory("Tuesday").addCategory("Wednesday").addCategory("Thursday")
    .addCategory("Friday").addCategory("Saturday");

  Crosstab crosstab = new Crosstab(Integer.class, columnDimension, weekdayDimension);
  for (InputColumn col : dateColumns) {
    columnDimension.addCategory(col.getName());
    CrosstabNavigator nav = crosstab.where(columnDimension, col.getName());
    Map countMap = distributionMap.get(col);
    nav.where(weekdayDimension, "Sunday").put(countMap.get(Calendar.SUNDAY));
    nav.where(weekdayDimension, "Monday").put(countMap.get(Calendar.MONDAY));
    nav.where(weekdayDimension, "Tuesday").put(countMap.get(Calendar.TUESDAY));
    nav.where(weekdayDimension, "Wednesday").put(countMap.get(Calendar.WEDNESDAY));
    nav.where(weekdayDimension, "Thursday").put(countMap.get(Calendar.THURSDAY));
    nav.where(weekdayDimension, "Friday").put(countMap.get(Calendar.FRIDAY));
    nav.where(weekdayDimension, "Saturday").put(countMap.get(Calendar.SATURDAY));
  }
  return new CrosstabResult(getClass(), crosstab);
}

Now we're done. You can take a look at the final result here. When I run this analyzer with a small sample of data in three columns the result looks like this:

             Order date Shipment date Delivery date
Sunday                0             0             0
Monday                2             0             1
Tuesday               0             2             1
Wednesday             0             0             0
Thursday              1             0             0
Friday                1             1             2
Saturday              0             1             0

You can also check out the unit test for this analyzer here.

Improved browsing of eobjects.org Subversion repository

2010-09-25T19:03:00.008+02:00

10 minutes ago I stumbled upon Repos style, an XSLT-based plugin for improving the visual browsing experience of Subversion repositories. It took literally 5 minutes to install and works great. Thank you Repos style :) Take a look at the beautiful result:

Besides beautifying the browsing experience it also adds a valuable history view. If you click the "view history" links you'll be able to track the history of individual folders and files.

See it yourself at http://eobjects.org/svn/...

Pattern finder 2.0 - the latest feature in AnalyzerBeans

2010-09-13T16:23:00.013+02:00

I'm happy to be able to present a feature in this blog post that I know a lot of you have been asking for: A new and improved "Pattern finder" (as known in DataCleaner). Currently it lives within AnalyzerBeans (which only has a command line interface) but it will be rather easy to convert it to the "old" DataCleaner as well.

The new Pattern finder works similarly to the old one. The new thing is that it supports a wide variety of configuration options (and it has been designed so that it will be significantly easier to add more options, if needed). Here are the current available options:

Property	Default value	Description
Discriminate text case	true	Sets whether or not text tokens that are upper case and lower case should be treated as different types of tokens.
Discriminate negative numbers	false	Sets whether or not negative numbers should be treated as different token types than positive numbers.
Discriminate decimals	true	Sets whether or not decimal numbers should be treated as different token types than integers.
Enable mixed tokens	true	Enables the "mixed" token type (denoted as '?' output). This type of token will occur when numbers and letters occur without separation of whitespaces.
Ignore repeated spaces	false	Sets whether or not repeated whitespaces should be ignored (ie. matched with single whitespaces)
Decimal separator	,*	The separator used to identify decimal numbers.
Thousands separator	.*	The character used as a thousands separator in large numbers.
Minus sign	-*	The character used to denote negative numbers.
Predefined token name	(none)	Can be used to define an anticipated "predefined token" that should be replaced before any subsequent pattern recognition. Requires that the "Predefined token regexes" property is also set. An example of a name could be "Titulation"
Predefined token regexes	(none)	Defines a set of regular expressions for the "predefined token". Requires that the "Predefined token name" property is also set. An example value for these regular expressions could be "[Mr,Mrs,Miss,Mister]" (which would correspond to the "Titulation" name).

* = Depending on locale, the shown value is the typical one.

This may all seem complicated, but rest assured that the default values are reasonable and almost exactly resembles what you would expect from the Pattern finder in DataCleaner (except for the "Discriminate text case" property, which is inherently turned off in DataCleaner).

Here's how it works with a set of different inputs (job title, email, name) and configurations:

> java -jar target/AnalyzerBeans.jar -conf examples/conf.xml -job examples/patternfinder_job.xml 

RESULT:
                            Match count Sample      
Aaaaa Aaa                            17 Sales Rep   
AA Aaaaaaaaa                          2 VP Sales    
Aaaaa Aaaaaaa (AAAA)                  2 Sale Manager (EMEA) 
Aaaaa Aaaaaaa (AAAAA, AAAA)           1 Sales Manager (JAPAN, APAC) 
Aaaaaaaaa                             1 President   


RESULT:
                                  Match count Sample      
aaaaaa.aaa@aaaaaaa[Domain suffix]           4 foo.bar@company.com 
aaaaaaa@aaaaaaaa[Domain suffix]             3 santa@claus.com 


RESULT:
                 Match count Sample      
aaaaaaa aaaaa              4 Jane Doe    
aaaaaaaa, aaaaaa           2 Bar, Foo    
aaa. aaaaaa aaa            1 Mrs. Foobar Foo

Notice that in the email example the two patterns end with "[Domain suffix]". This is because I've registered a corresponding "Predefined token" for this:

<analyzer>
  <descriptor ref="Pattern finder" />
  <properties>
    <property name="Predefined token name" value="Domain suffix" />
    <property name="Predefined token regexes" value="[\.com,\.org]" />
  </properties>
  <input ref="col_email" />
</analyzer>

So now that you've seen the new Pattern finder... Does it meet all your expectations? Let me know if you've got any ideas or unresolved issues!

Join the DataCleaner group at LinkedIn

2010-09-12T11:40:00.006+02:00

I've opened up a new LinkedIn group for DataCleaner and I would like to invite anyone with an interest in DataCleaner and open source data quality to join.

If you've read my blog lately you will know that we are currently in heavy development of a new engine for the application (called "AnalyzerBeans") and the groups focus right now is also on this development. But it is also for sharing experience, discussing features, issues and solutions.

Join the group to help us gather a bit of traction for the project.

More instructions for authoring AnalyzerBeans jobs

2010-09-11T22:32:00.001+02:00

I've previously posted a blog entry about how you could now download and run a simple example of AnalyzerBeans in the shell. I've updated the example and improved the command-line interface so that it will further assist you if you are interested in using the tool.

First of all, the command-line tool now has a reasonable usage screen:

> java -jar target/AnalyzerBeans.jar
-conf (-configuration, --configuration-file) FILE
: XML file describing the configuration of AnalyzerBeans
-ds (-datastore, --datastore-name) VAL
: Name of datastore when printing a list of schemas, tables or columns
-job (--job-file) FILE
: An analysis job XML file to execute
-list [ANALYZERS | TRANSFORMERS | DATASTORES | SCHEMAS | TABLES | COLUMNS ]
: Used to print a list of various elements available in the configuration
-s (-schema, --schema-name) VAL
: Name of schema when printing a list of tables or columns
-t (-table, --table-name) VAL
: Name of table when printing a list of columns

As you can see, you can now for example list all available analyzers (there are a lot, so I'm only posting the relevant parts for my up-coming example here, the rest have been replaced with "..."):

> java -jar target/AnalyzerBeans.jar -conf examples/conf.xml -list ANALYZERS
Analyzers:
----------
...
name: String analyzer
- Consumes multiple input columns
...
name: Value distribution
- Consumes a single input column
- Property: name=Record unique values, type=boolean, required=true
- Property: name=Bottom n most frequent values, type=Integer, required=false
- Property: name=Top n most frequent values, type=Integer, required=false
...
name: Number analyzer
- Consumes multiple input columns

I'll help you read this output: There are three analyzers listed. The String analyzer and Number analyzer both consume multiple columns, which means that they can be configured to have multiple inputs. Value distribution is another analyzer which only consumes a single column and has three configurable properties: Record unique values, Bottom n most frequent values and Top n most frequent values.

You can similarly list available transformers:

> java -jar target/AnalyzerBeans.jar -conf examples/conf.xml -list TRANSFORMERS
...

or datastores:

> java -jar target/AnalyzerBeans.jar -conf examples/conf.xml -list DATASTORES
...

or tables and columns in a particular datastore

> java -jar target/AnalyzerBeans.jar -conf examples/conf.xml -ds employees_csv -list TABLES
...
> java -jar target/AnalyzerBeans.jar -conf examples/conf.xml -ds employees_csv -t employees -list COLUMNS
...

So now you have all the details that enable you to author an XML-based AnalyzerBeans job yourself. Let's take a look at the example. I'm going to post a few snippets from the employees_job.xml file which I also used in my previous post. Notice that this file has been updated since my last post so you will need to run an "svn update" if you followed my previous tutorial, in order to get up-to-date code and data.

The file starts up with a little metadata. We're not going into detail with that. Then there's the <source> part:

<source>
<data-context ref="employees_csv" />
<columns>
<column id="col_name" path="employees.csv.employees.name" />
<column id="col_email" path="employees.csv.employees.email" />
<column id="col_birthdate" path="employees.birthdate" />
</columns>
</source>

The content is almost self-explanatory. There's a reference to the employees_csv datastore and the three columns defined in the CSV file: name, email, birthdate. Notice the id's (marked in red) of these three columns. These id's will be referenced further down in the XML file.

The next major part of the XML file is the transformation part. Let's have a look at one of the transformations:

<transformer>
<descriptor ref="Email standardizer" />
<input ref="col_email" />
<output id="col_username" name="Email username" />
<output id="col_domain" name="Email domain" />
</transformer>

This snippet defines that the Email standardizer transformer consumes a single column (col_email) and generates two new virtual columns: col_username and col_domain. Now understanding the final part of the XML file will be pretty obvious. Let's have a look at one of the analyzers defined in the <analysis> part:

<analyzer>
<descriptor ref="Value distribution" />
<input ref="col_username" />
</analyzer>

It simply maps the (virtual) col_username column to a Value distribution analyzer which is then executed (along with all the other analyzers defined in the file) when you run the job from the command line:

> java -jar target/AnalyzerBeans.jar -conf examples/conf.xml -job examples/employees_job.xml
...
Value distribution for column: Email username
Null count: 0
Unique values:
- asbjorn
- foo.bar
- foobar.foo
- jane.doe
- john.doe
- kasper
- santa
...

I hope that you find this XML format pretty straight forward to author. Of course we will be implementing a graphical user interface as well, but for the moment I am actually quite satisfied with this early user interface.

Developing a value transformer using the AnalyzerBeans Java API

2010-09-07T13:05:00.020+02:00

In this blog-entry I will demonstrate the Java API of AnalyzerBeans to create transformers, ie. components for transforming/converting/tokenizing/generating new values based on the existing values of a dataset. You will need Java programming skills to follow this tutorial.

I find that the easiest way to explain this process is by running an example. So here's my case: I want to transform birthdates of persons (represented as Date fields) into age fields (represented as a number field). A scenario is depicted below:

After the transformation I will be able to independently process the age field, eg. with a number analysis, value distribution or apply some business rule that depends on age.

The requirements for building a transformer class are the following:

The class must implement the Transformer<E> interface, where <E> is the data type of the generated fields. In our case we will therefore implement Transformer<Integer>.
The class must be annotated with the @TransformerBean annotation. The annotation takes an argument: The readable name of the transformer. We will thusly annotate: @TransformerBean(”Date to age”)
In order to read from the incoming fields we need to inject an InputColumn<E> instance (or alternatively an array of these), where <E> is the data type of the incoming fields. To inject we use the @Configured annotation. In our example this translates to: @Configured InputColumn<Date> dateColumn;

After these steps our code will look something like this:

@TransformerBean("Date to age")
public class DateToAgeTransformer implements Transformer<Integer> {

  @Configured
  InputColumn<Date> dateColumn;

  @Override
  public OutputColumns getOutputColumns() {
    // TODO
    return null;
  }

  @Override
  public Integer[] transform(InputRow inputRow) {
    // TODO
    return null;
  }
}

As we see, there are two methods defined by the Transformer interface, that we need to implement. They are:

getOutputColumns(): This method is called by the framework to determine which virtual columns will be produced by the transformer. In our case it is quite simple: The transformer creates virtual columns for age (both in days and in years, just to make it more flexible). The method body should therefore just be:
```
return new OutputColumns("Age in days", "Age in years");
```

transform(InputRow): This method will be called for each row with values to be transformed. The return type of the method is an Integer-array because we chose to implement Transformer<Integer>. The indexes of the returned array should match the output columns, ie. index 0 is for ”Age in days” and index 1 is for ”Age in years”. Let's have a look at the methods implementation:

Integer[] result = new Integer[2];
Date date = inputRow.getValue(dateColumn);

if (date != null) {
  long diffMillis = today.getTime() - date.getTime();
  int diffDays = (int) (diffMillis / (1000 * 60 * 60 * 24));

  result[0] = diffDays;

  // use Joda time to easily calculate the diff in years
  int diffYears = Years.yearsBetween(new DateTime(date), new DateTime(today)).getYears();
   result[1] = diffYears;
}

return result;

Of course I didn't do all the work of writing this tutorial without checking in the code so you could try it in action. The code for the ”Date to age” transformer is available here and there's also a unittest available here, that is pretty usable as a demonstration of how to unittest transformers. I hope some of you engage in developing transformers and let me know how it turns out. In my next blog post I'll explain how to build Analyzers which are the obvious next step when developing components for AnalyzerBeans.

There are a few other good examples of transformers that might be of interest:

The Convert to date transformer which will try to convert any value to a date. This is perhaps useful in combination with the transformer that I've just explained in this tutorial. In other words: These two transformers may need to be chained if for example the birth date to be transformed is stored in a String-based field.
The Tokenizer transformer because it has a flexible amount of output columns based on the users configuration. Notice the @Configured Integer numTokens that is used in the getOutputColumns() for this purpose.
The Email standarizer transformer, because it makes use of the NamedPattern object, which I have previously introduced in my blog entry "A nice abstraction over regular expressions".

Now you can run AnalyzerBeans (from the shell)

2010-08-29T12:26:00.012+02:00

Lately I've been blabbering a lot about the marvels of AnalyzerBeans - the project that is aimed at re-implementing an engine for data analysis based on my experience from DataCleaner.

An important milestone in any development project, especially those like AnalyzerBeans, that are implemented bottom-up, is when it is actually possible to use the application without having any developer skills. So far the development of AnalyzerBeans has been focused on making it work in a unittesting perspective, but now we've reached a point where it is also possible to invoke the engine from the command line.

Since we haven't released AnalyzerBeans yet, you will still have to check out the code and build it yourself. It's rather easy - it just requires Subversion and Maven. First, check out the code:

> svn co http://eobjects.org/svn/AnalyzerBeans/trunk AnalyzerBeans

Now build it:

> cd AnalyzerBeans
> mvn install

And now run the example job that's in there:

> java -jar target/AnalyzerBeans.jar \
> -configuration examples/conf.xml -job examples/employees_job.xml

The job will transform/standardize the "full name" and "email address" columns of a CSV-file (located in the examples-folder) and then print out value distribution and string analysis results for the standardized tokens: First name, Last name, Email username, Email domain.

If you've gone this far, you've probably also tried opening the xml-files employees_job.xml and conf.xml in the examples-folder. Maybe you've even figured out that the conf.xml describes the application setup and that the employees_job.xml file describes the job contents. You can edit these files as you please to further explore the application. I will be sure to update my blog soon with some more examples. Also one of the next features of the command line interface will be to print the available Analyzers and Transformers in order to make it easier to author the xml job-files.

If you're just trying this out now and if you are getting excited about AnalyzerBeans, here are my previous blog posts on the subject. Please don't hesistate to let me know what you think.

2010-08: A nice abstraction over Regular Expressions
2010-08: Visualizations and API Documentation for AnalyzerBeans
2010-07: Data transformation added to AnalyzerBeans
2009-06: Introducing AnalyzerBeans

A nice abstraction over regular expressions

2010-08-27T18:11:00.009+02:00

Often when you're developing data profiling, matching or cleansing software, you're dealing with expression matching, typically through regular expressions (regexes). One thing that I find is that it is often a tedious and error-prone task to define and reuse regexes or parts of regexes. In AnalyzerBeans there's a huge need for easier and reusable pattern matching. To counter this requirement I've come up with a helper-class, Named Pattern, which you can use to match and identify tokens in the patterns in a type-safe and easy way. Here's a short example for matching and tokenizing names based on two simple patterns:

//First define an enum with the tokens in the pattern(s)
public enum NamePart { FIRSTNAME, LASTNAME, TITULATION }

// The two patterns
NamedPattern<NamePart> p1 = new NamedPattern("TITULATION. FIRSTNAME LASTNAME", NamePart.class);
NamedPattern<NamePart> p2 = new NamedPattern("FIRSTNAME LASTNAME", NamePart.class);
NamedPattern<NamePart> p3 = new NamedPattern("LASTNAME, FIRSTNAME", NamePart.class);

// notice the type parameter <NamePart> - the match result type is typesafe!
NamedPatternMatch<NamePart> match = p1.match("Sørensen, Kasper");
assert match == null;

match = p2.match("Sørensen, Kasper");
assert match == null;

// here's a match!
match = p3.match("Sørensen, Kasper");
assert match != null;

String firstName = match.get(NamePart.FIRSTNAME);
String lastName = match.get(NamePart.LASTNAME);
String titulation = match.get(NamePart.TITULATION);

All in all I think that the NamedPattern class (and the NamedPatternMatch) in combination with your own enums is a pretty elegant way to do string pattern matching. There's also a way to specify how the underlying regular expression will be built by letting the enum implement the HasGroupLiteral interface.

Developers can dive into the details of these classes and interfaces at the Javadoc / API Documentation for AnalyzerBeans (package org.eobjects.analyzer.util).

Visualizations and API documentation for AnalyzerBeans

2010-08-09T18:30:00.009+02:00

I've spent a few hours trying to capture some of the basic principles of data flow and execution in my new favourite spare-time project AnalyzerBeans. Here's the results, that you will also find available in the API Documentation.

The first image shows the relationship analyzers, transformers and the data that they consume:

The second image shows a "close-up" of a row of data. Some of that values originate from the actual datastore, while some of the values may be virtual, generated by a chain of transformers:

Enjoy :)

Data transformation added to AnalyzerBeans

2010-07-19T18:20:00.002+02:00

I have been doing a lot of improvements to the API of AnalyzerBeans - a sandbox project that I am very passionate about. In short it is a new Data Profiling/Analysis engine that I think will eventually replace the core parts of DataCleaner. So here's a bit about "what's cookin'":

The largest of the new features is that it is now possible to transform data before it will be analyzed. The idea here is that it should be possible to tokenize/split/convert/etc. values before they enter the analysis. This means one fundamental change to analyzers, namely that they consume data through an intermediary input-column type which can be virtual (to represent eg. a token) or physical (to represent a "regular" column in a datastore). The new component type, "Transformer Beans" will support all the same cool stuff that I've already introduced to the analyzer components like dependency injection, persistent/scalable collections, annotation-driven composition and registration etc.
Another neat thing that I'm currently finishing up is an Analysis Job Builder. The idea is that analysis jobs should be immutable because this makes it a lot safer to parallelize the process of executing the jobs. Immutable structure are very good to work with when you are executing but they tend to be tedious when you're building the structure. So I'm also adding an API for building the jobs which will emphasize type-safety and syntactic neatness to make it easy to programmatically manage and verify the jobs you're building. This will make it a lot easier to build a good UI for AnalyzerBeans.

Using DataCleaner's API to run jobs as a part of your Java applications

2010-05-23T13:19:00.014+02:00

Yesterday someone asked me if there where any examples around of how to set up scheduled DataCleaner jobs in a Java EE environment. While the common case have been to just use the Command-Line Interface (CLI) for DataCleaner together with cron-jobs or Windows' scheduled tasks, he had a point - for some organizations this kind of solution would be insufficient - invocation through code would be better if you already have a lot of Java applications running (eg. in a Java EE environment).

So here's my response to that request - I'll try to walk you through the process of invoking DataCleaner through it's Java API. I'll start out with an example of a Profiling job - validation is quite similar but I'll cover that in another blog-post later. It's my ambition that these walkthroughs will eventually end up in the DataCleaner online docs.

The package dk.eobjects.datacleaner.execution holds the main entrypoints for setting up and running a DataCleaner job. First you need to have a DataCleanerExecutor - in this case we wanna execute profiling jobs so we'll use a factory-method for setting up our executor accordingly:

DataCleanerExecutor<ProfilerJobConfiguration,IProfileResult,IProfile> executor = ProfilerExecutorCallback.createExecutor();

Notice the three type-parameters. They dictate that this executor handles ProfilerJobConfigurations, it produces IProfileResults and it executes using IProfile's.

Now it's time to create some profiling jobs. We do this by adding configuration-objects that describe the tasks at hand (the executor will handle the lifecycle of the actual profilers for us). In this example we'll configure a ValueDistributionProfile:

// for this purpose we never use the "displayName" param for anything, so we just enter "valuedist" or whatever
IProfileDescriptor descriptor = new BasicProfileDescriptor("valuedist", ValueDistributionProfile.class);

ProfilerJobConfiguration jobConfiguration = new ProfilerJobConfiguration(descriptor);

// all properties are by convention placed as constants within a PROPERTY_ prefix in their profile class
jobConfiguration.addProfileProperty(ValueDistributionProfile.PROPERTY_TOP_N, "5");
jobConfiguration.addProfileProperty(ValueDistributionProfile.PROPERTY_BOTTOM_N, "5");

Also we need to select which columns to profile as a part of our job configuration. DataCleaner uses MetaModel for it's datastore connectivity so we need to find our retrieve our column definitions using a MetaModel DataContext. I'll examplify with typical MySQL database connection values but there are a lot of other options in the DataContextSelection class:

DataContextSelection dcs = new DataContextSelection();
dcs.selectDatabase("jdbc:mysql://localhost/mydb", null, "username", "password", new TableType[] {TableType.VIEW, TableType.TABLE});
DataContext dc = dcs.getDataContext();
Table[] tables = dc.getDefaultSchema().getTables();

// I'll just add all columns from all tables!
List<Column> allColumns = new LinkedList<Column>();
for (Table table : tables) {
allColumns.addAll(Arrays.asList(table.getColumns));
}

jobConfiguration.setColumns(allColumns);

Finally, we add our job configuration to the executor:

executor.addJobConfiguration(jobConfiguration);

If we want to, we can add our own observers to recieve notifications as the job progresses. For example, in the DataCleaner GUI we use an observer for updating the on-screen progress indicators.

executor.addProgressObserver(...);

Another optional feature is to set the execution options through an ExecutionConfiguration object. As an example we can configure our job to use multithreading assigning more than one connection and/or by allowing more than one query to execute at a time (the example below has a max thread count of 2*5 = 10):

ExecutionConfiguration conf = new ExecutionConfiguration();
conf.setMaxQueriesPerConnection(2);
conf.setMaxConnections(5);
executor.setExecutionConfiguration(conf);

And now it's time to kick off the executor! When we do this we provide our DataContextSelection object, which holds the connection information needed to spawn connections to the datastore.

executor.execute(dcs);

Alternatively you can start the execution asynchronously by calling:

executor.execute(dcs, false);

And now ... you're done. All you have to do now is investigate the results. You retrieve these calling:

List<IProfileResult> results = executor.getResults();

Consider using one of the result exporters in the DataCleaner API (providing support for CSV, XML and HTML export) or use some custom code to retrieve just the metrics of your interest by traversing the IProfileResult model.

I hope this walkthrough has brought some light to the subject of invoking DataCleaner through it's Java API. It's the first time I sit down and try to explain this part of the application so I might have missed some points but I think the major ideas are present. Let me know what you think - and suggestions for improving the API is always welcome.

A couple of notes to the use of DataCleaner's execution API:

Notice in the javadocs that almost all the classes covered in this blog-post has a serialize() and a static deserialize(...) method. These are used for saving and loading the configuration to/from XML documents. So if you've already created your jobs using DataCleaners GUI then you can save these jobs (as .dcp or .dcv files) and restore them using using deserialize(...). That might be an easier and quicker path to solving your problems if you're not much keen of setting up everything in code.
If you want a shortcut for setting up the ProfileDescriptors and ValidationRuleDescriptors, then take a look at DataCleaners bundled XML files, datacleaner-config.xml, datacleaner-profiler-modules.xml and datacleaner-validator-modules.xml. These a Spring Framework based files that are currently used by DataCleaner as a convenient way to serve these descriptors. You should be able to load the objects easily using Spring and then you'll have the descriptors set up automatically.

Watch out for manual flushing in JBoss Seam

2010-05-15T21:46:00.010+02:00

I've done quite a lot of development in JBoss Seam for the last six months and overall I'm quite enthusiastic. Also I'm looking forward to using some of the features of Seam in their new Java EE 6 incarnations (ie. in short: @Inject in stead of @In, @Produces in stead of @Factory, @Unwrap and @Out, and @ConversationScoped in stead of @Scope(CONVERSATION) ;-)).

One key feature of Seam is it's persistence strategy and at first glance it's quite a cool thing. The idea is to use an extended persistence context which means that your entities are kept managed across transactions. The extended persistence context is very important as Seam wraps each request in transactions and all changes to entities caused by actions in the request will then be automatically propagated to the database. The extended persistence context saves you from having to call merge(...) in order to reattach your entities all the time. Calling merge(...) is a heavy operation so this is good.

This pattern makes a lot of sense just until the point where you want to make it possible to edit an entity throughout a few requests but then forget about your changes (because the user changes his/her mind). To make this use case possible the Seam guys are advocating to use "MANUAL flushing" which means that Hibernate won't flush updates to the database unless you programmatically tell it to. Seems smart - here's the idea: Hibernate will keep track of all changes made in transactions (requests) but won't flush them. At a certain time the user will typically hit a "Save changes" button and then everything will be flushed.

Apart from the fact that MANUAL flushing is a Hibernate-specific feature not available with other JPA persistence providers, this pattern has three very serious flaws:

Any query fired will cause an implicid flush - even if the flush-mode is MANUAL. This means that if your conversation involves a query, your changes will be flushed even though you haven't invoked the flush-method yourself. Again - this almost certainly rules out the possibility to use MANUAL flushing in just about any conversation I can imagine (especially if you want to enable navigation by nested conversations). Queries are a good example of something that used to be a 'side-effect-free function' but is now something that can impose a lot of unintended changes in state.
NOTE: I stand (a bit) corrected here - I was adviced that this behaviour can be avoided by setting the flushmode of the query to COMMIT and it seems to work.
While we're at the topic - if you wan't to enable nested conversations then you will have to do a lot of plumbing code to make sure that the nested parts doesn't invoke the flush-method and then end up flushing on behalf of the parent-conversation also. It IS possible to code your way around this flaw but it's a very serious prohibitant to compose your application of reusable nested conversations.
The Seam guys seem to have failed to realize that transactions are used for other purposes than saving entities. For example, if you're using JMS, you would send messages at commit-time which means that developers of the JMS-dispatch code will assume that if a commit takes place, data has been persisted. If the message contains for example id's of updated entities the messagehandler will access these entities before any updates has taken place because the updates haven't been flushed!

I think that these flaws make it utterly hard to develop applications using MANUAL flushing because of the intrinsics it imposes on the flow in your application. In this light, I'm quite pleased that they didn't include manual flushing in Java EE 6 (or rather, JPA 2).

Query multiple datastores with MetaModel 1.2

2010-01-30T13:51:00.010+01:00

I am currently packaging and distributing the new version of MetaModel - version 1.2. In this blog-post I'll introduce what I think is the most exciting thing introduced in this version: Composite DataContexts aka. "Query multiple datasources with a single query". Or in plain english: You can now treat multiple datastores as if they where one.

An example:
Imagine that you want to match a database table with the contents of an excel spreadsheet, you can easily create a query that reads from both datastores and does all the joining, filtering etc. that is possible with regular MetaModel queries.

DataContext database = DataContextFactory.createJdbcDataContext( myConnection );
DataContext spreadsheet = DataContextFactory.createExcelDataContext(new File("my_spreadsheet.xls");

Table dbTable = database.getDefaultSchema().getTableByName("my_db_table");
Column dbPkColumn = dbTable.getColumnByName("my_primary_key");
Table excelTable = spreadsheet.getDefaultSchema().getTableByName("my_sheet");
Column excelFkColumn = excelTable.getColumnByName("my_foreign_key");

// now we create a composite DataContext which enables us
// to explore and query both DataContexts transparently
// through the same DataContext reference!
DataContext composite = DataContextFactory.createCompositeDataContext( database, spreadsheet );

// example query with carthesian product and cross-datastore where clause
Query q = new Query();
q.from(dbTable).from(excelTable);
q.select(dbTable.getColumns())
q.select(excelTable.getColumns());
q.where(dbPkColumn, OperatorType.EQUALS_TO, excelFkColumn);

DataSet ds = composite.executeQuery(q)
// read the result

... How cool is that?

Of course if a query is posted to the composite DataContext that spans multiple underlaying DataContexts, it will most likely spawn a case of "client side joining" which will not perform well compared to co-locating the datastores. But often that is not possible (or practical if it's just a case of ad-hoc analysis) so I believe that the new composite DataContext feature can add some real value to a lot of projects!

Other notable news in MetaModel 1.2: We now support MS Access databases and dBase (.dbf) database-files.

Setting up Mondrian for JNDI DataSources, XML/A and custom CSS styles

2009-11-30T18:00:00.032+01:00

The other day I decided that I wanted to set up mondrian as an adhoc analysis package for Lund&Bendsens intranet application, "Yacs". I didn't want to install a large application like Pentaho for just this specific need - rather I wanted to deploy just a simple cube schema, reuse the Java EE datasource definition that the intranet-application was already using and apply some basic styling to comply with the corporate profile. Doing these steps showed a lot more complex than I first imagined, primarily because I think the examples of the standalone mondrian distribution are overly complex and poorly designed, incapsulation-wise. Here is a list of steps I recommend doing to set up Mondrian "the right way":

Deploy your Java EE datasource in a container/database-specific way. If you JBoss and MySQL like I do, here's an example datasource descriptor, place it in the deploy-folder with a filename like "mydatasource.xml" (bold parts should be replaced with your specific configuration):
<datasources>
   <local-tx-datasource>
     <jndi-name>MyDataSource</jndi-name>
     <connection-url>jdbc:mysql://localhost/mydatabase</connection-url>
     <driver-class>com.mysql.jdbc.Driver</driver-class>
     <user-name>username</user-name>
     <password>password</password>
     <min-pool-size>5</min-pool-size>
     <max-pool-size>20</max-pool-size>
     <valid-connection-checker-class-name>
       org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker
     </valid-connection-checker-class-name>
     <metadata>
       <type-mapping>mySQL</type-mapping>
     </metadata>
   </local-tx-datasource>
</datasources>
Unzip the mondrian.war archive so you can edit the application.
Add a container specific mapping of the DataSource to this application. In JBoss this is done by placing a file called "jboss-web.xml" in the WEB-INF folder with this content:
<jboss-web>
   <resource-ref>
     <res-ref-name>MyDataSource</res-ref-name>
     <res-type>javax.sql.DataSource</res-type>
     <jndi-name>java:/MyDataSource</jndi-name>
   </resource-ref>
</jboss-web>
Now edit the WEB-INF/web.xml file and add the following entry inside the <web-app> element:
<resource-ref>
   <res-ref-name>MyDataSource</res-ref-name>
   <res-type>javax.sql.DataSource</res-type>
   <res-auth>Container</res-auth>
</resource-ref>
</web-app>
Also change the mapping of the JPivot filter so it goes like this:
<filter-mapping>
<filter-name>JPivotController</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
Create a schema-file and save it under WEB-INF/mycatalog.xml. I won't give instructions as to writing schemas - Mondrians documentation cover this quite well.
If you want to enable XML/A support, use this as a template for your WEB-INF/datasources.xml file (notice here that we use the application-local JNDI string here (including java:comp/env/...)):
<datasources>
   <datasource>
     <datasourcename>Provider=Mondrian;DataSource=MyDataSource;</datasourcename>
     <datasourcedescription>My example datasource</datasourcedescription>
     <url>http://localhost:8888/mondrian/xmla</url>
     <datasourceinfo>Provider=mondrian;DataSource=java:comp/env/MyDataSource;
     </datasourceinfo>
     <providername>Mondrian</providername>
     <providertype>MDP</providertype>
     <authenticationmode>Unauthenticated</authenticationmode>
     <catalogs>
       <catalog name="MyCatalog"
         <definition>/WEB-INF/mycatalog.xml</definition>
       </catalog>
     </catalogs>
   </datasource>
</datasources>
The views on the cube that you now want must be created as individual JSP pages. One of the things that are really lacking in the mondrian bundle is reasonable JSP pages with less complexity and reasonable reuse of datasources. Here's how I build mine (you can more or less put this stuff into the testpage.jsp page and then you're not dependent on all the jsp include stuff) - notice now that the datasource-reference entered here is the just the last, ie. the reference in web.xml:
<% if (session.getAttribute("query01") == null) { %>
  <jp:mondrianquery id="query01" datasource="MyDataSource" cataloguri="/WEB-INF/mydatasource.xml">
   
  </jp:mondrianquery>
<% } %>

Horray! Now you've got connection pooling, sharing and all the other cool stuff that Java EE DataSources provide. Next step: Add styling. First: Remove all the stylesheets that come with mondrian. You don't need them because it's actually quite a lot easier to add your own than to try and modify the existing ones. Here's the result of 10 minutes of styling:

The important CSS id's and classes are:

To control styling of the pivot table and its cells it's important that you add <div> around this tag in the JSP:
<wcf:render ref="query01" xslUri="/WEB-INF/jpivot/table/mdxtable.xsl" xslCache="true" />
You can then use your div's id-attribute to target cells, headers etc. in your stylesheet.

The class .heading-heading: Used for headings of headings, ie. the top-level blue cells in the screenshot above.

The classes .column-heading-span, .column-heading-even, .column-heading-odd: Used for column headers, ie. the gray cells above the pivot table content.

The classes .column-heading-span, .column-heading-even, .column-heading-odd: Used for column headers, ie. the gray cells above the pivot table content.

The classes .row-heading-span, .row-heading-even, .row-heading-odd: Used for row headers, ie. the gray cells to the left of the pivot table content.

The classes .cell-even and .cell-odd: Used for the cells on even and odd rows.

I encourage the mondrian crew to clean up the reference application, but I'm guessing they are using the messy configuration to convince people to switch to a full Pentaho deployment :-)

JPA and the N+1 select problem

2009-11-09T16:02:00.003+01:00

Warning to readers: This blog entry contains references to articles only available in Danish. So if you keep on reading be prepared to weep if you want to follow my suggestions ;-)

Lately I've been working hard on Lund&Bendsens intranet and processes involved around it. I've been using JBoss Seam for the most part and overall I'm quite thrilled about this choice of web framework. One of the cool parts about Seam is the way it integrates with Java Persistence API (JPA)/Hibernate and handles my persistence context even when I'm rendering the views for the intranet.

At the same time when I have been developing the intranet features in Seam and JPA, my colleague Kenn Sano wrote an excellent article about the N+1 Select Problem in JPA. Here's what it all comes down to (In Danish):

"Man kan [med JPA] "vandre rundt" i en objektgraf og på magisk vis hentes data, som stilles til rådighed i takt med, at vi traverserer - dvs. objekters tilstand indlæses fra databasen alt imens vi bevæger os rundt i objektgrafen. Hvis man ikke er opmærksom på, hvordan JPA fungerer, kan det resultere i mange SQL-kald mod databasen, hvilket kan have stor negativ indvirkning på performance."

I was doing this plentifully in Seam. When presenting a list of courses or a list of students, Seam lets me easily traverse the items in the list by using EL-expressions such as "#{course.location.address}" which involved several N+1 performance penalties. For instance on the list of all planned courses, including their locations, enrolled students etc. I observed a whole of N*4+1 query penalty. So there's no doubt you need to be aware of the impact of your querying strategy.

Note: I'm not blaming JBoss Seam for this behavior ... Seam makes everything a whole lot easier and when everything is easy you just tend to forget to think yourself ;-) Anyways - go read the article if you're interested in JPA and understand Danish.

New book on Open Source Business Intelligence tells the DataCleaner-story

2009-09-08T12:41:00.003+02:00

About half a year ago we received an exciting inquiry from Jos van Dongen on behalf of him and his co-author Roland Bouman, telling us that they where writing a new book about Open Source Business Intelligence and in particular Pentaho-based solutions. And for this they where looking into DataCleaner for the data profiling section of the book!

The book is now out! It's called "Pentaho Solutions" and it's published by Wiley Publishing. You can read about it and buy it on their website as well.

The book contains a walkthrough for building a data warehouse using Open Souce tools and in doing so applying DataCleaner for the important job of profiling and validation.

We congratulate Roland Bouman and Jos van Dongen for their great work to promote Open Source Business Intelligence and thank them for mentioning DataCleaner while they're at it!

Open Source Data Quality with DataCleaner 1.5.2

2009-07-14T10:48:00.003+02:00

Today I've announced the release of DataCleaner 1.5.2, yay! I'm pretty excited about this release as I think this is probably the biggest of the minor releases to date. And especially I hope to see that our new "single jar file" distribution-option will attract new users. Go read the announcement for more details now :)

Introducing AnalyzerBeans

2009-06-28T13:13:00.007+02:00

It's been some time now since I first designed the core API's of the DataCleaner project and as time goes on, some of my initial assumptions about the design of profilers, validation rules and so on have shown to be less-than-optimal in regards to flexibility and scalability for the application. This is why yesterday I decided to do a major change in the roadmap for the project:

The idea about the "webmonitor" application (DataCleaner 2.0) have been cancelled for now. If anyone wants to realize this idea it's still something that I am very much interested in, but as you will see I have found that other priorities are more important.
A new project have been founded - for now as a "sandbox" project: AnalyzerBeans. AnalyzerBeans is a rethought architecture for datastore profiling, validation etc. - in one word: "Analysis". When this project is stable and mature we will probably be ready for something I like to think of as a new DataCleaner 2.0.

So why rethink datastore analysis? Because the "old way" have proven to be very cumbersome for some tasks that I did not initially realise would have importance. The current DataCleaner design assumes that all profiles, validation rules etc. do serial-processing of rows. This is not always the best way to do processing although it simplifies optimization of the execution-mechanism because all components execute in the same way and can thus share result sets etc. In AnalyzerBeans we want the best of both worlds: Flexibility to do al sorts of weird processing and rigidity for the lot of profilers which actually do process rows serially.

The solution is a new annotation based component-model. Each profiler, validation rule etc. will not have to implement certain interfaces because we can now mix and match annotations to the specific type of analysis-component - each "AnalyzerBean". There are a lot more interesting features available when we introduce an annotation-based model, but let me first give you a simple example of how a regular row-processing DataCleaner-style profile would look like:

@AnalyzerBean(name="Row counter", execution=ExecutionType.ROW_PROCESSING)
public class MySerialCounter {

@Configured("Table to count")
private Table table;
private long count = 0l;

@Run
public void run(Row row, long count) {
this.count += count;
}
}

Now this is not so impressive. I've just replaced the IProfile interface of DataCleaner's API's with some annotations. But notice how I've gotten rid of the ProfileDescriptor class which was used to hold metadata about the profiler. Instead the annotations represent the class metadata. This is actually excactly what annotations are for :-) Also notice that I've gotten a type-safe configuration-property using the @Configured annotation. This means that I don't have to parse a string, ask for a Table of the corresponding name etc. And the UI will become a LOT more easy to develop because of type-safe facilities like this.

But an even more exciting way to use the new API is when creating a whole new type of profiler, an exploring AnalyzerBean:

@AnalyzerBean(name="Row counter", execution=ExecutionType.EXPLORING)
public class MySerialCounter {

@Configured("Table to count")
private Table table;
private Number count;

@Run
public void run(DataContext dc) {
DataSet ds = dc.executeQuery(new Query().selectCount().from(table));
ds.next();
this.count = (Number) row.getValue(0);
ds.close();
}
}

Now this is something totally new: A component that can gain total control of the DataContext and create it's own query based on some @Configured parameters. I imagine that this programming model will give us complete flexibility to do exiting new things that was impossible in the DataCleaner-framework: Join testing, non-serial Value Distribution etc.

There are a few other annotations available to the AnalyzerBean-developers but I will take a look at them in a more in-depth blog-entry later. For now - let me know if you like the ideas and if you have any comments. Anyone who would like to help out in the development of the AnalyzerBeans project should visit our wiki page on the subject.

Update (2010-09-12)

A lot has happened to AnalyzerBeans since this blog entry. Here's a list of blog entries (in chronological order) that will help interested readers dive deeper into the development of AnalyzerBeans:

Performance benchmark: DataCleaner thrives on lower column counts

2009-06-13T19:57:00.005+02:00

Today I've conducted an experiment. After fixing a bug related to CSV-file-reading in DataCleaner, I was wondering how performance was impacted by different kinds of CSV file compositions. The reason that I suspected that this could impact performance is that CSV files with many columns will require a somewhat larger chunk of memory in order to keep a single row in memory compared to CSV files with fewer columns. In the older versions of DataCleaner we discovered that using 200 or more columns would actually make the application run out of memory! Fortunately, this bug is fixed, but there is still a significant performance penalty, as this blog post will hopefully show.

I auto-generated three files for the benchmark: "huge.csv" with 2.000 columns and 16.000 rows, "long.csv" with 250 columns and 128.000 rows and "slim.csv" with only 10 columns and a roaring 3.200.000 rows. Together, each file has 32.000.000 cells to be profiled. I set up a profiler job with the profiles Standard measures and String analysis on all columns.

Here are the (surprising?) results:

filename	rows	columns	start time	end time	total time
huge.csv	16000	2000	18:54:48	19:40:28	45:40
long.csv	128000	250	19:44:53	19:52:31	7:38
slim.csv	3200000	10	19:53:46	19:55:03	1:17

So the bottom line is: Lowering the number of columns has a very significant, positive impact on performance. Having a lot of columns means that you will need to hold a lot more data in memory and needless to say you will have to replace this large chunk of memory a lot of times during the execution of a large profiler job. Going all the way from 45 minutes to 1½ is quite an improvement - so don't pre-join tables or anything like that before you run them through your profiler.

eobjects.org @ JavaOne

2009-06-05T01:55:00.003+02:00

I am currently hanging out at the lovely JavaOne conference in San Francisco, checking out cool new Java technology and meeting interesting people. Of course I'm here as a representative from my employer I also do some blogging (in Danish).

Yesterday I saw an interesting session about JFreeChart and surviving as an Open Source professional. Dave Gilbert told us about how he has managed to live from his hobby as a JFreeChart developer, about cool new features of the excellent charting API and about the struggles of making money on Open Source. Very fascinating stuff and I hope that everybody in the chart-consuming business will give it a try. I was quite happily surprised to see the new interactive chart functionality that has been put into the API - I'm wondering how that hadn't found my attention before now! It gave rise to a couple of ideas (or rather: Sparked my motivation) for me to try and implement charting in DataCleaner.

Seam, EJB's and EAR-packaging in Maven

2009-04-29T17:53:00.007+02:00

Lately I've been designing a new course on the splendid JBoss Seam (2.1) Web and Java EE framework. One thing that strikes me, being an enterprise Java developer, is that almost no good examples of setting up Seam using Maven exist. I realize that Seam's appeal comes from a tradition of wanting things to be nice and easy, so the seam-gen tool has absolute merits for creating your project but to me the downside of this approach is that you're bound to use Ant as a build tool and for a lot of reasons I won't go into here, I strongly prefer using Maven.

The examples of using Maven to build Seam applications that I have been able to track down have been limited in a variety of ways:

Most of them required to use a specific Maven archetype which altered the default project layout.
Almost all of them where out of date and was bound to a very "custom" maven repository which I don't think is suitable for enterprise application infrastructure.
Those that didn't fall within the former two points where restricted to web-applications (WAR-files) only and thus didn't support using EJB's in the Seam applications.

So this blog post is going to be a walk-through of how I managed (after quite some effort) to configure Maven to be able to build an EAR file containing EJB's and a web-application that is able to utilize these EJB's as Seam components. I'm only highlighting the most interesting parts - You can download the complete example for free - and in contrast to the examples I have seen, it's very bare-boned and won't take more than a few seconds to alter to your needs.

Project structure
OK - to meet the demand of packaging an EAR file I've created a Maven project consisting of three modules. It's possible that this part can be optimized a bit since I use a seperate module for doing all the packaging (which to my understanding is the way to do it). The modules are:

ejbs - containing the EJB's and possibly other "backend"-stuff like JPA-classes and so on.
web - containing the web application
packaging - for packaging the EAR file containg the two other modules (and Seam)

Which module depends on what?
One of the big issues that I faced was figuring out how to configure Maven's dependency management framework to generate a correct set of EAR, JAR and WAR files. If this is done wrong it won't work and you'll waste hours (or at least I did) trying to figure out what is wrong.

All provided dependencies go into the parent project. These are all the dependencies provided by your container (plus Seam itself which will be packaged within the EAR file). You will need to add JBoss's Maven repository to resolve these dependencies. Here are the important parts of my parent project pom:

<repositories>
   <repository>
     <id>repository.jboss.org</id>
     <name>JBoss Repository</name>
     <url>http://repository.jboss.org/maven2</url>
   </repository>
</repositories>
<modules>
   <module>ejbs</module>
   <module>web</module>
   <module>packaging</module>
</modules>
<dependencies>
   <dependency>
     <groupId>org.jboss.seam</groupid>
     <artifactId>jboss-seam</artifactid>
     <version>2.1.1.GA</version>
     <scope>provided</scope>
   </dependency>
   <dependency>
     <groupId>javax.faces</groupid>
     <artifactId>jsf-api</artifactid>
     <version>1.2_02</version>
     <scope>provided</scope>
   </dependency>
   <dependency>
     <groupId>org.hibernate</groupid>
     <artifactId>hibernate-entitymanager</artifactid>
     <version>3.4.0.GA</version>
     <scope>provided</scope>
   </dependency>
   <dependency>
     <groupId>org.hibernate</groupid>
     <artifactId>hibernate-validator</artifactid>
     <version>3.1.0.GA</version>
     <scope>provided</scope>
   </dependency>
   <dependency>
     <groupId>javax.servlet</groupid>
     <artifactId>servlet-api</artifactid>
     <version>2.5</version>
     <scope>provided</scope>
   </dependency>
   <dependency>
     <groupId>javax.servlet.jsp</groupid>
     <artifactId>jsp-api</artifactid>
     <version>2.1</version>
     <scope>provided</scope>
   </dependency>
   <dependency>
     <groupId>javax.ejb</groupid>
     <artifactId>ejb-api</artifactid>
     <version>3.0</version>
   </dependency>
</dependencies>

In the ejbs module you will not need any dependencies!

In the web module you will need to add seam-ui (excluding the "core" seam, because it is provided within the EAR file), facelets and other web-dependencies such as RichFaces, seam-pdf or whatever. Here are the dependencies of my web module pom:

<dependencies>
   <dependency>
     <groupId>com.sun.facelets</groupid>
     <artifactId>jsf-facelets</artifactid>
     <version>1.1.11</version>
   </dependency>
   <dependency>
     <groupId>org.jboss.seam</groupid>
     <artifactId>jboss-seam-ui</artifactid>
     <version>2.1.1.GA</version>
     <exclusions>
       <exclusion>
         <groupId>org.jboss.seam</groupid>
         <artifactId>jboss-seam</artifactid>
       </exclusion>
     </exclusions>
   </dependency>
</dependencies>

If you want to write Seam components that pertain only to the web module, then you will probably also need to include the ejbs module as a dependency in the web module pom. But remember to set the scope as provided, because it's all within the same EAR.

Last but not least you will need to write the packaging module pom. The Seam Documentation have been used to guide how this pom is structured: The ejbs module needs to be a registered EJB module. The web module needs to be a registered WAR module. And Seam itself needs to be a registered EJB module as well! JBoss EL needs to be placed in the /lib directory of the EAR and you need to make sure to exclude the EL API dependency from several artifacts - otherwise you'll get weird classpath issues when deploying. The important parts of the packaging module pom looks like this (pay attention to the parts in bold):

<build>
   <plugins>
     <plugin>
       <artifactId>maven-ear-plugin</artifactid>
       <configuration>
         <modules>
           <jarmodule>
             <groupId>org.jboss.el</groupid>
             <artifactId>jboss-el</artifactid>
             <includeinapplicationxml>false</includeinapplicationxml>
             <bundledir>lib</bundledir>
           </jarmodule>
           <webmodule>
             
             <contextroot>/seam-ejb-ex</contextroot>
           </webmodule>
         </modules>
       </configuration>
     </plugin>
   </plugins>
</build>
<dependencies>
   <dependency>
     
     <type>ejb</type>
   </dependency>
   <dependency>
     
     <type>war</type>
   </dependency>
   <dependency>
     <groupId>org.jboss.seam</groupid>
     <artifactId>jboss-seam</artifactid>
     <version>2.1.1.GA</version>
     <type>ejb</type>
     <exclusions>
       <exclusion>
         <groupId>javax.el</groupid>
         <artifactId>el-api</artifactid>
       </exclusion>
     </exclusions>
   </dependency>
   <dependency>
     <groupId>org.jboss.el</groupid>
     <artifactId>jboss-el</artifactid>
     <version>1.0_02.CR2</version>
     <type>jar</type>
     <exclusions>
       <exclusion>
         <groupId>javax.el</groupid>
         <artifactId>el-api</artifactid>
       </exclusion>
     </exclusions>
   </dependency>
</dependencies>

So that's all the Maven configuration. Now to some "gotchas" in regards to Seam configuration. A lot of these things are being "disguised" by seam-gen so consider this a list of things you might have forgotten if you're building the project from the bottom up:

Remember to put empty seam.properties files in to the resource folders of both the ejbs and web modules.
Remember to register the Seam interceptors (as described in the Seam Documentation) in the ejb-jar.xml file, located in the resources/META-INF folder ejbs module.
The components.xml file should be located in the web module's WEB-INF folder. But you can't just copy the file from a seam-gen project because seam-gen dynamically replaces some "magic strings" in it to see to container compliancy. Instead you will have to add this static entry to the components.xml file yourself:
<core:init pattern="seam-ejb-ex/#{ejbName}/local"></core:init>
Note the seam-ejb-ex part of that static jndi pattern string. You will have to replace this part of the string with the context root entry that was highlighted in the packaging module pom!

I hope this blog entry have cleared up some of the confusion that I met when I tried building Maven-based Seam/EJB projects. Please let me know if it works out and remember that you can download the full example and use it as a reference as you like.

DataCleaner 1.5.1 released

2009-04-20T19:57:00.002+02:00

I'm happy to announce the release of DataCleaner version 1.5.1. This release is a minor release, nevertheless containing a few nice features - especially for the users who are enjoying the exporting features that was introduced in 1.5:

An additional HTML export format have been added to the built-in export formats (usable when exporting Profiler results in the desktop app and when executing the runjob command-line tool).
The export format is now choosable directly in the desktop app.
Four new measures where added to the String Analysis profile: avg. chars and max/min/avg white spaces.

The new version of DataCleaner is (as always) downloadable for free on the downloads page and feedback from users is also greatly appreciated, ie:

Fill out our online user survey, or
Post your comments and questions at our discussion forum.

We hope that you all enjoy DataCleaner 1.5.1.

Data quality pro interview

2009-02-11T15:56:00.004+01:00

Dylan Jones over at data quality pro is working on a feature about DataCleaner and I'm very thankful for his work already (and pretty excited to see the final result). The feature have just been started with an interview with a very important person ... Me! :-) For all of those who take an interest in DataCleaner, the visions of the product and it's story I hope that you will head over there and read the article.

To be continued with more posts on the data quality pro articles.

Free OLAP cube icon

2009-01-25T16:38:00.011+01:00

Okay this is a bit off-topic compared to my normal posts, but here goes.

When I do websites or GUI design I usually look out for free/open source icon packages such as Tango or Crystal. I have been looking for a nice-looking icon to represent an OLAP cube, preferably in a style and coloring similar to the Tango icon set. Sorry to say, I didn't find any, so I went ahead and spent some hours creating a new icon on my own. Here's the result:

And a plain version without the sum/count text elements (good if you need to resize it to very small sizes):

I'm giving this away under a beerware license, so if you want it, it's yours.

DataCleaner 1.5 - a heavy league Data Profiler

2009-01-20T13:11:00.004+01:00

Often when I speak to data quality professionals and people from the business intelligence world I get the notion that most people think of Open Source tools as slightly immature when it comes to heavy processing, large loads, millions-of-rows-kinda-stuff. And this has had some truth to it. I don't want to name names, but at least I have heard a lot of stories about Open Source data integration / ETL tools that wasn't up for the job when you had millions of rows to transform. So I guess this notion have stuck to Open Source data profilers and data quality applications too...

In DataCleaner 1.5 I want this notion demystified and eradicated! Here are some of the things we are working on to make this release a truly enterprise-ready, performance-oriented and scalable application:

Multi-threaded, multi-connection, multi-query execution enging
The execution engine in DataCleaner have been thoroughly refactored to support multithreading, multiple connections and query-splitting to perform loadbalancing on the threads and connections. This really boosts performance for large jobs and sets the bar for processing large result sets in Open Source tools I think.
On-disk caching for memory-intensive profiles and validation rules
Some of the profiles and validation rules are almost inherently memory intensive. We are doing a lot of work optimizing them as much as we can but some thing are simply not possible to change. As an example, a Value Distribution profile simply HAS to know all distinct values of each column that is being profiled. If it doesn't - then it's not a value distribution profile. So we are implementing various degrees of on-disk caching to make this work without flooding memory. This means that the stability of DataCleaner is improved to a heavy league level.
Batch processing and scheduling
The last (but not least important) feature that I'm going to mention is the new command line interface for DataCleaner. By providing a command line interface for executing DataCleaner jobs you are able to introduce DataCleaner into a grand architecture for data quality, data warehousing, master data management or whatever it is that you are using it for. You can schedule it using any scheduling tool that you like and you can save the results to automate reporting and result analysis.

Using Python and Django to build the new DataCleaner website

2009-01-07T01:09:00.001+01:00

I have for a long time been a dedicated Java developer and in many ways, still am. But developing the new website for DataCleaner have been quite an eye-opener for the potential of dynamic languages and Python in particular. There are so many things about that language that I love and I must say that doing the same thing in Java would have taken at least twice the time! And thats even though I'm not an unexperienced Java developer.

OK, so what's the big difference? Well, deployment is one very crucial difference. J2EE servers are great for stability and system administration but often I find myself, as a web developer, not needing all those things that much - I just need a server that always runs and will tell me what I am doing wrong. Django (which have been my Python web-framework) have been excellent in doing this for me so I can kick-start my application in a matter of seconds.

Type-safety is another big difference. Java is type-safe, Python (and other dynamic languages) is not. For back-end development I am a big advocate of type-safety but in front-end development dynamic classes are such a great treat! An example of this is when transfering data from Controllers to the View in the Django framework's Model-View-Controller architecture. If you want to present some domain objects that are related in the view, but not in the domain model (or perhaps the domain model has some details to it that you want to skip for understandability), then you just infer a completely new attribute into the domain object! In Java or other type-safe languages such as C#, you would typically have to create a Map for storing the new particular association and then do a lookup in the view to resolve the association. This means more "logic" in the view and code that is harder to comprehend.

All in all I'm very happy to use Django. I would have liked a few more features in their QuerySet API (especially aggregation queries, which should be on the way) but then again - for the typical website it is pretty sufficient and allows fallback to native SQL. Thank you Django.

Note: This is not to say that I am abandoning Java, not at all! I love Java for it's stability and superior integrational capabilities, but in some cases, I simply want something that is fast and more in tune with the user experiencing and prototyping process of building websites.

How to query an XML file as if it was a relational database

2008-11-23T00:18:00.027+01:00

For a couple of times I've spent my blogging time explaining how you can use MetaModel to query CSV files and Excel spreadsheets just as if they where regular databases which enable filtering, grouping, sorting etc. in their query languages (usually some form of SQL with more or less dialect).

Today let's take a look at MetaModels support for XML content. XML is, to say the least, very different from relational databases. Whereas relational databases consist of tables as the basic structural element, the XML structure is hierarchical and not table-based. You could say that the structure of XML is potentially a lot more dynamic because metaphorically XML supports nesting tables inside each others to emphasize the structure and relations. So dealing with XML in a framework like MetaModel was not a no-brainer in terms of design considerations and decisions. The main theme of MetaModel have always been to provide a consistent model that was the same even though you had to work with different kinds o datastores. So how did we do this when XML and other table-based datastores are so inherently different? I'll show you using an example...

Consider this compressed XML example, ie. an RSS newsfeed (coincidentally taken from the eobjects.org newsfeed):

<?xml version="1.0"?><rss version="2.0">
<channel>
<title>eobjects - Blog</title>
<link>http://eobjects.org/trac/blog
<description>Welcome to the new eobjects.dk website</description>
<item>
<title>MetaModel 1.1 released!</title>
<pubdate>Wed, 05 Nov 2008 14:01:02 GMT</pubdate>
<link>http://eobjects.org/trac/blog/metamodel-1.1-released
<description>text goes here...</description>
<category>release</category><category>license</category><category>metamodel</category>
</item>
<item>
<title>DataCleaner 1.5 "snapshot" released</title>
<pubdate>Mon, 13 Oct 2008 07:00:13 GMT</pubdate>
<link>http://eobjects.org/trac/blog/datacleaner-1.5-snapshot-released
<description>text goes here...</description>
<category>release</category><category>datacleaner</category><category>license</category>
</item>
</channel>
</rss>

I want you to notice a few things about the RSS structure so that we get a full understanding on how MetaModel will interpret the XML file and map it to a table based structure. The resulting MetaModel structure is illustrated to the right (you will perhaps also notice that I have edited out a lot of the details in the XML code above, for brevity):

Notice that the <channel> element does not contain any data but only acts as what I would call a "wrapper tag". There are some different inner elements inside <channel> which causes MetaModel to name tables according to this shared root node. Hence the table names "channel_item", "channel_title" etc.

A lot of values are contained in inner tags within the <item> elements, like the "title", "pubDate" and "link" elements. Since these appear only once for each <item> they are considered columns in a general <item> table: "channel_item".

Notice the multiplicity of the <category> tags inside the <item>'s. This causes a seperate table to be created to handle multiplicity: "channel_item_category".

As you can see we do a rather intuitive "XML to table-based model" mapping in MetaModel. This is done automatically through the process that we call auto-flattening of tables. If you prefer, you can also flatten the tables manually if you wish to compose the table model yourself (but I'll have to dedicate a seperate blog entry to that topic sometime).
The XML-elements to tables mapping enables you to do all the cool querying stuff with your XML files, that I have shown you so many times before. Let's do a simple example:

File file = new File("newsfeed.rss");
DataContext dc = DataContextFactory.createXmlDataContext(file, true, true);
Schema schema = dc.getDefaultSchema();

Table table = schema.getTableByName("channel_item_category");
Column col = table.getColumnByName("category");

// We'll make a query to show us the most popular newsitem categories:
// SELECT category, COUNT(*) as num_items FROM channel_item_category GROUP BY category ORDER BY num_items DESC
SelectItem countItem = SelectItem.getCountAllItem();
countItem.setAlias("num_items");

Query q = new Query().select(column).select(countItem).from(table).groupBy(column).orderBy(countItem, Direction.DESC);

DataSet dataSet = dc.executeQuery(q)
// Do something with the dataset!

If you're just interested in finding out how your XML format looks like after being autoflattened by MetaModel, I recommend downloading DataCleaner (from which the screenshot to the right was taken from). Over and out, enjoy MetaModel...

Setting goals for volunteer Open Source projects and DataCleaner in particular

2008-11-18T11:28:00.003+01:00

As you probably know I'm the founder and main developer of the Open Source project called DataCleaner. While there have been some notable contributions from outsiders I wouldn't be arrogant if I said that the crucial and main parts of the code-base for DataCleaner was written by me. But in a lot ways I often think of me as a medium of change, the bringer of the code, not the real source of the goal-setting and planning of the project. Also my main job in regards to DataCleaner have been to try and attract more developers and broaden the interest among users for DataCleaner, but that's not really relevant for this topic.

In this blog post I'll investigate the relationship between me, the medium, and the community who represents the real decisionmaking entity of DataCleaner. I suspect that a lot of times people who participate in a community doesn't realize the powerful position that they possess and how they should utilize the mediums of change.

Ok, so here's my main point that I want to stress:

The goals of DataCleaner are socially constructed by the desires of the community

What does this mean? This means that I don't set the goals for the project myself. Actually I have been doing so a lot but only because no one else did it. If the community was to say that they wanted the project to go in a direction I would be perfectly happy to help them.

What sparked me to do this blog entry was actually my friend and co-worker at eobjects.org, Asbjørn Leeth. When I was discussing some more or less technical changes that I was thinking of in regards to DataCleaner he said:

"I think you should carefully consider the overall purpose of DataCleaner and where you want it to go. Who are the users and how should DataCleaner be used in a broader context?"

I absolutely agree on this quote but the thing is that I wouldn't be the one to make those decisions! I would probably have an oppinion but ultimately it's not my decision because I'm not the user, I'm just the medium of change. I think that the DataCleaner community should be better to involve themselves in these crucial themes, but I don't blame them because this is perhaps not a role that we are yet familiar with as Open Source actors.

We need to recognize why the developers do Open Source software and when they are rewarded. This varies a lot from community to community but in the case of DataCleaner I personally get a kick out of it everytime say to me that they are using the product and that they think it rocks! This is my greatest reward. It's a lot greater than the times that people pay me money to help them with their problems (that may or may not be DataCleaner related). What does it mean that the greatest reward is the recognition of others? It means that you could effectively steer the development of DataCleaner simply by putting out your own goals and ideas for people like me to realize! Easy! This requires involvement and gratitude from the community but given that the community will be able to use the mediums of change in a far more effective way.

One example of how this works is when Ben Bor some months ago sent me a list of some-20 changes and fixes that he wanted in DataCleaner. Within a few days I had effectively fixed around half of them and the rest where submitted to the roadmap so we'll work ourselves through them as we go. My point here is that while I may be the one with the easiest access to changes (because I know the product and code so well) I may not be the one who knows what the user wants. That being said, put very shortly here are a couple of things that I have been thinking of in regards to high-level changes in the strategy of DataCleaner:

Change the name of the application! We don't provide data cleansing. Rather we mostly do profiling and our validation engine is also rather OK, so perhaps we should think of more fitting names.
Remove the separation of profiling and validation in the User Interface. The User Interface should rather reflect the process and provide convenient tools to the user instead of represent the internal entities of the application.
These changes would definately imply a change to version 2.0 of DataCleaner because it would mean fundamental changes both to the User Interface and the core module.

Those are just my 25 cents in the goal-setting debate. I think for Open Source to really prosper we need user-based communities who understand that they are not just "takers of software" they are also "givers of oppinions".

Query your Excel spreadsheet with Java

2008-11-17T10:35:00.008+01:00

The MetaModel project lets you do wonderful and advanced things like filtering, ordering, grouping etc. when working with otherwise static content in CSV files, Excel spreadsheets, XML files etc. Many times I have knocked my head to the door when trying to get simple summary data out of my excel spreadsheets or simply filtering on single rows in the sheets.

An example: Recently I was working on my tax return statement and throughout the year I had recorded all relevant activities in a simple spreadsheet. I had marked the activities on various accounts such as representation, spendings, earnings whatever. Simplified it looked something like this:

Title	Amount	Account
Asus EEE	1.600	Spendings
Advisory for Company X	4.000	Earnings

One thing I think is funny when dealing with Excel is that throughout time you've learned how to set up your spreadsheet for easy hacking instead of dynamic querying. What I mean by this is that if I wanted to get SUM for each account in my spreadsheet I would propbably have fundamentally changed my (otherwise nice and pure) spreadsheet in order to make it easy to perform the SUM function on singular accounts. For example I may have introduced an ammount column for each account. But alas, adding a new Account would be pretty cumbersome.

Enter MetaModel! With MetaModel I can write queries in a SQL-like manner. When I say "SQL-like" you may fret and think "oh but don't learn me another SQL dialect, just give me the real deal!". The answer to this is that what I'll learn you now will effectively replace all SQL dialects because the same model (or MetaModel ;)) is usable for all kinds of datastores: Excel, CSV/TSV files, XML and SQL-databases). Let's have a look at some code. First we'll load the Excel file into MetaModel and get a hold of our schema and table structure and identify our columns of interest:

// Use the factory to create a DataContext and automatically (true) narrow column types
DataContext dataContext = DataContextFactory.createExcelDataContext(new File("my_tax_return_activities.xls"), true);
Schema schema = dataContext.getDefaultSchema();
// A simple way of getting the table object is just to pick the first one (the first sheet in the spreadsheet)
Table table = schema.getTables()[0];
Column amountColumn = table.getColumnByName("Amount");
Column accountColumn = table.getColumnByName("Account");

Now that we have the columns represented and the DataContext which we can use to perform queries, here's how we make a couple of interesting queries using the MetaModel query API:

// SELECT SUM(Amount) FROM sheet WHERE Account = "Spendings"
Query q = new Query().select(new SelectItem(FunctionType.SUM, amountColumn)).from(table).where(accountColumn, OperatorType.EQUALS_TO, "Spendings");

// SELECT Account, SUM(Amount) FROM sheet GROUP BY Account
Query q = new Query().select(accountColumn).select(new SelectItem(FunctionType.SUM, amountColumn)).from(table).groupBy(accountColumn);

Now when we've authored the queries we can let MetaModel execute them and deal with the result as appropriate. If we just want to print out the results to the console we'll do something like this:

DataSet dataSet = dataContext.executeQuery(q);
while (dataSet.next()) {
System.out.println(dataSet.getRow());
}
dataSet.close();

Querying a CSV file!

2008-11-11T06:35:00.032+01:00

Today I'm going to demonstrate some of the functionality of MetaModel 1.1, which provides a datastore-transparent querying API for Java. Actually what I'll show you isn't all that new, we've been able to do most of the querying stuff for CSV files (and other datastores) roughly since MetaModel 1.0, but it seems to me that too few realize what a powerful tool we have with MetaModel, so I'm just going to repeat myself a bit here ;) Also, I'll demonstrate on of the new cool features of MetaModel 1.1 - column type detection, narrowing and value transformation.

For this example I'll use some real data: I've extracted this CSV file from the eobjects.org trac system. It contains a list of all the tickets (issues, bugs, tasks, whatever) that are active at the time of writing... You'll notice if you take a look at the file, that it's not exactly a simple CSV file - a lot of text spans multiple lines and there are quite a lot of different data types.

Ok, so let's get started. I've saved the data to a file: tickets.csv. Now I want to read the file and let MetaModel generate a metadata model based on it. I will also let MetaModel try and detect the column types (since CSV only contains text-types natively) which will automatically transform all values to the most fitting and narrow type that MetaModel can find (this is indicated by the 'true' parameter in the code below). Here's how we get a hold of the datastore model for the file:

File file = new File("tickets.csv");
DataContext dataContext = DataContextFactory.createCsvDataContext(file, true);

Once we have a DataContext object we are ready to go for our datastore-transparent way of querying. What we do is: We get a hold of the schema of our CSV file and we locate the table of interest. Since CSV is a single-table datastore type, getting the table of interest can be done in two ways:

Table table;

//get table by name
table = schema.getTableByName("tickets");

//get table by index
table = schema.getTables()[0];

Now we can go ahead and investigate the structure of the CSV file. Since we turned on automatic column type narrowing you will see that the 'ticket' column have been converted from a text-based column to an INTEGER type. Also, as MetaModel can verify that 'ticket'-values are never missing, it is asserted that the column is not nullable:

Column ticketColumn = table.getColumnByName("ticket");

//Will print: "Ticket column type: INTEGER"
System.out.println("Ticket column type: " + ticketColumn.getType());

//Will print: "Ticket column nullable: false"
System.out.println("Ticket column nullable: " + ticketColumn.isNullable());

And now for the fun and impressing part... Let's try to make some queries! Here are a couple of examples:

Query q;

//SELECT * FROM tickets ORDER BY ticket
q = new Query().select(table.getColumns()).from(table).orderBy(ticketColumn);

//SELECT SUM(ticket) FROM tickets
q = new Query().select(FunctionType.SUM, ticketColumn).from(table);

//SELECT _reporter AS rep_name, COUNT(*) AS num_tickets FROM tickets GROUP BY rep_name
Column reporterColumn = table.getColumnByName("_reporter");
q = new Query().select(reporterColumn,"rep_name).selectCount().from(table).groupBy(reporterColumn);

To execute the queries is very simple - just ask your DataContext object to execute the query object. MetaModel will then analyze the query and process the result behind the scenes:

DataSet dataSet = dataContext.executeQuery(q);

We can process MetaModel DataSets in quite a few ways. A typical way would be to iterate through it, similar to a ResultSet:

while (dataSet.next()) {
Row row = dataSet.getRow();
//Extract values or do something similar with the row
System.out.println("row: " + row);
}

... Or we can transform the DataSet into a TableModel object:

TableModel tableModel = dataSet.toTableModel();

... Or we can transform it into a list of Object arrays:

List<Object[]> objectArrays = dataSet.toObjectArrays();

As you can see, with the MetaModel API a lot of things that used to be difficult is now really, really easy!

Click here to display the full example.

MetaModel 1.1 released!

2008-11-05T15:45:00.002+01:00

I just ditched my masters thesis today to work on good 'ol MetaModel! I spent my time straightening out the last remaining tasks on the 1.1 release which I have been looking forward to for some time now. So I'm now happy to announce that

MetaModel 1.1 have just been released!

Head over to the eobjects.org news site to learn more about what this release is all about!

Open Source acknowledged by the Data Quality community?

2008-10-20T18:40:00.004+02:00

Here in Denmark I often feel that the Data Quality, Master Data Management and Business Intelligence field is pretty fearsome towards Open Source software. I think this largely has to do with lack of presence, a lot of prejudices and few established consultancy firms in this part of the world.

This is also why it's always a great surprise, and a good one, when you catch the interest of the international Data Quality venue. Last week I was in a correspondence with the guys over at Data Quality Pro who was building a new Open Source Data Quality page and we had a nice chat about the tools available and the opportunities out there. I'm very glad that people are showing interest and hopefully the Danish BI scene will also adapt to the wonderful world of Open Source software as we go along...

Talking about getting the word out, where should you "advertise" your Open Source product to the business world? I personally think it's hard work to market your product even though you're giving it away for free and you would think that people automatically rushed to your website ;) Of course everyone needs to be aware that the software is here and I try a lot to put the word out there on conferences, wikipedia, sourceforge, freshmeat, ohloh etc.. But let me raise this question to everybody involved with marketing software for no cost: How do you do it?

Why can't my masters thesis be more like my Open Source project?

2008-10-17T17:08:00.006+02:00

Being a hard working student I sometimes have to question (and from time to time applaude) the practices of academia and the tools that we use to foster innovation, creativity and knowledge-sharing. My masters thesis subject is concerned with the development methods of open source communities and companies that try to enable community-based development of their products. Yesterday I was considering this quote:

"In the cathedral-builder view of programming, bugs and development problems are tricky, insidious, deep phenomena. It takes months of scrutiny by a dedicated few to develop confidence that you've winkled them all out." - Eric S. Raymond - The Cathredral and The Bazaar

Looking aside from the fact that it deals with programming and not writing a paper (and trying to grow global awareness and knowledge on a specific topic) I thought to myself, that the cathedral-builder process is pretty similar to the process of writing a masters thesis. There are pretty strict guidelines to follow, a lot of scrutiny involved and planning by a dedicated few - in my case myself and my supervisor.

So is there a room for another way, a more open process with distributed peers, continous redesign, short release-spans etc. Obviously there are things like wikipedia that provide this for topics of interest to the general public, but needless to say science projects often go beyond that level of information and have to deal with experiments, not just facts of life such as those in an encyclopedia.

Also there's the issue of academia culture. Ego and elitism doubtlessly play a big part in maintaining a high degree of secrecy and closeness of scientific endeavor. I'd love to see scientists work in a community-enabling fashion and then I'd love to contribute to one of those (or create my own for my masters these). Let's for example try something like this out and we'll be well under way:

"Bugtrackers" for all the items that needs investigations
"Source control management" and versioning systems for revisions of the paper(s)
Chatty mailing lists for peer review and discussions
"Continous integration" for managing/matching references and terms within the paper
Free availability to all underlying data, not just the published parts

The beauties of this would be similar to the beauties of open source. And particularly in academia there's a strong need to be able to track down who's done what and source control management and reference-management would greatly improve on that account. For evaluators it would be possible to see the actual changes made by each student in group work, for group-working students it would be possible to track the actual changes to the project (as opposed to having to read it all over again everytime you exchange documents)... Perhaps a more "open" science would be just what we need?

A day of releases!

2008-10-13T20:39:00.003+02:00

I saw this morning that the new OpenOffice 3 is out! Congratulations to the OO.o crew, I'm enjoying using your product for my upcoming masters thesis :)

Today is also the day that DataCleaner 1.5 "snapshot" has been released. Here's the press release:

As we're moving steadily along towards the release of DataCleaner 1.5 we are fixing a few bugs and enhancing a lot of features. This leads to the desire to release our work since practically nothing has undergone changes that could destabilize the application since the 1.4 release. So today we're releasing DataCleaner 1.5 "snapshot". This also marks the first release under our new LGPL license.

Here are the changes from 1.4 so far:
Change of license to LGPL.
New profile: Date mask matcher.
New profile: Regex matcher.
More file types supported (.dat, .txt)
XML file support improved (.xml)
Although this is in principle a development/beta release, we feel that it would be worth working with for most of your profiling needs. So... Go on, download it, tell us what you think and we'll see you around!

I hope you all enjoy the new version of DataCleaner!

Fast as lightning!

2008-10-04T15:20:00.003+02:00

Whoa! I just got through my Lightning Speak about eobjects.org and DataCleaner at the Open Source Days '08 conference about an hour ago. It was a great experience - very fun and kinda stressing (in the good, "get to the point"-kinda way) to have an alarm clock counting down for your 15 minutes of fame!

And in deed my presentation was very closely to the point. I wanted to tell people about the great creative projects at eobjects.org and especially about DataCleaner and the MetaModel project, which I dubbed a "derivative" project. My speak also quickly sketched the domain of data quality and people where nodding when I concluded that they all should download DataCleaner and give their datasources a quick profile the next time they worked on their projects.

You can download my slides here: http://eobjects.org/resources/download/opensourcedays.pdf

Unfortunately the format of the Ligthning Speak didn't allow for much time for comments and questions from the audience, but I hope and think that they had a good time!

Presenting DataCleaner at Open Source Days '08

2008-09-08T15:31:00.005+02:00

The Open Source Days conference in Copenhagen have been growing steadily for the last couple of years. Some of you may know it by it's former name, LinuxForum, though. This year the conference is featuring a new concept: Lightning speaks. Lightning speaks are less comprehensive speaks by less profiled speakers that are not necessarily based upon a paper. This doesn't mean that the speaks will be less relevant, on the contrary I think we will see a lot of hyper-interesting "up and coming" speakers delivering early keynotes of major topics to come.

As you may have guessed by my enthusiasm about the Lightning Speak concept, I am speaking at this years Open Source Days conference on Sunday the 4th of October at 14:00! The topic will be DataCleaner and how the eobjects.dk (and now eobjects.org) community have delivered one of the leading open source Data Quality solutions within less than a year! Here's the official speak statement:

DataCleaner is the most advanced open source data quality solution available. You can use DataCleaner to profile, validate and compare your data in an intuitive graphical environment. The application is compliant with lots of datasources such as JDBC databases, CSV files, Excel spreadsheets, OpenOffice.org database-files and xml files. Among the interesting features are identification of string patterns, value distributions, dictionary lookups, javascripted validation rules, regular expression validation along with traditional metrics used for data profiling and analysis. Come to this lightning speak to listen to Kasper Sørensen, the founder of eobjects.dk and main developer of DataCleaner who will demonstrate DataCleaner and give a quick overview of what's going on with open source data quality.

I hope that if you're joining the conference you'll come and hear me out. And if you're not planning to come - then please reconsider ;) I'm very much looking forward to showing of our great product and I think it will be a lovely experience to get some more focus on data quality as well.

We're moving eobjects.dk to a new server

2008-08-26T16:23:00.005+02:00

Hello everybody. This is a practical announcement ...

We're in the process of moving eobjects.dk to a new server.

Please return shortly to access the new and improved eobjects.dk!

Update

We're finally finished, but the IP address change will need some hours to cascade worldwide. If you're still being redirected to this page it's because the DNS changes haven't set in yet. The new eobjects.dk website is online! Impatient people who are suffering from the slow DNS cascades can also access the website directly by it's IP address (minor glitches is to be expected this way though).

Update

By now, all DNS changes should be complete så go ahead
enjoy the new eobjects.dk website

Development/snapshot release of DataCleaner 1.4

2008-08-25T21:15:00.003+02:00

We've released a development/snapshot release of DataCleaner 1.4 in order to get early reactions for all the improvements and new features as well as supporting our users with up to date functionality. In my own opinion the development release is just as stable and "safe to use" as 1.3, but of course it lacks a bit of the manual testing that we put into the real releases.

You can download the development release at our sourceforge download site.

Here's a short list of fixes since DataCleaner 1.3:

Better memory handling and garbage collection
Reference columns in drill-to-details windows
Better error handling when loading schemas
Quoting of string values in visualized tables (in order to distinguish empty strings and white spaces)
New profile: Value Distribution, which is an improved version of the Repeated Values profile. The Value Distribution profile has an option to configure the top/bottom n values to include in the result.
Better control of profile result column width.
Bugfix: Copy to clipboard functions now work properly.
Bugfix: Scrollbars added to visualized tables.

New eobjects.dk website

2008-08-19T18:03:00.004+02:00

Hi everybody,

I'm anticipating the release of a new eobjects.dk website design. The website will be launched pretty soon I hope - we're doing it as a part of a general server move. The move is to the laboratory at Copenhagen Business School (CBS) called Business of Open Source Software and Standards (BOSSS). With BOSSS we'll have a much better bandwidth and a better performing server as well as better physical security conditions.

The new website will be based on trac 0.11 (currently we use 0.10) and will feature a lot of improvements for visitors, users and contributors:

Better theming engine has enabled us to use a more flexible website design with wiki pages appearing as menu items.
A whole new news page which will be used to perform announcements on the progress of our projects.
A lot of other improvements caused by the trac upgrade.

Here's a little screenshot of the new webpage design (work in progress):

Any comments are welcome!

Considering MetaModel functions

2008-08-03T23:45:00.004+02:00

This blog entry could just as well have been a feature request but I'm going to kick-start it with a couple of thoughts I have for one of the crucial improvements to MetaModel that I've been dreaming about.

The last couple of weeks have brought considerable interest in MetaModel, largely thanks to articles posted on the server side and infoq. It's been great to get the message out and it's also sparked a lot of great ideas from users/evaluators on the discussion forum. A couple of them have been requests that we build more advanced SELECT items into the query model. In this post I'm going to discuss type-casting and extraction functions and how they can be made possible using the new IQueryRewriter interface.

The idea about query rewriting had been going on for some time, of course inspired by Hibernate's dialects. The thing was though, that for a start I wanted to skip dialect handling completely in order to get to know how far one could actually go without having to do any "hacking" in SQL. It worked out quite well but now that we need to incorporate more advanced, non-standardised features, we will of course need to be able to manipulate with the standard output. This is what the query rewriter is for, and in particular the AbstractQueryRewriter helps you do. I've made my first query rewriting "hack" today - using the TOP function for limiting the result set size, which is (as far as I know) only available in MySQL.

What we need to do now is expand the Query model API. We need to incorporate type casting. My thoughts are:

We must take an interface-first approach - how would one most appropriately like to type-cast a select item in a query? I'm thinking that we should add a "castAs(ColumnType type)" method on SelectItem.
Because not all of the ColumnType's are supported by all databases we should consider making a more abstract type enum. Something that will only contain a couple of more basic types like String, Integer, Decimal, Date, Boolean.
We should use the query rewriting approach to generate the actual SQL cast syntax. Some databases use the CAST(x AS y) function, others use special-purpose functions like TO_NUMBER(x).

Another feature that I want to include in MetaModel is functions for calculating or extracting something on behalf of a column. Let's take for example the YEAR(x) function (or in some databases the EXTRACT(YEAR FROM x) function).

One would initially just think that we should add this function to the FunctionType enum and then take it from there. But actually it's quite a different type of function. While SUM, COUNT etc. are aggregate functions, the YEAR function is a single-value function, ie you can't call YEAR on a set of values.
Therefore we should consider a rename of FunctionType to AggregateFunction and create a new enum, CalculationFunction (or maybe we can come up with a better name?)
We can use the same approach as before (query rewriting) to handle different dialects, but we need to make sure that we pick function names that are widely accepted and understandable to the user. Personally I prefer YEAR(x) over EXTRACT(YEAR FROM x) as the syntax is clearer and there are no constants inside the parameter, which is more java-ish. The downside is that we will then also need a MONTH(x), DAY(x) etc. function but that's not a biggie I think.

One last note - we should also consider if it's reasonable to keep using enums. Maybe we should switch to interfaces (and constants in the interface to ensure no API changes) for the sake of extensibility.

DataCleaner 1.3 and MetaModel 1.0.3 released

2008-07-27T21:36:00.002+02:00

Today is the day that we've released DataCleaner 1.3 and MetaModel 1.0.3. Here's a summary of changes:

DataCleaner 1.3
This release contains many new features and improvements.

You can save and load work in the Profiler and Validator.
Support for working with data quality in XML files.
Functionality to swap/dice columns and rows in profiler result tables.
A more modular and plugin-friendly UI.
Various bugfixes, optimizations, and wider database support.

MetaModel 1.0.3
A minor changes release. Only new feature is the capability to parse and map XML files to the schema-based model. Also the release marks some improvements to documentation and minor bugfixes.

What to remember when presenting OS BI

2008-06-27T10:13:00.006+02:00

As I wrote about in my previous blog post I went to Danish IT the other day to talk about Open Source BI. I've spent a lot of time the last two days contemplating on the presentation and how the audience percievede OS BI as pretty immature and insecure... Somehow some of my points about how one could take advantage of Open Source instead of seeing it as a threat and a risk got lost in the mix. I will try and sum up some of the thoughts that I've had on what went wrong and how to present BI products for people who a unfamiliar with Open Source.

First of the presentation wasn't really a "sales meeting", so I took on the academic perspective and showed the audience a broad view of the OS BI arena, including two alternatives for each product group; databases, ETL, reporting and OLAP. This was a really bad idea: Presenting the alternatives within OS was simply too much; instead of being impressed that there was a volume for rivalry between and within the OS communities this was thought of as a bad thing - fragmentation, instability etc. So instead, just show the business people a single suite of products, a silver bullet, even though we all know that this does not exist (neither does it in the commercial world, which is a good thing).
Taking a too feature-driven product-focus was not the best idea. Sure you should point out all the good features of OS BI products but for my demonstrations I focused on the great computational power and advanced features instead of showing them some nice user interfaces. In selecting what to present I would definately recommend to use more user friendly products like eclipse BIRT, Talend, OpenLaszlo, etc. Instead I showed them the Pentaho suite which for a large part has a somewhat boring theme.
Remember to get them a list of companies that have already undergone OS BI initiatives. I forgot this completely and it was a big mistake. I hope for the attendees that they folllowed my advise to go check it out themselves at Pentaho's and JasperSoft's websites.
Stress that the participation thing is not something that nescesarily requires coding-skills. Show them how support forums work and that the communication part is just as important. Without useful information the developers are lost and will probably not focus on the exact same thing as the customers do.
Get more authority into the room. Hard to admit I have a hard time getting authority in a room of business people because I'm more of the academic type. So bring along colleagues and trustees to help convince the audience that your message is legitimate. Also this will help potential customers understand that you're not the only one in the business who cares about OS BI - it will tell them that there are others and certainly enough to get started with consulting, training and recruiting.
Show them the numbers of Open Source. David Wheelers article should give you some good starting points. Address the fact that there are plenty of developers and tell them what motivates them (these points only seemed to kick in when I told them about my own OS projects so I must have not made it clear from the beginning)... Learning, reputation, ideology and "real" work/sponsored developers.

On the positive note however (seeing that the points above may leave you with the impression that the meeting went all wrong, which is not the case) there was several positive experiences. It helped a lot to show the actual community webpages and show how the development process worked. I did this as my last topic and it should have been in there earlier to help the audience get a feeling of the underlying ideas. Also, supplementing with notes on related Open Source products helps people understand that this is not just a "crazy idea" that popped up for BI. Show them JBoss, Apache, Linux, OpenOffice, Eclipse, Mozilla etc. And then tell them about integration, SOA etc. which are architectual challenges that are very suitably overcome using Open Source based solutions.

Addressing Open Source BI and data quality at Danish IT

2008-06-19T21:55:00.005+02:00

I've been offered to address the Danish IT (Dansk IT) networking group on Business Intelligence next wednesday. I'll be concentrating on talking about Open Source business models, pitfalls and opportunities and an overview of the Open Source BI market including demos of various tools - including DataCleaner of course. I'm thinking this is a great opportunity to get people involved with OS BI - an area that has been somewhat overlooked, at least in Denmark, I think.

Update: Just got home from the networking group and it was a very interesting day sparked with lots of discussions and perspectives on BI products. I can't say that everybody was convinced with going Open Source for their BI solutions but they definitely got an impression of what goes on and most people there was very interested in DataCleaner, perhaps because of the few implications it has on the rest of the BI portfolio to apply our data quality solution.

Update: You can now download my slides about Open Source Business Intelligence and please let me know what you think.

Report on DataCleaner development process

2008-06-15T09:33:00.003+02:00

As some of you may know DataCleaner started as an academic project for me, investigating how Open Source projects are established, managed and developed. I've been waiting for a long time for the evaluation of the project but yesterday I finally got the results, and I'm proud to announce that I got the top-grade for the assignment! (12 in the danish grading system, which spans from -2 to 12 - I'll have to blog about the grading system some time, it's hilarious).

The project received notable credits for the explorative style of development and this is something that I'm very proud to keep on practicing. I'm publishing the report for free download here, but I'm affraid it's in Danish, so if you don't understand it you'll have to ... learn Danish ;-)

Download 'Udvikling og styring af Open Source projekter' here (in Danish)

I would appreciate any kind of feedback on my research and I don't mind critics now that I have the acknowledgment of Copenhagen Business School, heh.

Fluent interfaces in MetaModel

2008-06-14T22:49:00.002+02:00

Just spent a couple of hours making the Query class of MetaModel implement fluent interfaces. Damn this syntax looks great:

new Query().select(myColumn).selectCount().from(myTable).where(myColumn, OperatorType.EQUALS_TO, "foobar").groupBy(anotherColumn).orderBy(myColumn);

This was one of the last TODO's with MetaModel in this round so I think we're going to release version 1.0 pretty soon! Stay tuned.

DataCleaner looks...

2008-06-11T23:02:00.005+02:00

Been working a couple of days on a new great look for DataCleaner, check it out:

I'm starting to get real excited about releasing version 1.2, it'll be a radical improvement in terms of both visual experience and functionality I think!

How to process millions of resultset rows in java

2008-06-01T23:10:00.001+02:00

I'm so excited, since I just think that we've solved a very common problem in java applications that have to deal with huge amounts of data. Here's the trouble:

Even though the JDBC spec. defines a way to specify the fetch size when executing queries, some drivers do not implement this feature, which means your program will run out of memory if you query eg. a couple of millions of records.
Even if your driver works as it is supposed to (that would be a reasonable assumption in most cases) there's still no effective way to optimize the computation of the many records by multithreading since the data is streamed through a single connection.

Because of the power of the MetaModel schema and query model we've been able to create a generic mechanism for splitting up a query into other queries that will yield less rows but the same collective result. The way we do this is by identifying attributes that can be used to filter in WHERE clauses, for example:

Consider we want to split up the query: "SELECT name, email FROM persons"
We will investigate the persons table and find columns that can be used to split the total resultset. We might find a reasonable age-column for this, so the query could be split to:

SELECT name, email FROM persons WHERE age < 30 OR age IS NULL
SELECT name, email FROM persons WHERE age > 30 OR age = 30

Depending on the desired size of the partial queries we will split up by finding further columns or by defining finer intervals to split by. Here's how it works:

DataContext dc = ...
Query q = ...

QuerySplitter qs = new QuerySplitter(dc, q);
List<Query> queries = qs.splitQueries();

I'd love to know what you all think of this? Personally I think it's a lovely way to optimize memory consumption and it offers new ways to utilize grid computing by distributing partial queries to diffent nodes in the grid to do remote processing. Also a lot of databases (MySQL for example) only dedicates a single thread per query - so by splitting the queries one could further optimize multithreading on the database.

Community choise awards

2008-05-15T23:03:00.003+02:00

I just signed DataCleaner up for sourceforge's community choise awards 08 :) I definately don't count on us winning anything though... Not because we don't deserve it (we do) but because noone knows about us (yet). Anyhoot, you can all help change that - go nominate us for "best new project" and "best project for the enterprise"!

Click the banner to the right to give us your vote!

Drill-to-detail in profiling results

2008-05-14T21:10:00.008+02:00

I've just added a new feature to DataCleaner that I'm very happy with. Now when you see a profiling result you can interact with it and drill to details on measures of interest to gain insight into how those measures are composed. Consider the screenshot below (sorry for the ugly window borders, I'm running linux here and we're still trying to tweak the UI to look sweet with the jgoodies look'n'feel).

What you're seeing is that the pattern finder identified a couple of patterns for the ADDRESSLINE2 column and I've just clicked the "aaaaa 999" pattern to find out which value where categorized to fit this pattern. This queries (drills to details) the datastore for these columns. Similarly I could have wanted to find out which values yielded the "max chars", "min chars" or other measures in the profile result matrix above. If I do that it will similarly query the values that originated the measures.

So how is this possible? Thanks to MetaModel it's quite easy to model such data explorations! Even though some of these queries cannot be transformed into SQL it is possible to post-process them using row filters that filter out only the correct rows based on an initial query. Let's look at an example, how to drill to details on the "max words" measure in String analysis:

//For each column result...
MatrixValue mv = matrixValues[7]; // 7 is the index of the "max words" measure

//Construct a query that get's as close as possible to the desired result
Query q = new Query();
q.addSelect(column);
q.addFrom(column.getTable())
q.addGroupBy(column).addSelect(new SelectItem(FunctionType.COUNT,"*",null));

//Set the query as the source of the details
mv.setDetailSource(q);

//Set a post processing row filter to include only those
//rows that have the right number of words
//(something that cannot be expressed in a query)
mv.addDetailRowFilter(new IRowFilter() {
public boolean accept(Row row) {
Object value = row.getValue(column);
if (value != null &&
new StringTokenizer(value.toString()).countTokens() == numWords) {
return true;
}
return false;
}
});

The code above is a slight rewrite of the real code for String analysis and it shows how easy it is (and ought to be) to attach a query to a value in a profile result! Drill-to-detail features are now a common thing in DataCleaner and in my oppinion it leverages the use of the profiler functionality to a new level.

That was easy

2008-05-07T23:49:00.003+02:00

This week's work...
MetaModel is now a part of the DataCleaner stack!

Demonstrating the mutable query

2008-05-03T12:25:00.012+02:00

Having wanted for a long time to be able to manipulate queries in an object-oriented manner is not something that I think has been just my personal ambition, but a strong barrier for a lot of data-centric applications out there. Considering the many dialects of SQL it is also striking how few attempts there has been to unify these dialects using a common meta-layer. Sure there are very succesfull (and good!) ORM frameworks like Hibernate and JDO that have built in dialect-translation but when you think about it they actually apply to a very limited scenario where you know in advance how the database is structured and what objects you want to store the content of the database. Consider instead a model of a database that is not a mapping to domain objects, but more an object-oriented wrapper for the database since the database is the domain. The advantages of an object-oriented domain model for the database is obvious, here are a couple of pointers;

Traversing a database (schemas, tables, columns, relationships etc.) becomes as easy as it is to traverse any other domain model.
Building and manipulating queries can be a continous and type-safe process. This gives us the opportunity to do meaningful fault-tolerant query-modifications in order to recover from database-insufficiencies and to enterpret the query not only as SQL, but as a generic query built for non-SQL datastores.
It is possible now to mimic a database with other data technologies, for examples flat/csv files, excel spreadsheets, web services or hardcoded datastores.
Building systems to support the database structure (for example metadata applications) becomes possible without extensive JDBC hacking.

Anyhoot, look no further, because we have invested considerable time into these issues by developing eobjects MetaModel. Let me show you how you build queries using the MetaModel framework in a totally type-safe manner!

java.sql.Connection connection = ... //Obtain a JDBC connection
DataContext dataContext = DataContextFactory.createJdbcDataContext(connection);
Schema[] schemas = dataContext.getSchemas();
Table customerTable = schemas[0].getTableByName("customers");

Query q = new Query();
q.addFrom(customerTable);
q.addSelect(customerTable.getColumns());
DataSet dataSet = dataContext.executeQuery(q);
while (dataSet.next()) {
Row row = dataSet.getRow();
System.out.println(row.toString());
}

Of course in this example a couple of hardcoded values are entered such as the "customers" literal, but I could have just as well traversed the schema by going through every table without any hard references.

So I guess the most striking question is: Doesn't it cause some ineffeciencies to generalize queries like this? And can I still execute my custom SQL-string query? The answer is "no, because that goes against the whole idea of MetaModel". You shouldn't be able to do anything that is not portable to other datastores. Then you could just as well do it "the old" JDBC way and of course we don't prohibit you from doing so. But let's instead take a look at how to do regular (portable and standards-compliant) SQL queries in MetaModel:

Query q = new Query();
q.addFrom(new FromItem(schema.getTable("products")).setAlias("p");
q.addSelect(schema.getTable("products").getColumn("product_type"));
q.addGroupBy(schema.getTable("products").getColumn("product_type"));

System.out.println(q.toString());
//yields: "SELECT p.product_type FROM products p GROUP BY p.productType"

Notice here that there are multiple overloaded addFrom() methods in Query. In the first example we added a table as the argument, in this next example we used a FromItem and gave it an alias. The same principle applies to the other parts of the query, lets see it by adding an aggregate function to our query:

Column priceColumn = schema.getTable("products").getColumn("price");
q.addSelect(new SelectItem(FunctionType.SUM, priceColumn);
System.out.println(q.toString());
//yields: "SELECT p.product_type, SUM(p.price) FROM products p GROUP BY p.productType"

Okay, it seems pretty straight forward eh? That's because it is. So get started and use MetaModel. Here's some more examples for ya so you can see how easy it is to build even complex queries:

Column marketYearColumn = schema.getTable("products").getColumn("market_year");
SelectItem yearSelect = new SelectItem(marketYearColumn);

q.addWhere(new FilterItem(yearSelect , OperatorType.HIGHER_THAN, 2004));
System.out.println(q.toString());
//yields: "SELECT p.product_type, SUM(p.price) FROM products p WHERE p.market_year > 2004 GROUP BY p.productType"

q.addOrderBy(new OrderByItem(q.getSelectClause().getItem(0),false));
System.out.println(q.toString());
//yields: "SELECT p.product_type, SUM(p.price) FROM products p WHERE p.market_year > 2004 GROUP BY p.productType ORDER BY p.product_type"

A meta domain model

2008-04-23T16:16:00.004+02:00

In the field of object-orientation it has for some years been common to talk about modelling the domain; to create domain models. This makes perfect sense if you're a car dealer or a pet shop, ie. a business with a specific domain. But what about those development projects that does not apply to a specific domain, but to all kinds of domains?

This was the situation when I decided to start the DataCleaner project some months ago. What I needed here was a domain model concentrated with data sources, their structures and the data they contained. I searched the Open Source offering on this and to my surprise didn't find any solid attempts to do this. Sure there are object-relational-frameworks like Hibernate which enables you to create your own domain model objects and "easily" map them to a database, but it's not possible to map the database itself to objects that represent the structure of the database.

Initially I just started making such a model myself - I mean, how hard could it be? The result was the DataCleaner metadata & data layer, which works fine in DataCleaner, but wasn't quite developed for reuse in other applications. So now I've started creating a new project, which I'm calling eobjects.dk MetaModel. The MetaModel project takes of where the DataCleaner metadata & data layer stops. We have classes like Schema, Table, Column etc. but we will try to remove (encapsulate) any tie to JDBC, because if you want to see a messy API (or more correctly: messy implementations), then look at JDBC. We will also use the MetaModel to take advantage of "new" language constructions in java like enumerations, generics etc.

Here are some of the plans for MetaModel:

Schema model: Schema (and unification of the very ambigious Catalog and Schema terminilogy in JDBC), Table, Column, ColumnType (enum), TableType (enum) etc.
Query model: Query, SelectClause, FromClause, WhereClause, GroupByClause, HavingClause, OrderByClause etc.
Data model: Dataset (with Streaming and keep-in-memory options), Row

My hope for this is to make an API which makes it possible to interact with your database in a type-safe manner and avoid query problems, hardcoded literals in the code etc.

Querying data with DataCleaner-core

2008-04-19T14:57:00.012+02:00

I promised the other day that I would return on the topic of using the metadata & data layer of DataCleaner-core. So here's what we'll do;

1) Open up a connection to the database (this is plain old JDBC). Here's how to do it with Derby, but any database could do:

Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
Connection con = DriverManager.getConnection("jdbc:derby:my_database;");

2) Let's create a schema object for an easier, object-oriented way of accessing the data.

JdbcSchemaFactory schemaFactory = new JdbcSchemaFactory();
ISchema[] schemas = schemaFactory.getSchemas(con);

Note that by default the JDBC schema factory only retrieves relations from the database of type "TABLE". You could in some situations though wish to broaden this restriction, for example to enable views:

JdbcSchemaFactory schemaFactory = new JdbcSchemaFactory();
schemaFactory.setTableTypes(new String[] {"TABLE","VIEW"});
ISchema[] schemas = schemaFactory.getSchemas(con);

3) Let's try exploring our metadata, consisting of schemas, tables and columns.

ITable productTable = schemas[0].getTableByName("Products");
IColumn[] productColumns = productTable.getColumns();
IColumn productCodeColumn = productTable.getColumnByName("product_code");

//This next int represents one of the constants in java.sql.Types.
int productCodeType = productCodeColumn.getColumnType();
boolean isProductCodeLiteral = MetadataHelper.isLiteral(productCodeType);
boolean isProductCodeNumber = MetadataHelper.isNumber(productCodeType);

4) Time to make a query or two. Let's start off by just querying the whole table and then querying two specific columns.

JdbcDataFactory dataFactory = new JdbcDataFactory();
IData someDataThatWeWillDiscard = dataFactory.getData(con, productTable);
IData data = dataFactory.getData(con, productTable, productCodeColumn, anotherColumn);
while (data.next()) {

IRow row = data.getRow();
int count = data.getCount();
System.out.println("Observed " + count + " rows with product code: " + row.getValue(productCodeColumn));

}

Notice the IData.getCount() which is crucial to understand. The data factory will try to generate a group by query to optimize the load on traffic between database server and client. Sometimes this is not possible though (for example for TEXT types in Derby, where GROUP BY is not allowed). The getCount() method returns how many occurances there are of this distinct combination of values, represented by the IRow interface. So make sure to always check the count, maybe there are less rows in the result than in the actual database, because the results have been compressed!

Observe in general how strongly typed an API this is. In other data-oriented API's one would have to type in the same column name several places (at least in the query and when iterating through the results) but with the DataCleaner-core metadata and data layer we get a completely object oriented and type safe way to do this. The amazing thing about this API is also that we could have just as well done the same thing with flat files or other data source types.

Getting uispec4j to run in Hudson on a headless linuxbox

2008-04-18T12:13:00.009+02:00

I've been fiddling around with this problem for some time now and I finally got all the pieces together so I guess I'd better share my newfound knowledge on these obscure topics that I hope I'll never have to encounter again.

It all started with a new fine and dandy testframework called uispec4j, that we wanted to use for DataCleaners GUI. Uispec4j is supposedly "Java GUI testing made simple" and so they caught our attention because the code coverage of DataCleaner GUI was not that impressive (yet, if you read this blog post and a lot of time has passed, it may hopefully be looking better).

So we started of by creating some neat unittests for DataCleaner GUI, using uispec4j. Hurray. They worked fine and dandy on our Windows development machines so we uploaded them to the repository and into the Continous Integration loop. This is where hell broke loose.

First off, our Continous Integration server was headless (ie. no screens, monitors, displays, whatever, just a remote console). Surely this wouldn't do because uispec4j requires a window manager to use for emulating the Java GUI. Fair enough, I installed X with the Xfce window manager:

apt-get install xorg xfce4

Then came the next problem. When starting X a fatal error occurred, telling me that no screens where installed. That seems fairly reasonable, but what the heck should I do about it? I decided to install a VNC server to host a remote screen. This would hopefully rid me of my troubles, since I didn't have the (physical) room for installing a monitor for the damn thing.

apt-get install vncserver

After configuring the vncserver I tried running my tests... Next obstacle: Telling Java which screen to use. This required to set the DISPLAY environment variable in /etc/profile:

export DISPLAY=:1

Now came the time for some mind-bobbling uispec4j errors. I found out that uispec4j only works with Motif on linux so you had to append "-Dawt.toolkit=sun.awt.motif.MToolkit" to your commandline like this:

mvn install -Dawt.toolkit=sun.awt.motif.MToolkit

every time you need to build the damn thing. Sigh, this wasn't something that my Continous Integration system (Hudson) was built for so I started to edit various batchscripts to see if I appended the damn "-Dawt.toolkit=sun.awt.motif.MToolkit" parameter to my containers startup script it would work, but no. Instead I found out that you could set the MAVEN_OPTS environment variable, so I did that in /etc/profile:

export MAVEN_OPTS="-Dawt.toolkit=sun.awt.motif.MToolkit"

But that didn't work either because Hudson doesn't comply with the damn thing :( I tried to set that "awt.toolkit" system property using some static initializers (which I generally think is a poor, poor, poor thing to do in Java in general), but guess what? Uispec4j is filled with static initializers as well, so that brought me no guarantees whether or not I was the first static initializer run. (edit: Apparently I might be wrong in this claim about uispec4j, check out the comments for more details).

Finally I got a new version of Hudson that had a per-project configuration of MAVEN_OPTS and that did the job. The last issue was actually a JVM issue. I had to change the runtime user of my J2EE container to be the same user that hosts the VNC server instance. If you try to access another users desktop, the JVM turns fatal. So don't touch my desktop or you'll get your fingers burnt!

Ah and a last thing about GUI testing: Make sure to set the Locale in your junit setUp methods or else the unittests won't be portable between computers if they have different languages and you assert on the labels of UI elements.

I once heard a very wise colleague and fellow developer say:

"You should test functionality and domain models through unittesting and test UI through UI!"

...

Maven-ing your way around DataCleaner

2008-04-17T08:38:00.003+02:00

There seems to be quite some frustrations for old ANT-users switching to use Maven so I thought I would make a small post about the main differences and various hacks that are useful to know as a Maven user. The good thing about ANT is that you can always hack your way around a problem and it's quite easy to find the problems that are stopping you. The bad thing is of course that the build-files seem to grow enormously and that you have to enforce your project infrastructure with some kind of common JAR-download area like a FTP or something similar. In contrast Maven focuses not on the build as a process, but more on the content of the build, because in 99% of the times the process of building a java project is pretty much the same, so why not omit the "how" completely and only focus on the "what" of your build? This "what" is configured in the pom.xml file!

Admitted, that was not my primary reason for choosing Maven! :) The thing that won me over was of course the dependency handling system which I really love and loathe a bit at the same time. What you need to be aware of about the dependencies is this:

Maven automagically creates a local repository for all the JARs you use in your projects.

There's also a central repository where maven will download the JARs from, if they are not found in the local one.

If you are working offline or behind a proxy and you need a new JAR you're bound to mess this up :( When Maven can't find it's JARs in the central repository or locally it will blacklist it!

You can however delete the blacklisting by removing (part of) the local repository, it is found in ~/.m2/repository...

windows: C:/Documents and Settings/[username]/.m2/repository

or linux: /home/[username]/.m2/repository).

OK, so that was the background-knowledge you had to know - now for some of the build goals. The mostly used maven goal is "install", oftenly prefixed with "clean", like this:

mvn clean install

The install goal will build the project, run the unittests, verify that everything worked and then install the resulting JAR/WAR/Whatever into your local repository. This means that you can then use the project as a dependency to another project, smart eh?
And now for some other commonly goals:

mvn site

Create a nifty project site with all sorts of nice information and reports (javadoc, unittests, codecoverage etc. depending on your configuration).

mvn install -Dmaven.test.skip=true

Ah, the "skip test" parameter. I spent a long time figuring that one out. This is handy if you're working with several projects at the same time and you've (consciously) broken the build and want to keep on using the dependency.

mvn jetty:run

My new DataCleaner-webmonitor favourite. This will bring up a Jetty container with DataCleaner-webmonitor running on localhost. This of course requires a little configuration in pom.xml, I'm sure you can figure it out, just find the plugin-elements that has to do with jetty :)

Traversing schemas with DataCleaner-core

2008-04-16T19:03:00.005+02:00

Being a new framework a lot of you guys probably wonder how to use DataCleaner as a Java API. Unlike a lot of other tools around that I've seen in the Business Intelligence domain DataCleaner was built bottom-up from a developers perspective and the User Interface was added on afterward so to use the DataCleaner-core API can be a real pleasure ... (so much for user-orientation, I'll have to elaborate on that another time)

Let's take a look at how to get the data of a CSV file. This will give us the data of the file using the same interfaces as JDBC-databases, excel-files and possibly other data sources.

ISchemaFactory schemaFactory = new CsvSchemaFactory();
File file = new File("my_file.csv");
ISchema[] schemas = schemaFactory.getSchemas(file);

Or in the case of a JDBC connection:

ISchemaFactory schemaFactory = new JdbcSchemaFactory();
Connection connection = DriverManager.getConnection("jdbc:my:database://localhost/foobar");
ISchema[] schemas = schemaFactory.getSchemas(connection);

The schemas retrievede here can be accessed in a very natural way and with a strong domain model, unlike traversing schemas in JDBC. Here's some examples:

ITable[] tables = schemas[0].getTables();
IColumn[] columns = tables[0].getColumns();
String columnName = columns[0].getName();

Handling the schemas this way serves an obvious purpose. We can now design our profiles, our validation rules etc. in a very uniform way that can be reused accross data source types. We'll talk about that next time :)