X

The Oracle BI Application Blogs provides the latest and tips and tricks on Oracle BI Applications Product (OBIA)

Restoring Snapshot data when upgrading to BI APPS 10.3

Subbarao Palacherla, and Zulfiqar Alibaig

During migration from BIAPPS 10.2 to BIAPPS 10.3, the historical data which has been accumulated over period of time in the BIAPPS 10.2 of Snapshot/Aggregate tables won’t be transferred automatically and will be lost. Once erased, such historical data cannot easily be retrieved or populated.
In order to preserve Snapshot data from aggregate or Snapshot tables, steps has been provided to create the scripts to backup required Warehouse tables from 10.2. They can be copied to 10.3 Dataware house schema and then restore the data back into the Snapshot tables once the Full load is complete.


OOTB delivered snapshot tables are categorized as below based on the sources they are loaded from.
1. Sourced from the staging tables
2. Sourced from other fact tables.

Based on the above tables, 2 approaches are designed and accordingly scripts to restore the older snapshots are explained in detail. If snapshot are loaded directly from fact staging tables then go with approach 1 else approach 2.
1) Insert scripts.
2) Update scripts.

This document explains in details the solution/steps that should be performed to restore the snapshots based on the above two approaches.
 

1. Insert scripts (Snapshot facts Loaded Directly from Staging Tables)

Inventory snapshot fact is taken as an example to explain the approach 1 in detail.
The below mentioned snapshot tables may have huge data and many dimensions. When we do a full load in 10.3, the snapshot data will be populated as per the load date and also the dimensional wids might get deferred compared to 10.2. To restore the old snapshot data from 10.2, the old dimension wids need to be replaced with the new wids. In this approach we need to reverse engineer by populating staging table data from the respective snapshot fact table in 10.2. Therefore, we have to create an insert script based on their respective fact staging tables to restore the old snapshot for below tables.
We have created a scripts on how to take the backup of snapshot data from 10.2 before full load in 10.3 and restore the old snapshot data after first full load in 10.3.
Based on the OOTB delivered snapshot fact tables (mentioned below) provided the scripts to restore the data. If any customization done by the customer then scripts have to be modified accordingly.

A. W_CST_INTRANSIT_DAILY_F
B. W_CST_ONHAND_DAILY_F
C. W_CST_ITEM_COST_DAILY_F
D. W_CST_ONHAND_ACC_DAILY_F
E. W_CST_INTRAN_ACC_DAILY_F
F. W_INV_AGING_F
G. W_INVENTORY_DAILY_BAL_F

2. Update scripts (Snapshot facts Loaded Directly from Other Fact tables)

The snapshots are taken based on the other fact tables at a particular time. So they can only replace the old dimensional wids with the new ones. For this use case, we need to go with the update approach. Created a scripts to take the backup of snapshot data before full load and restore the old snapshot data after full load run.

W_INVENTORY_MONTHLY_BAL_F
W_INV_LOT_MONTHLY_BAL_F


Based on the OOTB delivered snapshot fact tables (mentioned below) provided the scripts to restore the data. If any customization done by the customer then scripts have to be modified accordingly.

A. W_CST_ITEM_COST_MONTHLY_A
B. W_PROJ_STATUS_MNTH_SNP_F
C. W_PROJ_STATUS_QTR_SNP_F
D. W_PROJ_STATUS_YR_SNP_F
E. W_PROJ_COMMITMENT_SNP_F
F. W_MFG_JOB_DETAIL_SNP_F
G. W_WRKFC_SNP_F
H. W_AP_AGING_INVOICE_ENT_A
I. W_AP_AGING_SUPPLIER_ENT_A
J. W_AP_AGING_SUPPLIER_A
K. W_AP_AGING_INVOICE_A
L. W_AR_AGING_CUSTOMER_A
M. W_AR_AGING_CUSTOMER_ENT_A
N. W_AR_AGING_INVOICE_A
O. W_AR_AGING_INVOICE_ENT_A
P. W_INVENTORY_MONTHLY_BAL_F
Q. W_INV_LOT_MONTHLY_BAL_F

Note: The scripts are available in the zip file which can be downloaded from the below link.

Restore scripts


Disclaimer:
The above details including the scripts (based on OOTB code) are provided to the customers as workaround and not officially supported by Oracle. Customers should use them at their discretion and treat them as their customization. The scripts provided are not fully tested with customer snapshot data. They should be thoroughly validated and tested by the customers before implementing them. Customer has to modify the script as per the customization done by the customer.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.