datawarehouse extraction, snapshots?

Hi

I am currently supporting a datawarehouse, recently there were many problems with data extraction from data sources, basically the problem was that the same tables in data source were extracted repeadtely by several processes (not reusing same data) so I am in process of optimizing the extraction process.

I have consider several possibilities

1. select * from source tables and spool to flat file, ftp to destination server and sql load it to datawarehouse
2. export tables with direct path and import to datawarehouse
3. create snapshots for data source tables in datawarehouse with incremental refresh every night

I have been considering seriously option 3 since it would use less resource than other two. Has anyone used option 3 for ETL process in a datawarehouse? How the performance affecting the source database? Our source database is company's billing system with over 2000 users (normally 1500 online with MTS), a quite loaded system

I would be replicating child tables not parent tables so my historical data would be intact

Any suggestiong will be welcome!

Thank you Received on Thu Aug 15 2002 - 04:39:45 CDT