SSAS Cube structure of SCD Type 2 dimension

View previous topic View next topic Go down

SSAS Cube structure of SCD Type 2 dimension

Post  emonchen on Thu May 20, 2010 3:21 am

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).

Does anyone have experience with this in SQL Server Analysis Services, and when yes, how can I solve this?

Thanks!
Erik

emonchen

Posts : 12
Join date : 2010-02-11
Age : 39
Location : Delft, The Netherlands

View user profile

Back to top Go down

Re: SSAS Cube structure of SCD Type 2 dimension

Post  VHF on Sat May 22, 2010 1:37 pm

With the hierarchy you've shown an Account would need to show up twice if it changes cities, becuase it would exist both under the old city and under the new city. I would suggest separating the truely geographic attributes and other account attributes into separate hierarchies:

Continent --> Country --> State --> City
Account (business key)

An account will only show up twice if drilling down by City, then Account (and that particular account has fact data for both cities.)

I'm a little unclear whether the "Account Dimension (primary key)" attribute (to which you do not want the users to drill down) is actually a business data field or if it is just representing a unique row in your SCD2 dimension.


Last edited by VHF on Sat May 22, 2010 1:38 pm; edited 1 time in total (Reason for editing : clarity)

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: SSAS Cube structure of SCD Type 2 dimension

Post  emonchen on Thu Jun 03, 2010 9:29 am

Hi,
Thanks for your reply. I have built the hierarchies in such a way that the primary key value in the SCD2 dimension is always the primary key. So you're exactly right what you're saying in the last sentence, that the Account Dimension key is just representing a unique row in the dimension. When I build the hierarchy until City, then it works that I can't drill down further, but what I'm getting now is that the city is listed twice...

The first hierarchy makes sense that I could remove the last level of the hierarchy as I have it right now, but why should I create a hierarchy that just has the Account field?

Thanks!

emonchen

Posts : 12
Join date : 2010-02-11
Age : 39
Location : Delft, The Netherlands

View user profile

Back to top Go down

Re: SSAS Cube structure of SCD Type 2 dimension

Post  VHF on Thu Jun 03, 2010 10:11 am

You're right--there's really no need to create a hierarchy with just one attribute. (My understanding is that SSAS basicly does this automaticlly behind the scenes anyway--essentialy creates a "hierarchy" for each attribute.)


Last edited by VHF on Fri Jun 04, 2010 6:41 pm; edited 1 time in total (Reason for editing : typo)

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: SSAS Cube structure of SCD Type 2 dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum