Search This Blog

Loading...

Tuesday, October 28, 2008

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:

with
member MonthMember as [Dim Time].[Monthname].currentmember.NAME

select MonthMember on rows,
[Dim Time].[Month].[Month] on columns

from [TestCube]

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

image_thumb1

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

image_thumb31

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:

image_thumb18

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:

image_thumb3

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:

image_thumb7

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:

image_thumb5

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:

image_thumb9

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:

select count(Year)
from dbo.DimTime
group by Month
having count(Year)>1

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

image_thumb10

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.

image_thumb11

In our case the result looks like this:

image_thumb17

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.

1 comment:

  1. Hi.

    I read a same topic 2 month ago. The topic helps me to improve my competency.

    Apart from that, below article also is the same meaning

    key performance indicators examples

    Tks again and nice keep posting
    Rgs

    ReplyDelete