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

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

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

Page 3 / 6


11. Click OK to accept the selection.

The Insert Measure dialog closes, and we see the new measure appear (default name of Product_Id) in the Measures folder, as depicted in Figure 7.

Figure 7: Product_Id Appears in the Measures Folder (Circled)
 

12. Click-select Product_Id in the Measures folder, if required.

13. If necessary, click the downward arrow beneath the Cube Tree to open the Properties pane.

14. Click the Basic tab.

15. Modify the default Name of Product Id to the following:

Product Count

16. Type the following into the empty Description box, just below the Name box:

Distinct Count - Products

17. Click the box to the right of the Aggregate Function property label (at the bottom of the Basic tab), to enable the selector.

18. Select Distinct Count in the Aggregate Function selector.

The Basic tab of the Properties pane appears, with our modifications, as shown in Figure 8.

Figure 8: Product Count Measure – Properties Pane – Basic Tab

19. Click the Data tab as if going to the Data View to perform a routine browse.

A warning briefly appears, indicating that sample data is being generated, and that the cube requires processing, as a result of our modifications. The sample data then appears, along with a static warning below it, as partially depicted in Figure 9. The warning ensures that we are aware that the data is not what it might appear to be, and that the cube must be processed to make updated, actual data available.

Figure 9: Data View (Partial and Compressed) – With “Staleness” Warning at its Foot

Let’s process the cube to activate our changes.

20. Select File ---> Save to save the cube in its modified state.

21. Select Tools ---> Process Cube to initialize the processing steps.

A message box appears, stating that the cube has no aggregations, and asking if we wish to design them at this time, as shown in Figure 10.

Figure 10: Aggregations Message Box – Just Say “No”

NOTE: The message box may not appear, if the cube has been altered with regard to aggregations since its installation as an MSAS sample. If not, the next box will appear instead, skipping this one.