By: Scott Murray | Comments (4) | Related: > Analysis Services Dimensions
Problem
Why do I need to create attribute relationships in my SQL Server Analysis Services Cubes and how do I create them? What are some common data sets to create attribute relationships on in SSAS? How do you create attribute relationships in SQL Server Analysis Services? Check out this tip to learn more.
Solution
When designing a cube and specifically dimensions and attributes, deciding how users will navigate and aggregate the measure values related to a dimension becomes vitally important to the performance of a cube and the queries which are sent to a SSAS database. This process is maintained by creating natural hierarchies within a cube; these hierarchies allow you to easily navigate up and down the various levels of attributes within a dimensions. Of course, one of the most common hierarchies which gets created is a date hierarchy. Day rolls up to month which rolls up to quarter which rolls up to year. Certainly, you could squeeze week of year in between day and month. These hierarchies help both the user understand how to move through a dimension, but they also serve a second, and potentially more important role as a way to improve performance of aggregations and queries. SSAS makes extensive use of aggregations, as noted in these MSSQLTips:
- Optimize a SQL Server Analysis Services Measure Group Partition for Performance
- Improve SQL Server Analysis Services Performance with the Usage Based Optimization Wizard
An
integral part of creating performant aggregations is to
create and maintain the appropriate attribute relationships for your
hierarchies. The reason the relationship is so important is because the
hierarchy, the attribute relationship, and any related aggregations
work in conjunction with each other to produce faster results for a
query. The role the attribute relationship plays in the process
centers around creating a clear and specific rollup path for the
aggregations and ultimately the queries requesting a dataset. For
instance, if we have a
date hierarchy similar to one mentioned earlier in the tip ( Year
> Quarter > Month > Day), and we create an
appropriate relationship, then a
query against the date dimension has a clear path to retrieve the
figures it needs for query, for example, the Internet sales per
year. Furthermore, because the
relationship has been established, the measure rollup by year can look
to
see if there is an aggregation at the year level. If that level is not
available and if the attribute relationship is
setup properly (which we show later in the tip), then instead of having
to look all the way down to the date key value, SSAS can just try to
find an aggregate at the
next level of the relationship, which would be quarter in our example.
Summating the data at the quarter level would be much faster than
having to drill down to the date key level. Even if no aggregation
exists
at the quarter level, then the query could look to the month level
which again would be faster than summing the data at the date key
level.
To show this process in practice, let us take a look at an example
using a date field.
Establishing Attribute Relationships in SSAS
Before we establish a new attribute relationship, we need to install copies of the AdventureWorks DW SQL Server and SSAS databases. These databases are available for download, free, on CodePlex at: http://msftdbprodsamples.codeplex.com/releases/view/55330. Once you have the SQL Server and OLAP AdventureWorks databases installed, you will start the SQL Server Data Tools (SSDT); SSDT is the new name for Business Intelligence Development Studio in SQL Server 2012. For our date example, I actually create a new data dimension, Date TEST, based on the existing date view in the data source view. As shown below, year, quarter, month, week, and date were added as attributes to the dimension.
Now we will add a hierarchy with the levels of Year, Quarter, Month, Week, and Day as shown next. As noted in the second screen print, SSAS displays a caution triangle and displays a warning that says that no attribute relationships exists in the hierarchy and as such performance may be impacted.
Thus, we now know we should create an attribute relationship, but it is not required. To create the relationship we need to click on the Attribute Relationship tab. As shown in the following illustration, we currently do have attribute relationships setup; however all of them directly link each attribute back to the Date Key, the lowest level. No intermediate relationships exist to efficiently rollup the hierarchy levels. You will also notice that the attribute relationship tab contains two views. The graphical view is on the top of the below screen print while the detail list view is shown at the bottom.
In order to create a relationship, we can either use the drag and drop method or manual add method via the tool bar button. As shown below, to create our relationship, we actually start at the lowest level and then drag our attribute to the next higher level value. In our example, the Date key is already related to each of the four attributes in the hierarchy. As such, our first level of the relationship, which is Date Key to Week, has already been established. Our next step is to drag the Calendar Week Desc attribute onto the Calendar Month Name attribute.
As you can see in the below illustration, we now have established the Date Key to Calendar Week to Calendar Month relationships.
We could continue to use the drag and drop method to complete our relationship setup or we can use the New Attribute Relationship button. As displayed below, once you click the New Attribute Relationship button, the Create Attribute Relationship window opens. On the left side you select the "lower" attribute level to be added to the higher rollup value which appears on the right side. This screen also shows the Relationship Type option. For attribute values that are changing, this option should be Flexible. However for values that do not change, setting this option to Rigid will tell SSAS during processing that these values will not change with future reprocesses of this dimension. A date dimension is often a great candidate for a Rigid relationship type.
Completing the Calendar Quarter to Calendar Year relationship results in the below relationship setup. Notice that any attributes that do not fall within the relationship appear under the main key, for example Calendar Week.
Furthering our example, we could actually create a second hierarchy, and then establish an additional relationship for our new hierarchy as displayed next. Notice how multiple attribute relationship paths can exist.
Now when we review our hierarchies in the Date TEST dimension our hierarchy warnings are gone.
Next Steps
- Hierarchies are a very useful tool to utilize within SSAS dimensions in order to improve performance of queries related to a dimension. In order to get the most performance boost out of dimensions, we need to establish attribute relationships between each of the different attributes that exist within a dimension. The performance boost occurs, since SSAS can navigate the relationship in order find and aggregate the measure without having to go all the way back down to the dimension key, if an appropriate relationship exists.
- An attribute relationship basically moves from the lowest level of the hierarchy to the highest level. We can create this relationship by either dragging one attribute onto another in a GUI format, or we can use the Create Attribute Relationship window. SSAS warns us within a hierarchy if no relationship exists, but once created, the warning will disappear.
- Check out these additional resources:
- Natural and Unnatural Hierarchies by Chris Web
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
