All Forums Data Modeling
Raja_KT 1246 posts Joined 07/09
18 Sep 2013
History maintenance

Hi,
Which one will consume less space if I am to implement a centralized DWH ,( I am referrring to Historical data) if I am a designer
1) Implementing Dimensional model SCD or
2) Using VALIDTIME and TRANSACTIONTIME, bitemporal feature inTeradata?
Please share your thougts.
Thanks and regards,
Raja

Raja K Thaw

My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1

Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
18 Sep 2013

Hi Raja,
a SCD Type 2with TIMESTAMPs  is similar to uni-temporal using TRANSACTIONTIME.
But bi-temporal consumes more diskspace because you need an addtitional PERIOD for the VALIDTIME :-)
 
Dieter

Dieter

Raja_KT 1246 posts Joined 07/09
24 Sep 2013

Hi Dieter,
Thanks for the response. So I will not use bitemporal . Rather I will use Timestamp.
I am just curious about this design I have in mind. I have SCD type 2 table. I know exposing views on top of 3NF for presentation layer is a good option.  For SCD Type 2, should I use base historical table and one view holding current records to avoid performance issue?
Even I am using the term SCD type 2 in 3 NF :). Really a modeling issue or what is the best way to maintain history in 3 NF ?????
Thanks and regards,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
25 Sep 2013

Hi Raja,
you can use PPI to put all currently active rows in a single partition to avoid splitting it into two tables.
If you don't want to call it "Slowly Changing Dimension" in a 3NF data modell you might simly name it "Slowly Changing Data" instead :-)
 
Dieter

Dieter

You must sign in to leave a comment.