Performance Issue With PLP APBalanceFactLoad Task

Solution: We need to make sure that variable “ BIAPPS.ETL_HINT_NESTED” has value overwritten in Load plan

component of relevant adaptor (example – LP component “ 3 SDE Fact ARTRANS_FG EBS_12_1_3” needs to have value overwritten for above v ariable “BIAPPS.ETL_HINT_NESTED” in R12 adaptor mentioned maps. Example is explained for R1213 adaptor, but it’s applicable for R12, R1211, and R1212 adaptor also. Difference would be in name of all components (ex: Load plan name would change to “3 SDE Fact

ARTRANS_FG EBS_12_0” or “3 SDE Fact ARTRANS_FG EBS_12_1_3” and so on) Steps: ODI Changes  – 1)

Login to ODI rep ository from studio and go to “ BI Apps Project ”

2)

Expand “BI Apps Project ” first and then ex pand “Mappings ” and find adaptor “SDE_ORAR1213_Adaptor” after successful connection to ODI repository.

3)

Expand this folder “ SDE_ORAR1213_Adaptor” and find  below map

SDE_ORA_ARTransactionFact_ReceivableApplication_InitialBalance Expand this map and fin d “Interfaces” inside this folder and expand “ Interfaces” to find four entries as below.

4)

Double clicking a particular interface (highlighted in above diagram) opens ‘Overview’ Page  on right hand side. N ow click on ‘Flow’ tab available in bottom section  of this page

5)

On opening ‘Flow’ tab, select “ Staging Area Properties ” which will list “IKM Oracle Control Append ” and its option listed as below. Point out option with name OBI_HINT_EXTRACT, it would have value as shown i.e. #ETL_HINT_EXTRACT2. Ignore this step if value is any different from #ETL_HINT_EXTRACT2.

6) We need to remove existing value of “OBI_HINT_EXTRACT” option, which is #ETL_HINT_EXTRACT2 at present. On “Value” side, click on “#ETL_HINT_EXTRACT2”. This will open drop down list to choose values. We need to select value “:#ETL_HINT_EXTRACT” from drop down list.

After replacing value, it should appear as:

Ignore Step 6 if value of OBI_HINT_EXTRACT option is already “ :#ETL_HINT_EXTRACT”

7)

Now locate option with name “OBI_HINT_NESTED” just above OBI_HINT_EXTACT Value of this option should be changed to “#ETL_HINT_NESTED2”. To do this, click on value of this option OBI_HINT_NESTED, and from drop down list, choose “ ”.

This will open “ Expression Editor”, which will be blank. Please copy below expression and paste it

inside expression editor #ETL_HINT_NESTED2

8)

Now options should look like this:

9) Click Apply and OK. Save changes by File->Save. 10) Close below interface using highlighted section SDE_ORA_ARTransactionFact_ReceivableApplication_InitialBalance.W_AR_XACT_FS_RECPT  _REV

11) Now open another interface with name as below, inside same folder in navigator SDE_ORA_ARTransactionFact_ReceivableApplication_InitialBalance.W_AR_XACT_FS_UPG  _RECEIPTS

And do the same step from 4 to 10 as explained above. Make sure for this interface, option OBI_HINT_NESTED has value equal to #ETL_HINT_NESTED3

12) Open another interface with name as below SDE_ORA_ARTransactionFact_ReceivableApplication_InitialBalance.W_AR_XACT_FS_SQ_ AR_XACTS_APPREC_HIST

And do same step from 4 to 10 as explained above. Make sure for this interface, option OBI_HINT_NESTED has value equal to #ETL_HINT_NESTED1

13) Once all these changes are done. Find “ Packages” in navigator window with same folder “SDE_ORA_ARTransactionFact_ReceivableApplication_InitialBalance” and expand Packages to find “Scenarios”. Expanding Scenario will s how a scenario with name as:

SDE_ORAR1213_ADAPTOR_SDE_ORA_ARTRANSACTIONFACT_RECEIVABLEAPPLICATION_I NITIALBALANCE

14) Right click on this scenario and choose option “ Regenerate..” . Make sure name of regenerated scenario is same as earlier. And name of new scenario is SDE_ORAR1213_ADAPTOR_SDE_ORA_ARTRANSACTIONFACT_RECEIVABLEAPPLICATION_I NITIALBALANCE

