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.
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)
If you wish you can have a look at the inserted records by running the following
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.
Insert values for the StateProvince attribute in the City entity.
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: