Re: DWH concept, what is better ?

IMO, your designs have to be evaluated based on their performance, eaze of administration and data storage consumption - in that order of importance. If you break down your designs and evaluate them step by step using the above mentioned guidelines, the answer will become clear.

You did not provide the details of your environment, so not sure if you already know and use some of the ORACLE features that are useful in a DW environment. Look into materialized views, data replication and CDC (Change Data Capture with 9i). One of the primary concerns in running a data warehouse is how to minimize the performance impact on the production system during the ETL process where data is pulled (or pushed) from the production db into the DW. You want to minimize the amount of data processed during the ETL. So inevitably you want to check if "delta" processing is possible. I would suggest that you consider all these before going any further on your designs.

As to the 2 ETL designs you specifically asked, obviously with choice 2, prodcution database performance will not suffer when the ETL is run against the temporary tables. The price you pay is of course the additional database, maybe the additional server as well, you have to set up and manage. Assuming the production server is much more powerful with a lot of capacity to spare, and minimizing the time on refreshing the DW is important to you, choice 1 may be preferrable. So, hardware is also a factor you need to consider.

But, in stead of choosing between the two, you might want to read up on the ORACLE Data Warehousing Guide first. You might come up with better solutions.

• Jusung Yang

ora_kimo_at_yahoo.de (Kimo) wrote in message news:<6173a20e.0301210756.52fbc275_at_posting.google.com>...
> Hi....
>
> In order to extract, transfer and load to our Data Warehouse we have
> created 2 concepts,
> 1- export the needed data to temporary tables on the live database and
> then transfer and load the data into Data Warehouse.
> 2- export the data to temporary tables on a separated database and
> then transfer/load the data into DWH.
>
> we concept would you recommend me and can anyone tell me the
> advantages and
> disadvantages of each concept ?
>
> You can also tell me if you have another option !
>
> thanx
> kimo
Received on Tue Jan 21 2003 - 13:52:51 CST