11/15/09

Incremental Data loading

Many companies load data incrementally. For example, a company may load data each month for that month. To optimize calculation performance when you load data incrementally, make the dimension tagged as TIME a SPARSE dimension. If the time dimension is sparse, the database contains a datablock for each time period. When you load data by time period, Essbase accesses fewer data blocks because fewer blocks contain the relevant time period. Thus, if you have Intelligent Calculation enabled, only the data blocks marked as dirty are recalculated. For example, if you
load data for March, only the data blocks for March and the dependent parents of March are updated. However, making the time dimension sparse when it is naturally dense may significantly increase the size of the index, creating possibly slower performance due to more physical I/O activity to accommodate the large index.
If the dimension tagged as time is dense, you still receive some benefit from Intelligent Calculation when you do a partial data load for a sparse dimension. For example, if Product is sparse and you load data for one product, Essbase recalculates only the blocks affected by the partial load, although time is dense and Intelligent Calculation is enabled.