Rules files for building aggregate storage outlines must define only outline properties and field types that apply to aggregate storage outlines. See Table 123, Outline Differences Between Aggregate Storage and Block Storage.

After converting a block storage outline to aggregate storage, update the rules files by associating them to the aggregate storage version of the outline. See “Associating an Outline with an Editor” in the Oracle Essbase Administration Services Online Help.

Data sources for modifying aggregate storage outlines should not include field values that apply only to block storage outlines. Table 136 lists the member codes that are recognized in dimension build data sources as properties for members of aggregate storage database outlines. Any other consolidation code is ignored and + (Add) is assumed. See Using the Data Source to Work with Member Properties.

Currency name and currency category field types are not supported for aggregate storage outlines.

In aggregate storage outlines, formulas must be specified in the same format as MDX numeric value expressions. See Developing Formulas on Aggregate Storage Outlines.

To build shared members in an aggregate storage outline, select the autoconfigure setting in the Dimension Build Settings dialog box in Administration Services, and then make sure that Essbase is set up to allow sharing (clear “Do Not Share” in the Dimension Build Settings dialog box in Administration Services or omit the “Never allow Data Sharing” property in the rules file). When autoconfigure is selected and sharing is enabled, Essbase automatically creates duplicate members under a new parent as shared members. See Building Shared Members by Using a Rules File.

Using the import database data MaxL statement to load data values from a single data source does not involve the aggregate storage data load buffer.

If you use multiple import database data MaxL statements to load data values to aggregate storage databases, you can significantly improve performance by loading values to a temporary data load buffer first, with a final write to storage after all data sources have been read.

In the aggregate storage data load buffer, Essbase sorts and commits the values after all data sources have been read. If multiple (or duplicate) records are encountered for any specific data cell, the values are accumulated (see Resolving Cell Conflicts). Essbase then stores the accumulated values—replacing, adding to, or subtracting from existing data values in the database. Using the aggregate storage data load buffer can significantly improve overall data load performance.

While the data load buffer exists in memory, you cannot build aggregations or merge slices, because these operations are resource-intensive. You can, however, load data to other data load buffers, and perform queries and other operations on the database. There might be a brief wait for queries, until the full data set is committed to the database and aggregations are created.

The data load buffer exists in memory until the buffer contents are committed to the database or the application is restarted, at which time the buffer is destroyed. Even if the commit operation fails, the buffer is destroyed and the data is not loaded into the database. (You can manually destroy a data load buffer by using the alter database MaxL statement. See the Oracle Essbase Technical Reference.)

The following incremental data load example provides optimal performance when new data values do not intersect with existing values:

When performing an incremental data load, Essbase uses the aggregate storage cache for sorting data. You can control how much of the cache a data load buffer can use by specifying the percentage (between .01 and 1.0 inclusive; only two digits after the decimal point are significant—for example, 0.029 is interpreted as 0.02). By default, the resource usage of a data load buffer is set to 1.0, and the total resource usage of all data load buffers created on a database cannot exceed 1.0. For example, if a buffer of size 0.9 exists, you cannot create another buffer of a size greater than 0.1.

To set the share of resources the buffer is allowed to use, use the alter database MaxL statement with the resource_usage grammar.

For example, to set the resource_usage to 50% of the total cache, use this statement:

alter database AsoSamp.Sample
   initialize load_buffer with buffer_id 1
   resource_usage .5;

When loading data incrementally, you can specify how missing and zero values in the source data are treated when loading the data into the data load buffer.

For resolving cell conflicts for duplicate cells, you can specify whether to use the last cell loaded into the load buffer.

The data load buffer properties:

If you use multiple properties in the command and any conflict, the last property listed takes precedence.

To set data load buffer properties, use the alter database MaxL statement with the property grammar.

For example:

alter database AsoSamp.Sample
   initialize load_buffer with buffer_id 1
   property ignore_missing_values, ignore_zero_values;

In Administration Services Console, you can set options to ignore missing values and zero values, and to aggregate duplicate cells by using the value of the last cell loaded, on the Data Load dialog box. See “Data Load Dialog Box” in the Oracle Essbase Administration Services Online Help.

By default, when cells with identical keys are loaded into the same data load buffer, Essbase resolves the cell conflict by adding the values together.

To create a data load buffer that combines duplicate cells by accepting the value of the cell that was loaded last into the load buffer, use the alter database MaxL statement with the aggregate_use_last grammar.

For example:

