MDX Based Subsets - Data v Subset Manipulation

[Steve Vincent]

I've only just come across this and have found the same in 9.0 and 9.5 and to be honest it does not make a lot of sense to me

SITUATION:
Simple dimension, no levels, full of about 3500 elements. Subset created called "nodal" using MDX that looks like

Code: Select all

{TM1FILTERBYLEVEL( {TM1SORT( {TM1SUBSETALL( [Organisation Breakdown] )}, ASC)}, 0)}

I know that doesn't do anything to a simple dim like this, but i kept it that way on purpose. My real life example has a far more complicated structure.

Using this subset as a source for a TI, there is a single line of code in the metadata to add the value "X" to an attribute for every value. It takes nearly 4 minutes to complete.

If i do the same test to an exact copy of the subset but with no MDX, it takes less than a second to run. Logging is not on for the attributes cube and in 9.5 neither is the audit logging.

The part i don't understand is when you change the metadata to manipulate a subset rather than changing data. In both cases then the process runs near instantly. Why is the combination of changing data values using an MDX based subset so much slower? As a result i've had to re-write some processes 20/30 times as i've tried to debug the speed issues, thinking they were my fault rather than TM1's. They now have to create multiple dummy subsets based on the MDX versions, simply so i can use them to do the data manipulation. Simple & Succinct they are not

[lotsaram]

Hi Steve,

As the MDX to define dynamic subset members can refer to any object on the server TM1 takes the easy way out, rather than building an exhaustive map of dynamic subset dependencies TM1 simply invalidates all dynamic subsets on any change to meta data or data anywhere on the server. Dynamic subsets make such sluggish data sources in comparison to static subsets as typically with each record that is processed some change is made to data or meta data somewhere on the server therefore before the next record can be processed the dynamic subset has to be re-evaluated. (Naturally this can slow things down quite a bit to say the least.)

It would be nice if TI could treat the initial state of a dynamic subset as gospel for the duration of the process but alas that's not the case.

[Steve Vincent]

cheers, that i can understand but why then does it not do the same when using a cube view that is also made up of MDX based subsets? 98% of our processes manipulate data that way (these are the first to use dims as a source directly) so surely it should be even slower in those cases?