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


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

ntegration Management

MSDN:
Staging
Error Messages (Master Data Services)

»


See All Articles by Columnist

Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Latest Articles