To manage the changes in the underlying databases and to monitor cache entries, you must develop a cache management strategy.
You need a process to invalidate cache entries when the data in the underlying tables that compose the cache entry have changed, and a process to monitor, identify, and remove any undesirable cache entries.
This section contains the following topics:
The choice of a cache management strategy depends on the volatility of the data in the underlying databases and the predictability of the changes that cause this volatility.
It also depends on the number and types of queries that comprise your cache and the usage those queries receive. This section provides an overview of the various approaches to cache management.
You can disable caching for the entire system to stop all new cache entries and stop any new queries from using the existing cache. Disabling caching lets you enable it at a later time without losing any entries that are stored in the cache.
Temporarily disabling caching is a useful strategy in situations where you might suspect having stale cache entries, but want to verify if they are actually stale before purging those entries or the entire cache. If you find that the data stored in the cache is still relevant, or after you have safely purged problem entries, then you can safely enable the cache. If necessary, purge the entire cache or the cache that is associated with a particular business model before enabling the cache again.
See Using Fusion Middleware Control to Enable and Disable Query Caching for more information.
You can set a cacheable attribute for each physical table, enabling you to specify whether queries for that table are added to the cache to answer future queries.
If you enable caching for a table, then any query involving the table is added to the cache. All tables are cacheable by default, but some tables might not be good candidates to include in the cache unless you use the Cache Persistence Time settings. For example, suppose that you have a table that stores stock ticker data that is updated every minute. You could use the Cache Persistence Time settings to purge the entries for that table every 59 seconds.
You can also use the Cache persistence time field to specify how long the entries for this table are stored in the query cache. This is useful for data sources that are updated frequently.
To set the caching attributes for a specific physical table:
In the Administration Tool, in the Physical layer, double-click the physical table.
In the Physical Table properties dialog, in the General tab, make one of the following selections:
To enable caching, select Cacheable.
To prevent a table from being cached, deselect Cacheable.
To set a cache expiration time, specify a Cache persistence time and specify a unit of measure (days, hours, minutes, or seconds). If you do not want cache entries to automatically expire, select Cache never expires.
Click OK.
Oracle BI Server event polling tables store information about updates in the underlying databases.
An application (such as one that loads data into a data mart) could be configured to add rows to an event polling table each time a database table is updated. The Oracle BI Server polls this table at set intervals and invalidates any cache entries corresponding to the updated tables. Event polling tables can be the sole method of cache management, or they can be used with other cache management schemes. Event tables offer less flexibility about choice of cache entries and the timing of purges. See Setting Up Event Polling Tables on the Physical Databases for more information about event polling tables.
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:
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' );
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.
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' );
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''.
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 Managing the Oracle BI Presentation Services Cache Settings for more information about the Presentation Services query cache.
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. The table below shows examples of result records.
| Result Code | Result Message |
|---|---|
|
1 |
SAPurgeCacheByDatabase returns successfully. |
|
59115 |
Operation not performed because caching is not enabled. |
|
59116 |
The database specified does not exist. |
|
59117 |
The table specified does not exist. |
Due to differences in naming conventions between Microsoft Analysis Services and SAP/BW data sources, there is a cache subsystem for storing member unique names.
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 Configuration File 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.
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.
Caution:
With each increased logging level, performance is impacted. Use caution when increasing the log level for users.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, purge all cache before a software upgrade.
The cache is populated the first time that the query runs. Arrange to populate the cache during off-peak hours, to minimize performance impact.
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.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')
The next table describes the messages that are returned.
| Return Code | Return Message |
|---|---|
|
1 |
SAPurgeALLMCNCache returns successfully. |
|
1 |
SAPurgeMCNCacheByCube returns successfully. |
|
59116 |
The database specified does not exist. If the database and physical cube are both wrong, then this result code is returned. |
|
85025 |
The physical cube specified does not exist. |
Only users with administrative privileges can run ODBC purge procedures.
When you modify Oracle Business Intelligence repositories, the changes can have implications for entries that are stored in the cache. For example, if you change the definition of a physical object or a dynamic repository variable, cache entries that reference that object or variable might no longer be valid. These changes might result in the need to purge the cache. There are three scenarios to be aware of: when the changes occur in online mode, when they occur in offline mode, and when you are switching between repositories.
Online Mode
When you modify an Oracle Business Intelligence repository in online mode, any changes that you make that affect cache entries automatically result in a purge of all cache entries that reference the changed objects. The purge occurs when you check in the changes. For example, if you delete a physical table from a repository, then all cache entries that reference that table are purged upon check in. Any changes made to a business model in the Business Model and Mapping layer purge all cache entries for that business model.
Offline Mode
When you modify an Oracle Business Intelligence repository in offline mode, you might make changes that affect queries that are stored in the cache and render those cached results obsolete. Because the repository is not loaded by the server during offline mode edits, the server has no way of determining if the changes made affect any cached entries. The server therefore does not automatically purge the cache after offline changes. If you do not purge the cache, then there might be invalid entries when the repository is next loaded. Unless you are sure that there are no entries in the cache that are affected by your offline changes, then purge the cache for any business model that you have modified.
Switching Between Repositories
If you intend to remove a repository from the configuration of the Oracle BI Server, then ensure that you purge the cache of all cache entries that reference the repository. Failure to do so results in a corrupted cache. See Purging Cache in the Administration Tool for more information.
Changes to Dynamic Repository Variables
The values of dynamic repository variables are refreshed by data that is returned from queries. When you define a dynamic repository variable, you create an initialization block or use a preexisting one that contains a SQL query. You also configure a schedule for the Oracle BI Server to follow to execute the query and periodically refresh the value of the variable.
If the value of a dynamic repository variable changes, then any BI Server cache entry which uses this variable in a column becomes stale, and a new cache entry is generated when data in that entry is needed again. The old cache entry is not removed immediately, but remains until it is cleaned through the usual caching mechanism.