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 DBJ_STORDESIGN sample cube.
Storage on the context menu that appears, as shown in Illustration 6.
Illustration 6: Selecting
Design Storage from the Context Menu
Design Wizard Welcome dialog appears, as depicted in Illustration 7.
Illustration 7: Welcome
Dialog - Storage Design Wizard
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
Click the MOLAP
radio button to select the MOLAP storage mode.
the type of data storage dialog appears as shown in Illustration 8.
Illustration 8: Select
the Type of Data Storage Dialog
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.
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.
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
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.
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.
Click the Performance
gain reaches radio button to activate the percentage box to its right.
Type in 20
for the percentage.