Performance : Custom BI Report Based on Cross Subject Area With Transactions and Payments (Doc ID 2006822.1)

Last updated on JULY 13, 2017

Applies to:

Oracle Fusion Incentive Compensation - Version 11.1.11.1.0 and later
Oracle Fusion Incentive Compensation Cloud Service - Version 11.1.11.1.0 and later
Information in this document applies to any platform.

Symptoms

Have performance problem with custom BI report based on cross subject areas Incentive Compensation - Transaction Real Time and Incentive Compensation - Payment Real Time.

The report is taking more than 10 minutes to return 31 records.

Steps:
1. Create a cross subject area Analysis
2. Click on Results

Sample Logical Query:
SET VARIABLE ENABLE_DIMENSIONALITY=1; SELECT
   0 s_0,
   "Incentive Compensation - Transactions Real Time"."Participant"."Participant Name" s_1,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CITY_" s_2,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CNSNPTN_STRT_T_" s_3,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CONFIRMATION_NUM_" s_4,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CONSUMPTION_END_DATE_" s_5,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_COUNTRY_" s_6,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CUST_FIRST_NAME_" s_7,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CUST_LAST_NAME_" s_8,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_HOTEL_ID_" s_9,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_HOTEL_NAME_" s_10,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_NET_REVENUE_AMT_" s_11,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_PROCESSING_FEE_AMT_"+"Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CREDIT_CARD_FEE_AMT_"+"Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_PARITY_FEE_AMT_"+"Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_ICP_FEE_AMT_" s_12,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_PRODUCT_CODE_" s_13,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_PRODUCT_TYPE_CODE_" s_14,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_REFERRAL_CLICK_ID_" s_15,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_REFERRAL_ID_" s_16,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_STATE_" s_17,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TOTAL_CUST_CHG_AMT_" s_18,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRAVEL_END_CITY_" s_19,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRAVEL_END_STATE_CODE_" s_20,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRFL_NT_LCTN_CT_" s_21,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRFL_NT_TNTRY_CT_" s_22,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRFL_STRT_LCTN_CT_" s_23,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRIP_ID_" s_24,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Booked Date" s_25,
   "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Source Transaction Number" s_26,
   "Incentive Compensation - Transactions Real Time"."Type"."Transaction Type" s_27,
   "Incentive Compensation - Payments Real Time"."Payment Transaction"."Commission Amount Calculated Currency" s_28,
   "Incentive Compensation - Transactions Real Time"."Transaction"."Transaction Quantity" s_29,
   REPORT_SUM("Incentive Compensation - Payments Real Time"."Payment Transaction"."Commission Amount Calculated Currency" BY "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Booked Date","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_REFERRAL_ID_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_REFERRAL_CLICK_ID_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_PRODUCT_CODE_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CNSNPTN_STRT_T_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CONSUMPTION_END_DATE_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CONFIRMATION_NUM_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_PRODUCT_TYPE_CODE_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRFL_STRT_LCTN_CT_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CITY_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_STATE_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_COUNTRY_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRFL_NT_LCTN_CT_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRAVEL_END_CITY_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRAVEL_END_STATE_CODE_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRFL_NT_TNTRY_CT_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_HOTEL_ID_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_HOTEL_NAME_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_NET_REVENUE_AMT_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_PROCESSING_FEE_AMT_"+"Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CREDIT_CARD_FEE_AMT_"+"Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_PARITY_FEE_AMT_"+"Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_ICP_FEE_AMT_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TOTAL_CUST_CHG_AMT_","Incentive Compensation - Transactions Real Time"."Participant"."Participant Name","Incentive Compensation - Transactions Real Time"."Type"."Transaction Type","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CUST_FIRST_NAME_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRIP_ID_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CUST_LAST_NAME_") s_30,
   REPORT_SUM("Incentive Compensation - Transactions Real Time"."Transaction"."Transaction Quantity" BY "Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Booked Date","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_REFERRAL_ID_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_REFERRAL_CLICK_ID_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_PRODUCT_CODE_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CNSNPTN_STRT_T_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CONSUMPTION_END_DATE_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CONFIRMATION_NUM_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_PRODUCT_TYPE_CODE_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRFL_STRT_LCTN_CT_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CITY_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_STATE_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_COUNTRY_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRFL_NT_LCTN_CT_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRAVEL_END_CITY_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRAVEL_END_STATE_CODE_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRFL_NT_TNTRY_CT_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_HOTEL_ID_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_HOTEL_NAME_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_NET_REVENUE_AMT_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_PROCESSING_FEE_AMT_"+"Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CREDIT_CARD_FEE_AMT_"+"Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_PARITY_FEE_AMT_"+"Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_ICP_FEE_AMT_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TOTAL_CUST_CHG_AMT_","Incentive Compensation - Transactions Real Time"."Participant"."Participant Name","Incentive Compensation - Transactions Real Time"."Type"."Transaction Type","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CUST_FIRST_NAME_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_TRIP_ID_","Incentive Compensation - Transactions Real Time"."Source Transaction Details"."Base Transaction_CUST_LAST_NAME_") s_31
FROM "Incentive Compensation - Transactions Real Time"
WHERE
(("Participant"."Participant Name" = 'Test REP') AND ("Incentive Compensation - Payments Real Time"."Payment Transaction Details"."Pay Date" IN (timestamp '2015-03-31 00:00:00', timestamp '2015-03-31 00:00:00')))
ORDER BY 1, 26 ASC NULLS LAST, 2 ASC NULLS LAST, 17 ASC NULLS LAST, 16 ASC NULLS LAST, 14 ASC NULLS LAST, 8 ASC NULLS LAST, 5 ASC NULLS LAST, 28 ASC NULLS LAST, 15 ASC NULLS LAST, 4 ASC NULLS LAST, 6 ASC NULLS LAST, 24 ASC NULLS LAST, 3 ASC NULLS LAST, 18 ASC NULLS LAST, 7 ASC NULLS LAST, 22 ASC NULLS LAST, 20 ASC NULLS LAST, 21 ASC NULLS LAST, 23 ASC NULLS LAST, 10 ASC NULLS LAST, 11 ASC NULLS LAST, 12 ASC NULLS LAST, 13 ASC NULLS LAST, 19 ASC NULLS LAST, 25 ASC NULLS LAST, 9 ASC NULLS LAST
FETCH FIRST 75001 ROWS ONLY


Changes

Just upgrade to Release 9.  Same report was returning results fine in Release 8.

Cause

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