Skip navigation.
spilling the beans

MDX

The example model used

developers
|

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.


How to create a dynamic subset with MDX

developers
|

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. The standard error message which means the function is genuinely not supported by your version of TM1 is, "Failed to compile expression".


How to create a static subset with MDX

developers
|

A static subset is one which never varies in its content.
This query will return the same 3 members (Discount Loan, Term Loan and Retail) every time.
{ [Product].[Discount Loan], [Product].[Term Loan], [Product].[Retail] }


How to create a MDX-based subset in TM1

developers
|

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.


What is a MDX-based dynamic subset in TM1?

A dynamic subset is one which is not a fixed, static, list but instead it is based on a query which is re-evaluated every time the subset is used. In fact, MDX could be used to create a static subset and an example is shown below, but this unlikely to be useful or common.


Syndicate content