Optimizing Database Restructuring

In This Section:

Database Restructuring

Optimization of Restructure Operations

Actions That Improve Performance

Outline Change Quick Reference

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

Also see:

Database Restructuring

As your business changes, you change the Essbase database outline to capture new product lines, provide information on new scenarios, reflect new time periods, and so on. Some changes to a database outline affect the data storage arrangement, forcing Essbase to restructure the database.

Because changes that require restructuring the database are time-consuming (unless you discard the data before restructuring), consider deciding on such changes based on how they affect performance. This section provides the information necessary to understand how restructuring affects performance and describes tasks you can perform related to database restructuring:

Note:

For information about clearing data and thus avoiding some restructuring, see CLEARDATA and CLEARBLOCK in the Oracle Essbase Technical Reference or Clearing Data in the Oracle Essbase Administration Services Online Help.

Types of Database Restructuring

This section describes the two ways that a database restructure is triggered.

Implicit Restructures

Essbase initiates an implicit restructure of the database files after an outline is changed using Outline Editor or Dimension Build. The type of restructure that is performed depends on the type of changes made to the outline:

  • Dense restructure: If a member of a dense dimension is moved, deleted, or added, Essbase restructures the blocks in the data files and creates new data files. When Essbase restructures the data blocks, it regenerates the index automatically so that index entries point to the new data blocks. Empty blocks are not removed. Essbase marks all restructured blocks as dirty, so after a dense restructure you must recalculate the database. Dense restructuring, the most time-consuming of the restructures, can take a long time to complete for large databases.

  • Sparse restructure: If a member of a sparse dimension is moved, deleted, or added, Essbase restructures the index and creates new index files. Restructuring the index is relatively fast; the time required depends on the index size.

  • Outline-only restructure: If a change affects only the database outline, Essbase does not restructure the index or data files. Member name changes, creation of aliases, and dynamic calculation formula changes are examples of changes that affect only the database outline.

If you use incremental restructuring, Essbase defers dense restructuring. If you change a database outline frequently, consider enabling incremental restructuring. See Incremental Restructuring and Performance for a comprehensive discussion of incremental restructuring.

Note:

How a database outline is changed (by using Outline Editor or using dimension build) does not influence restructuring. Only the type of information change influences what type of restructuring, if any, takes place. For information about outline changes and the type of restructures they cause, see Outline Change Quick Reference.

Explicit Restructures

When you manually initiate a database restructure, you perform an explicit restructure. An explicit restructure forces a full restructure of the database. A full restructure comprises a dense restructure plus removal of empty blocks.

*  To initiate an full restructure, use a tool:

Tool

Topic

Location

Administration Services

Restructuring Databases Manually

Oracle Essbase Administration Services Online Help

MaxL

alter database

