Let's start with 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 has 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
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
Working with Distinct Counts in Analysis Manager
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
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 within
the context of a measure.
the Measures folder in the Tree View to the left of the Schema
Measure from the context menu.
single-line context menu appears, as shown in Illustration 5.
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.
Product_Id Appears in the Measures Folder (Circled)