De fragmentation of Essbase Cube

Overview of Fragmentation in Essbase

Fragmentation is typically unused disk space.

In Essbase, fragmentation occurs when blocks are "re-written" within the database. Not all "re-writes" to a cube will cause fragmentation. Listed below are some behaviors that can cause Essbase fragmentation.
Fragmentation cannot be reduced completely. Fragmentation is likely to occur with the following:

·         Frequent data load in cube

·         Frequent retrieval

·         Frequent Calculation of Cube

Defragmentation overview

Defragmentation is nothing but removing fragmentation i.e. optimizing unused space.

E.g. We can’t store more than 10kb file in to fragmentized space, because no 10kb free block is available in the fragmentized disk. After defragmentation we have enough space to store then 10kb file size.

Measuring Fragmentation

Fragmentation is measured by

·         Average Fragmentation Quotient

·         Average Clustering Ratio

We have taken measurement of average Fragmentation quotient and average clustering ratio in our Test environment on 13^th Dec 2014 as given below

 The optimum clustering ratio value is 1. If average clustering ratio is less than 1, then the cube is fragmented which affects performance and needs to be defragmented.

For fragmentation quotient,  Any quotient above the high end of the range indicates that reducing fragmentation may help

 The above screenshot is taken from my current project - Test Environment.
In Test, we have copied Production application on 13^th Dec 2014, so it is right now de-fragmented and showing good values of clustering ratio and fragmentation quotient.

Defragmentation steps

Measure Clustering ratio and Fragmentation Quotient

1.       Step 1 : Login to Essbase

Run -->  CMD --> ESSCMD

Enter Essbase server name as host

Enter proper username and password

2.       Step 2 : Select application and cube

SELECT APPNAME

Enter DB name --> DBNAME

3.       Step 3 : Get data base stats

GETDBSTATS

Check Average clustering ratio and Average Defragmentation Ratio as mentioned in above step.

Export Data – Level0

Export Level0 data from DBNAME cube and save it to “DBNAME_LEVEL0.txt” file

Clear All Data in DBNAME cube

Once data exported successfully, clear all data of database.

Load data from “DBNAME_LEVEL0.txt”

Now Application has blank Database, Right click on Database and select “Load Data”.

Click on “Find Data File” and browse data file then click ok

 Check if the data load to the cube is successful.

Restructure cube DBNAME

Measure Clustering ratio and Fragmentation Quotient

Repeat steps mentioned at the beginning for measuring clustering ratio and fragmentation quotient.