How to set hierarchies for a dimension inside SSAS Cube.

Problem: How to set hierarchies for a dimension inside SSAS Cube.

Solution: User Hierarchies

Inside Analysis Services dimensions each dimension contains a key attribute. Each attribute bound to one or more columns in a dimension table. The key attribute is the attribute in a dimension that identifies the columns in the dimension main table that are used in foreign key relationships to the fact table. Typically, the key attribute represents the primary key column or columns in the dimension table. You can define a logical primary key on a table in a data source view which has no physical primary key in the underlying data source

Attributes can be, and typically are, arranged into user-defined hierarchies that provide the drill-down paths by which users can browse the data in the measure groups to which the attribute is related. In client applications, attributes can be used to provide grouping and constraint information. When attributes are arranged into user-defined hierarchies, you define relationships between hierarchy levels when levels are related in a many-to-one or a one-to-one relationship

There are three types of hierarchies:

      1.       Natural hierarchy
2.       Ragged hierarchy
3.       Parent-child hierarchy

Example:

In this example, I would like to show we can hierarchy of a date dimensions to drill through.

Step 1: launch visual studio and open the date dimensions from the previous cube built, for more information go through this article How to build a cube..

Step 2: Now go to the dimension’s folder and select the data dimension and double click to open.

 

Step 3: From the data source view of the third tab, please select Calendar year, calendar quarter, month number of the year columns and drag those into attributes tab.

Step 4: now drag drop from the high level to low level in to hierarchy tab and you should except dots next to each level like below.

Step 5: to get a unique row in the hierarchy we should have composite keys set up or else the data would be redundant at this point by adjusting key columns.

Similarly set the month to add quarters and years

Step 6: next you must set the attribute relationships to set the hierarchy order.

Change the relations to below and point the order as below.

Step 7: to verify the hierarchy established is right you can browse from the dimensions to drill through.

Results: