Page 1 of 1

Tm1 Datasource for Power BI

Posted: Fri Jan 10, 2020 8:13 am
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.

Re: Tm1 Datasource for Power BI

Posted: Fri Jan 10, 2020 9:48 am
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

Re: Tm1 Datasource for Power BI

Posted: Fri Jan 10, 2020 10:57 am
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.

Re: Tm1 Datasource for Power BI

Posted: Fri Jan 10, 2020 11:11 am
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?

Re: Tm1 Datasource for Power BI

Posted: Fri Jan 10, 2020 2:53 pm
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.

Re: Tm1 Datasource for Power BI

Posted: Mon Jan 13, 2020 7:54 am
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!

Re: Tm1 Datasource for Power BI

Posted: Mon Jan 13, 2020 8:45 am
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.

Re: Tm1 Datasource for Power BI

Posted: Sun Aug 09, 2020 2:52 am
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.

Re: Tm1 Datasource for Power BI

Posted: Sun Aug 09, 2020 11:50 am
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

Re: Tm1 Datasource for Power BI

Posted: Fri Aug 21, 2020 2:35 am
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"}