alter database AsoSamp.Sample
   initialize load_buffer with buffer_id 1
   property aggregate_use_last;

Multiple data load buffers can exist on an aggregate storage database. To save time, you can load data into multiple data load buffers at the same time.

Although only one data load commit operation on a database can be active at any time, you can commit multiple data load buffers in the same commit operation, which is faster than committing buffers individually.

To load data into multiple data load buffers at the same time, use separate MaxL Shell sessions. For example, in one MaxL Shell session, load data into a buffer with an ID of 1:

alter database AsoSamp.Sample
   initialize load_buffer with buffer_id 1 resource_usage 0.5;
import database AsoSamp.Sample data
   from data_file "dataload1.txt"
   to load_buffer with buffer_id 1
   on error abort;

Simultaneously, in another MaxL Shell session, load data into a buffer with an ID of 2:

alter database AsoSamp.Sample
   initialize load_buffer with buffer_id 2 resource_usage 0.5;
import database AsoSamp.Sample data
   from data_file "dataload2.txt"
   to load_buffer with buffer_id 2
   on error abort;

When the data is fully loaded into the data load buffers, use one MaxL statement to commit the contents of both buffers into the database by using a comma-separated list of buffer IDs:

For example, this statement loads the contents of buffers 1 and 2:

import database AsoSamp.Sample data 
   from load_buffer with buffer_id 1, 2;

Multiple data load buffers can exist on an aggregate storage database. For a list and description of the data load buffers that exist on an aggregate storage database, use the query database MaxL statement with the list load_buffers grammar:

query database appname.dbname list load_buffers;

This statement returns the following information about each existing data load buffer:

You can incrementally commit the data load buffer to an aggregate storage database to create a slice. After loading the new slice into the database, Essbase creates all necessary views on the slice (such as aggregate views) before the new data is visible to queries.

To create a data slice, use the import database MaxL statement with the create slice grammar.

For example, to create a slice by overriding values (the default), use this statement:

import database AsoSamp.Sample data
   from load_buffer with buffer_id 1
   override values create slice;

In Administration Services Console, you can set an option to create a slice on the Data Load dialog box. See “Data Load Dialog Box” in the Oracle Essbase Administration Services Online Help.

You can merge all incremental data slices into the main database slice or merge all incremental data slices into a single data slice while leaving the main database slice unchanged. To merge slices, you must have the same privileges as for loading data (Administrator or Database Manager permissions).

When the data for a new data slice is in the data load buffer, Essbase scans the list of incremental data slices and considers whether to automatically merge them with the new slice. To qualify for an automatic merge, a slice must be smaller than 100,000 cells or smaller than two times the size of the new slice. Slices larger than 5,000,000 cells are never automatically merged. For example, if a new slice contains 300,000 cells, incremental data slices that contain 90,000 cells and 500,000 cells are automatically merged with the new cell. An incremental data slice that contains 700,000 is not.

After the new input view is written to the database, Essbase creates the aggregate views for the slice. The views created for the new slice are a subset of the views that exist on the main database slice.

If you cleared data from a region using the logical clear region operation, which results in a value of zero for the cells you cleared, you can elect to remove zero value cells during the merge operation. See Clearing Data from Specific Regions of Aggregate Storage Databases.

To perform merging operations, use the alter database MaxL statement with the merge grammar.

For example, to merge all incremental data slices into the main database slice, use this statement:

alter database AsoSamp.Sample 
   merge all data;

To merge all incremental data slices into the main database slice and remove zero value cells, use this statement:

alter database AsoSamp.Sample 
   merge all data remove_zero_values;

To merge all incremental data slices into a single data slice, use this statement:

alter database AsoSamp.Sample
   merge incremental data;

In Administration Services Console, you can merge data slices. See “Merging Incremental Data Slices (Aggregate Storage Databases)” in the Oracle Essbase Administration Services Online Help.

For data sets that are small enough to reload completely while maintaining low data latency, Essbase can remove the current contents of a database and replace the database with the contents of a specified data load buffer. The atomic replacement functionality transitions querying the old contents of the database to the new contents without interrupting service. The newly loaded data set is aggregated to create the same set of views that existed for the replaced data set.

Essbase also allows for automatically replacing the contents of all incremental data slices in a database. Consider a situation in which data can be separated into a relatively large, static data set that is never updated and a relatively small, volatile data set for which the individual updates are difficult to identify but are confined to the volatile data set. For example, the large, static data set consists of historical transaction data for the last three years; however, for the transaction data for the past two months, users can change a characteristic of a transaction in the source database. Tracking these changes can be prohibitively complex. You can load the static data set as the main slice in a database and the volatile data set as one or more incremental slices.

