MDX plays an even bigger role in the TM1 server since the release of IBM Planning Analytics (TM1 v11). Each time a view is refreshed or you open a subset, behind the scenes Planning Analytics for Excel (PAfE) or Planning Analytics Workspace (PAW) executes MDX queries.

The journey of the data from the TM1 server into your screen could be simplified into three main steps:

MDX is not just used in the TM1 internals, it can play a big role in your day-to-day TM1 development. As a TM1 developer, MDX can be used for:

To retrieve all employees from the Employee dimension in a process, it’s recommended to use a dynamic subset like below, to make sure you truly get all employees each time the process is executed.

Views can be created with MDX, making it easier to build a view and granting more flexibility:

Even though a TM1 rule or consolidation is the fastest way to calculate values, in certain circumstances it can be handy to create temporary calculations and elements with an MDX query. In the example below the Gross Margin % is a temporary calculation (only available in this query):

To learn more about MDX, the Cubewise EDU team has a very comprehensive training dedicated to MDX with TM1 (the Advanced MDX class), this is highly recommended to help you get started with MDX in a good way.

Arc for TM1 is the ultimate developer software for TM1 developers. On top of making it faster to write processes and rules, Arc brings many innovative features making it easier to learn and use MDX.

The Arc Subset Editor records all operations and displays each MDX expression individually. Each expression can then be edited individually, enabling you to remove or swap expressions without having to update the MDX manually:

The Arc Cube Viewer includes an MDX parser to view and edit the MDX behind each view. To create a new view from an MDX query, just create a new view in Arc and paste your query in the MDX parser:

The best way to learn MDX is through practice… That is why Arc includes a dedicated MDX module to test your MDX queries, the results will be displayed and all previous queries kept in the History tab:

If you are integrating TM1 with python, you must have heard of TM1py. TM1py is a free Python library to do basically anything you ever wanted to do programmatically with TM1 using Python.

Among other things, you can use TM1py to generate MDX queries from native views:

MDX is essential for TM1py, as it is the best (fastest and most flexible) way to consume TM1 data with python. Almost every TM1py script uses MDX in one way or another!

To avoid large hardcoded MDX queries or fussy string concatenations (to build dynamic MDX queries) and to make MDX generally more python friendly, we developed MDXpy .

It allows you to build MDX queries (for cubes and dimensions) programmatically with python. It is a huge productivity boost, as it effectively provides auto-completion and makes (MDX) syntax errors impossible.

Here is a sample of how to create a complex MDX filter expression with MDXpy:

Hello auto-completion! Goodbye MDX syntax errors!

• Analyzing your TM1 application with Arc

• Understanding relationships with your TM1 objects

• Do more with TM1 with python