Loading, Calculating, and Retrieving Aggregate Storage Data Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Loading, Calculating, and Retrieving Aggregate Storage Data


The most common processes for working with database information include maintaining the outline, loading data values to the database, calculating values, and retrieving database information. In the following topics, this chapter describes how performing these tasks with aggregate storage databases is different from performing these tasks with block storage databases:

Examples in this chapter refer to the outline in Figure 1.

Figure 1: Sample Aggregate Storage Outline

The simplified aggregate storage outline in Figure 1 is not completely expanded. A plus sign (+) node at the left of a member name indicates that the member has children that are not displayed.

Preparing Aggregate Storage Databases

The following topics describe dimension build and data load process differences between aggregate storage databases and block storage databases:

To use the information in these topics, you should be familiar with data load, dimension build, and rules file concepts and procedures. For information about using data sources to change outlines and to load data values, see Understanding Data Loading and Dimension Building.

Building Dimensions in Aggregate Storage Databases

If a dimension build of an aggregate storage database makes a structural change to the outline, all data values are cleared from the database when the dimension build is finished. See Table 2, Outline Differences Between Aggregate Storage and Block Storage for details about outline changes that cause restructuring.

Differences between outline characteristics of block storage outlines and aggregate storage outlines affect data sources and rules files. For example, defining a dimension as sparse or dense is not relevant to aggregate storage outlines.

For details, see the following subtopics of this topic:

Rules File Differences for Aggregate Storage Dimension Builds

Rules files for building aggregate storage outlines must define only outline properties that apply to aggregate storage outlines. For details about aggregate storage outline features and differences, see Table 2, Outline Differences Between Aggregate Storage and Block Storage.

Before using a rules file that is defined for a block storage outline with an aggregate storage outline, open the rules file in Data Prep Editor, associate the rules file with the aggregate storage outline, and validate the rules file. For instructions for associating outlines, see "Associating an Outline with an Editor" in Essbase Administration Services Online Help.

As you edit rules files for aggregate storage databases, some dimension build rules file options that apply only to block storage databases are displayed in Data Prep Editor dialog boxes. Table 1 lists block storage rules file settings that do not apply to aggregate storage outlines. Entries in rules files for these options are ignored when the rules file is processed.


Table 1: Aggregate Storage Dimension Build Rules File Differences

Rules File Location in the Administration Services Interface
Dimension Build Rules File Options That Do Not Apply to Aggregate Storage Databases

Dimension Build Settings dialog box, Global Settings tab

Data configuration options

Dimension Build Settings dialog box, Dimension Build Settings tab

Existing members option: Do not share

Dimension Properties dialog box, Dimension Properties tab

Dimension types option: Country

Two-Pass calculation option

Data storage options:

  • Never share

  • Dynamic Calc and Store

  • Dynamic Calc

All configuration options

Dimension Properties dialog box, Accounts Dimension tab

None of the options on this tab apply.

Field Properties dialog box, Dimension Build Properties tab

Field type options:

  • Currency name

  • Currency category

Currency functionality does not apply to aggregate storage databases.



For details about the dialog boxes, see Essbase Administration Services Online Help.

Data Source Differences for Aggregate Storage Dimension Builds

Data sources for modifying aggregate storage outlines should not include field values that apply only to block storage outlines. Table 2 displays property code values that are recognized in dimension build data sources as consolidation properties for members of aggregate storage databases: Any other code is ignored and + (Add) is assumed. For details about specifying member property codes, see Using the Data Source to Set Member Properties.


Table 2: Valid Consolidation Properties for Members of Aggregate Storage Outlines

Code
Description

%

Express as a percentage of the current total in a consolidation (applies only to accounts dimensions)

*

Multiply by the current total in a consolidation (applies only to accounts dimensions)

+

Add to the current total in a consolidation (applies only to accounts dimensions)

-

Subtract from the current total in a consolidation (applies only to accounts dimensions)

/

Divide by the current total in a consolidation (applies only to accounts dimensions)

~

Exclude from the consolidation (applies only to accounts dimensions)

O

Tag as label only



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. For information about writing formulas for aggregate storage outlines, see Developing Formulas on Aggregate Storage Outlines.

Loading Data into Aggregate Storage Databases

The differences between loading data into aggregate storage databases and loading data into block storage databases are described in the following topics:

For additional information related to aggregate storage data loads, see Table 5, Data Load Differences Between Aggregate Storage and Block Storage.

Data Source Differences for Aggregate Storage Data Loads

