Generate MDX Queries from Cubeview

MDX is a fast and powerful way to query data from TM1 Cubes. It can play a key role when you combine your TM1 models with other technologies (e.g. Canvas, R, Python).

Example of MDX Query:

SELECT NON EMPTY TM1SubsetToSet([Account],"Gross Margin") on ROWS,   NON EMPTY TM1SubsetToSet([Period],"All Periods") on COLUMNS    FROM [General Ledger] WHERE ([Version].[1],[Year].[2013],[General Ledger Measure].[Amount],[Currency].[Local],[Region].[1],[Department].[1])

TM1py can generate MDX Queries from existing cube views in your TM1 model. So, you don’t have to write them yourself.

This can be a great way to get started with MDX or to save time and be more productive.

This article explains the steps to create MDX query from an existing cubeview. For this example we will use the Gross Margin by periods view of the General Ledger cube:

The TM1py script which generates the MDX is called 

If you haven’t downloaded the samples, you can download the script here.

You need to update the TM1 connection parameters and then the cube and view name:

Before running the script, you should make sure that TM1py is installed and that TM1py can connect to your TM1 instance.

To run the script open a command line, go to the script folder, in this example C:TM1pyTM1py-samples-masterOther and then type:

• python “generate mdx from native view.py”

TM1py is going to print in the command line the MDX query:

SELECT NON EMPTY TM1SubsetToSet([Account],"Gross Margin") on ROWS,   NON EMPTY TM1SubsetToSet([Period],"All Periods") on COLUMNS    FROM [General Ledger] WHERE ([Version].[1],[Year].[2013],[General Ledger Measure].[Amount],[Currency].[Local],[Region].[1],[Department].[1])

To print the MDX query in a file you can replace the following code:

print(nv.MDX)

with

with open('output.csv','w') as file:        print(dim, file=file)

The script should look like this:

If you run the script again in the command line:

A file containing the MDX query will be created in the same folder where the script is located: