SSAS Cube structure of SCD Type 2 dimension

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

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.

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!

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