Cache

Oracle BI Server Query Cache - Cache Architecture – Configuration- Cache Management Techniques - Cache Parameters - Setting Caching and Cache Persistence for Tables - Cache Manager - Inspecting - Cache Entries - Cache Reports - Purging Cache Manually - Automatically - Event Polling Tables - Seeding the Cache - Cache Hit Conditions - Bypassing Cache

Standard

How do you configure a Subject Area that its reports never gets Cached?

We need to uncheck the Cacheable Property of all physical tables of this Subject Area, so whenever a report is run the query hits the database.

How do we see all the OBIEE Server Cache entries?

Open the RPD in Online MODE. Navigate to Manage/cache.
We can see all the Cache entries in the Cache Manager.
We can also Purge some of the Cache entries using Cache Manager.

What modification should be done in the report that the query hits the database, by passing the OBIEE Server cache?

User needs to add a setting SET VARIABLE DISABLE_CACHE_HIT=1: in the Prefix section of the Advanced Tab and run the report again.

A Subject Area is Configured against OLTP, how do we clear the cache pertaining to this Subject Area every 12 hours?

Create an initialisation block with refresh interval 12 hours

sql : select case when extract(hour from current_timestamp) <=12 Then 2 else 3 end AS PERIOD from dual This sql changes the value every 12 hours create variables and associate to this initialsation block , bring this variable in the corresponding business model, whenever the initialisation block fires the query the variable takes a different value, the cache pertaining to that business model gets cleared. How do we purge All Cache entries from UI?

We need to Login as Administrator. Navigate to Settings/Administration/Issue SQL.
Issue the Following SQL - Call SAPurgeAllCache();
All OBIEE Server Cache Entries will be purged.

Navigate to Settings/Administration/Manage Sessions
Click on close All Cursors and Cancel Running Requests.
All OBIEE Presentation Server Cache Entries will be purged.

Tough

What are Event Polling Tables? How do we Implement them?

OBIEE Event Polling Tables stores information about any updates in the underlying Databases. Oracle BI Server polls this table at set intervals and purges any stale cache entries that references this tables.

Implemention

Navigate to the folder C:\OracleBI\server\Schema

Run the script in the test file SAEPT.Oracle.sql against the Oracle database.

Event Polling tables are thus created.

Import the table S_NQ_EPT into the repository.

Navigate to Tools/Utilities




Click on Oracle BI Event tables and Execute.

Select the Event Polling Table S_NQ_EPT and set the polling Frequency.

Save the Repository.

Oracle BI Server clears the OBIEE Server cache entries for all the table names which are found in the S_NQ_EPT at the polling intervals. After clearing the cache it truncates the table S_NQ_EPT.

Populating the Event Polling Table.

For any Updates in the database has to be updated in the S_NQ_EPT Table. In other words any table which gets updated in an ETL load needs to have an Entry in the S_NQ_EPT Table. This can be done using the following PL/sql.

create two tables POC_NQ_EPT and POC_NQ_EPT_HST

CREATE TABLE POC_NQ_EPT
( TABLE_NAME VARCHAR2(100),
ETL_PROC_WID NUMBER,
REC_CNT NUMBER
)

CREATE TABLE POC_NQ_EPT_HST
( TABLE_NAME VARCHAR2(100),
ETL_PROC_WID NUMBER,
REC_CNT NUMBER
)

Create a stored Procedure as follows

create or replace
PROCEDURE POC_EPT as
var_for_max_proc_id number;
var_for_cnt number;
begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE POC_NQ_EPT_HST';
INSERT INTO POC_NQ_EPT_HST (SELECT * FROM POC_NQ_EPT);
EXECUTE IMMEDIATE 'TRUNCATE TABLE POC_NQ_EPT';

for rec in (select ALL_TABLES.TABLE_NAME TABLE_NAME from all_tables,all_tab_columns where
ALL_TABLES.table_name=ALL_TAB_COLUMNS.table_name
AND all_tab_columns.column_name='ETL_PROC_WID' AND ALL_TABLES.table_name LIKE 'W%' )
loop
--dbms_output.put_line(rec.TABLE_NAME);
EXECUTE IMMEDIATE 'SELECT MAX(ETL_PROC_WID),count(*) FROM 'REC.TABLE_NAME' ' INTO var_for_max_proc_id,var_for_cnt;

--dbms_output.put_line(var_for_max_proc_id);
INSERT INTO POC_NQ_EPT VALUES (rec.TABLE_NAME,var_for_max_proc_id,var_for_cnt);
COMMIT;
end loop;
insert into s_nq_ept(table_name) (select c.table_name from poc_nq_ept c, poc_nq_ept_hst hst
where c.table_name= hst.table_name and c.etl_proc_wid<> hst.etl_proc_wid
UNION ALL
select c.table_name from poc_nq_ept c, poc_nq_ept_hst hst
where c.table_name= hst.table_name and c.etl_proc_wid= hst.etl_proc_wid AND c.REC_CNT<>HST.REC_CNT);
end ;

This stored Procedure should be run after the ETL load completes. This Stored Procedure can be executed from a mapping which runs at the end of the ETL Load.