We are on Oracle Business Intelligence Applications (OBIA) 7.9.6.3 and had been experiencing performance issues with the SIL_APTransactionFact_DiffManLoad workflow/mapping. We tried a number of things but only had minimal improvements. Eventually, I found a solution for the poor performance on Oracle Support. This change resulted in a drastic improvement of this workflow.
The solution can be found on Oracle Support (http://support.oracle.com – Oracle Doc ID: 1446397.1), but for your convenience I have included the content below. There are other mappings that have a similar problem.
————————————————————————
OBIA 7963: SIL_APTransactionFact_Diffmanload Mapping And Performance Issue (Doc ID 1446397.1)
In this Document
Symptoms
Cause
Solution
Applies to:
Informatica OEM PowerCenter ETL Server – Version 7.9.6.3 [AN 1900] and later Information in this document applies to any platform.
Symptoms
The OBIEE application (7.9.6.3) ETL task “SIL_APTransactionFact_DiffManLoad” has run over 68 hours during full load execution.
Cause
- The size of these columns (DOC_HEADER_TEXT and LINE_ITEM_TEXT ) in DAC is 255 (except AP where its 1020 in DAC and Infa). But in Informatica the size for these two columns is 1020. Ideally it should be 255. This is a known performance issue.
- The cause of the problem has been identified in unpublished Bug 12412793- PSR: B16 INCREMENTAL: SIL_GLREVENUEFACT,
Solution
Below are the steps you will follow to modify the size of the fields in the lookup.
- Take a backup of existing Lookups ( LKP_W_AP_XACT_F and LKP_W_AR_XACT_F ).
- Login to Informatica Designer >Transformations
- Open the lookup and modify the size of the fields. The port lengths for the DOC_HEADER_TEXT and LINE_ITEM_TEXT were changed to 255 .
- Save the changes
- Rerun the test and confirm the performance issue is resolved and migrate the changes in PROD.
————————————————————————
The Data Community 