SSIS Frameworks. Automatic Rollbacks, and Poorly Designed OLTP Sources
2 posters
Page 1 of 1
SSIS Frameworks. Automatic Rollbacks, and Poorly Designed OLTP Sources
I've purchased the excellent book Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution and read it cover to cover a few times.
http://www.wrox.com/WileyCDA/WroxTitle/Microsoft-SQL-Server-2008-Integration-Services-Problem-Design-Solution.productCd-0470525762,descCd-DOWNLOAD.html
I'm trying to implement the updated framework by Rushabh Mehta.
http://sqlpass.eventpoint.com/Speaker/Details/Rushabh_Mehta
His framework is amazing in that it allows automated rollbacks of dimension and fact table loads. However, my database tables do not have datetime columns indicating when they were inserted/updated. It updates in place, hard deletes, etc. Most of it is non-transactional in nature.
Therefore, I don't see any way that this could be accomplished in such a situation. As I see it, if someone realized that the DW received bad data 3 days ago, I would have to do the following:
1. Restore both the OLTP source and the data warehouse database to the exact same time.
2. Restore the OLTP to the next day and run the extraction.
3. Repeat step 2 for each day until current.
Am I missing something here? I don't think there's any other option because my source database is so poorly designed.
http://www.wrox.com/WileyCDA/WroxTitle/Microsoft-SQL-Server-2008-Integration-Services-Problem-Design-Solution.productCd-0470525762,descCd-DOWNLOAD.html
I'm trying to implement the updated framework by Rushabh Mehta.
http://sqlpass.eventpoint.com/Speaker/Details/Rushabh_Mehta
His framework is amazing in that it allows automated rollbacks of dimension and fact table loads. However, my database tables do not have datetime columns indicating when they were inserted/updated. It updates in place, hard deletes, etc. Most of it is non-transactional in nature.
Therefore, I don't see any way that this could be accomplished in such a situation. As I see it, if someone realized that the DW received bad data 3 days ago, I would have to do the following:
1. Restore both the OLTP source and the data warehouse database to the exact same time.
2. Restore the OLTP to the next day and run the extraction.
3. Repeat step 2 for each day until current.
Am I missing something here? I don't think there's any other option because my source database is so poorly designed.
DavidStein- Posts : 24
Join date : 2010-04-01
Re: SSIS Frameworks. Automatic Rollbacks, and Poorly Designed OLTP Sources
You may be trying to overenginneer a problem that won't happen. What do you mean by 'bad data' and why would it take 3 days to know about it?
Similar topics
» ATM (Automatic Teller Machine) dimensional model
» Poorly sturctured data at source system
» Many to many relationship question
» Cubes on OLTP
» Can Snowflake schema be used in OLTP?
» Poorly sturctured data at source system
» Many to many relationship question
» Cubes on OLTP
» Can Snowflake schema be used in OLTP?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|