Cache Purge

To Purge Cache

Cache entries can be purged with the following options :

1) Presentation Services (Using SQL Commands)

2) Repository

3) Event polling table

Cache will hold the queries for all the logical request issued by the user in OBIEE Application. These cache entries will be stored in a log file. And the details of these cache entries willl be controlled by the log level set.

log level generally controls the amount of information that is stored for each and every cache entries in log file.

Presentation Services (Using SQL Commands)

The Cache entries can be purged in Presentation Services using SQL Commands. Only Administrator can purge the cache entries.

To check the physical query in cache for the logical request that is issued by the user:

Manage Sessions will hold all the list of queries for all the logical request that issued by the user in OBIEE Application.

Go to Administration -> Manage Sessions

To clear the cache entries:

There are different types of SQL commands that is issued in the Issue SQL to clear the cache entries . Each and Every SQL Commands servers different purpose:

Go to Administration ->  Issue SQL -> SQL Commands

1. Call SAPurgeAllCache();  :  This SQL Purges all the cache entries in one shot.

2. Call SAPurgeCacheByTable('DBNAME', 'CATNAME', 'SCHNAME', 'TABNAME');This SQL Purges     the cache entries of a specific table.

3. Call SAPurgeCacheByTable('DBNAME');This SQL Purges the cache entries of a specific database.

4. Call SAPurgeCacheByQuery(SELECT * FROM TABLE WHERE EMP_ID = '1');This SQL Purges         the cache entries of a specific query.

Repostiory

The cache entries can be purged through repository.

Go to Manage->Cache 

Selecting Cache from Manage, will navigate to a Cache manager window:

Cache manager will hold the list of cache entries. Each entry in a cache manager indicates the queries executed by the user at a specified interval of time. 

To purge the specified query, right click the entry and select purge which would delete the selected entry.

Rightclick -> Select Purge

(or)

To purge all the entries, 

Click Edit-> Select All-> Purge

Event polling Table:

Event polling table purges cache based on a event. It allows report to show only the recent data by purging the cache entries.

Event polling table has the following columns:

Catalog Name (CHAR/VARCHAR) - catalog name where the updated physical table resides.
Database Name (CHAR/VARCHAR) - database name where the updated physical table resides.
Other (CHAR/VARCHAR) - null (can be used if required in future)
Schema Name (CHAR/VARCHAR) - schema name where the updated physical table resides.
Table Name (CHAR/VARCHAR) - table name which is updated.
Update Time (DATETIME) - refers to the time table updated.
Update Type (INTEGER) - specifies the type of update.

Whenever the fact or dimension tables gets loaded, then an entry will be populated in the Event polling table. Cache will be purged, once the event polling table is populated with an entry. After purging the cache, the entry in the event polling table will also be deleted.