Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

SCD Type 2 doubt

2 posters

Go down

SCD Type 2 doubt Empty SCD Type 2 doubt

Post  sr123 Tue May 01, 2012 12:07 pm

Hi,

I have some questions with Type 2 dimensions.
All my dimension tables are scd type 2, these records are constantly updated in the source system and I am required to capture all history. And all these source tables (which are my dimensions) have sys_created_on and last_updated_on timestamp columns...would I still need to add begin_date and end_date columns? Is there any benefit in doing so? I am planning to add a dw_load_dt and active_flag columns especially since these are weekly loads and I think it helps to know when the data was loaded into the DW.
Any help is highly appreciated.

Thanks in advance!


sr123

Posts : 10
Join date : 2012-03-05

Back to top Go down

SCD Type 2 doubt Empty Re: SCD Type 2 doubt

Post  ngalemmo Tue May 01, 2012 2:09 pm

I think it helps to know when the data was loaded into the DW.

You answered your own question. Yes, it absolutely helps to know when information was loaded. If you get good timestamps from the source, then tracking both source (i.e. business) effective period and DW effective period is a good thing to do. It tells you both when it happened (business timestamps from source) and when the DW knew about it (system timestamps).
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

SCD Type 2 doubt Empty Re: SCD Type 2 doubt

Post  sr123 Tue May 01, 2012 2:42 pm

Thanks for answering! So you're saying that I do not need to further add begin and end date columns again correct? DW load date along with source date columns would suffice right? Just making sure I understood this right.


sr123

Posts : 10
Join date : 2012-03-05

Back to top Go down

SCD Type 2 doubt Empty Re: SCD Type 2 doubt

Post  ngalemmo Tue May 01, 2012 2:53 pm

No, I'm saying have both if you can.

For example, someone runs a DW report and is aware of a change they made to a customer the previous day. For some reason, the report doesn't reflect the change. The next day they run the report and everything looks good. Why?

If you have both sets of timestamps, one reflecting what happened in the source system and one reflecting what happened in the DW load, you would be able to clearly identify what happened. There are a lot of things you can do with this data. For example, you can measure service levels within the DW... such as 'how long does it take for a data change to make its way to the DW?'
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

SCD Type 2 doubt Empty Re: SCD Type 2 doubt

Post  sr123 Tue May 01, 2012 3:00 pm

Ok, thanks much for the clarification!

sr123

Posts : 10
Join date : 2012-03-05

Back to top Go down

SCD Type 2 doubt Empty Re: SCD Type 2 doubt

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum