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

“But Real Techies Don’t Use Wizards …”

My experience, at least since the advent of MSSQL Server 7.0 and MSSQL Server 2000, and their complementary OLAP Services and Analysis Services, respectively, has been that many “old hands” (as if such creatures could exist in the realm of OLAP to begin with…) tend to sneer at the use of wizards. While this is often, as with many other assistance features in MSSQL Server, because it admits many more practitioners to the “club” (for better or for worse), and tends to make more open the road to performing many redundant, but once “hidden” processes, the fact is that many of these tools, such as the Storage Design Wizard, help us to perform more efficiently, with less tendency to make errors. Using a sophisticated algorithm to do its work, the Wizard is, like most such tools, highly effective when used with proper training and a sufficient understanding of MSAS structural fundamentals.

Usage patterns and other variables enter the tuning equation, for which we have additional tools and procedures, as we shall see in future articles. But for initial storage configuration, and flexible modification as the irresistible march of time affects our data environments, the Storage Design Wizard offers much in the way of effective, efficient OLAP storage management.

Practice

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 future articles where more complex scenarios will arise.

Storage Design in a Simple Cube Structure

We will begin our practice example by opening MSAS Analysis Manager, and creating a copy of the HR cube upon which to run the Storage Design Wizard. We will make a copy of the cube so as to avoid making changes to the original, in case you need to revisit it in its original state in the future. (You can always restore the database to bring back the sample cubes exactly as they appeared at installation, as well. See the Books Online if you need to take this route.

Keep in mind, as we progress, that we are working with a small cube with no pre-existing aggregations.

1. Start Analysis Manager.
    
2. Expand the Analysis Servers folder by clicking the “+” sign to its immediate left.

Our server(s) appear.

3. Expand the desired server (mine appears as MOTHER1 in the illustrations).

Our database(s) appear, in much the same manner as shown in Figure 1.

Figure 1: A Sample Set of Databases Displayed within Analysis Manager

4. Expand the FoodMart2000 database.
    

5. Expand the Cubes folder.

The sample cubes appear, as shown in Figure 2.

Figure 2: The Sample Cubes in the FoodMart2000 Database

6. Right-click on the HR sample cube.
    
7. Select Copy from the context menu that appears, as shown in Figure 3.
    

Figure 3: Select Copy from the Context Menu

8. Right-click on the Cubes folder.
    
9. Select Paste from the context menu that appears, as shown in Figure 4.
    

Figure 4: Select Paste from the Context Menu

The Duplicate Name dialog appears.

We cannot have two cubes of the same name in a given MSAS database.

10. Type the following into the Name box of the Duplicate Name dialog:

OAS01

The Duplicate Name dialog appears, with our modification, as depicted in Figure 5.

Figure 5: The Duplicate Name Dialog, with New Name

TIP: This is also an excellent way of renaming a cube, as a “rename” capability is not otherwise in the cards. Simple create a duplicate, give it the name to which you wish to rename the old cube, and then delete the old cube. (This also works for MSAS databases.)

11. Click OK to save the name change.

The new cube appears in the cube tree, among those already in place. We now have a copy of the HR cube, OAS01, upon which we can work with the Storage Design Wizard.

12. Right-click on the OAS01 sample cube.
     
13. Click Design Storage on the context menu that appears, as shown in Figure 6.

Figure 6: Select Design Storage from the Context Menu