My Oracle Support Banner

Oracle Incentive Compensation Analytics - Partition Earning Fact (Doc ID 2146734.1)

Last updated on DECEMBER 10, 2016

Applies to:

Oracle Incentive Compensation Analytics for Oracle Data Integrator - Version 12.1.3 to 12.2.4 [Release 12.1 to 12.2]
Information in this document applies to any platform.

Goal

Problem: ETL performance with the Earning Transactions

 

The out of the box OICA provides incremental ETL for the open-periods. The current structure with earning fact table(cn_f_earning_txns), before loading the new transactions and update the surrogate keys of all the dimensions, it deletes all the transactions which were updated/deleted in the source EBS-OIC instance. This process creates fragmentation with volume data, and degrades the performance of sql's related to the fact table (cn_f_earning_txns). The fragmentation can be eliminated by reorganizing the database objects. But all Customers cannot allocate the time and resource for the re-organization of the fact table cn_f_earning_txns, if it is referencing 24*7.

 

 

Solution

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
 Problem: ETL performance with the Earning Transactions
Solution
 Partitioning Earning Transaction Fact Table
 Benefits of Partitioning
 Partitioning Key
 Partitioning Flow of the Earning Transactions Fact Tab
 Test Care Scenario
 OIC Calculation and ETL execution code
 OICA execution code
 Data Distribution of Earning Transaction Fact Table (CN_F_EARNING_TXNS)
 Cost Distribution with trace results
 Partitioning Code :
 Tips for Non-Partitioning
 DISCLAIMER:

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.