cancel
Showing results for 
Search instead for 
Did you mean: 
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!

 

13 REPLIES 13
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
Microsoft
Microsoft

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.

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!

Yggdrasill
Responsive Resident
Responsive Resident

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

Hello, 

 

I am a new bee in PBI. I have tried TM1 Restpi in GetData-> Web-> Advanced option in PBI Desktop. 'Get' Method mdx query executed succesfully and pulled the dimension elements. But now I tried to make POST request in MDX query, in fiddler it says 

"message=The content type specified is not supported. Please use JSON (application/json; charset=utf-8) as the content type."

 

Though I have changed the content-type, same error. 

 

Here is my code: 

 

let
url = "http://localhost:8000/api/v1/ExecuteMDX?$expand=Axes($select=Ordinal;$expand=Tuples($count;$expand=Members($select=Name))),Cells($select=Ordinal,Value)",
body = "{
""MDX"": ""SELECT {([kh_bgkto].[zTest1])} on 0,{([kh_ges].[test])} on 1 FROM [kh_bg] WHERE ([kh_verf].[KV1],[kh_jahr].[J02],[kh_perio].[P12C],[kh_versi].[V01],[kh_kons].[B01],[kh_konz].[KF01])""
}",
Parsed_JSON = Json.Document(body),
BuildQueryString = Uri.BuildQueryString(Parsed_JSON),
Source = Json.Document(Web.Contents(url,
[
Headers= [ #"Authorization"="Basic sddRtaW46", #"ContentType"="application/json; charset=UTF-8"], Content = Text.ToBinary(body)
]
))
in
#"Source"

 

 

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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!