T2T Extract Batch Failed if Processing More Than 400K Rows In OFSAA (Doc ID 1293496.1)

Last updated on JANUARY 22, 2015

Applies to:

Oracle Financial Services Enterprise Financial Performance Analytics - Version 5.1 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA) - Version 5.2
PFT

Symptoms

In Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) - Version 7.2.6, when attempting to run the T2T_FCT_ACCOUNT_SUMMARY_LOANS using Profitability Analytics (PFTBI) 5.1 with Oracle Financial Services Profitability Management (OFSAPM) 5.2,  if there are more than 400,000 rows in one table, the T2T process hangs and does not complete. Running with 350,000 rows in the source table does complete successfully

ERROR

From the $FIC_APP_HOME/common/FICServer/logs/RevAppserver.log:

[REVELEUSLOG] Feb 7,11 15:57:15 : [ICCEVENTHANDLER]Handling ICC Event :BatchrunID [OFSAAINFO_B0010_20101031_15] infoDate [B0010] taskID [BATCH] ComponentID [BATCH] taskType [2] status [4]
[REVELEUSLOG] Feb 7,11 15:57:15 : [NONE][NSockConn] Error while reading
java.io.IOException: Connection reset by peer
at sun.nio.ch.FileDispatcher.read0(Native Method)
at sun.nio.ch.SocketDispatcher.read(SocketDispatcher.java:33)
at sun.nio.ch.IOUtil.readIntoNativeBuffer(IOUtil.java:234)
at sun.nio.ch.IOUtil.read(IOUtil.java:207)


The issue can be reproduced at will with the following steps:

1. Load data to FSI_D_LOAN_CONTRACTS
 select count(*) from fsi_d_loan_contracts--409914
2. All data can be transferred to FCT_COMMON_ACCOUNT_SUMMARY

Checked with SQL (T2T used it) - it returns 110820 rows to load into FCT_COMMON_ACCOUNT_SUMMARY:

SELECT CASE
WHEN COUNT (1) = 1 THEN SUM (inline_view.rec_count)
ELSE COUNT (1)
END
FROM (SELECT COUNT (1) rec_count
FROM FSI_D_LOAN_CONTRACTS
INNER JOIN DIM_COMMON_COA
ON DIM_COMMON_COA.N_COMMON_COA_ID =
FSI_D_LOAN_CONTRACTS.COMMON_COA_ID
INNER JOIN DIM_CURRENCY
ON DIM_CURRENCY.V_ISO_CURRENCY_CD =
FSI_D_LOAN_CONTRACTS.ISO_CURRENCY_CD
INNER JOIN DIM_GL_ACCOUNT
ON DIM_GL_ACCOUNT.N_GL_ACCOUNT_ID =
FSI_D_LOAN_CONTRACTS.GL_ACCOUNT_ID
INNER JOIN DIM_ORG_UNIT
ON DIM_ORG_UNIT.N_ORG_UNIT_ID =
FSI_D_LOAN_CONTRACTS.ORG_UNIT_ID
INNER JOIN DIM_PRODUCT
ON DIM_PRODUCT.N_PRODUCT_ID =
FSI_D_LOAN_CONTRACTS.PRODUCT_ID
LEFT OUTER JOIN DIM_CUSTOMER
ON DIM_CUSTOMER.N_CUSTOMER_ID =
FSI_D_LOAN_CONTRACTS.CUSTOMER_ID
LEFT OUTER JOIN DIM_GEOGRAPHY
ON DIM_GEOGRAPHY.v_acct_branch_code =
FSI_D_LOAN_CONTRACTS.BRANCH_CD
LEFT OUTER JOIN FSI_EXCHANGE_RATE_HIST
ON FSI_EXCHANGE_RATE_HIST.FROM_CURRENCY_CD =
FSI_D_LOAN_CONTRACTS.ISO_CURRENCY_CD
AND FSI_EXCHANGE_RATE_HIST.EFFECTIVE_DATE =
FSI_D_LOAN_CONTRACTS.AS_OF_DATE
AND FSI_EXCHANGE_RATE_HIST.TO_CURRENCY_CD = 'HRK'
WHERE 1 = 1
AND FSI_D_LOAN_CONTRACTS.AS_OF_DATE =
TO_DATE ('20101031', 'YYYYMMDD')
AND DIM_COMMON_COA.F_LATEST_RECORD_INDICATOR = 'Y'
AND DIM_GL_ACCOUNT.F_LATEST_RECORD_INDICATOR = 'Y'
AND DIM_ORG_UNIT.F_LATEST_RECORD_INDICATOR = 'Y'
AND DIM_PRODUCT.F_LATEST_RECORD_INDICATOR = 'Y') inline_view

3. After starting T2T_FCT_ACCOUNT_SUMMARY_LOANS the log is stopped on the insert statement and no data has been loaded.

4. If we reduce rows in the FSI_D_LOAN_CONTRACTS to 350 000 T2T is working fine and we have result of 93898 rows loaded into FCT_COMMON....:

SELECT ROUND((a.d_end_time-a.d_start_time)*24*60) duration, a.* from ETLLoadHistory a where v_tbl_name='FCT_COMMON_ACCOUNT_SUMMARY' order by d_start_time DESC
0 OFSAAINFO_B0010_20101031_13 Task1 OFSAAINFO PROCESSING_AREA 1 FCT_COMMON_ACCOUNT_SUMMARY T2T_FACT_ACCOUNT_SUMMARY_LOANS 31.10.2010. 07.02.2011. 12:02:00 07.02.2011. 12:02:00 93898 0

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