Welcome to Part 3 of a 3-part series on using the new Entity-Based Staging Process in Master Data Services (MDS) 2012.

Part 2: Loading a Model Where All Attributes Come From a Source System

Part 3: Loading a Model Where Some Attributes Are Maintained Directly in MDS

Sample Model

This builds upon the model built in Part 2 (so please review that blog entry if you’ve not done so already).

Conceptually the Account Model looks like this:

The Account Entity in MDS looks like this:

In Part 2 we assumed that all of the attribute values come from a source system. However, in Part 3 we are changing that up a little. We are going to say that the Account Type is maintained directly in MDS.

When MDS is the System of Record

One of the most common use cases for MDS is to augment the data which comes from your source system(s) with additional context. This could be groupings or descriptive information not stored elsewhere.

For purposes of Part 3, the Account Type entity is maintained in MDS only. When an attribute is maintained directly in MDS, we need to alter the process described in Part 2 just a bit to ensure the values are preserved during the import process.

In Part 2 we said all the values come from the source. Put another way, all data is pushed to MDS. Conceptually, that looks like this:

However, if one or more values come from MDS, we need to add a step to retrieve those values. Otherwise, we’ll lose them. Two reasons for this:

If we use an ImportType of 2, which allows updates to occur for existing records, then a null value in the staging table will overwrite an existing value.
The stored procedures provided to populate an MDS Model are not parameterized to allow us to specify which attribute(s) are loaded. Since all attributes are part of the import process, we need to make sure all data is present in staging to preserve the existing values.

Think of it this way: We need to retrieve the values out of MDS – as if it were any other source system – to load staging before the stored procedure executes. Conceptually, that looks like this:

SSIS Package

We’re going to use the same SSIS Package from Part 2. (Below only shows the modifications to it, so please refer back to Part 2 for any details not listed here.)

Note the same 5 steps are present. Loading the Account Type Entity is not present in the SSIS package, because it’s maintained directly in MDS.

Step 2 is where the important change occurs. Within the data flow for the Account entity, we need to query MDS to pull back the existing values for Account Type. If we skip this step and leave it null in staging, those null values will indeed overwrite the existing values because we’re using an ImportType of 2.

How you retrieve the data out of MDS depends on your personal preference. In this example I used the Lookup data flow transformation in SSIS. I don’t want to do a cross-database join in my source query, so I matched up the data from MDS after the source data is in my SSIS pipeline.

Prerequisite for this step: A subscription view must exist for the entity to export the data out of MDS. A subscription view is what’s used to extract data out of MDS. Another reminder we’re not to interact directly with the MDS source tables. In my situation, I want a subscription view for the main Account entity – this is where the accounts are actually mapped to the values. The Account Type entity is just a distinct list of lookup values, and doesn’t give me the mappings to actual accounts.

And that’s it! Once you know to identify the source of all MDS attributes before you begin creation of a staging package, then it’s smooth sailing from there on.