Extend Dimension like Bridge

View previous topic View next topic Go down

Extend Dimension like Bridge

Post  rk1234 on Tue Jul 28, 2015 11:19 pm

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?

rk1234

Posts : 5
Join date : 2014-07-22

View user profile

Back to top Go down

Extend Dimension like Bridge

Post  zoom on Wed Jul 29, 2015 7:54 am

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.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Extend Dimension like Bridge

Post  rk1234 on Thu Jul 30, 2015 12:30 pm

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.

rk1234

Posts : 5
Join date : 2014-07-22

View user profile

Back to top Go down

Re: Extend Dimension like Bridge

Post  ngalemmo on Thu Jul 30, 2015 10:43 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Extend Dimension like Bridge

Post  rk1234 on Fri Jul 31, 2015 10:12 am

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.

rk1234

Posts : 5
Join date : 2014-07-22

View user profile

Back to top Go down

Re: Extend Dimension like Bridge

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