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

7. Change the first line of the query (the comment line) to the following:

— SSP11-2 Distinct Customer Dataset with Isolated DISTINCT Cube

8. Select File –> Save As …

9. Save the query as SSP11-2, to protect SSP11-1.

10. Remove the following (the first calculated member definition within the WITH clause)


   [Measures].[Distinct Customers]


   ‘COUNT(CrossJoin({[Unit Sales]},

   Descendants ([Customers].CurrentMember,

   [Customers].[Name])), ExcludeEmpty)’

11. Change the cube name in the FROM clause from



[Customer Sales]

The query is now pointed toward our new virtual cube. The modified query appears as shown in Figure 32.

Figure 32: The Modified Query

12. Execute the query using the Run Query button.

The results dataset appears as partially depicted in Figure 33.

Figure 33: The Results Dataset (Partial View)

We notice, after clicking Run Query, that the query runs and data is returned appreciably faster than the initial query we created in the first section of this article. This is because only the new DISTINCT_CUSTOMERS cube is subjected to the intensive portion of processing required to return the detailed set needed by the calculations we have put into place. The DISTINCT_CUSTOMERS cube, with only one measure, is much smaller than the Sales cube, so less processing is necessary to render the all-important distinct count for our query. As we see, the results are identical to those of our initial query, with all that remains being to format the Avg Sales per Customer calculated measure, if we choose to do so.



No comments yet... Be the first to leave a reply!