Extend Dimension like Bridge

[rk1234]

I've an situation of adding brand new (type-2) dimension but that relates to another huge (type-2) dimension. I'm kind of wondering what are the best options to cover this.

Example: Account Dimension (type-2) exist already. New feature came in to create new dimension Location which needs AccountKey
ie. Account (AccountKey,....EffectiveDate, ExpireDate)
Location(LocationKey,AccountKey......EffectiveDate, ExpireDate) ------> Account Relationship is necessary

What are the best options to cover this

Option1: Create new (type-2) dimension LOCATION with AccountKey and maintain all changes on either side (Account OR Location)
with Effective & Expire dates. That way, Account dimension is untouched and manage all changes in LOCATION dimension only.

Option2: Create new (type-2) dimension LOCATION and create a Bridge Table to keep track of relationships of Account & Location dimensions.
ie. Location dimension (LocationKey, ... EffectiveDate, ExpireDate) ---> NO Facts associated with this.
Plus AccountLocation (AccountKey, LocationKey) ----------> 2 attributes only. Account & Location relationship will be maintained here.

Which option is best and easy to implement and less cumbersome to query the dimensions on reporting side.
Is anybody face this kind of situation?

[zoom]

You need a bridge table if an account has more than 1 location tie to it in a current time. If an account can have only one current location all the time, then you do not need a bridge table. In that case, Location dim is not keeping any history... meaning it is not type 2 dim. A relationship between an account and location can be captured in the Fact table with start and end date in the fact table.

If an account has more than 1 location in a current time, then you need a bridge table and that bridge table can have that relationship start and end date.

[rk1234]

Thanks for update!. yes bridge table is required. but can we achieve the bridge with location dim it self. i.e. location dim (type-2) with accountkey and manage the data on every change of location & account with effective and expiredates.

[ngalemmo]

Technically, it is not a bridge, but a fact table.  It is tracking a business state, that is the relationship between an account and location over time.  It would be a bridge if an account may have multiple locations at the same time and it is maintaining the current association, no history.

The fact would be stable, even with the type 2 keys. You would not need to add rows if there is a change in either dimension. If you need the current dimension row you would use the same technique to access the current row as you would with any type 2 dimension.

[rk1234]

That means, Two (type-2) dimensions (Account & Location) and One Fact (with AccountKey,LocationKey only) with out any measures. The new dimension (Dimlocation has many attributes) is for reporting purpose only (grouping accounts by location only). I was picturing like this 1) DimAccount (type-2) 2)DimLocation (type-2)
3) DimAccountLocation(accountkey,locationkey). since there were no measures on Acct & Loc combination, thought of putting @dimension layer only.

[Sponsored content]