Re: datawarehouse extraction, snapshots?

Hi SG,

I'm not entirely sure what problems you're detailing, but something that might prove a useful option is a modification of option 2 with the use of transportable tablespaces. That way you can relatively efficiently "plug in" datafiles from the source database into the data warehouse where data can be massaged as necessary.

Check out the utilities manual on this export/import option, check out the various restrictions and determine if it might be an option worthy of consideration.

Cheers

Richard

sg wrote:
>
> 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:55:55 CDT