How to Import, Browse And Export Master Data Services' Data

October 7, 2010

Learn how to import data to Master Data Services (MDS) using Master Data Manager Portal UI to enter data manually or how to automate the import process by staging data into three different MDS staging tables

Introduction

Master Data Services (MDS) is a master data management
platform, which allows you to create a centralized hub for your master data.
This single and centralized authoritative master data source provides
consistent master data to all the business applications aligned to it. In this article,
I am going to demonstrate how you can import data to MDS, browse it and export
it via subscription views for consumption by reporting and analytical
applications.

Note: In this article, I am going to discuss the data
handling part of MDS, if you want to learn about creating MDS objects, refer to
my other article Creating Master Data Services Objects on this subject.

Master Data Import into MDS...

There are two different ways to import data into MDS. First,
you can use Master Data Manager Portal UI to enter data manually; second, you
can automate the import process by staging data into three different MDS
staging tables as discussed below and finally running import batch process to
push data to MDS objects from these staging tables in a batch. (There is a third
way to do it programmatically but that is not in the scope of this article.)

mdm.tblStgMemberAttribute
 This table is used for three different purposes, to update an attribute value
of existing member and collection, to delete a member or collection and to deactivate/reactivate
members or collections. To learn more about this staging table click MSDN: Attributes Staging
Table (Master Data Services).

mdm.tblStgBatch
 This table is used by MDS itself to capture the status of processing of each
batch of staged data, for example number of members and attributes in batch
processing, batch processing start and end time, etc.

In the example below, I am going to use the same
Product model, which comes with MDS as a sample. In this example, I am creating
a member for the ProductCategory entity, two members for the ProductSubCategory
entity and another two members of Product entity. Then I will be adding
different attribute values to these members along with the domain-based
attribute, which defines the relationship between these entities.

Run the above scripts to stage your data into the MDS
staging area. Next, you need to run the batch process to import these data from
the MDS staging area to the MDS database. Go to Master Data Manager UI, select the
appropriate model and version and click on Integration Management (depending on
your permission level this option might or might not be visible to you; for
more detail about security and permission refer to my next article on this
series) option as shown below:

Figure 1 - Integration Management

In the Import screen, there will be two sections; the first
one shows different staging batches run/queued so far and next one shows the
un-batched staging records details. Select the appropriate model and version
and you will see numbers of members, attributes and relationships pending processing.

Figure 2 - Batch Processing 1

Click on the Process unbatched data icon and MDS will
queue the batch for processing as you can see below. Once MDS completes
processing the batch, you can see the time taken in processing, number of
records processed and number of records failed in processing as shown in the
next image.

Figure 3 - Batch Processing 2

Figure 4 - Batch Processing 3

Browsing data into MDS...

Browsing data is quite simple and straight forward. Go
to Master Data Manager UI, select the appropriate model and version and click
on Explorer (depending on your permission level this option might or might not
be visible to you. For more detail about security and permission refer to my
next article on this series) option as shown below:

Figure 5 - Explorer

Next,
select the entity (all entities of the selected model/version will be listed
here) to browse through its data from the Entities menu on the top menu bar.
You can even browse through the data using the hierarchies created in the model.

Figure 6 - Data Browsing

Figure 7 - Browsing ProductCategory Entity

If the member list grows longer, you can specify the
filter criteria to apply filter/narrow down the list on this screen.

Figure 8 - Browsing ProductSubCategory Entity

You
can select a member and edit or delete it as per your need from the UI itself.
You can even add a member by clicking on the + sign on the left. Notice the
two product members, which we imported from the staging table here.

Figure 9 - Browsing Product Entity

Master Data Export from MDS...

MDS allows creating subscription views to consume
master data from the MDS database. Reporting and analytical applications
consume these subscription views to get access to the master data.

Go to the Master Data Manager UI, select the appropriate
model, version and click on Integration Management (depending on your
permission level this option might or might not be visible to you. For more
detail about security and permission refer to my next article on this series).
Click on Export menu as shown below and finally click on the + (Add
subscription view) icon to create a subscription view.

Figure 10 - Creating Subscription View 1

On
the next screen, you will be specifying different options for your subscription
view, like name of the subscription view, model, version, entity, etc. to base
this subscription view on, as shown below. Finally click on the save button to
create the subscription with the defined definition:

Figure 11 - Creating Subscription View 2

You
can verify the subscription view by going to SQL Server Management Studio,
connecting to MDS database and running this script:

SELECT * FROM mdm.ProductView
WHERE name IN ('LadyBird 100', 'Hercules 100')

Conclusion

Master
Data Services (MDS) is a master data management platform, which allows you to
create a centralized hub for your master data, which behaves like a single
authoritative source for your master data.

In
this article, I discussed how to import data into MDS, browse through it and
finally how to export master data by creating subscription views, which will be
consumed by reporting and analytical applications.

In
my next article, I will be talking about MDS versioning and security.