MDM process start to finish.

Question

Hi,

 

I have been working with MDS at my internship, trying to figure out how our DBAs and software developers could use this tool.   While I understand how to accomplish the various MDM tasks via the web application, I was wondering how these processes could be automated. 

 

This is what I know so far:

1. SSIS loads data into the staging tables using the mdq functions to cleanse the data.  Then udpStagingSweep procedure is used to load the data into the MDS data model.

 

2. If a view is available the newly loaded data appears in the view.

 

My questions: how does the data get validated against the business rules (automatically) before being loaded into the subscription view? Can you prevent invalid data from being loaded into the subscription view until the issue is resolved?

 

In my research I haven't seen any full usage scenarios that documents automation of the entire MDM process.  Any information you can provide to me regarding this would be much appreciated.

 

Note that I am not a DBA or developer.  I am willing to view technical details, but bear with me if I am confused at all.  Looking forward to your input.

 

-Nick

Answer 1

Hi Nick,

If you want to "automatically" run validation you can execute the validate model stored procedure.  You would just want to make sure the staging process is complete before you run validation.  The following post describes how to invoke the validation process externally via the stored procedure:

 http://social.msdn.microsoft.com/Forums/en-US/mdmintegration/thread/e7436e70-7753-4ec6-a982-0d295935fe89/#e2f66938-c411-4a07-b5df-e74853fd23f7

The following are 2 different approaches you could use for controlling data in subscription views. 

1)    Use the versioning capability and a version flag that can only be assigned to a committed version.  A version can only be committed if all members are valid:

Create a version flag (i.e. "Current" for our example) and specify that the flag can only be assigned to “committed” versions.

Create your subscription view specifying the version flag (i.e. “current”).

When you are ready for a version to be used as the current version, you would commit the version and then flag that version using the “current” version flag.  The version cannot be committed if there are any invalid members.  Note that once a version is committed, it cannot be edited. This ensures integrity for subscribing systems, since the data cannot be modified once a version has been used by a subscribing system. If you want to make changes to the master data, you would copy the version to create a new “open” version ready for update.

You will find that for master data such as financial chart of accounts structures or profit/cost center and organizational structures that drive monthly reporting processes, this versioning approach is typically the desired process to follow.

2)    If you want to just prevent any invalid members from appearing in the subscription views, you could select only the valid members from the subscription view (or create a SQL view using the subscription view and just select the members where ValidationStatus=”Validation Succeeded”).  Just be aware with this approach that you are not including all members, so the completeness of data is not ensured and while this data is being used in subscribing systems, changes can be made to the same version of data in MDS since the version is not in a committed state.

The approach would depend on the type of master data and your requirements for control over the data and its use in subscribing systems.

Regards,

Pam