DISTINCT COUNT aggregation with ActivePivot

Question

In ActivePivot, what is the most efficient way to configure DISTINCT COUNT aggregation?

For instance if I want to configure a measure that for each cell returns the number of distinct products that contribute to that cell.

Possible duplicate of [How can I create a "distinct count" measure with ActivePivot](https://stackoverflow.com/questions/23412641/how-can-i-create-a-distinct-count-measure-with-activepivot) — Benjamin Amelot, Jun 15 '18 at 14:45

score 0 · Answer 1 · answered Aug 19 '14 at 14:14

As ActivePivot supports the MDX language, you can do it in MDX. Here is an example where we define an MDX calculated member that counts the distinct desks contributing to a cell. (this query will run on the ActivePivot Sandbox sample application)

WITH
Member [Measures].[Desk Count] AS Count(
  Descendants(
    [Bookings].[Desk].CurrentMember,
    [Bookings].[Desk].[Desk]
  ),
  EXCLUDEEMPTY
)
SELECT NON EMPTY Hierarchize(
  DrilldownLevel(
    [Underlyings].[Products].[ALL].[AllMember]
  )
) ON ROWS
FROM [EquityDerivativesCube]
WHERE [Measures].[Desk Count]

But the most efficient way is to use a post processor, because post processors run in the core aggregation engine of ActivePivot while the MDX engine operates at a higher layer. The "LEAF_COUNT" post processor is designed for that purpose, here is how you would declare it in the Sandbox application:

<postProcessor name="DeskCount" pluginKey="LEAF_COUNT" formatter="LONG[#,###]">
    <properties>
        <entry key="leafLevels" value="Desk@Desk@Bookings" />
    </properties>
</postProcessor>

As the post processor must be declared in the configuration of the cube, it is not as flexible as the MDX solution, that a user can apply on any hierarchy at the last minute. But again it is more performant, especially in the context of hierarchies with large cardinalities.

DISTINCT COUNT aggregation with ActivePivot

1 Answers1