![]() Previous |
![]() Next |
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
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.
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.
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 toNO , Oracle OLAP does not cache the data even when you specify SESSION . In this case, specifying SESSION is the same as specifying NONE . |
(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".
When the variable data is aggregated using detail data from another variable, specifies that Oracle OLAP calculates the leaf data for the variable.
(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.
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".)
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.
To ensure that the aggregated values cannot be permanently committed to the Database, use SESSION.
Use STORE when you know either of the following is true which also ensures that the data that is calculated on the fly using the AGGREGATE function is not committed to the Database:
The users of the analytic workspace can only open it as read-only
You know that the users of the analytic workspace will not or cannot issue UPDATE and COMMIT statements.
Note: Use STORE with caution when it is likely that your users modify pre-computed data, and they access data that you have specified to be calculated on the fly using the AGGREGATE function. The problem is that any data that is calculated using the AGGREGATE function before the user's modification does not reflect the user's change unless the user made the change using an AGGREGATE function with the FORCECALC keyword or unless there is an $AGGREGATE_FORCECALC property on the variable being aggregated |
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.