TM1 Dynamic Subsets: Benefits and Performance


Dynamic subsets allow TM1 modelers to create a collection of dimension elements based on the following criteria:

  • Dimension hierarchy (element relationships)
  • Element name patterns
  • Element attribute values
  • Cube data values

TM1 Dynamic Subsets are a great way to minimize application maintenance. Dynamic subsets definition is expressed using MDX language and is stored with the subset. Subset expression is evaluated when subset is referenced by the TM1 server.

Dynamic subsets can significantly lower TM1 application’s maintenance and increase its flexibility because they can make turbo integrator processes, active forms and views more dynamic.

Consider an Example

Company needs to create a data entry template to collect sales and general administrative (SGA) expenses. Currently, there are fifteen general ledger accounts located under “SGA” element of the account dimension. In the future, SGA accounts list can be modified.

TM1 developer uses active form to design a data entry template. Dynamic subset is created leveraging MDX expression {[account].[SGA].Children}. In the future, when new accounts are added or removed from an SGA list, neither template nor subset will be affected by the changes. Alternatively, if static subset or list of elements were to be used in active form TM1RptRow() function, either the form or subset would’ve had to be “touched” following every change in the SGA accounts list.

Performance Considerations

Despite self-evident advantages of using dynamic subsets, there is also a disadvantage compared to static subsets. IBM technical documentation recommends limiting use of dynamic subsets as it can adversely affect performance. Performance “cost” is due to the fact that dynamic subset needs to be evaluated every time it is referenced. In some cases, performance difference between static and dynamic subsets can be significant.

Static Dynamic Subset

Static dynamic subset sounds like an oxymoron; however this is exactly the approach that can be used to combine the benefits of dynamic subset flexibility with the static subset performance. This approach is based on the premise that in most cases, dynamic subset expression does not need to be resolved at run-time; instead it can be resolved at the time of the dimension update.  As long as we update our subset at the same time and every time when its respective dimension is updated, subset will retain its dynamic nature.

We are still relying on MDX expression for the subset definition; however instead of storing it with the subset, we use it to regenerate a static subset every time dimension is updated.

Copyright © 2011 Canrock Solutions All rights reserved.
Contact Canrock Solutions: