Optimizing Database Restructuring

In This Section:

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

Subtopics

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 178 for information about restructuring:

Table 178. Topics Related To Database Restructuring

Topic	Related Information
Intelligent Calculation	Restructuring Databases
Sparse and dense dimensions	Sparse and Dense Dimensions, Selection of Dense and Sparse Dimensions Dense and Sparse Selection Scenarios
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 179:

Table 179. 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:

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
```
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.
Removes the database files that were copied in step 1
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:

Renames the dbname.esm file to dbname.esr.
Renames the essxxxxx.ind files to essxxxxx.inm.
Creates 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.

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:

Validate the partitions.
See Validating Partitions.
Note:
To validate a partition, you must have Database Manager permissions or higher.
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 180. 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 181. 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

Action

Calculation and Standard Restructure Effects

Incremental Restructuring Applies? (If Enabled)

Change a member alias or add an alias to a member

None

Rename member

None

Change member formula

Data must be recalculated to reflect formula changes.

No restructure.

Table 182. 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 183. 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