tag:blogger.com,1999:blog-13016431471760339272024-03-13T11:03:19.780+01:00kasper's sourcerandom thoughts, examples, tutorials and ideas on open source software, data quality, data warehousing, java programming, querying and more...Kasper Sørensenhttp://www.blogger.com/profile/03281134453771284315noreply@blogger.comBlogger97125tag:blogger.com,1999:blog-1301643147176033927.post-46038152754711208572015-02-27T16:21:00.001+01:002017-11-06T05:39:38.550+01:00DataCleaner 4 - a high-quality user experience to provide high-quality data<div dir="ltr" style="text-align: left;" trbidi="on">
It's been a long time since I've blogged about <a href="http://datacleaner.github.io/">DataCleaner</a> and that's partly because I've been busy on other projects - partly because the thing that I'm going to blog about now has been a very long time in the making. We're right now in the final stages of building DataCleaner version 4 which is (in my opinion) going to be a pretty disruptive move for the tool. And especially for the tools usability and user experience.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-ZPpXMAMSlrI/VPCJKqhY9VI/AAAAAAAAIco/E510EYn54Dg/s1600/welcome.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="353" src="https://4.bp.blogspot.com/-ZPpXMAMSlrI/VPCJKqhY9VI/AAAAAAAAIco/E510EYn54Dg/s1600/welcome.png" width="400" /></a></div>
<br />
The UI of DataCleaner is changing in many ways. The moment you start DataCleaner 4 you will see that the initial start screen has been simplified, beautified and is in general a lot less busy than previous versions. We also focus on what you want to have done by offering quick start options such as answering questions "Are my address correct and up-to-date?" or more openly "What can you tell me about my data?". More such options are going to be added by the way ...<br />
<br />
Registering and selecting your data in DataCleaner 4 is also a whole lot easier.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-l6BQY30S-30/VPCKNpb_LPI/AAAAAAAAIcw/J63fKW3chBk/s1600/select_ds.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="291" src="https://3.bp.blogspot.com/-l6BQY30S-30/VPCKNpb_LPI/AAAAAAAAIcw/J63fKW3chBk/s1600/select_ds.png" width="400" /></a></div>
<br />
When you start building your job, the way of working with it has undergone a drastic change... For the better! We've introduced a graph-based canvas which means that what you work with is a process flow that is in my opinion (and in fact <i>everyone </i>we've talked to about this) is a lot more intuitive and matches the mental model of our users.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-iRgVHazrab0/VPCKtRYKICI/AAAAAAAAIc4/i4-LpayILm4/s1600/job_building.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="291" src="https://1.bp.blogspot.com/-iRgVHazrab0/VPCKtRYKICI/AAAAAAAAIc4/i4-LpayILm4/s1600/job_building.png" width="400" /></a></div>
<br />
The components/functions that you want to apply to your job are positioned in the left-side tree and can now just be dragged on to the canvas. Draw lines between them and you start to design the data quality analysis job that you need. It's quite simple really.<br />
<br />
There's a bunch more we want to do of course. That's why it's not released. But for those curious minds, you can get it already as a <a href="http://datacleaner.github.io">early access download here</a>. I hope to get some good review/feedback remarks. Let us know how we're doing :-)</div>
Kasper Sørensenhttp://www.blogger.com/profile/03281134453771284315noreply@blogger.com4tag:blogger.com,1999:blog-1301643147176033927.post-19034336309424739532014-07-14T21:05:00.002+02:002014-07-14T21:10:24.973+02:00MetaModel 4.2 to offer 'Schema Inference'<div dir="ltr" style="text-align: left;" trbidi="on">
For the upcoming version 4.2 of <a href="http://metamodel.incubator.apache.org/">Apache MetaModel (incubating)</a> I have been working on adding a JSON module to the already quite broad data access library. The purpose of the new JSON module is to be able to read text files with JSON contents and present it as if it's a database you can explore and query. This presented (again) an issue in MetaModel:<br />
<b><br /></b>
<b>How do we present a queryable schema for a datastore which does not intrinsically have a schema?</b><br />
<br />
A couple of times (for instance in implementing MongoDB, CouchDB, HBase or XML modules) we have faced this issue, and answers have varied a little depending on the particular datastore. This time I didn't feel like creating another one-off strategy for building the schema. Rather it felt like a good time to introduce a common abstraction - called the 'schema builder' - which allows for several standard implementations as well as pluggable ways of building or infering the schema structure.<br />
<br />
The new version of MetaModel will thus have a SchemaBuilder interface which you can implement yourself to plug in a mechanism for building/defining the schema. Even better, there's a couple of quite useful standard implementations.<br />
<br />
To explain this I need some examples. Assume you have these documents in your JSON file:<br />
<br />
<pre class="prettyprint lang-js">{"name":"Kasper Sørensen", "type":"person", "country":"Denmark"}
{"name":"Apache MetaModel", "type":"project", "community":"Apache"}
{"name":"Java", "type":"language"}
{"name":"JavaScript", "type":"language"}
</pre>
<br />
Here we have 4 documents/rows containing various things. All have a <b>name</b> and a <b>type</b> field, but other fields such as <b>country </b>or <b>community </b>seems optional or situational.<br />
<br />
In many situations you would want to have a schema model containing a single table with all documents. That would be possible in two ways. Either as a columnized form (implemented via <b>SingleTableInferentialSchemaBuilder</b>):<br />
<br />
<table cellpadding="2" cellspacing="0" class="pretty-table">
<tbody>
<tr><th>name</th><th>type</th><th>country</th><th>community</th></tr>
<tr><td>Kasper Sørensen</td><td>person</td><td>Denmark</td><td></td></tr>
<tr><td>Apache MetaModel</td><td>project</td><td></td><td>Apache</td></tr>
<tr><td>Java</td><td>language</td><td></td><td></td></tr>
<tr><td>JavaScript</td><td>language</td><td></td><td></td></tr>
</tbody></table>
<br />
Or having a single column of type MAP (implemented via <b>SingleMapColumnSchemaBuilder</b>):<br />
<br />
<table cellpadding="2" cellspacing="0" class="pretty-table">
<tbody>
<tr><th>value</th></tr>
<tr><td>{name=Kasper Sørensen, type=person, country=Denmark}</td></tr>
<tr><td>{name=Apache MetaModel, type=project, community=Apache}</td></tr>
<tr><td>{name=Java, type=language}</td></tr>
<tr><td>{name=JavaScript, type=language}</td></tr>
</tbody></table>
<br />
The latter approach of course has the advantage that it allows for the same polymorphic behaviour as the JSON documents itself, but it doesn't allow for a great SQL-like query syntax like the first approach. The first approach needs to do an initial analysis to infer the structure based on observed documents in a sample.<br />
<br />
A third approach is to build multiple virtualized tables by splitting by distinct values of the "type" field. In our example, it seems that this "type" field is there to distinguish separate types of documents, so that means we can build tables like this (implemented via <b>MultiTableInferentialSchemaBuilder</b>):<br />
<br />
Table 'person'
<table cellpadding="2" cellspacing="0" class="pretty-table">
<tbody>
<tr><th>name</th><th>country</th></tr>
<tr><td>Kasper Sørensen</td><td>Denmark</td></tr>
</tbody></table>
<br /><br />
Table 'project'
<table cellpadding="2" cellspacing="0" class="pretty-table">
<tbody>
<tr><th>name</th><th>community</th></tr>
<tr><td>Apache MetaModel</td><td>Apache</td></tr>
</tbody></table>
<br /><br />
Table 'language'
<table cellpadding="2" cellspacing="0" class="pretty-table">
<tbody>
<tr><th>name</th></tr>
<tr><td>Java</td></tr>
<tr><td>JavaScript</td></tr>
</tbody></table>
<br/>
<br/>
As you can see, the standard-implementations of schema builder offer quite some flexibility. The feature is still new and can certainly be improved and made more elaborate. But I think this is a great addition to <a href="http://metamodel.incubator.apache.org/">MetaModel</a> - to be able to dynamically define and inject rules for accessing schema-less structures.
<br /></div>Kasper Sørensenhttp://www.blogger.com/profile/03281134453771284315noreply@blogger.com1tag:blogger.com,1999:blog-1301643147176033927.post-90385865903024375452014-06-09T09:42:00.000+02:002014-06-17T13:47:08.942+02:00Using Apache MetaModel for web applications, some experiences<div dir="ltr" style="text-align: left;" trbidi="on">
<p>Recently I’ve been involved in the development of two separate web applications, both built with <a href="http://metamodel.incubator.apache.org/">Apache MetaModel</a> as the data access library. In this blog I want to share some experiences of good and bad things about this experience.</p>
</div>
<h2>How we configure the webapp</h2>
<p>Before we look at what went good and what went bad, let’s first go quickly through the anatomy of the web apps. Both of them have both a "primary" database and some "periphery" databases or files for storage of purpose-specific items. We use MetaModel to access both types of databases, but obviously the demands are quite different.</p>
<p>To make the term "periphery" database understandable, let’s take an example: In one of the applications the user submits data which may contain country names. We have large catalog of known synonyms for country names (like UK, Great Britain, United Kingdom, England etc.) but every once in a while we are unable to match some input – that input is stored in a periphery CSV file so that we can analyze it once in a while and figure out if the input was garbage or if we’re missing some synonyms. Similarly other periphery databases may be to monitor the success rate of <a href="http://en.wikipedia.org/wiki/A/B_testing">A/B tests</a> (usability experiments) or similar things. All of this data we do not want to put into the "primary" database since the life-cycle of the data is very different.</p>
<p>In the cases I’ve worked on, we’ve been using a RDBMS (<a href="http://www.postgresql.org/">PostgreSQL</a> to be specific) as the "primary" database and CSV files, Salesforce.com and CouchDB as "periphery" databases.</p>
<p>The primary database is configured using Spring. One of the requirements we have is the ability to externalize all connection information, and we leverage Spring’s property placeholder for this:</p>
<pre class="prettyprint lang-xml">
<context:property-placeholder location="file:///${user.home}/datastore.properties" />
<bean class="org.apache.metamodel.spring.DataContextFactoryBean">
<property name="type" value="${datastore.type}" />
<property name="driverClassName" value="${datastore.driver}" />
<property name="url" value="${datastore.url}" />
<property name="username" value="${datastore.username}" />
<property name="password" value="${datastore.password}" />
</bean>
</pre>
<p>If you prefer, you can also configure a traditional java.sql.DataSource and inject it into this factory bean with the name ‘dataSource’. But with the approach above the 'type' of the datastore is even externalizable, meaning that we could potentially switch our web application’s primary database to MongoDB or something like that, just by changing the properties.</p>
<h2>Implementing a backend component using MetaModel</h2>
<p>When we need to get access to data, we simply inject that DataContext. If we also need to modify the data the sub-interface UpdateableDataContext is injected. Here’s an example:</p>
<pre class="prettyprint lang-java">
@Component
public class UserDao {
private final UpdateableDataContext _dataContext;
@Autowired
public UserDao(UpdateableDataContext dataContext) {
_dataContext = dataContext;
}
public User getUserById(Number userId) {
DataSet ds = _dataContext.query()
.from("users")
.select("username", "name")
.where("id").eq(userId)
.execute();
try {
if (!ds.next()) {
return null;
}
Row row = ds.getRow();
return new User(userId, row.getValue(0), row.getValue(1));
} finally {
ds.close();
}
}
}
</pre>
<p>In reality we use a couple of String-constants and so on here, to avoid typos slipping into e.g. column names. One of the good parts here is that we’re completely in control of the query plan, the query building is type-safe and neat, and the DataContext object being injected is not tied to any particular backend. We can run this query in a test using a completely different type of backend without issues. More about testing in a jiffy.</p>
<h2>Automatically creating the schema model</h2>
<p>To make deployment as easy as possible, we also ensure that our application can automatically build the tables needed upon starting the application. In existing production environments the tables will already be there, but for new deployments and testing, this capability is great. We’ve implemented this as a spring bean that has a @PostConstruct method to do the bootstrapping of new DataContexts. Here’s how we could build a “users” table:</p>
<pre class="prettyprint lang-java">
@Component
public class DatastoreBootstrap {
private final UpdateableDataContext _dataContext;
@Autowired
public UserDao(UpdateableDataContext dataContext) {
_dataContext = dataContext;
}
@PostConstruct
public void initialize() {
Schema schema = _dataContext.getDefaultSchema();
if (schema.getTable("users") == null) {
CreateTable createTable = new CreateTable(schema, “users”);
createTable.withColumn("id").ofType(ColumnType.INTEGER).asPrimaryKey();
createTable.withColumn("username").ofType(ColumnType.VARCHAR).ofSize(64);
createTable.withColumn("password_hash").ofType(ColumnType.VARCHAR).ofSize(64);
createTable.withColumn("name").ofType(ColumnType.VARCHAR).ofSize(128);
_dataContext.executeUpdate(createTable);
}
_dataContext.refreshSchemas();
}
}
</pre>
<p>So far we’ve demoed stuff that honestly can also be done by many many other persistence frameworks. But now it gets exciting, because in terms of testability I believe Apache MetaModel has something to offer which almost no other...</p>
<h2>Testing your components</h2>
<p>Using a PojoDataContext (a DataContext based on in-memory Java objects) we can bootstrap a virtual environment for our testcase that has an extremely low footprint compared to normal integration testing. Let me demonstrate how we can test our UserDao:</p>
<pre class="prettyprint lang-java">
@Test
public void testGetUserById() {
// set up test environment.
// the test datacontext is an in-memory POJO datacontext
UpdateableDataContext dc = new PojoDataContext();
new DatastoreBootstrap(dc).initialize();
// insert a few user records for the test only.
Table usersTable = dc.getDefaultSchema().getTable("users");
dc.executeUpdate(new InsertInto(usersTable).value("id", 1233).value("name", "John Doe"));
dc.executeUpdate(new InsertInto(usersTable).value("id", 1234).value("name", "Jane Doe"));
// perform test operations and assertions.
UserDao userDao = new UserDao(dc);
User user = userDao.getUserById(1234);
assertEquals(1234, user.getId();
assertEquals("Jane Doe", user.getName());
}
</pre>
<p>This is in my opinion a real strength of MetaModel. First we insert some records (physically represented as Java objects) into our data context, and then we can test the querying and everything without even having a real database engine running.</p>
<h2>Further evaluation</h2>
<p>The examples above are of course just part of the experiences of building a few webapps on top of Apache MetaModel. I noted down a lot of stuff during the development, of which I can summarize in the following pros and cons list.</p>
<p>Pros:</p>
<ul>
<li>Testability with POJOs</li>
<li>It’s also easy to facilitate integration testing or manual monkey testing using e.g. <a href="http://db.apache.org/derby/">Apache Derby</a> or the <a href="http://www.h2database.com/">H2 database</a>. We have a main method in our test-source that will launch our webapp and have it running within a second or so.</li>
<li>We use the same API for different kinds of databases.
<ul><li>When we do A/B testing, the metrics we are interested in changes a lot. So our results are stored in a <a href="http://couchdb.apache.org/">CouchDB</a> database instead, because of its dynamic schema nature.</li>
<li>For certain unexpected scenarios or exceptional values, we store data for debugging and analytical needs in CSV files, also using MetaModel. Having stuff like that in files makes it easy to share with people who wants to manually inspect what is going on.</li></ul>
</li>
<li>Precise control over queries and update scripts (transaction) scope is a great benefit. Compared with many Object-Relational-Mapping (ORM) frameworks, this feels like returning home and not having to worry about cascading effects of your actions. You get what you ask for and nothing more.</li>
<li>Concerns like SQL injection is already taken care of by MetaModel. Proper escaping and handling of difficult cases in query literals is not your concern.</li>
</ul>
<p>Cons:</p>
<ul>
<li>At this point Apache MetaModel does not have <i>any</i> object mapping facilities at all. While we do not want a ORM framework, we could definitely use some basic "Row to Object" mapping to reduce boilerplate.</li>
<li>There’s currently no API in MetaModel for altering tables. This means that we still have a few cases where we do this using plain SQL, which of course is not portable and therefore not as easily testable. But we can manage to isolate this to just a very few places in the code since altering tables is quite unusual.</li>
<li>In one of our applications we have the unusual situation that one of the databases can be altered at runtime by a different application. Since MetaModel caches the schema structure of a DataContext, such a change is not automatically reflected in our code. We can call DataContext.refreshSchemas() to flush our cached model, but obviously that needs to happen intelligently. This is <i>only</i> a concern for database that have tables altered at runtime though (which is in my opinion quite rare).</li></ul>
<p>I hope this may be useful for you to evaluate the library. If you have questions or remarks, or just feel like getting closer to the Apache MetaModel project, I strongly encourage you to join our <a href="http://metamodel.incubator.apache.org/#community">dev mailing list</a> and raise all your ideas, concerns and questions!</p>Kasper Sørensenhttp://www.blogger.com/profile/03281134453771284315noreply@blogger.com10tag:blogger.com,1999:blog-1301643147176033927.post-45376471743148758092013-06-18T12:55:00.000+02:002013-06-19T14:47:02.970+02:00Introducing Apache MetaModel<div dir="ltr" style="text-align: left;" trbidi="on">
Recently we where able to announce an important milestone in the life of our project <a href="http://metamodel.eobjects.org/" target="_blank">MetaModel</a> - it is being <a href="http://eobjects.org/trac/blog/apache-metamodel-incubation" target="_blank">incubated into the Apache Foundation</a>! Obviously this generates a lot of new attention to the project, and causing lots and lots of questions on what MetaModel is good for. We didn't grant this project to Apache just for fun, but because we wanted to maximize it's value, both for us and for the industry as a whole. So in this post I'll try and explain the scope of MetaModel, how we use it at Human Inference, and what you might use it for in your products or services.<br />
<br />
<div class="separator" style="border: none; clear: both; text-align: center;">
<img border="0" src="http://2.bp.blogspot.com/-dhuwa7AkfQQ/UcAoXMo1P0I/AAAAAAAAB7U/yrskyz2QUJA/s1600/apache-metamodel.png" style="border: none;" /></div>
<br />
First, let's recap the one-liner for MetaModel:<br />
<blockquote class="tr_bq">
<span style="background-color: #f6f6f6; font-family: Ubuntu, sans-serif; line-height: 16.890625px;">MetaModel is a library that </span><b style="background-color: #f6f6f6; font-family: Ubuntu, sans-serif; font-size: small; line-height: 16.890625px;">encapsulates</b><span style="background-color: #f6f6f6; font-family: Ubuntu, sans-serif; line-height: 16.890625px;"> the </span><i style="background-color: #f6f6f6; font-family: Ubuntu, sans-serif; line-height: 16.890625px;">differences</i><span style="background-color: #f6f6f6; font-family: Ubuntu, sans-serif; line-height: 16.890625px;"> and </span><b style="background-color: #f6f6f6; font-family: Ubuntu, sans-serif; line-height: 16.890625px;">enhances</b><span style="background-color: #f6f6f6; font-family: Ubuntu, sans-serif; line-height: 16.890625px;"> the </span><i style="background-color: #f6f6f6; font-family: Ubuntu, sans-serif; line-height: 16.890625px;">capabilities</i><span style="background-color: #f6f6f6; font-family: Ubuntu, sans-serif; line-height: 16.890625px;"> of different datastores.</span></blockquote>
In other words - it's all about making sure that the way you work with data is standardized, reusable and smart.<br />
<br />
But wait, don't we already have things like Object-Relational-Mapping (ORM) frameworks to do that? After all, a framework like <a href="http://openjpa.apache.org/" target="_blank">OpenJPA</a> or <a href="http://www.hibernate.org/" target="_blank">Hibernate </a>will allow you to work with different databases without having to deal with the different SQL dialects etc. The answer is of course <i>yes</i>, you can use such frameworks for ORM, but MetaModel is by choice not an ORM! An ORM assumes an application domain model, whereas MetaModel, as its name implies, is treating the datastore's metadata as its model. This not only allows for much more dynamic behaviour, but it also makes MetaModel applicable only to a range of specific application types that deal with more or less arbitrary data, or dynamic data models, as their domain.<br />
<br />
At <a href="http://www.humaninference.com/" target="_blank">Human Inference</a> we build just this kind of software products, so we have great use of MetaModel! The two predominant applications that use MetaModel in our products are:<br />
<ul style="text-align: left;">
<li><b>HIquality Master Data Management (MDM)</b><br />Our <a href="http://www.humaninference.com/master-data-management" target="_blank">MDM solution</a> is built on a very dynamic data model. With this application we want to allow multiple data sources to be consolidated into a single view - typically to create an aggregated list of customers. In addition, we take third party sources in and enrich the source data with this. So as you can imagine there's a lot of mapping of data models going on in MDM, and also quite a wide range of database technologies. MetaModel is one of the cornerstones to making this happen. Not only does it mean that we onboard data from a wide range of sources. It also means that these source can vary a lot from eachother and that we can map them using metadata about fields, tables etc.</li>
<li><b>DataCleaner</b><br />Our open source data quality toolkit <a href="http://datacleaner.org/" target="_blank">DataCleaner</a> is obviously also very dependent on MetaModel. Actually MetaModel started as a kind of derivative project from the DataCleaner project. In DataCleaner we allow the user to rapidly register new data and immediately build analysis jobs using it. We wanted to avoid building code that's specific to any particular database technology, so we created MetaModel as the abstraction layer for reading data from almost anywhere. Over time it has grown into richer and richer querying capabilities as well as write access, making MetaModel essentially a full CRUD framework for ... anything.</li>
</ul>
<div>
I've often pondered on the question of <i>What could other people be using MetaModel for</i>? I can obviously only provide an open answer, but some ideas that have popped up (in my head or in the community's) are:</div>
<div>
<ul style="text-align: left;">
<li><b>Any application that needs to onboard/intake data of multiple formats</b><br />Oftentimes people need to be able to import data from many sources, files etc. MetaModel makes it easy to "code once, apply on any data format", so you save a lot of work. This use-case is similar to what the <a href="http://www.datawarehousemanagement.org/" target="_blank">Quipu project</a> is using MetaModel for.</li>
<li><b>Model Driven Development (MDD) tools</b><br />Design tools for MDD are often used to build domain models and at some point translate them to the physical storage layer. MetaModel provides not only "live" metadata from a particular source, but also in-memory structures for e.g. building and mutating virtual tables, schemas, columns and other metadata. By encompassing both the virtual and the physical layer, MetaModel provides a lot of the groundwork to build MDD tools on top of it.</li>
<li><b>Code generation tools</b><br />Tools that generate code (or other digital artifacts) based on data and metadata. Traversing metadata in MetaModel is very easy and uniform, so you could use this information to build code, XML documents etc. to describe or utilize the data at hand.</li>
<li><b>An ORM for anything</b><br />I said just earlier that MetaModel is <i>NOT</i> an ORM. But if you look at MetaModel from an architectural point of view, it could very well serve as the data access layer of an ORM's design. Obviously all the object-mapping would have to be built on top of it, but then you would also have an ORM that maps to not just JDBC databases, like most ORMs do, but also to file formats, NoSQL databases, Salesforce and more!</li>
<li><b>Open-ended analytical applications</b><br />Say you want to figure out e.g. if particular words appear in a file, a database or whatever. You would have to know a lot about the file format, the database fields or similar constructs. But with MetaModel you can instead automate this process by traversing the metadata and querying whatever fields match your predicates. This way you can build tools that "just takes a file" or "just takes a database connection" and let them loose to figure out their own query plan and so on.</li>
</ul>
</div>
<div>
If I am to point at a few buzzwords these days, I would say MetaModel can play a critical role in implementing services for things such as <b>data federation</b>, <b>data virtualization</b>, <b>data consolidation</b>, <b>metadata management</b> and <b>automation</b>.<br />
<br />
And obviously this is also part of our incentive to make MetaModel available for everyone, under the Apache license. We are in this industry to make our products better and believe that cooperation to build the best foundation will benefit both us and everyone else that reuses it and contributes to it.</div>
</div>
Kasper Sørensenhttp://www.blogger.com/profile/03281134453771284315noreply@blogger.com26tag:blogger.com,1999:blog-1301643147176033927.post-4439410256864756002013-04-22T20:51:00.000+02:002013-04-22T22:06:28.792+02:00What's the fuzz about National Identifier matching?<div dir="ltr" style="text-align: left;" trbidi="on">
The topic of National Identification numbers (also sometimes referred to as social security numbers) is something that can spawn a heated debate at my workplace. Coming from Denmark, but having an employer in the Netherlands, I am exposed to two very different ways of thinking about the subject. But regardless of our differences on the subject - while developing a product for MDM of customer data, like Human Inference is doing, you need to understand the implifications of both approaches - I would almost call them the "with" and "without" national identifiers implementation of MDM!<br />
<div style="float: right;">
<img border="0" src="http://1.bp.blogspot.com/-FNgv7rfq2CM/UXWFFGWG8FI/AAAAAAAABTs/TmoMPYWIyF8/s1600/passport.png" style="border: none;" /></div>
<br />
In Denmark we use our National identifiers a lot - the CPR numbers for persons and the CVR numbers for companies. Previously I wrote <a href="http://kasper.eobjects.org/2013/01/cleaning-danish-customer-data-with-cvr.html" target="_blank">a series of blog posts on how to use CVR and CPR for data cleansing</a>. Private companies are allowed in Denmark to collect these identifiers, although consumers can opt to say "no thanks" in most cases. But all in all, it means that we have our IDs out in the society, not locked up in our bedroom closet.<br />
<br />
In the Netherlands the use of such identifiers is prohibited for almost all organizations. While talking to my colleagues I get the sense that there's a profound thinking of this ID as more a password than a key. Commercial use of the ID would be like giving up your basic privacy liberties and most people don't remember it by heart. In contrast, Danish citizens typically do share their credentials, but are quite aware about the privacy laws that companies are obligated to follow when receiving this information.<br />
<br />
So what is the end result for data quality and MDM? Well, it highly affects how organizations will be doing two of the most complex operations in an MDM solution: Data cleansing and data matching (aka deduplication).<br />
<br />
I recently had my second daughter, and immediately after her birth I could not help but noticing that the hospital crew gave us a sheet of paper with her CPR number. This was just an hour or two after she had her first breath, and a long time before she even had an official name! In data quality terms, this was nicely designed first-time-right (FTR, a common principle in DQ and MDM) system in action!<br />
<br />
When I work with Danish customers it usually means that you spend a lot of time on verifying that the IDs of persons and companies are in fact the <i>correct</i> IDs. Like any other attribute, you might have typos, formatting glitches etc. And since we have multiple registries and number types (CVR numbers, P numbers, EAN numbers etc.), you also spend quite some time on infering "what is this number?". You would typically look up the companies' names in the public CVR registry and make sure it matches the name in your own data. If not - probably the ID is wrong and you need to delete it or obtain a correct one. While finding duplicates you can typically standardize the formatting of the IDs and do exact matching for the most part, except for those cases where the ID is missing.<br />
<br />
When I work with Dutch customers it usually means that we cleanse the individual attributes of a customer in a much more rigorous manner. The name is cleansed on it's own. Then the address. Then the phone number, and then the email and other similar fields. You'll end up knowing if each element is valid or not, but not if the whole record is actually a cohesive chunk of data. While you can apply a lot of cool inferential techniques to check that the data is cohesive (for instance it is plausible that I, Kasper Sørensen, have the email i.am.<b>kasper.sorensen</b>@gmail.com) but you won't know if it is also my address that you can find in the data, or if it's just <i>some </i>valid address.<br />
<br />
Of course the grass isn't that much greener in Denmark as I present it here. Unfortunately we also do have problems with CPR and CVR, and in general I disbelieve that there will be one single source of the truth that we can do reference data checks on in the near future. For instance, change of address typically is quite delayed in the national registries, whereas it is much quicker at the post agencies. And although I think you can share your email and similar attributes through CPR - in practice that's not what people do. So actually you need a MDM hub which connects to several sources of data and then pick and choose from the ones that you trust the most for individual pieces of the data. The great thing is that in Denmark we have a much clearer way to do data interchange inbetween data services, since we do have a common type of key for the basic entities. This gives way for very interesting reference data hubs like for instance <a href="http://instantdq.com/" target="_blank">iDQ</a>, which in turn makes it easier for us to consolidate some of our integration work.<br />
<br />
Coming back to the more ethical question: Is the Danish National Identifiers a threat to our privacy? Or is it just a more modern and practical way to reference and share basic data? For me the winning argument for the Danish model is in the term "basic data". We do share basic data through CPR/CVR, but you can't access any of my compromising or transactional data. In comparison, I fear much more for my privacy when sharing data through Facebook, Google and so on. Sure, if you had my CPR number, you would also be able to find out where I live. I wouldn't share my CPR number with you if I did not want to provide that information though, and after all sharing information, CPR, addresses or anything else, <i>always </i>comes at the risk of leaking that information to other parties. Finally, as an MDM professional I must say - combining information from multiple sources - be it public or private registries - isn't exactly something new, so privacy concerns are in my opinion largely the same in all countries.<br />
<br />
But it does mean that implementations of MDM are highly likely to differ a lot when you cross national borders. Denmark and the Netherlands are maybe profound examples of different national systems, but given how much we have in common in general, I am sure there are tons of black swans out there for me yet to discover. As a MDM vendor, and as the lead for <a href="http://www.datacleaner.org/" target="_blank">DataCleaner</a>, I always need to ensure that our products caters to international - and thereby highly varied - data and ways of processing data.</div>
Kasper Sørensenhttp://www.blogger.com/profile/03281134453771284315noreply@blogger.com0tag:blogger.com,1999:blog-1301643147176033927.post-25903224889795813082013-01-17T09:53:00.003+01:002013-01-24T10:11:01.857+01:00Cleaning Danish customer data with the CVR registry and DataCleaner<div dir="ltr" style="text-align: left;" trbidi="on">
I am doing a series of 3 blog entries over at <a href="http://datavaluetalk.com/">Data Value Talk</a> about the political and practical side of the Danish government's recent decision to open up basic public data to everyone. I thought it would be nice to also share the word here on my personal blog!</div>
<div>
<a href="http://datavaluetalk.com/data-quality/cleaning-danish-customer-data-with-the-cvr-registry-and-datacleaner-part-1-of-3/"><img border="0" src="http://3.bp.blogspot.com/-bdbiEXuYEeM/UPe6S0bMR6I/AAAAAAAAAnU/SnYKdPUIeIc/s1600/cvr-blog-strip-2.png" style="border: none; border-right: 1px solid black; float: left; margin: 0px; padding: 0px;" /></a>
<a href="http://datavaluetalk.com/data-quality/cleaning-danish-customer-data-with-cvr-and-datacleaner-part-2-of-3/"><img border="0" src="http://2.bp.blogspot.com/-a6pYZEe4JQs/UPe6SyfN0-I/AAAAAAAAAnc/4ZmV2CHE4Es/s1600/cvr-blog-strip-1.png" style="border: none; border-right: 1px solid black; border-left: 1px solid black; float: left; margin: 0px; padding: 0px;" /></a>
<a href="http://datavaluetalk.com/data-quality/cleaning-danish-customer-data-with-the-cvr-registry-and-datacleaner-part-3-of-3/"><img border="0"src="http://3.bp.blogspot.com/-ZqWJpYFUwZ0/UQD1b3p9mZI/AAAAAAAAApA/F1L1JavQwqU/s400/cvr-blog-strip-3.png" style="border: none; border-left: 1px solid black; float: left; margin: 0px; padding: 0px;" /></a>
</div>
<div style="clear: both;">
<div>
Click the images above to navigate to the three chapters on my blog:<br />
<i>Cleaning Danish customer data with the CVR registry and DataCleaner</i>.</div>
</div>Kasper Sørensenhttp://www.blogger.com/profile/03281134453771284315noreply@blogger.com1tag:blogger.com,1999:blog-1301643147176033927.post-77943410729854617932012-12-07T14:30:00.002+01:002012-12-07T17:22:25.658+01:00How to build a Groovy DataCleaner extension<div dir="ltr" style="text-align: left;" trbidi="on">
In this blog entry I'll go through the process of developing a DataCleaner extension: <a href="http://datacleaner.org/extension/Groovy-DataCleaner/" target="_blank">The Groovy DataCleaner extension</a> (just published today). The source code for the extension is <a href="https://github.com/kaspersorensen/GroovyDataCleanerExtension" target="_blank">available on GitHub</a> if you wish to check it out or even fork and improve it!<br />
<img alt="Groovy" border="0" src="http://datacleaner.org/ws/extension/Groovy-DataCleaner/screenshot/1/300x300" style="border: none; float: right; margin-left: 10px;" title="Groovy" />
<br />
<i><br /></i>
<i>First step:</i> You have <b>an idea</b> for your extension. My idea was to get the <a href="http://groovy.codehaus.org/" target="_blank">Groovy language</a> integrated with DataCleaner, to offer an advanced scripting language option, similar to the <a href="http://datacleaner.org/resources/docs/3.0.3/html/ch05s02.html" target="_blank">existing JavaScript transformer</a> - just a lot more powerful. The task would give me the chance to 1) get acquainted with the Groovy language, 2) solve some of the more advanced uses of DataCleaner by giving a completely open-ended scripting option and 3) blog about it. The third point is important to me, because we right now have a <a href="http://datacleaner.org/newsitem/community-contributor-contest-2012" target="_blank">Community Contributor Contest</a>, and I'd like to invite extension developers to participate.<br />
<br />
<i>Second step:</i> Build a quick <b>prototype</b>. This usually starts by identifying which type of component(s) you want to create. In my case it was a transformer, but in some cases it might be an analyzer. The choice between these are essentially: Does your extension pre-process or transform the data in a way that it should become a part of a flow of operations? Then it's a Transformer. Or is it something that will consume the records (potentially after being pre-processed) and generate some kind of analysis result or write the records somewhere? Then it's a Analyzer.<br />
<br />
The API for DataCleaner was designed to be very easy to use. The ideom has been: 1) The obligatory functionality is provided in the interface that you implement. 2) The user-configured parts are injected using the @Configured annotation. 3) The optional parts can be injected if you need them. In other words, this is very much inspired by the idea of <a href="http://en.wikipedia.org/wiki/Convention_over_configuration" target="_blank">Convention-over-Configuration</a>.<br />
<br />
So, I wanted to build a Transformer. This was my first prototype, which I could hitch together quite quickly after reading the <a href="http://groovy.codehaus.org/Embedding+Groovy">Embedding Groovy documentation</a> and just implementing the Transformer interface revealed what I needed to provide for DataCleaner to operate:<br />
<pre class="prettyprint">@TransformerBean("Groovy transformer (simple)")
public class GroovySimpleTransformer implements Transformer<string> {
@Configured
InputColumn[] inputs;
@Configured
String code;
private GroovyObject _groovyObject;
public OutputColumns getOutputColumns() {
return new OutputColumns("Groovy output");
}
public String[] transform(InputRow inputRow) {
if (_groovyObject == null) {
_groovyObject = compileCode();
}
final Map<string object="object"> map = new LinkedHashMap<string object="object">();
for (InputColumn input : inputs) {
map.put(input.getName(), inputRow.getValue(input));
}
final Object[] args = new Object[] { map };
String result = (String) _groovyObject.invokeMethod("transform", args);
logger.debug("Transformation result: {}", result);
return new String[] { result };
}
private GroovyObject compileCode() {
// omitted
}
</string></string></string></pre>
<div>
<i>Third step: </i>Start <b>testing</b>. I believe a lot in unittesting your code, also at a very early stage. So the next thing I did was to implement a simple unittest. Notice that I take use of the MockInputColumn and MockInputRow classes from DataCleaner - these make it possible for me to test the Transformer as a <i>unit</i> and not have to do integration testing (in that case I would have to start an actual batch job, which takes a lot more effort from both me and the machine):</div>
<pre class="prettyprint">public class GroovySimpleTransformerTest extends TestCase {
public void testScenario() throws Exception {
GroovySimpleTransformer transformer = new GroovySimpleTransformer();
InputColumn<string> col1 = new MockInputColumn<string>("foo");
InputColumn<string> col2 = new MockInputColumn<string>("bar");
transformer.inputs = new InputColumn[] { col1, col2 };
transformer.code =
"class Transformer {\n" +
" String transform(map){println(map); return \"hello \" + map.get(\"foo\")}\n" +
"}";
String[] result = transformer.transform(new MockInputRow().put(col1, "Kasper").put(col2, "S"));
assertEquals(1, result.length);
assertEquals("hello Kasper", result[0]);
}
}
</string></string></string></string></pre>
<div>
Great - this verifies that our Transformer is actually working.<br />
<br />
<i>Fourth step:</i> Do the <b>polishing</b> that makes it look and feel like a usable component. It's time to build the extension and see how it works in DataCleaner. When the extension is bundled in a JAR file, you can simply click Window -> Options, select the Extensions tab and click Add extension package -> Manually install JAR file:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-cu8gK7rwxyA/UMHnllBZ48I/AAAAAAAAAUQ/e3ATQtP4GH4/s1600/extension_install_local.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-cu8gK7rwxyA/UMHnllBZ48I/AAAAAAAAAUQ/e3ATQtP4GH4/s1600/extension_install_local.png" /></a></div>
<br />
After registering your extension you will be able to find it in DataCleaner's <i>Transformation</i> menu (or if you built an Analyzer, in the <i>Analyze</i> menu).<br />
<br />
In my case I discovered several sub-optimal features of my extensions. Here's a list of them, and how I solved it:</div>
<table border="0" cellpadding="0" cellspacing="0" class="pretty-table">
<tbody>
<tr><th>What?</th><th>How?</th></tr>
<tr><td valign="top">My transformer had only a default icon</td><td>Icons can be defined by providing PNG icon (32x32 pixels) with the same name as the transformer class, in the JAR file. In my case the transformer class was GroovySimpleTransformer.java, so I made an icon available at GroovySimpleTransformer.png.</td></tr>
<tr><td valign="top">The 'Code' text field was a single line field and did not look like a code editing field.</td><td>Since the API is designed for Convention-over-Configuration, putting a plain String property as the groovy code was maybe a bit naive. There are two strategies to pursue if you have properties which need special rendering on the UI: Provide more metadata about the property (Quite easy), or build your own renderer for it (most flexible, but also more complex). In this case I was able to simply provide more metadata, using the @StringProperty annotation:
<br />
<pre class="prettyprint">@Configured
@StringProperty(multiline = true, mimeType = "text/groovy")
String code</pre>
The default DataCleaner string property widget will then provide a multi-line text field with syntax coloring for the specific mime-type:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-9nysziHs7_0/UMHqvLVaCbI/AAAAAAAAAUk/Y-MvOArONYw/s1600/groovy_syntax_coloring.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="139" src="http://2.bp.blogspot.com/-9nysziHs7_0/UMHqvLVaCbI/AAAAAAAAAUk/Y-MvOArONYw/s640/groovy_syntax_coloring.png" width="640" /></a></div>
</td></tr>
<tr><td valign="top">The compilation of the Groovy class was done when the first record hits the transformer, but ideally we would want to do it before the batch even begins.</td><td>This point is actually quite important, also to avoid race-conditions in concurrent code and other nasty scenarios. Additionally it will help DataCleaner validation the configuration before actually kicking off a the batch job.<br />
<br />
The trick is to add a method with the @Initialize annotation. If you have multiple items you need to initialize, you can even add more. In our case, it was quite simple:<br />
<pre class="prettyprint">@Initialize
public void init() {
_groovyObject = compileCode();
}</pre>
</td></tr>
<tr><td valign="top">The transformer was placed in the root of the <i>Transformation</i> menu.</td><td>This was fixed by applying the following annotation on the class, moving it into the <i>Scripting</i> category:
<br />
<pre class="prettyprint">@Categorized(ScriptingCategory.class)</pre>
</td></tr>
<tr><td valign="top">The transformer had no description text while hovering over it.</td><td>The description was added in a similar fashion, with a class-level annotation:
<br />
<pre class="prettyprint">@Description("Perform a data transformation with the use of the Groovy language.")</pre>
</td></tr>
<tr><td valign="top">After execution it would be good to clean up resources used by Groovy.</td><td>Similarly to the @Initialize annotation, I can also create one or more descruction methods, annotated with @Close. In the case of the Groovy transformer, there are some classloader-related items that can be cleared after execution this way.</td></tr>
<tr><td valign="top">In a more advanced edition of the same transformer, I wanted to support multiple output records.</td><td>DataCleaner does support transformers that yield multiple (or even zero) output records. To archieve this, you can inject an OutputRowCollector instance into the Transformer:
<br />
<pre class="prettyprint">public class MyTransformer implements Transformer<...> {
@Inject
@Provided
OutputRowCollector collector;
public void transform(InputRow row) {
// output two records, each with two new values
collector.putValues("foo", "bar");
collector.putValues("hello", "world");
}
}
</pre>
Side-note - Users of <a href="http://hadoop.apache.org/">Hadoop</a> might recognize the OutputRowCollector as similar to mappers in Map-Reduce. Transformers, like mappers, are in deed quite capable of executing in parallel.</td></tr>
</tbody></table>
<br />
<br />
<i>Fifth step:</i> When you're satisfied with the extension, <b>Publish</b> it on the <a href="http://datacleaner.org/extensions">ExtensionSwap</a>. Simply click the "Register extension" button and follow the instructions on the form. Your extension will now be available to everyone and make others in the community happy!<br />
<br />
I hope you found this blog useful as a way to get into DataCleaner extension development. I would be interested in any kind of comment regarding the extension mechanism in DataCleaner - please speak up and let me know what you think!
</div>
Kasper Sørensenhttp://www.blogger.com/profile/03281134453771284315noreply@blogger.com2tag:blogger.com,1999:blog-1301643147176033927.post-61418078504129904922012-11-06T16:08:00.001+01:002012-11-06T16:08:40.554+01:00Data quality of Big Data - challenge or potential?<div dir="ltr" style="text-align: left;" trbidi="on">
If you've been dealing with data in the last few years, you've inevitably also come across the concept of <b>Big Data</b> and <b>NoSQL</b> databases. What do these terms mean for data quality efforts? I believe they can have a great impact. But I also think they are difficult concepts to juggle because the understanding of them varies a lot.<br />
<br />
For the purpose of this writing, I will simply say that in my opinion Big Data and NoSQL exposes us as data workers to two new general challenges:<br />
<ul style="text-align: left;">
<li>Much <b>more data</b> than we're used to working with, leading to more computing time and new techniques for even handling those amounts of data.</li>
<li>More <b>complex data structures</b> that are not nescesarily based on a relational way of thinking.</li>
</ul>
<div>
So who will be exposed to these challenges? As a developer of tools I am definately be exposed to both challenges. But will the end-user be challenged by both of them? I hope not. But people need good tools in order to do clever things with data quality.</div>
<div>
<br /></div>
<div>
The <i>more data</i> challenge is primarily one of storage and performance. And since storage isn't that expensive, I mostly concern it to be a performance challenge. And for the sake of argument, let's just assume that the tool vendors should be the ones mostly concerned about performance - if the tools and solutions are well designed by the vendors, then at least most of the performance issues should be tackled here.</div>
<div>
<br /></div>
<div>
But the <i>complex data structures</i> challenge is a different one. It has surfaced for a lot of reasons, including:</div>
<div>
<ul style="text-align: left;">
<li>Favoring performance by eg. elminating the need to join database tables.</li>
<li>Favoring ease of use by allowing logical grouping of related data even though granularity might differ (eg. storing orderlines together with the order itself).</li>
<li>Favoring flexibility by not having a strict schema to perform validation upon inserting.</li>
</ul>
<div>
Typically the structure of such records isn't tabular. Instead it is often based on key/value maps, like this:</div>
</div>
<pre class="prettyprint">{
"orderNumber": 123,
"priceSum": 500,
"orderLines": [
{ "productId": "abc", "productName": "Foo", "price": 200 },
{ "productId": "def", "productName": "Bar", "price": 300 },
],
"customer": { "id": "xyz", "name": "John Doe", "country": "USA" }
}</pre>
<div>
Looking at this you will notice some data structure features which are alien to the relational world:<br />
<ul style="text-align: left;">
<li>The orderlines and customer information is contained within the same record as the order itself. In other words: We have data types like arrays/lists (the "orderLines" field) and key/value maps (each orderline element, and the "customer" field).</li>
<li>Each orderline have a "productId" field, which probably refers to a detailed product record. But it also contains a "productName" field since this information is probably often the most wanted information when presenting/reading the record. In other words: Redundancy is added for ease of use and for performance.</li>
<li>The "priceSum" field is also redundant, since we could easily deduct it by visiting all orderlines and summing them. But redundancy is also added for performance in this case.</li>
<li>Some of the same principles apply to the "customer" field: It is a key/value type, and it contains both an ID as well as some redundant information about the customer.</li>
</ul>
<div>
Support for such data structures is traditionally difficult for database tools. As tool producers, we're used to dealing with relational data, joins and these things. But in the world of Big Data and NoSQL we're up against challenges of complex data structures which includes traversal and selection within the same record.</div>
</div>
<div>
<br /></div>
<div>
In <a href="http://datacleaner.org/" target="_blank">DataCleaner</a>, these issues are primarily handled using the "Data structures" transformations, which I feel I need to give a little attention:</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-GusL_5--ssI/UJkkHaSH93I/AAAAAAAAAMk/g71FUUYt6R0/s1600/datastructures.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-GusL_5--ssI/UJkkHaSH93I/AAAAAAAAAMk/g71FUUYt6R0/s1600/datastructures.png" /></a></div>
<div>
Using these transformations you can both read and write the complex data structures that we see in Big Data projects. To explain the 8 "Data structures" transformations, I will try to define a few terms used in their naming:</div>
<div>
<ul style="text-align: left;">
<li>To "Read" a data structure such as a list or a key/value map means to view each element in the structure as a separate record. This enables you to profile eg. all the orderlines in the order-record example.</li>
<li>To "Select" from a data structure such as a list or a key/value map means to retreive specific elements while retaining the current record granularity. This enables you to profile eg. the customer names of your orders.</li>
<li>Similarly you can "Build" these data structures. The record granularity of the built data structures is the same as the current granularity in your data processing flow.</li>
<li>And lastly I should mention JSON because it is in the screenshot. JSON is probably the most common format of literal representing data structures like these. And in deed the example above is written in JSON format. Obviously we support reading and writing JSON objects directly in DataCleaner.</li>
</ul>
<div>
Using these transformations we can overcome the challenge of handling the new data structures in Big Data and NoSQL.</div>
</div>
<div>
<br /></div>
<div>
Another important aspect that comes up whenever there's a whole new generation of databases is that the way you interface them is different. In DataCleaner (or rather, in DataCleaner's little-sister project <a href="http://metamodel.eobjects.org/" target="_blank">MetaModel</a>) we've done a lot of work to make sure that the abstractions already known can be reused. This means that querying and inserting records into a NoSQL database works exactly the same as with a relational database. The only difference of course is that new data types may be available, but that builds nicely on top of the existing relational concepts of schemas, tables, columns etc. And at the same time we do all we can to optimize performance so that querying techniques is not up to the end-user but will be applied according to the situation. Maybe I'll try and explain how that works under the covers some other time!</div>
</div>
Kasper Sørensenhttp://www.blogger.com/profile/03281134453771284315noreply@blogger.com0tag:blogger.com,1999:blog-1301643147176033927.post-60037466106471202032012-10-04T11:15:00.000+02:002012-10-04T11:15:48.671+02:00Video: Introducing Data Quality monitoring<p>Everytime we publish some work that I've been working on I feel a big sigh of relief. This time it's not software, but a video - and I think the end result is quite well although we are not movie making professionals at Human Inference :)</p>
<p>Here's the stuff: Data Quality monitoring with DataCleaner 3</p>
<div style="text-align:center;">
<iframe width="640" height="360" src="http://www.youtube.com/embed/T5vImfOPBGo" frameborder="0" allowfullscreen></iframe>
</div>
<p>Enjoy! And do let us know what you think on our <a href="http://datacleaner.org/forum/1">discussion forum</a> or our <a href="http://www.linkedin.com/groups/DataCleaner-open-source-data-quality-3352784">linkedin group</a>.</p>Kasper Sørensenhttp://www.blogger.com/profile/03281134453771284315noreply@blogger.com0tag:blogger.com,1999:blog-1301643147176033927.post-55770399473375044692012-09-20T14:33:00.001+02:002012-09-20T14:33:40.573+02:00DataCleaner 3 released!<div dir="ltr" style="text-align: left;" trbidi="on">
Dear friends, users, customers, developers, analysts, partners and more!<br/><br/>After an intense period of development and a long wait, it is our pleasure to finally announce that DataCleaner 3 is available. We at Human Inference invite you all to our celebration! Impatient to try it out? Go <a target="_blank" href="http://datacleaner.org/downloads">download it</a> right now!<br/><br/>So what is all the fuzz about? Well, in all modesty, we think that with DataCleaner 3 we are redefining 'the premier open source data quality solution'. With DataCleaner 3 we've embraced a whole new functional area of data quality, namely <i>data monitoring</i>.<br/><br/>Traditionally, DataCleaner has its roots in data profiling. In the former years, we've added several related additional functions:- transformations, data cleansing, duplicate detection and more. With data monitoring we basically deliver all of the above, but in a continuous environment for analyzing, improving and reporting on your data. Furthermore, we will deliver these functions in a centralized web-based system.<br/><br/>So how will the users benefit from this new data monitoring environment? We've tried to answer this question using a series of images:<br/>
<div style="text-align:center">
<p>Monitor the evolution of your data:</p>
<img src="http://datacleaner.org/resources/infographic_part_evolution.png" alt=""/>
<p>Share your data quality analysis with everyone:</p>
<img src="http://datacleaner.org/resources/infographic_part_share.png" alt=""/>
<p>Continuously monitor and improve your data's quality:</p>
<img src="http://datacleaner.org/resources/infographic_part_improve.png" alt=""/>
<p>Connect DataCleaner to your infrastructure using web services:</p>
<img src="http://datacleaner.org/resources/infographic_part_connect.png" alt=""/>
</div>
<br/>The monitoring web application is a fully fledged environment for data quality, covering several functional and non-functional areas:<br/><ul><li> Display of timeline and trends of data quality metrics</li><li> Centralized repository for managing and containing jobs, results, timelines etc.</li><li> Scheduling and auditing of DataCleaner jobs</li><li> Providing web services for invoking DataCleaner transformations</li><li> Security and multi-tenancy</li><li> Alerts and notifications when data quality metrics are out of their expected comfort zones.</li></ul><br/>Naturally, the traditional desktop application of DataCleaner continues to be the tool of choice for expert users and one-time data quality efforts. We've even enhanced the desktop experience quite substantially:<br/><ul><li> There is a new Completeness analyzer which is very useful for simply identifying records that have incomplete fields.</li><li> You can now export DataCleaner results to nice-looking HTML reports that you can give to your manager, or send to your XML parser!</li><li> The new monitoring environment is also closely integrated with the desktop application. Thus, the desktop application now has the ability to publish jobs and results to the monitor repository, and to be used as an interactive editor for content already in the repository.</li><li> New date-oriented transformations are now available: Date range filter, which allows you to subset datasets based on date ranges, and format date, which allows to format a date using a date mask.</li><li> The Regex Parser (which was previously only available through <a target="_blank" href="http://datacleaner.org/extensions">the ExtensionSwap</a>) has now been included in DataCleaner. This makes it very convenient to parse and standardize rich text fields using regular expressions.</li><li> There's a new Text case transformer available. With this transformation you can easily convert between upper/lower case and proper capitalization of sentences and words.</li><li> Two new search/replace transformations have been added: Plain search/replace and Regex search/replace.</li><li> The user experience of the desktop application has been improved. We've added several in-application help messages, made the colors look brighter and clearer and improved the font handling.</li></ul><br/>More than 50 features and enhancements were implemented in this release, in addition to incorporating several hundreds of upstream improvements from dependent projects.<br/><br/>We hope you will enjoy everything that is new about DataCleaner 3. And do watch out for follow-up material in the coming weeks and months. We will be posting more and more online material and examples to demonstrate the wonderful new features that we are very proud of.
</div>Kasper Sørensenhttp://www.blogger.com/profile/03281134453771284315noreply@blogger.com0tag:blogger.com,1999:blog-1301643147176033927.post-7949972319825052702012-08-19T10:36:00.001+02:002019-02-12T04:23:14.080+01:00Gartner's Magic Quadrant for Data Quality Tools<br />
<div style="margin-bottom: 0in;">
<span style="font-size: medium;">Over the last two year
I've been obsessed with Gartner and their analyses of the Data Quality market. It's not because they're always right in
every aspect, but a lot of times they are, and in addition they are very prominent opinionmakers. And
in all honesty, a huge part of one's personal reasons to do open
source development is to get recognition from your peers.</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-mMEK1HA-CZY/UDCjw-qJj1I/AAAAAAAAABc/NGX3N2uB_q8/s1600/gartner.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="178" src="http://3.bp.blogspot.com/-mMEK1HA-CZY/UDCjw-qJj1I/AAAAAAAAABc/NGX3N2uB_q8/s320/gartner.png" width="320" /></a></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-size: medium;">While leading the
<a href="http://datacleaner.github.io/">DataCleaner</a> development and other projects at Human Inference, we
definately are paying a lot of attention to what Gartner is saying
about us. Also before I joined Human Inference, Gartner was important
to me. Their mentioning of DataCleaner in their <i>Who's who in Open
Source Data Quality</i> report from 2009 was the initial trigger for
contact to a lot of people, including Winfried van Holland, CEO of
Human Inference. So on a personal level I have a lot to thank Gartner for.</span></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-size: medium;">Therefore it is with great
proudness that I see that Human Inference has been promoted to the
<i>visionary </i>quadrant of Gartner's <i>Magic Quadrant for Data
Quality Tools </i>annual report,
which just came out (<a href="http://www.gartner.com/id=2111217">get it from Gartner here</a>). That's exactly where I think we deserve
to be.</span></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-size: medium;">At the same time I see
they are mentioning DataCleaner specifically as one of the strong
points for Human Inference. This is because of the <i>licensing model
</i>that we lend ourselves to with it, and for the <i>easy-to-use
interface </i>which it provides to data quality professionals.
Additionally our integration with <i>Pentaho Data Integration</i> (<a href="https://github.com/datacleaner/pdi-datacleaner">read more</a>) and
the application of our <i>cloud data quality</i> platform (<a href="http://www.easydq.com/">read more</a>) are
mentioned as strong points.</span></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-size: medium;">This is quite a
recognition since our last review by Gartner. In their 2012 update
for the <i>Who's who in Open Source Data Quality</i> (<a href="http://www.gartner.com/id=1898014">get it form Gartner here</a>)<i> </i>Gartner
critizised the DataCleaner project on a general negative attitude and
a range of false grounds. In particular, my feeling is that certain
misunderstandings about the integration of DataCleaner with the rest
of Human Inference's offerings caused our Gartner rating to be
undervalued at that time. Hopefully the next update to that report
will reflect their recent, more enlightened view. I should mention
that the two reports are rather independent of each other, so these
are just speculations from my side.</span></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-size: medium;">As such the Gartner
reports have shown to be a wonderful source of information about
competing products and market demand. Our <a href="http://kasper.eobjects.org/2012/06/revealing-dq-monitor-datacleaner-30.html">current plans for DataCleaner 3</a> are in deed also influenced by Gartner's (and our own)
description of <i>data monitoring </i>being a key functional area in
the space of data quality tools.</span></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-size: medium;">I am deeply grateful for
Gartner's treatment of DataCleaner over time. “You've got to take
the good with the bad” and for me that's been a great way of
continously improving our products. A good reception in the end makes
all the trouble worthwhile.</span></div>
Kasper Sørensenhttp://www.blogger.com/profile/03281134453771284315noreply@blogger.com0tag:blogger.com,1999:blog-1301643147176033927.post-8961287868259892362012-07-11T00:01:00.000+02:002012-07-11T09:31:18.928+02:00Query and update Databases, CSV files, Excel spreadsheets, MongoDB, CouchDB and more in Java<h2>Introduction to MetaModel 3</h2>
The other day we released version 3 of <a href="http://metamodel.eobjects.org/">MetaModel</a>, a project that I have thoroughly enjoyed working on lately. Let me share a bit of my enthusiasm and try to convince you that this is the greatest data access library there is for Java.<br />
<br />
First let me also say, just to make it clear: MetaModel is <i>NOT</i> an ORM (Object-Relational Mapping) framework. MetaModel does not doing <i>any</i> mapping to your domain object model. Contrary, MetaModel is oriented towards working with the data model that already exists in your datastores (databases, files etc.) as it is physically represented. So the model of MetaModel is in deed a <i>meta</i> model, just as it is a <i>metadata</i> model - it works with the concepts of tables, columns, rows, schemas, relationships etc. But it is also oriented towards abstracting away all the cruft of having to deal with the physical interactions with each individual data storage technology. So unlike most ORMs, MetaModel allows you to work with arbitrary data models, stored in arbitrary technologies such as relational (JDBC) databases, text file formats, Excel spreadsheets, NoSQL databases (currently CouchDB and MongoDB) and more.<br />
<br />
Here's an overview of the scope of MetaModel, depicted in our "module diagram".<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://metamodel.eobjects.org/modules.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="385" src="http://metamodel.eobjects.org/modules.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
It's important to be able to access, query and update all of these different datastore techonologies in the same way. This is basically the cross-platform story all over again. But the frontier of cross-platform is with MetaModel being moved to also entail datastore technologies, whereas it was previously mostly about the freedom of Operating System.</div>
<div class="separator" style="clear: both; text-align: left;">
In addition it is important that this common data access abstraction is elegant, scalable and flexible. I hope to convince you of this with a few examples.</div>
<h2>Code examples</h2>
<div class="separator" style="clear: both; text-align: left;">
Everything you do with MetaModel always starts with a <a href="http://metamodel.eobjects.org/apidocs/org/eobjects/metamodel/DataContext.html">DataContext</a> object. A DataContext represents the basis for any operation with your datastore. Typically one would use the <a href="http://metamodel.eobjects.org/apidocs/org/eobjects/metamodel/DataContextFactory.html">DataContextFactory</a> to get an instance for the datastore of interest. We'll assume you are working on an Excel spreadsheet "people.xlsx":</div>
<pre class="prettyprint lang-java">
DataContext dc = DataContextFactory.createExcelDataContext(new File("people.xlsx"));</pre>
<div class="separator" style="clear: both; text-align: left;">
Easy. Now let's explore the structure of this spreadsheet. We can do so either generically by traversing the graph of schemas, tables and columns - or by names if we already know what we are looking for:</div>
<pre class="prettyprint lang-java">
// getting column by path
Column customerNameColumn = dc.getColumnByQualifiedLabel("customers.name"); </pre>
<pre class="prettyprint lang-java">
// traversing all schemas, tables, columns
Schema schema = dc.getDefaultSchema();
Table[] tables = schema.getTables();
Column[] columns = tables[0].getColumns(); </pre>
<pre class="prettyprint lang-java">
// step-wise getting specific elements based on names
Table customersTable = schema.getTableByName("customers");
Column customerBalanceColumn = customerTable.getColumnByName("balance");</pre>
<h3>Queries</h3>
<div class="separator" style="clear: both; text-align: left;">
Now let's fire some queries. This is where it gets interesting! Our approach builds upon basic knowledge of SQL, but without all the dialects and runtime differences. Technically we express queries in a completely type safe manner by using the traversed metadata objects above. But we can also put in String literals and more when it is convenient to get the job done.</div>
<pre class="prettyprint lang-java">
// Simple query: Get <b>all</b> customer fields for customers with a credit balance <b>greater than</b> 10000.
DataSet ds = dc.query().from(customersTable).select(customersTable.getColumns())
.where(customerBalanceColumn).greaterThan(10000).execute();</pre>
<pre class="prettyprint lang-java">
// Slightly more advanced query: <b>Join</b> customers with their associated sales representatives
// and <b>group</b> the result to <b>count</b> which sales reps have the most customers
Column salesRepId = customersTable.getColumnByName("sales_rep_id");
Column employeeId = dc.getColumnByQualifiedLabel("employees.id");
DataSet ds = dc.query()
.from(customersTable).innerJoin("employees").on(salesRepId, employeeId)
.selectCount().and("employees.name")
.groupBy("employees.name").execute();</pre>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
You can even grab the <a href="http://metamodel.eobjects.org/apidocs/org/eobjects/metamodel/query/Query.html">Query</a> as an object and pass it on to methods and compositions which will eg. modify it for optimization or other purposes.</div>
<h3>Updates and changes</h3>
<div class="separator" style="clear: both; text-align: left;">
The last missing piece of the puzzle is making changes to your data. We've spent a lot of time creating an API that is best suited for this task across all types of datastores. Since there's a big difference in how different datastores treat updates, and MetaModel tries to unify that, we wanted an API which clearly demarcates <i>when </i>you're doing an update, so that there is no doubt about transactional bounds, scope of a batch update and so on. This is why you need to provide all your updated in a closure-style object called an <a href="http://metamodel.eobjects.org/apidocs/org/eobjects/metamodel/UpdateScript.html">UpdateScript</a>.</div>
Let's do a series of updates.<br />
<pre class="prettyprint lang-java">
// Batch #1: Create a table and insert a few records
dc.executeUpdate(new UpdateScript() {
public void run(UpdateCallback cb) {
<span style="text-align: -webkit-auto;"> </span> Table muppets = cb.createTable(schema, "muppets")
.withColumn("name").ofType(VARCHAR)
.withColumn("profession").ofType(VARCHAR).execute();
cb.insertInto(muppets).value("name","Kermit the frog")<br /> .value("profession","TV host").execute();
cb.insertInto(muppets).value("name","Miss Piggy")
.value("profession","Diva").execute();
}
});</pre>
<div class="separator" style="clear: both; text-align: left;">
</div>
<pre class="prettyprint lang-java">
// Batch #2: Update and delete a record
dc.executeUpdate(new UpdateScript() {
public void run(UpdateCallback cb) {
cb.update("muppets").value("profession","Theatre host")
.where("name").equals("Kermit the frog").execute();
cb.deleteFrom("muppets")
.where("name").like("%Piggy").execute();
}
});</pre>
<div class="separator" style="clear: both;">
</div>
<pre class="prettyprint lang-java">
// Batch #3: Drop the table
dc.executeUpdate(new UpdateScript() {
public void run(UpdateCallback cb) {
cb.dropTable("muppets").execute();
}
});</pre>
<div class="separator" style="clear: both;">
<span style="background-color: white;">As you can see, using the </span><a href="http://metamodel.eobjects.org/apidocs/org/eobjects/metamodel/UpdateScript.html" style="background-color: white;">UpdateScript</a><span style="background-color: white;">s we've encapsulated each batch operation. If the datastore supports transactions, this is also the point of transactional control. If not, MetaModel will provide appropriate synchronization to avoid race conditions, so you can safely perform concurrent updates even on Excel spreadsheets and CSV files.</span></div>
<div class="separator" style="clear: both;">
</div>
<h3>Wrapping up...</h3>
<div class="separator" style="clear: both;">
I am extremely happy working both as a developer of and a consumer of <a href="http://metamodel.eobjects.org">MetaModel</a>. I hope you felt this blog/tutorial was a good kick-start and that you got excited by the library. Please give MetaModel a spin and share your thoughts and impressions.</div>Kasper Sørensenhttp://www.blogger.com/profile/03281134453771284315noreply@blogger.com6tag:blogger.com,1999:blog-1301643147176033927.post-3058025981938411202012-06-14T16:05:00.000+02:002012-06-14T16:05:43.811+02:00Seeing sporadic OptionalDataExceptions in your Java code?This is basically to uphold a valuable mental note I made today:<br />
<div>
<br />
I was seeing very sporadic OptionalDataExceptions while doing deserialization of certain objects. Have wondered for a looong time why it was, and today I took the time (many hours) to sit down and figure out what it was.</div>
<div>
<br /></div>
<div>
After spending a lot of time creating custom ObjectInputStreams, debugging readObject() methods and more, I finally tracked it down to being an elementary (but extremely hard to identify) issue: Some of my collections (HashMaps, HashSets and more) were exposed to multiple threads and since these basic collection type are neither concurrent nor synchronized, this was the issue.</div>
<div>
<br /></div>
<div>
In essense: If you're seeing this issue, double-check all your HashSets, HashMaps, LinkedHashMaps, LinkedHashSets and IdentityHashMaps! If they're exposed to multithreading, then make them concurrent (or synchronized).</div>Kasper Sørensenhttp://www.blogger.com/profile/03281134453771284315noreply@blogger.com0tag:blogger.com,1999:blog-1301643147176033927.post-41771999157710584682012-06-04T13:50:00.000+02:002012-09-07T11:37:34.024+02:00Revealing the "DQ monitor" - DataCleaner 3.0Development of <a href="http://datacleaner.eobjects.org/">DataCleaner</a> has been a bit quiet lately (where 'lately' refers to that last month or so ;-)) but now I want to share an idea that we have been working with at Human Inference: <b>Data quality monitoring</b>.<br />
<br />
But first some motivation: I think by now DataCleaner has a pretty firm reputation as one of the leading open source data quality applications. We can, and we will, continue to improve it's core functionalities, but it is also time to take a look at the next step for it - the next big version, <b>3.0</b>. For this we are picking up a major piece of functionality that is crucial to any DQ and MDM project - <b>data quality monitoring</b>. We all know the old saying that you <i>cannot manage what you cannot measure</i>. This is a statement that has a lot of general truth to it, and also when it comes to data quality. Data profiling tools traditionally focus on a one-time measurement of various metrics. But to truly manage your data quality, you need to monitor it over time and be able to act on not only the current status, but also the <i>progress</i> you're making. Doing this is something new, which none of our open source competitors provide - a monitoring application for tracking data quality levels over time! We also want to make it easy to <i>share</i> this intelligence with everyone in your organization - so it has to be web based.<br />
<br />
Based on this we're setting forth the roadmap for DataCleaner 3.0. And we're already able to show very good results. In the following I will disclose some of what is there. Do not take it for anything more than it is: A snapshot from our current work-in-progress. But we do plan to deliver a final version within a few months.<br />
<br />
The applications main purpose is to present the history of data quality measurements. Here's a screenshot of our current prototype's timeline view:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-PAqHku5uSiQ/T8ycYiaUS5I/AAAAAAAAA2I/KKG0buVUnQg/s1600/dq_monitor_screenshot_view_timelines.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" src="http://2.bp.blogspot.com/-PAqHku5uSiQ/T8ycYiaUS5I/AAAAAAAAA2I/KKG0buVUnQg/s640/dq_monitor_screenshot_view_timelines.jpg" width="403" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
What is shown there is the metrics collected by a DataCleaner job, recorded and displayed in a timeline.
<br />
<br />
It's really easy to customize the timeline, or create new timeline views. All you need to do is select the metrics you're interested in. Notice in the screenshot below that there are different types of metrics, and for those that are queryable you have a nice autocompletion/suggestion interface:
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-i6QujAZnWk0/T8yci2WCOOI/AAAAAAAAA2Q/GsbmKv4kiso/s1600/dq_monitor_screenshot_create_timeline.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="398" src="http://2.bp.blogspot.com/-i6QujAZnWk0/T8yci2WCOOI/AAAAAAAAA2Q/GsbmKv4kiso/s400/dq_monitor_screenshot_create_timeline.jpg" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
If you click a point in the timeline, you'll get the option to drill to the point-in-time profiling result:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-o-1Hp5Xbz4A/T8yck7T93ZI/AAAAAAAAA2Y/-zAjOJQZCPE/s1600/dq_monitor_screenshot_drill_dialog.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="280" src="http://3.bp.blogspot.com/-o-1Hp5Xbz4A/T8yck7T93ZI/AAAAAAAAA2Y/-zAjOJQZCPE/s640/dq_monitor_screenshot_drill_dialog.jpg" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
If you drill, you will be given a full data profiling result (like you know them from the current version of DataCleaner, but in the browser). Our prototype is still a bit simplified on this point, but most features analysis result actually render nicely:
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-7FrYSnmAwtg/T8ycl9vbhYI/AAAAAAAAA2c/E0eUPR5LaUI/s1600/dq_monitor_screenshot_drill_result.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="287" src="http://1.bp.blogspot.com/-7FrYSnmAwtg/T8ycl9vbhYI/AAAAAAAAA2c/E0eUPR5LaUI/s400/dq_monitor_screenshot_drill_result.jpg" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
So how is it going to work in a larger context? Let me disclose a few of the ideas:
<br />
<ul>
<li><b>Queryable metrics:</b> Consider if you have a gender column and you always expect it to have values "MALE" or "FEMALE". But you also know that there is quite some dirt in there, and you wish to measure the progress of eliminating the dirt. How would you define the metric then? What you will need is a metric definition that you pass a parameter/query saying that you wish to monitor the number of values occurring that are NOT "MALE" or "FEMALE". Similar cases exist for many other use-cases like the Pattern finder, Dictionary matchers etc. etc. In the DataCleaner monitor, the user will be able to define such queries using IN [...] and NOT IN [...] clauses, like very simple SQL.</li>
<li><b>Scheduling:</b> The monitoring application will include a scheduler to let you automatic run your periodic data quality assesments. The scheduler will allow you to both set up periodic, but also trigger-based scheduling events. For instance, it might be that you have a workflow where you wish to trigger data profiling and monitoring in a wider context, such as ETL jobs, business processes etc.</li>
<li><b>Desktop integration:</b> You will also be able to run the jobs in the regular DataCleaner (desktop) application and then upload/synchronize your results with the monitoring application. This will make it easy for you to easily share your findings when you are interactively working with the desktop application.</li>
<li><b>Email alerting:</b> You will be able to set up expected ranges of particular metrics, and in the case that values are recorded outside the allowed ranges, the data steward will be alerted by email.</li>
<li><b>Repository:</b> All jobs, results, configuration data etc. is stored in a central repository. The repository allows you to centrally manage connection information, job definitions etc. that your team is using. The idea of a repository also opens up the door to concepts like versioning, personal workspaces, security, multi-tenancy and more. I think it will become a great organizing factor and a "hub" for the DataCleaner users.</li>
<li><b>Daily snapshots: </b>It is easy to define a profiling job that profiles a complete table. But when dealing with periodic data profiling, it is likely that you only wish to analyze the latest increment. Therefore DataCleaner's handling of date filters have been improved a lot. This is to ensure that you can easily request a profiling job of "yesterday and todays data" and thereby see eg. only profiling results based on the data that was entered/changed in your system recently.</li>
</ul>
Sounds exciting? We certainly think so. And this idea actually has been talked about for quite some time (I found some mentionings of a "DC web monitor" application as old as from 2009!). So I am happy that we are finally putting this idea to the test. Will love to hear from you if you have any thoughts, remarks or additional ideas.<br />
<br />
If you're interested in contributing or just taking a closer look at the development of DataCleaner 3.0, we've already started working on the <a href="http://eobjects.org/trac/query?milestone=DataCleaner+3.0">new milestone</a> in our issue tracking system. The code is located in DataCleaner's <a href="http://eobjects.org/svn/DataCleaner/trunk">source code repository</a> and we are eagerly awaiting you if you wish to pitch in!
<br/><br/>
<b>Update</b><br/><br/>
We now have an <a href="http://sourceforge.net/projects/datacleaner/files/datacleaner%20%28unstable%29/3.0-alpha/">alpha version available that you can play around with</a>. If you feel like being an early adopter, we would really appreciate any feedback from this!
<br/><br/>
<b>Update (2)</b><br/><br/>
We now have an <a href="http://sourceforge.net/projects/datacleaner/files/datacleaner%20%28unstable%29/3.0-beta/">beta version available that you can play around with</a>. This is starting to look feature complete, so please check it out and let us know what you think!Anonymousnoreply@blogger.com0tag:blogger.com,1999:blog-1301643147176033927.post-17035489585432391472012-04-17T15:48:00.000+02:002012-06-23T13:40:48.758+02:00Data quality monitoring with Kettle and DataCleanerWe've just announced a great thing - the <a href="http://datacleaner.eobjects.org/newsitem/data-profiling-and-data-quality-for-pentaho" target="_blank">cooperation with Pentaho and DataCleaner</a> which brings DataCleaners profiling features to all users of Pentaho Data Integration (aka. Kettle)! Not only is this something I've been looking forward to for a long time because it is a great exposure for us, but it also opens up new doors in terms of functionality. In this blog post I'll describe something new: <b>Data monitoring with Kettle and DataCleaner</b>.<br />
<br />
While DataCleaner is perfectly capable of doing continuous data profiling, we lack the deployment platform that Pentaho has. With Pentaho you get orchestration and scheduling, and even with a graphical editor.<br />
<br />
A scenario that I often encounter is that someone wants to execute a daily profiling job, archive the results with a timestamp and have the results emailed to the data steward. Previously we would set this sorta thing up with <a href="http://datacleaner.eobjects.org/resources/docs/2.5.1/html/pt05.html" target="_blank">DataCleaner's command line interface</a>, which is still quite a nice solution, but if you have more than just a few of these jobs, it can quickly become a mess.<br />
<br />
So alternatively, I can now just create a Kettle job like this:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-Tt1ZEEtrgqI/T41xx4q1muI/AAAAAAAAAvs/hcNFLFuLnZ0/s1600/monitoring_example.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="69" src="http://4.bp.blogspot.com/-Tt1ZEEtrgqI/T41xx4q1muI/AAAAAAAAAvs/hcNFLFuLnZ0/s400/monitoring_example.png" width="400" /></a></div>
<br />
Here's what the example does:<br />
<br />
<ol>
<li>Starts the job (duh!)</li>
<li>Creates a timestamp which needs to be used for archiving the result. This is done using a separate transformation, which you can do either using the "Get System Info" step or the "Formula" step. The result is put into a variable called "today".</li>
<li>Executes the DataCleaner job. The result filename is set to include the "${today}" variable!</li>
<li>Emails the results to the data steward.</li>
<li>If everything went well without errors, the job is succesful.</li>
</ol>
<div>
Pretty neat and something I am extremely happy about!</div>
<div>
<br /></div>
<div>
In the future I imagine to have even more features built like this. For example an ability to run multiple DataCleaner jobs with configuration options stored as data in the ETL flow. Or the ability to treat the stream of data in a Kettle transformation as the input of the DataCleaner job. Do you guys have any other wild ideas?</div>
<div><b>Update:</b> In fact we are now taking actions to provide more elaborate data quality monitoring features to the community. Go to <a href="http://kasper.eobjects.org/2012/06/revealing-dq-monitor-datacleaner-30.html">my blog entry about the plans for DataCleaner 3.0</a> for more information.</div>Anonymousnoreply@blogger.com4tag:blogger.com,1999:blog-1301643147176033927.post-83208696639116153532012-04-09T20:16:00.002+02:002012-04-09T20:20:13.542+02:00Implementing a custom datastore in DataCleaner<p>A question I am often asked by super-users, partners and developers of DataCleaner is: <b>How do you build a custom datastore in DataCleaner for my system/file-format XYZ</b>? Recently I've dealt with this for the use in the upcoming <a href="http://wiki.pentaho.com/display/EAI/Human+Inference">integration with Pentaho Kettle</a>, for a Human Inference customer who had a home grown database proxy system, and just today while it was asked on the <a href="http://datacleaner.eobjects.org/topic/305/Reading-web-server-log-files">DataCleaner forum</a>. In this blog post I will guide you through this process, which requires some basic Java programming skills, but if that's in place it isn't terribly complicated.</p>
<h3>Just gimme the code ...</h3>
<p>First of all I should say (to those of you who prefer "just the code") that there is already an example of how to do this in the <a href="http://eobjects.org/svn/DataCleaner/tags/DataCleaner-2.5/extensions/sample/">sample extension</a> for DataCleaner. Take a look at the <a href="http://eobjects.org/resources/view-doc.html?doc=/svn//DataCleaner/tags/DataCleaner-2.5/extensions/sample/src/main/java/org/eobjects/datacleaner/sample/SampleDatastore.java">org.eobjects.datacleaner.sample.SampleDatastore</a> class. Once you've read, understood and compiled the Java code, all you need to do is register the datastore in DataCleaner's conf.xml file like this (within the <datastore-catalog> element):</p>
<pre class="prettyprint lang-xml">
<custom-datastore class-name="org.eobjects.datacleaner.sample.SampleDatastore">
<property name="Name" value="My datastore" />
</custom-datastore>
</pre>
<h3>A bit more explanation please!</h3>
<p>OK, so if you wanna really know how it works, here goes...</p>
<p>First of all, a datastore in DataCleaner needs to implement the <a href="http://analyzerbeans.eobjects.org/apidocs/org/eobjects/analyzer/connection/Datastore.html">Datastore</a> interface. But instead of implementing the interface directly, I would suggest using the abstract implementation called the <a href="http://analyzerbeans.eobjects.org/apidocs/org/eobjects/analyzer/connection/UsageAwareDatastore.html">UsageAwareDatastore</a>. This abstract implementation handles concurrent access to the datastore, reusing existing connections and more. What you still need to provide when extending the UsageAwareDatastore class is primarily the <b>createDatastoreConnection()</b> method which is invoked when a (new) connection is requested. Let's see how an initial new Datastore implementation will look like:</p>
<pre class="prettyprint lang-java">
public class ExampleDatastore extends UsageAwareDatastore<DataContext> {
private static final long serialVersionUID = 1L;
public ExampleDatastore() {
super("My datastore");
}
@Override
protected UsageAwareDatastoreConnection<DataContext> createDatastoreConnection() {
// TODO Auto-generated method stub
return null;
}
@Override
public PerformanceCharacteristics getPerformanceCharacteristics() {
// TODO Auto-generated method stub
return null;
}
}
</pre>
<p>Notice that I have created a no-arg constructor. This is REQUIRED for custom datastores, since the datastore will be instantiated by DataCleaner. Later we will focus on how to make the name ("My datastore") adjustable.</p>
<p>First we want to have a look at the two unimplemented methods:</p>
<ul>
<li><b>createDatastoreConnection()</b> is used to create a new connection. DataCleaner builds upon the <a href="http://metamodel.eobjects.org">MetaModel</a> framework for data access. You will need to return a new DatastoreConnectionImpl(...). This class takes an important parameter, namely your MetaModel DataContext implementation. Often times there will already be a DataContext that you can use given some configuration, eg. a JdbcDataContext, a CsvDataContext, ExcelDataContext, MongoDbDatacontext or whatever.</li>
<li><b>getPerformanceCharacteristics()</b> is used by DataCleaner to figure out the query plan when executing a job. You will typically just return a <i>new PerformanceCharacteristics(false);</i>. Read the <a href="http://analyzerbeans.eobjects.org/apidocs/org/eobjects/analyzer/connection/PerformanceCharacteristics.html">javadoc</a> for more information :)</li>
</ul>
<h3>Parameterizable properties, please</h3>
<p>By now you should be able to implement a custom datastore, which hopefully covers your basic needs. But maybe you want to reuse the datastore class with eg. different files, different hostnames etc. In other words: Maybe you want to let your user define certain properties of the datastore.</p>
<p>To your rescue is the <a href="http://analyzerbeans.eobjects.org/apidocs/org/eobjects/analyzer/beans/api/Configured.html">@Configured</a> annotation, which is an annotation widely used in DataCleaner. It allows you to annotate fields in your class which should be configured by the user. The types of the fields can be Strings, Integers, Files etc., <a href="http://analyzerbeans.eobjects.org/apidocs/org/eobjects/analyzer/beans/api/Configured.html">you name it</a>. Let's see how you would expose the properties of a typical connection:</p>
<pre class="prettyprint lang-java">
public class ExampleDatastore extends UsageAwareDatastore<DataContext> {
// ...
@Configured
String datastoreName;
@Configured
String hostname;
@Configured
Integer port;
@Configured
String systemId;
// ...
}
</pre>
And how you would typically use them to implement methods:
<pre class="prettyprint lang-java">
public class ExampleDatastore extends UsageAwareDatastore<DataContext> {
// ...
@Override
public String getName() {
return datastoreName;
}
@Override
protected UsageAwareDatastoreConnection<DataContext> createDatastoreConnection() {
DataContext dataContext = createDataContext(hostname, port, systemId);
return new DatastoreConnectionImpl<DataContext>(dataContext, this);
}
}
</pre>
<p>If I wanted to configure a datastore using the parameters above, I could enter it in my conf.xml file like this:</p>
<pre class="prettyprint lang-xml">
<custom-datastore class-name="foo.bar.ExampleDatastore">
<property name="Datastore name" value="My datastore" />
<property name="Hostname" value="localhost" />
<property name="Port" value="1234" />
<property name="System id" value="foobar" />
</custom-datastore>
</pre>
<p>Notice that the names of the properties are inferred by reversing the camelCase notation which Java uses, so that "<b>datastoreName</b>" becomes "<b>Datastore name</b>" and so on. Alternatively you can provide an explicit name in the <b>@Configured</b> annotation.</p>
<p>I hope this introduction tutorial makes some sense for you. Once again I urge you to take a look at <a href="http://eobjects.org/svn/DataCleaner/tags/DataCleaner-2.5/extensions/sample/">the Sample DataCleaner extension</a>, which also includes a build setup (Maven based), and a custom MetaModel DataContext implementation.Anonymousnoreply@blogger.com0tag:blogger.com,1999:blog-1301643147176033927.post-4467219818736986192012-01-23T21:42:00.000+01:002012-06-23T13:42:15.038+02:00Now you can build your own DQ monitoring solution with DataCleanerIn the cover of night we've released a new version of <a href="http://datacleaner.eobjects.org/">DataCleaner</a> 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 <i>major feature</i> have been added in the inner workings of DataCleaner: The ability to <b>persist the results of your DQ analysis jobs</b>. 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 <b>data quality monitoring</b> solution. Later on I do expect there to be full (and backwards compatible) support in the UI as well.<br />
<br />
So what is it, and how does it work?<br />
Well basically it is simply two new parameters to the command line interface:<br />
<pre> -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</pre>
Here's an example of how to use it. Notice that I use the file extension <b>.analysis.result.dat</b>, which is the one thing that is currently implemented and recognized in the UI as a result file.<br />
<pre>> DataCleaner-console.exe -job examples\employees.analysis.xml\
-ot SERIALIZED\
-of employees.analysis.result.dat</pre>
Now start up DataCleaner's UI, and select "<i>File -> Open analysis job...</i>" - you'll suddenly see that the produced file can be opened:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-bvkpvoRtC9Q/Tx3EcHDZAXI/AAAAAAAAAcg/XpuJyHsMUAM/s1600/dc.analysis.result.filechooser.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="290" src="http://1.bp.blogspot.com/-bvkpvoRtC9Q/Tx3EcHDZAXI/AAAAAAAAAcg/XpuJyHsMUAM/s320/dc.analysis.result.filechooser.png" width="320" /></a></div>
And when you open the file, the result will be displayed just like a job you've run inside the application:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-vaRXyaiyV8M/Tx3ExHLKx7I/AAAAAAAAAcs/2b3m5a4idVw/s1600/dc.analysis.result.chart.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="270" src="http://2.bp.blogspot.com/-vaRXyaiyV8M/Tx3ExHLKx7I/AAAAAAAAAcs/2b3m5a4idVw/s320/dc.analysis.result.chart.jpg" width="320" /></a></div>
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 ;-)<br />
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 <a href="http://analyzerbeans.eobjects.org/apidocs/org/eobjects/analyzer/result/AnalysisResult.html" target="_blank">AnalysisResult</a> instance. Thereby you could eg. create a timeline of a particular metric and track changes to the data that you are monitoring.
<b>Update:</b> Please read my follow-up blog post about the <a href="http://kasper.eobjects.org/2012/06/revealing-dq-monitor-datacleaner-30.html">plans to include a full Data Quality monitoring solution</a> as of DataCleaner 3.0.Anonymousnoreply@blogger.com4tag:blogger.com,1999:blog-1301643147176033927.post-53020416608181992282011-12-23T16:11:00.000+01:002011-12-23T20:58:04.198+01:00Push down query optimization in DataCleaner<p>As a follow-up to my previous post about how we make DataCleaner super-fast by applying some nice multi-threading tricks, <a href="http://kasper.eobjects.org/2011/11/datacleaner-engine-explained.html">The DataCleaner engine explained</a>, I would now like to touch upon another performance booster: <b>Push down query optimization</b>.</p>
<p>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.</p>
<p>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 <a href="http://kasper.eobjects.org/2011/09/data-profiling-sqlized-uh-oh.html">writing SQL is a barrier to productivity, creativity and good data quality results</a>. So in DataCleaner we do not offer this option, because we have something that is <i>much, much nicer</i>. That solution is push down query optimization!</p>
<p>Let me illustrate. I will be using the <a target="_blank" href="http://dev.mysql.com/doc/sakila/en/sakila.html#sakila-installation">Sakila example database for MySQL</a>:</p>
<p>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:</p>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-Kohc9pltOJ4/TvSSQVqCZsI/AAAAAAAAALU/LshXyh5FeJw/s1600/pushdown0.jpg" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="222" width="320" src="http://2.bp.blogspot.com/-Kohc9pltOJ4/TvSSQVqCZsI/AAAAAAAAALU/LshXyh5FeJw/s320/pushdown0.jpg" /></a></div>
<p>In the DataCleaner logs we can see what queries are actually fired to the database. Open up the log file (in the <i>logs</i> folder) and inspect <i>datacleaner.log</i>. You will find a line like this:</p>
<blockquote>
Executing query: SELECT `nicer_but_slower_film_list`.`title` FROM sakila.`nicer_but_slower_film_list`
</blockquote>
<p>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 <b>horror</b>, <b>sports</b> and <b>action</b> movies and apply it to my pattern finder:</p>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-mJkbLj9VcVo/TvSVGExoh_I/AAAAAAAAALg/kITKrvCSS2w/s1600/pushdown1.jpg" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="133" width="320" src="http://3.bp.blogspot.com/-mJkbLj9VcVo/TvSVGExoh_I/AAAAAAAAALg/kITKrvCSS2w/s320/pushdown1.jpg" /></a></div>
<p>If we run the job, and inspect the log file again, we see now this entry:</p>
<blockquote>
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` = '<b>Horror</b>' OR `nicer_but_slower_film_list`.`category` = '<b>Action</b>' OR `nicer_but_slower_film_list`.`category` = '<b>Sports</b>')
</blockquote>
<p>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.</p>
<p>Let's also apply a Max rows filter that limits the analysis for only <i>20 records</i> and chain it so that it depends on the Equals filter:</p>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-BNIEAW3Rwd8/TvSYKa5EqDI/AAAAAAAAALs/FzhD09PrrHc/s1600/pushdown2.jpg" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="133" width="320" src="http://4.bp.blogspot.com/-BNIEAW3Rwd8/TvSYKa5EqDI/AAAAAAAAALs/FzhD09PrrHc/s320/pushdown2.jpg" /></a></div>
<p>If we now run the job, both filters will have been applied to the query:</p>
<blockquote>
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') <b>LIMIT 20</b>
</blockquote>
<p>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!</p>
<p><b>Additional information for developers</b>: If you're developing plugins to DataCleaner and want to make a query optimized filter, then simply make sure you implement the <a target="_blank" href="http://analyzerbeans.eobjects.org/apidocs/org/eobjects/analyzer/beans/api/QueryOptimizedFilter.html">QueryOptimizedFilter</a> interface! Happy coding!</p>Anonymousnoreply@blogger.com2Copenhagen, Denmark55.6760968 12.568337155.604469300000005 12.4104086 55.7477243 12.726265600000001tag:blogger.com,1999:blog-1301643147176033927.post-54821313915706466862011-11-29T13:35:00.001+01:002011-11-29T23:04:55.235+01:00The DataCleaner engine explained<img src="http://datacleaner.eobjects.org/resources/screenshots/dc_2.1_c_small.png" alt="" style="float: right; border: none;"/>
<p>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 <b>videoblog</b> entry about <b>DataCleaner's data quality processing engine</b>, and how it compares to traditional <b>ETL engines</b>.</p>
<p>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!</p>
<div style="clear:both;"></div>
<div>
<object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" width="640" height="498" id="csSWF">
<param name="movie" value="http://datacleaner.eobjects.org/resources/webcasts/etlightweight_controller.swf" />
<param name="quality" value="best" />
<param name="bgcolor" value="#1a1a1a" />
<param name="allowfullscreen" value="true" />
<param name="scale" value="showall" />
<param name="allowscriptaccess" value="always" />
<param name="flashvars" value="autostart=false&thumb=http://datacleaner.eobjects.org/resources/webcasts/etlightweight_firstframe.png&thumbscale=45&color=0x000000,0x000000" />
<!--[if !IE]>-->
<object type="application/x-shockwave-flash" data="http://datacleaner.eobjects.org/resources/webcasts/etlightweight_controller.swf" width="640" height="498">
<param name="quality" value="best" />
<param name="bgcolor" value="#1a1a1a" />
<param name="allowfullscreen" value="true" />
<param name="scale" value="showall" />
<param name="allowscriptaccess" value="always" />
<param name="flashvars" value="autostart=false&thumb=http://datacleaner.eobjects.org/resources/webcasts/etlightweight_firstframe.png&thumbscale=45&color=0x000000,0x000000" />
<!--<![endif]-->
<div id="noUpdate">
<p>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 <a href="http://www.adobe.com/go/getflashplayer">downloading here</a>. </p>
</div>
<!--[if !IE]>-->
</object>
<!--<![endif]-->
</object>
</div>
<p>Enjoy using <a href="http://datacleaner.eobjects.org">DataCleaner</a> :)</p>Anonymousnoreply@blogger.com0tag:blogger.com,1999:blog-1301643147176033927.post-19872852290666511652011-10-30T10:33:00.000+01:002011-10-30T10:33:07.156+01:00Standardize the date formats in your dataOne 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:<br />
<div>
<ul>
<li>2011-10-30</li>
<li>20111030</li>
<li>30th of October, 2011</li>
<li>30/10/11</li>
</ul>
<div>
<div>
And maybe some even more exotic ones...</div>
<div>
In this blog entry I will show you how to solve that migration issue with the use of <a href="http://datacleaner.eobjects.org/">DataCleaner</a>.</div>
<div>
<br /></div>
</div>
</div>
<div>
<span class="Apple-style-span" style="font-size: large;">1. Date mask matching</span></div>
<div>
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 <b>Date mask matcher </b>and the <b>Boolean analyzer</b>. Here are the steps involved.</div>
<div>
<ol>
<li>First set up you datastore in the welcome screen of DataCleaner.</li>
<li>Click the "Analyze!" button to begin composing your job.</li>
<li>In the tree to the left, select the columns of interest - in our example at least the <i>birthdate</i> column.</li>
<li>Click "Add transformer -> Matching and standardization -> Date mask matcher".</li>
</ol>
<div>
Your screen will now look something like this:</div>
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-0cL2g6Cm-HA/Tq0Rwxv4etI/AAAAAAAAAB8/6v5Flyer_Sg/s1600/datestd1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="271" src="http://2.bp.blogspot.com/-0cL2g6Cm-HA/Tq0Rwxv4etI/AAAAAAAAAB8/6v5Flyer_Sg/s320/datestd1.png" width="320" /></a></div>
<div>
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!</div>
<div>
<br /></div>
<div>
<span class="Apple-style-span" style="font-size: large;">2. Analyzing matches</span></div>
<div>
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:</div>
<div>
<ol>
<li>Click "Add analyzer -> Boolean analyzer".</li>
<li>Make sure all the transformed boolean columns are checked.</li>
<li>Click the "Run analysis" button.</li>
<li>Wait for the analysis to run.</li>
</ol>
<div>
Your screen will now contain an analysis result like this:</div>
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-6Su83jYmPyY/Tq0TTT1EOvI/AAAAAAAAACE/OiWgQRJg7N4/s1600/stddates2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="150" src="http://3.bp.blogspot.com/-6Su83jYmPyY/Tq0TTT1EOvI/AAAAAAAAACE/OiWgQRJg7N4/s320/stddates2.png" width="320" /></a></div>
<div>
The result has two parts: The <i>Column statistics</i> and the <i>Frequency of combinations</i>.</div>
<div>
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.</div>
<div>
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 (<i>Combination 1</i>) 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:</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-xO91UlD13cY/Tq0UYxHusUI/AAAAAAAAACM/hAA61vTCgvk/s1600/stddates3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="115" src="http://2.bp.blogspot.com/-xO91UlD13cY/Tq0UYxHusUI/AAAAAAAAACM/hAA61vTCgvk/s320/stddates3.png" width="320" /></a></div>
<div>
This gives us a good hint about which date masks we need to add to our date mask matcher.</div>
<div>
The "1982.03.21" date is a simple case - we should simply create a date mask like this: <span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">YYYY.MM.dd</span></div>
<div>
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: <span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">dd'th of' MMMMM, YYYY</span></div>
<div>
<br /></div>
<div>
<span class="Apple-style-span" style="font-size: large;">3. Converting to dates</span></div>
<div>
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!</div>
<div>
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:</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-T-H9VnDx2f0/Tq0Wb5-wJkI/AAAAAAAAACU/-KeYZmSOPJU/s1600/stddates4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="http://3.bp.blogspot.com/-T-H9VnDx2f0/Tq0Wb5-wJkI/AAAAAAAAACU/-KeYZmSOPJU/s320/stddates4.png" width="320" /></a></div>
<div>
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).</div>
<div>
We add the few masks that are relevant for our example:</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-ZMtw85qvbto/Tq0XV8PoEhI/AAAAAAAAACc/y9eC58yTwA0/s1600/stddates5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="227" src="http://2.bp.blogspot.com/-ZMtw85qvbto/Tq0XV8PoEhI/AAAAAAAAACc/y9eC58yTwA0/s320/stddates5.png" width="320" /></a></div>
<div>
And we verify that there are no immediate unrecognized dates, by clicking the "Preview data" button:</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-S1_6MtdCofo/Tq0Xf_VjvsI/AAAAAAAAACk/9txEUJDjp7s/s1600/stddates6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="http://1.bp.blogspot.com/-S1_6MtdCofo/Tq0Xf_VjvsI/AAAAAAAAACk/9txEUJDjp7s/s320/stddates6.png" width="299" /></a></div>
<div>
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:</div>
<div>
<ol>
<li>Go to the <i>Filters</i> tab.</li>
<li>Click "Add filter -> Not null".</li>
<li>Select only your converted column (in our example "birthdate (as date)").</li>
<li>Click "INVALID -> Write to CSV file".</li>
<li>Select the desired columns for the error handling file.</li>
<li>Optionally right click the "Write to CSV file" tab and select "Rename component" to give it a name like "write errors".</li>
<li>Go back to the <i>Filters </i>tab and click the VALID button to write the valid records to a CSV file or a spreadsheet.</li>
</ol>
<div>
After these steps, you should be able to inspect your job flow by clicking the <i>Visualize</i> button:</div>
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-fjwWo0T1cgQ/Tq0Y7cVJkLI/AAAAAAAAACs/wSY0S9PXRpo/s1600/stddates7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="90" src="http://3.bp.blogspot.com/-fjwWo0T1cgQ/Tq0Y7cVJkLI/AAAAAAAAACs/wSY0S9PXRpo/s320/stddates7.png" width="320" /></a></div>
<div>
Now your date standardization job is ready to run again and again to enforce standardized dates!</div>Anonymousnoreply@blogger.com1tag:blogger.com,1999:blog-1301643147176033927.post-6816969856976443182011-09-26T20:37:00.000+02:002019-02-12T04:22:05.098+01:00Data Profiling SQLized. Uh oh...<div style="clear: both; float: right; width: 260px; background-color: #f0f0f0; border: 1px solid gray; padding: 5px; margin-left: 5px;">
<img height="320" src="http://3.bp.blogspot.com/-KBvH5qyXKbc/ToDELopw8tI/AAAAAAAAAB4/hGUEv4R8Vck/s320/scrooge" width="249" />
<p>What does 'Scrooge' and 'Kasper' have in common? Not much according to my SQL data profiler.</p></div>
Some months back (admittedly, more than a couple) I was <a href="http://kasper.eobjects.org/2011/01/its-very-easy-to-make-your-own-data.html">explaining</a> 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:<br />
<br /><br />
<i>Don't do this at home kids!</i><br />
<br /><p>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:<p><blockquote>
SELECT MAX(column), MIN(column), COUNT(column), COUNT(*)<br/>FROM table;</blockquote>
<p>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:</p><blockquote style="clear: both;">
SELECT MAX(column) AS <b>highest_value</b> FROM table;<br />
SELECT MIN(column) AS <b>lowest_positive_value</b> FROM table WHERE column > 0;<br />
SELECT MIN(column) AS <b>lowest_negative_value</b> FROM table WHERE column < 0;<br />
SELECT COUNT(*) AS <b>num_values</b> FROM table WHERE column IS NOT NULL;<br />
SELECT COUNT(*) AS <b>num_nulls</b> FROM table WHERE column IS NULL;</blockquote><p>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:</p><blockquote>
SELECT COUNT(*) AS <b>num_lowercase</b> FROM table WHERE LCASE(column) = column;<br />
SELECT COUNT(*) AS <b>num_uppercase</b> FROM table WHERE UCASE(column) = column;<br />
SELECT COUNT(*) AS <b>num_mixed_case</b> FROM table WHERE LCASE(column) <> column AND UCASE(column) <> column;</blockquote><p>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:<p>
<p>INITCAP-based approach (eg. PostgreSQL and Oracle):</p><blockquote>
SELECT COUNT(*) AS <b>num_first_letter_capitalized</b> FROM table WHERE INITCAP(column) = column;</blockquote>
<p>SUBSTRING-based approach (eg. Microsoft SQL Server):</p><blockquote>
SELECT COUNT(*) AS <b>num_first_letter_capitalized</b> FROM table<br />
WHERE UCASE(SUBSTR(column FROM 0 FOR 1)) = SUBSTR(column FROM 0 FOR 1)<br />
AND LCASE(SUBSTR(column FROM 1)) = SUBSTR(column FROM 1)</blockquote>
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!<br />
<br />
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 <a href="http://datacleaner.github.io" target="_blank">DataCleaner</a>'s Pattern Finder to make it SQL optimized. Read on and judge for yourself :-)<br />
<br />
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:<br />
<blockquote>
Mickey Mouse -> 'Aaaaaa Aaaaa'<br />
Minnie Mouse -> 'Aaaaaa Aaaaa'<br />
Joachim von And -> 'Aaaaaaa aaa Aaa'<br />
kasper@eobjects.dk -> 'aaaaaa@aaaaaaa.aa'</blockquote>
<i>(Random fact: 'Joachim von And' is the Danish name for Scrooge McDuck)</i><br />
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.<br />
<br />
In PostgreSQL for example, this would look like:<br />
<br />
<blockquote>
SELECT regexp_replace(regexp_replace(regexp_replace(column, '[a-z]','a','g'), '[A-Z]','A','g'), '[0-9]','9','g') as <b>pattern</b>, COUNT(*) as <b>pattern_count</b> from table GROUP BY pattern;</blockquote>
<br />
This actually works like a charm and returns:<br />
<br />
<table border="0" cellpadding="0" cellspacing="0" class="pretty-table">
<tbody>
<tr><th>pattern</th><th>pattern_count</th></tr>
<tr><td>Aaaaaa Aaaaa</td><td>2</td></tr>
<tr><td>Aaaaaaa aaa Aaa</td><td>1</td></tr>
<tr><td>aaaaaa@aaaaaaaa.aa</td><td>1</td></tr>
</tbody></table>
So why even use a profiling tool for finding patterns? All this seems to be possible through raw SQL?<br />
<br />
I will now stop playing the devil's advocate... Cuz' seriously... This is nonsense! Having worked for some years on a pretty good <a href="http://datacleaner.github.io" target="_blank">data quality analysis tool</a>, this approach absolutely disgusts me. Here's just a few random reasons why, off the top of my head:<br />
<br />
<ul>
<li>We still haven't scratched the surface when it comes to supporting eg. non-ASCII characters in patterns.</li>
<li>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 <a href="https://datacleaner.github.io/docs/5.4.0/components/pattern_finder.html" target="_blank">Pattern Finder documentation</a>)</li>
<li>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.</li>
<li>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.</li>
<li>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.</li>
<li>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.</li>
<li>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!</li>
<li>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.</li>
</ul>
Anonymousnoreply@blogger.com7Copenhagen, Denmark55.6760968 12.568337155.604469300000005 12.4104086 55.7477243 12.726265600000001tag:blogger.com,1999:blog-1301643147176033927.post-12488947666151742432011-08-15T15:19:00.000+02:002011-08-15T15:19:47.229+02:00Get your data right... First Time Right!In my blog I mostly talk about data quality tools like <a href="http://datacleaner.eobjects.org">DataCleaner</a> that are <i>diagnostic</i> and <i>treating</i>, rather than <i>preventive</i>. 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 <a href="http://www.humaninference.com">Human Inference</a> which is just that - our <a href="http://www.humaninference.com/solutions/first-time-right">First Time Right</a> JavaScript solution.
<br />
<br />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.
<br />
<br />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.
<br />
<br />Take a look at the video below, which demonstrate most of the features:
<br />
<br /><iframe width="640" height="390" src="http://www.youtube.com/embed/BN80Ezyo2WY?rel=0" frameborder="0" allowfullscreen></iframe>
<br />
<br />Now this is quite exciting functionality, but this is also a technical blog, so I'll talk a bit about the technology involved.
<br />
<br />We built the project based on <a href="http://code.google.com/webtoolkit/">Google Web Toolkit</a> (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!
<br />
<br />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 <a href="http://en.wikipedia.org/wiki/JSONP">JSONP</a> 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.
<br />
<br />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.
<br />
<br />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.
<br />
<br />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.Anonymousnoreply@blogger.com1tag:blogger.com,1999:blog-1301643147176033927.post-59297107533537928612011-08-04T13:46:00.008+02:002011-08-05T16:44:40.426+02:00Eye candy in Java 7: New javadoc style!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.<br /><br />But one of the things in Java 7 which has escaped most people attention (I think) is the new javadoc style.<br /><br />Check it out:<br /><img style="display:block; margin:0px auto 10px; text-align:center;width: 400px; height: 273px;" src="http://3.bp.blogspot.com/-1Ha-DY-m8H4/TjqHNfBFdvI/AAAAAAAAAB0/-xSPHAjg4r0/s400/mm-apidocs.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5636966549341697778" /><br /><br />And see it live - we've just published an updated <a href="http://metamodel.eobjects.org/apidocs/">API documentation for MetaModel</a>.Anonymousnoreply@blogger.com1tag:blogger.com,1999:blog-1301643147176033927.post-91420090618364546292011-08-01T21:59:00.013+02:002012-06-23T13:39:15.679+02:00Unit test your data<div>
<img alt="" border="0" id="BLOGGER_PHOTO_ID_5635990927739223698" src="http://1.bp.blogspot.com/-K_DhJcZF5Qs/TjcP41U4fpI/AAAAAAAAABs/s7OHbwhJfG4/s400/testing.jpg" style="float: right; margin: 10px; width: 200px;" />In modern software development <a href="http://en.wikipedia.org/wiki/Unit_testing">unit testing</a> 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.</div>
<div>
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 <a href="http://datacleaner.eobjects.org/topic/206/2-1-1-Pattern-Finder-predefined-token">recently</a> reminded of by a <a href="http://datacleaner.eobjects.org/">DataCleaner</a> user on our forums.</div>
<div>
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?</div>
<table border="0" cellpadding="0" cellspacing="0" class="pretty-table"><tbody>
<tr><th>Code</th><th>Data</th></tr>
<tr><td>Compile-time checking<br />
(Ensuring correct syntax)</td><td>Database constraints</td></tr>
<tr><td>Unit testing<br />
(Checking a single unit of code)</td><td>Validating data profiling?</td></tr>
<tr><td>Continuous integration<br />
(Running all tests periodically)</td><td>Data Quality monitoring?</td></tr>
<tr><td>Bug tracking<br />
(Maintaining records of all code issues)</td><td>?</td></tr>
<tr><td>Static code analysis<br />
(a la <a href="http://findbugs.sourceforge.net/" target="_blank">FindBugs</a>)</td><td>Explorative data profiling?</td></tr>
<tr><td>Refactoring<br />
(Changing code without breaking functionality)</td><td>ETL with applied DQ rules?</td></tr>
</tbody></table>
<br />
<div>
<small>For explanation of the various data profiling and monitoring types, please refer to my previous post, <a href="http://kasper.eobjects.org/2011/04/two-types-of-data-profiling.html">Two types of data profiling</a>.</small></div>
<div>
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, <a href="http://www.datavaluetalk.com/2011/02/17/data-quality-analysis-%E2%80%93-it-requires-a-bit-of-all-worlds/" target="_blank">switching tools also comes at a large price</a>.</div>
<div>
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 <a href="http://datacleaner.eobjects.org/">DataCleaner</a>.</div>
<div><b>Update:</b> Further actions in this direction have been taken with the plans for DataCleaner 3.0, see <a href="http://kasper.eobjects.org/2012/06/revealing-dq-monitor-datacleaner-30.html">this blog post</a> for more information.</div>Anonymousnoreply@blogger.com0tag:blogger.com,1999:blog-1301643147176033927.post-86568617663434531782011-07-14T21:59:00.003+02:002011-07-14T22:03:06.103+02:00A colorful value distributionA few weeks ago I was dealing a bit of attention to the charts in <a href="http://datacleaner.eobjects.org">DataCleaner</a>. Of special interest are the <a href="http://datacleaner.eobjects.org/topic/200/DataCleaner-2-1-3D-charts-" target="_blank">value distribution charts</a> which has caused some discussions...<br /><br />Anyways, here's a proposal which includes nicer (IMO) coloring, a "distinct count measure", a dedicated "<blank>" keyword and a few other niceties.<br /><br /><table style="width:194px;"><tr><td align="center" style="height:194px;background:url(https://picasaweb.google.com/s/c/transparent_album_background.gif) no-repeat left"><a href="https://picasaweb.google.com/115504339658300272373/ValueDistributionChartProposals?authuser=0&feat=embedwebsite"><img src="https://lh6.googleusercontent.com/-JsEpTTpZINo/ThQ9q01vOWE/AAAAAAAAABA/apkqt2qn45k/s160-c/ValueDistributionChartProposals.jpg" width="160" height="160" style="margin:1px 0 0 4px;"></a></td></tr><tr><td style="text-align:center;font-family:arial,sans-serif;font-size:11px"><a href="https://picasaweb.google.com/115504339658300272373/ValueDistributionChartProposals?authuser=0&feat=embedwebsite" style="color:#4D4D4D;font-weight:bold;text-decoration:none;">Value distribution chart proposals</a></td></tr></table><br /><br />You can expect to see this live in DataCleaner 2.3 which is expected in august.Anonymousnoreply@blogger.com0