migrate quarter snapshot dim tables to scd type 2

View previous topic View next topic Go down

migrate quarter snapshot dim tables to scd type 2

Post  scabral on Sat Dec 20, 2014 12:02 pm

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

scabral

Posts : 58
Join date : 2012-05-02

View user profile

Back to top Go down

Re: migrate quarter snapshot dim tables to scd type 2

Post  ngalemmo on Sat Dec 20, 2014 3:08 pm

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.


avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: migrate quarter snapshot dim tables to scd type 2

Post  scabral on Sat Dec 20, 2014 8:07 pm

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

scabral

Posts : 58
Join date : 2012-05-02

View user profile

Back to top Go down

Re: migrate quarter snapshot dim tables to scd type 2

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