While processing data source records for loading values into aggregate storage databases, Analytic Services processes source data records only for the level 0 dimension intersections where the accounts dimension 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 
 

Analytic Services 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 
 

Since level 0 cells exist only if they contain values, specifying #MISSING or #MI as a value removes the associated cell if it is present in the database. Sorting data sources is unnecessary because Analytic Server reads and sorts all records internally before loading values to the database.

Rules File Differences for Aggregate Storage Data Loads

Rules file specifications for loading values to aggregate storage databases reflect the aggregate storage data load process. Options that apply only to block storage data loads are grayed out in Data Prep Editor when the rules file is associated with an aggregate storage outline.

For block storage data loads, you choose for each data source, through the rules file, 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. For information about the data load process, see Aggregate Storage Data Load Process.

Before using with an aggregate storage outline a rules file that is defined for a block storage outline, open the rules file in Data Prep Editor, associate it with the aggregate storage outline, and validate the rules file. For instructions, see "Associating an Outline with an Editor" in Essbase Administration Services Online Help.

Aggregate Storage Data Load Process

For general information about loading data, see Performing and Debugging Data Loads or Dimension Builds.

Aggregate storage databases facilitate analysis of very large dimensions containing up to a million or more members. To efficiently support loading data values into such large databases, Analytic Services enables you to combine the processing of multiple data sources through a temporary aggregate storage data load buffer.

When you take advantage of the aggregate storage data load buffer, Analytic Services sorts and works with the values after all data sources have been read. If multiple records are encountered for any specific data cell, the values are accumulated. Analytic Services then stores the accumulated values-replacing, adding to, or subtracting from existing data values in the database.

Note: For data loads using the aggregate storage data load buffer, the choice for replacing, adding, or subtracting values is specified for the entire set of data sources. For all other data loads, the choice for replacing, adding, or subtracting values is specified per data source through rules files.

Taking advantage of the aggregate storage data load buffer can significantly improve overall data load performance because it requires fewer input/output operations.

The following topics provide an overview of the data load processes based on the method used.

Using MaxL to Perform Aggregate Storage Data Loads

Using the MaxL import database data statement to load data values from a single data source does not involve the aggregate storage data load buffer. If you use multiple statements in incremental data loads to aggregate storage databases, you can significantly improve performance if you load values to the aggregate storage data load buffer first, with a final write to storage after all data sources have been read.

To use this buffer in MaxL, you specify a series of separate statements that perform the following tasks:

A sample of the sequence of statements loading three data sources (file_1, file_2, and file_3) follows:

  1. First, an aggregate storage data load buffer is initialized, as defined by the following MaxL alter database statement:
    alter database agg.sample
       initialize load buffer with buffer_id 1;
    

  2. Next, data sources are read into the aggregate storage data load buffer where they are sorted and accumulated. You can include any combination of data sources, such as .xls files, text files, and SQL relational sources. The following three statements show an example of multiple data sources to be accumulated in the buffer:

    import database agg.sample data
       from server data_file 'file_1.txt'
       to load_buffer with buffer_id 1;
    import database agg.sample data
       from server data_file 'file_2'
       using server rules_file 'rule'
       to load_buffer with buffer_id 1;
    import database agg.sample data
       from server excel data_file 'file_3.xls'
       to load_buffer with buffer_id 1;

    Note: Import statements need not be contiguous. As long as the buffer exists, the database is locked from queries, aggregations, or data loads from other means, such as from Administration Services. You can perform other MaxL operations, such as displaying security information or creating triggers.

  3. A final import statement loads the buffer contents to the database cells, replacing existing values:

    import database agg.sample data
       from load_buffer with buffer_id 1
       override values;

    Note: Performing an application restart loses the data load buffer. When you restart the application, you restart the entire process.

Using Essbase Administration Services Console to Perform Aggregate Storage Data Loads

Essbase Administration Services Console always uses the aggregate storage data load buffer for aggregate storage data loads. In this process, when you initiate a data load you provide all data source file names and rules file names in a single dialog box.

For further information about the methods for loading values to aggregate storage databases, see the following topics:


Tool
Topic
Location

Administration Services

Performing a Data Load or Dimension Build for Aggregate Storage Databases

Essbase Administration Services Online Help

ESSCMD

BUILDDIM

IMPORT

LOADDB

Technical Reference

MaxL

alter database

import data

Technical Reference



Note: If values have been calculated and stored through an aggregation, Analytic Services automatically updates higher-level stored values when data values are changed. No additional calculation step is necessary. The existence and size of an aggregation can affect the time it takes to perform a data load. For additional information about aggregations, see Aggregations.

