| Author |
Topic |
|
dowens
Yak Posting Veteran
82 Posts |
 Posted - 2005-11-09 : 15:33:10
|
| sql2000I have an existing data warehouse that is refreshed nightly from system1.We will be moving to a new system2 1/1/06.The old system1 will be phased out by the end of 2006.Several tables on system2 will have different keys.I have to move the data from both systems in the data warehouse.Here's the issue.I have to totaly refresh the data warehouse nightly.Once system2 is phased out how do I keep the data from system1and refresh only data from system2. |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-09 : 17:06:06
|
You shouldn't be doing a "total refresh" on a data warehouse anyway. Bill Inmon's definition:"A warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process". Key word for you, here, is "non-volatile". |
 |
|
|
dowens
Yak Posting Veteran
82 Posts |
Posted - 2005-11-10 : 07:35:22
|
| I do not disagree. In any event, I still have the issue. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-10 : 09:12:23
|
| [quote...ut how do I keep the data from system1and refresh only data from system2.[/quote]Uhmmm...a WHERE clause?More information please. How is data from the two systems differentiated in your data warehouse? |
|
|
|
dowens
Yak Posting Veteran
82 Posts |
Posted - 2005-11-10 : 10:21:51
|
| At this point I only have data from system1.System2 will be a duplicate of system1 just on another platform.The data from system1 will not be converted to system2.System2 will contain data beginning 2006.System1 will be phased out during 2006.Once System1 is phased out, I need to keep the date in the datawarehouse.Data from System2 will have to be refreshed nightly.How can I keep data from System1 and refresh only data from Sytem2. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-10 : 10:33:47
|
| You need a way to confidently different between the two datasets. Either a column on each table indicating the source of the data, or a reliable datetime stamp so that you can exclude old records from your nightly refresh.Again, this is really an appropriate time to go with a best-practices ETL model involving incremental inserts and updates. |
|
|
|
dowens
Yak Posting Veteran
82 Posts |
Posted - 2005-11-10 : 10:40:11
|
| I would love to do incremental updates, however that's not feasibleat this time.Here's my thoughts.Once Sytem1 is phased out. I store the data from system 1 in a seperate db.Nightly refresh the main db with System2's data.Then use DTS to load the data from system1, nightly, back into the main db.Is this good/bad idea or is there a better way? |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-10 : 11:01:21
|
| You already know my opinion. It is a crappy solution. But when the best solution is not feasable (never time to do it right...), then you have to implement a crappy solution. And among the crappy solutions yours is workable. Just budget for a large maintenance window and lots of administrative overtime. |
|
|
|
|
How to refresh