articles >> business intelligence >> Optimizing Microsoft SQL Server Analysis Services: Enhance ...

Optimizing Microsoft SQL Server Analysis Services: Enhance Cube Processing Performance with Optimize Schema

By : William E. Pearson, III Apr 25, 2004

Page 3 / 6


11. Click OK to apply the name change.

The new cube, SSP_OPT_SCHEMA, appears in the cube tree, among those already in place. We now have a copy of the Warehouse cube, within which we can perform the steps of our practice exercise. Let’s process the new cube to “register” it with Analysis Services, and to make sure we are all in a “processed” state.

 

Process the Clone Cube

1. Right-click the new SSP_OPT_SCHEMA cube.

2. Select Process… from the context menu that appears, as depicted in Figure 4.

Figure 4: Select Process… from the Context Menu

The Process a Cube dialog appears, as depicted in Figure 5, with the processing method defaulted to Full Process (as this is the first time the cube has been processed).

Figure 5: Full Process Selected in the Process a Cube Dialog

3. Click OK to begin processing.

Processing begins. The Process viewer displays various logged events, then presents a green Processing completed successfully message, as shown in Figure 6.

Figure 6: Indication of Successful Processing Appears (Compact View)

If we examine the SQL generated in processing the cube (by highlighting either of the rows beginning with the word “Partition,” in the Process viewer, then clicking View Details), we will see that the joins under consideration are in effect.

4. Click Close to dismiss the viewer.

We will next examine representative properties of the SSP_OPT_SCHEMA cube, prior to undertaking the process for selecting the Optimize Schema option.

Review Select Property Settings

Before we pursue schema optimization further, let’s take a look at the initial settings of the Member Key Column property in our copy of the Warehouse cube. We have already discussed the fact that the Optimize Schema option changes this property. We can verify that fact with ease by examining the setting before and after the selection of the option.

1. Right-click the SSP_OPT_SCHEMA cube in the cube tree.

2. Select Edit from the context menu that appears.

The Cube Editor opens.

3. Expand the Dimensions folder in the Tree pane, by clicking the “+” sign to its left, as necessary.

4. Expand the Store dimension in the Tree pane.