Cache Management in OBIEE 11g

   BI Server Query Cache

     Query Cache is a feature of the BI Server to save query results on the disk, to be later reused when      a similar query request arrives.

     

     As such Query Cache is performance enhancement feature which is transparent to the user,   

     It falls on the OBIEE administrator to ensure it is setup and managed in an optimal fashion.

   

   Keeping the Cache Fresh

   Problem:

If cache entries are NOT purged when the ETL has updated the underlying database tables, then the users see incorrect or old data in their Reports and Dashboards.

   Solution:

A well understood method of Cache Management is to configure Oracle BI Server Event Polling Tables. These tables track the updates to database, with a row inserted for each table which has been updated by the ETL process. The Oracle BI Server polls the table(s) at predetermined interval, removing the stale cache entries.

        The Repository Creation Utility (RCU) run as part of the OBIEE installation, also creates the Event Polling Table by the name of S_NQ_EPT in the BI_PLATFORM schema.
        Next, make the Event Polling Table active in the BI Server:

• Bring S_NQ_EPT into the repository by Import Metadata dialog
• Mark the table as an Event Polling object by going to Utilities-> Oracle BI Event Tables, Click Execute
• Set the Event Polling Interval – Default is 60 Minutes (Less than 10 Minutes polling interval is too low)

Gotcha:

        Pay particular attention to the Insert statement populating the EPT table.    
        If the S_NQ_EPT table is in a different schema then the Cached tables, 
       then you might find Cache Purge not working as expected.
       
       Check for associated errors in the NQSERVER.log

 

        In Order To Resolve this:

•  Put Event Polling Table in the same schema as the DW tables.
• Move the Event Polling Table to the default schema in the repository. 
• Use the Fully Qualified Table name checkbox.

Resulting Insert Statement is pretty straightforward.



Alternate Solution:
        If the above sounds like too much work, and the requirement is to Purge Cache for all the tables in a Star or Subject Area after an ETL Batch has been completed then read on.

    There is a little noticed nugget in the Cache Management documentation for OBIEE 11g.

        "When the value of a dynamic repository variable changes, all cache entries that are associated with a business model

that reference the value of that variable are purged automatically. The cache entries are purged when the repository

variable refresh rate is reached, if its value has changed."

 
    In order to leverage this feature:

• Query the ETL Status Table(s) to monitor when the desired ETL load has completed
  • Use an Initialization Block with suitable refresh rate.
• Populate a Repository Variable with the Result
• Introduce a Derived Logical Column in the Business Model (say Sales) which references the Dynamic Repository Variable.
• When the ETL Completion Status flips from 'N' to 'Y', All Tables Referenced by the BM layer containing the Logical Column will h their Cache Purged.

 

Moral of the Story:

        Read the Documentation. As someone colorfully said, RTFD.

        You might be surprised with what you learn.