Optimizing Microsoft SQL Server Analysis Services: MDX Optimization Techniques: Segregating DISTINCT COUNT
10. Replace Sales with DISTINCT_CUSTOMERS in the Name box of the Duplicate Name dialog, as depicted in Figure 8.
Figure 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, as we shall see. To prepare the new cube further, we will dump the unneeded measures.
12. Right-click the new DISTINCT_CUSTOMERS cube.
13. Select Edit from the context menu that appears, as shown in Figure 9.
Figure 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 Figure 10.
15. Select New Measure from the context menu.
Figure 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 Figure 11.
Figure 11: Select Customer_Id from the Insert Measure Dialog
17. Click OK to accept the selection.
The Insert Measure dialog closes, and we see the new measure appear (default name of Customer Id: the underscore character is removed) in the Measures folder.
18. Click-select Customer_Id in the Measures folder, if required.
19. If necessary, click the downward arrow beneath the Cube Tree to open the Properties pane.
20. Click the Basic tab.
21. Modify the default Name of Customer Id to the following:
22. Type the following into the empty Description box, just below the Name box:
Distinct Count – Customers
23. Click the box to the right of the Aggregate Function property label (at the bottom of the Basic tab), to enable the selector.
24. Select Distinct Count in the Aggregate Function selector.
The Basic tab of the Properties pane appears, with our modifications, as shown in Figure 12.
Figure 12: Distinct Customers Measure – Properties Pane – Basic Tab