New post
Question
· Nov 13, 2023

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

#Analyzer #IRIS Analytics Architect #InterSystems IRIS BI (DeepSee)

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 https://docs.intersystems.com/iris20231/csp/docbook/DocBook.UI.Page.cls?... . 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
Sam Duncan · Nov 14, 2023

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.)

1 0
Nick Petrocelli · Nov 14, 2023

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:
https://docs.intersystems.com/iris20231/csp/documatic/%25CSP.Documatic.cls?LIBRARY=%25SYS&PRIVATE=1&CLASSNAME=%25DeepSee.PlugIn.Distinct

This uses our %KPI() function syntax, more detailed description of the calling options can be found here:
https://docs.intersystems.com/iris20231/csp/docbook/Doc.View.cls?KEY=D2RMDX_percentK

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.

3 0