In a Data Warehouse, you’ll typically get at least one slowly changing hierarchy. You’ll probably also have other

slowly changing attributes on the same dimension that are not in the hierarchy, causing many changes.

Setting up this scenario in SSAS 2005 is easy, but it is also easy to do it wrong! Therefore I thought it would be

useful to blog on how I do this and what potential pitfalls one can come across when attempting this for themselves.

Consider a Product dimension that has a Supplier hierarchy. The business wants this hierarchy to change over

time. Additionally there is another Type-2 attribute: RRP, which also changes over time.

So, Product ‘1234’ changed from SupplierGroup1->SupplierA to SupplierGroup1->SupplierB on ‘2002-Feb-01’.

That supplier didn’t work out so it went back to SupplierGroup1->SupplierA on ‘2002-Apr-01’. Also on’2002-Mar-01’ the price changed from £9.99 to £10.99.

If browsing the cube by Supplier Hierarchy and Date you would want to see all sales for Jan-2002 and Apr-2002

onwards to by SupplierGroup1->SupplierA and from Feb-2002 to end of Mar-2002 to SupplierGroup1->SupplierB.

So, lets see how you might go about setting this up in SSAS 2005.

(For the purpose of this blog I have setup 3 dimensions all using the same Product dimension but setup

differently and created a Fact Sales that has 1 sale for every day from 2002-Jan-01 to 2002-Apr-30).

Initially you might set this up like a normal type-1 hierarchy that does not track history, and expect SSAS to sort it out when things change. This is what I call the Bad Hierarchy Product dimension.

Bad Hierarchy Product Dimension

In this scenario we have created a Supplier hierarchy that goes Supplier Group->Supplier->Product Code. The product code being the natural key of the product dimension e.g. ‘1234’.

You can see by the attribute relationship that Product Code rolls up into Supplier. Well, lets see what results that gives us.

Hmm, not quite what we were hoping for. The hierarchy doesn’t seem to have changed at all!

Well this is easy to explain. In the attribute relationship we have told SSAS that Product Code rolls up into Supplier. When SSAS processes dimensions it will retrieve the distinct value of every attribute and then aggregate them based on attribute relationships we define. SSAS will only have one value for product code ‘1234’ and that is, yep you guessed it, ‘1234’. SSAS will then find the first Supplier it finds for Product Code ‘1234’ and say, “right that’s it I’ve got what I want, no need to do anything else” hence the supplier hierarchy never changes.

So the solution is simple:

Type-1 attributes are attributes of the natural key (Product Code)

Type-2 attributes are attributes of the surrogate key (Product Key)

So having just worked out this rule, you may try building the hierarchy by using the surrogate key as the bottom level of the hierarchy. I call this the duplicate product dimension

Duplicate Product Dimension

This time, we have used the Product Key as the lowest level of the hierarchy, and all Type-2 attributes are attributes of the surrogate key. For the Product Key attribute we have changed the ‘Name’ property to be the Product Code, but the ‘Key’ property to be the surrogate key.

So lets see what results that give us.

Hmm well the good news is that the hierarchy has changed, but we seem to be getting duplicate product codes beneath each one.

Well again this is easy to explain. We have 4 surrogate keys for product code ‘1234’. We have set the hierarchy to roll up from Product Key to Supplier. SSAS does not know that product key 1,2,3 and 4 have the same Product code and therefore you want to group by them so it just adds them all.

So, at least our numbers above product code are correct, but this will still not do!

So let see how we do this properly.

Product Dimension

We create a new attribute in the dimension called ‘SupplierLeaf’ that has composite keys: Supplier Group, Supplier and Product Code and set the ‘Name’ property to ‘Product Code’.

We then setup the hierarchy as follows.

So we use the new attribute ‘SupplierLeaf’ as the lowest level of the supplier hierarchy, but rename it to ‘Product Code’ within the hierarchy. We set supplier to be an attribute of Supplier leaf and set supplier leaf as an attribute of the surrogate key. So lets see how this looks.

Bingo! we done it correctly. So, for every slowly changing hierarchy in a dimension you need to add a new ‘Leaf’ attribute. to get it to work correctly.

One you have set this up, it is quite logical and easy to understand how to to it. But as I say, it is easy to get wrong. I hope this helps