OBIS 10G/11G - BI Server (Logical Query|Result) Cache

Bi Server Architecture


Query caching is a persistent cache mechanism. When you ask for data with a query, the data are retrieved from the cache instead to ask again the data source.

OBIEE has two query caches:

The following article talks about the BI Server Query Cache.

The query cache consists of :

This is an:

  • on-demand,
  • shareable
  • and secure

cache that can supports:

  • subsets,
  • aggregations
  • and derivations.

The cached results act much like precomputed aggregates in that they can dramatically accelerate query performance.

All security attributes remain in force for the cached results, so users can access only the data they are authorized to view.

To ensure the information stored in the cache is current with the underlying databases, the BI Server administrator can set up cache purging rules.

The query cache is a data set cache managed by the BI Server.

The query cache is not shared across clustered servers (except iBots).

Caching occurs by default at the subrequest level, which results in multiple cache entries for some SQL statements. Caching subrequests improves performance and the cache hit ratio, especially for queries that combine real-time and historical data. To disable subrequest caching, set OBIEE 10G/11G - Logical Query Result Cache Parameters to YES.


Purge it

When you have some trouble, you may want to purge it or in a development environment simply to disable it.

Why caching doesn't work

Be careful, the server cache has several reasons to not cache a logical query.

Cache Location

  • 11g: [middlware_home]/instances/instance1/bifoundation/OracleBIServerComponent/coreapplication_obis1/cache
  • 10g: OracleBIData\cache

Cache Seeding

Queries (dashboards and reports for BI Presentation Service) can be scheduled with Delivers and the results cached to make information instantly accessible.

With the help of an agent, you can seed the cache. (It seems that you need to have a BIAdmin privilege to do it).

Obiee Agent Cache Seeding


You can configure it with the help of global parameters and of BI Server parameters.

Cache Sharing Across Users

If shared logon has been enabled for a particular connection pool, then the cache can be shared across users and does not need to be seeded for each user. If shared logon has not been enabled and a user-specific database login is used, then each user generates their own cache entries.

Shared Cache (Global Cache)

In a clustered environment, Oracle BI Servers can be configured to access a shared cache called the global cache.

This global cache resides on a shared file system storage device and stores:

  • purging events,
  • seeding events (often generated by agents),
  • and result sets that are associated with seeding events.

The seeding and purging events are sorted by time and stored on the shared storage as a logical event queue. Individual Oracle BI Server nodes push to and pull from the logical event queue. Each Oracle BI Server still maintains its own local query cache for regular queries.

Data source


Typically, XML data sources are updated frequently and in real time. Setting a refresh interval for XML data sources is analogous to setting cache persistence for database tables. The refresh interval is a time interval after which the XML data sources are to be queried again directly, rather than using results in cache. This refresh interval is specified on the XML tab of the Connection Pool dialog box.

The default interval setting is Infinite, meaning that the XML data source is not automatically refreshed.

The refresh interval setting specifies the time interval after which the Oracle BI Server XML Gateway connection will be refreshed.

  • For URLs that begin with http:// or https://, the gateway will refresh when it detects that the interval has expired.
  • For URLs that reside on a local or network drive, the gateway will refresh when the interval has expired and the system detects that the URLs have been modified.

Documentation / Reference

Discover More
Obiee Initialization Block Caching
OBIEE - All the caches

in OBIEE. Obiee have two main caches mechanisms : The query cache (Bi Server cache) which is a data set cache The presentation server cache (Presentation Server Cache) which is an ODBC data set cache...
Obiee Nqsgetleveldillability
OBIEE - BI Server (JDBC|ODBC) Driver Functions

The Bi Server interface functions permit: to get data dictionary information. to manage the query cache to set a repository session variable You can call them : through a client that provides...
Bi Server Architecture With Client
OBIEE - BI Server (OBIS|nqsserver|nqs)

OBI Server is also known as: OBIS (Oracle Business Intelligence Server) or nqsserver (The process name) OBI Server is principally a query and analysis server software: that can integrate data...
Obiee Database Cast Feature
OBIEE - Cast as date - Which date format to use ?

When you use the cast function to transform a character data type as a date, you may struggle with the date format that you have to give in a formula column because it depends of the configuration. ...
Obiee Connection Pool Shared Logon
OBIEE - How to define one different database credential for each user (the shared logon) ?

For system architecture reasons, you may want to have one database credential by user. Example : When one schema (of one user) is the logical entity of one company, you may decide to have the same...
Obiee Security User Indexcol
OBIEE - IndexCol Function - Handle hierarchy Level security

The INDEXCOL function helps to select the appropriate column and behave as the IF THEN structure. It can then use external information to return the appropriate column for the logged-in user to see. ...
Bi Server Architecture
OBIEE - Query Processing (What happens when you issue an Logical SQL Request (through Answers for instance) ?

Query processing in OBIEE Logical SQL Request Logical Request Generation (Logical SQL Request with security) Query compilation Cache service. The server will check to see if this request can...
Obiee11g System Session Variable
OBIEE - System session variables (reserved variables)

System session variables are session variables that the Oracle BI Server and Oracle BI Presentation Services use for specific purposes. System session variables have reserved names, that cannot be used...
Obi Edition
OBIEE - Usage Tracking S_NQ_ACCT Table

The table S_NQ_ACCT is a database table that holds all data log of the usage tracking functionality. Group Column Description BI Server COMPILE_TIME_SEC The time in seconds required to compile...
Obi Edition
OBIEE - Virtual Private Database

The virtual Private Database check box in the database object identifies the physical database source as a virtual private database (VPD). When a VPD is used, returned data results are contingent on the...

Share this page:
Follow us:
Task Runner