MSBI (SSIS/SSRS/SSAS) Online Training

Monday, May 31, 2010

SSAS - slowly changing dimension type 2 in ssas

This scenario is from Erik like –
I have an SCD Type 2 dimension on customer demographics. I am in the process of creating a dimension using SQL Server Analysis Services, including hierarchies on for example geographic data and marketing hierarchy tiers. For example, I have the following hierarchy on geographic data:Continent --> Country --> State --> City --> Account (business key) --> Account Dimension (primary key)The deepest tier, the account dimension, is completely irrelevant for the end user, therefore when an end user browses the cube, the last tier should be completely hidden. Also, when some data of the account changes, I'll have an end date and new start date when it's an SCD Type 2 change. If I set the Account Dimension key in the hierarchy on Account Dimension and Account, I'll have the account listed twice when I browse the cube (I can't only set the account dimension key to the account dimension key field, since I'll have duplicate keys in the hierarchy when an account for example changes cities).
Useful links:
http://pedrocgd.blogspot.com/2007/10/bi-casestudy-slowly-changing-dimension.html
http://blogs.microsoft.co.il/blogs/barbaro/archive/2007/09/08/Slowly-Changing-Dimension-type-2-in-SSAS.aspx