Tag Archives: data quality mart

Evolving Data Into Information through Lineage

Leave a reply


Information lineage through data DNA

 

Ira Warren Whiteside         

 

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.

 

  1. 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/
  2. 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.

 

                    

                               

                                                 

 

 

 

 

 

 

         

 

                                        

 

         

 

 

 

 

 

 

 

 

J

Merry Christmas Data Classification, Feature Engineering , Data Governance. ‘How to’ do it and some code take a look

Leave a reply


I was heavily involved in business intelligence, data warehousing and data governance as of several years ago and recently have had many chaotic personal challenges, upon returning to professional practice I have discovered things have not changed that much in 10 yearsagovernance The methodologies and approaches are still relatively consistent however the tools and techniques have changed and In my opinion not for the better, without focusing on specific tools I’ve observed that the core to data or MDM is enabling and providing a capability for classifying data into business categories or nomenclature.. and it has really not improved.

  • This basic traditional approach has not changed, in essence man AI model predicst a Metric and is wholly based on the integrity of its features or Dimensions.



Therefore I decided, to update some of the techniques and code patterns, I’ve used in the past regarding the information value chain and or record linkage , and we are going to make the results available with associated business and code examples initially with SQL Server and data bricks plus python

My good friend, Jordan Martz of DataMartz fame has greatly contrinuted to this old mans BigData enlightenment as well as Craig Campbell in updating some of the basic classification capabilities required and critical for data governance. If you would like a more detailed version of the source as well as the test data, please send me an email at iwhiteside@msn.com. Stay tuned for more update and soon we will add Neural Network capability for additional automation of “Governance Type” automated classification and confidence monitoring.

Before we focus on functionality let’s focus on methodology


Initially understand key metrics to be measured/KPI‘s their formulas and of course teh businesse’s expectation of their calculations

Immediately gather file sources and complete profiling as specified in my original article found here

Implementing the processes in my meta-data mart article would provide numerous statistics regarding integers or float field however there are some special considerations for text fields or smart codes

Before beginning classification you would employ similarity matching or fuzzy matching as described here



As I said I posted the code for this process on SQL Server Central 10 years ago here is s Python Version.

databricks-logo Roll You Own – Python Jaro_Winkler(Python)

databricks-logoroll You Own – Python Jaro_Winkler(Python)
Import Notebook
Step 1a – import pandas
import pandas

Step 2 – Import Libraries

libraries from pyspark.sql.functions import input_file_name

from pyspark.sql.types import *
import datetime, time, re, os, pandas

ML Libraires

from pyspark.ml.feature import RegexTokenizer, StopWordsRemover, NGram, HashingTF, IDF, Word2Vec, Normalizer, Imputer, VectorAssembler
from pyspark.ml import Pipeline

import mlflow

from mlflow.tracking import MLFlowClient

from sklearn.cluster import KMeans
import numpy as np
Step 3 – Test JaroWinkler

JaroWinkler(‘TRAC’,’TRACE’)
Out[5]: 0.933333
Step 4a =Implement JaroWinkler(Fuzzy Matching)

%python

def JaroWinkler(str1_in, str2_in):

if(str1_in is None or str2_in is None):
return 0

tr=0
common=0
jaro_value=0
len_str1=len(str1_in)
len_str2=len(str2_in)
column_names=[‘FID’,’FStatus’]

df_temp_table1 = pandas.DataFrame(columns=column_names)
df_temp_table2 = pandas.DataFrame(columns=column_names)

#clean_string(str1_in)
#clean_string(str2_in)

if len_str1 > len_str2:
swap_len=len_str2
len_str2=len_str1
len_str1=swap_len
swap_str=str1_in
str1_in=str2_in
str2_in=swap_str

max_len=len_str2

iCounter=1

while(iCounter <= len_str1):
df=pandas.DataFrame([[iCounter,0]], columns=column_names)
df_temp_table1=pandas.concat([df_temp_table1,df], ignore_index=True)
iCounter=iCounter+1

iCounter=1

while (iCounter <= len_str2):
df=pandas.DataFrame([[iCounter,0]], columns=column_names)
df_temp_table2=pandas.concat([df_temp_table2,df], ignore_index=True)
iCounter=iCounter+1

iCounter=1
m=round((max_len/2)-1,0)
i=1

while(i <= len_str1):
a1=str1_in[i-1]

if m >= i:
  f=1
  z=i+m 
else:
  f=i-m
  z=i+m 
if z > max_len:
  z=max_len
