Connecting Power BI to TM1 for FREE with TM1py

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…).

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.

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.

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:

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:

• Power BI to TM1 step by step

• Mastering TM1 with FREE online resources
• How to run a Market Basket Analysis with TM1py and PAW
• Why it’s vital to monitor the health of your TM1 system