Run the Incremental ETL with Clear Cache
Note:
- Execute this procedure only if you require the Oracle Analytics Server (OAS) Clear Cache option.
- Oracle Analytics Server Clear Cache option works only when Oracle Analytics Server and Oracle Data Integrator are installed on the same machine.
- Import the file
AM.zip
as per the steps specified in section 'Import Argus Mart Schema Objects' of section 4.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
- Set up the bi_clear_cache.sql.
- Copy the bi_clear_cache.sql from the ODI folder.
- Place the file to a location from where OAS can access it.
For example: - Linux: /u01/oascache/bi_clear_cache.sql- Windows c:\oascache\bi_clear_cache.sql
- Make sure the OS user that installs OAS has the required permissions for the bi_clear_cache.sql file.
- 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 OAS RPD.
- Configure Oracle Data Integrator for Oracle Analytics Server Clear Cache.
- Login to Oracle Data Integrator Studio.
- Connect to Oracle Argus Mart Work Repository.
- Navigate to the Designer tab.
- Open the package PKG_BI_CACHE.
- Navigate to the Diagram sub-tab, and select odiOSCommand_ClearCache.
- 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.
- Save the changes.
- Navigate to Overview > Scenarios tab, right-click the PKG_BI_CACHE Version 001 scenario, and select Regenerate.
- In the Regenerate Scenario pop-up window, click OK.
- In the Scenario Variables window, click OK.
- Login to ODI Studio.
- Connect to Argus Mart Work Repository.
- Navigate to the Operator tab.
- Go to Load Plan and Scenarios > LP_ARGUSMART and right-click the load plan LP_INC_AM_with_ClearCache.
- Enter the values for following variables:
Variable Description Sample Value VAR_ALN_BI_DOMAIN_PATH:
Defines Oracle Analytics Server 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/oascache
VAR_ALN_BI_USER:
Defines the Login ID of the Oracle Analytics Server admin user.
weblogic
VAR_ALN_BI_PWD:
Defines password of the Oracle Analytics Server admin user.
—
- To start the load plan execution click OK.
Parent topic: Manage the Incremental ETL Process