Understanding Oracle BI Server Cache

To enable the Oracle BI Server Cache, the Cache needs to be activated within the Oracle Enterprise Manager:

Additionally, the NQSConfig file contains additional parameter to steer the entry and usage of the Oracle BI Server Cache:

If the Cache is enabled, the created Cache entries can be viewed on the hard disk, but also the BI Admin Tool. The Cache files are placed in:



These Cache entries hold the information about the logical SQL used to created the Cache and the returned result set for the query. The Cache Entries can also be viewed from the BI Admin Tool to provide further information about the respective Cache Entry and e.g. the last usage:


Some Reports/Analysis may not qualify for a creation of a Oracle BI Server Cache entry. This can also be seen in the Session log, with the following statement:

The logical query block fail to hits or seed the cache in subrequest level due to [[ only one subrequest ]]

In general, this is due to one of the following reasons given from the Official Documentation:

  • Noncacheable SQL element. If a SQL request contains CURRENT_TIMESTAMP, CURRENT_TIME, RAND, POPULATE, or a parameter marker, then it is not added to the cache.
  • Noncacheable table. Physical tables in the Oracle BI Server repository can be marked “noncacheable.” If a query references any noncacheable table, then the query results are not added to the cache.
  • 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 exception is query hits that are aggregate roll-up hits. These are added to the cache if the NQSConfig.INI parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to YES.
  • Result set is too big.This situation occurs when you exceed the size set in DATA_STORAGE_PATHS, or if you have rows in excess of the number set in MAX_ROWS_PER_CACHE_ENTRY. See Section A.3.2, “DATA_STORAGE_PATHS” and Section A.3.3, “MAX_ROWS_PER_CACHE_ENTRY” for more information.
  • Query is canceled. This can happen by explicit cancellation from Oracle BI Presentation Services or the Administration Tool, or implicitly through timeout.
  • Oracle BI Server is clustered. Queries that fall into the ‘cache seeding’ family are propagated throughout the cluster. Other queries continue to be stored locally. Therefore, even though a query might be put into the cache on Oracle BI Server node 1, it might not be on Oracle BI Server node 2.

Additionall one of the following cases may also prevent from entries being created within the Cache:

  • Caching is not enabled (CACHE=N in the NQSConfig.INI file)
  • The WHERE clause in the logical SQL is not semantically the same, or not a logical subset of a cached statement
  • the columns in the SELECT list not yet exist in the cached query, or they are not able to be calculated from them
  • It has not equivalent join conditions, so that the resultant joined table of any incoming query is not the same as (or a subset of) the cached results
  • If DISTINCT is used, the cached copy has to use this attribute as well
  • Aggregation levels are not compatible, since they either have to be the same or more aggregated than the cached query
  • No further aggregation (for example, RANK,MAX) can be used in the incoming query
    (sub request / sub Report)
  • Any ORDER BY clause has to use columns that are also in the cached SELECT list

As an example, the following (main) Report:

cache main report

Is based on the results of another Analysis, which also uses an Aggregation:

cache sub report

The main Report is executed, but no Cache Entry is created:

cache monitor

This can also be analyzed from the Session log for the SQL for this statement:

session log

Since, the Sub Report also contains an Aggregation, no Cache Entry will be created for the Report. This can be seen in the Session log (may be required to set log level to 3 or even 4 to see below message):

cache message

To allow the Oracle BI Server to create a Cache Entry, the Analysis needs to be re-written. E.g. the Sub Report needs to be removed and needs to be part of the (main) Report. In the above case, by creating the Aggregate in the (main) Report and by the “BY” clause within the formula to specify the Group element.

Once, a Cache Entry is created for one User, it may also be shared to fulfill a request from another User. This can be seen from the Oracle BI Admin Tool within the Cache Manager Screen:

cache usage

Sometimes Users are also mislead in terms of Cache usage since Oracle BI provides two cache systems:

