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

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, '')

MSDN:
Staging
Error Messages (Master Data Services)

»


See All Articles by Columnist
Arshad Ali