DQS Cleansing Transform in SSIS - Data profiling and cleansing using SSIS and DQS

Data Quality Services has come up with some interesting concepts.

1) Two main pillars of DQS concepts are - Knowledge Base and Domains.

2) In laymen terms I would consider KB as tables and domains as fields of those tables. Domains have datatypes, constraints, and other attributes just like fields of a table. Domains can also be hooked with reference data providers, to get enumeration of valid and possible values.

3) Composite domains are combination of one or more domains and makes up a complex combination, which is synonymous to calculated fields.

4) When data is passed against these domains, it acts like a regular expression match on the data that is matched against the domain.

5) After the data match against the domain, it applies fuzzy logic kind of algorithm similar to what SSIS Fuzzy Lookup transform. Based on this feature, concepts like "confidence" against the matched value and options to correct the data is offered.

A tool packed with all these features is very useful for data stewards i.e. data quality analysts and operators, and that tool is Data Quality Client. From a higher level if you analyze these points, they all collectively fit in a way to function as a transform. DQS Cleansing Transform in a new transform introduced in SSIS Denali. A very informative article from SSIS Team demonstrating the use of this transform can be read from here. Another great informative article on DQS Cleansing Transform is available from DQS Team, which can be read from here.

Creation of knowledge base and domains is much like creating tables and fields, and if DQS provides an interface to import tables from source systems in the form of KB and domains just like Data Source View, it would make the process very easy and make the tool more friendlier. Honestly, I feel that organizations would have their knowledge embedded in the metadata of database objects. And creating that kind of KB and domains again is not a light weighted exercise. Let's see if such facilitation gets added to DQS in the time to come.

The bright side of DQS is that cleansing becomes more decoupled, powerful, and data profile task and data profile viewer would complement the capabilities of DQS as data profiling and data cleansing are closely associated processes. I would not be surprised if Data Profile Viewer and Data Profile Task gets dissolved into DQS and a new transform called DQS Profiling Transform gets added to SSIS toolbox in Data Flow along with DQS Cleansing Transform !!

Install SQL Server Denali CTP3 - SSDQS and BISM leads the show

SQL Server Denali CTP3 and SQL Server 2008 R2 SP1 have been publicly released, and is available for download. You can read about the official announcement from here and the quick link to download Denali CTP3 is here.

Star of the release seem to be SQL Server Data Quality Services and Business Intelligence Semantic Model !! Yes you are reading it right, I didn't say SSAS, I mentioned BISM. There are a lot other new exciting features that shows its glitter right while installation itself. As they say, "A Picture is worth thousand words", and without making you all read too much of instructions, below are a few significant screenshots from my Denali CTP3 installation in my VM.

Installation is quite straight-forward, but you would find additional tab pages while installation. In the below screen you can see new features for selection like Data Quality Services, Data Quality Client, Distributed Replay Controller, and Distributed Replay Client.

If you analyze carefully, DQS is classified under Database Engine Services category. This itself gives the first insight into the architecture of DQS. In my understanding, as its contained in the database engine service probably as an object model, you can set up different instances of DQS only by creating different SQL Server instances. Data Quality is closely associated with data, so it makes sense to position DQS more close to DB than classifying it as a shared features. But from a scalability perspective, my immediate train of thought says that it would have been better introduce it as a separate service in itself so that one can create several instances of DQS on different servers, group the same in a cluster and create a Data Quality Control center. But that would require DQS to be an independent and matured service of the likes of SSAS and SSRS with supporting client tools. So I am happy for now having DQS as a feature under Database Engine Services. To complete DQS installation, follow the steps mentioned here.

Thankfully, DQS team has announced DQS Team blog, which would be a driving resource for the community looking forward to DQS. Expect more updates on SSDQS from me very soon.

After a few screens, you would find the glimpse of BISM. You would get options to select Multidimensional mode (OLAP and SSAS) or Tabular Mode (Powerpivot and VertiPaq Engine). This seems the realization of the vision of enabling users to choose OLAP or Tabular data and querying it with MDX or DAX, with any combinations.

After that you would get few other new pages.

Finally once you have installed entire tools and services, just open BIDS and see the new world that is getting ready at Microsoft to bring new winds of change in MS BI.

Master Data Management and Data Quality Ebook - Download FREE

Data Quality and Master Data Management are a common and very huge exercises in terms of implementation within any organization.

Any enterprise would have a collection of applications for its various business and internal operations. Every application cannot be expected to be designed or readily available, such that is standardizes each piece of information. At some level, free form textual input would be made available for business needs. This is the first point where data quality issues makes its first step. Lack of standardization and integration between different applications for capturing information that is common across applications leads to inconsistent data, which is another point that stimulates data quality issues. Profiling data to identify the areas of your data repositories that are lack data quality, and devising the cleansing policy to rectify data quality issues is collectively known as data cleansing process. Fuzzy lookup transform, Data Profile Viewer and task are SSIS gadgets in SQL Server 2008 R2 that can aid in dealing with data quality issues.

