Developer-controlled BI Cache Settings (On-Premise and SaaS)

Introduction

As an Oracle Business Intelligence (BI) Developer, you may not always have direct access to the BI Common Semantic Model (RPD) or the underlining Operating System. This can make it difficult or near impossible to change the global cache settings. That said, as a developer, you can still change the cache settings at the analysis request level. This can be done from the Advanced tab of the analysis request.

Main Article

If your dashboard is not refreshing and you feel the problem may be cache related, below are four suggestions that may help to resolve your cache issue. It is recommended to perform these changes individually, and in the order below.  Performing each step independently will assist in identifying where the cache is occurring. This may be beneficial if you are experiencing caching on multiple Dashboards and need to repeat the solution.

1) Check Bypass Oracle BI Presentation Services Cache

2) Change Partial Update Settings

3) Add an Advanced SQL Clause

4) Clear All Presentation Services Cache

The article below outlines these steps in further detail.

1. Check Bypass Oracle BI Presentation Services Cache

When users access Answers to run queries, the Presentation Services caches the results of the queries. Presentation Services use the request key and the logical SQL string to determine if subsequent queries will use cached results. If the cache can be shared, then subsequent queries are not stored.

There may be instances where you want to bypass the Presentation Services Cache and force the analysis request to run from scratch. To do this, begin by checking the “Bypass Oracle BI Presentation Services Cache” checkbox from the Advanced tab of the analysis request. To apply this setting, you must click the “Apply SQL” button at the bottom of the tab and then click Save.

1

2

 

2. Change Partial Update Settings

Partial Update defaults to Affected Views, which means the SQL and HTML code are only updated by an event such as drilling or sorting. The advantage of having Partial Update set to Affect Views is that performance may be enhanced since not all views are redrawn each time. That said, at times this may be undesirable; especially if you are noticing some views with stale or incomplete data. If you are experiencing this, changing the dropdown box to “Entire Report” will force all views to refresh every time the analysis request is accessed. To apply this setting, you must click the “Apply SQL” button at the bottom of the tab and then click Save.

3

2

3. Add an Advanced SQL Clause

If checking “Bypass Oracle BI Presentation Services Cache” and changing “Partial Update” to “Entire Report” does not resolve your refresh issue, you may need to bypass the BI Server Cache. The BI Server saves the results of the query in files and reuses the results when a similar query is requested. Therefore, if the SQL has not changed, Oracle BI will choose not to re-run the query and the results may not reflect the most recent ETL updates. To Bypass the BI Server Cache, type “SET VARIABLE DISABLE_CACHE_HIT=1” in the Prefix section of the “Advanced SQL Clause”, click the “Apply SQL” button at the bottom of the tab and then click Save.

SET VARIABLE   DISABLE_CACHE_HIT=1;

4

 

4. Clear All Presentation Services Cache

If you are a developer with Administration privileges, you can also try clearing all Presentation Server Cache. This is accessed through the Administration Link (top right from Analytics) and then clicking on Manage Sessions. Once in the “Cursor Cache” section, click on “Close All Cursors”. This will clear all Presentation Server Cache. It is important to note that when clicking on “Close All Cursors” you are affecting all current active user sessions. This may have an adverse effect on reports or dashboards that are relying on cached results for improved performance.

5

 

6

Further Reading

Managing Performance Tuning and Query Caching

Summary

This article describes four areas available in the Presentation to manage BI cache. The first three suggestions (below) affect the cache on a per analysis request basis and are changed on the Advanced tab.

1) Check Bypass Oracle BI Presentation Services Cache

2) Change Partial Update Settings

3) Add an Advanced SQL Clause

As with all code changes, it is advised that you make these changes in a test / development environment and that you archive the request before making the change. This is particularly important when making changes to the advance tab and clicking on the Apply SQL button. Once this is done, Oracle BI creates a new analysis based on the SQL statement that you have added or modified. Therefore, you lose all views and formatting you have previously created for the analysis. The XML code is also modified for the new analysis. Thus, it is always advisable to make sure you have a backup you can restore from.
The fourth suggestion, available only to those with Administrator privileges, clears allpresentation services cache for all open requests.

4) Clear All Presentation Services Cache

This option should be used as a last resort as it affects all current active user sessions and may decrease the performance of reports or dashboards relying on cached results.

 

Add Your Comment