Category Archives: Computers and Internet

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

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

Melissa Data Contact Verify

SQL Saturday New York #158

Leave a reply

Last Saturday August 4, 2012 we attended SQL Saturday #158 in New York.

We (Tessie, Victoria and Brandon) were there educating folks on SSIS Data Quality for Melissa Data.

For Actuality Business Intelligence and Melissa Data the event planning staff executed a great event and very well organized. Here is a video of Tessie and Victoria(Daughter) in action.

All in all we had a full day of interacting with SQL Saturday New Yorkers and also  giving  presentation Advanced Fuzzy Matching(TSQL, SSIS and MDS).

Last we also brought our grandchildren(Julia, Jack an Jake),  and they were excited to have the chance to begin the processes of becoming future BI Consultants.

Secondofly here is a video of my grandaughter(Julia) reminding me from here persepctive  “I’m a pretty princess, not you”

Thirdofly Julia and Tessie with Lady Liberty.

Master Data Services – SSIS MDM Load

Leave a reply

 

 

Master Data Services – SSIS MDM Load

 

Microsoft recently released Master Data Services in SQL Server 2008 R2 CTP. Without getting into the strategic benefits, definitions etc. of MDM(Master Data Management) this tool will be very helpful for any application in automating the setup and maintenance of look up or cross reference tables or utilizing reference data.

 

  

Kirk Haselden has provided a white paper on The What, Why, and How of Master Data Management

 

 

 

Nick Barclay: BI-Lingual has provided an excellent post on getting started with this tool. In his post he provide a walkthrough on how to set up a sample Geography Model and the associated Entities, Attributes and Hierarchies. In addition he provides the TSQL to load them. I have expanded on his work and provided a basic SSIS Package for loading the Model.

 

 

 

 

 MDS_StagingLoadBarclayProject.dtsx

The approach was to use derived column transforms to set up the required metadata and aggregate transform to generate distinct results. Obviously Nick has done all the heavy lifting I thought it would be helpful to have an SSIS Package to compliment his very informative post.

 

 

 

Last I intentionally kept this package basic, they are many opportunities for improvement and making the entire process data driven, which I will explore next.

 

 

 

An additional reference for understanding MDM is David Loshin’s book.

 

Ira Warren Whiteside

Actuality Business Intelligence

 

 

"karo yaa na karo, koshish jaisa kuch nahi hai"

"Do, or do not. There is no try."