Optimizing Microsoft SQL Server Analysis Services: MDX Optimization Techniques: Segregating DISTINCT COUNT
25. Click the Advance tab of the Properties pane.
26. Ensure that Big Integer is selected for the Data Type property, as depicted in Figure 13.
Figure 13: Distinct Customers Measure – Properties Pane – Data Type Tab
27. Right-click the Unit Sales measure in the Measures folder.
28. Select Delete from the context menu that appears, as shown in Figure 14.
Figure 14: Select Delete from the Context Menu
The Confirm Measure Delete dialog appears, as depicted in Figure 15.
Figure 15: Confirm Measure Delete Dialog
29. Click Yes.
The Unit Sales measure disappears from the Measures folder.
30. Perform steps 27 through 29 above for all members of the Measures folder, except the new one we created above, Distinct Customers.
Our DISTINCT_CUSTOMERS cube will have no measures except the DISTINCT COUNT measure we have created. The idea here is that, once we have finished designing our new cube, it will contain only this single measure and an identical set of dimensions to those found in the cube whose performance we are intent upon improving (in the case of our exercise, the Sales sample cube). It is far simpler, since our Sales cube has already been designed, to clone it, as we have, and eliminate the unwanted objects to achieve our ends, than to build a new cube from scratch. We are also assured, using this approach, that the dimensions will, indeed, precisely mirror those in the original Sales cube, a requirement important to our achieving success.
Let’s clear out any remaining, unneeded objects in the DISTINCT_CUSTOMERS cube.
31. Expand the Calculated Members folder below the Measures folder in the cube tree, if necessary.
32. Using the same approach taken with each of the measures we deleted above, delete each of the calculated members.
33. Expand the Actions folder below the Measures folder in the cube tree, if necessary.
34. Using the same approach taken with each of the measures we deleted above, delete any actions that appear.
The DISTINCT_CUSTOMERS cube is now prepared structurally for its intended use. Let’s process the cube to activate our changes, and to finalize its preparation for its role in optimizing distinct count operations.
35. Select Tools —> Process Cube to initialize the processing steps, as shown in Figure 16.
Figure 16: Initiating Cube Processing …
The Save the Cube dialog appears, informing us that the cube must be saved before we can proceed.