Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: refreshing DW environment from APPS (9i)

RE: refreshing DW environment from APPS (9i)

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 18 Jan 2007 12:02:56 -0500
Message-ID: <FBEIIHEAOIFCBBNIIFOGOEMOCKAA.mwf@rsiz.com>


why join at all?

You probably want all the changed oe_order_headers_all rows and all the oe_order_lines rows.

Then you likely want to make aggregates on the changed lines and apply net deltas to the aggregates you keep in your datawarehouse.

Maybe I’m not groking something.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of cosmin ioan
Sent: Thursday, January 18, 2007 9:51 AM To: oracle-l_at_freelists.org
Subject: re: refreshing DW environment from APPS (9i)

hello all,
we have a quandary, on the topic above, on a Oracle Applications environment
(normalized) that we're trying to refresh to a DW (big/fact tables),
basically looking for a way to efficienty capture changes.

Here's a scenario of the oe_order_lines_all and oe_order_headers_all, some basic tables in the apps:

select count(1)
from oe_order_headers_all ooha,
oe_order_lines_all oola
where ooha.header_id = oola.header_id
and ((ooha.last_update_date > '1-jan-2005' and ooha.last_update_date <= '31-dec-2005')

   or (oola.last_update_date > '1-jan-2005' and oola.last_update_date <= '31-dec-2005'))

so, basically, what we're tying to do is capture any changes for the year
(month/day/whatever), regardless of whether the orders or lines have been
placed in 2005. The problem with this approach, because of the "OR" clause, you're forced into full table scans. (and here, I'm only giving as an example a 2 table join however there are FACTS, etc, that require many more joins so the "full table scan" stuff gets really ugly).

One can probably circumvent this by using materialized views to determine the delta, however, for speed purposes, we really like partitioned tables and I'm not sure yet there are such things as "partitioned materialized views", so probably we'll need to go the MV route and then further, populate an identical schema partitioned table.... don't really like this due to the doubly wasted space.

Any thoughts on how other enterprises do this, for large APPS deployments
(billions of rows) converting the data to DW style tables, in an efficient
way?

thanks much,
Cos

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 18 2007 - 11:02:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US