Essbase removes the current contents of all incremental data slices and creates a new slice (using the add values grammar) with the contents of a specified data load buffer. The newly loaded data set is augmented with aggregated views based on the set of views that exist on the main slice.

To replace the contents of a database or the incremental data slices in a database, use the import database MaxL statement with the override grammar.

For example, to replace the contents of a database, use this statement:

import database AsoSamp.Sample data
   from load_buffer with buffer_id 1
   override all data;

To replace the contents of all incremental data slices with a new slice, use this statement:

import database AsoSamp.Sample data
   from load_buffer with buffer_id 1
   override incremental data;

In Administration Services Console, you can set the option to replace the contents of the database or data slices in the Data Load dialog box. See “Replacing Database or Data Slice Contents (Aggregate Storage Database)” in the Oracle Essbase Administration Services Online Help.

Essbase provides statistics on the size and number of incremental data slices, and the cost of querying the incremental data slices.

The time it takes for a query to access all of the incremental data slices is expressed as a percentage (between .01 and 1.0 inclusive). If a database has a main slice and multiple incremental data slices, a query statistic of 0.66 means that two-thirds of the query time was spent querying the incremental data slices and one-third was spent querying the main data slice. If the cost of querying the incremental data slices is too high, you can merge the slices.

To view the incremental data slice statistics, see “Viewing Aggregate Storage Statistics” in the Oracle Essbase Administration Services Online Help.

Incremental data loads on aggregate storage databases may use disk space up to two times the size of your current data files. In cases where databases are larger than 2 GB, you can reduce disk space utilization by setting the maximum file size of the default tablespace to no more than 2 GB. See Working with Tablespaces.

In Spreadsheet Add-in, the send command is equivalent to using the incremental data load functionality with the override grammar.

While performing a send operation, new requests for lock, unlock, and retrieve and lock will wait until the send operation is completed.

See the Oracle Essbase Spreadsheet Add-in Online Help.

While processing data source records for loading values into aggregate storage databases, Essbase processes source data records only for the level 0 dimension intersections where the member does not have a formula.

The following example shows a data source with records for only level 0 intersections. The last field contains data values; the other fields are level 0 members of their respective dimensions.

Jan, Curr Year, Digital Cameras, CO, Original Price, 10784
Jan, Prev Year, Camcorders, CO, Original Price, 13573

Essbase ignores records that specify upper-level members and, at the end of the data load, displays the number of skipped records.

For example, the following record would be skipped because member Mid West is a level 1 member:

Jan, Curr Year, Digital Cameras, Mid West, Original Price, 121301

Sorting data sources is unnecessary because Essbase Server reads and sorts records internally before committing values to the database.

Rules file specifications for loading values to aggregate storage databases reflect the aggregate storage data load process.

For block storage data loads, through the rules file, you choose for each data source whether to overwrite existing values, add values in the data source to existing values, or subtract them from existing values.

For aggregate storage data loads using the aggregate storage data load buffer, you make this choice for all data load sources that are gathered into the data load buffer before they are loaded to the database.

To use a rules file that was defined for a block storage outline with an aggregate storage outline, first associate the rules file with the aggregate storage outline. See “Associating an Outline with an Editor” in the Oracle Essbase Administration Services Online Help.

You can clear data from a specified region in an aggregate storage database and retain the data located in other regions. This feature is useful when you want to delete volatile data (such as data corresponding to the last month) but retain historical data. You must have Database Manager or Administrator permission to clear data.

Methods for clearing data from a region:

In specifying the region to be cleared, follow these guidelines:

For example, to clear all January data for Forecast1 and Forecast2 scenarios, use this statement:

alter database AsoSamp.Sample clear data in region 'CrossJoin({Jan},{Forecast1, Forecast2})';

During the clear region operation, you cannot perform operations that update the database (such as loading data, merging data slices, or clearing data from another region), nor export data. You can query the database; however, the query results are based on the data set before the clear region operation.

The clear data in region grammar cannot clear data from the entire database. See Clearing All Data from an Aggregate Storage Database.

Clearing all data from an aggregate storage database is the same as for a block storage database. To clear the entire database, either use:

