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
Because of the attribute relationship, the value of attribute1 is determined based on attribute2, like this:
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.