Forum Discussion

dbradley's avatar
dbradley
New Member
9 years ago

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 t...
hugoberry's avatar
hugoberry
Icon for Responsive Resident rankResponsive Resident
9 years ago

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

  • LillyBeast's avatar
    LillyBeast
    Frequent Visitor
    3 years ago

    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.

    • jack_kennedy01's avatar
      jack_kennedy01
      Regular Visitor
      3 years ago

      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_Forecast&AdminHost=XXXXXXXXXX&TM1Server=XXXXXXXXXX&UserName=XXXXXXXXXX&Password=XXXXXXXXXX")


      Any help really appreciated. Thanks

      • LillyBeast's avatar
        LillyBeast
        Frequent Visitor
        3 years ago

        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's avatar
    Anonymous
    Not applicable
    6 years ago

    ---deleted content---

      • archerjayden's avatar
        archerjayden
        Icon for Helper I rankHelper I
        5 years ago

        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