Repopulating a dw for specific customers

View previous topic View next topic Go down

Repopulating a dw for specific customers

Post  jtigger on Fri Apr 27, 2012 3:46 am

Hi,
I am new to datawarehousing and have created a small datawarehouse in sql2008r2.It has been running smoothly until the other day.
WThe have lost 6 months worth of a customers data due to there database becoming corrupt.The data is now back in the live system.
However i now have the task of trying to get this data into my dw.I have no idea what is the best way or how to deal with this situation should it have in the future.
i am thinking of the following,
1.Extract the customer specific details to a seperate test database,maniulate the data there and then insert it into the datawarehouse.

2.Repopulate the whole of the dw.

Option 1 could get messy as i could end up missing data.
Option 2 i think would take time,but at the same time it would mean the data is correct but would take the dw out for sometime.

Please can anyone suggest what is the best option?.I have never come across this and am really unsure what to do in this scenario.
Any advise would be very much appreciated.
Thank you
JTigger

jtigger

Posts : 2
Join date : 2012-04-27

View user profile

Back to top Go down

Re: Repopulating a dw for specific customers

Post  BoxesAndLines on Fri Apr 27, 2012 9:52 am

Database backups and restores.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Repopulating a dw for specific customers

Post  ngalemmo on Fri Apr 27, 2012 11:10 am

Reload and do regular backups.

My worst DW experience was similar. It was in the mid-1990's at an HMO. It was a large DW for the time and operations dutifully did full and incremental backups on schedule. There was a power issue that destroyed a number of disk drives. So they went about restoring the hardware and recovering the database. They had an automated tape library for backups. It was a large machine that stored tape cartridges and mounted them into a dozen different drives. A full backup was 80-90 cartridges. As they started the restore they discovered bad tapes. It turns out that one of the tape drives was faulty and would occasionally crimp and damage a tape. They tried successive backups with the same result. After a few days they managed to restore from a good set that was around 10 weeks old. We then had to reload daily batches until we caught up. It took a few weeks.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Repopulating a dw for specific customers

Post  Mike Honey on Sun Apr 29, 2012 9:50 pm

I've found these scenarios quite common - most solutions I've been involved with strike something similar every year or two. Overall I think there has been a steady decline in the technical quality of source systems over the years. If it's not outright data corruption, its the discovery that source system timestamps that you were relying on for your incremental extract are actually not reliable...

My strategy to defend against this is to fully refresh fact tables as far as I possibly can. It can be surprising how much data you can thump in overnight on modern hardware, especially with a "massively parallel" data load design.

My first fall back position is to load incrementally on weeknights, then fully refresh each weekend. The typical incremental extract is actually my last resort.

Obviously there are wasted resources as redundant data is flowing around each night. But the result is a simpler, more robust design - which implies less effort and lower risk for development, testing, support and maintenance. You also gain a lot of design flexibility as it is far quicker and easier to add or modify fact columns.

With a full refresh design, the solution to your problem would be ... simply to wait until the next full refresh.

Good luck!
Mike
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Repopulating a dw for specific customers

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