Using Cognos API with Progress DataDirect REST Connector

[jackson.eyton]

Using Cognos 11.2, I have thus far been unable to authenticate a session with the CognosAPI using the Progress DataDirect REST connector. I have tried several iterations of OAuth with my API key. Using the following as a reference:
https://www.ibm.com/docs/en/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ag_manage.doc/autorestjdbcug.pdf

I am able to authenticate sessions and get a token using some powershell scripting. I would like to build a config file for the Progress DataDirect connector to query directly for a data module though. Has anyone done this?

[cognostechie]

Hi

I am from the Progress 4GL world and used Cognos with Progress DB back when Cognos version was 7!! I was a programmer on an ERP system written in Progress 4GL and would like to tell you two things:

1> It's not the ODBC/JDBC driver that is the problem but the fact that Cognos will generate SQL, not Progress code, and send it to your DB. Progress takes a lot of time to interpret and compile the code which is what was making the queries extremely slow.
2> Data Modules are a Cognos feature and capture data so for querying a data module from Cognos, you don't need to connect to Progress

Another way (which I used back then) is not to connect Cognos to Progress but to dump data in flat files and use that to build Cognos cubes which can then be used by Cognos Reports and Dashboards.     

[jackson.eyton]

Hi, I appreciate the response! However, I'm not concerned with query response times as of yet. Given that I am wanting to use the Progress DataDirect REST Connector in Cognos for API calls I don't plan to query loads of data. My struggle is with obtaining the authentication token from the Cognos API using Progress DataDirect AutoREST connector. So far I've been unable to make heads or tails of the documentation that I linked before, to successfully authenticate to my Cognos API.

Using the Cognos API documentation I am able to authenticate with postman or my own scripting no problem. I could pull what I need to CSV then upload the files to cognos via API calls, but I would prefer to load the data directly from the API using the Progress DataDirect AutoREST connector (thats what its for right?).

I understand this is a bit of a niche thing :-/

[cognostechie]

You seem to be missing the entire point but I wish you Good Luck !! The challenge will not be in connecting (call Progress for support) but in retrieving the data which you will probably learn later on!

[jackson.eyton]

I must be... 😬
I started working with this based on a demonstration video from pmsquare here:

They don't show accessing the cognos API but other APIs and it seems to work well.

[jackson.eyton]

This is as far as I have been able to get with the help of IBM Support, Googlefoo, reading this doc, and generative AI.

.rest file:

Code Select Expand

{
 //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}" ****Cognos API wants this session key in IBM-BA-Authorization but this was suggested here****
]
 // 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} ****I think this is wrong but could find no help****
    'Cache-Control' = 'no-cache'
 }
 }
}


I added some notes wrapped in ****'s. From this I use the following jdbc uri string:
jdbc:ibmcognos:autorest:Config=d:/REST_Config/session.rest:CustomAuthParams=MyAPIKeyHere

And this is the error I get when testing:
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=MyAPIKeyHere Caused 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=MyAPIKeyHere I/O Error URI does not specify a valid host name: d:/REST_Config/session.rest:CustomAuthParams=MyAPIKeyHere Caused 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=MyAPIKeyHere URI does not specify a valid host name: d:/REST_Config/session.rest:CustomAuthParams=MyAPIKeyHere


ANY insights would be greatly appreciated :-/

[cognostechie]

It says the host name is not valid but I see that you have provided the hostname, not localhost so can't really say what's going on. Maybe the lines are not in proper sequence?

Back then, I used Datadirect ODBC driver which was relatively much easier to configure, not JDBC or REST so maybe you can try that? Progress website has ODBC drivers.