We have already discussed quite some design tips for building Analysis Services (SSAS) Multidimensional cubes and dimensions:
- Analysis Services (SSAS) Multidimensional Design Tips – Data Source View and Cubes
- Analysis Services (SSAS) Multidimensional Design Tips – Creating Dimensions
In this last part of the series we’ll look at attribute relationships and hierarchies in a dimension. As with the previous articles, the examples are created using the AdventureWorks 2014 Enterprise sample OLAP cube.
Hierarchies
Hierarchies are a useful tool in SSAS to reduce complexity between attributes and guide users into a certain drill-down behavior. In fact, it’s the first design tip SSAS gives you after creating a new dimension:
We can find some examples in the Reseller dimension:
In the browser, we can inspect the Reseller Bank hierarchy:
When building a Pivot table in Excel, it becomes clear how a hierarchy provides us with a clear drill-down path:
To avoid any confusion between the hierarchy levels and the attributes those levels originate from, it’s a good idea to hide the original attributes. For example, if you have a hierarchy with the levels Year – Month – Day and an attribute called Month, it might be confusing; why are there two month attributes? This is also a design tip provided by SSAS:
You can combine different attributes with each other, SSAS allows you to create hierarchies in all directions:
However, not all hierarchies might lead to optimal performance. SSAS warns you about this (see the yellow triangle with exclamation mark):
We’ll come back on this in the section about attribute relationships.
Parent-child hierarchies
A parent-child hierarchy is a special type of hierarchy. It’s number of levels can vary depending on the data. The best practices are quite simple: try to avoid them. Larger parent-child hierarchies have terrible performance and they are hard to secure. If you really need a parent-child structure, you can try to flatten the relationship into a pre-defined set of levels and use the HideMemberIf property to create a ragged hierarchy. You can read all about it in the article User-Defined Hierarchies – Ragged Hierarchies.
Attribute Relationships
Attribute relationships are one of the most important concepts in SSAS dimensions. They tell the cube how the data is structured and they are vital to query performance. In the Reseller dimension, we can find the following relationships:
This is the default behavior: every attribute is related to the key attribute (Reseller in this example). There are two kinds of relationships: flexible and rigid. Flexible relationships can change over time. For example, a reseller might change banks. Rigid relationships are fixed; they cannot change. Here, the relationship Reseller – Business Type is rigid (as indicated by the black arrow). This means that the business type will never change for a reseller. If it does, the processing of the dimension will fail if you use Process Update.
In a date dimension, you can typically set all relationships to rigid, as we can expect the calendar structure to never change.
You can read the attribute relationships of the date dimension as follows: at the lowest level at the left we have the individual dates (which are the key attribute of the dimension). Dates roll up into months. Months roll up into quarters, quarters roll up into semesters and semesters finally roll up into years at the right.
For attribute relationships to function, data must be of a certain quality. There can be no duplicates (watch out for white space and different casing/accents) and a member cannot have multiple parents. For example, you cannot create an attribute relationship between month and year if you have the following data:
The problem here is that January is not unique. It appears in the years 2016 & 2017, which means it has multiple parents. If you would see the month January, you wouldn’t be able to tell from which year it is. The solution is simple: you either create a different month attribute that contains the year as well, to make it unique. For example: January, 2017. Or you can configure the existing attribute to have a composite key:
Keep in mind SSAS won’t stop you from creating attribute relationships that aren’t supported by the underlying data. When processing the dimension however, you will be confronted with several error messages. Let’s illustrate with an example. Suppose we created the following attribute relationship between business type and Last Order Year in the Reseller dimension.
When we process the dimension, we receive the following error:
Natural hierarchies
When you create a hierarchy and you create attribute relationships between the levels of the hierarchy, you have created a natural hierarchy. Such a hierarchy will be optimized for performance (for example when creating aggregations or a query plan). If you don’t have matching attribute relationships for the hierarchy, the hierarchy will be unbalanced and not optimized for performance. SSAS will warn you for this situation as discussed in the previous section.
Sorting Attributes
When there’s an attribute relationship between two attributes, you can use the child attribute to sort the parent attribute. A typical example is sorting the months. If you do not apply any sorting, months would be visualized in the following order:
You can solve this issue in two ways:
- Assign the month number as the key column of the months, and the month name as the name column.
- Or you can define an attribute relationship between Month and the Month Number attributes.
With solution 1, you simply need to make sure the sorting is set to Key.
Now the months will be sorted according to the Key column (1 = January, 2 = February and so on). For the other solution, we need to add an attribute relationship between Month of Year and Month Number:
Now we can change the OrderBy property of Month of Year to AttributeKey. The OrderByAttribute should be set to Month Number.
After processing the dimension, we can see the months are correctly sorted.
Member Properties
Every attribute that has an attribute relationship with another attribute is a member property of that other attribute. This means there’s a direct relationship between those attributes. Let’s look at an example.
In the date dimension, we have the attribute Calendar Quarter.
It has attribute relationships with the following attributes:
- Calendar Quarter of Year
- Calendar Semester of Year
- Calendar Semester
- Fiscal Quarter
These attributes are also member properties of Calendar Quarter. We can easily check this in the dimension browser:
Selecting items from the list would put them side by side with their parent attribute:
SSAS can do this because it knows there’s a 1:1 relationship between the attribute and its member properties, thanks to the attribute relationships. Now, member properties become interesting for attributes that aren’t useful for direct analysis, because it wouldn’t make much sense to do so. Examples are phone numbers, email addresses, remarks etc. However, sometimes they are useful if you need more information about a certain member. For example, in an analysis with our top 5 worst performing sales persons, you might want to include the email addresses so you can easily contact them.
Using such attributes directly in an analysis can cause performance problems. For example, suppose you have 30,000 customers each with a unique email address. If you create a pivot table with customers and their emails on the row axis, Excel will be slow because it will try to calculate subtotals for each customer and its email. This isn’t necessary because there’s a 1:1 relationship. We can avoid this by using the property AttributeHierarchyEnabled. When this is set to False, you won’t be able to use this attribute directly. You can only use it as a member property. Since SSAS knows there’s a 1:1 relationship, there will be no subtotals in Excel and performance will be much faster.
In the Reseller dimension, we can see some attributes have been disabled (notice the gray icons).
In Excel, we can see member properties in the tooltip:
You can also add member properties by right-clicking an attribute and selecting an item from the list Show Properties in Report.
This will add the member property next to the parent attribute:
Notice the member property is not visible in the pivot table fields and there are no subtotals. Reporting Services also supports the use of member properties, but Power BI Desktop (currently) does not.
Conclusion
In this article, we went over some best practices for hierarchies and relationships in SSAS dimensions. We showed why attribute relationships are important, why we would need hierarchies and how we can improve performance with member properties.
Previous articles in this series
- Analysis Services (SSAS) Multidimensional Design Tips – Data Source View and Cubes
- Analysis Services (SSAS) Multidimensional Design Tips – Creating Dimensions
Reference Links
- MDX Member Properties – User-Defined Member Properties
- SSRS: Retrieving Member Properties in Reporting Services (with Analysis Services)
- Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014
- 4-6-Specifying Attribute Relationships in User-Defined Hierarchy
Koen has over 7 years of experience in developing data warehouses, cubes, and reports using the Microsoft BI stack. Somehow he has developed a particular love for Integration Services along the way.
He has a blog at http://www.sqlkover.com and he is a frequent speaker at local SQL Server events. You can find him on Twitter as @Ko_Ver.
View all posts by Koen Verbeeck