Creating Dynamic Subsets in Applix TM1 with MDX - A Primer

Lead author: Philip Bichard.
Additional Material: Martin Findon.

About This Document

This MDX Primer is intended to serve as a simple introduction to creating dynamic dimension subsets using MDX in TM1. It focuses on giving working examples rather than trying to explain the complete theory of MDX and makes sure to cover the features most useful to TM1 users.

The example model used

In this document many examples of dynamic queries will be given. They all work (exactly as written, just copy-and-paste them into the Expression Window in the Subset Editor of the appropriate dimension to use them) on the simple set of cubes and dimensions shown below. The model is deliberately simple with no special characteristics so you should find it easy to transfer the work to your own model.

Syntax and Layout

A query can be broken over multiple lines to make it more readable. For example:
     {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)},
       Test2.([Rate Measures].[Rate]) = 19

is more readable than having the whole query in one line. The actual filter section is more easily read and modified now by having it on a line by itself.

How to create a dynamic subset with MDX

TM1 only supports a certain number of functions from the complete MDX specification. Different versions of TM1 will support different functions (and potentially support the in different ways). The valid set of functions for the version of TM1 that you are using can be found in the main Help file, under Reference Material / MDX Function Support. Before trying to write a new query, make sure it is supported, and although some unlisted functions certainly do work they must be used at your own risk.

How to create a MDX-based subset in TM1

The same basic steps can be followed with all the examples in this document. Generally the examples can be copy-and-pasted into the Expression Window of the Subset Editor of the dimension in question – often Product. Note that it is irrelevant which cube the dimension is being used by; you will get same results whether you open the dimension Subset Editor from within a cube view, the cube tree in Server Explorer or the dimension tree in Server Explorer.

TM1SubsetAll, Members, member range

The basis for many queries, this returns (almost, see below) the entire dimension, which is the equivalent of clicking the ‘All’ button in the Subset Editor.
TM1SUBSETALL( [Product] )

Note that only the final instance in the first hierarchy of members that are consolidated multiple times is returned.

The Members function, on the other hand, delivers the full dimension, duplicates included: