My ex-colleague Jon might spend far too much time pseudonymously posting silly comments on blogs, but he deserves an extra helping of chocolate mousse for finding this problem to do with redundant attribute relationships – this is something everyone designing AS2005 cubes needs to be aware of!
Imagine you had a Geography dimension with three attributes: City (the key attribute), State and Country, where each Country has many States, and each State has many Cities. Now you probably already know that if you have built your dimension with the wizard, the chances are that your dimension will have two relationships pre-built – between City and State and City and Country – and that you need to change this so that there are relationships between City and State and State and Country in order to get optimal performance and to help aggregation design. AS2005 understands transitive relationships between attrbutes, and so even though there are just two relationships built in your dimension it knows that because City is related to State and State is related to Country, there is a transitive relationship between City and Country.
BUT, did you know that bad things can happen if you actually build the relationship between City and Country in your dimension? Before Jon found out about this, I had understood that the only effect was that having this redundant relationship might increase the size of the dimension and increase processing times. However, it turns out there are two other potentially serious side-effects:
1) MDX Script Scopes may include unexpected extra cells. So, for example, if you’re assigning values to a subcube in your MDX Script (and most of the calculations created by wizards in BI Dev Studio do this) then you may see incorrect results with the redundant attribute relationships in place.
1) MDX Script Scopes may include unexpected extra cells. So, for example, if you’re assigning values to a subcube in your MDX Script (and most of the calculations created by wizards in BI Dev Studio do this) then you may see incorrect results with the redundant attribute relationships in place.
2) Querying fact data may ignore some slices or return Nulls on certain related attributes when the measure group has granularity on an attribute different from the key attribute.
Apparently what’s happening is that, using our example, the redundant relationship between City to Country would cause AS to ignore the legitimate relationship between State and Country. Even more confusingly, if you were to create a user hierachy from Country to State to City then the incorrect behaviour would go away.
So the moral of all this is to be extra-careful when designing your dimensions and not to let any redundant attribute relations remain in there. This problem won’t be fixed in SP1 but there will be a warning in the dimension editor UI when redundant relationships are detected, though, which should make more people aware of it.
Apart from Jon, thanks also to Matt Carroll and Marius Dumitru on the AS dev team for taking the time to explain what’s going on here to me.
Hey, stop giving other people the credit for my amusing and insightful comments, Chris!
Colin
The wizard will *not* create two relationships for you. The wizard will only create relationship to dimension/table keys.Theere are numerous problems with redundant attributes, We\’ve added a check in the dimension designer for SP1 to detect and report this condition.-rob
Rob,
Chris isn\’t suggesting the wizard creates two redundant relationships for you, he\’s saying that the wizard will automatically infer that every non-key attribute is related to the key attribute and will then create these relationships automatically (i.e. City->State and City->Country in his example). Since this will typically not reflect the complete set of relationships in the dimension (i.e. State-Country), the user must then manually add in relationships between non-key attributes, and remember to remove the extraneous relationships to the key attribute (i.e. City->Country) so as to avoid the problems with redundant relationships.
The exception here (as you imply) is that if you have a snowflaked dimension schema then the wizard will infer the "correct" relationships, based on the table keys.
Jon
I couldn\’t have put it better myself, Jon. Thanks!
… "this is something everyone designing AS2005 cubes needs to be aware of" …
If it was something everyone should know, don\’t you think they would have put it in the very extensive and helpful books online for AS2005? 😉 I continually find myself wasting valuable time trying to find good information in books online.
More to the particular topic, I was blown away by the problems that redundant attribute relationships cause in AS2005. When I read the new warning, I initially thought to disregard it because it only spoke of problems when the dimension was being used on measuregroups with a granularity that was different than the key attribute. I think they understate the potential problems.
Also, I find it hard to believe that "less is more". Can\’t SSAS just disregard the extra meta-data information that it finds to be extraneous? I have always believed that the more information you have available to you, the better decisions you can make when solving software problems. Attribute relationships should be no different.
Anyway, thank you for your blog. It\’s time better spent than reading books online. Would you mind reviewing some new AS2005 books sometime?
P.S. I checked Adventureworks and it has redundant attribute relationships too.
I think people on the AS dev team would be happy to admit that BOL wasn\’t where it should have been at RTM, but I do know they have been working hard on it ever since and that it\’s getting better.
Re AS2005 books, I\’ve already done one or two and still have \’The Microsoft Data Warehouse Toolkit\’ to go. If any authors out there want to send me free copies of books I haven\’t got to review I\’d be more than happy to receive them 😉