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:
- Full 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. Essbase marks all restructured blocks as dirty, so after a full restructure you need to recalculate the database. Full restructuring is the most time-consuming of the restructures and, for large databases, can take a very long time to complete.
- Sparse restructure: If a member of a sparse dimension or a member of an attribute dimension is moved, deleted, or added, Essbase restructures the index and creates new index files. Restructuring the index is relatively fast; the amount of time required depends on the size of the index.
- 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 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:
- If you use Intelligent Calculation in your database, all restructured blocks are marked as dirty whenever data blocks are restructured. This 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 this table to find more information about the topics mentioned in this section:
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:
- 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.
- 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.
- Removes the database files copied in Step 1, including .IND, .PAG, .OTL, .ESM, and .TCT files.
- 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:
- ESSxxxxx.IND
- dbname.OTL
- dbname.ESM
To perform a sparse restructure, Essbase does the following:
- Renames the dbame.ESM file to dbname.ESR
- Renames the ESSxxxxx.IND files to ESSxxxxx.INM.
- Creates new index files (ESSxxxxx.IND) to store index information that is changed by the restructuring operation.
- 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:
- Outline only (no index or data files)
- Sparse (only index files)
- Full (index files and data files) as a result of adding, deleting, or moving members and other operations as listed in Table 86
- Full (index and data files) as a result of changing a dense dimension to sparse or changing a a sparse dimension to dense.
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:
- Adding or deleting a non-attribute 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 linked reporting objects (LROs) in a database, incremental restructuring is automatically disabled on that database. This does not affect other databases on the server.
- Certain member additions and certain changes to sparse dimensions can also trigger immediate restructuring. For more information, see Table 86.
Note: Recalculate the database after any type of restructure operation.
Using Incremental Restructuring
You can enable incremental restructuring for any of the following:
- An individual database in an application
- All databases in an application
- All databases in all applications
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:
- All data-This option preserves all data, but is requires more time than the other options.
- Level 0 data-This option preserves data only for level zero (leaf node) members. If the outline change requires a database recalculation, and if all data required for the calculation is in level zero members, this is the fastest restructure option, and requires the least amount of disk space.
When you select Level 0 data, all upper level blocks are deleted before restructuring. This option reduces the disk space required to restructure and improves calculation time. Essbase recreates the upper-level blocks when it calculates the database.
- Input data-This option preserves only the blocks that contain loaded data. This option prevents any blocks created by data loading from being deleted, whether they are upper-level or lower-level blocks. If you change the database and need to recalculate, and if you load data into various levels of the outline, this is restructure option is the fastest, and uses the least disk space.
The Input data option deletes all blocks that contain calculated values before restructuring. This option reduces the disk space required to restructure and improves calculation time when the database is calculated.
- Discard all data-This option preserves no data. Use this option when you expect to reload the data or when the outline is so radically changed that no existing data applies.
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:
- Validate the partitions. Use the Validate page of the Partition Wizard. See Validating the Partition for more information.
Note: To validate a partition, you must have DB Designer or higher privileges.
- 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
|