OBIEE Cache Management

Cache Management

Overview: Oracle BI Server to maintain a local, disk-based cache of query result sets (query cache). The query cache enables the Oracle BI Server to satisfy many subsequent query requests without accessing back-end data sources, thereby increasing query performance.

Advantages

1. Performance Improvement
2. Less network traffic

Limitations

1. Chance to get stale data
2. Hard disk memory
3. Cache maintenance

Architecture

Enabling Cache

9. Log in to Fusion Middleware Control

2. From the target navigation pane, open Business Intelligence and select coreapplication.
3. Select Capacity management again Performance

4. Click on Lock and Edit configuration and click OK

5. Select cache enabled checkbox and then click Apply buttion

6. Click On activate Changes Button
7. Restart the BI Services.

Cache Purging:

As updates occur on the back-end databases, the query cache entries can become stale. Therefore, you must periodically remove entries from the query cache using one of the following methods.

1. Manual
2. Persistency Time
3. Event pooling table
4. ODBC Functions

Manual:

After enabling cache, run any report. Then cache will be created and Open RPD in online mode.

Click on Manage→cache→Right click on the entry→click on purge→Click ok and close.

Persistency Time:

It is useful to purge the cache based on fixed time.To provide this setting we need to know accurately the table updated frequency.

Example on Persistency Time:

Open RPD in online mode→Double click on D1 Products→In general tab→Select cache Persistence time and type 2mins and click OK→check In and save .Now reload server Metadata,Develop the report and check the cache after 2mins.Cache will be purged automatically after 2 mins.

Note: Cache can be controlled at table level  in physical layer. Double click on any table →Go to General tab enable/disable cache. Revert to cacheable→ cache never Expires.

Event Polling Table:

The use of an Oracle BI Server event polling table (event table) is a way to notify the Oracle BI Server that one or more physical tables have been updated and then that the query cache entries are stale.

An event polling table (S_NQ_EPT) is a way to notify the Oracle BI Server that one or more physical tables have been updated. Each row that is added to an event table describes a single update event. The cache system reads rows from, or polls, the event table, extracts the physical table information from the rows, and purges cache entries that reference those physical tables.Finally it truncate the Event Table(S_NQ_EPT)

Configuring the event polling table:

1. Navigate to the path “\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\schema” –> Open SAEPT.Oracle file and copy the DDL from there .DDL should be as below .
   –======================================================
   —  Script that creates an Event Polling Table for    ==
   — the Siebel Analytics Server in an Oracle database. ==
   –======================================================

————————————————–
—  Make sure to drop any existing table having —
— the same name as the Event Polling Table.    —
————————————————–
drop table S_NQ_EPT ;

————————————–
—  Create the Event Polling Table. —
————————————–
create table S_NQ_EPT (
UPDATE_TYPE    DECIMAL(10,0)  DEFAULT 1       NOT NULL,
UPDATE_TS      DATE           DEFAULT SYSDATE NOT NULL,
DATABASE_NAME  VARCHAR2(120)                      NULL,
CATALOG_NAME   VARCHAR2(120)                      NULL,
SCHEMA_NAME    VARCHAR2(120)                      NULL,
TABLE_NAME     VARCHAR2(120)                  NOT NULL,
OTHER_RESERVED VARCHAR2(120)  DEFAULT NULL        NULL
) ;

2. Copy this DDL and create table in Oracle database
   
3. Import S_NQ_EPT table into Repository.
   
4. To define the table as an event table perform the steps as shown in below screenshot.

Tools Menu→utilities→select Oracle BI event tables→execute→select S_NQ_EPT→provide polling frequency as 2 mins→click OK→Check in changes→click OK

Now event table is configured in the Oracle BI repository and cache invalidation occurs automatically. As long as the scripts that update the event table are accurately recording changes to the tables, stale cache entries are purged automatically at the specified polling intervals (10mins in our example).

Test :

1. Insert data into S_NQ_EPT table

INSERT INTO S_NQ_EPT(update_type,update_ts,database_name,catalog_name,schema_name,table_name,other_reserved)
VALUES (1,sysdate,’ORCL’,NULL,’BISAMPLE’,’SAMP_REVENUE_F’,NULL);

2. Wait the polling interval frequency and verify that the cache entry is deleted after 2 Mins. You can trace in the NQQuery.log file and also observe that , the data from table S_NQ_EPT is truncated .

ODBC Functions:

We have the following four types of odbc functions to purge the cache programmatically.

1. SA Purge all Cache→Deletes all cache

Syntax: call SAPurgeAllCache()

2. SA Purge by db → Deletes all cache related to a specific database

Syntax: Call SAPurgeCacheByDatabase( 'Datwarehouse' );   

3. SA Purge by table→ deletes specific table cache

Syntax:  Call SAPurgeCacheByTable( 'Datawarehouse',  '','DWH', 'XW_SALES_F');  

4. SA Purge by query→ Deletes a specific query cache

Syntax: call SAPurgeCacheByQuery('select product from PRODUCTS');

Execute the above functions in Analytics after developing the Report and make sure cache is created.

Click on Administration→Under maintenance and troubleshooting→Issue sql  and type any of the above function depending on the requirement→ Click on Issue SQL →check in the Cache folder →Cache is removed.

Click on Issue SQl Button

Cache Seeding:

Inserting the cache into cache folder is called as seeding.

Two types of Seeding:

1. Manual seeding:

Navigate to the report in the catalog, click on the report and then automatically cache will be created.

2. By using schedule:

Using Agents we can schedule the cache to be created in the cache folder