Hyperion ESSbase Database Fragmentation and Removal

Database Fragmentation

Fragmentation is produced when Essbase writes a modified data block to a new  location on disk and leaves unused space in the former location of the data block, as shown on the slide. (Modified blocks are larger because data from a data load or calculation is added to the blocks.)

 

Measuring Database Fragmentation

Average clustering ratio:

• An inverse measure of data block “scatter”
• Higher average clustering ratio means lower fragmentation

Managing Database Fragmentation : Two management strategies that are not mutually exclusive 

• Prevention strategy

–  Recognize contributing factors  

–  Optimize data loads

•  Reduction strategy

–  Export and reload

–  Dense database restructure

Preventing Database Fragmentation: Tactics to help prevent database fragmentation

• Recognizing contributing factors  

–  Read/Write/Calculation frequency

–  Data loads

–  Calc and Store

 –  Isolation level

• Optimizing data loads

–  Fragmentation effect  

–  Load time effect

Reducing Database Fragmentation: Strategies to reduce database fragmentation 

• Exporting and reloading  
• Performing a full database restructure

Options  for Restructuring

• Outline only—Restructures the outline  only; does not restructure the index  and data files (least time-consuming)

• Sparse—Restructures and creates a new index.

• Full—Restructures blocks and creates new index and data files (most  time consuming but removes database fragmentation)