Using Distinct Count Measures in AS2000 Local Cubes

If you have a measure using Distinct Count aggregation in your source cube, the Local Cube Task will convert that measure into an equivalent calculated measure. Distinct Count aggregation is not directly supported in local cubes, but it is possible to create a calculated measure that provides the same functionality.
It’s not possible to create this Distinct Count calculated measure in all cases. Here are the limitations:

  1. There must be at least one other measure in the source cube. You do not have to include this measure in the local cube. If you don’t, it will be included as a hidden measure.
  2. There must be a dimension in the source cube that has a level with a Member Key Column that is the same as the Source Column used in the DISTINCT COUNT measure.
  3. You must be creating the cube using the Create Local Cube Syntax. The calculated measure cannot be used when using Create Global Cube Syntax. You can change the Cube Syntax option on the Cube Props tab.
There are additional limitations when creating a local cube from a virtual cube that contains more than one regular cube:
  1. The dimension having the level with the same Source Column must be in the same regular cube as the Distinct Count measure and it can only be in that cube. If that dimension is used in any of the other cubes in the virtual cube, you can do the following:

    1. Right click on the dimension in the Shared Dimensions folder in the Analysis Manager and select Copy.
    2. Paste the dimension into the Shared Dimensions folder with a different name.
    3. Add the new dimension into the regular cube that contains the Distinct Count measure. (It must not be added to any of the other regular cubes used in the virtual cube.)
    4. Add the new dimension into the virtual cube.


  2. The Distinct Count calculation is not completely accurate when creating a local cube from a virtual cube. Specifically, the Distinct Count calculation may be 1 higher than it should be in certain situations. The OLAP Client Management System will partially correct for this problem, if it finds these two additional objects in the virtual cube:

    1. A COUNT measure in the regular cube containing the DISTINCT COUNT measure that has the same Source Column as the DISTINCT COUNT measure.
    2. A COUNT measure in any of the other regular cubes used in the virtual cube that has the same Source Column as the DISTINCT COUNT measure.
For example, in the Food Mart 2000 sample database, the HR cube has a Distinct Count measure called [Number of Employees]. The Source Column for this measure is "salary"."employee_id". There already is a level that meets the second qualification - the Employee Id level of the Employees dimension has a Member Key Column of "salary"."employee_id". There is at least one other measure in the cube, so no changes need to be made for the Distinct Count calculated measure to be added to the local cube.
Here is the code that will be generated.
COMMAND (CREATE MEMBER CURRENTCUBE.Measures.[Number of Employees] AS
'
Count
(
NonEmptyCrossJoin
(
{[Measures].[Count]},
Descendants
(
[Employees].CurrentMember, ,
LEAVES
)
)
)
''
Here is an example of the code generated when creating a local cube from a virtual cube. ProductForDistinctCount is the name of the dimension that has been added in order to create the Distinct Count calculation. The [Count Products] measure is a COUNT measure in the same regular cube as the DISTINCT COUNT measure. The [Sales Count] measure is a COUNT measure in one of the regular cubes used in the virtual cube.
COMMAND (CREATE MEMBER CURRENTCUBE.Measures.[Distinct Products] AS
'
iif
(
[Measures].[Count Products] = 0,
0,
Count
(
NonEmptyCrossJoin
(
{[Measures].[Count Products]},
Descendants
(
[ProductForDistinctCount].CurrentMember, ,
LEAVES
)
)
) - iif
(
[Measures].[Sales Count] > 0,
1,
0
)
)
'
, SOLVE_ORDER = 0)
)

If a cube doesn’t have the required measure and level to convert the Distinct Count measure to a calculated measure, the Distinct Count measure and any calculated measures that depend on it are removed from the local cube.
Analysis Services only allows one Distinct Count measure in a regular cube. By using virtual cubes you can create a cube with more than one Distinct Count measure and they will all be converted into Distinct Count calculated measures in the local cube.
The inclusion of the calculated form of the Distinct Count measure in a local cube can cause slower browsing speed for that local cube. All calculations take time, and if the local cube has a large amount of data, you will notice the extra time that is taken to perform the distinct count calculation. But a decline in performance does not occur in every situation. If you keep your local cube files to a reasonable size, you might not notice any degradation of performance at all. And there are many situations in which organizations need to use Distinct Count in order to provide significant information to their users.
The calculated form of the Distinct Count measure is not created when using the Global Cube Syntax. The only way to include a measure using Distinct Count with Global Cube Syntax is to replace the Distinct Count measure in the server cube with the appropriate calculated measure. You can do that by using the same code that the OLAP Client Management System uses for creating Distinct Count measures in local cubes.

Back to Knowledge Base