while (f <= z):
  a2=str2_in[int(f-1)]
  if(a2==a1 and df_temp_table2.loc[f-1].at['FStatus']==0):
    common = common + 1
    df_temp_table1.at[i-1,'FStatus']=1
    df_temp_table2.at[f-1,'FStatus']=1 
    break
  f=f+1
i=i+1

i=1
z=1
while(i <= len_str1): v1Status=df_temp_table1.loc[i-1].at[‘FStatus’] if(v1Status==1): while(z <= len_str2): v2Status=df_temp_table2.loc[z-1].at[‘FStatus’] if(v2Status==1): a1=str1_in[i-1] a2=str2_in[z-1] z=z+1 if(a1 != a2 ): tr = tr+0.5 break break i=i+1 wcd = 1.0/3.0 wrd = 1.0/3.0 wtr = 1.0/3.0 if (common != 0): jaro_value = (wcd * common)/ len_str1 + (wrd * common) / len_str2 + (wtr * (common – tr)) / common return round(jaro_value,6) Step 4b – Register JaroWinkler spark.udf.register(“JaroWinkler”, JaroWinkler) Out[6]:
Step8a – Bridge vs Master vs AssociativeALL

%sql
DROP TABLE IF EXISTS NameAssociative;
CREATE TABLE NameAssociative;

SELECT
Name
,NameInput

,sha2(replace ( NameLookput,’%[^a-Z0-9, ]%’,’ ‘) , 256) as NameLookupCeaned ,a.NameLookupKey
,sha2(replace( NameInput,’%[^a-Z0-9, ]%’,’ ‘) , 256) as NameInput,b.NameInputKey
,JaroWinkler(a.NameLookup, b.NameInput) MatchScor
,RANK() OVER (Partition by a.DetailedBUMaster ORDER BY JaroWinkler(a.NameLookupCleande, b.NameInputCleaned) DESC) NameLookup,b.NameLookupKey
FROM NameInput as a
CROSS JOIN NameLookup as b

The secret path to Data Quality and Busines Clarity

1 Reply

The secret to implementation of data quality is to follow the path below, mainly very Business Driven and focused approach extremely iterative and collaborative

The software or tools may change but the logical path, defined by identifying important business measurements required for successful and measurable results will not.

The key is not think of it as some kind of technical POC or tool trial.

It is important to realize “What” you want to measure and there by understand will not change, only “How” you create the result will change.

While many organizations are led down the path of creating a Data Governance Program, it’s frankly to large of a task, and more importantly cannot adequately be planned, with first implementing a Data Quality program, with analytical capabilities.

For example in the real world if you wanted to drill an oil well, first and before you plan, budget, move or buy equipment you would,  survey the land, examine the  minerals and drill a test well. This is not the same as in our IT data world as doing a vendor or tool Proof of Concept(POC) or a pilot to see if the vendor product works.

The oil company know exactly how there equipment works and the processes they will follow, they are trying to determine “where” to drill , not “how” to drill .

In our world , the IT WORLD, we act as if we need to “somehow” complete a “proof of concept” without really know exactly what concept we’re proving.

Are we proving the tool works, are we proving our data has errors or  our processes are flawed in essence we verifying that if we find bad data we want to fix them or the data, none of these concepts need “proving”.

My point is proving these low level concepts is probably worthless to the business and maybe even destructive, unless they are associated with a actual set of Business Goals or Measurements and they are linked directly with understandable Business deliverables. This is my way of saying put this information in an organized set of  spreadsheets linking business metrics, required fields and the order you analyze them and follow a proven process to document them and provide deliverables for both the business and technical needs.

When I say linking I mean creating an “information value chain” relating Business Goals :to Business Questions and breaking them down(decomposing)  them into the following:

  1. Business Goal – Corporate Objectives
  2. Business Question – Question needed for managing meeting the objectives.
  3. Metric – Specific formulas required. (Profit= Revenue – Expenses)
  4. Hierarchies – The order to the fields(attributes) necessary to analyze or drill down on the metrics.(Product, Department, Time)
  5. Dimension s  Natural grouping of attributes relating to each other.(Customer, Name, Address etc…)
  6. Business Matrix – Cross reference or matrix showing relationships between Business Questions, Business Processes, Metric and Dimensions. Comparing you business model to your data model.

 

The methodology for building the information value chain is as follows:

 

metric

 

 

Following this approach as the diagram shows will yield a data model and application architecture that will support answering actual business questions and provide the foundation to continue the path to data governance or to simply hold in place and explore you data to better understand your issues, their impact and then plan and prioritize your next step

Follow the path, pick a “real” goal or measurement , preferably one that matters

After that follow the path in the diagram

 

path