How to create multiple distinct count measures in a single cube (304215)


The information in this article applies to:

  • Microsoft SQL Server 2000 Analysis Services
This article was previously published under Q304215

SUMMARY

This step-by-step article describes how to create separate regular cubes for each distinct count measure, and then combine these regular cubes to create a single virtual cube that has multiple distinct count measures.

MORE INFORMATION

In Microsoft SQL Server 2000 Analysis Services, you can create only one distinct count measure for a regular cube. To work around this limitation, follow these steps:
  1. Create a separate regular cube for each distinct count measure.
  2. Join these regular cubes in a virtual cube to create a single cube with multiple distinct count measures.
The following example explains how to create two regular cubes, and then join them to create a virtual cube that has two distinct count measures. When you follow the steps that are mentioned in the following example, you create a virtual cube that has shared dimensions (d1, d2, and d3) and two distinct count measures (mA and mB).

Note This example assumes that you have already created the shared dimensions (d1, d2, and d3) and the tables that you must have to create the measures.

To create a regular cube (cubeA), follow these steps:
  1. In the Analysis Manager tree pane, right-click the Cubes folder, point to New Cube, and then click Wizard.
  2. On the Welcome to the Cube Wizard page, click Next.
  3. On the Select a fact table from data source page, expand your data source in the Data sources and tables box, and then click the name of the table that you want to use as the fact table for your cube.
  4. Click Next.
  5. On the Select the numeric columns that define your measures page, double-click the column in the Fact table numeric columns box to define your measure (mA), and then click Next.
  6. On the Select the dimensions for your cube page, double-click the shared dimensions d1, d2, and d3 in the Shared dimensions box, and then click Next.

    Note If a message box appears, click OK.
  7. On the Finish the Cube Wizard page, type cubeA in the Cube name box, and then click Finish.
  8. In Cube Editor, expand cubeA in the tree pane, and then expand Measures.
  9. Click the mA measure.
  10. In the Properties pane, click the Basic tab, and then click Distinct Count in the Aggregate Function list.
  11. Save cubeA, and then close Cube Editor.
To create another regular cube (cubeB), follow these steps:
  1. In the Analysis Manager tree pane, right-click the Cubes folder, point to New Cube, and then click Wizard.
  2. On the Welcome to the Cube Wizard page, click Next.
  3. On the Select a fact table from data source page, expand your data source in the Data sources and tables box, and then click the same table that was used to create the cubeA.
  4. Click Next.
  5. On the Select the numeric columns that define your measures page, double-click the column in the Fact table numeric columns box to define your measure (mB), and then click Next.
  6. On the Select the dimensions for your cube page, double-click the shared dimensions d1, d2, and d3 in the Shared dimensions box, and then click Next.

    Note If a message box appears, click OK.
  7. On the Finish the Cube Wizard page, type cubeB in the Cube name box, and then click Finish.
  8. In Cube Editor, expand cubeB in the tree pane, and then expand Measures.
  9. Click the mB measure.
  10. In the Properties pane, click the Basic tab, and then click Distinct Count in the Aggregate Function list.
  11. Save cubeB, and then close Cube Editor.
To create a virtual cube that has both distinct count measures (mA and mB), you can join the two regular cubes. To do so, follow these steps:
  1. In the Analysis Manager tree pane, right-click the Cubes folder, and then click New Virtual Cube.
  2. On the Welcome to the Virtual Cube Wizard page, click Next.
  3. On the Select the cubes to include in the virtual cube page, double-click cubeA and cubeB in the Available cubes box, and then click Next.
  4. On the Select the measures for the virtual cube page, double-click mA and mB in the Available measures box, and then click Next.
  5. On the Select the dimensions for the virtual cube page, double-click d1, d2, and d3 in the Available dimensions box, and then click Next.
  6. On the Finish the Virtual Cube Wizard page, type cubeVirtual in the Virtual cube name box, and then click Finish.

REFERENCES

For more information about the distinct count measure, visit the following Microsoft Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmeasures_0m0j.asp

Modification Type:MajorLast Reviewed:2/25/2004
Keywords:kbhowto kbwizard kbDatabase KB304215 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.