· Nov 13, 2023

Generating Pivot Table from Cube with several "Distinct Member Count" calculated measures takes a long time

Hello all,

Currently I am trying to create a pivot table based on a cube with several calculated measures that perform a distinct member count, as in . However, when I add these measures as the columns of the pivot table, the resulting row generation step can take a long time (at least five minutes). This includes row generation when performing drill-downs.

Are there any optimization techniques for this type of measure, or other ways I can improve the speed of row generation? Thanks!

Product version: IRIS 2022.1
$ZV: IRIS for UNIX (Red Hat Enterprise Linux 7 for x86-64) 2022.1.4 (Build 812U) Mon Oct 16 2023 10:26:59 EDT
Discussion (6)3
Log in or sign up to continue

Calculated measures (and other calculated members) are evaluated at runtime whether they are defined in the cube definition or elsewhere. (Measures other than calculated measures are evaluated at build time and their values are stored in the fact table, but I don't think that would be suitable for what Nick wanted to do here.)

I got an answer to this question internally and am reposting it here. From Daniel Bissex:

Using the MDX COUNT function is the slower of two options. We also have a provided Distinct plugin to address exactly this inefficiency:

This uses our %KPI() function syntax, more detailed description of the calling options can be found here:

This reduced the runtime from ~5 minutes to about two seconds! The linked documentation is for IRIS 2023.1, but these functions are available in 2022.1 as well.