Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dbradley
New Member

Connection to IBM Cognos TM1 via OData

Hi,

 

I've been struggling to connect to an IBM Cognos TM1 Database (enabled for Rest API) through Power BI and the OData Feed.

 

I see there is a third-party connector available to assist with this but really I would love to find out if it is possible to connect without an extra layer.

 

I've been tearing my hair out over it and the best I can currently achieve through power BI is an 'Unable to connect: Invalid URI: The hostname could not be parsed', or a 400/404 generic error.

 

The standard URI for the TM1 server with odata is http(s)://host:port/api/v1/

 

Any advice/thoughts would be really appreciated.

 

It may be just that the TM1 Rest API isn't fully functioning enough to support the Odata connection in Power BI.

 

Thanks!

 

19 REPLIES 19
LillyBeast
Frequent Visitor

Odata feed specifically does not work with TM1 output, because TM1 does not output absolute path in the metadata. 
However, REST API over Web.Contents works extremely well, and makes it possible to build a tm1 etl pipeline using just PBI.

Cubewise PY library is also using REST API, but can be trickier to implement in a non-desktop mode.


j91961
New Member

We have the following working:

 

https://code.cubewise.com/blog/connecting-power-bi-to-tm1-for-free-with-tm1py

 

Understandably with a custom python script you need to import the data into the dataset so no option here for directquery, but it works for free.

Likhitha6417
New Member

is  it possible to connect to IBM Cognos TM1 via OData

lstell
New Member

We have a connector that is being actively used by a number of Power BI users today. Please visit our website www.tm1connect.com to get a trail of the software and to review other content. 

 

 

lstell
New Member

we actally have a connector that will address the need to map to TM1. Please feel free to review the product and related content about TM1Connect at www.tm1connect.com

 

 

v-yuezhe-msft
Employee
Employee

Hi @dbradley,

Power BI doesn’t provide a particular Cognos TM1 connector, an idea that add Cognos TM1 cubes as a data source in Power BI Desktop has been submitted in the following link, please vote it up.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/7635186-cognos-tm1-as-a-data-sour...

Additionally, there are some workarounds in this scenario. Firstly, you can create a ODBC connection for Cognos TM1 database, then connect to the ODBC data source in Power BI Desktop. For detailed information about creating IBM Cognos TM1 data source in ODBC Data Source Administrator, please see: How to Configure cognos datasource via ODBC Connection.

Secondly, follow the instructions in this video to connect to Cognos TM1 in Power BI Desktop, you may need to use TM1 Gateway.


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Please, delete this answer!

Your first link is to a post very poorly describing connecting TO an ODBC source FROM TM1, which has since been deleted. 
The second link is a laughably bad advert for a tm1 web virtualization service, which I'm not even sure is even working any longer.

If you are looking at connecting Power BI to TM1 10.2.x or Planning Analytics 2.0, do take at look at our TM1 Gateway products. 

More details can be found at http://www.itlink.com.sg/Public/Products

Alternatively, send us an email at info@itlinkbiz.com

 

 

 

Thanks very much for the pointers, much appreciated. I'll let you know how I get on and certainly will vote the suggestion up!

So. How did it go ? Did you find out what you needed to do ? @dbradley 

Introducing DataFusion

 

Based on open-source web-technologies, Octane's DataFusion bridges the integration voids between IBM Planning Analytics and various Analytics & Business Intelligence platforms like Microsoft Power BI, Qlik and Tableau.

 

All connections formed through DataFusion are web-based and integrate entirely without the need of a centralised database. The user-interface and navigation, built keeping in mind a user friendly design and dynamic page layout, makes working with DataFusion fast and easy.

 

DataFusion blends into your business applications environment using existing views with native IBM Planning Analytics security. It can also be customised to dynamically create views and get data that you are looking for. DataFusion is attuned to address your specific business needs and can be augmented further using industry standard tools and APIs. All you need do is let Octane know and we'll get right to them.

 

DataFusion helps you deliver a true version of the truth across your enterprise ""real-time"", thereby accelerating business decision making and enhancing performance.

 

http://www.octanesolutions.com.au/datafusion/

Hi, if you are looking to report from TM1 within PowerBI, you might be interested in the connector we have built to do this. If you'd like to discuss this further please email me at Dave.kerby@simpson-associates.co.uk

Thanks
Dave
hugoberry
Responsive Resident
Responsive Resident

Not ideal, however have you tried accessing the REST interface using a Web.Contents data access function?

 

=Web.Contents("http://tm1server:8000/api/v1/Cubes/$count")

If successful at least you know you're getting somewhere. Afterwards you can use Odata.Feed with some of its parameters set 

https://msdn.microsoft.com/en-us/library/mt260868.aspx

Yup, that's the solution. We built out a whole reporting infrastructure out of REST API'ing TM1 servers and post-processing responses into normalized models.

Hi @LillyBeast We are desperatly trying to import TM1 data into Power BI via the REST API method but really struggling to find an end to end Power Query example that can do it for us.  I am trying to see end to end how we can authenticate and then load some data from a view into Power BI (Import) mode. Do we need to issue an MDX query against TM1 from Power BI?

 

We tried calling this as a test to load data but doesnt seem to import anything


Html.Table(Web.Contents("http://XXXXXXXXXX:9510/tm1web/UrlApi.jsp#Action=Open&Type=CubeViewer&Cube=General_Ledger&View=Budget...")


Any help really appreciated. Thanks

First, try getting a response back from this call: 

 

http://<your-server-here:port-here>/api/v1/ExecuteMDX?$expand=Axes($expand=Hierarchies/$ref,Tuples($expand=Members/$ref)),Cells

 

with body = "{"MDX":"insert your MDX here"}"
and header = "Authorization: Basic <base 64 encoded pwd/uname here>"

 

We are using straight TM1 api as described on the IBM resources, and as implemented by tm1py.  If you know some python, you can peek at tm1py github repo to get an idea for how some common ops are translating into the api calls.

Anonymous
Not applicable

---deleted content---

If anyone is still stuck with this we now have a TM1 Data Connector for use in Power BI. 

 

Download the custom data connector from https://micatio.com/products-and-services/tm1-power-bi-custom-connector/

Hi @micatio,

 

This lets you connect to Cloud only however I am looking for on premise TM1 Server connect to Power BI ? Do you have any connectors for that ?

 

Thanks

Archer

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors