The information in this chapter is not relevant to aggregate storage databases.
Database Restructuring
As your business changes, you change the Analytic Services 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 Analytic Services to restructure the database.
Because changes that require restructuring the database are very time-consuming, (unless you discard the 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 information about clearing data, and thus avoiding some restructuring, see CLEARDATA and CLEARBLOCK in the Technical Reference or Clearing Data in the Essbase Administration Services Online Help.
Types of Database Restructuring
You can restructure a database explicitly or implicitly. To explicitly restructure a database, use the alterdatabaseDBS-NAMEforcerestructure MaxL command. See "alter database" in the Technical Reference for more information.
An explicit restructure triggers a full restructure of the database.
Analytic Services uses three types of implicit restructure operations:
Full restructure: If a member of a dense dimension is moved, deleted, or added, Analytic Services restructures the blocks in the data files and creates new data files. When Analytic Services restructures the data blocks, it regenerates the index automatically so that index entries point to the new data blocks. Analytic Services 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, Analytic Services 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, Analytic Services 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, Analytic Services defers full 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 (that is, 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.
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, Analytic Services 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 information about restructuring:
Table 77: Topics Related To Database Restructuring
When Analytic Services restructures both the data blocks and the index, it uses these files:
Table 78: Files Used During Database Restructuring
File
Description
essxxxxx.pag
Analytic Services data file
essxxxxx.ind
Analytic Services index file
dbname.esm
Analytic Services 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.
The outline file does not store data.
Full Restructures
To perform a full restructure, Analytic Services 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.
Sparse Restructures
When Analytic Services does a sparse restructure (restructures just the index), it uses the following files:
essxxxxx.ind
dbname.otl
dbname.esm
To perform a sparse restructure, Analytic Services 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.
Optimization of 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 79
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:
If you change a dimension frequently, make it sparse.
Use incremental restructuring to control when Analytic Services performs a required database restructuring.
Select options when you save a modified outline that reduce the amount of restructuring required.
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, Analytic Services defers restructuring so that a change to the database outline or to a dimension does not cause structural change. Analytic Services restructures the index and, if necessary, the affected block the next time the block is accessed.
Understanding Incremental Restructuring
When incremental restructuring is enabled, Analytic Services defers restructuring for the database changes listed in Table 79, 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. Disabling of incremental restructuring does not affect other databases on the server.
Certain member additions and certain changes to sparse dimensions can also trigger immediate restructuring. For detailed descriptions of the effects of various actions, see Table 79.
Regardless of whether incremental restructuring is enabled, if an outline has already been incrementally restructured (a full restructure is already pending), adding shared members causes Essbase to perform a full 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:
An individual database in an application
All databases in an application
All databases in all applications
To enable incremental restructuring, use the INCRESTRUC setting in the essbase.cfg file. For detailed information on the INCRESTRUC setting and for syntax, see the Technical Reference.
Analytic Services logs outline changes in an internal file, dbname.ocl. Analytic Services 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 Analytic Services 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 detailed information on the VALIDATE command, see Using VALIDATE to Check Integrity.
Options for Saving a Modified Outline
Analytic Services 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 should be handled during restructure; for example, you can choose to preserve all data, to preserve only level 0 or input data, or to discard all data during restructure. For more information, see "Saving Outlines" in 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.
Analytic Services 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, Analytic Services may issue a "Can't cancel" message. If such a message is displayed, Analytic Services is performing final cleanup and it is too late to cancel.
Outline Change Log
If you activate the outline change log, Analytic Services 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 Analytic Services must make to the log, thus slowing performance.
By default, Analytic Services doesnot log outline changes. To see if outline logging is slowing performance, look for OUTLINECHANGELOG TRUE in the essbase.cfg file. For a comprehensive discussion of the outline change log, see Understanding and Using the Outline Change Log.
Analytic Services Partitioning Option
When you use Partitioning, Analytic Services 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 Analytic Services restructures data when you are using partitioning, perform the following steps to make sure that data is synchronized across partitions:
Table 79 shows 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 79: 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.
Analytic Services 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. Analytic Services 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 needs to be recalculated to reflect changes to relationships.
Analytic Services restructures the data files to reflect a changed block size. Analytic Services 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.
Analytic Services 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.
Analytic Services restructures the data files to reflect a changed block size. Analytic Services 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.
Analytic Services restructures the index file.
No. Analytic Services restructures the index file, overriding incremental restructure.
Move regular member within a dense dimension
Data needs to be recalculated to reflect changes in consolidation.
Analytic Services 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: Analytic Services 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
Analytic Services 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
Analytic Services 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
Analytic Services restructures both index and data files.
Yes. Restructure deferred.
Define dense dimension regular member as Dynamic Calc member
Analytic Services restructures both index and data files.
Yes. Restructure deferred.
Add, delete, or move sparse dimensionDynamic Calc member
Analytic Services restructures only index files.
For member add or delete, restructure is deferred.
For member move, Analytic Services restructures only index files, overriding incremental restructure.
Add, delete, or move sparse dimension Dynamic Calc and Store member
Analytic Services restructures only index files.
For member add, restructure deferred.
For member move or delete, Analytic Services restructures only index files (overrides incremental restructure).
Add, delete, or move dense dimension Dynamic Calc and Store member
Analytic Services 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.
Analytic Services restructures both index and data files.
Analytic Services restructures both index and data files overriding incremental restructure.
Change label only property
Data needs to be recalculated.
Analytic Services 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.
Analytic Services 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.
Analytic Services restructures the index.
Analytic Services restructures the index, overriding incremental restructure.
Change the order of dimensions
Data needs to be recalculated.
Analytic Services restructures both index and data files.
Analytic Services 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