SCD Type 2 doubt

View previous topic View next topic Go down

SCD Type 2 doubt

Post  sr123 on 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

View user profile

Back to top Go down

Re: SCD Type 2 doubt

Post  ngalemmo on 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).
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: SCD Type 2 doubt

Post  sr123 on 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

View user profile

Back to top Go down

Re: SCD Type 2 doubt

Post  ngalemmo on 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?'
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: SCD Type 2 doubt

Post  sr123 on Tue May 01, 2012 3:00 pm

Ok, thanks much for the clarification!

sr123

Posts : 10
Join date : 2012-03-05

View user profile

Back to top Go down

Re: SCD Type 2 doubt

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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