Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2016-06-29   |   Comments   |   Related Tips: More > 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.

 
SQL Server 2016 Master Data Services Big Area

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

 
SQL Server 2016 Master Data Services Area Entity

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.

SQL Server 2016 Master Data Services Region 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.

SQL Server 2016 Master Data Services Entity Explorer

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.

SQL Server 2016 Master Data Services Entity Explorer to see dependencies and data
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.


Last Updated: 2016-06-29


next webcast button


next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools