Written by Heather Cole, May 13th 2021
Have you been asked to pass the TM1 data to Power BI, Tableau, Qlik or another tool and are struggling to explain to the Power BI team it is not that easy because it’s multidimensional data? You are not alone. Finance team using multi-dimensional tools like Planning Analytics, TM1 know that BI tools prefer relational data. Today we will share how to connect TM1 to Power BI, without having to write complex Turbo Integrator (TI) processes!
Why is TM1 Data a Problem?
Planning Analytics, TM1 data is not really the problem, the challenge is that finance teams that use powerful multidimension data models often have data on many levels. An example is if some departments have multiple layers of management where others only have one manager. The rollup structure will contain data at many levels. This creates what is known as Ragged Hierarchies. And for years Business Intelligence Professionals have been challenged to deal with ragged hierarchies and multi-dimensional data. So, when the CFO asks you to push your TM1 data to Power BI, you might want to pause before saying no problem.
There are a few alternatives to solve the challenge of getting Planning Analytic TM1 Data to Power BI.
- You could write several complex Turbo Integrator Processes to export the data from Planning Analytics TM1 to Power BI and maintain them. But if not written correctly this could tax your TM1 System.
- You could consider not pushing to Power BI at all and leverage the new functionality of Planning Analytics Workspace. Workspace provides dynamic visualization within Planning Analytics. Check out our earlier blog What’s New in Planning Analytics Workspace version 61 and 61. If you have not seen the latest on Planning Analytics Workspace, check out Lodestar’s video on Planning Analytics.
- You could invest in TM1 Connect a powerful tool that simplifies the flow of data, hierarchies, and security to Power BI.
What is TM1 Connect?
TM1 Connect is a tool created by CarpeDatum and it simplifies the complex process of converting TM1 data which is multi-dimensional into a format and structure that relational tools like Power BI, Tableau, and Qlik understand. It can convert data cubes, system cubes and even leverage dimension member attributes which you can use for sorting and filtering in Power BI.
If you have ever tried to report off multi-dimensional data, you are familiar with the common challenge of dealing with ragged hierarchies and levels, but TM1 Connect has intelligence built in to help solve this problem. When you have hierarchies defined in TM1 and need to export it often you will lose the roll up, but not with TM1 Connect.
TM1 Connect also lets you leverage user and security groups that you have built in Planning Analytics TM1 with Power BI. TM1 connect provides direct connectivity for your reporting tools to TM1 data, using a drag and drop interface. So, it eliminates the need to create and maintain complex Turbo Integrator (TI) processes. And your Power BI users will not have to recreate the complex business logic already built in your Planning Analytics / TM1 models.
TM1 Connect Will Not Tax TM1
If you have ever used a reporting solution like Power BI directly off source data you know you run the risk of burdening the source system as you put a call to the source data tables. That why it’s recommended to grab data not from source systems but data repositories, data marts, data lakes…
So, connecting Power BI directly to TM1 or calling on turbo integrator processes could slow down your TM1 system. That’s why TM1 Connect has TM1 Connect Smart Cache. Smart Cache, caches data from TM1 for queries coming in from Power BI lessening burden on TM1. I see why they call it Smart Cache.
When passing data with TM1 Connect to Power BI it can be done in batch mode for daily updates or on demand. The easy-to-use user interface makes managing the integration of Planning Analytics TM1 and Power BI a breeze.
For more information check out our earlier blog, Why Buy TM1 Connect?
And visit the www.tm1connect.com website, but please tell them Lodestar sent you.
So, what are you waiting for? If your organization uses Power BI and Planning Analytics, let’s chat about how life and administration can be so much easier. Email us at Sales@lodestarsolutions.com for more information.