Surrogate keys and Dimension-to-Dimension links

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

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.