Performance issue with ConsolidatedCountUnique

[meervdt]

I have a TM1 cube with 17 dimensions. One of the measures is calculated with a ‘ConsolidatedCountUnique’. The rule is as follows:

['NumberofClients' ] = C: ConsolidatedCountUnique(2,'Client','Cube', !DimA, !DimB, …, !MeasureDim);

The client dimension has about 14.000 elements. Whenever I drag this client dimension in the cubeviewer (or workspace advanced) it takes forever to show the individual clients.
I just tried and it took two minutes to show a department with 8 individual clients. The performance is good when not using this dimension or using another measure.

Is there a way to speed things up?

[tomok]

You're calling a function that has to query 14,000 elements in order to calculate a "unique" count and you wonder why it's slow? Really? One of my favorite axioms in TM1 is just because you can do something doesn't mean you should. These Count functions have their purpose but are notoriously slow (and rightly so considering what they have to do). Use them at your own peril.

[mvaspal]

Hi

We use the unique count for calculating the Customer count and SKU count in a quite big FMCG sales model. We have ca. 10k SKUs, and >15k Ship To customers, and the formula works surprisingly well. We always use it against quantity (which is always a non-calculated element), so instead of " !MeasureDim" as in the above example, we use simply 'Quantity'

I don't say it is incredibly fast, but it is reasonably fast. Of course when I try to query distinct sku count for all 15k customers, it takes minutes (also depends on MTQ settings), but smaller queries work quite well

[meervdt]

Thanks mvaspal for you reply.

I have tried you suggestion and it works reasonably fast on smaller queries in the cube viewer. However in TM1Web or WorkSpace Advanced it does not perform well.
What can be the reason for this?

[mvaspal]

Hi

In W.Adv, be sure you use DQM. However, it will be most probably still slower than "native" TM1, in my experience the MDX that BI creates is never so optimized as in tm1 native user interfaces.