Tm1 Datasource for Power BI

Post Reply
kavitha2002
Community Contributor
Posts: 180
Joined: Sat May 05, 2018 11:48 am
OLAP Product: tm1
Version: 10.3.10100.8
Excel Version: 14

Tm1 Datasource for Power BI

Post by kavitha2002 »

Hello Everyone,

I am currently looking forward for an option to connect TM1 to Power BI tool without using any third party connector.

I read in some forum that, creating ODBC datasource using Cognos Connection Framework Manager and using ODBC connecting to Power BI. I am not sure, I have to try this option.

I would like to know anybody has come across this situation? or any other feasible solution using REST api or MDX statement?

Thanks in advance.
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: Tm1 Datasource for Power BI

Post by David Usherwood »

I didn't pursue this but it looks like you could use PowerBI's Python integration to link to TM1PY:
https://www.tm1forum.com/viewtopic.php? ... rbi#p71322
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Tm1 Datasource for Power BI

Post by lotsaram »

David Usherwood wrote: Fri Jan 10, 2020 9:48 am I didn't pursue this but it looks like you could use PowerBI's Python integration to link to TM1PY:
https://www.tm1forum.com/viewtopic.php? ... rbi#p71322
That's a starting point, but really only a starting point.
kavitha2002 wrote: Fri Jan 10, 2020 8:13 am I am currently looking forward for an option to connect TM1 to Power BI tool without using any third party connector.
Well you NEED a connector as PowerBI doesn't connect natively to TM1. Either you write your own or you look at one of the commerical alternatives already available.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
kavitha2002
Community Contributor
Posts: 180
Joined: Sat May 05, 2018 11:48 am
OLAP Product: tm1
Version: 10.3.10100.8
Excel Version: 14

Re: Tm1 Datasource for Power BI

Post by kavitha2002 »

Thanks for the post.. I have gone through link.

As I am very new to all these PowerBI, TM1PY etc, can you provide me some link or steps on how to proceed further. It would be a great help for me:)

I would like to create an own connecto to connect Power BI with TM1. I dont know python, how long will it take to create a connector Interface?
kavitha2002
Community Contributor
Posts: 180
Joined: Sat May 05, 2018 11:48 am
OLAP Product: tm1
Version: 10.3.10100.8
Excel Version: 14

Re: Tm1 Datasource for Power BI

Post by kavitha2002 »

In Python, I can use TM1PY library, and I can access the TM1 Data. In Power BI I can use Python, so that via Python Scripts I can access TM1.

Is my understanding correct? if not kindly correct me.
MariusWirtz
Posts: 29
Joined: Sat Apr 08, 2017 8:40 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: 2016

Re: Tm1 Datasource for Power BI

Post by MariusWirtz »

Hello Kavitha2002,

Regarding,
"In Python, I can use TM1PY library, and I can access the TM1 Data. In Power BI I can use Python, so that via Python Scripts I can access TM1."
Yes your understanding is correct.

In fact I already started working on TM1py features that simplify the retrieval of TM1 data for PowerBI.
I described my ideas briefly here.
https://github.com/cubewise-code/tm1py/issues/180

I think it would only take a few days of work to implement this features.

The WIP Merge Request you can find here:
https://github.com/cubewise-code/tm1py/pull/182
However, currently this feature does not have highest priority on my list. I expect to continue working on it towards end of Q1 or Q2.

If you are interested in participating and contributing code that would be great!
kavitha2002
Community Contributor
Posts: 180
Joined: Sat May 05, 2018 11:48 am
OLAP Product: tm1
Version: 10.3.10100.8
Excel Version: 14

Re: Tm1 Datasource for Power BI

Post by kavitha2002 »

@MariusWirtz Thanks for your confirmation and reply.

Based on your reply, my understanding is there is a possibility of implementing via TM1PY.

Via Python scripts in PBI, data will be live or do we need to refresh the data by writing some scheduling.

As I said am completely new on this area, so will try my level best.
rapmaster
Posts: 2
Joined: Fri Dec 23, 2016 12:57 am
OLAP Product: ssas
Version: 10.0.1
Excel Version: 0

Re: Tm1 Datasource for Power BI

Post by rapmaster »

There is actually a TM1 Power BI Data Connector available now at https://micatio.com/products-and-servic ... connector/

This just removes all the hassle of trying to script it using Power BI M language.

Supports Views and RAW MDX.
MariusWirtz
Posts: 29
Joined: Sat Apr 08, 2017 8:40 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: 2016

Re: Tm1 Datasource for Power BI

Post by MariusWirtz »

Hi @rapmaster,

thanks for bringing this up. This is an interesting project!

Can the report data be refreshed once the report is hosted in the PBI cloud?

I can see that the connector allows retrieving facts (i.e. cell values with their coordinates) from a cube.
I'm not a PBI expert, but wouldn't it be necessary to also retrieve dimensions/hierarchies with the element properties (parents, attributes, etc.), in order to allow PowerBI to create a star scheme from your data sets?

I am asking this question because when we added functionality to TM1py to retrieve TM1 data in PowerBI I found it necessary to implement two separate functions:
- one to retrieve "facts"
- one to retrieve a slice from a dimension.
https://github.com/cubewise-code/tm1py/issues/306
abcthing
Posts: 5
Joined: Thu Apr 30, 2020 1:15 am
OLAP Product: TM1
Version: 11.0.0.19
Excel Version: 2016

Re: Tm1 Datasource for Power BI

Post by abcthing »

I used Python to connect TM1 in power BI, and I think Micatio connector is a better solution now.
In my test environment, I made some changes.
1. Authorization is changed from LDAP to BASIC.
2. api/tm1/api/v1 was changed to api/v1.

It is suggested that the author add two parameters.
1. Authorization Mode: AllowedValues = { "LDAP", "BASIC"}
2. REST Mode: AllowedValues = { "api/tm1/api/v1", "api/v1"}
Post Reply