Optimizing Database Restructuring

This chapter describes how changes to a database outline affect Essbase:

In addition to the information in this chapter, look for information and instructions concerning restructuring in the following topics:

Understanding 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, etc. 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 very time-consuming, (unless you discard your data before restructuring), you may wish to make decisions about these kinds of changes, based on how much they affect performance. This section provides the information you need to understand how restructuring affects performance, and describes tasks you can perform related to database restructuring:

Note: For more information about clearing data, and thus avoiding some restructuring, see.

Types of Database Restructuring

Essbase uses three types of restructure operations:

If you use incremental restructuring, Essbase defers full restructuring. If you change a database outline frequently, consider enabling incremental restructuring. See Incremental Restructuring and Performance for more information.

Note: Whether a database outline is changed via the Outline Editor or using dimension building does not influence restructuring. Only the type of information change influences what type of restructuring, if any, takes place.

Conditions Affecting Database Restructuring

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

Use this table to find more information about the topics mentioned in this section:

Table 84: Topics Related To Database Restructuring

Topic
Related Information

Intelligent Calculation

Optimizing with Intelligent Calculation

Sparse and dense dimensions

Basic Architectural Elements

Attribute dimensions

Working with Attributes

Dimension building

Introducing Dynamic 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 these files:

Table 85: 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 that stores all metadata for a database and defines how data is stored.

This outline file does not store data.



Understanding a Full Restructure

To perform a full restructure, Essbase does the following:

  1. Creates temporary files that are copies of the .IND, .PAG, .OTL, .ESM, and .TCT files. Each temporary file substitutes either N or U for the last character of the file extension, so the temporary file names are dbname.INN, ESSxxxxx.INN, ESSxxxxx.PAN, dbname.OTN, dbname.ESN, and dbname.TCU.
  2. Reads the blocks from the database files copied in Step 1, restructures the blocks in memory, and then stores them in the new temporary files. This step takes the most time.
  3. Removes the database files copied in Step 1, including .IND, .PAG, .OTL, .ESM, and .TCT files.
  4. Renames the temporary files to the correct file names: .IND, .PAG, .OTL, .ESM, and .TCT.

Understanding a Sparse Restructure

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

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

Optimizing Restructure Operations

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

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

Actions That Improve Performance

There are a number of things you can do to improve performance related to database restructuring:

Incremental Restructuring and Performance

If you make frequent changes to a database outline, you may want to consider enabling incremental restructuring. When incremental restructuring is 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 Table 86, unless otherwise noted in the table.

The following changes override incremental restructuring; that is, they result in immediate restructuring, regardless of whether incremental restructuring is enabled:

Note: Recalculate the database after any type of restructure operation.

Using Incremental Restructuring

You can enable incremental restructuring for any of the following:

To enable incremental restructuring, use the INCRESTRUC parameter in the ESSBASE.CFG file. For more information on the INCRESTRUC parameter and syntax, see the Technical Reference in the docs directory.

Essbase logs outline changes in an internal file, dbname.OCL. Essbase clears the file when it does a full database 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. VALIDATE causes Essbase to restructure any blocks whose restructure was deferred; thus, the file is cleared. When you issue VALIDATE, make sure that the database is not in read-only mode (read-only mode is used for backing up a database). For more information on the VALIDATE command, see Checking Structural and Data Integrity.

Saving a Modified Outline

Essbase displays this dialog box when you save outline changes that trigger database restructuring (using the Outline Editor):

In the Restructure Database dialog box, you must choose one of the options:

If your 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 is most likely to 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, thus slowing performance.

By default, Essbase does not log outline changes. To see if outline logging is slowing performance, look for OUTLINECHANGELOG TRUE in the ESSBASE.CFG file. For more information about the log, 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. Use the Validate page of the Partition Wizard. See Validating the Partition for more information.
  2. Note: To validate a partition, you must have DB Designer or higher privileges.

  3. Synchronize the outlines of the partitions. Select Database > Synchronize Outline to open the Synchronize Outline dialog box. See Synchronizing Outlines for more information.

Tip: You can use ESSCMD to synchronize outlines. See the Technical Reference in the docs directory for information about these commands. See Automating the Production Environment for information about ESSCMD.

Outline Change Quick Reference

Table 86 shows all outline changes that affect calculation and restructuring, including incremental restructuring (see Incremental Restructuring and Performance for more information).

Note: If you are using Partitioning, restructuring affects only the local, target database, not the database to which you are connected.

Table 86: How Actions Affect Databases and Restructuring  

Action
Calculation and Standard Restructure Effects
Incremental Restructuring Applies? (If Enabled)
Delete, Add, or Move Member

Delete member of sparse dimension

Data needs to 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.

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

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

Delete member of attribute dimension

None

No

Delete member of dense dimension

Data needs to 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 needs to be recalculated. The data remains associated with the original member name, but, because the parent of the shared member may have depended on the child data, recalculation is needed.

No restructure.

No

Add member to sparse dimension

Data for the new member needs to 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 needs to be loaded or calculated to derive new values. Data needs to 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 needs to be recalculated. The new shared member affects the consolidation to its parent.

No restructure.

No

Move regular member within a sparse dimension

Data needs to 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 needs to be recalculated to reflect changes in consolidation.

Essbase restructures both index and data files.

Yes. Restructure deferred.

Move an attribute dimension member

None

No

Other Member-Related Changes

Change a member alias or add an alias to a member

None

No

Rename member

None

No

Change member formula

Data needs to be recalculated to reflect formula changes.

No restructure.

No

Dynamic Calculation-Related Changes

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 both 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 both index and data files.

Yes. Restructure deferred.

Define dense dimension regular member as Dynamic Calc member

Essbase restructures both index and data files.

Yes. Restructure deferred.

Add, delete, or move sparse dimension Dynamic Calc member

Essbase restructures only index files.

For member add or delete, restructure is deferred.

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

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

Essbase restructures only index files.

For member add, restructure deferred.

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

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

Essbase restructures both index and data files.

No

Add, delete, or move dense dimension Dynamic Calc member

No restructure.

No

Property and Other Changes

Change dense-sparse property

Data needs to be recalculated.

Essbase restructures both index and data files.

Essbase restructures both index and data files overriding incremental restructure.

Change label only property

Data needs to be recalculated.

Essbase restructures both index and data files.

Restructure deferred.

Change shared member property

Data needs to 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 needs to be recalculated.

Essbase restructures both index and data files.

Essbase restructures both 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 user-defined attribute

None

No






© 2002 Hyperion Solutions Corporation. All rights reserved.
http://www.hyperion.com