OBIEEintermediate
OBIEE Caching Strategy
Explain how BI Server query result caching works, including cache hit criteria, seeding, purging, and its trade-offs versus real-time reporting needs.
Caching is one of the most interview-relevant OBIEE topics because it sits at the intersection of performance and data correctness — get it wrong and users either wait too long or see stale numbers, and both complaints land on the BI team's desk.
It's a librarian who remembers the answer to a question she was just asked and gives it to the next person who asks something similar, but she has to be told the moment new books arrive so she stops handing out outdated answers.
Key Concepts
1
The BI Server maintains a query result cache: when a logical SQL request is executed, the server can store the result set (keyed by a canonicalized version of the logical SQL, plus security context) so that a subsequent identical or subsumed request is served from memory instead of re-hitting the database. A "subsumed" match means a new request asking for a subset of previously cached columns/filters can still hit the cache — a subtlety worth mentioning in interviews.
query result cache
2
Cache behavior is controlled at multiple levels: the NQSConfig.INI [CACHE] section (ENABLE = YES, MAX_ROWS_PER_CACHE_ENTRY, MAX_CACHE_ENTRY_SIZE, MAX_CACHE_ENTRIES), the cacheable/non-cacheable flag on physical tables (in the RPD's physical layer, a table can be marked "cacheable" or not, and given a cache persistence time), and explicit purge events. Purging can be triggered manually (SAPurgeAllCache, SAPurgeCacheByQuery, SAPurgeCacheByTable, SAPurgeCacheByDatabase ODBC procedures), by an ETL job calling an event polling table, or automatically via a cache persistence timeout.
cacheable/non-cacheablecache persistence timeNQSConfig.INI[CACHE]ENABLE = YES
3
The trade-off interviewers want you to articulate: caching dramatically reduces database load and latency for frequently-run dashboards, but introduces staleness risk. Best practice is to disable or tightly control caching on tables that change intraday and to integrate cache purging into the ETL pipeline (purge relevant tables right after a load completes) rather than relying purely on time-based expiry.