Share via


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:

Object Type

Format Type

Entity

Leaf Members

Consolidated Members

Collection

Collection Members

Explicit Parent Child

Explicit Levels

Derived Hierarchy

Derived Parent Child

Derived Levels

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