Optimizing Microsoft SQL Server Analysis Services: MDX Optimization Techniques: Considering DISTINCT COUNT
5. Execute the query using the Run Query button.
The results dataset appears as shown in Figure 17.
Figure 17: The Results Dataset – Distinction Attained
6. Save the query as SSP10-2.
The values for the new measure are in alignment with those of the measure we created in the Cube Editor.
7. Exit the MDX Sample Application and Analysis Manager when ready.
In this article, we introduced the concept of distinct counts, discussing why they are often a requirement in our analysis efforts, and in those of the information consumers whom we support. In our introduction, and throughout our examination of the MDX syntax we explored to achieve our illustrative ends, we highlighted some of the challenges that are inherent in distinct counts.
We performed practice exercises, to illustrate solutions for hypothetical business needs that called upon the use of distinct count capability, obtaining exposure to the options afforded us by the MSAS user interface, as well the MDX syntax involved with using the alternative solutions that we proposed.
We now have a basis in distinct counts that will allow us to examine more detailed nuances surrounding the capability. In subsequent articles, we will examine specific performance considerations inherent in the production of distinct counts, as well as options that are available to tune our efforts for more efficient operation. The need for distinct counts is a fact of business life, and mastery of the costs and results of this vital capability represent a unique opportunity to add another tool to our MSAS skill sets.
Copyright 2004 by the author.