Speaking the language of business intelligence with an Australian accent

Tuesday, November 24, 2009

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
tblStgMemberResults

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.

MDMIntegrationManagement

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.

UnbatchedStagingRecords

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.

StagingBatches

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.

BatchLineItem

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.

MDMExplorer

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

CityEntitySelect

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.

CityEntityRecords

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

DataLoad12

StateProvince entity, CountryRegion attribute

 DataLoad11

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

6 comments:

Unknown said...
This comment has been removed by the author.
Stef said...

Thank you for these Posts! I have been looking everywhere for a straigthforward way to start working with MDS. Your posts are WAY better than anything else I have found.

Nick Barclay said...

Glad the post helped you, Stef :)

Unknown said...

Great job! Really got me going with MDS esp when I am beginner.

I have though read that with SQL Server 2012 they have changed the whole staging and loading process. So this part does not work when using MDS 2012.
Do you have any related posts where 2012 version is used?

Thank you!

Anonymous said...

Hi,
Fantastic post. In case your platform is MS SQL Server 2012 or 2014 then loading data into stage tables is a little different. Below three insert tables for loading data.

--------------------
INSERT INTO mds.stg.CountryRegion_Leaf
(
ImportType
, ImportStatus_ID
, Batch_ID
, BatchTag
, ErrorCode
, Name
, Code
, FrenchCountryRegionName
, SpanishCountryRegionName)
SELECT
1
, 0
, null
, 'Import a new CountryRegion'
, null
, EnglishCountryRegionName
, CountryRegionCode
, FrenchCountryRegionName
, SpanishCountryRegionName
FROM AdventureWorksDW2014.dbo.DimGeography
--------------------
INSERT INTO mds.stg.StateProvince_Leaf
(
ImportType
, ImportStatus_ID
, Batch_ID
, BatchTag
, ErrorCode
, Name
, Code
, CountryRegion)
SELECT
1
, 0
, null
, 'Import a new StateProvince'
, null
, StateProvinceName
, StateProvinceCode
, CountryRegionCode
FROM AdventureWorksDW2014.dbo.DimGeography
--------------------
INSERT INTO mds.stg.City_Leaf
(
ImportType
, ImportStatus_ID
, Batch_ID
, BatchTag
, ErrorCode
, Name
, Code
, StateProvince)
SELECT
1
, 0
, null
, 'Import a new City'
, null
, City
, StateProvinceCode + '_' + UPPER(SUBSTRING(City,1,4)) + '_' + PostalCode
, StateProvinceCode
FROM AdventureWorksDW2014.dbo.DimGeography

Anonymous said...

Correction to my previous post:
Below inserting SQL-statements:
INSERT INTO mds.stg.CountryRegion_Leaf
(
ImportType
, ImportStatus_ID
, Batch_ID
, BatchTag
, ErrorCode
, Name
, Code
, FrenchCountryRegionName
, SpanishCountryRegionName)
SELECT DISTINCT
1
, 0
, null
, 'Import a new CountryRegion'
, null
, EnglishCountryRegionName
, CountryRegionCode
, FrenchCountryRegionName
, SpanishCountryRegionName
FROM AdventureWorksDW2014.dbo.DimGeography
--------------------
INSERT INTO mds.stg.StateProvince_Leaf
(
ImportType
, ImportStatus_ID
, Batch_ID
, BatchTag
, ErrorCode
, Name
, Code
, CountryRegion)
SELECT DISTINCT
1
, 0
, null
, 'Import a new StateProvince'
, null
, StateProvinceName
, StateProvinceCode
, CountryRegionCode
FROM AdventureWorksDW2014.dbo.DimGeography
--------------------
INSERT INTO mds.stg.City_Leaf
(
ImportType
, ImportStatus_ID
, Batch_ID
, BatchTag
, ErrorCode
, Name
, Code
, StateProvince)
SELECT
1
, 0
, null
, 'Import a new City'
, null
, City
, StateProvinceCode + '_' + UPPER(SUBSTRING(City,1,4)) + '_' + PostalCode
, StateProvinceCode
FROM AdventureWorksDW2014.dbo.DimGeography