Optimizing Microsoft SQL Server Analysis Services: Optimization Tools: The Storage Design Wizard

To conclude this section, let’s proceed with the following steps.

19. Click Start.

The Next button will activate as soon as the design process finishes. The Set aggregation options dialog, after the process is complete, appears as shown in Figure 10.

Figure 10: The Set Aggregation Options Dialog, Results Indicated at Lower Right

We see that the Wizard has produced 12 aggregations, and reached the 20% estimated performance gain level, as indicated underneath the Performance vs. Size graph. Pressing Continue (which has become enabled) at this point will result in intermittent incremental increases above 20%, with a leveling off of the curve, so a degree of manual tweaking can be had for minimal additional effort. We will leave the results as they are, however and move ahead.

20. Click Next, to arrive at the Finish the Storage Design Wizard dialog, as shown in Figure 11.
     

Figure 11: The Finish the Storage Design Wizard Dialog

We can either save our new definition at this point, or process the cube to create the new dimensions. The definition remains stored until the design is enacted, via the processing cycle.

Let’s process the cube, and pay attention to the effects in the resulting Process Log.

21. Leaving the radio button (underneath What do you want to do now?) at its default of Process now, click Next.

Cube processing begins, and runs its course quickly, as witnessed by the Process Log window that next appears. Once processing is finished, we notice the green Processing completed successfully message appears at the bottom of the log window, as depicted in Figure 12.

Figure 12: The Process Log Window with Processing Completed Successfully Message (Window Partially Collapsed).

We note that processing duration time, together with the various steps of the process, is detailed in the log window. This presents an opportunity, in tuning evolutions, to compare process times between the current log and previous logs (all logs are captured in a database, which we will explore closely in forthcoming articles). We note, too, that the log states “cube needs to be processed” four lines from the top of the entries, an indication that the condition was noted as soon as we began processing. Keep in mind that changes planned via the Storage Design Wizard (among numerous other structural changes) require a processing run to be consummated.

22. Click Close to close the log window, once you have examined it.

We leave the Process Log window and the Cube Editor behind, and arrive in Analysis Manager, once more.

23. Delete the OAS01 cube, if desired, by right-clicking and selecting Delete from the context menu that appears.
     
24. Select File - Exit from the Main Menu in the Management Console to close Analysis Manager.

And so we see operation of the Storage Design Wizard from start to finish, for a simple cube. We will revisit the Storage Design Wizard from time to time, specifically within the context of our work with partitions, which allow us to design aggregations differently for separate “sections” of a cube, and within other articles where we can employ it to help us meet our optimization objectives.

Summary

In this article, we introduced the Storage Design Wizard, and emphasized its value as a tool within the important, and often complex, context of storage configuration for MSAS. Our objective was to introduce the tool, to allow us to undertake detailed topics surrounding considerations in tuning MSAS, including storage, aggregation, partitioning, and many others, in articles designed for those individual topics later in our series.

We explored some of the scenarios where we might use the Storage Design Wizard, and discussed the storage types that are available to us in our cube designs. We then practiced using the Storage Design Wizard within the context of a simple cube with no pre-existing aggregations, and exposed the steps involved in increasing performance through basic storage design. We processed the cube to put our changes in effect, discussing processing considerations within the scope of storage design. Finally, we discussed how the Processing Log can be used as a means of ascertaining the effects of our storage and aggregation design upon cube processing performance.

Copyright 2004 by the author.