Optimizing Microsoft SQL Server Analysis Services: MDX Optimization Techniques: Segregating DISTINCT COUNT

Isolating DISTINCT COUNT into a Separate Cube

We will begin performance enhancement efforts with the creation of a cube into which we will isolate the Distinct Count capabilities of the existing solution. We will manage this aspect of our solution from within the MSAS Analysis Manager.

Let’s start Analysis Services and proceed with the following steps:

1. Open Analysis Manager.

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

Our server(s) appear (my server, MOTHER1, is depicted in some of the illustrations).

3. Expand the desired server.

Our database(s) appear, in much the same manner as depicted in Figure 4.

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

4. Expand the FoodMart 2000 database.

5. Expand the Cubes folder.

The sample cubes appear, as shown in Figure 5.

Figure 5: The Sample Cubes in the FoodMart2000 Database

NOTE: Your databases / cube tree may differ, depending upon the activities you have performed since the installation of MSAS (and the simultaneous creation of the original set of sample cubes). Moreover, some of the illustrations will occasionally depict measures, dimensions, and objects that do not appear in the pristine samples. If this happens, please ignore the differences as they are unlikely to impact the steps we are undertaking as part of our practice exercise.

Should you want or need to restore the cubes to their original state, simply restore the database under consideration. For instructions, see the MSSQL Server 2000 Books Online.

Because we need a cube that matches the Sales cube, with which it will be working, as we shall see, with regard to dimensions, we will simply copy the Sales cube and rename it in the following steps:

6. Right-click the Sales sample cube.

7. Select Copy from the context menu that appears, as depicted in Figure 6.

Figure 6: Select Copy from the Context Menu

8. Right-click the Cubes folder for FoodMart 2000.

9. Select Paste from the context menu that appears, as shown in Figure 7.

 

Figure 7: Select Paste from the Context Menu

The Duplicate Name dialog appears, providing a means for the renaming of the Sales cube clone we are attempting to create. MSAS will not allow duplicate cube names within the same database.