Adding a Distinct Count Measure within the Cube
Lets consider an example of a need for a distinct count, within the familiar context of a sample cube that is available to anyone who has installed Analysis Services 2005. For the purposes of our practice exercise, we will say that client colleagues at the Adventure Works organization have asked that we assist them in working with a cube they have inherited from the original implementation team. (The team has recently turned over the system to local employees and departed the scene.)
The client has asked us to assist with the creation of a distinct count of Products within the existing fact table, where the sales data for the organization is currently captured for reporting and analysis purposes. The client representatives with whom we are working tell us that the measure will be used in various averages and other calculations, among additional possible applications. Moreover, our giving them some guidance in how to accomplish such a distinct count will come in handy as the need arises elsewhere, in other data structures and so forth.
When we examine the schema for the fact table, FactInternetSales, we note that Products are represented in the table with the ProductKey column, as shown in Illustration 1.
Illustration 1: Product Keys in the Fact Table
Because every row of the table contains a Product Key, and because multiple sales of each product occur within the time period represented by the fact table data, it is relatively easy to see that a simple count of the keys would dramatically overstate the total number of distinct, or unique, Products. We explain to our client colleagues that we can create a distinct count measure to handle needs of this sort, and propose to demonstrate the steps in the section that follows.
Procedure: Establish a Distinct Count Capability in Analysis Services
We begin our efforts by opening the cube within a project in the Business Intelligence Development Studio. I like to set up a lab environment for each of my client or research projects where I have both the respective cubes and reports involved with the engagement within an integrated solution in Visual Studio. This ensures ease in testing cube modifications through to the report layer from a single, central location, as well as providing the advantage of effective source control and numerous other conveniences. (In this particular case, I have both a copy of the sample Adventure Works DW and the AdventureWorks Sample Reports projects added into a single solution within the Business Intelligence Development Studio, where I can access all member objects from one point, the Solution Explorer.)
1. Open the Adventure Works cube from within the Solution Explorer.
2. Once the Cube Designer opens, select the Cube Structure tab, as required.
3. Select Cube -> New Measure ... from the main menu, as depicted in Illustration 2.
Illustration 2: Creating a New Measure in the Cube
4. Select Distinct count in the Usage selector atop the New Measure dialog that appears.
5. Leaving the Source table selected at the default of FactInternetSales, select ProductKey (in the Source column list) by clicking it, as shown in Illustration 3.
Illustration 3: Settings within the New Measure Dialog
6. Click OK to accept the selection and create the new measure.
7. Right-click the new Internet Sales 1 measure group that appears next, and rename it (via the Rename selection on the context menu that appears) to Product Distinct Count. Rename the measure that appears beneath it (expand the measure group, if necessary to see it) to the Distinct Products.
The renamed measure group and its sole member measure appear as depicted in Illustration 4.
Illustration 4: Newly Created Measure with Containing Measure Group ...
As we see through the results of our actions above, Analysis Services has created not only the distinct count measure itself, but it has also constructed a free-standing measure group to house it. Were we to add more distinct count measures, we would see that a separate measure group is created for each measure. Analysis Services allows only one measure with the aggregation function DISTINCT COUNT (a property which we set earlier via the New Measure dialog in our most recent steps above) in any single measure group. Moreover, because of the pronounced differences in how Analysis Services manages distinct count measures, Microsoft recommends that we avoid having a measure with any other aggregation function within a measure group containing a distinct count measure.
We can examine the properties for the new distinct count measure, and see the aggregation function setting to which I refer, by simply right-clicking the Product Distinct Count measure and selecting Properties from the context menu that appears (the Properties pane would appear within the Business Intelligence Development Studio anyway, assuming default settings, if we had selected the measure), as shown in the compressed view of Illustration 5.
Illustration 5: Properties Pane for the Newly Created Distinct Count Measure
(Compressed View of the Studio)
The next step will be to deploy the project, which we will do in preparation of verifying our handiwork via the Cube Browser.
8. Deploy the Analysis Services project within which you're working.