Hierarchical Dimension

Aug 6, 2009 at 11:57 AM

We have a Company dimension, which has a company key (surrogate key) and parent company Key.

 ...other company information

The parent Key itself maps to the Company Key, this is how to get the Parent's company information.

We need the Parent Company Key to be SCD 2.


1) When using Kimball SSIS SCD how do we cater for the scenario that when a new Company arrives (new entry), the parent Company Key will not be available to be looked up on the Company Dimension as it has not yet been entered into the Company Dimension

2) the Company (existing) is updated to a parent Company that has not yet been added to the Dimension, ie it is a new record. 

Would we need to do two passes into the Dimension?


Aug 6, 2009 at 7:20 PM

I have a similar situation in my Company and Warehouse dimensions.

Yes, you can address this by doing (a little bit more than) multiple passes on the Dimension - you have to do some lookups in between to get the "new" surrogate key for the parent from the updated table.  And you'll have to make as many passes on the table as there are levels in your hierarchy... although you can wire up your process to be dynamic inside a For Loop container by waiting for the outputs of changed rows to dwindle to zero.

However, the largest problem with this architecture is that any change to a "parent" will require that every single child company be updated.  This may result in "explosions" of new rows in your dimension table... I hope you know what you're in for...

Aug 7, 2009 at 9:49 AM

We resolved this by passing the business Keys of each of the company levels - initially we only had companyId - so now we also include companyId, parentId etc .. on the Company dimension. We already had all the business keys on the Staging tables.

We do SCD 2 on these foreign business keys. At this point we have any new records and the historical records.

Then straight after this process we do an SQL Update to update each of the foreign surrogate keys (parentKey etc).

Thanks for the warning about "explosions" in the child company records - we don't expect too many changes here, but will definitely watch out for this in future hierarchies.