Combining Data Loads and Dimension Builds

When using the aggregate storage data load buffer, you can combine data sources and rules files to add members to the outline and to load data values to the level 0 cells. Regardless of the order you specify the files, Analytic Services first makes the outline changes and then loads the data values.

Calculating Aggregate Storage Databases

Aggregate storage database values are calculated through the outline structure and MDX formulas. When a data load is complete, all the information needed to calculate an aggregate storage database is available. The values to support a retrieval request are calculated by a consolidation of the values loaded for level 0 members. Formulas for accounts dimension members are calculated for each retrieval. Values calculated for retrievals are not stored.

To improve retrieval performance, Analytic Services can aggregate values and store them ahead of time. However, aggregating and storing all values can be a lengthy process that requires disk space for storage. Analytic Services provides an intelligent aggregation process that balances time and storage resources. For details, see Aggregating an Aggregate Storage Database.

To prepare an aggregate storage database for retrieval, you create the outline and load the level 0 values. Then you calculate the database by aggregating, and storing additional values, with the remaining values to be calculated when retrieved.

Note: If a database needs calculation scripts for special calculations and data dependencies, make it a block storage database.

The following topics further describe calculation of aggregate storage databases:

For additional information related to aggregate storage database calculations, see Table 3, Calculation Differences Between Aggregate Storage and Block Storage.

Outline Factors Affecting Data Values

The hierarchical structure of an aggregate storage outline determines how values are rolled up. L0 member values roll up to L1 member values, L1 member values roll up to L2 member values, and so on.

Consolidation operators assigned to members of the dimension tagged as accounts define the operations used in the roll-up: add (+), subtract (-), multiply (*), divide (/), percent (%), and no operation (~). For an explanation of operators, see Table 10.

Note: In aggregate storage outlines, consolidation operators assigned to members of non-accounts dimensions have no effect on calculation. For non-accounts dimensions, the only allowable consolidation operator is the add (+) operator.

For more complex operations, you can provide MDX formulas for members of the accounts dimension. MDX formulas are written in the same format as MDX numeric value expressions. For information about writing formulas for aggregate storage outlines, see Developing Formulas on Aggregate Storage Outlines.

Block Storage Calculation Features That Do Not Apply to Aggregate Storage Databases

The following characteristics of calculating block storage databases do not apply to aggregate storage databases:

Calculation Order

Calculation order may affect calculation results. Aggregate storage calculation order and block storage calculation order may differ. For block storage databases, by default, Analytic Services calculates dynamically-calculated members on standard dimensions and then aggregates members along the attribute dimensions.

For aggregate storage databases, Analytic Services calculates data in the following order:

  1. Aggregates members on all dimensions except the dimension tagged as accounts.The order in which members and dimensions are aggregated is optimized internally and changes according to the nature of the database outline.

  2. Calculates accounts dimension members and formulas.

Because the internal calculation order for an aggregate storage database is not predictable, any inherent rounding errors are also not predictable. These rounding errors are expected behavior in computer calculation and are extremely small in relation to the data values concerned.

Aggregating an Aggregate Storage Database

For aggregate storage databases, after data values are loaded into the level 0 cells of an outline, the database requires no separate calculation step. From any point in the database, users can retrieve and view values that are aggregated for only the current retrieval. Aggregate storage databases are smaller than block storage databases, enabling quick retrieval of data values.

For even faster retrieval, you can precalculate data values and store the precalculated results in aggregations. The following terms are integral to an explanation of aggregate storage database calculation:

Aggregate Cells

Cells for level 0 intersections across dimensions, without formulas, are called input cells. Data values can be loaded to them. Higher-level cells of the accounts dimension are always calculated at retrieval. All other higher-level intersections across dimensions are aggregate cells. For example, for the outline in Figure 1, Price Paid > Curr Year > 1st Half > Portable Audio > CO is an aggregate cell; Original Price > Curr Year > Jan > Camcorders > CO is another aggregate cell. Values for aggregate cells must be rolled up from lower-level values.

Aggregate cell values are calculated for each request, or they can be precalculated and stored in aggregations.

Aggregate Views

When Analytic Services defines which aggregate cells to precalculate and store, it works with the cells in sets, known as aggregate views. An aggregate view is a collection of aggregate cells. The collection is based on the levels of the members within each dimension.

For example, consider one aggregate view for the outline in Figure 1. This aggregate view includes aggregate cells for level 0 members of the Measures, Years, and Geography dimensions, level 1 members of the Months dimension, and level 2 members of the Products dimension. The dimensions are arranged in the outline as Measures, Years, Months, Products, Geography. The example aggregate view is shown as 0,0,1,2,0.

