Adding new fields to a live system

So, the DW DB is loaded with data of various SCD types.
The data is in live use.
New requirements arise that require new SCD 2 fields to be added.
Backups exist of the source systems.

Are there any design tips, experiences that anyone can share on how to populate the new SCD 2 fields.

Is it just a question of rebuilding the whole DW DB using those backups until you've caught up with 'Now'?

Thanks

Ponzie

I'm first going to make the assumption that the new SCD2 field(s) is going to be added to a dimension table that already contains at least one SCD2 field.

By default, an SCD2 field does not start recording historical changes until the day you implement it. When you initially add the column, all instances (current and historical rows) of the particular row would be assigned the current column value.

That behavior may or may not meet your business requirements because the column values for historic rows are likely to be historically inaccurate. If you need to recreate the past it can be an immense undertaking because you have to consider the impact from the existing SCD2 columns. The historic rows that exist were created based on the change activity dates relative to the existing SCD2 columns and are likely not at all related to changes that may have occurred with your new SCD2 columns. If you start inserting new historic rows you run the strong likelihood of creating time overlapping dimension rows, or worse, corrupting the foreign keys in your fact tables if you aren't meticulously careful. This approach is NOT for the faint of heart or someone only casually familiar with how everything works in the DW.

Rebuilding from scratch from historic backups might work if you thoroughly think it through. Keep in mind that standard SCD2 change detection methods create new dimension rows and assign effectivity dates when the ETL "detects" the change. Simply running repeated loads from backup using your standard ETL would NOT work. Lets say for example you are somehow able to reload all of your data from multiple backups in one day...Today. The initial load would set the Effective_From date to some default way in the past and the Effective_To date to some default way in the future. The next sequential load would recognize the changes in your SCD2 columns, create new rows and adjust effectivity dates. The problem is that the change was detected "Today" and by default your effectivity dates would reflect "Today" rather than when the change really occurred sometime in the past. When you adjust and assign the effectivity dates, you need to set them relative to the date of the backup...not Today. If the business requires you to recreate dimensional history, this is probably the safest approach but as I said, you need to think it through. I suspect for most people this is not an option since totally rebuilding from scratch may takes months to process.

Hope that helps!

Thanks for that.
Pretty much as I feared!

Our ETL is designed to distinguish between the date the data is loaded in to the Data Warehouse Database and the date that the source data is representing - i.e. the date of the backup.
This was so that we could load at least some historic type 2 data.

I think the business case is going to have to be really strong to keep re-loading historic data - as you say, it takes a long time!

thanks

If the new column only applies to data loaded into the DW after it is added to the dimension table, then it's a simple process. Add the column, and treat the old rows as if the data came in with a null in the new column.

If you need the new column to be polulated for history, then you have no choice but to reload all of the historical data.

Did this get resolved?

let me try that myself....

May be you will try.

Hi

You can find this info by using search box in the top of website with some keywords related before posting questions.

I agree with you man.

hamburg113 wrote:Hi

You can find this info by using search box in the top of website with some keywords related before posting questions.

Thank you. I was trying to find that.

thank you to share such a interesting and help full post with us, i like it very much and i hope that you will keep it up
BISE peshawaar

sabila1230 wrote:thank you to share such a interesting and help full post with us, i like it very much and i hope that you will keep it up

i agree with you sabila...


________________________
http://termlifeinsurancemales.com/life-insurance-with-health-risks/life-insurance-for-people-with-diabetes
http://termlifeinsurancemales.com/life-insurance-by-state/california-life-insurance

Has it been resolved then guys?


__________________
http://www.fitnesssupplementreviews.com/decatrim-reviews/ and http://www.fitnesssupplementreviews.com/hydroxycut-review/