Managing Aggregate Storage Applications and Databases

In This Section:

Aggregate Storage Security

Managing Storage for Aggregate Storage Applications

Managing the Aggregate Storage Cache

Improving Performance When Building Aggregate Views on Aggregate Storage Databases

Aggregate Storage Database Restructuring

Exporting Aggregate Storage Databases

The information in this chapter applies only to aggregate storage databases and is not relevant to block storage databases.

Also see:

Aggregate Storage Security

Defining and executing aggregations requires Calculation (Administration Services) or Execute (MaxL) permission or higher. Dimension builds that clear database values require Write permission.

See these security-related topics:

  • About Essbase Native Security Mode in the Oracle Essbase Database Administrator's Guide

  • “Managing User/Group Permissions for Applications and Databases”

    in the Oracle Essbase Administration Services Online Help

  • “Privileges and Roles” in the Oracle Essbase Technical Reference

Managing Storage for Aggregate Storage Applications

For aggregate storage applications, Tablespace Manager controls data retrieval and storage, using tablespace definitions to manage data storage and work areas on the disk.

Working with Tablespaces

Tablespaces help optimize data file and work file storage and retrieval. Tablespaces define location definitions that map data artifacts, such as aggregate views and aggregations, to files. Each application directory contains directories for four tablespaces:

  • default—Contains database data structure and database values (After data is loaded, the tablespace location cannot be changed.)

  • log—Contains a binary transactional log of default tablespace updates

  • metadata—Contains information about file locations, files, and objects contained in the database

  • temp—Provides a temporary workspace to be used during operations such as data loads, aggregations, and retrievals

Tablespace names are case-sensitive, regardless of operating system. You cannot change the location or size of metadata and log. For default and temp you can specify multiple locations and sizes, and you can define tablespace properties:

  • Directory path locations

  • Maximum disk space to be used at each location

  • Maximum file size allowed within each location

Note:

You can modify or delete file locations used to store information within a tablespace if the locations are empty.

Because Tablespace Manager allocates disk space in fixed-size increments, specifying the maximum disk space for a tablespace location specifies an end point but does not reserve the specified space.

When space is needed, Essbase checks file locations (in numerical order) and, when space is found, starts writing. When all locations are used, no space is available, and an error is returned. When database values are cleared, tablespace files shrink, releasing disk space. Work files that are no longer needed are deleted, making space available for other programs.

Based on the maximum size specified for files, Essbase writes multiple files; for example, ess00001.dat, ess00002.dat, and so on. If you back up database files to other media, do not set a maximum tablespace file size greater than the size that the media can handle.

Defining Tablespaces

You define tablespace definitions for each aggregate storage application.

  To define tablespaces, use a tool:

Tool

Topic

Location

Administration Services

Managing Tablespaces

Oracle Essbase Administration Services Online Help

MaxL

alter tablespace

Oracle Essbase Technical Reference

Note:

UNIX platforms enforce a maximum 2 GB file limit. If, during a data load or an aggregate build, the .dat file limit is exceeded, this message is displayed: "Failed to extend file: file exceeds maximum file size for this system." Essbase closes the data file, creates the next file (essn+1), and continues.

Managing the Aggregate Storage Cache

Aggregate storage cache facilitates memory usage during data loads, aggregations, and retrievals. The cache memory locking feature is used only with block storage applications.

When an aggregate storage outline is started, a small area in memory is allocated as the aggregate storage cache for the relevant application. As additional cache area is needed, the cache size incrementally increases until the maximum cache size is used or the operating system denies additional allocations.

Note:

Denial of aggregate cache memory allocations does not deny increased use of existing memory.

You can view the current aggregate cache memory allocation and the maximum aggregate cache size setting. Changing the setting may optimize memory use. The default maximum cache size, 32 MB, is the minimum setting size. You can use the size of input-level data to determine when to increase the maximum size for the cache. Administration Services and MaxL display the size of input-level data as the aggregate storage database property: Size of level 0 values.

