Microsoft SQL Server 2008 R2: Hierarchies, Collections, and MDS Metadata

Microsoft SQL Server 2008 R2 Master Data Services

Manage and maintain your organization’s master data effectively with Microsoft SQL Server 2008 R2 Master Data Services

(For more resources on this subject, see here.)

The reader is advised to refer the previous article on Creating and Using Models since this article is related to it.

Master Data Services includes a Hierarchy Management feature, where we can:

• Browse all levels of a hierarchy
• Move members within a hierarchy
• Access the Explorer grid and all its functionality for all members of a given hierarchy.

As we’ve seen already, there are two types of hierarchies in MDS—Derived Hierarchies and Explicit Hierarchies. We will look at how to create and use both types of hierarchies now.

Derived Hierarchies

In our example scenario, as we have stores in many different cities and states, we have a requirement to create a “Stores by Geography” hierarchy. In order to create the hierarchy, carry out the following steps:

1. Navigate to the System Administration function, which can be accessed using the Master Data Manager home page.
2. Hover over the Manage menu and click on the Derived Hierarchies menu item, which will open the Derived Hierarchy Maintenance page.
3. Click on the green plus icon to add a Derived Hierarchy, which will open the Add Derived Hierarchy page.
4. Enter Stores By Geography as the Derived Hierarchy Name and click on save.
5. The Edit Derived Hierarchy page will now be displayed, where we can build the hierarchy. On the left-hand side of the screen we can pick entities to be in our hierarchy, whereas the middle pane of the screen displays the hierarchy in its current state. A preview of the hierarchy with real data is also available on the right-hand side. Drag the Store entity from the left-hand side of the screen and drop it onto the red Current Levels : Stores By Geography node in the center of the screen:

   

6. The choice of entities of on the left hand side will now change to the only two entities that are related to Store, namely City and StoreType. Repeat the drag-and-drop process, but this time drag the City entity onto the red Current Levels node so that the Current Levels hierarchy is as follows:

   

7. The Available Entities and Hierarchies pane will now be updated to show the State entity, as this is the only entity related to the City entity. Drag the State entity over to the red Current levels node, above the City entity.
8. The Available Entities and Hierarchies pane will now be updated to show the Country. Drag the Country entity over to the red Current Levels node, above the State entity. This is the last step in building our Stores By Geography hierarchy, which will now be complete.

We will now look at how we can browse and edit our new hierarchy.

Exploring Derived Hierarchies

Before we make any changes to the Derived Hierarchy, we will explore the user interface, so that we are comfortable with how it is used.

Carry out the following in order to browse the new hierarchy features:

1. Navigate to the home page and select the Explorer function.
2. Within the Explorer function, hover over the Hierarchies menu, where a menu item called Derived: Stores By Geography should appear. Click on this new item, which will display the Derived Hierarchy, as shown below:

   

3. The buttons above the hierarchy tree structure are as follows (from left to right):
   • Pin Selected Item—Hides all members apart from the select item and all of its descendants. This option can be useful when browsing large hierarchies.
   • Locate Parent of Selected Item—The immediate parent of the selected member could be hidden, if someone has chosen to pin the item (as above). Locate Parent of Selected Item will locate and display the members parent, as well as any other children of the parent.
   • Refresh Hierarchy—Refreshes the hierarchy tree to display the latest version, as edits could occur outside the immediate tree structure.
   • Show/Hide Names—Toggles the hierarchy view to be either the member code and the name, or just the code. The default is to show the member name and code.
   • Show/Hide Attributes—On the right-hand side of the screen (not shown) the children of the selected item are shown in the Explorer grid, along with all their attributes. This button shows or hides the Explorer grid.
   • View Metadata—Displays a pop-up window that will display the metadata for the selected member. We will discuss metadata towards the end of this article.
4. Select the DE {Germany} member by clicking on it. Note: the checkboxes are not how members are selected; instead, clicking on the member name will select the member.
5. Use the Pin Selected Item button to pin the DE {Germany} member, which will hide the siblings of Germany as shown below:

   

