Guldmann Fumbles with Master Data

News just in… Microsoft has purchased Zoomix, a data quality software vendor. This is truly interesting while Zoomix will contribute with the missing data improvement layer to the Microsoft Sql server data Platform. By this expanding Microsoft already ongoing MDM effort (stratature/bulldog/+EDM)

I found it intriguing reading Kirks Haseldens blog on the Data Profiling abilities efforts in SQL Server 2008. It seems that SSIS and SQL server is going to bridge Microsoft’s effort to integrate Zoomix and +EDM, I find it fair to say this is really interesting news, seen in a MDM perspective.

Guldmann

When venturing into any data quality program, is data profiling an essential cornerstone to turn. It provides a wealth of information about the data that you have. A master data repository must hold functionality to automatically identify data quality issues in a more than one way. Common for needed profiling plug-in is the requirement to drill trough to the very entries causing the anomalies, and the very same data must facilitate KPI reports over the same.

 Basic statistics, frequencies, selectability, data patterns, ranges and outliers.

Through patterning the content of the attributes the possibility to detect a vararity of condition emerges. Say Postal code is mostly created with two chars indicating the country, followed by a space and then 4 numeric chars indicating the postal area, consider the mount of information deriving from patterning of this information.

e.g.

This patterning makes it easy to query on, combined with the knowledge of how many times the pattern emerges, the count of spaces and NULL values, the max length, min length, average length makes the foundation of any data analysis.

Looking into the attributes selectability gives strong indication if the attribute is a candidate for a unique business key.

Numeric range analysis provides knowledge about utilization.

Data patterns gives info regarding which entries doesn’t applied with a given mask

Datatype selection

This example is boiled down, naturally there will be far more data types and attributes in a real life scenario.

Data Scrubbing is a technical term used when correcting data towards a better data quality. I believe the term originates from the data warehouse world. It is actually the same as the meaning of data cleansing in MDM. The process of heightening data quality can be everything from detecting, removing, and/or correcting the dirty data in a database. Dirty data can be missing, incorrect, out-of-date, redundant, incomplete, or formatted incorrectly.

The reason for low quality of data can be found in everything from the result of human error in entering the data, the merging of dispersed systems. Lack of company wide standards and lack of governance takes, or due to old systems containing deprecated data.

The data scrubbing program belongs as a plug-in in the MDM repository used to clean the repository. But oddly enough is it mostly implemented in the ETL jobs transporting data to between dispersed systems or even into the Enterprise Data Warehouse. So instead of implementing this facility one place it is implemented redundantly, and with implementations of diverting quality, causing even more inconsistencies.

Should repositories hold history, my initial opinion would be why not. Seeing the changes over time might be useful in supporting applications especially with a not clean cut uniquely identified scenario with a distributed system of entry. Utilizing Type 2 history in the mapping between the business keys and the repository will yield a lot of useful information, synchronizing wise.

In a analytical MDM context, Dimension creating would also have benefit of such a secure source of history.

I don’t really buy the argument of this meaning an explosion in the amount of data. After all, only some 30% of customers have changes to their contact info a year. This should indeed be manageable. Should the amount of data be an issue, then this is one of the situations where I see Dan E. Linstedt's Data Vault Data Modeling thoughts being utilized.

Just by goggling Master Data Management one can see a growing list of vendors and advisors. I guess the future seems clouded on the area. I’ve personally always believed in writing MDM applications bottom up instead of buying a full featured suite, while the price of these often wasn’t balanced with their functionality. However products like Stratature/Kaliedo shift this picture somewhat. They are products making it easier to convince the business that they can afford MDM

Designing Dimensions and hierarchies is an important task when we do BI work for our clients. Later on this work ends up in a mockup cube. It is then the users sees how the hierarchies unfold and collapses, and it’s like a more detailed understanding of what they are getting sinks in, and quite often… its rework time.

I think EDM has the same capabilities as the cube mockup. Being as visual as it is, makes it easy to understand super domain master data.

MDM Methodology

Data Modeling vs. Master Data Models

What communicates the better

The import process validates the data which is due for import. AS-IS Complex rules in +EDM is parsed row by row and is unacceptable slow. This is not at all agile enough to accommodate enterprise needs, where data should drip in to the stewards in near real-time. Microsoft is looking into improving this area. Personally I am looking forward to test this improvement a lot while I see it as critical when utilizing EDM in connections with external systems, especially when SOE is distributed.

When I first saw Stratature one of the things that caught my eye was its strong hierarchy management capabilities. This was very exiting while, in my time as a BI consultant I have encountered a lot people who were spending a significant amount of their time managing hierarchies in spreadsheets. Not only is it a time consuming task to maintain these, but also often error-prone while inconsistencies build up when more than one person access and maintain them. +EDM offers a fully featured approach to maintain ragged as well as fixed hierarchies, and with that yet another reason (and by all means let’s get more reasons) to use excel as a data source is deprecated.

Having received the outline of where Microsoft is taking their very exiting acquisition, and tried the product in a prior beta version (+EDM), I feel the urge to write a little about +EDM and its connectivity. How agile it is communicating with the surrounding systems on the corporate platform.

It seems obvious when using the +EDM that it’s designed for having SOE/SOR very centralised. Should one strategy be otherwise, it does offer views exposing data out of the repository, And likewise for data input it offers a table where one can batch load data from. In this blog I will try to elaborate why I believe it will fall very short on this front.

