Changing Attribute Relationships for existing Hierarchy

In our project we have the Customer Dimension, with the Hierarchy Country Name – City – Customer. This should by definition be a Natural Hierarchy – One Country has Many Cities and one City has many Customers.

When hovering over the warning sign of that hierarchy we are told that there are no Attribute Relationships between the different levels of the Hierarchy.

By clicking the Attribute Relationships Tab of the Dimension we get the picture.

Each non-key attribute is only related to the key attribute.

This means that every query must go to the Key attribute and access data at the lowest level of detail of the fact table – in this case the Customer No level.

A certain query for Revenue per Country would then have to identify every transaction from every Customer in every City within a certain Country to “roll up” all the transactions for that particular Country.

If we “explain” to Analysis Services that Customers can be rolled up into Cities and Cities can be rolled up into Countries and combine this with appropriate Aggregations – e.g. per Customer or per City we will in many cases reduce query time since less transactions have to be rolled up.

Our Query for Revenue per Country might be answered by Analysis Services by Adding up totals for Cities belonging to a particular Country – much less work than having to sum up all transactions at the lowest level!

We relate the attributes of each level to one another by dragging the lowest level to the next level – in this case Customer to City.

Now we are halfway – next operation is to drag City to Country Name.

That’s it. Now we have attribute relationships that define the relations between the different levels of this Hierarchy.

By rearranging these Attribute Relationships we have now stated to Analysis Services:

• One Country can contain many Cities and a certain City always belongs to only one Country
• One City can contain many Customers and a certain Customer can only belong to one City

Depending on data this may turn out to be untrue.

• There may be a customer named Johnson in different Cities.
• There may be a City named Springfield in different Countries.

To make sure that this will never occur we have to make composite keys that ensure that each level of the Hierarchy can be uniquely identified.