A 32 MB cache setting supports a database with approximately 2 GB of input-level data. If the input-level data size is greater than 2 GB by some factor, the aggregate storage cache can be increased by the square root of the factor. For example, if the input-level data size is 3 GB (2 GB * 1.5), multiply the aggregate storage cache size of 32 MB by the square root of 1.5, and set the aggregate cache size to the result: 39.04 MB.

For aggregation materialization performance, consider the number of threads set for parallel calculation. The aggregation materialization process uses multiple threads that divide the aggregate storage cache. Increasing the number of threads specified in the CALCPARALLEL configuration setting for aggregate storage applications or databases may require an increase in aggregate storage cache size. See the CALCPARALLEL configuration setting in the Oracle Essbase Technical Reference.

Note:

Setting the number of threads higher than the number of processors may improve aggregate storage application performance.

Do not increase the maximum size of the aggregate storage cache beyond what is needed.

  To set aggregate storage cache size, use a tool:

Tool

Topic

Location

Administration Services

Sizing the Aggregate Storage Cache

Oracle Essbase Administration Services Online Help

MaxL

query application

alter application

Oracle Essbase Technical Reference

Note:

A changed aggregate storage cache setting becomes effective when the application is restarted.

Improving Performance When Building Aggregate Views on Aggregate Storage Databases

You might encounter the following message while building aggregate views on an aggregate storage database:

For better performance, increase the size of aggregate storage cache

This message sometimes occurs when an aggregate storage database is larger than a few hundred million input cells.

To improve the performance of building aggregates, take the following steps.

  1. Increase the size of the aggregate storage cache to at least 512 MB or 20% of the input data size, whichever is smaller. (If the cache setting is already greater than this amount, proceed to the next step.) You can use Administration Services Console or the following MaxL command:

    alter application appname set cache_size xMB

    This setting takes effect after you restart the application.

  2. If you still see the message when building aggregate views after increasing the aggregate storage cache, use the ASOSAMPLESIZEPERCENT configuration setting. Syntax:

    ASOSAMPLESIZEPERCENT [appname [dbname]] n

    Gradually increase the n value until the message disappears and optimal aggregation performance is reached. For a database that contains:

    • 20 million input cells, start with 5%

    • 100 million cells, start with 1%

    • More than 1 billion cells, start with 0.1%

    Clear the aggregate views; then reselect and rebuild them. If the message still appears, increase the setting and try again.

    Performance of building aggregate views may not improve until the message no longer occurs. When the message no longer occurs and performance no longer improves, stop increasing the setting.

    Note:

    If you increase the ASOSAMPLESIZEPERCENT setting too high, performance will start to degrade again. The optimal setting for a database larger than 1 billion cells will probably be less than 3%. For more information on ASOSAMPLESIZEPERCENT, see the Oracle Essbase Technical Reference.

Aggregate Storage Database Restructuring

Database restructures may be forced by some aggregate storage database outline changes, including changes to hierarchies. A hierarchy comprises a top member and its descendants.

  • A dynamic hierarchy includes only one stored level. The Accounts dimension is a dynamic hierarchy.

  • An attribute dimension is one hierarchy. The generation 1 member is the top member of the hierarchy.

  • If a standard dimension is not tagged as multiple hierarchies enabled, it is one hierarchy. The generation 1 member is the top member of the hierarchy.

  • If a standard dimension is tagged as multiple hierarchies enabled, it contains multiple hierarchies. The generation 2 members are the top members of the hierarchies. For example, the Products dimension in ASOsamp.Sample contains two hierarchies. The top members are the generation 2 members All Merchandise and High End Merchandise.

    This image shows an outline of the Products dimension, as described in the text preceding the image.

What outlines changes affect:

  • Whether data must be cleared from the database before restructuring

  • The time and storage required to restructure the outline

Levels of Aggregate Storage Database Restructuring

To minimize the time and storage needed for database restructures, if a database outline changes frequently, analyze the outline and the types of outline changes.

Levels of restructuring for aggregate storage databases, listed from most to least expensive (in regard to time, storage, and data):

Table 235. Aggregate Storage Restructuring Levels

User-Outline Changes

Essbase-Restructure Level

Performance Impact

Add, delete, or move a standard dimension

