Purge Cache Automation in OBIEE 11g

Why do we Purge Cache Automatically?

The cache option in OBIEE 11g can help us to improve query performance greatly. But sometimes when we refresh the data mapping or reload the transaction data, the result over report will be out of date and not refreshed immediately due to cache. The best way is that we clear the cache after we loaded data or modified the RPD. I did some material research and came up with a solution to clear cache automatically – using scheduled tool on ETL server to invoke scripts after ETL load. I’d like to share the steps as following.

Step 1: Clear Cache on Oracle BI

OBIEE 11G has Oracle BI Server utilities nqcmd and NQClient to run test queries against the repository. We can use nqcmd command to clear OracleBIServer cache. The nqcmd utility is available both on Windows and UNIX systems. The syntax of nqcmd command is:

nqcmd -dmy_dsn -umy_username [-pmy_password] -ssql_input_file -omy_result_file

1)       Create a file called purgecache.txt and place it at[FMW_HOME]/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

In the file, enter the code “call SAPurgeAllCache();” (without the quotes), which is a special BI Server command for clearing the entire cache.

2)       Create a shell script called purgecache.sh, place it in a directory where you store your custom scripts which includes the following commands. Note that there are some paths (in bi-init.sh) need to be set before you             run nqcmd which are in step 2. The purgecache.sh contains following commands.

  source /[FMW_HOME]/instances/instance1/bifoundation/OracleBIApplication/coreappli cation/setup/bi-init.sh
  [FMW_HOME]/Oracle_BI1/bifoundation/server/bin/nqcmd -d AnalyticsWeb -u administrator -p password -s                                                                                                         [FMW_HOME]/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup/purgecache.txt

Step 2: Clear Cache in Oracle Presentation Server

OBIEE 11G has a catalog manager command called “ClearQueryCache” to clear out the Presentation Server cache.

The syntax of ClearQueryCache command is:
runcat.cmd/runcat.sh -cmd clearQueryCache  -online <OBIPS URL> -credentials <credentials properties file>

1)       Create a catalog manager credential properties file. Open a text file and type the following entries.
login = <weblogic_admin_Username>
pwd = <weblogic_Admin_Userpassword>
And save in a directory with the file name as catmancredentials.properties.

2)       Open command prompt and navigate to <MW_HOME>instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalogmanager\

3)       Run the following command to clear OBIPS query cache:
runcat.sh -cmd clearQueryCache -online http://host:port/analytics/saw.dll?-credentials catmancredentials.properties

Step 3: Enable Passwordless Login Option between DAC and OBI Servers

1)        Login into server x.x.x.x as user oracle and generate a pair of public keys using following command.

            ssh-keygen -t rsa

2)       Use SSH from server x.x.x.1 (DAC server) to connect server x.x.x.2 (OBI server), use oracle as user and create .ssh directory under it, use following command.

            ssh oracle@x.x.x.1 mkdir -p .ssh

3)       Use SSH from server x.x.x.2 and upload new generated public key (id_rsa.pub) on server x.x.x.1 under oracle‘s .ssh directory as a file name authorized_keys.

cat .ssh/id_rsa.pub | ssh oracle@x.x.x.x.2 ‘cat >> .ssh/authorized_keys’

4)       Due to different SSH versions on servers, we need to set permissions on .ssh directory and authorized_keys file.

           ssh oracle@x..x.x.1 “chmod 700 .ssh; chmod 640 .ssh/authorized_keys”

Step 4: Invoke the PurgeCache script from DAC Server

1)       Create the NEW ITEM in the TASK TAB in the DESIGN, input TASK NAME, COMMAND FOR INCREMENTAL LOAD, COMMAND FOR FULL LOAD, TASK PHASE, EXECUTION TYPE and EXECUTING PRIORITY.

EXAMPLE FOR CLEAR OBIEE CACHE:

NAME: Clear OBIEE Cache

COMMAND FOR INCREMENTAL LOAD:  sh  XXX.sh

COMMAND FOR FULL LOAD: sh  XXX.sh

TASK PHASE: post ETL process

EXECUTION TYPE: External Program

EXECUTING PRIORITY: 5

2)       Add the NEW TASK in the following tasks in the EXECUTION PLAN.

Following tasks means the tasks which will be executed after the execution plan is done.

3)       Create EXECUTION PLAN