Re: datawarehouse extraction, snapshots?

From: sg <s4v4g3_at_europe.com>
Date: Thu, 15 Aug 2002 13:21:41 +0200
Message-ID: <3D5B8EC5.5030509@europe.com>

our problem is that extraction processes is exchausting our billing system resources. This is a mobile company so

imagine we have a table for customer contracts which is around 15000000 rows, for extraction a the process probably do

select column1, column3, column 5
from CONTRACTS;

then next extraction process comes and require part of data from that table and runs

select column2, column4, column 6
from CONTRACTS;

then tehre comes another process which does probably similar thing but for different column combinations, there are around 20 of these extraction processes going in our billing system everynight!

So what I am trying to do is relieve this load from billing system and move the load to our datawarehouse server is more suited for these kind of heavy queries. I want to move the entire table contracts everyday to datawarehouse. And I considered three options. Snapshot in datawarehouse would use less resources as far as I am concerned but will the snapshot log in our billing system affect billing system performance? Because it is a very busy system with around 1500 online 10 hours / day.

Transportable tablespace cant go because we have different data block sizes (8k vs 32k)

Richard Foote wrote:

> 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 - 06:21:41 CDT