Master Data Services Schema

Interesting stuff learned today about how to navigate the Master Data Services schema.

Model-specific table suffixes (i.e. “mdm.tbl_{model ID}_{suffix}):

Suffix Description
TR TRansactions”.Stores member change   transactions for the entire model, for all entities that use deprecated   “Attribute” transaction logging type, but not “Member” or   “None” logging type
AN ANnotations”. Contains transaction   annotations. Has a FK on the _TR table.
VL Validation Log”.   Contains info about all members in all entities within the model that have   failed validation, as defined by business rules
CS Change Set”.   Contains high-level info about all changesets for all entities (that require   approval flow) in the model

Entity-specific table suffixes (i.e. “mdm.tbl_{model ID}_{entity ID}_{suffix}):

Suffix Description
EN “ENtity“.   Leaf members of the entity
HP Hierarchy Parent”.   Consolidated members of the entity, which can be  parents in explicit hierarchies.
CN CollectioN“.   Collection members of the entity.
HR Hierarchy Relationship”.   Contains parent-child relationships for explicit hierarchies. So it contains   FKs on the EN and HP tables.
CM Collection Membership”.   Contains parent-child relationships for collections. So it contains FKs on   the EN, HP, and CN tables. Similar to the HR table for explicit hierarchies.



HiStory”.   Contains member history for the corresponding member type table. This is used   when the entity’s transaction logging type is “Member”, but not   deprecated “Attribute” logging type or “None” logging   type.



ANnotations”. Contains member history   annotations for the corresponding member type history table. For example, the   EN_AN table’s Revision_ID column refers to the ID column of the _EN_HS table
EN_PD PenDing”.   This is where changes pending approval are stored. Only used when the entity   has change set approval flow enabled. Once a changeset is approved, the   related rows is the _EN_PD table are applied to the _EN table.


Member Security”.   When hierarchy member security permissions are assigned, the MS tables   contain effective row permissions for each member for each user. Only applies   to Leaf and Consolidated member types. Collection member type does not   support member security

User-defined attribute columns, in the member tables (EN, HP, and CN) use names like this:

uda_{entity ID}_{attribute ID}

System views

Various system views are also created for entities and derived hierarchies. Their names typically follow this format:

“mdm.viw_SYSTEM_{model ID}_{entity or derived hierarchy ID}_{suffix}

There are many different kinds of suffixes. Many of them also pertain to deprecated features. There are also a few model-specific views that don’t have the second ID for entity or derived hierarchy.

Entity-Based Staging (EBS) objects

When an entity is created, staging table(s) and sproc(s) are created. When the sproc is executed, rows are copied from the staging table(s) to the member table(s).

Table “stg.{staging named}_Leaf”. If the entity supports explicit hierarchies, then an additional two tables are created:

“stg.{staging named}_Consolidated”

“stg.{staging named}_Relationship”


Data Dictionary

There are also some tables that can be used to map an entity to a table.

mdm.tblEntity retains the Name (MDS entity name) and the EntityTable (SQL Server table name)

mdm.tblAttribute has a list of reusable columns and their definitions

Share this post...

Leave a Reply

Your email address will not be published. Required fields are marked *