Beginning MDS - Creating a Derived Hierarchy (Part 6 of 7)

There are two kinds of Hierarchy that can be created within MDS: Derived and Explicit. We’re only going to deal with derived hierarchies for now.

As the name suggests, derived hierarchies are derived from the relationships between entities within a model. In our Geography model we have used attributes to define a relationship between the City and the StateProvince entities and another one between StateProvince and CountryRegion. These relationships will enable the easy creation of a derived hierarchy. CountryRegion > StateProvince > City.

Walkthrough

Once more browse to the Master Data Manager and select System Administration

In the Model Explorer page select Manage > Derived Hierarchies

In the Derived Hierarchy Maintenance page ensure Geography is selected in the Model dropdown and click the + sign to add a new derived hierarchy.

 

Type Cities in the Derived hierarchy name textbox. Click Save.

 

All that is left to do in the Edit Derived Hierarchy: Cities page is to drag and drop each of the desired entities from the Available Entities and Hierarchies area into the Current Levels area.

Start with the lowest (in this case the leaf) entity first. Drag and drop the City entity onto the Current levels: Cities area. Note that the preview area comes to life now too.

Now drag and drop the StateProvince entity from the Available Entities and Hierarchies onto the City item in the Current Levels area. Finally drag and drop the CountryRegion entity onto the StateProvince entity.

Once you’ve used up all three entities, you’ll be able to preview your complete derived hierarchy.

 

On to Getting at the Data with TSQL