OLAP Setup and User's Guide

Designing the Multidimensional Database

All the considerations for designing a multidimensional database in DB2 OLAP Server are covered in detail the Database Administrator's Guide, and in other chapters of this manual. The following list summarizes the steps that most often affect performance:

• Choose dimension types carefully, matching dense and sparse dimensions to the data.
• Check the block size and the number of blocks your outline will generate and tune the dimension types to get a block size in the range of 8K to 64K. Within this range, larger blocks are optimal for calculation performance, while smaller blocks are optimal for query performance.
• Consider using dynamic calculations. Select some members for dynamic calculation and check the effect this has on block size.
• Order the outline with dense dimensions first and sparse dimensions second. Order the sparse dimensions in increasing size, with the largest sparse dimension last in the outline. This lets you load data more efficiently.
• When you select an anchor dimension, select the dimension that has the most members. The number of members in the anchor dimension determines how many rows DB2 OLAP Server has to process to read or write a block of data. As the number of members (columns in the fact table) increases, the number of rows that can fit in each data block decreases. Processing fewer rows per block improves performance, so the anchor dimension should be the one with the most members. Also, the density of the anchor dimension determines how many null characters the product must store in each row of the fact table. Denser data reduces the proportion of null characters stored and improves storage efficiency.