Designing Storage and Processing the Cube
Analysis Services offers multiple storage options for the data and the aggregations of data in our cubes. These types, or modes, of storage include:
- Multidimensional OLAP (MOLAP)
- Relational OLAP (ROLAP)
- Hybrid OLAP (HOLAP)
Having completed the structural design of our new cube, we will need to designate one of the above storage modes, as well as to tell Analysis Services how to generate aggregations, or to precalculate summaries, to improve query performance and to otherwise enhance cube efficiency. Next, we'll process the cube, which will load data from the data source we have designated, and which will generate the summary calculations that we have defined in our aggregation instructions.
To design storage, we will access and use the Storage Design Wizard. If we clicked Yes at the last dialog, we arrive at the Welcome to the Storage Design Wizard dialog shown in Illustration 40below. (If, alternatively, we had saved the cube and exited the process at the dialog, we will need to access the Storage Design Wizard from the Analysis Manager console. This is done by right-clicking on MyFirstCube, the selecting Design Storage from the popup menu, at which point we are greeted with the Welcome to the Storage Design Wizard.)
Illustration 40: The Design Storage Wizard Welcome Dialog
Clicking Next takes us to the Select the Type of Data Storage dialog, where radio buttons with each of the modes we discussed above are presented, along with a brief discussion of the meaning of each (more information is available in Books Online and the Reference Library). We'll select the default, MOLAP, as our data storage mode (as depicted below in Illustration 41), and then click Next.
Illustration 41: The Select the Type of Data Storage Dialog
We arrive at the Set Aggregation Options dialog, where we tell Analysis Services to provide a performance boost of up to 65 percent, without consideration for the disk space required to do so, by selecting the Performance Gain Reaches radio button and typing in "65" to indicate the desired percent, as shown below in Illustration 42. As is obvious, the tradeoff between disk space consumed and overall cube query performance can be managed with this option.
Illustration 42: The Set Aggregation Options Dialog
We can now click Start and get an idea of the Performance vs. Size considerations of our choice, the tradeoff being, of course, that performance increases require the use of greater disk space. An illustration of possible results (which may vary slightly between environments / hardware) is shown below, in Illustration 43.
Illustration 43: The Set Aggregation Options Dialog, with Performance vs. Size Chart Display
We click Next and reach the Finish the Storage Design Wizard dialog box. Under the "What do you want to do?" heading, we select the Process Now radio button, as shown in Illustration 44. The next step is to Process the Cube or to Save it for later processing, perhaps after further alterations. We will select Process Now, and click Finish.
Illustration 44: The Finish the Storage Design Wizard Dialog
The Process window that appears next allows us to monitor the processing of the cube, both in phases, activities, and statistics generated at the bottom of the window. When processing is finished, we should see the "Processing completed successfully" message at the bottom of the dialog, as shown in Illustration 45.
The cube having processed with no apparent problems, we return to the Analysis Manager console by clicking Close.
Illustration 45: The Process Window in Action