MDX in TM1 leverages the standard MDX language (note leverages off, not uses). It is an industry standard query language for multi dimensional analysis. TM1 does not support the entire language and also adds on TM1 relevant features as well.
Keeping Dimension Subsets Up to Date
In a dimension, a great way to keep a subset up to date is to use MDX. Using MDX will ensure that changes to the dimensions will be reflected in your subset. For example, if you have a dimension with 50 “n” level elements and add a new “n” level element, then you would want to use MDX to ensure that a subset that only shows “n” level elements remains up to date with the new list of 51.
How to Add an MDX Statement to a TM1 Dimensions Subset
To add all relevant elements to a TM1 subset, do the following:
- Open the set editor for the relevant dimension
- View the Expression Window,
- Turn on Macro Recording.
- Do your manipulation of the subset and whilst doing this you will see the code being created in the Expression Window.
- When you are finished, turn off Macro Recording and attach it to the subset and
- Save the subset with the expression attached.
Then next time the subset is used it will run the code and refresh the elements included in the subset to comply with the code you have saved with it.
Note that there is a performance overhead using dynamic subsets, so don’t use them where you can use a static list. For example, there would be no need to create a dynamic subset of months. There are usually a fixed number of them, so just create a a subset with them manually and save it!
TM1 Specific Commands
The following are TM1 MDX commands and links to the relevant discussion on each (as I add them!):
TM1FilterByPattern( <set>, <pattern_str> )
TM1FilterByLevel( <set>, <level_number>)
TM1DrillDownMemeber( <set1>, <set2>|ALL [,RECURSIVE] )
TM1Member
TM1Sort( <set>, ASC|DESC )
TM1SortByIndex( <set>, ASC|DESC )
TM1SubsetAll( <dimname>)
TM1SubsetToSet
TM1TupleSize
TM1 -Specific MDX expressions
<dimension>.<subsetname>
<member>.Ancestors
In addition to these, a range of Microsoft MDX commands are supported. Please see this list on the IBM website for more information.
