Enhancing Data Analytics with SSAS Dimension Hierarchies

Introduction

This article will discuss how SSAS Dimension Hierarchies can be used to analyze data much efficiently. If you are a data analyst, you want to start the analysis with a higher hierarchy. Then navigate the narrow attributes when required. For example, it will be better to start with analyzing revenue by year. If you need to analyze further into the data, you can choose the needed year and expand the Quarter -> Month, respectively. Let us see how we can create SSAS Dimension Hierarchies in OLAP Cubes to suit different requirements.

Before we start, it is important to note that we are creating hierarchies in a dimension, and a hierarchy can contain attributes of a single dimension only while an attribute can be a member of multiple hierarchies.

To demonstrate different types of SSAS dimension Hierarchies, let us create an SSAS OLAP cube using Fact Internet Sales in the AdventureworksDW database. Following is the Data Source View for the proposed OLAP cube. Please note that we have used user-friendly names for the tables.

Let us create an OLAP cube and process it; The processed cube can be seen as shown in the below screenshot.

Let us analyze data using Excel just after creating the OLAP Cubes in Excel Pivot Table. Let us select Product Cost and Sales Amount and attributes from the Sales Territory, as shown below.

Though there are defined hierarchies, users still can select the attributes and see the hierarchical effect. However, the user has to select the hierarchy in the correct business order. For example, for the above example, the user has to select Group, Country, and Region in the same order. If not, data will not be displayed in the correct order.

Natural Business Hierarchies

In every business, there are natural business Hierarchies. For example, Region -> Country in the Territory Dimension, Product Category -> Product Sub Category -> Product Name in the Product dimension are the natural hierarchies that can be created.

The following screenshot shows how hierarchies are created for the Sales Territories.

If you set the AttributeHierarchyVisible to False for the attribute property, this attribute can only be accessed through the hierarchies, not by the individual attributes.

After creating the hierarchy and setting up the property, as shown in the above screenshot, you can process only the dimension.

Let us view this in Microsoft Excel Pivot tables, as shown below.

You can see that the hierarchy is available in the pivot table while the other attributes are not visible.

In the following Promotion Dimension, two SSAS Dimension hierarchies are created, as shown in the following screenshot.

Non-Natural Hierarchies

In the previous section, we created natural SSAS Dimension hierarchies. However, there are non-natural hierarchies such as Gender -> Marital Status or Occupation -> Education in the Customer dimension. The following screenshot shows how to create multiple non-natural hierarchies in the Customer Dimension.

There is no difference when creating non-natural hierarchies to the natural hierarchies. However, there are a few best practices that should be adopted when creating Non-Natural hierarchies.

Hierarchies should not have many attributes. Ideally, you should not have more than two attributes to a non-natural hierarchy
Since these are non-natural hierarchies, it is important to create hierarchies for all possibilities. For example, when you create a hierarchy for Gender -> Marital Status, you should create another hierarchy for Marital Status and Gender
Since these are non-natural hierarchies, end-users do not have an idea of what you have in the hierarchies. Therefore, the hierarchy name should reflect what you have in the hierarchy. For example, as shown in the above screenshot, hierarchies are name, Education-Occupation, etc

Date Hierarchy

Date hierarchy is one of the most common SSAS Dimension hierarchies that is used by many users as it is obvious that date is the most common dimension that is used for data analytical purposes.

Let us create a hierarchy for the date dimension. In the above OLAP cube, the date is a role-playing dimension. A Role-playing dimension means depending on the surrogate keys that are joining with the fact table; you will have a different role for the dimension. In the above OLAP cube, the Date Dimension is linked to the fact table via OrderDateKey, ShipDateKey, and DueDateKey. For this configuration, there will be three dimensions named Order Date, Ship Date, and Due Date, as shown in the below screenshot.

When you create a hierarchy for Date Dimension, that hierarchy will be available for all the other role-playing dimensions, as shown in the below excel pivot table.

However, you will see that the month name is ordered by the alphabetical order of the name, not the order of the month number.

To achieve this, we need to do three configurations for the Month Name attribute, as shown in the below screenshot.

Key Column

For every attribute, there will be a Key and Name properties. By default, the attribute name will take the key and name properties. To facilitate the different orders, we need to change the key of the attribute, as shown in the below screenshot.

In this configuration, the key is changed to Calendar Year and Month Number of Year.

NameColumn

You need to change the NameColumn property to the EnglishMonthName.

OrderBy

By default, any attribute will be ordered by the Name property. Now you need to configure this to Key so that it will be ordered by Year and Month Number, which is the key of the attribute.

After processing the date dimension, you will find that now the Month Name is in the order by the month number, not by the Month Name.

Bucket Hierarchies

Let us assume that we want to analyze data with the annual income of the customers. As you know, since yearly income is a continuous variable, we need to create buckets or ranges for the yearly income. To achieve this, you can modify the DiscretizationMethod to EqualAreas, and you can define the number of buckets you need to create, as shown in the following screenshot.

Then you can combine this attribute with a different attribute such as Occupation to create SSAS Dimension Hierarchies, as shown in the below screenshot.

Parent-Child Hierarchy

Until now, the SSAS dimension hierarchies that we discussed have an equal number of levels. For example, in the Group -> Country-> Region hierarchy has three levels. If you look at the natural traditional organization hierarchy, there will be an unequal number of levels.

Let us create another Cube from FactResellerSales fact table with other related dimensions. In this cube, we are interested in the DimEmployee table.

Note: if you are adding the employee dimension to the existing project, use it after adding include the dimension to the Dimension usage configuration of the OLAP Cube.

As you see in the above data source view, in the DimEmployee table, there is a self-referencing between EmployeeKey and ParentEmployeeKey columns.

We will add a new column in the data source view to accommodate the Full Name of the employee with the following expression.

CASE
WHEN MiddleName IS NULL THEN
FirstName + ‘ ‘ + LastName + ‘ ( ‘ + Title + + ‘ )’
ELSE
Title + ‘ ‘ + FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName + ‘ ( ‘ + Title + + ‘ )’
END

This expression includes the First Name, Middle Name, Last Name, and the designation attributes of the Employee.

In the Employee dimension, select the Employee Key and change the name column to the Full Name that was created above.

This can be seen from the following screenshot.

For the Parent Employee Key, modify the Level Naming Template property, as shown in the below screenshot.

You are done with the Parent Employee Hierarchy and now process the dimension.

You can view any measure with the employee hierarchy, as shown in the below screenshot.

Conclusion

SSAS Dimension Hierarchies can be used to improve the data analytical capabilities for the data analyst. We have looked at natural hierarchies as well as non-natural hierarchies. Though there is no significant difference in how you create non-natural hierarchies, we discussed that there are important and best practices to follow.

In the date hierarchies, we discussed changing the attribute key so that we can sort the month name according to the month number rather than the month name. Further, we discussed how to create clusters for the continuous attribute so that it can be converted to SSAS Dimension Hierarchies. Finally, we looked at how to create hierarchies with a non-equal level of hierarchies, such as an Organization hierarchy.

Author
Recent Posts

Dinesh Asanka

Dinesh Asanka is MVP for SQL Server Category for last 8 years. He has been working with SQL Server for more than 15 years, written articles and coauthored books. He is a presenter at various user groups and universities. He is always available to learn and share his knowledge.

View all posts by Dinesh Asanka