Objects Overview in MDM database created by Master Data Services in SQL Server 2012 RC0
There are so many objects in MDM database that gets created when you create a model in Master Data Services. Below outlines the significance of each object:
Schema Name |
Type Of Object |
Object Name Sample |
Purpose/Comments |
Created or Used or Referenced in/by User defined process? |
||||||||||||||||||
Mdm |
Tables |
Mdm.tbl_N_N_XX |
These are internal system tables and should not be altered or used in any queries. |
No |
||||||||||||||||||
Mdm |
Tables |
Mdm.tblXXXXX |
These are also internal system tables and should not be altered or used in any queries. |
No |
||||||||||||||||||
Stg |
Tables |
Stg.EntityName_Leaf |
· With SQL Server 2008 R2 onwards, Master Data Services has 1:1 staging leaf table for each entity in MDM. · Columns in these tables maps to attributes in its corresponding MDM entity. · It is only used during Entity Based Staging process for loading MDM system – once data is imported (updated/inserted/deleted) from these leaf tables into MDM entities, it can be truncated, as there is no linkage to MDM’s actual data that are showing up in UI. |
Yes |
||||||||||||||||||
Stg |
Tables |
Stg.EntityName_Consolidated |
· These are consolidated members staging tables that maps to consolidated members in MDM · Just like leaf tables, these are also used only during Entity Based Staging process to import Updates (insert/update/delete) for those consolidated members in MDM |
Yes |
||||||||||||||||||
Stg |
Tables |
Stg.EntityName_Relationship |
· These staging tables are used to import the Parent Child relationship defined in an Explicit Hierarchy. · Once the Relationships in Explicit Hierarchy imported from this staging table, it can be truncated |
Yes |
||||||||||||||||||
Mdm |
Views |
Mdm.SubscriptionViewName |
Subscription views can be created over following MDM objects:
Subscription Views are used to extract MDM data for any downstream application. |
Yes |
||||||||||||||||||
Mdm |
Views |
Mdm.viw_SYSTEM_xxx |
These are system views and should be used or referenced in any downstream application |
NO |
||||||||||||||||||
Stg |
Views |
Stg.viw_EntityName_MemberErrorDetails |
These views are used to review import errors that are captured as part of the Entity Based Staging process. |
Yes |
||||||||||||||||||
Mdm |
Stored Proceduers |
Mdm.udpXXXXXX |
These are system stored procedures generated and used by internal MDM system. These are not intended to be referenced in any downstream application. They should not be use or altered for any MDM queries. |
NO |
||||||||||||||||||
Stg |
Stored Procedures |
Stg.udp_EntityName_Leaf Stg.udp_EntityName_Consolidated Stg.udp_EntityName_Relationship |
These procedures are used during Entity Based Staging Process to import (process insert/update/delete based on Import Type Parameter) data from entity’s stage table into corresponding MDM entity or consolidated member or explicit relationship. |
Yes |
||||||||||||||||||
Mdm |
User Defined Functions |
Mdm.udfXXXXX |
These are system functions and should not be used or referenced or altered in any downstream application. |
NO |