The concept of solve order applies to query execution. When a cell is evaluated in a multidimensional query, the order in which the calculations should be resolved may be ambiguous. To remove ambiguity, you can use the solve order property to specify the required calculation priority.

The value of the solve order property determines the priority with which Essbase calculates the formulas. The formulas on the members that have a specified solve order are calculated in order from the lowest solve order to the highest. (See Example Using the Solve Order Property). You can specify a solve order between 0 and 127. The default is 0.

You can specify the solve order at the member level or at the dimension level. Essbase uses the following information to define calculation precedence:

The following example is based on the ASOSamp.Sample database. To remove ambiguity in query results, the example uses the solve order property to specify the required calculation priority.

The spreadsheet query in Figure 174, Results from Spreadsheet Query of ASOSamp.Sample database Showing the Variance Between Two Ratios (C12) retrieves data for the number of units sold and the number of transactions for January of the current year and for January of the previous year. The Variance member shows the difference between the current year and the previous year. The Avg Units/Transaction member shows a ratio of the number of units sold per transaction.

Figure 174. Results from Spreadsheet Query of ASOSamp.Sample database Showing the Variance Between Two Ratios (C12)

Figure 175, ASOSamp.Sample Database Showing the Measures, Years, and Time Dimensions shows the database outline for these members and the formulas applied to the Variance and Avg Units/Transaction members.

Figure 175. ASOSamp.Sample Database Showing the Measures, Years, and Time Dimensions

When calculating the variance of the average units per transaction (cell C12 in Figure 174, Results from Spreadsheet Query of ASOSamp.Sample database Showing the Variance Between Two Ratios (C12)), the result could be the variance between the two ratios, or the result could be the ratio of the two variances. The result depends on whether Essbase gives precedence to the formula on Variance or the formula on Avg Units/Transaction.

The value of the solve order property, which is attached to the members in the database outline, determines the priority with which Essbase evaluates the formulas. The formula on the member that has the higher solve order takes precedence.

In the example, if the Variance member has a higher solve order than the Avg Units/Transaction member, then the formula on the Variance member takes precedence and the result is the variance between two ratios. This is the case in the ASOsamp.Sample database, because the solve order of the Variance member is 20 and the solve order of the Avg Units/Transaction member is 10. The formula on Variance takes precedence, because the Variance member has the higher solve order. The result for cell C12 of the query in Figure 174, Results from Spreadsheet Query of ASOSamp.Sample database Showing the Variance Between Two Ratios (C12) is the variance between the two ratios, as shown in Table 137:

Alternatively, if you change the ASOsamp.Sample database, and you give the Avg Units/Transaction member a higher solve order than the Variance member, then the formula on the Avg Units/Transaction member takes precedence, and the result is the ratio of two variances, as shown in Table 138 and in Figure 176, Results from Spreadsheet Query of ASOsamp.Sample Database Showing the Ratio of Two Variances (C12):

Figure 176. Results from Spreadsheet Query of ASOsamp.Sample Database Showing the Ratio of Two Variances (C12)

The following topics describe terms that are integral to an explanation of aggregate storage database calculation.

Original Price, Curr Year, Qtr1, Personal Electronics, CO 
Original Price, Curr Year, Qtr1, Personal Electronics, KS 
Original Price, Curr Year, Qtr1, Home Entertainment,   CO 
Original Price, Curr Year, Qtr1, Home Entertainment,   KS 
Original Price, Curr Year, Qtr2, Personal Electronics, CO 
Original Price, Curr Year, Qtr2, Personal Electronics, KS 
Original Price, Curr Year, Qtr2, Home Entertainment,   CO 
Original Price, Curr Year, Qtr2, Home Entertainment,   KS 
Original Price, Curr Year, Qtr3, Personal Electronics, CO 
Original Price, Curr Year, Qtr3, Personal Electronics, KS 
Original Price, Curr Year, Qtr3, Home Entertainment,   CO 
Original Price, Curr Year, Qtr3, Home Entertainment,   KS 
Original Price, Curr Year, Qtr4, Personal Electronics, CO 
Original Price, Curr Year, Qtr4, Personal Electronics, KS 
Original Price, Curr Year, Qtr4, Home Entertainment,   CO 
Original Price, Curr Year, Qtr4, Home Entertainment,   KS 
Original Price, Prev Year, Qtr1, Personal Electronics, CO 
Original Price, Prev Year, Qtr1, Personal Electronics, KS 
Original Price, Prev Year, Qtr1, Home Entertainment,   CO 
Original Price, Prev Year, Qtr1, Home Entertainment,   KS 
and so on...

You can use either Administration Services Aggregation Design Wizard or MaxL statements to perform an aggregation. The aggregation process has two phases:

During the aggregate view selection phase, Essbase analyzes how calculating and storing various combinations of aggregate views might affect average query response time. As input to the analysis, you can define physical storage and performance requirements. You can also track data usage and provide the information to the analysis process. See Selecting Views Based on Usage.

Based on their usefulness and resource requirements, Essbase creates a list of aggregate views. Included with each view in the list is storage and performance information that applies when that aggregate view plus all other aggregate views listed above it are stored. You can choose to aggregate the listed views, select and aggregate a subset of the listed views, or rerun the selection process with different input criteria. You can also add to an aggregation the materialization of new views that are based on new selection criteria.

See Fine-Tuning Aggregate View Selection.

Whether or not you materialize the selection, you can save the selection of aggregate views as an aggregation script. Aggregation scripts provide flexibility and can save time because they enable you to bypass the selection process if the same selection is needed again. See Working with Aggregation Scripts.

After the selection process is finished, the selected aggregate views are calculated when you materialize the selected aggregate views into an aggregation.

The following process is recommended for defining and materializing aggregations:

The default selection of aggregate views proposed by Essbase provides excellent performance. However, accepting all aggregate views in the selection list does not guarantee optimum performance. For the default selection, Essbase analyzes stored hierarchies and assumes an equal chance that any aggregate cell will be retrieved. Essbase cannot account for external factors such as the amount of available memory at the time of a query. Available memory can be affected by such factors as the cache memory definition at retrieval time, or the memory other concurrent processes require.

You may want to track which data is most queried and include the results and alternate views in the aggregate view selection process. (See Selecting Views Based on Usage.) As you tune and test aggregations, consider the following points:

Essbase provides information to help you select and store the right balance of aggregate views for your database. Weigh this information against what you know about your database retrieval requirements and environment. Use the following information to help you select aggregate views for an aggregation:

The following process is recommended for fine-tuning aggregations:

Essbase enables you to capture retrieval statistics against a database. You can then use these statistics to build aggregations tailored to retrieval patterns in your company. Also, Essbase includes alternate hierarchies in its analysis of the database when usage information is used in the aggregate view selection process.

Database usage for periodic reporting may be different than for ongoing user retrievals. To optimize different retrieval situations, consider tracking situational usage patterns and creating aggregation scripts for each situation.

Before you begin the aggregation selection process, ensure that query tracking is on and that it has been on long enough to capture representative usage.

Query tracking holds query usage information in memory. Performing any of the following operations clears query usage information.

Query tracking remains on until you turn it off, stop the application, or change the outline.

By default, Essbase uses internal mechanisms to decide how to create aggregations. User-defined view selection provides a way for you to influence default view selection and view selection based on query data. See Selecting Views Based on Usage.

Administrators may apply view selection properties to stored hierarchies to restrict Essbase from choosing certain levels for aggregation.

Essbase considers only views that satisfy the selected view selection properties.

You should be familiar with the dominant query patterns of databases before changing default properties; preventing selection of certain views will make queries to those views slower while improving the speed of other queries. Similarly, enabling Consider All Levels on a secondary hierarchy may speed queries to that hierarchy while making other queries slower.

Query hints tell Essbase what types of queries likely will occur. For example, to indicate queries at the bottom level of the time dimension, you can specify one member at the bottom level of time, such as January. This tells Essbase that any member at the bottom level of time likely will be queried. Essbase gives members indicated in query hints priority when selecting views, optimizing common queries.

If no member is specified for a dimension, it means that any member of that dimension may be used in a query. For example, using a query hint of (Sales, 100, East) on Sample.Basic means that profit margin measures for level 1 products at level 1 markets is a common type of query, regardless of Year and Scenario dimensions, which were omitted.

Usage-based view selection overrides query hints. See Selecting Views Based on Usage. User-defined view selection overrides query hints if there is a conflict between the two. See Understanding User-Defined View Selection

Asterisks “*” indicate that no member is selected for a dimension.

Numerous hints can exist in the same outline. Any view that fits at least one hint is given more weight than views that fit none.

Query Hints cannot contain dynamic, label-only, or shared members.

Each aggregation script represents a specific aggregate view selection against a database.

