OBIEE - Purge BI Server Cache

Oracle BI is most powerful and user friendly Business Analytics tool.This is because its simple to use and generates high data reports in quite a less amount of time (less than a sec for some cases).One of the reasons for such performance is because of the Cache feature in the tool.

When a query is run by one user it can be saved as a file to cache; if another user queries for the same information, rather than wait for the information to be fetched from the database it can be queried or retrieved  from the cache for a better performance.

There are some problems when we are using Cache, one among them is existence of stale data.So purging is one among the admin activities that needs to be carried when we use cache feature.

Ways to Purging BI Server Cache

There are three ways of doing it

1. via the Presentation Services 
2. using the Administration Tool
3. with an OS (Operating System) level command

Purging the Cache via the Presentation Service

Select Settings, Administration; the Administration Window will open
Select to Issue SQL; this window will allow you to issue SQL Directly
In the command window enter the text below and click to Issue SQL
          Call SAPurgeAllCache()

Using the Administration Tool

Administration tool is client tool for OBIEE from which we can access the BI server components like RPD,Cache,Security etc. 

Open Admin Tool

Open RPD in Online Mode

Go to Manage --> Cache

You can now see the cache entries in the window. Select the cache entry you want to purge and right click and purge cache.

With an OS (Operating System) level command

We can also use the nqcmd executable to issue the same command, taking advantage of ODBC extensions functions.

Login to the hosting machine and navigate to the Server Bin directory

Linux Command Shell
$ cd /OracleBI/server/Bin$

Use an editor such as vi to create a file containing the command you want to issue. The first window shows the command to open the text editor, the next is the contents of the file.

     Linux Command Shell
    $ vi purge.txt$


    purge.txt
    Call SAPurgeAllCache()

Follow the instructions listed below to issue the purge command(s)

    Linux Command Shell
    $ cd /OracleBI/server/Bin$ nqcmd -d “AnalyticsWeb” –u Administrator –p Password –s purge.txt$

In this example –d is used to identify the datasource, -u username, -p password and –s sql input file
The advantage of the latter approach is that the executable can be scripted to occur at regular intervals.

Some of the default commands provided by Oracle are below

Call SAPurgeAllCache( );

Call SAPurgeCacheByTable( );

Call SAPurgeCacheByQuery( );