Sep 2, 2020

Connecting Power BI to TM1 for FREE with TM1py

Getting data into Power BI

It is easy to connect your data together. Power BI (PBI) includes 45+ certified connectors for Files (Excel, CSV…), Databases (SQL Server, Oracle, IBM DB2…), Power Platforms (Power BI datasets), Azure (Azure SQL databse, Azure Databricks…), Online Services (Sharepoint, Salesforce…) and Other (Web, Active Directory…).

How to connect Power BI to TM1?

To get data from other sources such as IBM Planning Analytics powered by TM1 (TM1), PBI enables you to use your own Python scripts. The easiest way to access TM1 with Python is to leverage TM1py.

How does it work?

TM1py has direct access to TM1 thanks to the TM1 REST API. Getting your cube data into a PBI dashboard can be achieved with only two steps:

  1. Convert each dimension that you need for slicing
  2. Convert a slice of cube data into a Power BI data set

With TM1py, you can get dimension information with one line of code:

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

Then to get data from a view or an MDX query, you need to use the execute_ function:

data = tm1.power_bi.execute_view(cube_name="Sales", view_name="PowerBI", private=False)

Once dimensions and cube data have been converted into PBI fields, you can start building your dashboard.

Semi-live data

The data coming from TM1 will be considered as “semi-live” data in PBI as you will have to click the Refresh button to get the latest snapshot:

TM1 to Power BI step by step

If you are interested to learn more about how to get TM1 data into Power BI, you can go through the following step by step guide:


Related content

Loading related content