15) LP changes: Now expand “Load Plans and Scenarios” in Navigator window. Expand “BIAPPS Load Plan” And then expand “ Load Plan Dev Components ” to find “ SDE” folder. Expand “SDE” to find folder “ EBS_12_1_3 ”.

16) Expand “EBS_12_1_3” to find Load plan with name “3 SDE Fact ARTRANS_FG EBS_12_1_3 “. Double click on this Load plan opens up “Overview” page. Click on “Steps”

17) Click on “Steps” and then expand first “ Parallel ” to find entries like these

18) Find “Scenario” with name SDE_ORA_ARTRANSACTIONFACT_RECEIVABLEAPPLICATION_INITIALBALANCE Right click in Load Plan section and choose “Remove Step”.

19) Now drag and drop this scenario from Mapping folder to LP component opened in workspace. Drag it under same “Parallel” section. Tip – Drag scenario and drop it at keywords “Parallel”.

After dragging scenario into LP, c hange these properties of this newly added scenario Name – Remove “SDE_ORAR1213_ADAPTOR_” Restart – change this option to “Restart from Failed Step” from drop down option. Version – change it to -1

Click on right end of “Scenario” in properties window to change “Version”. Click “Finish”.

20) Now go to “Scenario Variables” in properties window and change value of these variables for this newly added scenario. BIAPPS.ETL_BULK_MODE

- Check Overwrite option – and set value to N

BIAPPS.ETL_HINT_NESTED3 - Check Overwrite option – and set value to /*+ FULL(XLA_TRANSACTION_ENTITIES_UPG) FULL(XLA_EVENTS) USE_HASH(XLA_TRANSACTION_ENTITIES_UPG XLA_EVENTS ) */ BIAPPS.ETL_HINT_NESTED2 - check Overwrite option – and set value to /*+ FULL(CR) FULL(CRH) FULL(RA_APP) FULL(RA_REV) FULL(ARD_REV) FULL(ARD_APP) USE_HASH(CR CRH RA_APP RA_REV ARD_REV ARD_APP) */ BIAPPS.ETL_HINT_NESTED1 - Check Overwrite option – and set value to /*+ FULL(AR_PAYMENT_SCHEDULES_ALL1) FULL(AR_CASH_RECEIPTS_ALL) FULL(AR_RECEIVABLE_APPLICATIONS_ALL) FULL(AR_DISTRIBUTIONS_ALL) FULL(RA_CUSTOMER_TRX_ALL) FULL(AR_PAYMENT_SCHEDULES_ALL) FULL(CE_BANK_ACCT_USES_ALL) FULL(RA_SALESREPS_ALL) USE_HASH(AR_PAYMENT_SCHEDULES_ALL1 AR_CASH_RECEIPTS_ALL AR_RECEIVABLE_APPLICATIONS_ALL AR_DISTRIBUTIONS_ALL RA_CUSTOMER_TRX_ALL AR_PAYMENT_SCHEDULES_ALL CE_BANK_ACCT_USES_ALL RA_SALESREPS_ALL) */

There should be no line gap in value of ETL_HINT_NESTED1, ETL_HINT_NESTED2, ETL_HINT_NESTED3 variable values.

21) Save changes by File->Save.

22) For each adaptor, relevant LP component should be edited. And matching scenario should be added from matching mapping folders to corresponding LP component. Do it for map SDE_ORA_ARTransactionFact_ReceivableApplication_InitialBalance Ex: LP 3 SDE Fact ARTRANS_FG EBS_12_0

-

Add scenario from these ODI adaptors

 – 

SDE_ORAR12_Adaptor

3 SDE Fact ARTRANS_FG EBS_12_1_1 --

SDE_ORAR1211_Adaptor

3 SDE Fact ARTRANS_FG EBS_12_1_2 --

SDE_ORAR1212_Adaptor

3 SDE Fact ARTRANS_FG EBS_12_1_3 --

SDE_ORAR1213_Adaptor

23) Run a full load ETL.

×

Report "Performance Issue With PLP APBalanceFactLoad Task"

Your name