Previous
Previous
 
Next
Next


CACHE

Within an aggregation specification, a CACHE statement tells Oracle OLAP whether to cache or store the calculated data, whether to populate leaf or detail data when the variable data is aggregated using detail data from another variable, and whether to cache NA values when a summary values calculates to NA.


Note:

The CACHE statement is only one factor that determines whether variable data that has been aggregated on-the-fly using the AGGREGATE function is stored or cached. See "How Oracle OLAP Determines Whether to Store or Cache Aggregated Data".

Syntax

CACHE {NOSTORE|NONE|STORE|SESSION|DEFAULT} [LEAF|NOLEAF] [NA|NONA]

Parameters

NONE
NOSTORE

For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP calculates the data each time the AGGREGATE function executes. When you specify either of these keywords, Oracle OLAP does not store or cache the data calculated by the AGGREGATE function.

STORE

For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP stores data calculated by the AGGREGATE function in the variable in the Database. When you specify this option, the results of the aggregation are permanently stored in the variable when the analytic workspace is updated and committed.

SESSION

For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP caches data calculated by the AGGREGATE function in the session cache (see "What is an Oracle OLAP Session Cache?"). When you specify this option, the results of the aggregation are ignored during updates and commits and are discarded after the session.


Note:

When SESSCACHE is set to NO, Oracle OLAP does not cache the data even when you specify SESSION. In this case, specifying SESSION is the same as specifying NONE.

DEFAULT

(Default) For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP uses the value of the VARCACHE option to determine what to do with data that is calculated by the AGGREGATE function. See "How Oracle OLAP Determines Whether to Store or Cache Aggregated Data".

LEAF

When the variable data is aggregated using detail data from another variable, specifies that Oracle OLAP calculates the leaf data for the variable.

NOLEAF

(Default) When the variable data is aggregated using detail data from another variable, specifies that Oracle OLAP does not calculate the leaf data for the variable.

NA

For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP places any NA values that are the results of the execution of the AGGREGATE function in the Oracle OLAP session cache. In this case, when there is a variable has an $NATRIGGER property with an AGGREGATE function as its expression, Oracle OLAP does not recalculate the values for the variable. (For more information on the caching NA values, see "How Oracle OLAP Determines Whether to Store or Cache Results of $NATRIGGER".)

NONA

For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP does not cache any NA values that are the results of the execution of the AGGREGATE function. In this case, when a variable has an $NATRIGGER property with an AGGREGATE function as its expression, Oracle OLAP recalculates the values for the variable.

Usage Notes

When to Use NOSTORE

Use NOSTORE when you know that your users are likely to modify pre-computed data, and you want any data that calculated by the AGGREGATE function to consistent with any of those users' changes.

In other words, suppose a user makes a change to detail-level data, such as sales figures for three stores, which are in a geography dimension. The geography dimension rolls up data from stores to cities to states to regions to countries. In other words, there are five levels in the geography dimension's hierarchy. Now suppose that users tend to access data only at the store level (your detail data), the regions level, and the countries level. Those are the levels for which you roll up sales data and commit it to the Database. Because users do not access data at the city and state level, you specify that the data cells in those two levels are calculated on the fly. When users modify the store-level data and then access city data, the city data are calculated every time that a user requests it. Therefore, any changes that a user makes to the store-level details accurately rollup to the city and state level every time that user accesses a data cell in the city or state level. (However, this is not true of the data in the region and country levels, because those cells store pre-computed data.)

When to Use STORE or SESSION

The advantage to using STORE or SESSION is that it improves query performance. For example, suppose your users use a Table tool to look at a variable's data and an individual user requests the same data cells several times in the same session. When you use the default of NOSTORE, then any data that is not aggregated using the AGGREGATE command has to be calculated every time the user requests that data even if you do not use the FORECALC keyword in the AGGREGATE function. On the other hand, when you use STORE or SESSION, then any given cell of data is calculated only once because it is available in either the variable or the cache for the entire session. Therefore, the next time a user requests that data cell, the data is returned from the variable or the cache instead of being calculated on the fly, which results in faster query time for the user.

Frequently you do not want the data that is calculated using the AGGREGATE function to be stored permanently in the Database since that would defeat the purpose of calculating data on the fly.

Examples

For examples of using a CACHE statement in an aggregation specification, see Example: Using a CACHE Statement in an Aggregation Specification and Example: Populating All Levels of a Hierarchy Except the Detail Level.