Fragmentation in Essbase (BSO)

• What is Fragmentation?

Fragmentation is unused disk space.

• When does Fragmentation occur?

Fragmentation is likely to occur with the following:

Read/write databases that users are constantly updating with data

Databases that execute calculations around the clock

Databases that frequently update and recalculate dense members

Data loads that are poorly designed

Databases that contain a significant number of Dynamic Calc and Store members

Databases that use an isolation level of uncommitted access with commit block set to zero

• How can you measure fragmentation?

You can measure fragmentation using the average clustering ratio or average fragmentation Quotient.

Using the average fragmentation quotient

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

performance, with the following qualifications:

The reported value of the Fragmentation Quotient is more accurate when there are no other write

transactions running on the database.

For databases less than 50 MB using the Direct I/O access mode, the fragmentation quotient tends to be high. A high fragmentation quotient does not necessarily indicate a need to reduce fragmentation, because the free space is created in 8 MB chunks and all of it might not get used right away.

Database Size	Fragmentation Quotient Threshold
Small (up to 200 MB)	60% or higher
Medium (up to 2 GB)	40% or higher
Large (greater than 2 GB)	30% or higher

Using the average clustering ratio:

The average clustering ratio database statistic indicates the fragmentation level of the data (.pag) files. The maximum value, 1, indicates no fragmentation.

• How do you can prevent and remove fragmentation?

You can prevent and remove fragmentation:

To prevent fragmentation, optimize data loads by sorting load records based upon sparse dimension members. For a comprehensive discussion of optimizing data load by grouping sparse members.

To remove fragmentation, perform an export of the database, delete all data in the database with CLEARDATA, and reload the export file.

To remove fragmentation, force a dense restructure of the database.