Getting TM1 data into Power BI

TM1py v1.5 introduces two new functions to make it even easier to build beautiful Power BI (PBI) dashboards with Live TM1 data. This article is a step by step guide to get data from IBM Planning Analytics powered by TM1 (TM1) into Power BI Desktop.

In this example, we are going to build a dashboard in Power BI Desktop with data coming from the Sales cube like below. First, we will need to retrieve all dimensions and then the data:

• Power BI Desktop
• TM1py v1.5
• Python librairies (Pandas & Matplotlib)

In order to bring TM1 data into the report, you have to use python scripts as data sources for PBI. One script per dimension that you need for slicing/filtering in the dashboard and one script to bring the data from a cube view or a MDX query.

For every dataset that you want to retrieve you have to go through the below steps in PBI:

• Get data -> More... -> other -> python script

First things to do is to check your configuration before starting writing python scripts..

• Check the connectivity with the TM1 instance.
• The TM1py configuration file (config.ini) needs to be accessible from PBI (each user laptop)

The first step is to connect all dimensions that we need for slicing/filtering in the dashboard. We are going to need one script per dimension to convert each dimension as PBI fields:

To add a new python script, in PBI, click Get data:

Then select Python script:

TM1py v1.5 introduced a new function, the get_member_properties function to retrieve a slice of a dimension.

By default the function will retrieve all elements and attributes from a dimension:

business_unit = tm1.power_bi.get_member_properties(        dimension_name="Business Unit",        hierarchy_name="Business Unit",        skip_consolidations=True)

To filter the elements, you need to use member_selection. To filter attributes, you will need to use: attributes=[“attributeName”]:

   business_unit = tm1.power_bi.get_member_properties(        dimension_name="Business Unit",        hierarchy_name="Business Unit",        member_selection="{Tm1SubsetAll([Business Unit])}",        skip_consolidations=True,        attributes=["State", "City"],        skip_parents=False)

For our dashboard, we choose to retrieve all elements and all attributes and to skip consolidations. Including a few lines of python code to read the configuration file and connect to TM1, the script will look like this:

import configparserfrom TM1py import TM1Serviceconfig = configparser.ConfigParser()config.read('C:TM1pypower-biconfig.ini')with TM1Service(**config["tm1srv01"]) as tm1:    business_unit = tm1.power_bi.get_member_properties(        dimension_name="Business Unit",        hierarchy_name="Business Unit",        skip_consolidations=True)    business_unit

It is recommended to test your script in a Jupyter Notebook as it is easier to handle errors with Jupyter than PBI:

Once your script is working in Jupyter Notebook, just copy it in the PBI Python script window:

Click Ok, tick business_unit:

Before loading the data, it is recommended to check the data type that PBI assign automatically to each column. To do that click the Transform Data button:

If a type does not match you can right click on a column and go to change type. Then click the Close & Apply button and the new field should be created:

That’s it! The field Business Unit in PBI is now linked to the Business Unit dimension in TM1.

Now you need to repeat these steps for each dimension (that you need for slicing/filtering the dashboard):

Now that all dimensions are available as PBI fields, we can now write a Python script to get data from the Sales cube into a new PBI data set.

TM1py includes two functions that will retrieve data from a cube view (tm1.power_bi.execute_view) or from a MDX query (tm1.power_bi.execute_mdx) in a correct format for PBI.

The shape of the cube view / MDX query drives the shape of the data frame. Below some recommendations:

• Position all dimensions that you want to use for slicing/dicing in the rows
• Position the measures on the columns
• Position dimensions that are not relevant for the report in the titles (so they will not be included in the data frame)

In the example below, data is retrieved with an MDX query:

from TM1py import TM1Serviceimport configparserconfig = configparser.ConfigParser()config.read('C:TM1pypower-biconfig.ini')with TM1Service(**config["tm1srv01"]) as tm1:      mdx = """    SELECT    NON EMPTY    { Tm1FilterByLevel ( {Tm1SubsetAll([Business Unit])} , 0 )} *    { Tm1FilterByLevel ( {Tm1SubsetAll([Customer])} , 0 ) } *    { Tm1FilterByLevel ( {Tm1SubsetAll([Executive])} , 0 ) } *    { Tm1FilterByLevel ( {Tm1SubsetAll([Industry])} , 0 ) } *    { Tm1FilterByLevel ( {Tm1SubsetAll([Product])} , 0 ) } *    { Tm1FilterByLevel ( {Tm1SubsetAll([State])} , 0 ) } *    {TM1FILTERBYPATTERN( {TM1SUBSETALL( [Time] )}, "2019*")} *    { [Version].[Actual], [Version].[Budget], [Version].[Actual vs Budget] }     ON ROWS,    { [SalesMeasure].[Revenue], [SalesMeasure].[COGS], [SalesMeasure].[Gross Margin] } ON COLUMNS    FROM [Sales]    """    df = tm1.power_bi.execute_mdx(mdx)    df

First test the script in a Jupyter Notebook:

Then copy it into the PBI script window, click the Transform Data button to check the type for each column:

In this example, the column Business Unit was defined as number whereas it should be text. Right-click on the column header and change the type to Text:

Once the Field has been loaded, you can now rename it with your cube name:

Once you have the dimensions and the data, you can take a look at the model. PowerBI should be able to auto-detect all relations.

You can start building beautiful Power BI dashboard!

To refresh the data, just press the Refresh button in the Power BI ribbon. Power BI will execute all python scripts and then update the dashboard:

With a bit more work you can achieve this:

• Depending on the configuration, i.e. if the TM1Py config file is set to use SSO/CAM, then PBI will pull data based on the User therefore if TM1 security applies then the data retrieved will only be what the user can view natively in TM1.
• If retrieving data from a cube view, then these must be public views and TM1 security still applies
• If data is pulled into PBI by an Admin user, once the data in in PBI TM1 security is lost therefore anyone that has access to the PBI report will be able to see all the data
• PBI doesn’t deal well with ragged hierarchies, but this can be mitigated by the use/creation of additional attributes
• Anytime data is pulled from TM1 the user retrieving the data need a TM1 licence, but not once its been published
• Publishing Bi reports to the cloud: No TM1 licence required, only PBI
• The connection with TM1 is considered Semi-Live: Data is only as current as the last Refresh and Publish
• In PBI, you need to check the Python directory, go to File > Options and Settings > Options > Python scripting: