Run the Incremental ETL with Clear Cache

Note:

• Execute this procedure only if you require the Oracle Business Intelligence Enterprise Edition Clear Cache option.
• Oracle Business Intelligence Enterprise Edition Clear Cache option works only when Oracle Business Intelligence Enterprise Edition and Oracle Data Integrator are installed on the same machine.

1. Import the file AM.zip as per the steps specified in section 'Import Argus Mart Schema Objects' of section 4.2
2. Refresh the Designer tab and verify that:
   • A new load plan named LP_INC_AM_with_ClearCache is available.
   • PKG_BI_CLEAR_CACHE component is added to this new Load Plan.
   • The following new variables are available:
     • VAR_ALN_BI_DOMAIN_PATH
     • VAR_ALN_BI_SQL_PATH
     • VAR_ALN_BI_USER
     • VAR_ALN_BI_PWD
3. Set up the bi_clear_cache.sql.
   1. Copy the bi_clear_cache.sql from the ODI folder.
   2. Place the file to a location from where OBIEE can access it.

      For example:
      - Linux: /u01/obieecache/bi_clear_cache.sql- Windows c:\obieecache\bi_clear_cache.sql
      

   3. Make sure the OS user that installs OBIEE has the required permissions for the bi_clear_cache.sql file.
   4. Edit the SQL file as required:

      To...	Add
      Clear the entire cache	call SAPurgeAllCache();
      Purge the cache of OPVA_DWH database	call SAPurgeCachebyDatabase('OPVA_DWH');
      Purge the cache of AI80_SRC database	call SAPurgeCachebyDatabase('AI80_SRC');
      Purge the cache of multiple databases	call SAPurgeCachebyDatabase('OPVA_DWH');call SAPurgeCachebyDatabase('AI80_SRC');

      Note:

      Here, the OPVA_DWH and AI80_SRC are the database names in the physical architecture inside OBIEE RPD.
4. Configure Oracle Data Integrator for Oracle Business Intelligence Enterprise Edition Clear Cache.
   1. Login to Oracle Data Integrator Studio.
   2. Connect to Oracle Argus Mart Work Repository.
   3. Navigate to the Designer tab.
   4. Open the package PKG_BI_CACHE.
   5. Navigate to the Diagram sub-tab, and select odiOSCommand_ClearCache.
   6. Configure the following based on your environment:

      Parameter	Action
      Command To Execute (Only for Windows)	Update the command to have relative folder paths as per the Windows OS convention. For example: #ARGUSMART.VAR_ALN_BI_DOMAIN_PATH/bitools/bin/nqcmd.cmd -d AnalyticsWeb -u #ARGUSMART.VAR_ALN_BI_USER -p #ARGUSMART.VAR_ALN_BI_PWD -s #ARGUSMART.VAR_ALN_BI_SQL_PATH\bi_clear_cache.sql
      Output File	Update the file location.
      Error File	Update the file location.

   7. Save the changes.
   8. Navigate to Overview > Scenarios tab, right-click the PKG_BI_CACHE Version 001 scenario, and select Regenerate.
   9. In the Regenerate Scenario pop-up window, click OK.
   10. In the Scenario Variables window, click OK.
5. Login to ODI Studio.
6. Connect to Argus Mart Work Repository.
7. Navigate to the Operator tab.
8. Go to Load Plan and Scenarios > LP_ARGUSMART822 and right-click the load plan LP_INC_AM_with_ClearCache.
9. Enter the values for following variables:

   Variable	Description	Sample Value
   VAR_ALN_BI_DOMAIN_PATH:	Defines Oracle Business Intelligence Enterprise Edition Domain Home Path.	/u01/app/oracle/Middleware/user_projects/domains/bi
   VAR_ALN_BI_SQL_PATH:	Defines complete directory path where bi_clear_cache.sql is available.	/u01/obieecache
   VAR_ALN_BI_USER:	Defines the Login ID of the Oracle Business Intelligence Enterprise Edition admin user.	weblogic
   VAR_ALN_BI_PWD:	Defines password of the Oracle Business Intelligence Enterprise Edition admin user.	—

10. To start the load plan execution click OK.