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

October 7, 2010

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.tblStagingMember – This table is used to create leaf, consolidated members, and collections for your model/entities. To learn more about this staging table see MSDN: Members Staging Table (Master Data Services).
• 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.tblStgRelationship – This table is used for either to add members to collections or to move members between explicit hierarchies. To learn more about this staging table, see MSDN: Relationships 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.

/* Add a member in ProductCategory entity */
INSERT INTO mdm.tblStgMember ([Batch_ID], [UserName], [ModelName], [HierarchyName], [EntityName], 
	[MemberType_ID], [MemberName], [MemberCode], [Status_ID], [ErrorCode])
VALUES (NULL,NULL,'Product',NULL,'ProductCategory', 1, 'Bicycle', 5, 0, '')
/* Add an attribute to ProductCategory entity to relate this member with top level ProductGroup entity 
	which has two members Wholesale and Retail */
INSERT INTO mdm.tblStgMemberAttribute([Batch_ID], [UserName], [ModelName], [EntityName], 
	[MemberType_ID], [MemberCode], [AttributeName], [AttributeValue], [Status_ID], [ErrorCode])
VALUES (NULL, NULL, 'Product', 'ProductCategory', 1, 5, 'ProductGroup', 2, 0, '')
 
/* Add a member in ProductSubCategory entity */
INSERT INTO mdm.tblStgMember ([Batch_ID], [UserName], [ModelName], [HierarchyName], [EntityName], 
	[MemberType_ID], 	[MemberName], [MemberCode], [Status_ID], [ErrorCode])
VALUES (NULL,NULL,'Product',NULL,'ProductSubCategory', 1, 'Bicycle for male', 39, 0, '')
/* Add an attribute to ProductSubCategory entity to relate this member with ProductCategory entity member 
	which we created above */
INSERT INTO mdm.tblStgMemberAttribute([Batch_ID], [UserName], [ModelName], [EntityName], 
	[MemberType_ID], [MemberCode], [AttributeName], [AttributeValue], [Status_ID], [ErrorCode])
VALUES (NULL, NULL, 'Product', 'ProductSubCategory', 1, 39, 'ProductCategory',  5, 0, '') 
/* Add a member in ProductSubCategory entity */
INSERT INTO mdm.tblStgMember ([Batch_ID], [UserName], [ModelName], [HierarchyName], [EntityName], 
	[MemberType_ID], [MemberName], [MemberCode], [Status_ID], [ErrorCode])
VALUES (NULL,NULL,'Product',NULL,'ProductSubCategory', 1, 'Bicycle for female', 40,0,'')
/* Add an attribute to ProductSubCategory entity to relate this member with ProductCategory entity member 
	which we created above */
INSERT INTO mdm.tblStgMemberAttribute([Batch_ID], [UserName], [ModelName], [EntityName], 
	[MemberType_ID], [MemberCode], [AttributeName], [AttributeValue], [Status_ID], [ErrorCode])
VALUES (NULL, NULL, 'Product', 'ProductSubCategory', 1, 40,'ProductCategory', 5, 0, '') 
 
/* Add a member in Product entity, this member will be part of 'Product Management' hierarchy */
INSERT INTO mdm.tblStgMember ([Batch_ID], [UserName], [ModelName], [HierarchyName], [EntityName], 
	[MemberType_ID], [MemberName], [MemberCode], [Status_ID], [ErrorCode])
VALUES (NULL,NULL,'Product','Product Management','Product', 1, 'Hercules 100', 'Hercules100',0,'')
/* Add an attribute to Product entity to relate this member with ProductSubCategory entity member 
	which we created above and also specify some of the other attributes values for this member*/
INSERT INTO mdm.tblStgMemberAttribute([Batch_ID], [UserName], [ModelName], [EntityName], 
	[MemberType_ID], [MemberCode], [AttributeName], [AttributeValue], [Status_ID], [ErrorCode])
VALUES (NULL, NULL, 'Product', 'Product', 1, 'Hercules100', 'ProductSubCategory', 39, 0, '') 
INSERT INTO mdm.tblStgMemberAttribute([Batch_ID], [UserName], [ModelName], [EntityName], [
	MemberType_ID], [MemberCode], [AttributeName], [AttributeValue], [Status_ID], [ErrorCode])
VALUES (NULL, NULL, 'Product', 'Product', 1, 'Hercules100', 'SafetyStockLevel', 100, 0, '') 
INSERT INTO mdm.tblStgMemberAttribute([Batch_ID], [UserName], [ModelName], [EntityName], 
	[MemberType_ID], [MemberCode], [AttributeName], [AttributeValue], [Status_ID], [ErrorCode])
VALUES (NULL, NULL, 'Product', 'Product', 1, 'Hercules100', 'DaysToManufacture', 10, 0, '') 
 
/* Add a member in Product entity, this member will be part of 'Product Management' hierarchy */
INSERT INTO mdm.tblStgMember ([Batch_ID], [UserName], [ModelName], [HierarchyName], [EntityName], 
	[MemberType_ID], [MemberName], [MemberCode], [Status_ID], [ErrorCode])
VALUES (NULL,NULL,'Product','Product Management','Product', 1, 'LadyBird 100', 'LadyBird100',0,'')
/* Add an attribute to Product entity to relate this member with ProductSubCategory entity member 
	which we created above and also specify some of the other attributes values for this member*/
INSERT INTO mdm.tblStgMemberAttribute([Batch_ID], [UserName], [ModelName], [EntityName], 
	[MemberType_ID], [MemberCode], [AttributeName], [AttributeValue], [Status_ID], [ErrorCode])
VALUES (NULL, NULL, 'Product', 'Product', 1, 'LadyBird100', 'ProductSubCategory', 40, 0, '') 
INSERT INTO mdm.tblStgMemberAttribute([Batch_ID], [UserName], [ModelName], [EntityName], 
	[MemberType_ID], [MemberCode], [AttributeName], [AttributeValue], [Status_ID], [ErrorCode])
VALUES (NULL, NULL, 'Product', 'Product', 1, 'LadyBird100', 'SafetyStockLevel', 100, 0, '') 
INSERT INTO mdm.tblStgMemberAttribute([Batch_ID], [UserName], [ModelName], [EntityName], 
	[MemberType_ID], [MemberCode], [AttributeName], [AttributeValue], [Status_ID], [ErrorCode])
VALUES (NULL, NULL, 'Product', 'Product', 1, 'LadyBird100', 'DaysToManufacture', 10, 0, '')

Download above code.

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.

References

MSDN: Staging Process Workflow (Master Data Services)

MSDN: Staging Error Messages (Master Data Services)

» See All Articles by Columnist Arshad Ali