Creating Hierarchies in SQL Cubes

Once you have finished creating your SQL cube, now it's time to look into some advanced features like creating hierarchy at all.

We need to work on the dimensions to create hierarchy on it.

Step 1: Open the Business Intelligence studio and open the Analysis service project that you have created. Then open one of the dimensions on it; here I'm using the customer.dim that I have created.

Double-click on the dimension to open it on the designer.

We can see four tabs as shown below:



Step 2:

There are 3 panes in the Dimension structure tab: Attributes, Hierarchy and Data Source View.

From the Attributes pane, drag an attribute that you want to use in the user-defined hierarchy to the Hierarchies pane. Drag additional attributes to form levels in the user-defined hierarchy.

The order in which attributes are listed in the hierarchy is important.



You can view the hierarchy relation from the attribute relationship tab.



After deploying the solution you can try browsing the hierarchy from the browse tab.