MDX in TM1 – a Basic Introduction

MDX in TM1 leverages of 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.  To do this enable the view of the Expression Window, then 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. 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.

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] )
TM1Sort( <set>, ASC|DESC )
TM1SortByIndex( <set>, ASC|DESC )
TM1SubsetAll( <dimname>)
TM1 -Specific MDX expressions

In addition to these, a range of Microsoft MDX commands are supported.  Please see this list on the IBM website for more information.

If you like this post, please spread the love…

About John

Managing Director of Infocube. We are a management accounting consultancy that specialises in Business Analytics.

This site uses Akismet to reduce spam. Learn how your comment data is processed.