Write for Us
The Storage Design Wizard Welcome dialog appears, as depicted in Figure 7.
Figure 7: Storage Design Wizard Welcome Dialog
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 Figure 8.
Figure 8: The Select the Type of Data Storage Dialog
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” with 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.
The percentage performance gain we type into this box option represents the targeted percentage improvement between the maximum and minimum query times. Twenty is a good starting target, and can be expected, generally, to result in adequate aggregation to ensure a significant increase in performance. Diminishing returns can result from setting the percentage unnecessarily high; the idea here is to attain a good level of balance between the increased disk space required by new aggregations and the level of overall performance.
A third radio button represents another option for getting to the best aggregation mix. With the Until I click stop setting, we can attempt to manually determine the best balance in conjunction with keeping an eye on the Performance vs. Size graph that appears to the right of the dialog. We would, ideally, determine the point at which the increase in performance begins to level off while storage continues to increase, then stop the process.
As we progress through the series, we will focus, from time to time, on the use of the parameters found within the Set aggregate options dialog, along with numerous others, to meet specific tuning objectives, or to offer options for overall improvement in a certain aspect of query or processing performance. For now, let’s get a grasp of the operation of the tool.
The Set Aggregation Options dialog, with our settings, appears as depicted in Figure 9.
Figure 9: The Set Aggregation Options Dialog
The remaining buttons include the following:
Try Idera’s new SQL admin toolset
Try Idera’s new SQL admin toolset
Try Idera’s new SQL admin toolset