Strangely enough for a predominantly SSIS blog, this post is all about how to perform data profiling without using SSIS. Whilst
the Data Profiling Task is a worthy addition, there are a couple of limitations I’ve encountered of late.
The first is that it requires SQL Server 2008, and not everyone is there yet.
The second is that it can only target SQL Server 2005 and above. What about older systems, which are
the ones that we probably need to investigate
the most, or other vendor databases such as Oracle? With these limitations in mind I did some searching to find a quick and easy alternative to help me perform some data profiling for a project I was working on recently. I only had SQL Server 2005 available, and anyway most of my target source systems were Oracle, and of course I had short timescales. I looked at several options. Some never got beyond
the download stage, they failed to install or just did not run, and others provided less than I could have produced myself by spending 2 minutes writing some basic SQL queries. In
the end I settled on an open source product called DataCleaner. To quote from their website: DataCleaner is an Open Source application for profiling, validating and comparing data. These activities help you administer and monitor your data quality in order to ensure that your data is useful and applicable to your business situation. DataCleaner is
the free alternative to software for master data management (MDM) methodologies, data warehousing (DW) projects, statistical research, preparation for extract-transform-load (ETL) activities and more. DataCleaner is developed in Java and licensed under LGPL. As quoted above it claims to support profiling, validating and comparing data, but I didn’t really get past
the profiling functions, so won’t comment on
the other two.
The profiling whilst not prefect certainly saved some time compared to
the limited alternatives.
The ability to profile heterogeneous data sources is a big advantage over
the SSIS option, and I found it overall quite easy to use and performance was good. I could see it struggling at times, but actually for what it does I was impressed. It had some data type niggles with Oracle, and some metrics seem a little strange, although thankfully they were easy to augment with some SQL queries to ensure a consistent picture.
The report export options didn’t do it for me, but copy and paste with a bit of Excel magic was sufficient. One initial point for me personally is that I have had limited exposure to things of
the Java persuasion and whilst I normally get by fine, sometimes
the simplest things can throw me. For example installing a JDBC driver, why do I have to copy files to make it all work, has nobody
ever heard of an MSI? In case there are other people out there like me who have become totally indoctrinated with
the Microsoft software paradigm, I’ve written a quick start guide that details every step required. Steps 1- 5 are
the key ones,
the rest is really an excuse for some screenshots to show you
the tool. Quick Start Guide Step 1 - Download Data Cleaner.
The Microsoft Windows zipped exe option, and I chose
the latest stable build, currently DataCleaner 1.5.3 (final). Extract
the files to a suitable location. Step 2 - Download Java. If you try and run datacleaner.exe without Java it will warn you, and then open your default browser and take you to
the Java download site. Follow
the installation instructions from there, normally just click Download Java a couple of times and you’re done. Step 3 - Download Microsoft SQL Server JDBC Driver. You may have SQL Server installed, but you won’t have a JDBC driver. Version 3.0 is
the latest as of April 2010. There is no real installer, we are in
the Java world here, but run
the exe you downloaded to extract
the files.
The default Unzip to folder is not much help, so try a fully qualified path such as C:\Program Files\Microsoft SQL Server JDBC Driver 3.0\ to ensure you can find
the files afterwards. Step 4 - If you wish to use Windows Authentication to connect to your SQL Server then first we need to copy a file so that Data Cleaner can find it. Browse to
the JDBC extract location from Step 3 and drill down to
the file sqljdbc_auth.dll. You will have to choose
the correct directory for your processor architecture. e.g. C:\Program Files\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\auth\x86\sqljdbc_auth.dll. Now copy this file to
the Data Cleaner extract folder you chose in Step 1. An alternative method is to edit datacleaner.cmd in
the data cleaner extract folder as detailed in this data cleaner wiki topic, but I find copying
the file simpler. Step 5 – Now lets run Data Cleaner, just run datacleaner.exe from
the extract folder you chose in Step 1. Step 6 – Complete or skip
the registration screen, and ignore
the task window for now. In
the main window click settings. Step 7 – In
the Settings dialog, select
the Database drivers tab, then click Register database driver and select
the Local JAR file option. Step 8 – Browse to
the JDBC driver extract location from Step 3 and drill down to select sqljdbc4.jar. e.g. C:\Program Files\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\sqljdbc4.jar Step 9 – Select
the Database driver class as com.microsoft.sqlserver.jdbc.SQLServerDriver, and then click
the Test and Save database driver button. Step 10 - You should be back at
the Settings dialog with a
the list of drivers that includes SQL Server. Just click Save Settings to persist all your hard work. Step 11 – Now we can start to profile some data. In
the main Data Cleaner window click New Task, and then Profile from
the task window. Step 12 – In
the Profile window click Open Database Step 13 – Now choose
the SQL Server connection string option. Selecting a connection string gives us a template like jdbc:sqlserver://<hostname>:1433;databaseName=<database>, but obviously it requires some details to be entered for example jdbc:sqlserver://localhost:1433;databaseName=SQLBits. This will connect to
the database called SQLBits on my local machine.
The port may also have to be changed if using such as when you have a multiple instances of SQL Server running. If using SQL Server Authentication enter a username and password as required and then click Connect to database. You can use Window Authentication, just add integratedSecurity=true to
the end of your connection string. e.g jdbc:sqlserver://localhost:1433;databaseName=SQLBits;integratedSecurity=true. If you didn’t complete Step 4 above you will need to do so now and restart Data Cleaner before it will work. Manually setting
the connection string is fine, but creating a named connection makes more sense if you will be spending any length of time profiling a specific database. As highlighted in
the left-hand screen-shot, at
the bottom of
the dialog it includes partial instructions on how to create named connections. In
the folder shown C:\Users\<Username>\.datacleaner\1.5.3, open
the datacleaner-config.xml file in your editor of choice add your own details. You’ll see a sample connection in
the file already, just add yours following
the same pattern. e.g. <!-- Darren's Named Connections -->
<bean class="dk.eobjects.datacleaner.gui.model.NamedConnection">
<property name="name" value="SQLBits Local Connection" />
<property name="driverClass" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
<property name="connectionString"
value="jdbc:sqlserver://localhost:1433;databaseName=SQLBits;integratedSecurity=true" />
<property name="tableTypes">
<list>
<value>TABLE</value>
<value>VIEW</value>
</list>
</property>
</bean>
Step 14 – Once back at
the Profile window, you should now see your schemas, tables and/or views listed down
the left hand side. Browse this tree and double-click a table to select it for profiling. You can then click Add profile, and choose some profiling options, before finally clicking Run profiling.
You can see below a sample output for three of
the most common profiles, click
the image for full size.
I hope this has given you a taster for DataCleaner, and should help you get up and running pretty quickly.
