SCD Type 2 doubt
2 posters
Page 1 of 1
SCD Type 2 doubt
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!
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
Re: SCD Type 2 doubt
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).
Re: SCD Type 2 doubt
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
Re: SCD Type 2 doubt
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?'
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?'
Similar topics
» Design0-Doubt
» Why do I need type 3 and 6 SCDs when I can implement type 7?
» Type 2 dimension or type 2 column?
» Kimbal Fact Table Type - Transactional Fact Type Issue
» rationale behind dimension with Type 0 and missing Type 5
» Why do I need type 3 and 6 SCDs when I can implement type 7?
» Type 2 dimension or type 2 column?
» Kimbal Fact Table Type - Transactional Fact Type Issue
» rationale behind dimension with Type 0 and missing Type 5
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|