Mastering Microsoft SQL Server tools required for EIM (MDS, DQS, Profiling and SSIS) – Complete Kit

Overview

Understanding and mastering the various Microsoft SQL Server Tools available for Data Quality , Master Data Management , Fuzzy Matching and Enterprise Information Management can be daunting and exasperating. In the hopes of reducing the anxiety and frustration I am providing a practical roadmap using openly available resources and requiring only a few days to complete.

I have reviewed and organized several articles and blog post that would allow you within a few days to master the skills required to utilize several SQL Server 2012 capabilities mentioned above.

From a business perspective where to be covering the following:

Basic

Master Data Management (MDS)

Data Quality (DQS)

Integration (SSIS)

Advanced

Fuzzy Matching(data Deduplication)

Data Profiling (Source analysis against business rules)

Basic EIM

Master Data Services

Nick Barclay: BI-Lingual: MDS Architecture Notes

This article will provide overview of the MDS architecture in support of MDM (Master Data Management)

http://nickbarclay.blogspot.com/2009/12/mds-architecture-notes.html

Nick Barclay: BI-Lingual: Beginning Master Data Services (Part 1 thru 7)

This article will teach you the steps for implementing Microsoft MDS as well as the tasks required to develop and load a Model. Complete all exercises in order. MDS is the tool Microsoft provide to support creating and maintaining reference data set (Lookup and Code tables) in support of Master Data Management.

http://nickbarclay.blogspot.com/2009/11/beginning-master-data-services-part-1.html

Data Quality Services

Enterprise Information Management using SSIS, MDS, and DQS Together [Tutorial]

This next article will teach you how to implement and utilize the Data Quality cleansing and matching capabilities.

http://technet.microsoft.com/en-us/library/jj819782.aspx

Fuzzy Matching and Deduplication

Advanced SSIS Fuzzy Matching via Record Linkage Methodology – SQLServerCentral

This article will teach you the concepts and methodology recommended for Fuzzy Matching or Deduplication, in the context of a well known Record Linkage Methodology.

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/71486/

Advanced Matching and Data Profiling

I have also included the next two articles to further explore the code and capabilities to solve complex matching and deduplication efforts

Roll Your Own Fuzzy Match / Grouping (Jaro Winkler) – T-SQL – SQLServerCentral

http://www.sqlservercentral.com/articles/Fuzzy+Match/65702/

Roll Your Own SSIS Fuzzy Matching / Grouping SSIS (Jaro – Winkler) – SQLServerCentral

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/65616/

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

https://irawarrenwhiteside.com/2014/04/13/creating-a-metadata-mart-via-tsql/

Guerrilla MDS MDM The Road To Data Governance

Leave a reply

Watch “Guerrilla MDS MDM The Road To Data Governance” by @irawhiteside on @PASSBIVC channel here: youtu.be/U0TtQUhch-U #SQLServer #SQLPASS

For tomorrow’s MDM you must be ready to embrace Agile Iterative and/or Extreme Scoping in order to fully realize the benefits and learn the constraints of MDS

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?

Integrating Master Data Services Into The Enterprise

Leave a reply

Excellent blog by Chris on the MDS underpinnings and Metadata.

Bluewater SQL

A lot of what is typically discussed in regards to Master Data Services (MDS) revolves around either the Silverlight web-based interface or the Excel plug-in. What’s not often discussed are the back-end integrations scenarios for loading, extracting (querying) and the otherwise day-to-day management or reporting requirements a Master Data solution might involve. In this post, we will look at common integration scenarios and techniques for handling each within MDS.

Loading Data (Batch)

Out-of-the-box MDS has facilities to stage and easily load data in batch. For each entity (and hierarchy) you create, a staging table is also created. For leaf members the staging table takes the form of:

stg.<Entity Name>_Leaf

Data is then loaded directly to the table using a SSIS package or any other tool capable of writing to a SQL Server table. When all the data is loaded the batch can be started programmatically using calling a stored procedure…

View original post 1,509 more words

Guerilla MDM via Microsoft MDS: Force Model Validation or TSQL Script to force set Validation Status ID ‘s

1 Reply

Using Metadata and code generation to programmatically set Validation Status ID’s for all leaf members in an MDS Entity.

Primarily this script relies on the MDS mdm.udpMemberValidationStatusUpdate procedure for a single member. Unfortunately I could not get the stored proc for multiple members(mdm.udpMembersValidationStatusUpdate) to work, so I created a script to rely on the single member version.( mdm.udpMemberValidationStatusUpdate). I could not resolve the “operand type clash” error.

The script requires the model name and entity name, to generate a TSQL statement to update each member id to the designated Validation status.
IF OBJECT_ID('tempdb..#MemberIdList') IS NOT NULL DROP TABLE #MemberIdList

DECLARE @ModelName nVarchar(50) = ‘Supplier’
DECLARE @Model_id int
DECLARE @Version_ID int
DECLARE @Entity_ID int
DECLARE @Entity_Name nVarchar(50) = ‘Supplier’
DECLARE @Entity_Table nVarchar(50)
DECLARE @sql nVarchar(500)
DECLARE @sqlExec nVarchar(500) = ‘EXEC mdm.udpMemberValidationStatusUpdate ‘
DECLARE @ValidationStatus_ID int = 4
— Found the following information intable [mdm].[tblList]
–ListCode ListName Seq ListOption
–lstValidationStatus ValidationStatus 0 New, Awaiting Validation
–lstValidationStatus ValidationStatus 1 Validating
–lstValidationStatus ValidationStatus 4 Validation Failed
–lstValidationStatus ValidationStatus 3 Validation Succeeded
–lstValidationStatus ValidationStatus 2 Awaiting Revalidation
–lstValidationStatus ValidationStatus 5 Awaiting Dependent Member Revalidation
–MemberType_ID = 1 (Leaf Member)
DECLARE @MemberType_ID int = 1
–Get Version id for Model
SET @Version_ID = (SELECT MAX(ID)
FROM mdm.viw_SYSTEM_SCHEMA_VERSION
WHERE Model_Name = @ModelName)
print @Version_ID
–Get Model IDfor Model
SET @Model_ID = (SELECT Model_ID
FROM mdm.viw_SYSTEM_SCHEMA_VERSION
WHERE Model_Name = @ModelName)
print @Model_ID
–Get Entity ID for specific Entity
SET @Entity_ID =
(SELECT [ID]
FROM [mdm].[tblEntity]
where [Model_ID] = @Model_ID
and [Name]= @Entity_Name)

–Get Entity Table Namefor specific Entity

SET @Entity_Table =
(SELECT[EntityTable]
FROM [mdm].[tblEntity]
where [Model_ID] = @Model_ID
and [Name]= @Entity_Name)
print ‘Processing Following Model ‘ + convert(varchar,@ModelName)
print ‘Model ID = ‘ + convert(varchar,@Model_id)
print ‘Version ID = ‘ + convert(varchar,@Version_ID)
print ‘Entity = ID ‘ + convert(varchar,@Entity_ID)
print ‘Entity Name = ‘ + convert(varchar,@Entity_Name)
print ‘Entity Table Name ‘ + convert(varchar,@Entity_Table)
print ‘Validation Status ID being set to ‘ + convert(varchar,@ValidationStatus_ID) + ‘ for all members in ‘+ convert(varchar,@Entity_Name)

–Create local temp table to hold member ids to update
CREATE TABLE #MemberIdList
( id int,
Processed int)
–Generate SQL to populaet temp table
set @sql = N’INSERT INTO #MemberIdList select id, 0 from mdm.’ +convert(varchar,@Entity_Table)

–Generate SQL to display stored proc update
EXECUTE sp_executesql @sql

–Create Tabe Variable to hold SQL Commands and prepare for execute
Declare @Id int
DECLARE @MemberSQL TABLE
(
ID int,
mdssql nvarchar(500),
Processed int
)
While (Select Count(*) From #MemberIdList Where Processed = 0) > 0
Begin
Select Top 1 @Id = ID From #MemberIdList Where Processed = 0

set @sql = N’ mdm.udpMemberValidationStatusUpdate ‘ +convert(varchar,@Version_ID) +’,’ +convert(varchar, @Entity_ID) +’,’ +convert(varchar,@ID) +’,’ +convert(varchar, @MemberType_ID) +’,’ + convert(varchar,@ValidationStatus_ID)

print @sql

INSERT INTO @MemberSQL
Select @id , @sql, 0

Update #MemberIdList Set Processed = 1 Where ID = @Id

End

Declare @sqlsyntax nVarchar(500)

While (Select Count(*) From @MemberSQL Where Processed = 0) > 0
Begin
Select Top 1 @id = id From @MemberSQL Where Processed = 0
Select Top 1 @sqlsyntax = mdssql From @MemberSQL Where Processed = 0

print @sqlsyntax
— comment this line to not execute update Validation Status ID
EXECUTE sp_executesql @sqlsyntax

Update @MemberSQL Set Processed = 1 Where ID = @Id
End

Declare @Entity_TableSQL nVarchar(500)
set @Entity_TableSQL = ‘SELECT * FROM [mdm].’ + @Entity_Table
print @Entity_TableSQL
exec sp_executesql @Entity_TableSQL
Here are several links I referenced:

The code for forcing the Validation of the Model is here. Jeremey Kashel’s Blog

Microsoft SQL Server 2012 Master Data Services 2/E Tyler Graham

Table Variables in TSQL

I am sure this can be improved, please contact me with questions or suggestions.

-Ira Warren Whiteside

On MDM, Data Models and Big Data

Leave a reply

Interesting discussion on MDM , Logical Data Models and Big Data , hashing around approaches.

Liliendahl on Data Quality

As described in the post Small Data with Big Impact my guess is that we will see Master Data Management solutions as a core element in having data architectures that are able to make sustainable results from dealing with big data.

If we look at party master data a serious problem with many ERP and CRM systems around is that the data model for party master data aren’t good enough for dealing with the many different forms and differences in which the parties we hold data about are represented in big data sources which makes the linking between traditional systems of record and big data very hard.

Having a Master Data Management (MDM) solution with a comprehensive data model for party master data is essential here.

Some of the capabilities we need are:

Storing multiple occurrences of attributes

People and companies have many phone numbers, they have many eMail addresses…

View original post 213 more words

Ways of Sharing Master Data

Leave a reply

Liliendahl on Data Quality

The ”buy vs. build” option is well known within many disciplines not at least around your IT application stack. The trend here is that where you in the old times did a lot of in-house programming today you tend to buy more and more stuff to prevent reinventing wheel. Yesterday there was a post on that on Informatica Perspectives. The post is called Stop The Hand-Coding Madness!.

We certainly also see that trend when it comes to Master Data Management (MDM) solutions. And my guess is that we will see that trend too when it comes to the master data itself.

What has puzzled me over the years is how a lot of organizations spend time on and makes their personal errors when they type in the name, address and other core data about individuals and companies they do business with or alternatively letting us business partners type in…

View original post 436 more words

Agile MDM Data Modeling and Requirements incorporating a Metadata Mart.(Guerilla MDM)

Leave a reply

Recently an acquaintance of mine asked about my thoughts on the approach for creating a MDM data model and the requirements artifacts for loading MDM.

I believe with this client and with most clients that the first thing that we have to help them understand is that they have to take an organic and evolving a.k.a. an “agile” approach utilizing Extreme Scoping in developing and fleshing out their MDM data model as well as the requirements.

High-level logical data models for customer, location, product etc, such as Open Data Model are readily available and generally not extremely customized or different.

If we look at the MDM aspect of data modeling and requirements, I look at it as three layers:

1.the logical data model.

2.the sub models(reference models) which detail the localization in the sources for each domain, which in essence is our source-based reference models.

3.an associative model that allows for the nexus/mapping between the sources their domain values in the master domain model.

It doesn’t really get interesting until you’re able to dive into the submodel and associative models and relationships creation and understand the issues that you’re dealing with and anomalies in the source data, which inherently involves quite a bit of data profiling and metadata analysis.

In my experience the creation of the MDM model and sub models involves three simultaneous and parallel tracks.

1. Meet with users of and preform metric decomposition(defined in my slides) and define the (information value chain) and create logical definitions of metrics , groupings of dimensions and hire attributes and subsequently hierarchies that are required from an analysis perspective. A data dictionary.

1a. The business deliverable here is a business matrix which will show the relationship between the metrics and the dimensions in relation to the business processes and/or KPI’s. I’ll send an example.

1b. The relationship of the above deliverable to the logical model, is that the business deliverables here specifically the business matrix and the associated hierarchies will drive the core required data elements from the KPI and therefore the MDM perspective. In addition it will drive the necessary hierarchies( relationships) and thereby the associations that will be required at the next level down which will involve the source to target mapping required for loading the reference tables.

1c. Lastly and once we’ve truly understand the clients requirements with enough detail in terms of metric definition and hierarchies we can then select and incorporate reference models or industry experts and validating and vetting our model, this should be relatively straightforward.

1d. From an MDM perspective, where multiple teams are simultaneously gathering information/requirements we would want to use and have access to the artifacts especially for new business process modeling or any BI requirements modeling.

2. Introduce the metadata mart concept and utilize local techniques and published capabilities(I have several that can easily be incorporated) to instantiate a metadata mart and incorporate metadata and data profiling as part of the data modeling process and user awarement process.

2a. The deliverable here is a set of Excel pivot tables and/or reports that allow the users to analyze and understand the sources for domains and how they’re going to relate to the master data model even at logical level

3. Capture relations to defining master domains and documenting in Source to Target Matrixs the required mappings and transformations.

This process will result in a comprehensive set of deliverables that while in their final state will be the logical and physical data models for MDM, in addition the client will have the necessary data dictionary definitions as well as I high-level source to target mappings.

My concern with presenting a high-level logical MDM model to user is that it is intuitively too simplistic and to straightforward. Obviously at the high-level the MDM constructs look very logical for customer, location, products etc..

They don’t expose the complexity and frankly the magic(hard work) from an MDM perspective of the underlining and supporting source reference models and associative models that are really the heart and soul of the final model. The sooner and more immediate that we can engage the client an organized! facilitated, methodological process of simultaneously profiling and understanding their existing data and defining the final state a.k.a. the MDM model that they are looking for the better we’re going to be in managing long-term expectations.

There’s an old saying which I think specifically applies here especially in relation to users and expectations it is “They get what they inspect, not what they expect

I know that this went on a bit long, I apologize I certainly don’t mean to lecture, but I think the crux of the problem in a waterfall or traditional approach to creating an MDM model is overcome by following a more iterative or agile approach.

The reality is that the MDM model is the heart of the integration engine.

Fast, Data Driven Netezza Load – The Prittie Good Approach

Leave a reply

A few years ago I have a client that required migrating and re hosting a data warehouse from the Oracle environment to the Netezza Appliance. The application had hundreds of tables and almost 2900 Source to Target Mappings. This would have taken 1000′s of hours of manual coding. For this client we implemented a reusable data driven architecture that relys on a metadata mart. The end result a greatly reduced TCO for generating the code required vs manual cosing. In addition the process leaves behind a metadata mart to report on for auditing and other Data Governance efforts. We generated over 100,000 lines of code and over 3000 load scripts.After implementation the application can be maintained via the tables and automatically regenerated, providing a pro active or self healing application architecture to respond to changes in incoming source files or target changes. Code Here is a version for Oracle Loader

Prittie Good Approach: The objective is to “tactically” enable the clients analytical and reporting tools to their data via the Netezza platform very fast, with minimal coding effort, but in a “strategic fashion”. The “Prittie” approach will load a client’s data into the Netezza platform as fast and inexpensive as possible while still providing a reusable “production level” set of code for implementation. The approach as defined is to minimize upfront source analyses and data profiling and leverage the “speed” of Netezza , while applying specific coding techniques developed by you to automatically generate a the SQL code necessary to import data into the Netezza environment from industry standard delimited files with headers and a mapping document. The application will build a Metadata Mart for support data driven code generation and as a tactical tool for Data Governance. The primary TCO driver or value add gained with using our approach is to significantly reduced time and cost for an initial load , data profiling and evaluation of data quality, and speed up the process of Business Analysis, as well as leveraging an iterative methodology. In addition business data profiling as well as source analysis can then be accomplished on the Netezza platform, very quickly, while capturing critical load “halting” problems as well as basic profiling metadata immediately and resolving them.

Target: Tables and Columns are derived from parsing machine generated DDL for existing tables, for table columns formats and column order.
Source: Filenames and Column are derived from parsing Header files extracted from industry standard “tab” delimited files with Headers, this provide columns contained in the files and there order.
Mapping: (Source to Target) are derived from parsing the “Bill of Lading” file containing cross reference of multiple filenames to target tables and a grouping column in this case. You original file also had external name this is no longer needed;

Prittie Approach Specific Logic: From these three inputs the application generates Netezza ANSI Standard SQL Code that create an external table with columns ordered by the incoming filename and associated with the DDL Column format, and the associated Netezza logic for external filename allocation and logging. A simple SQL statement is generated that checks for duplicates and data errors , that can be logged. In addition I have includde the ability to generate the Drop and Insert Logic to complete the “Prittie” Import and Load Architecture for Netezza.

Application Stability: The Source and Mapping files design are relatively stable. The Target is based on some of the idiosyncrasies found in the DDL created by the query tool used, with some additional effort this code can be enhanced to handle some “looseness”. The issue is in how the code (DDL) is generated and by what tool. The rest of the application is shielded from this by the creation of a “Source-Target-Mapping Cross Reference” table that is used in the actual code generation.

ETL Potential: As we have discussed the application could be extended easily to handle basic ETL. This could be accomplished by including one more that that contained transformations for specific table columns combinations or data types. An example would be to include a new mapping table that converted Oracle Datetime to ANSI as required. This is a simple example but the underlying design could handle relatively complex transformations. We could also move the Location and Logging Directory to a table to further make the application Metadata Driven.

Doug Prittie and Ira Warren Whiteside

Microsoft BI Stack Leveraged with Vendor Add – In’s for Data Quality and Data Lineage.

Leave a reply

Low cost strategic Business Analytics and Data Quality

This paper will outline the advantages of developing your complete Business Analytics solution within the Microsoft suite of Business Intelligence capabilities, including Microsoft SQL Server(SSIS, SSAS, SSRS and Microsoft Office).

This approach will yield and very powerful and detailed Analytical application focused on your business and it particular needs both data sources and business rules. In addition to a reporting and analytical solution, you will have insight and access to the data and business rule “Lineage” or DNA allowing you click on any anomalies in any dash board and not only drill into the detail, but also see how data was transformed and/or changes as it was process to create your dashboard, excel workbook or report.

Lineage as it applies to data in Business Intelligence provides the capability see track back where data came from. Essentially it supports “Metric Decomposition” which is a process for breaking down a metric (business calculation) into separate parts. With this capability you can determine your data’s ancestry or Data DNA.

Why does this matter? The key to Lower Cost of Ownership, that’s why.

An “Absolute Truth” in today’s environment (Business Discovery, Big Data, Self Service Whatever?) is that you never have enough detail and usually have to stop clicking before you have an answer to your question and look at another report of application. Just when you about to get to the “bottom of it” you run out of clicks As Donald Farmer (VP Product Management) pointed out recently we are hunters and that’s why we like search engines. We enter phrases for things were hunting, and the search engine presents large list which we then follow the “tracks” of what we ae looking for , if we go down a wrong path we back track and continue, continually getting closer to our answer. Search engines implement lineage thru “key words “ and there relationship to web pages, which intern link to other pages. This capability is now “heuristic” or common sense.

While the method of analyzing data is “Rule of Thumb” for internet searches, it is not in terms of analyzing your business reports.

If you were able to “link” all your data from it the original source to the final destination, then would you have the ability to “hunt” through your data the way you hunt with Google, Bing etc…

Lineage Example

ABI Data Lineage

Let apply this concept to customer records with phone numbers (phone1 column). First we clean the phone number (phone1Cleansed column). We keep both columns in our processing results. Then in addition we keep the name of the “Business Rule we used to clean it (phone1_Category), an indicator to identify valid or invalid phone numbers(phone1_Valid) and the actual column name used as a source(phone1_ColumnName). The “phone1_Rule” column is used to tell us which rule was used, the first row used only the Phone Parse rule to format the number, the second row used two rules, NumbersOnly tells us thet the number contains non numeric character and the Phone Parse rule formats the number with periods separating the area code, prefix and suffix.

ABI SSIS validation example with lineage

The end result is when this is included in your reporting or analytical solution you know:

Who: The process that applied the change, if you also log package name(Optional)
What: The final corrected or standardized value(phone1Cleansed)
Where: Which column originated the value.(phone1_ColumnName)
When: The date and time of the change.(Optional)
Why: Which rules were invoked to cause the transformation.(phone1_Category, phone1_Rule and phone1_Valid)

In addition if you then choose to implement data mining and/or “Predictive Analytics” already a part of the Microsoft BI Suite, you will be facing the typical pitfalls associated with trying to predict based on either “dirty data” or highly “scrubbed data”, lacking lineage.

I have assembled a collection of vendors product that when used in conjunction with the Microsoft BI suite can provide this capability at reasonable in line with your investment with Microsoft SQL Server.

If done manually this would require extensive additional coding, however with the tools we have selected, this can be accomplished automatically as well as automating the loading of an Analysis Services cube for Analytics.

Our solution brings together the Microsoft offerings of Actuality Business Intelligence, Melissa Data and HaloBI. This solution can be implemented by any moderately skilled Microsoft developer familiar with the BI Suite(SSIS and SSAS) and does not require any expensive niche ETL or Analytical software.

In the next post I will walk through a real world implementation.

Microsoft SSIS Package

Data Lineage SSIS Example using ABI Profiling, Melissa Data , SSIS Fuzzy Groupng, SSAS cube genertion

Actuality Business Intelligence SSIS Profiling

ABI SSIS Data Profiling

Melissa Data Contact Verify

Overview

Basic EIM

Master Data Services

Nick Barclay: BI-Lingual: MDS Architecture Notes

Nick Barclay: BI-Lingual: Beginning Master Data Services (Part 1 thru 7)

Data Quality Services

Enterprise Information Management using SSIS, MDS, and DQS Together [Tutorial]

Fuzzy Matching and Deduplication

Advanced SSIS Fuzzy Matching via Record Linkage Methodology – SQLServerCentral

Advanced Matching and Data Profiling

Roll Your Own Fuzzy Match / Grouping (Jaro Winkler) – T-SQL – SQLServerCentral

Roll Your Own SSIS Fuzzy Matching / Grouping SSIS (Jaro – Winkler) – SQLServerCentral

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?

Loading Data (Batch)

Low cost strategic Business Analytics and Data Quality

Why does this matter? The key to Lower Cost of Ownership, that’s why.

Lineage Example

Actuality Business Intelligence SSIS Profiling

Melissa Data Contact Verify