In BIAPPS whenever a ODI package is executed it reads W_ETL_LOAD_DATES table to verify if a record exists with that package name. If record found package is executed in incremental mode else Full load mode. In incremental mode package will update the last_executed_date of existing record in W_ETL_LOAD_DATES table and in Full load mode package will insert new record.
To perform a full load we need to execute Reset Data warehouse scenario through BIACM, This scenario resets the data warehouse by truncating the W_ETL_LOAD_DATES table. This ensures that packages in subsequent load will not find a record in W_ETL_LOAD_DATES table and truncate all target tables and do a fresh full load.
We can also achieve this by truncating table W_ETL_LOAD_DATES manually in Data Ware House database.
TRUNCATE TABLE W_ETL_LOAD_DATES;
Steps to execute Reset Data warehouse scenario through BIACM:
Login to BIACM http://<hostname>:9704/biacm
Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud
Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.
Click on manage load plans
Click on actions
Click on Execute Reset Data Warehouse Scenario
Select context, logical agent, work repository and log level
After executing this scenario, check in Data Ware House schema. Table W_ETL_LOAD_DATES should be truncated.
Then execute load plan, it will truncate and full load all tables as there are no records in W_ETL_LOAD_DATES table.
To Full load particular table, we need to delete a record from W_ETL_LOAD_DATES associated to package which populates this table.
Ex:
DELETE FROM TEST_DW.W_ETL_LOAD_DATES where PACKAGE_NAME = ‘PLP_PLP_APSNAPSHOTINVOICEAGING’;
