Question

Hey there, folks,

I have decided to add a MDS using SQL Server Master Data Services 2012 to an architecture I am working on. I have run into considerable confusion because I am not sure how to put the ETL process. I have two ideas, which I've listed below.

Number one: Between Staging Area and DV. Doing it this way, data from DV will be extracted by the ETL process and will be loaded into the MDS. Afterwards, data from MDS will be extracted and loaded into DV.

Number two: Between DV and Dimensional Data Store. The ETL will extract all relevant data from DV and load it to MDS before extracting it from MDS and loading it into DDS.

Which option do you think is the best?

Answer 1

In general, Master Data, the managing of data in an organization, originates downstream from the data warehouse or Data Vault—Data Vault is a means of integrating data from source systems via its business keys. After the data has been christened master data by the business, you should make it an up-stream source system for the staging areas. When you properly implement data solutions, all source systems will align, in real-time, to the reference data. They'll become a system of record and be reference data themselves.

Answer 2

The question is, how do you plan to use MDS? MDS is a means of organizing data. I don't have any prior experience with MDS, but from what I can tell, it is something that you want to use as a source to the DV, not as a direct feed to the data marts.

Comments

I thought you should directly feed data marts because of the schema of a MDS. It's quite different from DV.

Answer 3

I have used MDM solutions with the DV architecture in the past. From what I can tell, it sounds as if you are viewing the Customer MDM domain and not the product. In my opinion, the MDM model is the right thing to use as your MDM hub. However, you will need to consider the overall purpose of the MDM and determine that for yourself.

Here's another suggestion that fits right between your option one and option two. You add another stage after the DV but before the Business DV layer. The DV will feed the MDM when you also need Governance Data Stewards involved with merge and purge actions. This way, the MDM will be in charge of the customer number. As a result, you will need to feed the MDM to the DV as well as any and all applications.

As the MDM will be creating and managing the persistent customer number, DV will not need to do that job.