Oracle Essbase Technical Reference

    Conditions Affecting Database Restructuring

    Intelligent Calculation, name changes, and formula changes affect database restructuring:

    • If you use Intelligent Calculation in the database, all restructured blocks are marked as dirty whenever data blocks are restructured. Marking the blocks as dirty forces the next default Intelligent Calculation to be a full calculation.

    • If you change a name or a formula, Essbase does not mark the affected blocks as dirty. Therefore, you must use a method other than full calculation to recalculate the member or the database.

    Use Table 110 for information about restructuring:

    Table 110. Topics Related To Database Restructuring

    Topic

    Related Information

    Intelligent Calculation

    Restructuring Databases

    Sparse and dense dimensions

    Attribute dimensions

    Designing Attribute Dimensions

    Dimension building

    Understanding Data Loading and Dimension Building

    Outline Editor

    Creating and Changing Database Outlines

    Temporary Files Used During Restructuring

    When Essbase restructures both the data blocks and the index, it uses the files described in Table 111:

    Table 111. Files Used During Database Restructuring

    File

    Description

    essxxxxx.pag

    Essbase data file

    essxxxxx.ind

    Essbase index file

    dbname.esm

    Essbase kernel file that contains control information used for database recovery

    dbname.tct

    Transaction control table

    dbname.ind

    Free fragment file for data and index free fragments

    dbname.otl

    Outline file in which is defined all metadata for a database and how data is stored.

    Dense Restructures

    *  To perform a dense restructure, Essbase does the following:

    1. Creates temporary files that are copies of the following files:

      essxxxxx.ind
      essxxxxx.pag
      dbname.otl
      dbname.esm
      dbname.tct

      Each temporary file substitutes either n or u for the last character of the file extension. Temporary file names are:

      essxxxxx.inn
      essxxxxx.pan
      dbname.otn
      dbname.esn
      dbname.tcu
    2. Reads the blocks from the database files copied in step 1, restructures the blocks in memory, and stores them in the new temporary files. This step takes the most time.

    3. Removes the database files that were copied in step 1

    4. Renames the temporary files created in step 1 to the correct file names.

    Sparse Restructures

    When Essbase does a sparse restructure (restructures only the index), it uses the following files:

    • essxxxxx.ind

    • dbname.otl

    • dbname.esm

    *  To perform a sparse restructure, Essbase does the following:

    1. Renames the dbame.esm file to dbname.esr.

    2. Renames the essxxxxx.ind files to essxxxxx.inm.

    3. Creates index files (essxxxxx.ind) to store index information that is changed by the restructuring operation.

    4. Removes dbname.esr and essxxxxx.inm created in step 1.

    Optimization of Restructure Operations

    If a database outline changes frequently, analyze the outline and the types of changes that you are making. Changes to sparse dimensions or attribute dimensions are relatively fast, because only the index changes. Changes to dense dimensions are relatively slow, because data blocks are rebuilt.

    These types of restructure operations are listed from fastest to slowest:

    • Outline only (no index or data files)

    • Sparse (only index files)

    • Dense (index files and data files) as a result of adding, deleting, or moving members and other operations. See Outline Change Quick Reference

    • Dense (index and data files) as a result of changing a dense dimension to sparse or changing a sparse dimension to dense

    Actions That Improve Performance

    Several actions improve performance related to database restructuring:

    • If you change a dimension frequently, make it sparse.

    • Use incremental restructuring to control when Essbase performs a required database restructuring.

    • Select options when you save a modified outline that reduce the amount of restructuring required.

    Note:

    Setting the isolation level to committed access may increase memory and time requirements for database restructure. Consider setting the isolation level to uncommitted access before a database restructure. For information on isolation level settings, see Ensuring Data Integrity.

    Incremental Restructuring and Performance

    If you make frequent changes to a database outline, consider enabling incremental restructuring. With it enabled, Essbase defers restructuring so that a change to the database outline or to a dimension does not cause structural change. Essbase restructures the index and, if necessary, the affected block the next time the block is accessed.

    Understanding Incremental Restructuring

    When incremental restructuring is enabled, Essbase defers restructuring for the database changes listed in Outline Change Quick Reference, unless otherwise noted.

    The following changes override incremental restructuring; that is, they result in immediate restructuring, even if incremental restructuring is enabled:

    • Adding or deleting a nonattribute dimension.

    • Deleting a stored member of a sparse dimension.

    • Changing a dimension definition from sparse to dense or from dense to sparse.

    • If you are using LROs in a database, incremental restructuring is automatically disabled on that database. Disabling incremental restructuring does not affect other databases on the server.

    • Certain member additions and certain changes to sparse dimensions can also trigger immediate restructuring. See Outline Change Quick Reference.

    Whether or not incremental restructuring is enabled, if an outline has already been incrementally restructured (a dense restructure is pending), adding shared members causes Essbase to perform a dense restructure.

    Note:

    Recalculate the database after any type of restructure operation.

    Using Incremental Restructuring

    You can enable incremental restructuring for any of the following databases:

    • One database in an application

    • All databases in an application

    • All databases in all applications

    To enable incremental restructuring, use the INCRESTRUC configuration setting in the essbase.cfg file. See the Oracle Essbase Technical Reference.

    Essbase logs outline changes in an internal file, dbname.ocl. Essbase clears the file when it does a dense restructure or when you clear or reset the database. The file dbname.ocl can grow quite large. To clear this file, issue VALIDATE in ESSCMD, which causes Essbase to restructure any blocks whose restructure was deferred. When you issue VALIDATE, ensure that the database is not in read-only mode (which is used for backing up a database). See Using VALIDATE to Check Integrity.

    Options for Saving a Modified Outline

    Essbase displays a dialog box when you save outline changes that trigger database restructuring (using Outline Editor). In the Restructure Database dialog box, you define how data values are handled during restructure; for example, you can preserve all data, preserve only level 0 or input data, or discard all data during restructure. See “Saving Outlines” in Oracle Essbase Administration Services Online Help.

    If the database contains data, you need enough free disk space on the server to create a backup copy of the database. Backup ensures that any abnormal termination during the restructure process does not corrupt the database.

    Essbase may display a “Restructuring not required” message yet still perform an index-only restructure. This event most likely will occur if you make changes to a sparse dimension. If you try to cancel a restructure operation, Essbase may issue a “Can’t cancel” message. If such a message is displayed, Essbase is performing final cleanup, and it is too late to cancel.

    Outline Change Log

    If you activate the outline change log, Essbase records all activity that affects the outline (member name changes, member moves, and so on). The more changes you make to the outline, the more updates Essbase must make to the log, slowing performance.

    By default, Essbase does not log outline changes. To see whether outline logging is slowing performance, look for OUTLINECHANGELOG TRUE in the essbase.cfg file. See Understanding and Using the Outline Change Log.

    Essbase Partitioning Option

    When you use Partitioning, Essbase tracks outline changes so that you can synchronize the database outlines across partitions. Tracking outline changes slows restructuring, particularly when there are many structural changes.

    If Essbase restructures data when you are using partitioning, perform the following steps to make sure that data is synchronized across partitions:

    1. Validate the partitions.

      See Validating Partitions.

      Note:

      To validate a partition, you must have Database Manager permissions or higher.

    2. Synchronize the outlines of the partitions.

      See Synchronizing Outlines.

    Outline Change Quick Reference

    The tables in this section show all outline changes that affect calculation and restructuring, including incremental restructuring.

    Note:

    If you are using Partitioning, restructuring affects only the database to which you are connected.

    Table 112. Actions: Delete, Add, or Move Member

    Action

    Calculation and Standard Restructure Effects

    Incremental Restructuring Applies? (If Enabled)

    Delete member of sparse dimension

    Data must be recalculated to reflect changes to relationships.

    Essbase deletes from the index file all pointers to blocks represented by the deleted member. Because the blocks are no longer pointed to, they become free space. No restructure.

    For regular members, no. Essbase restructures the index, overriding incremental restructure.

    For label-only members, yes, restructuring is deferred.

    Delete member of attribute dimension

    None

    No

    Delete member of dense dimension

    Data must be recalculated to reflect changes to relationships.

    Essbase restructures the data files to reflect a changed block size. Essbase restructures the index.

    Yes. Restructure deferred.

    Delete shared member in sparse or dense dimension

    Data must be recalculated. The data remains associated with the original member name, but, because the parent of the shared member may have depended on child data, recalculation is needed.

    No restructure.

    No

    Add member to sparse dimension

    Data for the new member must be loaded or calculated to derive new values.

    Essbase restructures the index.

    Yes. Restructure deferred.

    Add member to dense dimension

    Data for the new member must be loaded or calculated to derive new values. Data must be recalculated.

    Essbase restructures the data files to reflect a changed block size. Essbase restructures the index.

    Yes. Restructure deferred.

    Add member to attribute dimension

    None

    No

    Add shared member to sparse or dense dimension

    Data must be recalculated. The new shared member affects the consolidation to its parent.

    No restructure.

    No

    Move regular member within a sparse dimension

    Data must be recalculated to reflect changes in consolidation.

    Essbase restructures the index file.

    No. Essbase restructures the index file, overriding incremental restructure.

    Move regular member within a dense dimension

    Data must be recalculated to reflect changes in consolidation.

    Essbase restructures index and data files.

    Yes. Restructure deferred.

    Move an attribute dimension member

    None

    No

    Table 113. Actions: Other Member-Related Changes

    Action

    Calculation and Standard Restructure Effects

    Incremental Restructuring Applies? (If Enabled)

    Change a member alias or add an alias to a member

    None

    No

    Rename member

    None

    No

    Change member formula

    Data must be recalculated to reflect formula changes.

    No restructure.

    No

    Table 114. Actions: Dynamic Calculation-Related Changes

    Action

    Calculation and Standard Restructure Effects

    Incremental Restructuring Applies? (If Enabled)

    Define Dynamic Calc member as Dynamic Calc and Store

    For dense dimension members: Essbase restructures both index and data files.

    For sparse dimension members: no restructure.

    Yes. Restructure deferred.

    Define Dynamic Calc and Store member as Dynamic Calc

    None

    No

    Define regular dense dimension member as Dynamic Calc and Store

    None

    No

    Define regular dense dimension member as Dynamic Calc

    Essbase restructures both index and data files.

    Restructure deferred.

    Define sparse dimension Dynamic Calc and Store member or Dynamic Calc member as regular member

    No restructure

    No

    Define sparse dimension regular member as Dynamic Calc or Dynamic Calc and Store

    Essbase restructures index and data files.

    Yes. Restructure deferred.

    Define dense dimension Dynamic Calc and Store member as regular member

    No restructure

    No

    Define dense dimension Dynamic Calc member as regular member

    Essbase restructures index and data files.

    Yes. Restructure deferred.

    Define dense dimension regular member as Dynamic Calc member

    Essbase restructures index and data files.

    Yes. Restructure deferred.

    Add, delete, or move sparse dimension Dynamic Calc member

    Essbase restructures index files.

    For member add or delete, restructure is deferred.

    For member move, Essbase restructures index files, overriding incremental restructure.

    Add, delete, or move sparse dimension Dynamic Calc and Store member

    Essbase restructures index files.

    For member add, restructure deferred.

    For member move or delete, Essbase restructures index files (overrides incremental restructure).

    Add, delete, or move dense dimension Dynamic Calc and Store member

    Essbase restructures index and data files.

    No

    Add, delete, or move dense dimension Dynamic Calc member

    No restructure.

    No

    Table 115. Actions: Property and Other Changes

    Action

    Calculation and Standard Restructure Effects

    Incremental Restructuring Applies? (If Enabled)

    Change dense-sparse property

    Data must be recalculated.

    Essbase restructures both index and data files.

    Essbase restructures index and data files overriding incremental restructure.

    Change label only property

    Data must be recalculated.

    Essbase restructures index and data files.

    Restructure deferred.

    Change shared member property

    Data must be recalculated to reflect the changed data value of the child.

    Essbase restructures both index and data files.

    Restructure deferred.

    Change properties other than dense-sparse, label, or shared

    Data may need to be recalculated to reflect changed consolidation properties, such as changing time balance from first to last.

    No

    Change the order of two sparse dimensions

    No calculation or data load impact.

    Essbase restructures the index.

    Essbase restructures the index, overriding incremental restructure.

    Change the order of dimensions

    Data must be recalculated.

    Essbase restructures both index and data files.

    Essbase restructures index and data files (overrides incremental restructure).

    Change the order of attribute dimensions

    None

    No

    Create, delete, clear, rename, or copy an alias table

    None

    No

    Import an alias table or set a member alias

    None

    No

    Change the case-sensitive setting

    None

    No

    Name a level and generation

    None

    No

    Create, change, or delete a UDA

    None

    No