Surrogate keys and Dimension-to-Dimension links

View previous topic View next topic Go down

Surrogate keys and Dimension-to-Dimension links

Post  amir2 on Thu Jul 14, 2011 8:10 am

Hi

In our Banking scenario, we have a FactDailySnapshot, DimAccount and DimProduct. Each row in FactDailySnapshot has a foreign key (FK) to its associated Account and Product dimensions. The FKs are using the surrogate keys generated during ETL:

FactDailySnapshot
- FK --> DimAccount
- FK --> DimProduct
- EndOfDayBalance

DimProduct
- SK
- ProductType e.g. "Investment", "Savings" --- these are Business Key

DimAccount
- SK (surrogate key)
- ProductType e.g. "Investment", "Savings" --- these are Business Key
AND/OR
- ProductTypeSK

In other words, should the Account dimension link to its Product using the product's SK or use its Business Key, or use both?

An IBM document referes to this issue as Outboard or Outrigger.

Finally, are there simple rules for when Dimensions can be linked to each other?

amir2

Posts : 29
Join date : 2010-07-29

View user profile

Back to top Go down

Re: Surrogate keys and Dimension-to-Dimension links

Post  ngalemmo on Thu Jul 14, 2011 10:46 am

amir2 wrote:
In other words, should the Account dimension link to its Product using the product's SK or use its Business Key, or use both?

An IBM document referes to this issue as Outboard or Outrigger.

Finally, are there simple rules for when Dimensions can be linked to each other?

The simple rule is never. But its never that simple.

In a pure star model, you can associate dimensions through a factless fact table. However you only need to do so to support dimension only reporting.

There are also a few scenarios were snowflaking is a resonable solution.

It is also advantageous to store dimensional FKs in other dimensions for ETL purposes. For example, in your case you may store the account FK in the product dimension to handle data sources that only provide a product reference. This allows you to populate the account FK in the fact table without much effort when appropriate. But you would not use the dimension key in reporting. Usually you just store the surrogate key in such cases.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Surrogate keys and Dimension-to-Dimension links

Post  hang on Fri Jul 15, 2011 9:04 am

amir2 wrote:should the Account dimension link to its Product using the product's SK or use its Business Key, or use both?
When you use SK, you have referential relationship between the two dimension tables, meaning a PK in one table is FK in the other. However when using Business Key, you cannot assume the two tables are referentially related, especially when SCD2 is involved. It's similar to a common attributes appearing in two dimensions.

Outrigger refers to SK reference. In my opinion, if Product is SCD1, product outrigger is preferred model, assuming an account can only have one (principle) product. Similarly to account FK in product, where a product must belong to only one account. Otherwise leave the correlation in the fact table, or use bridge.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Surrogate keys and Dimension-to-Dimension links

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