Cognos Analytics

 View Only
  • 1.  Connecting to CognosAPI via Progress DataDirect Autonomous REST Connector for JDBC

    Posted Mon March 25, 2024 11:57 AM

    I have been reading through the IBM documentation for the progress datadirect REST connector here:
    Progress DataDirect Autonomous REST Connector for JDBC User's Guide for Partners (ibm.com)

    I would like to create a config file to pull some information back from the Cognos API, however I can't seem to get the Progress DataDirect connection to authenticate. I have tried multiple iterations of the Oauth options shown in the above pdf and using an API key that I've generated for myself. I am able to collect my auth token via my API key using some powershell so I know the process can work, I just can't seem to comprehend how to apply this to theDataDirect connector. I assume others have done this before, I wasn't able to locate anything useful in my searching though.



    ------------------------------
    Jackson Eyton
    ------------------------------


  • 2.  RE: Connecting to CognosAPI via Progress DataDirect Autonomous REST Connector for JDBC
    Best Answer

    IBM Champion
    Posted Tue March 26, 2024 02:38 PM
    Edited by Austin Rexroat Tue April 02, 2024 09:41 AM

    Hi @Jackson Eyton,

    may be a bit off topic - but might lead you to the right place ...

    We are using IBM Cognos REST API with XHR requests in Dashboards:

    See source code example here: https://github.com/AMVARA-CONSULTING/cognos/blob/master/CognosRestApiXHR/CognosXHR_getReportData.js

    The trick is to use customHeaders:

    // Add Headers needed to communicate with IBM Analytics
    const customHeaders = new Headers();
    customHeaders.append('X-Ca-Xssencoded', 'true');
    customHeaders.append('X-Requested-With', 'XMLHttpRequest');

    See it in action: https://amvara-consulting.github.io/dashboard_tickets.github.io/#/

    See dashboard source: https://github.com/AMVARA-CONSULTING/dashboard_tickets

    I will be explaining the nifty details in a Webinar in two days from now, on 28.03.2024: https://community.ibm.com/community/user/businessanalytics/discussion/webinar-ibm-cognos-analytics-rest-api-superpower-building-fast-and-visually-impressive-dashboards

    Hope to see you there.

    Cheers, Ralf



    ------------------------------
    Ralf Roeber
    https://linkedin.com/in/ralf-roeber/
    ------------------------------



  • 3.  RE: Connecting to CognosAPI via Progress DataDirect Autonomous REST Connector for JDBC

    Posted Wed March 27, 2024 04:00 PM

    Thanks Ralf! This is very cool and I will definitely be attending your webinar! In my case I have verified I can authenticate and query the API using scripting, however I am hoping I can build a config file for the Progress DataDirect Autonomous REST connector that IBM includes for querying APIs directly in cognos.



    ------------------------------
    Jackson Eyton
    ------------------------------



  • 4.  RE: Connecting to CognosAPI via Progress DataDirect Autonomous REST Connector for JDBC

    Posted Wed April 17, 2024 12:26 PM

    So I have worked with IBM Support some and been reading the datadirect documentation. This is what I've come up with for a .rest file:

    {
     //An entry that defines how HTTP response status codes are processed by the driver.
     "#http":[ { "#code":200, "#action":"FAIL", "#operation":"SELECT", 
     "#match":"\"status\":\"error"", "#message":"{message}", },
     { "#code":200, "#action":"OK" }, 
     { "#code":400, "#action":"ZERO_ROWS" },
     { "#code":401, "#action":"REAUTHENTICATE" },
     { "#code":404, "#action":"ZERO_ROWS" },
     { "#code":429, "#action":"RETRY_AFTER" },
     { "#code":503, "#action":"RETRY_AFTER" }]
     //An entry for a custom authentication request.
    "#authentication" : [ 
      //Payload
      {
        "parameters": [
          {
            "name" : "CAMAPILoginKey",
            "value" :"{CustomAuthParams[1]}"
          }
        ]
      },
      //HTTP request 
      "PUT http://myCognosServer.com:9300/api/v1/session",
      //Data request credentials. "{/session}" refers to the service 
      //response from the preceding HTTP request. 
      "HEADER X-XSRF-Token={/session_key}" 
    ]
     // A GET request with custom HTTP headers
     "session":{
     "#path": "http://myCognosServer.com:9300/api/v1/session",
     "#headers":{
    	'accept' = 'application/json'
    	'IBM-BA-Authorization' = {/session_key}
    	'Cache-Control' = 'no-cache'
     }
     }
    }
    

    Here is the jdbc url I am attempting to use with it:
    jdbc:ibmcognos:autorest:Config=d:/REST_Config/session.rest:CustomAuthParams=MyAPIKeyHere

    And here is the error that I am getting:

    XQE-DS-0006 Unable to logon to the data source.[IBM Cognos][AutoREST JDBC Driver][AutoREST]I/O Error URI does not specify a valid host name: d:/REST_Config/session.rest:CustomAuthParams=AWlEMTRGQjNFRUYzNjY0NDNDQjIzRUY1M0JDNzA2QzZGOEdMtISrCD0Am/YOgDZ+WjE3XddnCaused by: com.ibm.cognos.jdbc.autorest.externals.org.apache.http.client.ClientProtocolException: URI does not specify a valid host name: d:/REST_Config/session.rest:CustomAuthParams=AWlEMTRGQjNFRUYzNjY0NDNDQjIzRUY1M0JDNzA2QzZGOEdMtISrCD0Am/YOgDZ+WjE3XddnI/O Error URI does not specify a valid host name: d:/REST_Config/session.rest:CustomAuthParams=AWlEMTRGQjNFRUYzNjY0NDNDQjIzRUY1M0JDNzA2QzZGOEdMtISrCD0Am/YOgDZ+WjE3XddnCaused by: com.ibm.cognos.jdbc.autorest.externals.org.apache.http.client.ClientProtocolException: URI does not specify a valid host name: d:/REST_Config/session.rest:CustomAuthParams=AWlEMTRGQjNFRUYzNjY0NDNDQjIzRUY1M0JDNzA2QzZGOEdMtISrCD0Am/YOgDZ+WjE3XddnURI does not specify a valid host name: d:/REST_Config/session.rest:CustomAuthParams=AWlEMTRGQjNFRUYzNjY0NDNDQjIzRUY1M0JDNzA2QzZGOEdMtISrCD0Am/YOgDZ+WjE3Xddn
    
    



    ------------------------------
    Jackson Eyton
    ------------------------------



  • 5.  RE: Connecting to CognosAPI via Progress DataDirect Autonomous REST Connector for JDBC

    Posted Thu April 18, 2024 12:47 PM

    Hi Jackson,

    I was working with the Progress DataDirect connector for a POC a couple months ago and while I didn't use custom authentication, what I found useful was to download a trial version of progress DataDirect and use that as a simple way to debug my config file. You can also get help from Progress trial support and they should be able to help provide you more guidance.

    You can go to https://www.progress.com/trial-datadirect and select Autonomous REST connector for your data source and JDBC and fill out your info to get the download. Once you have it, you can run it locally as long as you have java installed. If you check out https://www.progress.com/connectors/autonomous-rest-connector there is a demo video at the bottom of the page as well. I ran that arc.html URL in my local environment and was able to run through tests and also turn on logging to see when there were issues.

    After I downloaded it, there was an email I could use to ask support questions and I was able to additionally get on a call with a Progress SE who helped out as well.



    ------------------------------
    Jeff Rosler
    ------------------------------



  • 6.  RE: Connecting to CognosAPI via Progress DataDirect Autonomous REST Connector for JDBC

    Posted Fri April 19, 2024 09:32 AM

    Hi Jackson,

    as you handle all the authentication stuff in your configuration file you should in my optionion use the authentication method Custom. So instead of:

    jdbc:ibmcognos:autorest:Config=d:/REST_Config/session.rest:CustomAuthParams=MyAPIKeyHere

    jdbc:ibmcognos:autorest:Config=d:/REST_Config/session.rest;AuthenticationMethod=Custom;

    However the driver does NOT support PUT requests which definitely posts a problem as this seems to be the only way to authenticate within Cognos. At least the documentation has no mention about using PUT requests, only POST requests.

    Would be great if you could solve the problem. If you do, it would be great to hear of you.

    Kind regards

    Peter Mandzak



    ------------------------------
    Peter Mandzak
    ------------------------------