Every now and then I see an internal mail with a query and I think “What were they thinking?” In this case a customer was trying to build a hierarchy that is built from attributes that are in different dimensions.
My immediate reaction to this arises because if attributes (Say A,B & C) that make this hierarchy are splattered across other dimensions then the underlying modeling was not done properly as they should all be in the same set of dimension tables, or ideally in the same table already as I am not a huge fan of snowflake schemas.
But there’s nothing to stop you building your cube over an operational system and so on reflection this is a valid question, so what’s the answer?
When you are designing your dimension in the BI development studio, you need to include all of the tables you need in the design pane and to have already declared how to join those tables together in the data source view. You can than drag in the attributes you need into the dimension where you need this hierarchy.
If you need those attributes in another dimension to take part in another hierarchy then you just reuse the tables you need in the designer for that dimension.
So as usual the customer is always right!