How to use Materialized View For W_GL_BALANCE_A for better performance

(Doc ID 1491054.1)

Last updated on SEPTEMBER 27, 2016

Applies to:

Business Intelligence Applications Consumer - Version 7.9.6.3 [AN 1900] and later
Information in this document applies to any platform.

Goal

The tech note for perf tuning OBI apps that was release in May 2012 (BI Apps706_Perf_Tech_Note_v8.pdf, page 55) provides guidance on improving data load performance into W_GL_BALANCE_A (financial analytics 7.9.6.3) using materialized View (MV) technique. There are issues with this tech note and we are looking for help to make this technique work since the W_GL_BALANCE_A table gets rebuilt as is and it takes 2.5 hours on our nightly refresh (W_GL_BALANCE_F has 160+M rows in it).

Some details on our experiences on the MV technique:
The MV is built upon 3 base tables W_GL_BALANCE_F, W_GL_ACCOUNT_D, W_GLACCT_GRPACCT_TMP and subsequently the MV log's have been built for the 3 base tables to accommodate fast refresh during the nightly incremental data load.

The problem we are encountering now is that one of the base table which is W_GLACCT_GRPACCT_TMP is being truncated and re-loaded by the task before (PLP_GLGrpAcct_Derive) which is resulting in the MV(W_GL_BALANCE_A) becoming invalid for fast-refresh.

Do you have updates notes here to help make this work? Or perhaps an alternative technique?
 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms