About the Series ...
This is the fourteenth tutorial article of the series, MDX
in Analysis Services. The series is designed to provide hands-on
application of the fundamentals of MDX from the perspective of MS SQL Server
2000 Analysis Services ("MSAS"); our primary focus is the
manipulation of multidimensional data sources, using MDX expressions, in a
variety of scenarios designed to meet real-world business intelligence needs.
information on the series, as well as the hardware / software requirements to
prepare for the tutorials we will undertake, please see the first lesson of
this series: MDX
Concepts and Navigation.
Note: At the time of writing, Service
Pack 3 updates
are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis
Services, and the related Books Online and Samples.
The screen shots that appear in this article were taken from a Windows 2003
Server, and may appear somewhat different from coinciding views in other
In our last tutorial, Named
Sets in MDX: An Introduction, we introduced named sets in MDX queries, focusing
on their creation through use of the WITH clause, to allow us to gain an
understanding of the general capabilities of static and dynamic
named sets. We introduced the concepts behind named sets, and then examined
the MDX syntax required to create them and to specify them for presentation in
our results. Next, we discussed the nature of static and dynamic named sets,
and then activated what we had learned through an illustrative practice example
for each of the two types. Finally, we discussed the results we obtained in
each hands-on example, illustrating the value that named sets can offer us.
this article, we introduce the concept of distinct counts, discussing why
they are useful - indeed, often required - in our organizational analysis
efforts. Throughout our session, we will describe some of the challenges that
are inherent in distinct counts, and then we will undertake practice
exercises to illustrate solutions to meet our business needs. As a part of the
practical exercises, built around a hypothetical business need, we will provide
an introduction to the approach afforded us by the MSAS user interface, and
then to an alternative approach we can take using MDX.