Beginning MDS - Loading Members & Attributes (Part 5 of 7)

Now that we’ve defined some basic structures we can add some data. Through MDS' web based management interface we can manually add individual members or configure attributes on an entity one-by-one, or we can load them en masse. I'll leave the one-by-one method to the reader to figure out. What most will want to know is how to get a lot of data into the system in one hit.

For those familiar with the product formerly known as PerformancePoint Services 2007 Planning, the process of batch loading records is much the same. You insert the data to be loaded into system-defined staging tables, ensuring the appropriate metadata is defined on each record. MDS internal stored procedures are run over the staged data to check the validity of the records in accordance with the entity & attribute structures that have been set up. Each record is marked with a flag and an error code to show whether it has passed or failed validation and provides details as to why. Once validated and error-free the data can then be loaded into the appropriate area within MDS. Action can also be taken on the bad records in order to get them loaded too.

Walkthrough

In this walkthrough we're going to:

• Load leaf members into the City, StateProvince and CountryRegion entities
• Load related StateProvince attributes into the City entity
• Load related CountryRegion attributes into the StateProvince entity
• Load freeform Spanish and French country names into the CountryRegion entity

Open up SSMS and connect to your MDS repository database (whatever you've called it). Mine's called "MDS".

Run the following TSQL to insert data into the mdm.tblStgMember table (I am assuming you've got the AdventureWorksDW2008R2 database installed on the same SQL instance)

-- insert City-grained entity members into tblStgMember   INSERT INTO mdm.tblStgMember   (           ModelName         , EntityName         , MemberType_ID         , MemberName         , MemberCode   )   SELECT         'Geography'       , 'City'       , 1       , City       , StateProvinceCode + '_' + UPPER(SUBSTRING(City,1,4)) + '_' + PostalCode   FROM   AdventureWorksDW2008R2..DimGeography

If you wish you can have a look at the inserted records by running the following

SELECT *     FROM   mdm.tblStgMember

Now that we know the data is in the staging table we can kick off the batch load process. Browse to the Master Data Manager web page and select the Integration Management option ensuring that you select Geography in the Model dropdown and VERSION_1 in the Version dropdown.

On the Import page (with the appropriate Model and Version selected in the corresponding dropdowns) note that there are 655 total member records that are flagged in the Unbatched Staging Records section. Click the Process button located above the Model dropdown.

Now the Staging Batches area at the top of the page comes to life showing that a new staging batch instance has been spun up for our 655 records. In the background the loading process is already running to validate our 655 records.

You can check on the status of the batch by clicking on the batch line item and then clicking the View details for selected batch button. The end-to-end process should take a few seconds.

Note that if you check the loaded records in SSMS (the mdm.tblStgMember table) the Status_ID field should be populated with a 1 indicating success. The ErrorCode field will be populated with “ERR210000” which I guess must mean “success” (doesn’t seem to be any doco on these codes at the time of this writing).

UPDATE (Nov 26th): MDS PM Kirk Haselden has listed all the staging table error codes here.

Once the batch has loaded we can check on the new members. Browse back to the Master Data Manager page by clicking on the MDS logo in the top left of the screen and select the Explorer  option.

Select Entities > City to view the members that have been successfully loaded.

Note the yellow question marks next to each record. This means that business rule validation has not yet been run against these members. We won’t worry about business rules for the purposes of this walkthrough, you can ignore the yellow question marks for now.

If the question marks are really annoying you press the Apply Business Rules button (the one with the green check mark) to change them into green checks. We haven’t created any business rules to be applied so the change here is really only cosmetic in the context of this walkthrough.

Now we need to load members into the StateProvince and CountryRegion entities using the same process, just different TSQL. Once you have run the code to stage the members go back to the Integration Management screen kick off the batch process to load the members into their corresponding entities.

StateProvince

-- insert StateProvince-grained entity members into tblStgMember  INSERT INTO mdm.tblStgMember   (           ModelName         , EntityName         , MemberType_ID         , MemberName         , MemberCode   )   SELECT DISTINCT         'Geography'       , 'StateProvince'       , 1       , StateProvinceName       , StateProvinceCode   FROM   AdventureWorksDW2008R2..DimGeography

CountryRegion

-- insert CountryRegion-grained entity members into mdm.tblStgMember  INSERT INTO mdm.tblStgMember   (           ModelName         , EntityName         , MemberType_ID         , MemberName         , MemberCode   )   SELECT DISTINCT         'Geography'       , 'CountryRegion'       , 1       , EnglishCountryRegionName       , CountryRegionCode   FROM AdventureWorksDW2008R2..DimGeography

Next we'll use the same method to load the attribute information into each of the entities using the same technique. The only difference is that this time we insert the data into the mdm.tblStgMemberAttribute table.

Insert values for the StateProvince attribute in the City entity.

-- insert StateProvince attributes into mdm.tblStgMemberAttribute  INSERT INTO mdm.tblStgMemberAttribute   (           ModelName         , EntityName         , MemberType_ID         , MemberCode         , AttributeName         , AttributeValue   )   SELECT         'Geography'       , 'City'       , 1       , StateProvinceCode + '_' + UPPER(SUBSTRING(City,1,4)) + '_' + PostalCode       , 'StateProvince'       , StateProvinceCode   FROM AdventureWorksDW2008R2..DimGeography

…and now insert values for the CountryRegion attribute in the StateProvince entity.

Follow the same batch loading process as before to load the new attribute values.

-- insert CountryRegion attributes into mdm.tblStgMemberAttribute  INSERT INTO mdm.tblStgMemberAttribute   (           ModelName         , EntityName         , MemberType_ID         , MemberCode         , AttributeName         , AttributeValue   )   SELECT DISTINCT         'Geography'       , 'StateProvince'       , 1       , StateProvinceCode       , 'CountryRegion'       , CountryRegionCode   FROM   AdventureWorksDW2008R2..DimGeography

Return to the Master Data Manager and ensure Geography and VERSION_1 are selected in the Model and Version dropdowns. Click the Explorer option and examine the members and attributes of both the City and the StateProvince entities. The attributes within each will now have been populated with the corresponding member of the appropriate entity.

City entity, StateProvince attribute

StateProvince entity, CountryRegion attribute

 

All that is left to do now is populate the freeform FrenchCountryRegionName and SpanishCountryRegionName attributes of the CountryRegion entity. Same method, different TSQL. Here is the code:

-- insert French country name attribute values into mdm.tblStgMemberAttribute  INSERT INTO mdm.tblStgMemberAttribute   (           ModelName         , EntityName         , MemberType_ID         , MemberCode         , AttributeName         , AttributeValue   )   SELECT DISTINCT         'Geography'       , 'CountryRegion'       , 1       , CountryRegionCode       , 'FrenchCountryRegionName'       , FrenchCountryRegionName   FROM AdventureWorksDW2008R2..DimGeography

-- insert Spanish country name attribute values into mdm.tblStgMemberAttribute  INSERT INTO mdm.tblStgMemberAttribute   (           ModelName         , EntityName         , MemberType_ID         , MemberCode         , AttributeName         , AttributeValue   )   SELECT DISTINCT         'Geography'       , 'CountryRegion'       , 1       , CountryRegionCode       , 'SpanishCountryRegionName'       , SpanishCountryRegionName   FROM AdventureWorksDW2008R2..DimGeography

If you want to, admire your handiwork the same way you did before by browsing to the Explorer and viewing the contents of the CountryRegion entity, now complete with country names in French and Spanish.

 

TIP: if you want to clear out the staging tables run the following sproc.

-- clear all staging table records  EXEC mdm.udpStagingClear NULL, NULL, 2, DEFAULT, DEFAULT

On to Creating a Derived Hierarchy