Clears data and aggregate views, and performs full outline restructure

Very high

User must reload input (level 0) data, select the aggregate views, and rerun the database aggregation.

  • Add, delete, or move a hierarchy.

  • Change the number of stored levels in a hierarchy.

    See:

  • Change the top member of a stored hierarchy from label-only to stored or from stored to label-only.

  • Change a dynamic hierarchy to a stored hierarchy or a stored hierarchy to a dynamic hierarchy.

  • Change a primary or an alternate hierarchy so that it matches or no longer matches its primary or alternate hierarchy.

    All level 0 members of a primary hierarchy must be represented directly or indirectly (for example, a parent that is a sum of its children may represent its children) in all alternate hierarchies. The top level of the primary hierarchy must equate to the top level of each alternate hierarchy. See Example: Changes in Alternate Hierarchies.

Clears aggregate views, and performs full outline restructure

Very high

Storage requirement is up to three times the size of the database file (.dat file).

User must select the aggregate views and rerun the database aggregation.

Perform a change that is not included in other categories; for example, delete or move a member, or add a member that is not the last of its siblings

Performs full outline restructure

High

Storage requirement is up to three times the size of the database file (.dat file).

Perform a light restructure change (described below) to an alternate hierarchy or an attribute dimension

Rebuilds all aggregate views that are based on attribute dimensions or alternate hierarchies

Low

Storage requirement is up to three times the size of the affected views. Such aggregate views normally exist only if you used query tracking to select views based on usage. See Selecting Views Based on Usage.

On nonattribute dimensions without stored level 0 members (for example, all level 0 members are shared or have formulas), add a child or child branch without changing the number of levels in the hierarchy and without crossing a power of 2 boundary.

Performs light outline restructure

Note:

If the number of levels in the hierarchy changes, Essbase clears all aggregate views and performs a full outline restructure. Performance impact is Very High.

If the number of levels in the hierarchy does not change, but adding a child or child branch crosses a power of 2 boundary, Essbase performs a full outline restructure. Performance impact is High.

Very low

On nonattribute dimensions with stored level 0 members:

  • Add a child as the last child of a parent without crossing a power of 2 boundary (1, 2, 4, 8, 16, and so on). For example, if a parent member has three children, you may add a fourth child as the last child of the parent.

  • Add a child branch as the last child branch of an existing parent without crossing a power of 2 boundary and without changing the number of levels in the hierarchy.

Examples:

  • Renames a member

  • Changes a formula

  • Changes an alias

  • Changes a dynamic hierarchy consolidation operator (for example, from + to -)

Performs light outline restructure.

Very low

On nonattribute dimensions with stored level 0 members:

  • Add a child that crosses a power of 2 boundary as the last child of a parent. For example, if a parent member has three children and you add a fourth and fifth child, the fifth child crosses the power of 2 boundary. See Example: Addition of Child Members.

  • For scenarios in which adding a child branch as the last child branch of an existing parent that crosses a power of 2 boundary or changing the number of levels in the hierarchy, which triggers a full outline restructure, see Example: Addition of Child Branches.

Clears aggregate views, and performs full outline restructure

Very high

Outline-Change Examples

This sections contains examples of the more complicated outline changes described in Table 235, Aggregate Storage Restructuring Levels.

Example: No Change in the Number of Stored Levels in a Hierarchy

In ASOsamp.Sample, the Measures dimension is tagged as accounts. Therefore, as a dynamic hierarchy, Measures includes only one stored level.

This image shows an outline of the Measures dimension with one stored level (Ratios).

Adding the child member All to Ratios does not change the number of stored levels in the Measures dimension. Saving the outline triggers a light restructure.

This image shows the effect of adding All as a child to Ratios, as described in the text preceding the image.

In ASOsamp.Sample, Income Level is a stored hierarchy dimension.

This image shows an outline of the Income Level stored hierarchy dimension, with two levels.

Adding a child member does not change the number of levels (two) in the hierarchy. Adding a seventh or eighth child member at the end is allowed; however, adding a ninth child member crosses the power of 2 boundary (see Example: Addition of Child Members), requiring a full outline restructure.