6. To now locate the parent of DE {Germany}, and display the parent’s other children (for example, USA and United Kingdom), click on DE {Germany}, then click on the Locate Parent of Selected Item button. The hierarchy tree will revert back to the original structure that we encountered.
7. Now that we have returned to the original hierarchy structure, expand the US member until the member CA {California} is visible. Click on this member, which will display some of the cities, which we have loaded for the State of California:

   

   Editing multiple entities
   The above point illustrates one of the useful features of the hierarchy editor. Although we can edit the individual entities using their respective Explorer grids, with a Derived Hierarchy, we can edit multiple entities on a single page.

8. We don’t actually need to edit the cities for the moment, but we do want to look at showing and hiding the Explorer grid. Click on the Show/Hide Attributes button to hide the Explorer grid. Click on the button again to make the Explorer grid reappear.
9. Finally, we’re able to look at the Metadata for the Derived Hierarchy. Click on the View Metadata button to open the Metadata Explorer, which is shown below. This is where we would look for any auxiliary information about the Derived Hierarchy, such as a description to explain what is in the hierarchy. We’ll look at metadata in detail at the end of this article.

We will now look at how we add a new member in a Derived Hierarchy.

Adding a member in a Derived Hierarchy

Adding a member in a Derived Hierarchy achieves exactly the same thing as adding a member in the entity itself. The difference is that the member addition process when carried out in a Derived Hierarchy is slightly simplified, as the domain attribute (for example, City in the case of the Store entity) gets automatically completed by MDS. This because in a Derived Hierarchy we choose to add a Store in a particular City, which negates the need to specify the City itself.

In our example scenario, we wish to open a new Store in Denver. Carry out the following steps to add the new Store:

1. Expand the US {United States} member of the Stores By Geography hierarchy, and then expand the CO {Colorado} member.
2. Click on the 136 {Denver} member.
3. On the far right-hand side of the screen, the Stores for Denver (of which there are none) will be shown. Click on the green plus icon to begin the process of adding a Store.
4. Enter the Name as AW Denver and enter the Code as 052.
5. Click on the save icon to create the member.
6. Click on the pencil icon to edit the attributes of the new member. Note that the City attribute is already completed for us.
7. Complete the remaining attributes with test data of your choice.
8. Click on the save icon to save the attribute values.
9. Click on the green back arrow button at the top of the screen in order to return to the Derived Hierarchy.

Notice that we now have a new Store that exists in the Derived Hierarchy, as well as a new row in the Explorer grid on the right-hand side of the screen.

We will now continue to explore the functionality in the hierarchy interface by using Explicit Hierarchies.

Explicit Hierarchies

Whereas Derived Hierarchies rely on the relationships between different entities in order to exist, all the members within Explicit Hierarchies come from a single entity. The hierarchy is made by making explicit relationships between leaf members and the consolidated members that are used to give the hierarchy more than one level.

Explicit Hierarchies are useful in order to represent a ragged hierarchy, which is a hierarchy where the leaf members exist at different levels across the hierarchy.

In our example scenario, we wish to create a hierarchy that shows the reporting structures for our stores. Most stores report to a regional center, with the regional centers reporting to Head Office. However, some stores that are deemed to be important report directly to Head Office, which is why we need the Explicit Hierarchy.

Creating an Explicit Hierarchy

As we saw when creating the original Store entity in the previous article, an Explicit Hierarchy can get automatically created for us when we create an Entity.

While that is always an option, right now we will cover how to do this manually. In order to create the Explicit Hierarchy, carry out the following steps:

1. Navigate to the System Administration function.
2. Hover over the Manage menu and click on the Entities menu item.
3. Select the Store entity and then click on the pencil icon to edit the entity.
4. Select Yes from Enable explicit hierarchies and collections drop-down.
5. Enter Store Reporting as the Explicit hierarchy name.
6. Uncheck the checkbox called Include all leaf members in mandatory hierarchy. If the checkbox is unchecked, a special hierarchy node called Unused will be created, where leaf members that are not required in the hierarchy will reside. If the checkbox is checked, then all leaf members will be included in the Explicit Hierarchy. This is shown next:

   

7. Click on the save icon to make the changes to the entity, which will return us to the Entity Maintenance screen, and conclude the creation of the hierarchy.