Types Of Database Restructuring:

Types of Database Restructuring:

1) Implicit Restructures. Explicit Restructures.

Implicit Restructures: Analytic Services 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, 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. Empty blocks are not removed. Analytic Services marks all restructured blocks as dirty, so after a dense restructure you need to recalculate the database. Dense 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 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. 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.

Dense Restructures: To perform a dense 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 .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: ? essxxxxx.ind ? dbname.otl ? dbname.esm

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 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) ? Dense (index files and data files) as a result of adding, deleting, or moving members and other operations as listed in Table 80 on page 866. ? 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: 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, 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 80 on page 866, unless otherwise noted in the table. The following changes override incremental restructuring; that is, they result in immediate restructuring, even if 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 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 descriptions of the effects of various actions, see Table 80 on page 866. Whether or not incremental restructuring is enabled, if an outline has already been incrementally restructured (a dense restructure is already pending), adding shared members causes Analytic Services 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: ? 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. See the Technical Reference. Analytic Services logs outline changes in an internal file, dbname.ocl. Analytic Services 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 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 (readonly mode is used for backing up a database). See “Using VALIDATE to Check Integrity” on page 796.