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 Illustration 4.
Illustration 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 Illustration 5.
Illustration 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 Illustration 6.
Illustration 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 Illustration 7.
Illustration 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.
10. Replace Sales with DISTINCT_CUSTOMERS in the Name box of the Duplicate Name dialog, as depicted in Illustration 8.
Illustration 8: New Name Appears in the Duplicate Name Dialog
11. Click OK to close the dialog, and to create the DISTINCT_CUSTOMERS cube.
The DISTINCT_CUSTOMERS cube appears in the cube tree.
While the dimensions of the DISTINCT_CUSTOMERS cube must mirror those in the Sales cube, the new cube will require only one measure; that measure will be a Distinct Count measure, which we will add next. To prepare the new cube further, we will then dump the unneeded measures.
12. Right-click the new DISTINCT_CUSTOMERS cube.
13. Select Edit from the context menu that appears, as shown in Illustration 9.
Illustration 9: Select Edit from the Context Menu
The Cube Editor opens.
14. Right-click the Measures folder in the Tree View to the left of the Schema tab.
A single-line context menu appears, as depicted in Illustration 10.
15. Select New Measure from the context menu.
Illustration 10: Select New Measure from the Context Menu
The Insert Measure dialog appears.
16. Click-select customer_id.
The Insert Measure dialog, selection circled in red, appears as shown in Illustration 11.
Illustration 11: Select Customer_Id from the Insert Measure Dialog