Notes for implementing Universal adapter for OBIA Project analytics

ODI E-LT and Work Tables (I$ and C$)

In BI Applications 11.1.1.7.1, the ETL is performed by ODI, which transforms the data by issuing SQLs to both the source and DW DBMS, otherwise known as E-LT.  During this E-LT processing ODI creates intermediate or work tables in the BI Application Data Warehouse schema, these are tables prefixed with I$ and C$.  When a scenario successfully completes, it will automatically delete these tables, as they're transitory and are no longer required.  However, where a scenario does not complete successfully, it is possible these tables get left behind and from time to time it maybe desirable to clean up these tables to reclaim space.

Each execution of a scenario is assigned a unique Session ID and this is used as part of the name of the work table name.  The reason for this approach is to ensure that each work table is unique and to ensure that parallel processes even for the same scenario don't try and create a work table with the same name, collide and fail.  We have configured our restartability strategy to pick up from the point of failure and to reuse the existing session ID as much as possible and have documented the steps to ensure this happens in the Restartability section of the ETL documentation, however, there are situations where it's feasible particularly in early development phases when scenarios fail and work tables are left over. The work tables in of themselves don't cause any harm, however they can in certain circumstances be large and it's normal to want to clean them up to free up database disk space.

Cleanup Utility

As some work tables maybe being used, or about to be used in the case of a stuck E-LT job, BI Applications provides a utility that can be executed to go and clean up these orphan work tables safely.  The approach used to ensure work tables that are still in use are not deleted, the utility only deletes work tables associated with scenarios older than the value given in an Older Than Days parameter.  The default value is 30, but this can easily be changed.

To run the procedure:

1. Open ODI Studio and connect to the BI Apps ODI Repository.
2. Navigate to the Designer tab and use the navigator to navigate to: BI Apps Project -> Components -> DW -> Oracle -> Clean Work and Flow Tables folder
3. In the folder find the Clean Work and Flow Tables package and in this package is the UTILITIES_CLEAN_WORK_AND_FLOW_TABLES scenario.  Right click the scenario and select the 'Execute' option.  At the prompt, provide the desired number of days to go back before deleting tables