Bitemporal vs SCD

[TheDarkKnight]

can anyone really present the difference between Bitemporal architecture vs the SCD2.
why is the one better than the other?

[rathjeevesh]

SCD type 2 is driven by surrogate key concept which is a primary key for the dimension table. Historical information about the dimension can be easily stored and retrieved using SCD 2 technique.

Bitemporal design approach will add lot of complexity while handling dimension to fact relationship. Either all queries need to have a restriction of which record you want to retrieve (current/history) or you need to have a multipart (composite) primary key foreign key relationship from dimension to fact. Else there is every possibility of having many to many relationships between dimension & fact.

Thus using SCD 2 technique in DW is effective and it is a key differentiator between Kimball & Inmon approach.

-JR

[BoxesAndLines]

Sure, with bi-temporal you have two sets of dates. One set for the business effectivity. One set for dw effectivity.

[temporalcraig]

you may also find a couple other previous threads on this forum helpful.:

http://forum.kimballgroup.com/t1776-using-bitemporal-dimensions-to-show-corrections

http://forum.kimballgroup.com/t990-bi-temporal-as-at-reporting

craig

[Sponsored content]