Distinct Counts in Analysis Services 2005 (by William Pearson)

We have revisited distinct counts at points in other articles within both my Introduction to MSSQL Server Analysis Services and MDX Essentials series’, examining specifics with regard to appropriate use, and details of optimization within the perspective under examination in the article concerned. In this article, we will introduce distinct counts as they are managed in Analysis Services 2005. The redesign of the capability based upon the hierarchy and attribute structure that debuts in Analysis Services 2005 results in much more impressive performance and flexibility of deployment within our integrated business intelligence solutions, as many have come to report recently in blogs, forums, and other media outlets.

In this article, we will gain some hands-on exposure to distinct counts in Analysis Services 2005. Our examination of the expanded capability will include:

• A discussion surrounding the general concepts of distinct counts, including why they are useful (and often required) within the design of any robust analysis effort.
• An examination of some of the challenges inherent with using distinct counts in Analysis Services 2000, and how distinct counts have been redesigned in Analysis Services 2005 to overcome some of these shortcomings of the previous version.
• Creation of a distinct count measure within a sample cube to demonstrate the ease with which we can add distinct count capabilities to the cubes in our individual business environments.
• A discussion of other considerations that surround the use of distinct counts.

Read more...