And even if the Oracle BI Presentation Server Cache is not enabled (by default not enabled) the Oracle BI Presentation Server still has a Cursor Cache which can be viewed from the Session Monitor (http://localhost:9704/analytics/saw.dll?Sessions) and which can not be disabled and only purged manually from the Session Monitor “Cancel All Cursor” Button.


Understanding Oracle BI EE Presentation Server Cache(s)

Within the Oracle BI System, multiple components provide a caching mechanism to re-use previously executed Reports to improve query performance of subsequent requests. The following diagram provides an Overview of the main Architecture and main caching components:

Oracle BI Server Cacheing cropped

By default, the Oracle BI Presentation Server Cache is not actived. However, the Oracle BI Presentation Server additionally provides a Oracle BI Presentation Server Cache which can not be deactived. This “Cursor” Cache can (only) be viewed from the Session Monitor Administration Page http://localhost:9704/analytics/saw.dll?Sessions:

session monitor

Thus, even when the Oracle BI Presentation Server Cache is not actived, subsequent execution of the same Analysis maybe retrieved from the Oracle BI Presentation Server Cursor Cache which may often mislead the perception of cacheing.

For the first execution of an Analysis a new Cursor and Cursor Cache maybe created eventually:

cache monitor

A subsequent execution of the same Report will return instantly (even with no Presentation Server Cache configured) since the Report can be retrieved from the Presentation Server Cursor Cache. No additional Cursor will be created and only the time of execution will be updated for the previous Cursor.

cache monitor 2

As said before, the Cursor Cache can not be disabled and the Cursor Cache can only be purged manually from the Session Monitor. However, a Report/Analysis can be foreced to bypass the Presentation Server Cursor Cache (and Presentation Server Cache) by checking the bypass Presentation Server Cache option on the Advanced Tab of a Analysis.


The disk directory of the Cursor Cache is not documented by Oracle, but they are stored under the query cache folders of:



Which places a result set file for the respective Presentation Server Cursor Cache:


To active the regular Presentation Server Cache, the instanceconfig.xml needs to be edited. The instance config can typically found here (for 11g):


To active the Caching the following xml elements need to be added to the file:


After restarting the Presentation Server, the Cache Files will be created in the following directory:


Summary: Even with no Presentation Server Caching enabled, the Presentation Server Cursor Cache will cache result sets to fulfill subsequent Analysis requests from the Client.

Installing Oracle R on Database VM Image

Download the Oracle Pre-Built Database Developer VMs from OTN.image
Import the extracted VM into Virtual Box and bootup the Virtual Machine. After starting the VM, the following screen is presented witin Virtual Box:
check the Linux Version of the Downloaded Virtual Machine with below command:

# uname -r

For this VM it is: 4.1.12-61.1.27.el7uek.x86_64, thus: Oracle Enterprise Linux 7.

Install R (distribution) for Oracle R Enterprise

Afterwards, switch user to root (Password is “oracle”) and navigate to the yum Repository configuration:
using the vi (i command for – – inserting- – Mode and Esc for exiting the insert Mode, save with :wq) update the following sections within the repo file:



Install R with the following command
this automatically downloads and performs the installation:

Installing Oracle R Enterprise Server

Add the Oracle Home Lib to the Library Path environment variable:
This can also be added to the ~/.bashrc
As next step, download the Oracle R Enterprise software from OTN.
And store to a installation directory e.g. /u01/ORE_Inst_Dir
and unzip the files:
Start the installation by running the installer:

Installing Oracle R Client

Unzip the Client zip files:
switch to root user and install all unzipped files:
Start Oracle R Client with the ORE command and load the Oracle R libaries with library(ORE). When loading the R libaries, the following error is thrown:

Loading required package: OREembed
Error in dyn.load(file, DLLpath = DLLpath, …) :
   unable to load shared object ‘/u01/app/oracle/product/’:
   libpng12.so.0: cannot open shared object file: No such file or directory
Error: package ‘OREembed’ could not be loaded


Since, the file libpng12.so.0 is not available it needs to be installed:
[Thanks to the following article which features the same issue]

After installing the missing file, the ORE Libaries are loaded successfully within R:

Further references:


Official Oracle Documentation:


Creating single virtual domain for Siebel and OBIEE

If Siebel CRM and Oracle BI EE are running on two different Servers (which should be the case for most implementations), then the embedded Oracle BI screens may not function properly for all actions such as, opening the properties for List Formats, requiring a second click for editing within the formula editor of the segment designer due some issues with cross-site scripting. This behavior is also documented within the following SR: avoid UI navigation issues associated to Siebel Marketing – OpenUI integrated to OBI (2008422.1) and within the Official documentation of the Installation guide: https://docs.oracle.com/cd/E23943_01/bi.1111/e16364/siebel_crm_integration.htm#BIEIT3307

Since these various issue are caused by some cross-site scripting, the workaround is to pretend to the client that Siebel and Oracle BI are running on the same host, hence creating a single virtual domain for Siebel and OBIEE.

This can be achieved by configuring a proxy reverse setting on the HTTP Web Server where Siebel web extensions are running. This proxy reverse configuration will exchange the hostname of OBIEE e.g. oraclebiserver.com with the hostname of the Siebel host e.g. siebelserver.com

Within the support note of the SR (2008422.1) OHS is used for this proxy reverse configuration. The following diagram shows the updated request flow for the single virtual domain:

A short step-by-step guide for the Workaround using OHS can be found here.

Tip: Using Row-wise variables and Initialization Blocks


Tip: Using Row-wise variables and Initialization Blocks

It is often required to use row-wise variables (array’s) to store a list of items. E.g. For the security/visibility it may be required to store the Organization a User has access to. This can be achieved using row-wise variables and Initialization Blocks from OBIEE.

SELECT ‘ActiveOrgs’, OrganizationTable.Name
FROM OrganizationTable
WHERE OrganizationTable.Use = ‘:USER’

This will initialize the Variable ActiveOrgs with the results of the Initialization Blocks. This variable can than be used at other places to filter the respective Organizations.


detecting Report (Analysis) Orphans in OBIEE

A Dashboard typically represent a combination of different Reports to provide summary information about a Business Process or Sub-Process e.g. with HR: A Recruiting Dashboard may contain Reports about hiring sources and the respective Time to Hire or a Dashboard provides Reports about certain balance information E.g. current capital in Fixed Assets. Over time these Reports may be replaced or exchanged and many reports may only be created for Testing purposes. The management of all these Reports becomes very difficult and the IT Department may want to understand the structure of the Web Catalog in terms of Report usage on Dashboard and Report Orphans. The Oracle BI Sample App provides a example of how this can be implemented under the Lifecycle and Admin section:



Pre-built Reports exist that Analyse the structure of the Webcat to determine unused Analysis:


These samples require to execute scripts on the BI instance, which is also documented within the Sample App Image under /app/oracle/SAScripts/ReferenceChecker.

BI Publisher now available with OAC

Until today, Oracle BI Publisher was only available as a (SaaS) bundle with Oracle Fusion Applications Cloud and OTBI, see also a previous Article. However, with Oracle Analytics Cloud, Oracle BI Publisher is now also available as a “first degree” Platform Cloud Service (PaaS) giving more flexibility especially in terms of Data Sources. This allows to create highly formatted Reports/Documents such as Invoices, Purchase Orders, Dunning Letters without the context of Oracle Fusion Applications Cloud.

See the full announcement here.

Issue when using Report prompts within OTBI Answers and OTBI Fusion

A strange Issue exists with OTBI in terms of Report prompts. If a User adds a prompt directly within the Report, the prompts appear correctly within BI Answers. However, if the same Report is opened from within Oracle Cloud, the prompts are not shown to the User.

Prompts added to Report:


Prompts appearing in BI Answers of OTBI when running (opening) the Report:


However, when opening the same Report from Fusion (OTBI embedded) the Report executes without showing the prompts, even though the prompt has been specified as “required”:


When clicking on the “Edit” button of the Fusion Catalogue Browser, the following warning message is being displayed:


The standard provides prompted Analysis usually of Type “Dashboard”, hence this Issue is not applying for standard content. E.g. the Analysis on Department details also provides filters as Dashboard prompts: