BiApps Data load performance tuning variable hints

Posted by : Arjun Lagisetty Thursday, July 17, 2014

BI Apps out of the box code takes bulk insert only for full loads to use bulk insert mode for incremental loads we need to change the following variables. Following list of variables is not exhaustive but the variables are per fact. This list mostly contains facts for Financial analytic.

Please note that parallelism has to be enabled at table level as well.

1. SDE_ORAR1213_ADAPTOR_SDE_ORA_GLJOURNALSFACT
a. BIAPPS_ETL_BULK_MODE
Default value : F

Change to: Y

b. ETL_HINT_INSERT_VALUES
Default value : ‘No value’
Changed to: /*+ APPEND */

TARGET: W_GL_OTHER_FS
SOURCE: GL_JE_LINES

2. SDE_ORA_PersistedStage_GLLinkageInformation_GLExtract
a. BIAPPS_ETL_BULK_MODE
Default value : F
Change to: Y

b. ETL_HINT_INSERT_VALUES
Default value : ‘No value’
Changed to: /*+ APPEND */
TARGET: W_ORA_GL_LINKAGE_GL_JRNL_PS

SOURCE: GL_JE_BATCHES, GL_JE_HEADERS, GL_IMPORT_REFERENCES,
GL_LEDGERS, GL_PERIODS

3. SDE_ORA_GLBalanceFact

a. BIAPPS_ETL_BULK_MODE
Default value : F
Change to: Y

b. ETL_HINT_INSERT_VALUES
Default value : ‘No value’
Changed to: /*+ APPEND */
TARGET: W_GL_BALANCE_FS

SOURCE: GL_LEDGERS, GL_BALANCES, GL_PERIODS, GL_CODE_COMBINATIONS, GL_ENCUMBRANCE_TYPES,FND_DUAL

4. SILOS_SIL_GLOTHERFACT
a. BIAPPS_ETL_BULK_MODE
Default value: F
Change to: Y
b. ETL_HINT_UPDATE
Default value: ‘No value’
Changed to: /*+ APPEND */
TARGET: W_GL_OTHER_F
SOURCES: LKP+FS

5. SIL_GLBalanceFact:
a. BIAPPS_ETL_BULK_MODE
Default value: F
Change to: Y
b. ETL_HINT_UPDATE
Default value: ‘No value’
Changed to: /*+ APPEND */
TARGET: W_GL_OTHER_F, W_GL_BALANCE_F

6. SIL_GLLinkageInformationGeneral
a. BIAPPS_ETL_BULK_MODE
Default value: F
Change to: Y
b. ETL_HINT_UPDATE
Default value: ‘No value’
Changed to: /*+ APPEND */

7. PLP_GLOtherGroupAccount_A_Stage

a. BIAPPS_ETL_BULK_MODE
Default value : F
Change to: Y