Hi All,

Has anyone set up connector from Power BI to Planning Analytics successfully

I understand that there is OData feed connection in PowerBI, PA went hubless from 2.0.5 and uses OData by default now ...I am struggling to set up connection between PowerBI and TM1

I also researched about ODBO however powerBI doesn't have that connector

There are third party connectors like gateways available however it's again another tool which needs licensing, maintenance and support

Any insights on this topic would be greatly appreciated

Thanks in Advance
Ajay

------------------------------
Ajay Yendluru
------------------------------

#PlanningAnalyticswithWatson

Someone may correct me here but from what I can tell most of the visualization tools that support Odata expect it to be a straight GET call against a URL, possibly with some parameters.  Unfortunately, the TM1 REST API isn't that simple and requires you to use POST as well as parse the response into a much flatter looking dataset.

Tableau has a custom Web Data Connector that essentially allows you to build a custom webpage to perform any actions you need and then pass the response into Tableau.  I wrote a very basic WDC as a sample once, but someone with better web/javascript skills than I have could probably make something relatively easily.  The downside is that large datasets don't come over the REST API very quickly.  It's not terrible but compared to dropping to file its much slower.  One upside is you can maintain user security to the data in TM1 since you can require login on the page that you will use to select your data.

The reason I bring up the Tableau example is you can roughly perform the same thing with PowerBI.
https://docs.microsoft.com/en-us/power-bi/desktop-connector-extensibility

Also, many users just simply use a TI process to write out the data to database or file and then let their other tools read it from there.

Unfortunately there is nothing out of the box for any of these tools that I know of.

------------------------------
Robby Meyers
------------------------------

PowerBI now supports python so you could use Marius Wirtz' tm1py module. I tried it myself and got data coming back but no dimensions - needs more investigation.

------------------------------
David Usherwood
------------------------------

Original Message:
Sent: Fri June 07, 2019 07:02 PM
From: Robby Meyers
Subject: Access PA Cubes from Visualization tools

Someone may correct me here but from what I can tell most of the visualization tools that support Odata expect it to be a straight GET call against a URL, possibly with some parameters.  Unfortunately, the TM1 REST API isn't that simple and requires you to use POST as well as parse the response into a much flatter looking dataset.

Tableau has a custom Web Data Connector that essentially allows you to build a custom webpage to perform any actions you need and then pass the response into Tableau.  I wrote a very basic WDC as a sample once, but someone with better web/javascript skills than I have could probably make something relatively easily.  The downside is that large datasets don't come over the REST API very quickly.  It's not terrible but compared to dropping to file its much slower.  One upside is you can maintain user security to the data in TM1 since you can require login on the page that you will use to select your data.

The reason I bring up the Tableau example is you can roughly perform the same thing with PowerBI.
https://docs.microsoft.com/en-us/power-bi/desktop-connector-extensibility

Also, many users just simply use a TI process to write out the data to database or file and then let their other tools read it from there.

Unfortunately there is nothing out of the box for any of these tools that I know of.

------------------------------
Robby Meyers
------------------------------

PowerBI supports Python scripting so you can use Marius Wirtz' TM1PY library (https://github.com/cubewise-code/tm1py ) with Pandas to create a dataframe for PowerBI. I played with this a bit and data came through but did not pursue. 
There are also several middleware tools which present TM1 content as ODBC data sources which PowerBI/Tableau/Qlikview can consume.

------------------------------
David Usherwood
------------------------------

Original Message:
Sent: Fri June 07, 2019 07:02 PM
From: Robby Meyers
Subject: Access PA Cubes from Visualization tools

Someone may correct me here but from what I can tell most of the visualization tools that support Odata expect it to be a straight GET call against a URL, possibly with some parameters.  Unfortunately, the TM1 REST API isn't that simple and requires you to use POST as well as parse the response into a much flatter looking dataset.

Tableau has a custom Web Data Connector that essentially allows you to build a custom webpage to perform any actions you need and then pass the response into Tableau.  I wrote a very basic WDC as a sample once, but someone with better web/javascript skills than I have could probably make something relatively easily.  The downside is that large datasets don't come over the REST API very quickly.  It's not terrible but compared to dropping to file its much slower.  One upside is you can maintain user security to the data in TM1 since you can require login on the page that you will use to select your data.

The reason I bring up the Tableau example is you can roughly perform the same thing with PowerBI.
https://docs.microsoft.com/en-us/power-bi/desktop-connector-extensibility

Also, many users just simply use a TI process to write out the data to database or file and then let their other tools read it from there.

Unfortunately there is nothing out of the box for any of these tools that I know of.

------------------------------
Robby Meyers
------------------------------

Ajay,

One of the things you could do as well is use something like TM1Connect.   TM1Connect will allow you to publish TM1 views as if they were SQL tables.   It has an ODBC connector as well.   From PowerBI it just looks like an ordinary table, but TM1Connect allows you to incorporate dimensions, hierarchies, attributes as table columns.     

The connection to TM1 is direct connect as well as cached, so you can pull data in real time or you can set it up so that it pulls on a periodic basis to ease the query burden on TM1.    This is important because PowerBI and other products like Tableau and Qlik will run a series of queries against the same dataset to investigate the query metadata.    If you don't have some level of caching, it puts a pretty heavy burden on TM1 to constantly pull views just so it can get the names of the columns or distinct values of a particular column, etc.

While it does require licensing, it does not require scripting.    If you have a lot of different or changing data sources, you will find your scripting is costing you more than licensing.

Check out TM1Connect at www.TM1Connect.com.

Hope this helps!

------------------------------
Steven Garno
------------------------------