MDS works as a hub that centralizes enterprise critical entities data and synchronizes data sources. It provides two data exchange interfaces:
1:Staging tables for data importing
There are three types of staging tables in MDS:
- Leaf Members Staging tables.
These tables are created automatically when entities are created in Model. You could customize the staging table name. I prefer adding model name as prefix because some of entities may exist in more than one models. They are under "stg" schema. It's useful for access managmenet. A ETL service account should only access "stg" schema. Table column details are in
Leaf Member Staging Table (Master Data Services)
- Consolidated Member Staging Table
These tables are created when explicit hierachies are created. Explicit hierachy is used for handling ragged hierarchy in which members can exist at any level.Explicit hierachies contain members from a single entity. An explicit hierachy uses consolidated mmebers to group other members. And an explicit hierachy also includes all leaf members from the associated entity.
Here are the steps to create a sample explict hierachy using "product" sample model:
1: go to home->System Administration, then select "product" model. Click "proudct"->Entities, When this screen shows up, click "Product" and then "explict Hierachies"
2: Click "Add" to create a new explict hierachy
After click "save", two new staging tables are created in database. One for consolidated members and the other one for relationships between consolidated members and leaf members.
We could use that table to import consolidated members through ETL although we're going to enter consolidated members manully this time.
3: go back to home->explorer->Hierachies->"Explict:Ragged Product"
4: Select "All consolidated members in hierachy" and then click "Add" to add them one by one.
5: After all consolidated members are added in, then we can move leaf members under consolidated members.
change the filter back from " All consolidated members in hierachy" to "Leaf members". And then seach products that will be moved.
6: After you get the items on the left panel, click checkboxes, and then click "Cut" to cut them from root and then "Paste" them under one of consolidated members.
No one will like this cumbersome process. It can be done through ETL.
- Realationship Staging Table
This staging table is created when an explict hierachy is created. it works with sconsolidated staging table to import data into explict hierachy.
In next article, We'll discuss how to build a practial ETL process to load data into MDS.
Comments
Post a Comment