History maintenance

Data Modeling
Enthusiast

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

3 REPLIES
Junior Contributor

Re: History maintenance

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

Enthusiast

Re: History maintenance

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

Junior Contributor

Re: History maintenance

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