migrate quarter snapshot dim tables to scd type 2
2 posters
Page 1 of 1
migrate quarter snapshot dim tables to scd type 2
Hi,
We currently have a star schema with dimension tables that are setup as quarterly snapshots. So each quarter, the dim tables get loaded with all the data, so nothing gets overwritten from the previous quarters. The Dim tables have a begin and end date to store the quarter values (e.g. Begin Dt = 01-01-2014, End Dt = 3-31-2014).
we've come to the conclusion that we should change the dim tables to be true SCD type 2 tables in order to not store each quarter, but only insert new rows if one of the fields changes from the previous version. This way, we will be removing many records of redundant data from the quarterly snapshots.
I'm looking for a good approach that will go through the old quarterly snapshot tables and load the new SCD type 2 Dim tables correctly.
has anyone done this type of task in the past that could share some ideas?
thanks
scott
We currently have a star schema with dimension tables that are setup as quarterly snapshots. So each quarter, the dim tables get loaded with all the data, so nothing gets overwritten from the previous quarters. The Dim tables have a begin and end date to store the quarter values (e.g. Begin Dt = 01-01-2014, End Dt = 3-31-2014).
we've come to the conclusion that we should change the dim tables to be true SCD type 2 tables in order to not store each quarter, but only insert new rows if one of the fields changes from the previous version. This way, we will be removing many records of redundant data from the quarterly snapshots.
I'm looking for a good approach that will go through the old quarterly snapshot tables and load the new SCD type 2 Dim tables correctly.
has anyone done this type of task in the past that could share some ideas?
thanks
scott
scabral- Posts : 58
Join date : 2012-05-02
Re: migrate quarter snapshot dim tables to scd type 2
Snapshot dimensions? Never heard of that one…
Once you have your type 2 load logic coded and tested, the easiest thing to do is start loading it from your snapshot one quarter at a time, chronologically.
Once that is done, it is a matter of rekeying facts that used the snapshot. Compare the snapshot to the new dimension and build a cross reference of old and new dimension keys based on effective dates then use the xref to apply the new keys to the facts.
Once you have your type 2 load logic coded and tested, the easiest thing to do is start loading it from your snapshot one quarter at a time, chronologically.
Once that is done, it is a matter of rekeying facts that used the snapshot. Compare the snapshot to the new dimension and build a cross reference of old and new dimension keys based on effective dates then use the xref to apply the new keys to the facts.
Re: migrate quarter snapshot dim tables to scd type 2
thanks ngalemmo ,
i'm not sure what the kimball term would be for this "snapshot" dimension. We basically built it this way so that if we had to rerun a particular quarter, we could just delete the rows from the dims and facts for that quarter and just re-run the process using the specific quarter begin and end dates. This way, nothing else gets changed or updated except for the quarter being re-run. This was originally a need that the business gave us, so that's how we modeled it. Maybe type 0? or some hybrid method, not sure.
i think a merge statement would work best to load the dim tables. I've seen some example of the merge statement begin used to load type 2 dimensions, so I think I will go that route. One quarter at a time is definitely the way to go.
thanks
scott
i'm not sure what the kimball term would be for this "snapshot" dimension. We basically built it this way so that if we had to rerun a particular quarter, we could just delete the rows from the dims and facts for that quarter and just re-run the process using the specific quarter begin and end dates. This way, nothing else gets changed or updated except for the quarter being re-run. This was originally a need that the business gave us, so that's how we modeled it. Maybe type 0? or some hybrid method, not sure.
i think a merge statement would work best to load the dim tables. I've seen some example of the merge statement begin used to load type 2 dimensions, so I think I will go that route. One quarter at a time is definitely the way to go.
thanks
scott
scabral- Posts : 58
Join date : 2012-05-02
Similar topics
» Periodic snapshot or Transaction type of FI
» Accumulating Snapshot Fact OR Type 3 Dimension?
» How to Track SCD Type 2 for Accumlating or Periodic Snapshot
» Type 2 Dimension with accumulating snapshot with time stamp
» Periodic snapshot fact tables with sparse data
» Accumulating Snapshot Fact OR Type 3 Dimension?
» How to Track SCD Type 2 for Accumlating or Periodic Snapshot
» Type 2 Dimension with accumulating snapshot with time stamp
» Periodic snapshot fact tables with sparse data
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum