Creating a Hierarchy



A Hierarchy is a set of logically related attributes with a fixed cardinality. While browsing the data, a hierarchy exposes the top level attribute which can be broken down into lower level attributes. For example, Year -> Semester – Quarter – Month is a hierarchy. While analyzing the data, it might be required to drill down from a higher level to a detail level, and exposing data as a hierarchy is one of the best solutions for this.


Creating a hierarchy is as easy as dragging and dropping attributes in the hierarchy pane of the dimension editor. We want to create a hierarchy in the Sales Territory dimension. Open Sales Territory dimension in the dimension editor, drag and drop attributes in the hierarchy pane, click on each of them and rename them to something appropriate. After completing this, your hierarchy should look similar to the below screenshot.

Hierarchy Step 1

You will find a warning icon on the hierarchy pane, which says that attribute relationships are missing between these attributes. Country has a one-to-many relationship with Region, and Group has a one-to-many relationship with Country. But these relationships need to be defined explicitly in the dimension. Click on Attribute Relationships tab, right-click the region attribute and select “New Attribute Relationship”. Set the values as shown in the below screenshot to correct the relationships between these attributes.

Hierarchy Step 2

After you have applied the above changes, your attribute relationship tab should look like the below screenshot.

Hierarchy Step 3

If you have observer carefully, relationship types are of two types: Rigid and Flexible. This has an effect on the processing of the cube. Rigid means that you do not expect the relationship to change and Flexible means that relationship values can change. In our dataset, Group is a logical way to categorize countries and it can change, while regions within country have limited or no change. So the relationship type between country and group should be flexible and relationship type between region (sales territory key) and country should be rigid. Double click on the arrow joining Key attribute and Country, and change the relationship type as shown below.

Hierarchy Step 4

Check out the Hierarchy pane, and you should find that the warning icon is no longer visible. You can change the name of the hierarchy to something appropriate. In the interest of beginners who might get confused with the distinction between attributes and hierarchy, we will keep the name as “Hierarchy”.

Edit the Date dimension, and create a Year – Semester – Quarter – Month hierarchy in the date dimension.

Comments For This Article

Friday, December 9, 2016 - 3:03:53 AM - Rajesh Back To Top (44929)

I have created the below attribute relationship for for the DimDate table and made as rigid between DateKey and Calendar Year. But still having the warning message as "Attribute relationship do not exist between one or more levels of this hierarchy.This may result in decreased query performance". Please let me know the issue.


Date Key - Calendar Year – Calendar Semester – Calendar Quarter – English Month Name 

get free sql tips
agree to terms