Optimizing Database Restructuring Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Optimizing Database Restructuring


This chapter describes how changes to a database outline affect Analytic Services:

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

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 alter database DBS-NAME force restructure 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:

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:

Use this table to find information about restructuring:


Table 77: 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 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:

  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.

Sparse Restructures

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

To perform a sparse restructure, Analytic Services 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.

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:

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, 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:

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:

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 does not 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:

  1. Validate the partitions.

    For a brief discussion and instructions, see Validating Partitions.

    Note: To validate a partition, you must have database designer permissions or higher.

  2. Synchronize the outlines of the partitions.

    For a comprehensive discussion and instructions, see Synchronizing Outlines.

Outline Change Quick Reference

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 dimension Dynamic 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

None

No





Hyperion Solutions Corporation link