Tag Archives: cache

Problem With Caching A Report in Answers Using A Script

The cache is purged but new cache is not created. Customer is not able to cache the reports which contain Database Functions for Columns.
All the cache entries were not created when used a logical query which contains DB functions in it. The query doesn’t get cached and there is no error
Cache is created when the queries are not using any database functions.


Queries containing the evaluate function are not qualified to cache. Hence this query is not getting cached. This is expected behavior.
Evaluate is not cacheable because there is no reliable means for OBIEE to interpret the semantics of the string function definition and determine if the
function defined there can be reused under what condition. Take a simple example, Evaluate(‘Today()’ as Date), OBIEE would have no idea this function
returns a result that can be reused till the end of day.

Dynamic repository variables are refreshed by data returned from queries. The same rule applies here. If the query that refreshes the variable is using a
database function and evaluate then the whole query (report) will not be cached as the value of the variable cannot be cached.
If the query that refreshes the variable does not use database functions then it is cached and the report should be cached.
However, when the value of a dynamic repository variable changes, all cache entries associated with a business model that reference the value of the variable,
are purged automatically.

If you see the query in the Cache Manager then it means that is cached. You can check if that particular cached query is used or not by checking the “Last
used” column after the user ran the report. The cache should be used and the value of the “Last Used” column should be updated and different from the value
of the “Created” column.

Using iBots is an alternative. If you schedule the reports they should appear in the cache until the cache is purged. Keep in mind that reports having the
database functions and evaluate will not be cached either. Also, reports having dynamic repository variables will automatically be purged when the variable
The iBot should not include queries with database functions and evaluate in order to be cached.

Data Not Getting Refreshed in Answers when Changed in Base Database. Cache problem.

Found something interesting about cache today.


Have a report in Answers.
For one of the values (Jan09 / Budget) the value is 10,000.
Value in database is updated to 600,000
Looking at the report in Answers, it still displays the amount for this value as 10,000.  This happens even after selecting ‘Refresh’ for the report.

If the user drills into Jan09, & so views each week of Jan09, then the value can be seen as 600,000. Drill back out to Jan09 & again it is displaying as 10,000 (the original value).

Turning off caching (in NQSConfig.INI with [ CACHE ] ENABLE =NO ) DOES fix the issue. However, this causes performance issues.
In fact, this is working as designed.

As per Answers guide, this is expected behavior:

“When executing an Oracle BI Interactive Dashboard or a request, Oracle BI uses temporary storage areas, called caches, to save frequently accessed or recently accessed results. Storing certain results in cache helps to improve Oracle BI performance. You can use the Refresh feature to make sure that your request bypasses saved information in the Oracle BI Presentation Services cache and is issued to the Oracle Business Intelligence Server for processing. NOTE: The Oracle BI Server maintains its own cache. This cache is separate from the Oracle BI Presentation Services cache.”

So, the ‘Refresh’ link only bypasses the OBI Presentation Services cache and the request is issued to the Oracle Business Intelligence Server for processing. It does not bypass OBI Server cache. If the BI Server cache is enabled the request hits the BI Server cache and you then see the old data.

It will not bypass the BI Server cache and will only satisfy your request (and fire the SQL to database) if there is no BI Server cached results.

The BI Server cache should be cleared to fix the issue.

A very good cache article by David Kwan

David Kwan posted a very nice OBIEE caching article – To Cache or Not to Cache That is The Question. I found it very interesting and worth reading. From myself, I’d like to add that in some situations using cache is necessary – for example – a relatively static Dashboard report (maybe updated monthly) from a very large table. I am positive that it’s not worth it for a BI server or a database to waster resources running it for every user over and over.

Users missing cache

If your users are missing cache, you might find the service request below helpful.

User1 one and User 2 have the same profile but User 2 is not hitting User 1’s cache even though the physical queries are exactly the same.

Customer had implemented data-level security. This resulted in different SQL for each user.

Cache hits are not based upon the physical queries.
They were using session variables in the connection pool login/password fields. Each different login has a different query, the issuer of the query is different, this results in the cache being specific to a user / session variable.
They modified the connection pool to use a shared login/password and this resulted in a cache hit.

OBIEE Tutorials

While browsing web, I’ve found the following question:

“The query logs do show that there is a cache hit but the report still takes more than 1.5 mins to display. Why ?”

Solution offered

Checking the nqquery.log when the cache is hit, it was found the time elapsed between running the query, and getting the results was only 34 seconds
2008/10/20 07:17:38
2008/10/20 07:18:12

There is a defect logged to address the fact that you might see differences in the following measures for the same query:
-Time (Analytics web > Administration > Manage Sessions > Time column
-TOTAL_TIME_SEC (Usage tracking parameter)
-Elapsed Time (in NqQuery.log)

Elapsed Time (in NqQuery.log):
This is the total clock time it takes from the point SAS receives request to
the moment it gets data from SAW or ODBC client, until the moment data leaves
Analytics Server. Response Time and Physical Query Response Time are both
included in this.
These time parameters in NQQuery.log file do not reflect the time data spent
travelling between Analytics Web and Analytics Server.

TOTAL_TIME_SEC (Usage tracking parameter):
The time in seconds that the Oracle BI Server spent working on the query
while the client waited for responses to its query requests.

Time (Analytics web > Administration > Manage Sessions > Time column

This defect is targeted to be fixed in the next main release

Also it was recommended to change the following NQSconfig.ini settings which were set very high to 100000.. The recommended values are as follows:

MAX_QUERY_PLAN_CACHE_ENTRIES = 1024; // default is 1024
MAX_DRILLDOWN_INFO_CACHE_ENTRIES = 1024; // default is 1024
MAX_DRILLDOWN_QUERY_CACHE_ENTRIES = 1024; // default is 1024