Purging and Maintaining Cache Using ODBC Procedures

The Oracle BI Server provides ODBC-extension functions for purging cache entries. Some of these functions are particularly useful for embedding in an Extract, Transform, and Load (ETL) task. For example, after a nightly ETL is performed, all Oracle BI Server cache entries can be purged. If only the fact table was modified, then only cache related to that table can be purged. In some cases, you might need to purge the cache entries associated with a specific database.

Only administrators have the right to purge cache. Therefore, scripts that call these ODBC-extension functions must run under credentials with administrator privileges. The following ODBC functions affect cache entries that are associated with the repository specified by the ODBC connection:

Monitoring and Managing the Cache

Managing Performance Tuning and Query Caching 7-17

■ SAPurgeCacheByQuery. Purges cache entries that exactly match a specified query.

For example, using the following query, you would have one or more query cache entries that retrieve the names of all employees earning more than $100,000:

SELECT lastname, firstname FROM employee WHERE salary > 100000;

The following call purges the cache entries that are associated with this query:

Call SAPurgeCacheByQuery('SELECT lastname, firstname FROM employee WHERE salary > 100000' );

■ SAPurgeCacheByTable. Purges all cache entries that are associated with a

specified physical table name (fully qualified) for the repository to which the client has connected.

This function takes up to four parameters that represent the four components (database, catalog, schema, and table name proper) of a fully qualified physical table name. For example, you might have a table with the fully qualified name of

DBName.CatName.SchName.TabName. To purge the cache entries that are associated with this table in the physical layer of the Oracle Business Intelligence repository, run the following call in a script:

Call SAPurgeCacheByTable( 'DBName', 'CatName', 'SchName', 'TabName' );

■ SAPurgeAllCache. Purges all cache entries. The following is an example of this

call:

Call SAPurgeAllCache();

■ SAPurgeCacheByDatabase. Purges all cache entries associated with a specific

physical database name. A record is returned when any of the ODBC procedures to purge the cache are called. This function takes one parameter that represents the physical database name, and the parameter cannot be null. The following shows the syntax of this call:

Call SAPurgeCacheByDatabase( 'DBName' );

7.6.2.1 About ODBC Procedure Syntax

If there is a single quotation mark within the string argument of a procedure, then you must use another single quotation mark to escape it. For example:

Call SAPurgeCacheByQuery('SELECT TOPN("- Currency"."Markdown %", 10) saw_0, "XX Line"."Order No" saw_1, "- Bill-To Site"."Customer Name" saw_2, "- Currency"."Net USD" saw_3, "- Currency"."Markdown USD" saw_4, "- Currency"."Markdown %" saw_5 FROM "Apps 11i - XX Lines" WHERE

("XX Line"."Open Flag" = ''Y'') AND ("Operating Unit"."Group Name" = ''Group'')

AND ("- Currency"."Net USD" >= 10000) ORDER BY saw_0');

The line in bold highlights the extra single quotation marks that are used as escape characters for the items ''Y'' and ''Group''.

Note: Wildcards are not supported by the Oracle BI Server for this function. In addition, DBName and TabName cannot be null. If either one is null, then an error message is displayed.

Monitoring and Managing the Cache

7.6.2.2 About Sharing the Presentation Services Query Cache

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

■ SAGetSharedRequestKey: An ODBC procedure that takes a logical SQL

statement from Presentation Services and returns a request key value. The following shows the syntax of this procedure:

SAGetSharedRequestKey('sql-string-literal')

The value of the request key is affected by the following factors:

■ Whether the Virtual Private Database option has been selected in the repository

physical database object

■ Whether any session variables have been marked as Security Sensitive in the

repository

Presentation Services takes security sensitive variable values into consideration when computing the request key for logical requests against database objects marked as Virtual Private Databases.

See Section 7.9, "Managing the Oracle BI Presentation Services Cache Settings" for more information about the Presentation Services query cache.

7.6.2.3 About Result Records

A result record is returned after you issue a purge cache command. The result record contains two columns. The first column is a result code and the second column is a short message that describes the result of the purge operation. Table 7–1 shows examples of result records.

7.6.2.4 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, see the topic about the MDX Member Name Cache Section in Appendix A, "NQSConfig.INI File Configuration Settings."

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, then the record for the cached unique name is returned. If there is no cache that matches the query, then the subsystem sends a probing query to SAP/BW.

Table 7–1 Query Result Codes

Result Code Result Message

1 SAPurgeCacheByDatabase returns successfully.

59115 Operation not performed because caching is not enabled.

59116 The database specified does not exist.

Monitoring and Managing the Cache

Managing Performance Tuning and Query Caching 7-19

The probing query is 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.

Be aware of the following cache purge 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,

you should purge all cache before a software upgrade.

■ The cache is populated the first time that the query runs. You should arrange to

populate the cache during off-peak hours, to minimize performance impact.

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 that are 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')

Table 7–2 describes the messages that are returned.

Only users with administrative privileges can run ODBC purge procedures.

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

Note: In the Administration Tool, you 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 by a user with administrator privileges.

Table 7–2 SAP Purge Cache Return Codes and Messages

Return Code Return Message

1 SAPurgeALLMCNCache returns successfully.

1 SAPurgeMCNCacheByCube returns successfully.

59116 The database specified does not exist.

Note: If the database and physical cube are both wrong, then this result code is returned.

Monitoring and Managing the Cache