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