Data Quality

On Wednesday May 12 celebrated the 10th Forum of Business Intelligence, which has been Dataprix Media Partner and I had the opportunity to attend.

The event was quite interesting, and it was a good opportunity to learn first-hand opinions and impressions of deployments responsible for business intelligence projects.

I find it very appropriate that most of the interventions were responsible for the area of IT or business data warehouse, as the vision and how to communicate to the person who runs a project internally is often the most realistic and that can best explain the needs, problems and what actually can be considered at the level of business success or failure of a technology project.

In the market for a Data Integration is a leading manufacturer Informatica. This company is the first independent provider of data integration software. His best-known tool and the heart of his platform is Informatica PowerCenter, which has gone through many versions, and is a reference in the world of integration.

But apart from PowerCenter, Informatica also has other tools that focus on more specific purposes, while that are integrated into the platform, and always in the context of Data Integration.

The task Data Profile of SQL Server Information Services stores the results of profiling in an XML document that can be examined with the Data Profile Viewer. Article Dataprofiling with SQL Server 2008 explains how to use this new Task in SSIS.

Although this method is very simple, sometimes may not be sufficient. Addressing a data quality project may involve, for example, storing a history of profiles to assess how data quality of processed data has been improving.

The best way to work with historical data is using a database and storing the data in tables, where you can make queries, reports and comparisons. To achieve that all you would need to do is moving the metadata that the profiling task has been storing in the XML file to database tables.

Well, someone has already prepared an easy way to do it. Thomas Frisendal from the website Information Quality Solutions explains how to create an XSLT file for each type of profiling that is used to extract the XML generated by the Data Profile Task SSIS into one or more XML files with a format that can be directly imported to tables .  

One of the many improvements brought about SQL Server 2008 at the ETL with Integration Services is their ability to perform data profiling with its new Data Profile Task.

The data profiling is one of the first tasks typically addressed in Data Quality processes, and involves an initial analysis of the source data, usually on tables, with the goal of beginning to know their structure, format and level of quality. Inquiries are made at the table level, column, relationships between columns, and even relationships between tables.

The SSIS Data Profile Task works by selecting a table in a SQLServer 2000 database or above (no use on other databases) the profiling options you want to perform on the data in the table, and an XML file for saving the result. It's really simple.

You can select up to 8 types of profiling, 5 for column level and 3 several columns level analysis.

Column level profile: 

It does already enough time I presented me the possibility to start a project to offer cleaning services of data online. If we speak in terms of what plows he is heard more, we would be able to interpret it as a new meaning of the acronyms DAAS: Datacleansing Ace TO Service.

At that time I chose the name of Dataclean.es, among others things because the control was free. I registered it to my name and I did an approximation to a plan of business. Until I began to prepare a web where wanted to create a first simple version of the idea. This prototype remained in practically a simple structure, but I think that can serve to illustrate the intention that had.

As in the end me did not I decide to give the great step and to develop the project, and is a grief that the effort that dedicated to do the approach remain in a document of my portable one, I have determined to share the plan of business, enclosed in this post. I swallowed I have placed online the prototype web that began. Notice that is just as I left it, functions almost nothing.