Importing Data Into Master Data Services 2012 – Part 3

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

Part 1: Overview of the Staging Process

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:

1. 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.
2. 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.

Finding More Information

Part 1 of this series (Overview of the Staging Process)

Part 2 of this series (discusses Loading a Model Where All Attributes Come From a Source System)

MSDN – Importing Data (Master Data Services)

MSDN – Exporting Data (Master Data Services)

Comments

[hxy0135]

Hi,
I am studying MDS and hope we can use it in our dimension management. I tried several models. It seems to me the tool is only good for manage entities having small dataset. using it to manage a entities having a bigger data set having close to1 million rows is really cumbersome: in UI, if a member is not shown on the screen, it is too hard to find that member in the entity. No search feature can be used to search for the member? And if I tried to publish my change via Excel add-in, I always get “The validation cannot validate more than 1000 rows.”

did I have missed any information to get that impression?

Thank you .

[Melissa Coates]

The MDS filters are good to narrow down your results (ie, the search you're looking for). Also look into Attribute Groups - they may help you a lot.

[hxy0135]

thank you for your reply!
When you say "MDS filters ", do you mean the filter button on MDS tab of Excel add-in?

[Melissa Coates]

I was referring to filters in Master Data Manager (the web interface). Go to Explorer on the main menu, then choose an Entity. You'll see the Filters in the menu towards the top.

[Bill Anton]

Melissa, great series - thanks for sharing!