Optimizing Microsoft SQL Server Analysis Services: Optimization Tools: Usage-Based Optimization Wizard
15. Leaving settings at default, click OK.
Processing begins, and runs rapidly, as evidenced by the Process viewer’s presentation of processing log events in real time. The Processing cycle ends, and the success of the evolution is indicated by the appearance of the Processing completed successfully message (in green letters) at the bottom of the viewer, as shown in Figure 13.
Figure 13: Indication of Successful Processing
16. Click Close.
We are returned to the Cube Editor. Let’s manipulate the data from the Data tab, and thereby create some log entries to use in our practice example with the Usage-Based Optimization Wizard.
17. Click the Data tab.
Data appears in the default formation, ready for our manipulations.
18. Perform numerous manipulations on various dimensions and measures, leaving the Time dimension at 1997 and its underlying hierarchy.
Dragging dimensions and measures, and exploding on various items will generate Query log entries.
19. Select File —> Exit to leave the Cube Editor and return to Analysis Manager.
We are now ready to work with the Usage-Based Optimization Wizard
Putting the Usage-Based Optimization Wizard to Work
We can call the Usage-Based Optimization Wizard for a specific cube, in the context of that cube, simply by right-clicking the cube for which we wish it to go to work.
1. Right-click on the SSPerf_Warehouse cube.
2. Select Usage-Based Optimization… from the context menu that appears, as shown in Figure 14.
Figure 14: Select Usage-Based Optimization … from the Context Menu
The Welcome to the Usage-Based Optimization Wizard dialog appears, as depicted in Figure 15.
Figure 15: The Welcome to the Usage-Based Optimization Wizard Dialog
3. Click Next.
The Select a Partition dialog appears. This dialog only appears for a cube that has multiple partitions. I chose the Warehouse cube, because this sample cube already has a partition, and I wanted specifically to introduce this dialog. (Incidentally, I try to avoid the Sales cube in general, simply because a very, very large percentage of the somewhat light MSAS writing on the web focuses on the Sales cube, and I like to try to present concepts from a fresh perspective.)
The Warehouse cube, if unmodified since its installation with MSAS as a sample, consists of two partitions. We will discuss partitions in later articles, indeed we will perform the partitioning process as well as other activities surrounding partitioning many times over the series. For the purposes of this lesson, we need only consider the following facts regarding partitioning:
MSAS cubes are composed of one or more partitions.
A partition is a storage container for the data and aggregations in an MSAS cube.
Regardless of the number of partitions that make it up, a cube appears to a single, unified object from the perspective of an information consumer.
Individual partitions in the same cube can be supplied by different data sources.
Partitions can be stored independently of each other in separate physical locations.