How do you backfill and SCD type 2 column?

View previous topic View next topic Go down

How do you backfill and SCD type 2 column?

Post  macksv2 on Tue Jan 27, 2015 4:50 am

Hi ,

We have just created a star schema where one of our dimension tables have multiple type 2 scd columns. We have tested this with new data and moving forward, the scd's are working fine (ie it creates new records and we correctly flag the latest record and have the correct start and end dates for previous and current records). The question is, how do we backfill historical records for these scd columns ? We have the data in the source system but is there a technique in backfilling historical records.

Related to this, if you have an established star schema and want to add an attribute in one of your dimensions, could you add it as a type 2 scd and backfill historical data for this attribute?

Thanks.

Regards,

Mike

macksv2

Posts : 2
Join date : 2015-01-27

View user profile

Back to top Go down

Re: How do you backfill and SCD type 2 column?

Post  nick_white on Tue Jan 27, 2015 7:58 am

Hi,
presumably you have historic facts you also want to load, otherwise there is no point in loading historic data into dimensions?
Assuming you do, then you would just normally create the historic Dim records in a staging area and then write the data to your Dim - this would be a one-off process.
You could use your standard SCD process to load this Dim but only if you start with your historic records and load them in order; as you have already loaded your "starting position" and moved forward you'll have to create a one-off process to load your historic data.

New attribute question: yes, you can. If you want to update old Dim records then you'll have to write a one-off data fix to do this. You wouldn't create new historical Dim records as obviously they wouldn't be connected to any facts so there is no point in creating them.

Hope this helps?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: How do you backfill and SCD type 2 column?

Post  ngalemmo on Tue Jan 27, 2015 1:25 pm

As far as the dimension itself goes, it's easy enough to figure out how to retro-fit old data…  but what about the facts?

Presuming you have existing facts that date back to the old data you are trying to load, the challenge will be re-keying the facts so they references the appropriate row.  This is not a minor task.  Overall, it requires about as much effort and testing as new development.

Personally, I hate having to do things twice.  If you have a source with history and you are implementing a Type 2, load it the first time.

As to your second question, sure you can… but again, you need to re-key facts that reference that table.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: How do you backfill and SCD type 2 column?

Post  macksv2 on Wed Jan 28, 2015 12:10 am

Thanks for your suggestions.

I will try them out.

macksv2

Posts : 2
Join date : 2015-01-27

View user profile

Back to top Go down

Re: How do you backfill and SCD type 2 column?

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