Before data can be exported out from +EDM it has to meet the rules defined. And this thought is good however. In the current state of +EDM executing rules is very slow rules while they are row based. With a surprising low amount of rules and not that many rows it takes very long time to validate your data. This cripples us building up complex rules determining the completeness level of data, and basing or determination of synchronization on this.

Mapping to external systems, like SAP seems very primitively supported. By adding the Business key from SAP as an attribute in your MDM it allows the user to manually maintain a simple link to your CODE which is the +EDM internal key.  What about gearing in hierarchies? Is a manual procedure here not error prune?

Complex workflow where data has to be validated by various people seems likewise hard to implement. However this might be possible with changing rights on data. Still it seems very crude and not agile enough for a modern MDM workflow setup.

Most annoyingly is +EDM batch input/output strategy. Near real-time is what most modern MDM synchronization strategies sough’s after. Having a table where data may or may not be imported from and reading the status of the import is really inflexible.

This sounds dreadful negative, but it really isn’t. +EDM is a very exiting product which really addresses a lack in the Microsoft suite when it comes to MDM. Taking into account this is a prior beta version I’ve been testing out I am sure when the final product reaches RTM it will be much improved on this front.

CREATE PROCEDURE [dbo].[FieldUse]

@field sysname,

@value sysname

AS

BEGIN

DECLARE @SQL NVARCHAR(MAX)

SET @SQL=''

SELECT @SQL=@SQL+'SELECT '''+s.NAME+''' AS [Schema],'''+t.NAME+''' AS [Table], '''+COALESCE([collation_name],'')+''' AS [collation_name] ' +',COUNT(*) AS Occurrences FROM ['+s.[NAME]+'].['+t.NAME+'] WHERE ['+@field+']='''+@value+''';'

FROM sys.columns c

JOIN sys.tables t

ON c.object_id = t.object_id

JOIN sys.schemas s

ON t.schema_id = s.schema_id

WHERE c.name = @field

ORDER BY s.name ,t.NAME;

EXEC sp_executesql @SQL

END

This I am visiting Microsoft in Redmond attending MDM sessions with the Stratature team.

Their product looks really promising especially when it comes to hierarchies.

In the upcoming time I will be musing on the topic.

When a company chooses MDM Repository, I have often been surprised. There is enormous focus on MDM holding master data and Hierarchical data. Which Framework does this best which can hold most values? How easy they are when a new attributes is needed. I never quite understood this focus.  I mean, how hard is it, to write Create or Alter Table? Choosing a MDM Repository should in my view be made out from quite a more complex set of criteria’s.

·         How well does it integrate with a service orientated architecture.  Are we talking about Batch replications or a more sought after near real time push of data?

·         Does it Map business keys with Repository Keys? This has much relevance when feeding data back to the external systems with as little impact at them as possible.

·         Can it be Meta Modeled? Or do we have to accept certain limitation in terms of attributes?

·         Which Services does it feature?

o   Data washing against public repositories

o   Doublet spotting with phonetic functions and group 

o   Completeness degree reporting?

o   Harmonization of records and values?

o   Constraint and translation of values?

o   Etc.

·         How well is centralized Data entry (SOE) handled? Does it allow new attributes to be edited? Do it enforce constrains when editing Hierarchical data. Security is that taken care off?  who can change what, who sees what, single sign on and workflows?

There are endless more considerations to make when choosing a repository. Rather than focusing on just values.

Which Attributes goes into a MDR Entity

Answering this question has been and is one of the tougher nuts to crack for me.

·         Seen from the distributed systems data is separated into Domain Data, which really has no relevance to others and General data which more than one system could benefit from.

·         Seen from a Centralized System of Entry point of view should the amount of data driving the process be centralized in the MDR.

·         The user point of view is that he really does not want to be troubled with data that is not relevant for his domain operation.

·         The entity owner’s point of view should data be entered in as few steps as possible and involving the least amount of people necessary and with the strictest possible governance.

·         Seen from our customer’s point of view should the attributes reflect and correspond to general definitions to on the marked.

I guess answering this can only be putting aside most arguments and taking the business point of view and combining it with the Data Responsible finding a balance.

In writing…

System of Reference and Entry, where do we get our data, and where should we put it

The ambition of data consolidation in an MDM scenario must be balanced to the maturity of the organization.  Distributed systems, the organization often are not ready for the sought-after big bang centralization. The Strategy can be divergent from Entity to Entity, however 2 factors has to be set always. Where should data be born (System of Entry SOE) and where should the consolidated data be read from (System of Reference SOR).  

SOE – System of Entry

SOE handles designating the origin of entry entity data, often with attributes disunited over a number of systems. The actions entities can undergo are also assigned under SOE. Where is the entity Initiated, where does it die, and who controls the workflow.

Rule

“Origin of attributes and actions must be uniquely determined to avoid inconsistencies.”

SOR – System of References

SOR is more controversial. As with SOE data could be distributed over a number of systems. However it is my experiences that this is a mine filled road, only leading to chaos and inconsistencies.  A centralized repository as SOR is for my experiences the only safe road. Here a Consolidated picture can be sought and argued with strong rules to enforce diversities in hierarchies and data.  No matter what implementation level the ambitions amounts to is it imperative that the realization is compliant with a hub and spoke service orientated architecture. Otherwise the MDR scenario will only add to the Asian noodle soup.