ActiveForm: Performance MDX

[Mark2007]

Hi there,
the following Problem:
in a cube, there is a large Dimension, containing Projects (~1.000.000 Elements). Now, I want to built a Report via activeform, where the user can select his Region and gets all his Projects. I'm trying to do this via mdx:

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [dimName] )}, "PD*_C")}

The Performance is pretty slow, I think due to the large Dimension.

Do you have any ideas
- are there differences in Performance between different MDX-functions?

e.g. could "[dimname].[topelement].children" be faster?
etc?

Regards and thanks

Mark

[Wim Gielis]

Hello Mark,

Don't use dynamic subsets on such dimensions.
Use TI to populate a subset in a static way, either at the push of the button, either after updating the dimension.

[Steve Rowe]

Just to expand slightly on what Wim is saying.

If you have enough information in the active form to paramaterise your MDX statement then you have enough information to paramaterise a subset reference.
So pre-build the subsets and reference the subset directly in the active form.

If the user only has one option / area of responsibility then you could set that as an attribute of the user and nest it into the subset reference so they don't even need to make a selection.

Then the MDX is not running in line with your report. The pre-build subsets should be static but refreshed via TI on sufficient frequency that they remain updated.

[paulsimon]

Hi

Another major factor in Active Form performance is to take as few columns as possible from the Active Form and the others from Views. I often build special consolidations with random weights to get over the issue of it not retrieving a row if the values cancel each other out. I then just use the column with the consolidation to drive the zero suppression in the active form and find that this is a lot faster.

Recent versions of TM1 do cache the results of MDX expressions, however, I would still agree with previous comments that if you can generate a static subset then it may be faster.

From the sounds of it you may only need a subset per region rather than per user. Then you just need an attribute on the user to give their region. You can get the username via the TM1User function and then retrieve their region subset and then make up the name of the subset.

I would also look at security. Assuming that not all users can see all projects, then this is another way to limit down the list.

I have not had to cope with a dimension this size for a while. It would be interesting to know how security copes as a way of limiting the list of elements vs a subset.

You might also want to consider whether all projects in the dimension are still live. I would guess that with a million projects most are completed. Therefore you could consider a separate cube with only current projects, or a hierarchy in the dimension with only current projects.

Regards

Paul Simon