"The Need for Distinction"
As anyone in the realm of
business intelligence and general analysis has probably come to realize, we
often encounter the need to quantify precisely the members of various
sets of data. Those of us who have become familiar with MSAS are aware of its
capabilities when it comes to categorizing and aggregating data within the
hierarchical contexts of dimensions and levels. We can, for the most part,
readily tap these capabilities from the user interface that MSAS provides. Through
the exploitation of more advanced approaches, including the use of calculated
members / measures and MDX (multidimensional expressions), we can extend our
analysis even further, and leverage MSAS to reach far more specific objectives.
NOTE: For more information on calculated
members, see Calculated
Members: Introduction, Calculated
Members: Further Considerations and Perspectives, and Calculated
Members: Leveraging Member Properties among numerous general references
in the Database Journal MDX Essentials series.
One of the basic requirements
that come into play, at least in some form, in virtually any analysis scenario,
is the need to count the members of a set targeted for analysis. An example
might be the need to count the number of products we shipped from a given
warehouse, or group of warehouses, to a geographical location or group of
stores. This can be accomplished readily enough with the Count()
function (see Basic
Numeric Functions: The Count() Function for details about using the MDX Count()
As many of us know, Count()
does a fine job of giving us a total count. This would mean that the results
we might achieve in using Count() with products, in the scenarios
above, would represent total number of products shipped. What we would
not get, and what we might find far more useful in some situations, would be a
count of the different products that were shipped. Count(), in
providing a total number, would also be providing multiple counts of the
same products, because products will have been shipped multiple times, in
many instances. To reach our objective of counting different products,
then, we would need to count each different product shipped only once. To
count them multiple times not only misstates the number of different
products, but it also likely renders averages, and other metrics based upon
the count value, meaningless or misleading.
The word "different"
here is easily supplanted by "distinct." Moreover, as many of us
know, performing distinct counts has historically presented a challenge
in the OLAP world. Let's discuss an example that illustrates the challenge, and
then convert that challenge to an opportunity to meet a business need using the
distinct count capabilities found within MSAS.
Handling Distinct Counts via the MSAS User Interface
Let's take a look at a
scenario that illustrates a need for a distinct count, using a hypothetical
business need to add practical value. Let's say that a group of information
consumers within the FoodMart organization have approached us with a need that
they wish to meet within the Warehouse cube. The consumers want to be
able to report upon number of products within various metrics
without having to be concerned with an issue they faced with a previous system
- a scenario of "double counting" in many inventory reports that
concerned product-related transactions between warehouses and stores.
We might initially
attempt to meet the needs of the consumers with somewhat advanced MDX, but let's
try to minimize complication, while heading off many of the issues, with a
straightforward approach from within the Cube Editor component of the
MSAS user interface, Analysis Manager, first. This provides all that we
need, in many cases. (We will examine an MDX approach in the next section of
Let's start Analysis
Services and proceed with the following steps:
Expand the Analysis
Servers folder by clicking the "+" sign to its immediate
server(s) appear (my
server, MOTHER1, is depicted in some of the illustrations).
in much the same manner as shown in Illustration 1.
Illustration 1: A Sample
Set of Databases Displayed within Analysis Manager
Expand the FoodMart2000
Expand the Cubes
sample cubes appear,
as shown in Illustration 2.
Illustration 2: The
Sample Cubes in the FoodMart2000 Database
NOTE: Your databases / cube tree may differ, depending upon
the activities you have performed since the installation of MSAS (and the
simultaneous creation of the original set of sample cubes). Should you want or
need to restore the cubes to their original state, simply restore the database under
consideration. For instructions, see the MSSQL Server 2000 Books Online.
the Warehouse sample cube.
from the context menu that appears, as shown in Illustration 3.
Illustration 3: Select Edit
from the Context Menu
Editor opens. The Schema tab appears as depicted in Illustration
Illustration 4: Cube
Editor - Schema Tab for the Warehouse Sample Cube
be creating a measure in the Cube Editor to enable us to make our
distinct Product counts. Distinct Count can only exist as a
the Measures folder in the Tree View to the left of the Schema
single-line context menu appears, as shown in Illustration 5.
Measure from the context menu.
Illustration 5: Select New
Measure from the Context Menu
Measure dialog appears.
Measure dialog, selected measure circled in red, appears in Illustration
Illustration 6: Select Product_Id
from the Insert Measure Dialog
to accept the selection.
Measure dialog closes, and we see the new measure appear (default name of Product_Id)
in the Measures folder, as depicted in Illustration 7.
Illustration 7: Product_Id
Appears in the Measures Folder (Circled)