Database Reference
In-Depth Information
available. As I mentioned, the DBAg cautions that too many dynamic members
can negatively impact performance. The processor penalty may be desirable
over disk Io.
2. Eliminate large dimensions from consideration for inclusion in the dense block.
The objective in selecting dimensions is to pack the block as tightly with data as
possible. Density decreases as the size of the dimension increases. The measures
dimension is the exception to this rule.
3. Consider the measures and time period dimensions. Because many BSo data-
bases are financially oriented, measures and time periods are probably good
candidates for the dense block. These two dimensions when used in combina-
tion are naturally packed with data. If one measure has data, the odds are that
many will be populated.
4. Eliminate dimensions that will grow significantly over time. The block size
should stay relatively the same size over the life of the database.
5. Consider whether the data in the dimension will provide opportunities to
restrict the amount to be processed. This is particularly true for budgeting
applications where the calculation is restricted to a single year. Set dimensions
that will to be used to subset the data as sparse.
6. reject dimensions that are or will be partitioned. A common approach to
improve performance is to partition large databases. Partitioning on a dense
dimension is more processor intensive than using one that is sparse by causing
multiple retrieves across the partition to pull one partitioned block. For this
reason it is not a good idea to make the partitioned dimension dense.
4.4.4 Dimension Order
Does the dimension order really make a difference? The answer is sometimes, but there
can be unintended consequences. There are two schools of thought with regards to the
dimension order in the outline file. The hourglass format was adopted during the early
days of Essbase. I think that I first heard about it with version 5. one of the advantages
of this format is that the last dimension in the outline is used as the anchor dimen-
sion when calculating the calculator cache. The calculator cache is used by BSo to keep
track of the level-0 member that has data during aggregations. There is not much pub-
lic information about this process other than it is in bitmap form. All of the sparse
dimensions other than the very last one are represented in the bitmap. In relation to
calculator cache, the last sparse dimension that is not represented in the bitmap is called
the anchor. making the largest dimension last reduces the bitmap size, increasing the
chance that there will be enough room in the cache for multiple bitmaps. When there is
enough memory for multiple bitmaps, the parents
and
children are in the bitmap, not
just children. The order is:
• time (if dense)
• Account (if dense)
• The remaining dense dimension ordered largest to smallest
• The sparse dimension ordered smallest to largest
Figure 4.1 shows Sample.Basic in the hourglass arrangement. In this and all subsequent
examples assume a sparse Scenario dimension; by default it is dense in Sample.Basic.
