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 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!
Comments
I think you can define a calculated measure in the cube and it will be calculated during the cube rebuild instead of executing MDX:
https://docs.intersystems.com/iris20231/csp/docbook/DocBook.UI.Page.cls…
I actually already have the calculated measures defined in the cube itself and not in the pivot table - apologies for being unclear.
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:
https://docs.intersystems.com/iris20231/csp/documatic/%25CSP.Documatic.cls?LIBRARY=%25SYS&PRIVATE=1&CLASSNAME=%25DeepSee.PlugIn.DistinctThis 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.
Good to know, thanks for posting it here.
thanks for taking the time to post your solution to others can benefit!!