Tm1 Datasource for Power BI
-
- 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
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.
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.
-
- Site Admin
- Posts: 1457
- Joined: Wed May 28, 2008 9:09 am
Re: Tm1 Datasource for Power BI
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
https://www.tm1forum.com/viewtopic.php? ... rbi#p71322
-
- MVP
- Posts: 3667
- 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
That's a starting point, but really only a starting point.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
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.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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- 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
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?
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?
-
- 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
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.
Is my understanding correct? if not kindly correct me.
-
- Posts: 31
- 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
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!
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!
-
- 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
@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.
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.
-
- 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
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.
This just removes all the hassle of trying to script it using Power BI M language.
Supports Views and RAW MDX.
-
- Posts: 31
- 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
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
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
-
- 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
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"}
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"}