What causes database Fragmentation

What causes database Fragmentation

Essbase database 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.

• Sending data to a dense dimension that previously did not have any data - This occurs when you have a write back cube where users are adding new data to a dense dimension, such as adding data to the current month or adding data to an account that previously did not have any data.  (Assuming that time and accounts are dense)
• Loading data into a cube - Load rules can also cause database fragmentation if they are not set up properly.  When loading data into Essbase, you always want to sort the data so that sparse dimensions are represented first in the data loads.  The sorting of data files/sources has a huge impact on Essbase performance and fragmentation.  Rule of thumb: make sure that the data source is sorted by the columns that represent the sparse dimensions first.
• Adding incremental data to a cube - Adding the "current month's data" to an Essbase cube can cause fragmentation if the time dimension is tagged as dense.

Fragmentation has a huge impact on cube performance.  There are two ways to "un-fragment" an Essbase cube.  One is to clear and reload the data into the cube.  The other is to cause Essbase to perform a dense restructure.