Author: Gourav Sarkar, Software Development Manager, Oracle BI-Apps Development
In OBIA, for data upgrades from one version to the other, standalone load plans are provided out-of-the-box (OOTB). These standalone load plans need to be executed by the customers after up-taking a new BI-Apps version. Data upgrade is required to facilitate the customers apply the data fixes related to OBIA upgrade.
Impact to Customized Warehouse
If customizations are done in DW ETL, upgrade will result in overwriting the customizations on DW data. To avoid overwriting the customized data in DW, a utility is provided to help customers apply upgrade selectively.
Challenges faced in this scenario are -
(1) Customizations leading to DW new tables
(2) Customizations in out-of-box DW tables i.e. adding new columns to OOTB tables
For the first scenario, the OOTB upgrade ETL will not regress / overwrite the customizations. In the case of the second scenario, the OOTB tables may get truncated as part of upgrade ETL run. In such a situation, customers will need to re-run the customized code or use the utility to selectively run upgrade ETL.
Below steps outline how the utility can be used to selectively run the OOTB Upgrade ETL.
Utility Functionality :
The utility disables Load Plan steps which customers don’t want to execute. It also intelligently finds out and disables the related load plan steps that truncate / reset the target tables (the target tables are the ones associated with the scenarios which customers don’t want to reload). If a reset / truncate table step has multiple target tables associated with it, the utility removes only the target table (which customers don't want to execute) from that step.
The utility consists of 2 parts –
(1) CSV Files
CSV files act as inputs to the utility. Each adaptor version (e.g. EBS 12.2, EBS 12.1.3, EBS 12 etc.) has separate csv files. The CSV files contain all SDE, SIL and PLP scenarios used in the upgrade load plans to load various target tables. Each CSV file contains 4 columns -
(a) Load Plan step name,
(b) Scenario Name,
(c) Target table name populated by the scenario,
(d) Enable Flag (Y/N) The enable flag controls the enabling/disabling of load plan steps. If the flag is set to ‘N’, the load plan step pertaining to the scenario and the other associated steps (i.e. reset / truncate table step) will be disabled.
(2) The Groovy Code
The groovy code is the heart of the utility and disables the Load Plan steps based on the .CSV input file.
Running the Utility :
(1) List down the target tables (staging or warehouse tables) for which you have added new columns and you don’t want to run the upgrade maps.
(2) Create a copy of the .csv file (e.g. “Copy_Of_EBS1213_DW_Upgrade.csv”) pertaining to the specific adaptor. The csv files for various adaptors are available here.
(3) Set the Enable_flag value to ‘N’ (last column in each row) in the copied version of the corresponding .CSV file for those tables mentioned in point#1.
Assume if you don’t want to load W_AP_AGING_INVOICE_A table, then find the W_AP_AGING_INVOICE_A table entry (or entries) in the .csv file and set the enable_flag to ‘N’ (as depicted below)
(4) Remove the other rows (which you don’t want to modify) from the copied version of the .csv file. Please ensure that there are no additional blank rows at the end of the .csv file.
(5) Save the .csv file
(6) Log into the ODI Studio using your login credentials.
(7) Create a duplicate copy of the upgrade load plan that you want to execute. Please also create backups of the tables mentioned in point #1.
(8) Open Tools -> Groovy -> New Script
(9) Copy the code available here and paste it in the highlighted section below.
(10) Execute the script by clicking on the green triangle button -
(11) The program will run and the following window will pop up.
(12) Select the upgrade Load Plan Name that you want to execute. Also enter location of the copied version of .CSV file. Click on “OK” button.
(13) The program runs and disables the steps flagged in the .csv files .
(14) After successful groovy script execution, a log file named "DisableLPSteps.log" will be created in the same directory where you placed the copied version of the .csv file.