OBIEE Cache is enabled, but why is the query not cached?

Repeatedly customers pose the question – OBIEE cache is enabled, but why is the query not cached? The reason why the queries are not cached can be of many reasons. Some of the reasons are:

Non-cacheable SQL function: If a request contains certain SQL functions, OBIEE will not cache the query. The functions are CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, RAND, POPULATE. OBIEE will also not cache queries that contain parameter markers.

Non-cacheable Table: Physical tables in the OBIEE repository can be marked ‘non-cacheable’. If a query makes a reference to a table that has been marked as non-cacheable, then the results are not cached even if all other tables are marked as cacheable.




Query got a cache hit: In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache. Note: The only exception is the query hits that are aggregate “roll-up” hits, will be added to the cache if the nqsconfig.ini parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to Yes.

Caching is not configured: Caching is not enabled in NQSConfig.ini file.



Result set too big: The query result set may have too many rows, or may consume too many bytes. The row-count limitation is controlled by the MAX_ROWS_PER_CACHE_ENTRY nqsconfig.ini parameter. The default is 100,000 rows. The query result set max-bytes is controlled by the MAX_CACHE_ENTRY_SIZE nqsconfig.ini parameter. The default value is 1 MB. Note: the 1MB default is fairly small. Data typically becomes “bigger” when it enters OBIEE. This is primarily due to Unicode expansion of strings (a 2x or 4x multiplier). In addition to Unicode expansion, rows also get wider due to : (1) column alignment (typically double-word alignment), (2) nullable column representation, and (3) pad bytes.



Bad cache configuration: This should be rare, but if the MAX_CACHE_ENTRY_SIZE parameter is bigger than the DATA_STORAGE_PATHS specified capacity, then nothing can possibly be added to the cache.

Query execution is cancelled: If the query is cancelled from the presentation server or if a timeout has occurred, cache is not created.

OBIEE Server is clustered: Only the queries that fall under “Cache Seeding” family are propagated throughout the cluster. Other queries are stored locally. If a query is generated using OBIEE Server node 1, the cache is created on OBIEE Server node 1 and is not propagated to OBIEE Server node 2

Oracle BI Query not Cached.....know the reasons

There are several reasons for obiee query not being cached.Here are most of them.
First of all are you sure its not cached.

To Check,

1.Open your RPD in online mode.
2.Under Manage you will find cache.
3.When you click on it Cache manager is popped up.
4.If you find the query in the window then you are wrong,its cached else there is a problem we have to fix.

Now lets see the possible reasons for this

1.Cache Enabled :
Was your cache enabled.
         To Know open the NQSCONFIG.INI file
         Under Cache Sction if 'YES' tihen everything is fine here.
         If no then make it YES.

2.Use of Non-cacheable SQL functions:

      If any of the Non-cacheable functions are used in the request then the query will not be cached.

       i.e CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, RAND, POPULATE

3.Non-cacheable Table:
             If the query for the request makes any connection to a non cacheable table then the query/results will not be cached even if all other tables are set to cacheable.


4.  Query execution Terminated:
While running the request on Presentation Server if the execution is forcibly cancelled or time out occurred, cache will not create.

5.Bad cache configuration:
If the MAX_CACHE_ENTRY_SIZE parameter is set to a bigger value as compared to the actual storage space (DATA_STORAGE_PATHS) available on the drive then nothing can possibly be added to the cache.

6.Result set to Large to Handle by Cache:
The max row limit for cache is set in a parameter in nqsconfig.ini, named MAX_ROWS_PER_CACHE_ENTRY (default 100,000 rows) and MAX_CACHE_ENTRY_SIZE (default 1 MB). If the rows retrieved in the request exceeds the MAX ROW LIMIT or the data retrieved by the request exceeds the MAX CACHE ENTRY SIZE then the cache will not be created.

7.When a Query got a cache hit:
In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache. The only exception is the query hits that are aggregate “roll-up” hits, will be added to the cache if the nqsconfig.ini parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to Yes.