Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Analysis Services Attribute Relationships


By:   |   Read Comments (4)   |   Related Tips: > Analysis Services Dimensions

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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:

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.

Dates 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.

New Hierarchy

warning

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.

attribute relationship new

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.

Drag Week to Month

As you can see in the below illustration, we now have established the Date Key to Calendar Week to Calendar Month relationships.

Week to Month Attribute relationship

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.

New Button

Create New Attribute Relationship

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.

Final Attribute Relationship

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.

Multiple Attribute paths

Now when we review our hierarchies in the Date TEST dimension our hierarchy warnings are gone. 

No Hierarchy Warning
Next Steps 


Last Update:


signup button

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, June 02, 2016 - 1:33:26 PM - Jeroen vs Back To Top

Thanks a lot for this explanation, this helped me pin-point and solve an issue with using the Cube date dimension through Power BI. 

 


Saturday, December 27, 2014 - 2:43:59 PM - mouse Back To Top

Scott, That would call for a custom hierarchy. Create your own date tally table and assign week numbers as desired, by logic or otherwise. Then create your hierarchy using your custom source.

Ron, No. Even if the weeks duplicate over months, the months themselves being different aggregate the related data appropriately.


Friday, December 19, 2014 - 10:10:28 AM - Scott Back To Top

Ron,

 

This items is just an example, but I have worked with clients who assign weeks to specific months especially in a retail environment.


Friday, December 19, 2014 - 9:15:25 AM - Ron Back To Top

This is a topic I'd like to understand better, especially as it relates to the date dimension.  But I'm puzzled by your inclusion of the weeks under the months as it's not a clean relationship.  Rarely do months start at the start of a week.  Won't this cause issues?


Learn more about SQL Server tools