Introduction to MSSQL Server 2000 Analysis Services: Basic Storage Design - Page 4

Storage Design in a Simple Cube Structure

Let's take a look at the Storage Design Wizard in a practice exercise.

We will work with a simple cube structure, to minimize distraction from the steps involved. We will return to the Storage Design Wizard within the context of managing partitions, among other considerations, in a subsequent article where more complex scenarios will arise.

The Storage Design Wizard Welcome dialog appears, as depicted in Illustration 7.

The Select the type of data storage dialog appears. (Had aggregates already existed in the cube, an Aggregates already exist dialog would have appeared.) It is here that we select from the storage modes that we discussed earlier.

The Select the type of data storage dialog appears as shown in Illustration 8.

Recall from our discussion earlier that the MOLAP option places both the detail data and the aggregations in the cube. This will be a good selection for our example, so that we can focus on the further actions of the Storage Design Wizard. We will devote future articles to the characteristics and appropriate uses of the ROLAP and HOLAP options.

The Set Aggregation Options dialog appears. Within this dialog, we can exploit more of the powerful features of the MSAS Storage Design Wizard. Here, MSAS determines the combination of aggregations that give us the most "return," within the cube design we have submitted. The process is easy and needs to be accomplished only infrequently, providing that it is performed correctly at appropriate points in the life cycle of a given cube.

The Storage Design Wizard applies an 80 / 20 rule within the sophisticated algorithm that we mentioned earlier in helping us to attain, at least from a preliminary perspective, the optimal mix of aggregations within our cube structure. Because all aggregated measures in a cube are derivatives, higher-level aggregations of measures can be derived, upon demand, from lower level aggregations. The example that is cited most often is that of an aggregation that exists at intersects of the Time dimension and another dimension in the cube.

Let's take, for example, a Store by Month aggregation. This aggregation can "roll up" to Store by Quarter, Store by Year and other higher-level aggregations within MSAS. The benefit of this is that all the higher-level aggregations do not need to be stored in their "materialized" state, adding to the overall space requirement in what can be an exponential manner. Precalculated aggregations need not exist for every rolled up intersect. MSAS allows for the dynamic generation of these aggregations upon demand - in effect, they can be maintained as "virtual" aggregations.

On the Set aggregation options dialog, we can set parameters that affect both aggregation storage and query performance. In the Aggregation options section of the dialog, we can mandate restrictions on the total cube size by setting an upper limit for the amount of space that we feel we can afford to devote to the cube. The algorithm is then put to work to determine the "best mix" of aggregations that it can manage in the space we dictate.

We will leave the Estimated storage reaches selection at the default of 100 MB for this exercise. We will, however, make an adjustment to the next parameter, Performance gain reaches.