This image shows a child member added to Income Level. The effect of adding a child member is described in the text preceding the image.

Example: Change in the Number of Stored Levels in a Hierarchy

In the Product dimension in ASOsamp.Sample, renaming Photo Printers to Printers and adding child members increases the number of levels in the All Merchandise hierarchy from four to five. When the outline is saved, Essbase clears all aggregate views and performs a full outline restructure.

This image shows an outline of the All Merchandise hierarchy. The effect of adding child members, which increases the number of stored levels, is described in the text preceding the image.

Example: Changes in Alternate Hierarchies

If you delete the shared member Orange under Drinks by Category and do not delete its nonshared member under Drinks, the alternate hierarchy Drinks by Category is no longer a replica of the Drinks hierarchy. When the outline is saved, Essbase clears all aggregate views and performs a full outline restructure.

If you delete the shared and nonshared Orange members, the alternate hierarchy Drinks by Category remains a replica of the Drinks hierarchy. When the outline is saved, Essbase performs a full outline restructure but does not clear aggregate views.

This image shows an outline of the Drinks hierarchy and Drinks by Category alternate hierarchy. The effect of hierarchy changes is described in the text preceding the image.

Example: Addition of Child Members

In ASOsamp.Sample, adding a child member under Systems in the All Merchandise hierarchy increases the number of children under Systems to three, crossing the power of 2 boundary. When the outline is saved, Essbase performs a full outline restructure.

This image shows an outline of the All Merchandise hierarchy. The effect of adding child members is described in the text preceding the image.

However, adding a child member under Computers and Peripherals increases the number of children under Computers and Peripherals from three to four. Adding a fourth child, which must be added after the existing members, does not cross the boundary of 2 or 4. The child must be added after existing members. When the outline is saved, Essbase performs a light restructure.

This image shows an outline of Computers and Peripherals, which is in the All Merchandise hierarchy. The effect of adding child members is described in the text preceding the image.

Example: Addition of Child Branches

In ASOsamp.Sample, adding a child branch under Computers and Peripherals in the All Merchandise hierarchy increases the number of children to four. Adding this child, which must be added after the existing members, does not cross the power of 2 boundary. The new member, called Other Peripherals, has two children. Systems (which is a sibling of Other Peripherals) has two children. Adding the child branch stays within the power of 2 boundary for children of sibling members at the same level. When the outline is saved, Essbase performs a light restructure.

This image shows an outline of Computers and Peripherals, in which a child branch is added. The effect of adding a child branch and members is described in the text preceding the image.

Adding a child branch with three child members crosses the power of 2 boundary and may require that Essbase perform a full outline restructure. However, if Systems already had three members, the power of 2 boundary would be four and, at most, four children can be added to Other Peripherals without triggering a full outline restructure.

Exporting Aggregate Storage Databases

If you have read permission for an aggregate storage database, you can export level 0 data from the database to a specified text file. The export file contains only uncompressed data, not control, outline, or security information. During data export, users can connect to Essbase Server and perform read-only operations on the database.

Exported data can be reloaded without a rules file if there are no outline changes. Consider exporting data for the following reasons:

  • To transfer data across platforms

  • To create an exported file in text, rather than binary, format

  • To create backups

Exports

The default location for export files is ARBORPATH/app/. You can specify an alternate location; see the Oracle Essbase Administration Services Online Help or the Oracle Essbase Technical Reference.

Aggregate storage database exports have limits:

  • You can export only level 0 data (input data).

  • You cannot perform columnar exports. In a columnar export, the output file displays a member name from each dimension in every row (and names can be repeated from row to row).

To avoid creating export files larger than 2 GB, Essbase may create multiple export files that include a number suffix in the name, as follows: _1, _2, and so on. For example, if the first file name is /home/exportfile.txt, the next file is /home/exportfile_1.txt.

To improve performance, you can export data in parallel.

  To export data, use a tool:

Tool

Topic

Location

Administration Services

Exporting Databases

Oracle Essbase Administration Services Online Help

MaxL

export data

Oracle Essbase Technical Reference