Data Warehousing and Business Intelligence

5 January 2010

Many-to-many in attribute relationship

Filed under: Analysis Services — Vincent Rainardi @ 7:58 am

In SSAS cube there is a potential that hierarchies are not working correctly because of attribute relationship. Say in the cube we have a hierarchy consisting of:

  • Top level: attribute1
  • 2nd level: attribute2

We also have an attribute relationship that links attribute1 to attribute2. If you click + on attribute2, you can see attribute1 appears ‘under’ attribute2.

And the data in the dim table is as follows:
select attribute2, attribute1, count(*) from dim1 group by attribute2, attribute1

Attribute2 Attribute1 Count
Member1 Member A 600
Member1 Member B 500
Member2 Member A 200
Member2 Member B 300

Because of the attribute relationship, the value of attribute1 is determined based on attribute2, like this:

Attribute2 Attribute1 Count
Member1 Member A 1100
Member2 Member B 500

Basically, if we create an attribute relationship from attribute2 to attribute1, we tell SSAS to determine the value of attribute1 based on the value of attribute2. I’m not sure how SSAS determine the value of Attribute1 for Member1 and Member2: whether it’s Member A or Member B. In my experience it can be either (but not both).

I experience this most when cubes are upgraded from 2005 to 2008. But this is actually not an upgrade problem. It’s an attribute relationship issue: we shouldn’t create the attribute relationship if it’s many-to-many (see red text above). Member1 can belong to Member A, and it can also belong to Member B. That’s many-to-many. We should only create attribute relationship only when it’s many-to-one: Member1 will always belong to Member A. And this principle applies to both 2005 & 2008.

Flexible or rigid doesn’t come into it. This many-to-many and many-to-one has nothing to do with the attribute relationship being flexible or rigid. If Member1 will always belong Member A (like January belongs Q1), then it’s rigid. But if Member1 now belongs Member A but later it can belong to Member B, then it’s flexible.

But how can we create a hierarchy if it’s many-to-many. The simple answer is you can’t. Without hierarchy users can still list attribute1 and attribute2 side-by-side on the row, which will reveal their relationship. So on the attribute relationship tab (on 2008), if you have many-to-many, just link both attributes to the key.

4 Comments »

  1. […] Be careful when defining attribute relationships. They could produce incorrect results. Product1 is currently under category1, but next year it could be under category2. See this post. […]

    Pingback by Top 10 Tips: Building Cubes « Data Warehousing and Business Intelligence — 7 July 2010 @ 7:12 pm | Reply

  2. This is very good help full, save my time a lots.

    Comment by Nagendra Prasad — 7 September 2010 @ 5:44 am | Reply

  3. Here are this and some other articles on Many-to-Many Dimension:

    http://ssas-wiki.com/w/Articles#Many-to-Many_Dimension

    Comment by Sam Kane — 17 March 2011 @ 1:31 pm | Reply

  4. […] Be careful when defining attribute relationships. They could produce incorrect results. Product1 is currently under category1, but next year it could be under category2. See this post. […]

    Pingback by Top 10 Tips: Building Cubes « ellynguyen — 3 June 2011 @ 3:45 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: