Attribute relationship example

Setting proper attribute relationships for the attributes of a dimension is essential for query performance and usability. As Mosha wrote in his blog, it might be the most important advice for cube designers to set the attribute relationship correctly. The full article can be viewed here and it is very detailed.

I still saw some cases recently where the improper use of attribute relationship settings caused wrong query results (which might even be worse than a low performance), so I decided to post a wrap up of this topic here.

First, you need to know that setting attribute relationship has these benefits:

• Better query performance
• More efficient methods for the cube to store the data of the hierarchies
• Easier way to write MDX queries (as the dependant attribute is correctly positioned and can be referenced e.g. by currentmember), see below for an example
• Dependant attribute may be used as metadata (especially helpful when the dependant attribute has it property „Enable attribute hierarchy“ set to „false“)
• Higher ranked attribute may be used as „Order-By“-attribute.

Let me provide an example for the MDX query. Think of a sample cube with a date dimension containing two attributes for month and monthname and think about the following query:

if there is no relationship between the month and monthname-attribute the monthname-attribute is not changed when selecting the month-attribute and therefor resides its default which is ‚All‘.

But if there is a relationship, binding the monthname-attribute to the month-attribute, the monthname-attribute is automatically set to the corresponding member:

This shows how query syntax is influenced by the proper use of attribute relationship. In the first example we had to try something with the linkmember() MDX-function to achieve the same result. So designing the attribute relationship in a proper way leads to easier to understand queries with a better performance.

The data was loaded from a very simple date dimension table like shown below:

The attribute relationship can be modeled in BIDS when designing a dimension. After creating the dimension using the wizard, all attributes are associated to the key attribute of the dimension as shown below:

The attribute ‚Month‘ and ‚Monthname‘ have no relationship but are simply tied to the attribute „Dim Time‘ which is the key attribute for our simple time dimension. If you build a hierarchy, like the YearMonth-hierarchy in the screenshot above, you’ll notice a yellow warning sign informing you that there is no relationship between Month and Year. You’ll get further information when placing the mouse coursor on the warning sign:

Whenever you see this yellow warning sign we’re talking of a non-natural hierarchy.

The relationship can easily be set by dragging the ‚Year‘ attribute to the ‚Month‘ attribute. Now the same dimension looks like this:

Notice that the yellow warning sign in the hierarchy title has disappeared. In this case we’re talking of a natural hierarchy.

But be warned: This doesn’t mean that your dimension is healthy now. For our simple example we actually made a big mistake. If you check the dimension in the dimension browser, you’ll see something like this:

There are no more months for the years 2006 to 2008 anymore! This also means that no fact data will be displayed for those months and that the aggregation will be wrong.

If you model an attribute relationship like Month -> Year you have to be absolutely sure that every month corresponds to exactly one year. You can verify this using a simple SQL query like the one below:

This query should return no rows at all but in our case it returns

So every month in our source table is mapped to four years which breaks our attribute relationship above.

If you’re having BIDS Helper installed on your machine you can also do a dimension health check from the dimension context menu.

In our case the result looks like this:

You can clearly see the rows that break our relationship.

So in this simple example it would have been a bad advice to simply set the attribute relationship as we did above. You should always check the relationship in your source data before designing such a relationship.

And even if the users say that the relationship is unique, you shouldn’t rely on that. For example a product might belong to a unique product group. But what happens if the association changes over time and you want to keep track of the changes using a slowly changing dimension of type 2. This can also easily break up your attribute relationship. And as the MDX results might differ after changing the relationship, this could also have an influence on existing reports. So it’s a good advise to carefully design your hierarchies before you start creating reports, because it’s not difficult turning a non-natural hierarchy into a natural one. I’ll post an example for this in the next post.