Master Data Management is a very very sensitive exercise and need extensive domain knowledge and research in the business for which its intended, and there are full fledged software suites just for this purpose. This activity is a common exercise in any systems integration programme. Even when you are accumulating information from various sources for your ETL loads, you would need to maintain a temporary / permanent staging environment to manage common master data from these source systems, before you start processing transactional data. Though this master data management is functionally different from MDM carried out during a system integration programme, but technically more or less they are similar exercises. Master Data Services is the SQL Server offering which is in very initial stages (in my personal viewpoint) to help users deal with MDM exercise upto an extent.

A new free ebook is available from Solid Quality Mentors, which describes the above mentioned content, and the ways to implement it using SQL Server 2008 R2 tools. This book can be downloaded from here. I wish that this book should have come a little late, then it might have covered SQL Server Data Quality Services which is the upcoming offering in the Data Quality area from SQL Server Denali.

SQL Server Data Quality Services Architecture Video + My thoughts

SQL Server Denali is going to comes up with a new service, known as Data Quality Services (SSDQS). Knowing the architecture, targeted use, integration options, cost of ownership, developer friendliness etc are the fitment tests that a product needs to pass before architects give a green flag from the initial fitment test. Principal Group Program Manager of DQS gave a detailed demonstration at Tech Ed, on the developments happening in the Microsoft SQL Server factory for DQS right from architecture discussion to different detailed demos. I am not going to reiterate the same here in this post, but you can download the video from here.

Some of my thoughts after going through this video on DQS are as follows:

1) I am a big admirer of SSIS, so I was looking forward on how SSIS can hook into DQS. As we already saw in CTP 1 of Denali that SSIS would have a transform named SSIS Data Quality Client (at least as of now and to the best of my knowledge). This would behave more or less in the same way Fuzzy lookup or web services are used with SSIS. Whatever that happens inside DQ Server should be transparent to SSIS.

2) DQS has DQ Domains, Domain Rules, Domain Management, Knowledge Base, Knowledge Discovery, DQ Projects, DQ Client, Profiling, Notification etc as its core components in its architecture. These knowledge base are like Schema with rules and attributes and each knowledge base would fall under a domain. Dataset would be passed through these reference data / knowledge bases, which would identify and rectify data that violates the rules of the knowledge base, and at the same time enrich the knowledge base. This is my summarized version, if you get confused, just follow the video.

3) SQL Azure Data Market Place is presently and potentially the most promising client as well as customer for DQS, in my personal opinion. Data Quality market is already having it's leaders and DQS might take some time to reserve a seat in that league and gain customer confidence.

4) DQS uses reference data sets and knowledge base rules, as a part of the data quality setup. It would be interesting to see if Master Data Services can bring in some help in this area. If MDS can be integrated with DQS, and that is turn can be hooked up from SSIS, it would make a good integration design. Else functionally I see some kind of overlap between MDS and Domain knowledge / Reference data setup for DQS, though the intended purpose of both the tools / services are completely different.

There's a lot to come and go before SQL Server Denali goes RTM. So till then stay tuned and enjoy the thrill !

Data Quality Services and SSIS in SQL Server Denali

SQL Server Data Quality Services (SSDQS) is one of the new advancements in SQL Server Denali that is getting crafted at Microsoft labs. If you try to google out on this subject, you would find almost no content even on MSDN, as this development of this service is still is progress, and this is probably one of the reason that it has not been released in CTP1.

These days I have been spending quite a lot of time analysing SQL Server Denali CTP1. SSIS has already opened an interface to take advantage of this service, and this is in the form of a Data Flow Transform - "Data Correction" transform. The below image shows the interface of the editor of this transform.

Data Cleansing is a very big exercise and is one of the most integral part of any data migration project. There are a lot many different kind of portal based tools available in the industry for data quality, having a sort of operational interface that Master Data Services provides. The beginning of Data Quality related services and components began with SQL Server 2008 with the inception of Data Profiling Task and Data Profile Viewer. In my views, making data quality related services available in ETL is a very wise move and symbiotic for SSIS and SSDQS. This would help to open another very strong avenue where SSIS can serve in a very promising manner i.e. Corporate Data Cleansing projects, and SSDQS got it's very first integral client as SSIS apart from the DQ Client.

SQL Server has introduced many management libraries like AMO, SMO, DMO, RMO etc over the period of time. Data Quality Services can be expected to introduce a brand new object model something like Quality Management Objects - QMO, that can be operated by client applications like .NET and Sharepoint, where service providers can build their own flavor of data quality solutions using this framework. These are just some thoughts that my mind is sketching out, knowing SSDQS is on the horizon. I would have to wait to see if my vision about the roadmap for SSDQS is correct, or Microsoft surprises us even beyond our imagination.