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 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 this article.
Working with Distinct Counts in Analysis Manager
Let's start Analysis Services and proceed with the following steps:
1. Open Analysis Manager.
2. Expand the Analysis Servers folder by clicking the "+" sign to its immediate left.
Our server(s) appear (my server, MOTHER1, is depicted in some of the illustrations).
3. Expand the desired server.
Our database(s) appear, in much the same manner as shown in Illustration 1.
Illustration 1: A Sample Set of Databases Displayed within Analysis Manager
4. Expand the FoodMart2000 database.
5. Expand the Cubes folder.
The 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.
6. Right-click on the Warehouse sample cube.
7. Select Edit from the context menu that appears, as shown in Illustration 3.
Illustration 3: Select Edit from the Context Menu
The Cube Editor opens. The Schema tab appears as depicted in Illustration 4.
Illustration 4: Cube Editor - Schema Tab for the Warehouse Sample Cube
We will 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.
8. Right-click the Measures folder in the Tree View to the left of the Schema tab.
9. Select New Measure from the context menu.
The single-line context menu appears, as shown in Illustration 5.
Illustration 5: Select New Measure from the Context Menu
The Insert Measure dialog appears.
10. Click-select product_id.
The Insert Measure dialog, selected measure circled in red, appears in Illustration 6.
Illustration 6: Select Product_Id from the Insert Measure Dialog
11. Click OK to accept the selection.
The Insert 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)