Aggregation scripts can save you time. For example, after loading new data values you need not perform another aggregate view selection. You can speed the aggregation process by using the selection stored in an aggregation script to materialize the aggregation.

Aggregation scripts also give you flexibility. You can use them to save aggregate view selections optimized for different retrieval situations; for example, you can use one script to optimize retrievals in month-end reporting and another for daily retrieval requirements.

Aggregation scripts for a database become invalid when the selection it contains is invalid for the database. Create aggregation scripts when you create aggregations. Do not manually modify aggregation script files, which may cause unpredictable results. For information about when you must create aggregations and aggregation scripts, see Replacing Aggregations.

To possibly improve aggregation performance time, you can use the CALCPARALLEL configuration setting to increase the number of threads. Because each thread handles a task of building an aggregate view, the number of threads you define establishes how many aggregate views can be built concurrently. Even on single-CPU computers, increasing the CALCPARALLEL configuration setting may improve performance where building aggregates is I/O-bound, such as for databases too large to fit in memory. Because threads must share aggregate storage cache, the cache size must be sufficiently large to support the number of threads defined. Otherwise, increasing the number of threads could degrade aggregation performance time.

See the Oracle Essbase Technical Reference.

At times you might want to manually clear aggregations from the disk; for example, to make the disk space available for disk-intensive operations. Clearing aggregations clears all data, except level 0 values, from the database, releasing the disk area for other use. After aggregations are cleared, queries calculate retrieved values dynamically from level 0 values.

For information about when Essbase Server automatically clears aggregated data, see “Database restructure” in Table 123, Outline Differences Between Aggregate Storage and Block Storage.

You can replace an aggregation by clearing the existing aggregation and materializing a different selection of aggregate views. You can perform a new aggregate view selection and materialization process, or you can run an aggregation script. Consider replacing the aggregation in the following situations:

You must replace an aggregation and associated aggregation scripts after the number of levels in a dimension has been changed or one or more dimensions have been added or removed from the outline. See Performing Database Aggregations and Working with Aggregation Scripts.

You can set Time Balance properties on aggregate storage Accounts dimensions to provide built-in calculations along the Time dimension. This saves time and performance overhead of using member formulas to achieve time-balance functionality.

The following time-balance properties are supported on stored or formula-bearing Accounts dimension members:

Consider a stored measure such as Headcount in a human-resources application. Within a Year-Quarter-Months hierarchy, Headcount data is loaded at the month level.

The desired yearly or quarterly Headcount value is not the sum of its months; rather, it should be the last recorded value within a time period.

Tagging Headcount as TB Last with SKIPMISSING means that, for Year 2005, its value is the last nonempty value of the headcount for its months. If Dec has a nonmissing Headcount value, then that value will be returned; otherwise, the Nov value will be checked and returned if nonmissing.

If a formula-bearing member has a time balance tag, the formula is executed only for level-0 Time members, and the Time dimension is aggregated according to the time balance tag.

The time balance tags provide a built-in calculation along the Time dimension. To perform other time-based calculations using formulas, such as period-to-date and rolling averages, you can create a dimension called TimeView and write all time-based formulas on that dimension. Doing so enables you to use Time Balance calculation functionality without losing the ability to perform other time-based calculations.

A Flow tag can be assigned to Accounts dimension members bearing formulas.

The following example describes the problem to be solved with flow metrics. Assume you have Sales and Additions figures for all 12 months. You want to perform an aggregation to populate each month’s beginning inventory.

You would use an MDX formula on the Beginning Inventory member in order to calculate its value. Without flow metrics, to obtain each month’s beginning inventory, the calculator engine would have to reiterate the MDX formula exponentially.

Inventory = SUM(MemberRange(Jan:Time.CurrentMember), (Additions - Sales)) + Beg_Inventory

To optimize the illustrated example, assign the Inventory member the formula (Addition – Sales), and tag the member as Flow.

If alternate hierarchies are used in the aggregate storage time dimension, the following restrictions apply when using Flow and TB tags on the Accounts dimension:

The MDX Aggregate function can be used to aggregate measures tagged with time balance tags. See the Oracle Essbase Technical Reference.

The following calculation logic applies if

If the above cases are both true, MDX Aggregate() semantics are used to evaluate the cells.

For example, consider a scenario in which:

The value of (Year, Holiday_TRUE, [Opening Inventory]) is evaluated according to the following MDX logic:

Aggregate( {Set of dates that are holidays in Year}, [Opening Inventory])