Clever Geek Handbook

Compressing DISTINCT COUNT with ActivePivot

In ActivePivot, the most efficient way to set up DISTINCT COUNT aggregation?

For example, if I want to set up a measure that, for each cell, returns the number of individual products that contribute to that cell.

+3


source to share


1 answer


Since ActivePivot supports MDX language, you can do it in MDX. Here is an example where we define an MDX computed member that counts the individual tables contributing to a cell. (this request will run in the sample ActivePivot Sandbox 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, since post processors run in the main aggregated ActivePivot processor, while the MDX engine runs at a higher level. The post processor LEAF_COUNT is for this purpose, this is how you could declare it in your Sandbox application:

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

Because the post processor must be declared in the cube configuration, it is not as flexible as the MDX solution, which the user can apply in any hierarchy at the last minute. But again, it is more efficient, especially in the context of a high-power hierarchy.

+3


source






More articles:


All Articles