Master Data Services

16 min readAug 22, 2020

Create a Model (Master Data Services)

· You must have permission to access the System Administration functional area.

· You must be a model administrator.

· Override the system setting and not remove transaction log data, select NO.

· To retain only today’s log data and truncate log data for all previous days, select YES and set the Days field to 0.

· To retain log data for a specified number of days, select YES and set the Days field to the number of days.

· Select Create entity with same name as model to create an entity with the same name as the model.

Entities (Master Data Services)

· Entities are objects that are contained in Master Data Services models.

· Each entity contains members, which are the rows of master data that you manage.

· Models can contain as many entities as you want to manage.

· Each entity should group a similar kind of data.

· When users are assigning attributes to the members in an entity, you can have them choose from a constrained list of values.

· To do this, you use an entity to populate the list of values for the attribute.

· This is called a domain-based attribute.

Create and Execute an Entity Sync Relationship (Master Data Services

· Entity sync is a one-way and repeatable synchronization between entity versions.

· It enables you to share entity data between different models.

· You can keep a single source of truth in one model and reuse this master data in other models.

· With entity sync, you can also make a one-time copy of data.

· All leaf members with free-form and file attributes in the source entity are synced to the target entity during sync execution.

· This creates, deletes and modifies entity schema and members.

· Once a sync relationship has been established, the target entity can be modified only by the sync process.

· A sync relationship can be removed at any time to make the target entity editable.

Entity Permissions (Master Data Services)

Permission

Description

Read

User can read members, attributes, hierarchy memberships, or collection memberships.

Create

User can create members, and assign attribute values during create.

Update

User can update members, attributes, hierarchy memberships, or collection memberships.

Delete

User can delete members.

Deny

Deny all access to the entity.

Domain-Based Attributes (Master Data Services)

· A domain-based attribute is an attribute with values that are populated by members from another entity.

· You can think of a domain-based attribute as a constrained list;

· Domain-based attributes prevent users from entering attribute values that are not valid.

· To select an attribute value, the user must pick from a list.

· You can use the same entity as a domain-based attribute of multiple entities

Attribute Groups (Master Data Services)

· Attribute groups help organize attributes in an entity.

· When an entity has lots of attributes, attribute groups improve the way an entity is displayed in the Master Data Manager web application.

· How Attribute Groups Change the Display

o Attribute groups are displayed as tabs above the grid in the Explorer functional area of Master Data Manager.

§ Attribute groups always include the Name and Code attributes.

§ Each attribute for an entity can belong to one or more attribute groups.

§ All attributes are automatically included on the All Attributes tab in Explorer.

§ There is no way to hide the All Attributes tab.

§ Attribute groups are administered in the System Administration functional area of Master Data Manager.

Create a Collection (Master Data Services)

· Master Data Services, create a collection when you want to create flat lists of leaf and consolidated members.

· Collections do not need to include all members from the entity.

Database Logins, Users, and Roles (Master Data Services)

· Master Data Services includes logins, users, and roles that are automatically installed on the SQL Server Database Engine instance that hosts the Master Data Services database.

· These logins, users, and roles should not be modified.

Schemas

Role

Description

mdm

Contains all Master Data Services database and Service Broker objects other than the functions contained in the mdq schema.

mdq

Contains Master Data Services database functions related to filtering member results based on regular expressions or similarity, and for formatting notification emails.

stg

Contains Master Data Services database tables, stored procedures, and views related to the staging process. Do not delete any of these objects. For more information about the staging process, see Overview: Importing Data from Tables (Master Data Services).

Security (Master Data Services)

Hierarchies (Master Data Services)

· A hierarchy is a tree structure that you can use to:

· Group similar members for organizational purposes.

· Consolidate and summarize members for reporting and analysis.

· Each hierarchy contains members from one or more entities.

· When a member is added, changed, or deleted, all hierarchies are updated.

· This ensures that the data is accurate in all hierarchies.

· Hierarchies also help ensure that each member is counted once and only once.

· Ragged hierarchies from a single entity, which are called explicit hierarchies

· Level-based hierarchies from multiple entities, based on the existing relationships between entities and their attributes, which are called derived hierarchies.

· A hierarchy is different from a taxonomy.

· A taxonomy organizes members by multiple attributes at the same time, while a hierarchy organizes members by one attribute at a time.

· A taxonomy can include the same member multiple times, while a hierarchy includes a member only once.

Create a Subscription View to Export Data (Master Data Services)

· Select either Version or Version Flag in Version Options, and then select from the corresponding list.

· Create a subscription view based on a version flag.

o When you lock a version, you can reassign the flag to an open version without updating the subscription view.

Business Rules (Master Data Services)

· A business rule is a rule that you use to ensure the quality and accuracy of your master data.

· You can use a business rule to

o automatically update data,

o to send email, or

o to start a business process or workflow.

Create and Publish Business Rules

· Business rules are If/Then/Else statements that you create in Master Data Manager.

· If an attribute value meets a specified condition, then an action is taken, otherwise a Else action is taken.

· Possible actions include setting a default value or changing a value.

· These actions can be combined with sending an email notification.

· To use business rules you must first create and publish your rules, then apply the published rules to data.

· You can apply rules to subsets of data or to all data for a version by validating the version.

· A version cannot be committed until all attributes pass business rule validation.

· If you create a business rule that uses the OR operator, you should create a separate rule for each conditional statement that can be evaluated independently.

· You can then exclude rules as needed, providing more flexibility and easier troubleshooting.

· You can set priority order for rules to run in by moving business rules up and down.

· However, before priority is taken into account, business rules are applied based on the type of action the rule takes.

o The order is as follows:

Default Value
Change Value
Validation
External Action
User Defined Action Script
If a business rule is excluded or is not published with a status of Active, the rule is still available but is not included when business rules are applied.
Business rules apply to the attribute values for all leaf or all consolidated members, not both.
Business rules can be applied to any version of a model that is Open or Locked.
Changes made to data when business rules are applied are not logged as transactions.
A business rule cannot contain more than one start workflow action.

Overview: Importing Data from Tables (Master Data Services)

· You use Master Data Services staging tables, stored procedures and Master Data Manager .

· When you add and modify data, you can do the following.

o Load and update members, and update attribute values

o Deactivate and delete members

o Move explicit hierarchy members

· Adding and updating data includes the following main tasks.

o Load data into the staging tables in the Master Data Services database.

o Load the data from the staging tables into the appropriate Master Data Services tables.

For each entity in the model, there is a staging table.
The table name indicates the corresponding entity, and the entity type such as leaf member.

· The name of the table is specified when an entity is created and cannot be changed.

· If the staging table name contains a _1 or other number, another table of that name already existed when the entity was created.

· The Master Data Services includes the following types of staging stored procedures.

stg.udp_<name>_Leaf
stg.udp_<name>_Consolidated
stg.udp_<name>_Relationship

Explicit Hierarchies (Master Data Services)

· An explicit hierarchy organizes members from a single entity in any way you specify.

· The structure can be ragged and unlike derived hierarchies, explicit hierarchies are not based on domain-based attribute relationships.

· An explicit hierarchy uses consolidated members that you create for the purpose of grouping other members.

· These consolidated members can belong to only one explicit hierarchy at a time.

· An explicit hierarchy also includes all of the leaf members from the associated entity.

· An explicit hierarchy can be ragged, which means that the hierarchy can end at different levels simultaneously.

· Each consolidated member can have an unlimited number of consolidated and leaf members underneath, or can have none.

· The leaf members can be under a single consolidated member or under multiple levels of consolidated members.

· There are two types of explicit hierarchies: mandatory and non-mandatory.

o A mandatory explicit hierarchy is a hierarchy in which all leaf members must be included in the hierarchy tree.

o A non-mandatory explicit hierarchy is a hierarchy in which all leaf members are in a system-created Unused node.

o You can move members out of this node as you need them.

o The rest of the members can remain in the Unused node.

· The following rules apply to explicit hierarchies (both mandatory and non-mandatory).

Each leaf member can be included in the hierarchy only once.
All consolidated members must be included in a hierarchy.
Consolidated members cannot be in more than one explicit hierarchy.
Consolidated members in the hierarchy tree do not have to contain leaf members underneath them.
If you delete an explicit hierarchy, all consolidated members that were used in the hierarchy are deleted.
If you delete a consolidated member that was in an explicit hierarchy, all leaf members that were grouped by that consolidated member are moved to the root.

· The following table shows some of the differences between explicit and derived hierarchies.

EXPLICIT HIERARCHIES VERSUS DERIVED HIERARCHIES

Explicit Hierarchies

Derived Hierarchies

Structure is defined by the user

Structure is derived from the relationships between domain-based attributes

Contains members from a single entity

Contains members from multiple entities

Uses consolidated members to group other members

Uses leaf members from one entity to group leaf members from another entity

Can be ragged

Always contains a consistent number of levels

Derived Hierarchies (Master Data Services)

· A Master Data Services derived hierarchy is derived from the domain-based attribute relationships that already exist between entities in a model.

· You can create a derived hierarchy to highlight any of the existing domain-based attribute relationships in the model.

· In a derived hierarchy, the leaf members from one entity are used to group the leaf members of another entity.

· A derived hierarchy is based on the relationship between these entities.

· An explicit hierarchy, in contrast, is based on members from a single entity only and is structured in any way you specify.

· You can change the structure of a derived hierarchy without affecting the underlying data.

· As long as the relationships still exist in the model, deleting a derived hierarchy has no effect on your master data.