The 0,0,1,2,0 aggregate view contains aggregate cells that include the following member intersections:

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... 
 

Aggregations

Aggregations are consolidations, based on outline hierarchy, of level 0 data values. An aggregation contains one or more aggregate views. Analytic Services provides an intelligent aggregation process that selects aggregate views to be rolled up, aggregates them, and then stores the values for the cells in the selected views. If an aggregation includes aggregate cells dependent on level 0 values that are changed through a data load, the higher-level values are automatically updated at the end of the data load process.

The term aggregation is used for both the process and the result of the process.

Performing Database Aggregations

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

During the aggregate view selection phase, Analytic Services analyzes how calculating and storing various combinations of aggregate views would affect average query response time. Based on their usefulness, Analytic Services creates an internal list of the aggregate views and determines a stopping point in physical storage used that balances performance requirements with the impact on physical storage. If desired, you can specify the amount of physical storage for an aggregation.

The first time that you perform an aggregation after an outline change, it is recommended that you let Analytic Services select the physical storage stopping point. After the calculation and storage phase, you can test retrieval times based on your retrieval requirements. To optimize performance for your situation, you can rerun the aggregation wherein you can increase or decrease the storage stopping point to be used by the aggregation. Specifying a storage limit reduces or increases the storage space required, the time required to materialize the aggregation, and the time required for some retrievals.

Administration Services Aggregation Design Wizard enables you to create aggregation scripts. An aggregation script represents a specific scenario that is relevant to the existing outline and to aggregate view selection determined by the storage limit, if one is specified. Aggregation Design Wizard displays the selected aggregate views and enables you to save aggregation scripts for later materialization. You can also execute the aggregation script immediately to materialize the aggregate views that it specifies. For additional information about aggregation scripts, see Working with Aggregation Scripts.

Aggregation Design Wizard also enables running aggregation processes in the background. When you run an Administration Services process in the background you can continue to use Essbase Administration Services Console for other activities at the same time as the background process is running.

MaxL addresses the aggregation selection and materialization phases in a single statement. To specify that Analytic Services determine the stopping point, omit the optional part of the MaxL execute aggregate process statement beginning with the keywords stopping when total_size exceeds.

To perform a database aggregation, use any of the following methods:


Tool
Topic
Location

Administration Services

Improving Retrievals by Precalculating Aggregations

Essbase Administration Services Online Help

MaxL

execute aggregate process

Technical Reference



Working with Aggregation Scripts

Saved aggregation scripts enable you to materialize an aggregation at a different time than the aggregate views for the aggregation are selected. Support for multiple aggregation scripts also enables you to choose from different selection scenarios. For example, one aggregation script that builds a 40 MB aggregation may support normal retrieval requirements. For a large, detailed weekly report, you could build a 60 MB aggregation that reduces the time to produce the report, then go back to the 40 MB script.

Only Administration Services Aggregation Design Wizard enables you to save aggregation scripts. Aggregation scripts are stored in the database directory as text files with the .csc extension and are valid until the outline is changed. To avoid the potential clutter of invalid aggregation script files, when you change the outline, be sure to delete existing aggregation scripts manually or through the Aggregation Design Wizard. You can also rename existing aggregation scripts.

Note: When Aggregation Design Wizard displays the list of existing aggregation scripts, it lists all files with the .csc extension in the database directory. Only valid aggregation script files can be executed.

You can use the Aggregation Design Wizard to execute an aggregation script or you can manually copy the contents of the aggregation script to a MaxL script which can be executed at a later time. Executing an aggregation script materializes the aggregate views specified within it. Although you can create multiple aggregation scripts, only one aggregation can be materialized at a time.

Retrieving Aggregate Storage Data

This topic includes the following subtopics:

For additional information related to query support, see Table 6, Query Differences Between Aggregate Storage and Block Storage.

Attribute Calculation Retrievals

Aggregate storage applications support only the Sum member of the Attribute Calculations dimension. If you specify any other member name from the Attribute Calculations dimension, such as Min or Avg, an error is returned. For information about the Attribute Calculations dimension, see Understanding the Attribute Calculations Dimension.

Retrieval Tools Supporting Aggregate Storage Databases

Analytic Services provides the following programs and tools that enable data retrieval from aggregate storage databases:

Any commands that support features that apply only to block storage cannot be used with aggregate storage databases; for example, the Report Writer <SPARSE command.



Hyperion Solutions Corporation link