With Data Profiling can apply the age old management adage “You get what you inspect, not what you expect” Readers Digest.

This article will describe how to implement a data profiling dashboard in Excel and a metadata repository as well as the TSQL required to load and update the repository.

Future articles will explore the data model as well as column and table relationship analysis using the Domain profiling results.

Data Profiling is essential  to properly determine inconsistencies  as well data transformation requirements  for integration efforts.

It is also important to be able to communicate the general data quality for the datasets or tables you will be processing.

With the assistance over the years of a few friends(Joe Novella, Scott Morgan and Michael Capes) as well as the work of Stephan DeBlois,  I have created a set of TSQL  Scripts that will create a set of tables that will provide the statistics to present your clients with a Data Quality Scorecard comparable to existing vendor tools such as Informatica , Data Flux , Data Stage and the SSIS Data Profiling Task.

This article contains a  Complete Data Profiling Kit – Download (Code, Excel Dashboards and Samples) providing capabilities similar to leading vendor tools, such as Informatica, Data Stage, Dataflux etc…

The primary difference is the repository is open and the code is also open and available and customizable. The profiling process has 4 steps as follows:

1. Create Table Statistics – Total count of records.

2. Create Column Statistics – Specific set of statistics for each column(i.e… minimum value, maximum value , distinct count, mode pattern, blank count, null count etc…)

3. Create Column Domain Statistics – domain count(count of unique vales),domain pattern(SSN=999-99-9999,ZIP =  99999-9999)

Here is a sample of the Column Statistics Dashboard: There are three panels show one worksheet for a sample “Customers” file.

Complete Column Dashboard:

• DatabaseName – Source Data Base Name
• SchemaName – Source Schema Name
• TableName – Source Table Name
• ColumnName – Source Column Name within this Table
• RecordCount – Table record count
• DistinctDomainCount – The number of distinct values within entire table.
• UniqueDomainRatio – The ration of unique records to total records.
• MostPopularDomain – The most frequently occurring value for this column.
• MostPopularDomainCount – The total for the most popular domain.
• MostPopularDomainRatio – The ration for the most popular value to total number of values.
• MinDomain – The lowest value with in this column.
• First indicator for valid values violations.
• MaxDomain – The highest value with in this column.
• First indicator for valid values violations.
• MinDomainLength – Length in characters for the minimum domain.
• MaxDomainLength – Length in characters for the maximum domain.
• NullDomainCount – The number of nulls within this column.
• NullDomainRatio – The ration of nulls to total records.
• BlankDomainCount – The number of blanks within this column.
• BlankDomainRatio – The ration of blanks to total records.
• DistinctPatternCount – The number of Distant values within entire table.
• MostPopularPattern – The most frequently occurring pattern for this column.
• MostPopularPatternCount – The total for the most popular domain.
• MostPopularPatternRatio – The ration for the most popular pattern to total number of patterns.
• InferredDataType – The data inferred from the values.

Complete Dashboard:

Column Profiling Dashboard 1-3:

 Column Profiling Dashboard 2-3:

Column Profiling Dashboard 3-3:

Domain Analysis:

In the example below you see an Excel  Worksheet that contains a pivot table allowing you to examine a columns patterns , in this cse Zip code, and subsequently drill into the actual values related to one of the patterns. Notice the Zip code example, we will review the pattern “9999”, or Zip code with only 4 numeric digits. When you click on the pattern og “9999” the actual value is revealed is

 Domain Analysis for ZipCode

Domain Analysis for Phone1

Running the Profiling Scripts Manually

Perquisites:

The scripts support two databases. One is the MetadataMart for storing the profiling results, the other is the source for your profiling.

There are four scripts , simple run them in the following order:

1. 0_Create Profilng Objects – Create all the Data Profiling Tables and Views

2. 1_Load_TableStat – This script will load records into the TableStat profiling table

3. 2_Load ColumnStat – This script will load records into the ColumnStat profiling table.  Specify Database, Schema and Table name filters as needed. Example, to profile every table names starting with “Dim” then change the table filter to SET @TABLE_FILTER = ‘Dim%. Specify the Database where the Data Profiling tables reside DECLARE @MetadataDB VARCHAR(256) SET @MetadataDB = ‘ODS_METADATA’ Specify Database, Schema and Table name filters as needed. Example, to profile every table names starting with “Dim” then change the table filter to SET @TABLE_FILTER = ‘Dim%

4. SET     @DATABASE_FILTER = ‘CustomerDB’

5. SET  @SCHEMA_FILTER = ‘dbo’

6. SET  @TABLE_FILTER = ‘%Customer%’

7. SET  @COLUMN_FILTER = ‘%’

8. 3_load DomainStat – This script will load records into the DomainStat profiling table. Specify the Database where the Data Profiling tables reside DECLARE @MetadataDB VARCHAR(256) SET @MetadataDB = ‘ODS_METADATA’ Specify Database, Schema and Table name filters as needed. Example, to profile every table names starting with “Dim” then change the table filter to SET @TABLE_FILTER = ‘Dim%

9. SET     @DATABASE_FILTER = ‘CustomerDB’

10. SET  @SCHEMA_FILTER = ‘dbo’

11. SET  @TABLE_FILTER = ‘%Customer%’

12. SET  @COLUMN_FILTER = ‘%’

13. -1_DataProfiling – Restart – Deletes and recreates all profiling tables

 Please feel free to contact me with any questions?