All topics
4 / 55
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.

The BI Server caches query results. It is the largest available performance gain and the fastest way to show users stale numbers.

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
How a hit is decided. The cache is keyed on a canonicalised form of the logical SQL plus the security context.
2
The security context is essential: two users with different data security must not share a cache entry, or one of them sees the other's rows.
3
Subsumption is the subtlety worth knowing. A new query can hit an existing entry even when it is not identical, if it asks for a subset:
4
    cached:  revenue by region, by month, for 2026
    hit:     revenue by region for 2026        -- aggregate the entry
    hit:     revenue by region, by month, for March 2026  -- filter it
    miss:    revenue by product                -- a column not held
5
So one broad cached result serves many narrower questions, which is what makes seeding effective.
6
Seeding is the deliberate use. Run the expensive queries on a schedule, before users arrive:
7
    03:00  the ETL finishes
    03:30  purge the cache
    04:00  an agent runs the main dashboard analyses -> cache seeded
    08:00  users arrive and hit a warm cache
8
Purging is the correctness half, and the order matters. Cache entries must be purged when the underlying data changes, or users see yesterday's numbers.
9
    purge after the ETL, before seeding
    -- purging after seeding throws away what you just built
10
Three ways to purge.
    event polling   a table the ETL writes to; the server polls it
                    and purges the affected entries
    scheduled       purge everything on a timer
    manual / API    from the Administration Tool or a command
11
Event polling is the correct mechanism: the ETL announces which tables changed, and only the entries depending on those tables are dropped. Time-based purging either drops too much or leaves stale entries between runs.
12
Where caching is wrong. A subject area over live transactional data, where the point is currency. The cache is configurable per physical table, so real-time tables can be excluded while the warehouse tables are cached.
13
The failure to recognise. "The numbers on the dashboard do not match the report I just ran." Usually one came from cache and one did not, which means the purge is not tied to the refresh.
14
What the interviewer is probing.1. "What are the cache layers?" Probing: the breadth. Stalls: "One cache." Moves up: the BI Server query cache, Presentation Services caching, and the database's own — plus seeding through agents.
15
2. "What is the risk with the BI Server cache?" Probing: the correctness point. Stalls: "None." Moves up: stale results after a data load, so the cache must be purged or event-polled as part of the ETL, not left to expire.
16
3. "What is cache seeding?" Probing: the technique. Stalls: "Warming up." Moves up: running agents after the load to populate the cache with the expensive queries, so the first user does not pay for them.
17
4. "What is an event polling table?" Probing: the mechanism. Stalls: "A log." Moves up: a table the ETL writes to and the server polls, purging cache entries for the changed physical tables automatically.
Use cases:cachingperformanceetl-integration