How to analyze dependencies in a SQL Sever 2016 Master Data Services data model

By: Siddharth Mehta   |   Updated: 2016-06-29   |   Comments   |   Related: > Master Data Services

Problem

In Master Data Services an entity can have attributes that are derived from other entities. City entity can have an attribute named State which may be derived from the State entity, and the State entity can have an attribute named Country which may be derived from the Country entity. Based on this, you should perform an impact analysis in case of inter-dependencies between entities.

Solution

In this tip we look at how to use the Entity Dependencies Explorer to analyze dependencies / relationships between entities. While defining an entity, we can create domain-based attributes that are based on other entities. You can read more about this in this tip. This creates a dependency of one entity on other.

SQL Server 2016 Master Data Services (MDS) ships with sample data packages which can be installed optionally on your MDS installation. One of the packages is CustomerSample package. We will assume this package is already installed on your MDS installation and we will use this sample data for discussing Entity Dependencies Explorer. You can read more about deploying an entity model on MDS from this tip.

Explore the CustomerSample model using the Explorer screen, and you will find different entities in this model. Explore the entity BigArea, and you will be able to find data as shown below.

Explore the Area Entity which uses BigArea as one of its attributes. The data in this entity looks as shown below.

Let's consider yet another entity that uses Area as one of its attributes, the Region entity. In real life scenarios there would be many entities with a number of entities dependent on a parent entity.

To analyze dependencies, navigate to the Explorer screen and click on the Entity Dependencies menu option. This menu option is available only with SQL Server 2016. The menu items contain a list of entities with a number in brackets. This number represents the number of dependencies associated with the entity.

Click on the Big Area menu option, and this should open a screen as shown below. Click on BigArea in the explorer pane, and this will show two items under it - NAm and Int. Click on NAm and this will open another entity under it - Area. This means the Area entity is dependent on the NAm member. Similarly keep expanding other items as shown below and you will be able to explore all the associated dependencies and data in each dependent entity.

Next Steps

• The Entity Dependency explorer is a fully editable view. Try to drag and drop different members from one entity to another to change dependencies. You can also use Cut and Paste features in this view.
• Read more SQL Server 2016 Master Data Services tips.

About the author

Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips