Oracle® Business Intelligence Server Administration Guide > Query Caching in the Oracle BI Server > Purging and Maintaining Cache Using ODBC Procedures >

Storing and Purging Cache for SAP/BW Data Sources

In Microsoft Analysis Services, member caption name is the same as member unique name. However, in SAP/BW data sources, member caption name is different from member unique name. Therefore, the Oracle BI Server maintains a cache subsystem for SAP/BW member unique names. This subsystem is turned off by default. For configuration information, refer to the topic about the MDX Member Name Cache Section of the NQSConfig.INI file in Oracle Business Intelligence Infrastructure Installation and Configuration Guide.

When a query is received for member unique name, the subsystem checks the cache to determine whether cache exists for this query. If cache exists, the record for the cached unique name is returned. If there is no cache that matches the query, the subsystem sends a probing query to SAP/BW.

The probing query will be logged when the log level is equal or greater than 2. The status of the subsystem, such as if the subsystem is enabled and events such as start and shutdown events, are also written to the server log.

CAUTION:  With each increased logging level, performance is impacted. Use caution when increasing the log level for users.

Purging Cache for SAP/BW Data Sources

Only Oracle BI Administrators have privileges to run ODBC purge procedures. It is the responsibility of the Oracle BI Administrator to maintain the cache. Therefore, the Oracle BI Administrator should be aware of the following issues:

  • The size of multidimensional cache entries can grow very large. Therefore, a limit on the size of each member set has been established in the MDX_MEMBER_CACHE section of the NQSConfig.INI file.
  • The format of persisted cache might not be consistent after an upgrade. Therefore, the Oracle BI Administrator should purge all cache before a software upgrade.
  • The cache will be populated the first time the query runs. The Oracle BI Administrator should arrange to populate the cache during off-peak hours, to minimize performance impact.

NOTE:  In the Oracle BI Administration Tool, the Oracle BI Administrator can purge cache for an individual cube table by right-clicking the cube table, and then selecting Purge Member Cache. This must be performed in online mode and by a user with Oracle BI Administrator privileges.

The following purge procedures are specific to SAP/BW data sources:

  • SAPurgeALLMCNCache. Purges all SAP/BW cache entries.

    The following shows the syntax of this procedure:

    SAPurgeALLIMCNCache ()

  • SAPurgeMCNCacheByCube. Purges all cache entries associated with the specified physical cube. The database name and cube name are the external names of the repository objects. The following shows the syntax of this procedure:

    SAPurgeMCNCacheByCube( 'DBName', 'CubeName')

The following messages will be returned.

Return Code
Return Message


SAPurgeALLMCNCache returns successfully.


SAPurgeMCNCacheByCube returns successfully.


The database specified does not exist.

NOTE:  If the database and physical cube are both wrong, this result code will be returned.


The physical cube specified does not exist.

Oracle® Business Intelligence Server Administration Guide Copyright © 2007, Oracle. All rights reserved.