What Is Cache?… 5 Steps to Automatically Purge Cache in Oracle BI

by Raviteja Mamillapalli

What is Cache?

Analytics systems reduce database calls by creating a ‘Cache’ of data on the same machine as the Analytics engine.  Caching has a small cost in terms of disk space to store and a small number of I/O transactions on the server, but this cost is easily outweighed by the improvement in response time.

Types of Cache in OBIEE

There are two different types of cache in Oracle Business Intelligence Enterprise Edition (OBIEE):

1. The Query Cache (BI Server Cache): The OBIEE Server can save the results of a query in cache files and then reuse those results later when a similar query is requested. This type of cache is referred to as Query cache.
2. The Presentation Server Cache: When users run analytics, Presentation server can cache the results.

Why do we need to Purge Cache?

The main reason to implement the cache purge process is, when we have data that is being updated frequently in the warehouse and if there is a query cache present for the query that hits on the database, then the numbers may vary from that of the database. In such cases if you purge the cache the issue gets resolved.

Steps to Automatically Purge BI Server Cache

[Assuming a Oracle Business Intelligence Enterprise Edition (OBIEE) server and Data warehouse Administration Console (DAC) server are installed on the Linux machines.]

Here are the few steps to purging BI SERVER cache automatically in Oracle Business Intelligence Applications.  For our purposes, the OBIEE server and DAC server are on two different Linux machines.

Step 1

As the OBIEE and DAC servers present are two different servers we need to login to OBIEE server from DAC server.  This can be achieved by using the SSH command in Linux.

Step 2

As the DAC server requires password-less login, we either need to setup the RSA keys or use the ‘sshpass’ command to login to the OBIEE server.  Where RSA keys are most preferable by the client personal.  Setting up RSA keys is mostly the work of a DBA. 

Step 3

Once the login without using a password is completed, we now need to move to the OBIEE HOME and create two files: purge.txt and purgecache.sh

We also must provide the privileges required for execution.

Contents of purge.txt is

Contents of purgecache.sh is

Step 4

On the DAC server, go to the folder where DAC/scripts are present and copy the post_etl.bat file and change the extension of it to the post_etl.sh.  Add the following content into the file.

Step 5

Edit the DAC system properties for Script after Every ETL to execute post_etl.sh. 

—–

Thus after every ETL load the purging of the cache can be achieved.

We can check the output of the purging process by navigating to the dac/log folder and check for post_etl.sh.log file.

The Above steps help us purge the BI SERVER cache automatically after every DAC load.

Presentation Server Cache Management

The Presentation server cache can be managed in the instanceconfig.xml file and it’s a one-time setup by adding the tags shown below (just above the </ServerInstance> at the end of the file).  Don’t bother to automate the purge of Presentation server cache.

Web Cache
<CacheMaxExpireMinutes>1440</CacheMaxExpireMinutes> <CacheMinExpireMinutes>1440</CacheMinExpireMinutes> <CacheMinUserExpireMinutes>1440</CacheMinUserExpireMinutes>

<Catalog>
<AccountIndexRefreshSecs>120</AccountIndexRefreshSecs> <AccountCacheTimeoutSecs>180</AccountCacheTimeoutSecs> <PrivilegeCacheTimeoutSecs>180</PrivilegeCacheTimeoutSecs> <CacheTimeoutSecs>120</CacheTimeoutSecs> <CacheCleanupSecs>600</CacheCleanupSecs> </Catalog>
<StatePool>
<MemoryExpireMinutes> 1440 </MemoryExpireMinutes>
<DiskCleanupIntervalMinutes> 1440 </DiskCleanupIntervalMinutes>
</StatePool>

This one only for BI Server to Presentation server expire only. <ConnectionExpireMinutes>10</ConnectionExpireMinutes>

Note:
By specifying a low amount of time for a cache purge, refreshes can occur frequently.  This may negatively affect performance.   Test thouroughly to see which level is correct for your application.

Raviteja Mamillapalli is a Senior Consultant at KPI Partners and works with the expert team within the KPI Partners Offshore Technology Center. His areas of professional focus include OBIEE, Oracle Business Intelligence Applications, Informatica, and DAC.  He has received praise for his functional knowledge in healthcare and high-tech industries. Check out Ravi’s blog at KPIPartners.com.