Tag Archives: Metadata data profiling governance

Evolving Data Into Information through Lineage

ORIGINS
COMMON SENSE

We in IT have complicated and diluted the concept and process of analyzing data and business metrics incredibly in the last few decades. We seem to be focusing on the word data.

“There is a subtle difference between data and information.”

Information vs data

There is a subtle difference between data and information. Data are the facts or details from which information is derived. Individual pieces of data are rarely useful alone. For data to become information, data needs to be put into context.

Examples of Data and Information

The history of temperature readings all over the world for the past 100 years is data.

If this data is organized and analyzed to find that global temperature is rising, then that is information.

The number of visitors to a website by country is an example of data.

Finding out that traffic from the U.S. is increasing while that from Australia is decreasing is meaningful information.

Often data is required to back up a claim or conclusion (information) derived or deduced from it.

For example, before a drug is approved by the FDA, the manufacturer must conduct clinical trials and present a lot of data to demonstrate that the drug is safe.

“Misleading” Data

Because data needs to be interpreted and analyzed, it is quite possible — indeed, very probable — that it will be interpreted incorrectly. When this leads to erroneous conclusions, it is said that the data are misleading. Often this is the result of incomplete data or a lack of context.

For example, your investment in a mutual fund may be up by 5% and you may conclude that the fund managers are doing a great job. However, this could be misleading if the major stock market indices are up by 12%. In this case, the fund has underperformed the market significantly.

Comparison chart

Synthesis: the combining of the constituent elements of separate material or abstract entities into a single or unified entity ( opposed to analysis, ) the separating of any material or abstract entity into its constituent elements.

Synthesis

Data into Information is dominant in terms of data movement and replication, in essence data logistics.

Lineage is the key.

And with the simple action of linking data file metadata names to a businesses glossary or terms, Will result in deeply insightful and informative business insight and analysis.

“Analysis the separating of any material or abstract entity into its constituent elements”

In order for a business manager for analysis you need to be able to start the analysis at a understandable business terminology.

And then provide the manager with the ability to decompose or break apart the result.

They are three essential set of capabilities and associated techniquestechniques for analysis and lineage.

Data profiling and domain analysis as well as fuzzy matching components available on my blog https://irawarrenwhiteside.com/2014/04/13/creating-a-metadata-mart-via-tsql/
Meta-data driven creation of a meta-data mart through code generation techniques, implemented.

Underlining each of these capabilities is a set of refined, developed and proven code says for accomplishing these basic fundamental task.

One case study

I have been in this business over 45 years and I’d like to offer one example of the power of the concept of a meta-data mart and lineage as it regards to business insight.

A lineage, information and data story for BCBS

I was called on Thursday and told to attend a meeting on Friday between our companies leadership and the new Chief Analytics Officer. He was prototypical of the new IT a “new school” IT Director.

I had been introduced via LinkedIn to this director a week earlier as he had followed one of my blogs on metadata marts and lineage.

After a brief introduction, our leadership began to speak and the director immediately held up his hand he said “Please don’t say anything right now the profiling you provided me is at the kindergarten level and you are dishonest”

The project was a 20 week $900,000 effort and we were in week 10.

The company has desired to do a proof of concept and better understand the use of the informatics a tool DQ as well as direction for a data governance program.

To date what had been accomplished was in a cumulation of hours of effort in billing that has not resulted in any tangible deliverable.

The project had focused on the implementation and functionally of the popular vendor tool, canned data profiling results and not providing information to the business.

The director commented on my blog post and asked if we could achieve that at his company, I of course said yes.

Immediately I proposed we use the methodology that would allow us to focus on a tops down process of understanding critical business metrics and a bottoms up process of linking data to business terms.

My basic premise was that unless your deliverable from a data quality project can provide you business insight from the top down it is of little value. In essence you’ll spend $900,000 to tell a business executive they have dirty data. At which point he will say to you “so what’s new”.

The next step was to use the business terminology glossary that existed in informatica metadata manager and map those terms to source data columns and source systems, not an extremely difficult exercise. However this is the critical step in providing a business manager the understanding and context of data statistics.

The next step, was the crucial step in which we made a slight modification to the IDQ tool and allowed the storing of the profiling results into a meta-data mart and the association of a business dimension from the business glossary the reporting statistics.

We were able to populate my predefined metadata mart dimensional model by using the tool the company and already purchased.

Lastly by using a dimensional model we were able to allow the business to apply their current reporting tool.

Upon realizing the issues they faced in their business metrics, they accelerated the data governance program and canceled the data lake until a future date.

Now for the results.

Within six weeks we provided an executive dashboard based on a meta-data mart that allowed the business to reassess their plans involving governance and a data lake.

Here are some of the results of their ability to analyze their basic data statistics but mapped to their business terminology.

6000 in properly form SS cents
35,000 dependence of subscribers over 35 years old
Thousands of charges to PPO plans out of the counties they were restricted to.
There were mysterious double counts in patient eligibility counts, managers were now able to drill into those accounts by source system and find that a simple Syncsort utility had been used improperly and duplicated records.

Creating a Metadata Mart via TSQL – Complete Data Profiling Kit – Download

15 Replies

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:

Create Table Statistics – Total count of records.
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…)
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:

0_Create Profilng Objects – Create all the Data Profiling Tables and Views
1_Load_TableStat – This script will load records into the TableStat profiling table
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%
SET @DATABASE_FILTER = ‘CustomerDB’
SET @SCHEMA_FILTER = ‘dbo’
SET @TABLE_FILTER = ‘%Customer%’
SET @COLUMN_FILTER = ‘%’
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%
SET @DATABASE_FILTER = ‘CustomerDB’
SET @SCHEMA_FILTER = ‘dbo’
SET @TABLE_FILTER = ‘%Customer%’
SET @COLUMN_FILTER = ‘%’
-1_DataProfiling – Restart – Deletes and recreates all profiling tables

Please feel free to contact me with any questions?

Metadata Mart Roadmap to Data Governance

Create Table Statistics – Total count of records.

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…)

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

Complete Dashboard:

Column Profiling Dashboard 1-3:

Column Profiling Dashboard 2-3:

Column Profiling Dashboard 3-3:

Domain Analysis:

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:

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

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

SET @DATABASE_FILTER = ‘CustomerDB’

SET @SCHEMA_FILTER = ‘dbo’

SET @TABLE_FILTER = ‘%Customer%’

SET @COLUMN_FILTER = ‘%’

SET @DATABASE_FILTER = ‘CustomerDB’

SET @SCHEMA_FILTER = ‘dbo’

SET @TABLE_FILTER = ‘%Customer%’

SET @COLUMN_FILTER = ‘%’

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

Please feel free to contact me with any questions?