Cognos Analytics

 View Only

Connecting to REST API data

By Jason Tavoularis posted Thu July 30, 2020 09:39 AM

  

In Cognos Analytics 11.1.7 we are introducing a new way to use a web service response as a data source.

This feature is powered by Progress DataDirect Autonomous REST technology embedded within Cognos Analytics.  It’s ready-to-go within Cognos Analytics 11.1.7, on premise and on cloud, with no additional installation or license entitlement needed. The IBM Knowledge Center includes the relevant sections of the Progress documentation with all the versatile configuration options that it supports.

We’ve designed this for maximum flexibility. You’ll be able to create live data server connections to JSON end points of web services by entering a JDBC URL that refers to the REST API URL with the data you want to access, along with any configuration options you need for handling HTTP headers, pagination, authentication and other such things.

Here’s the JDBC URL I used to access  the NHL team data at https://statsapi.web.nhl.com/api/v1/teams

Here’s what the result set of data looks like in a data module:

A tabular schema is automatically inferred from the response of an API, such that REST responses are presented as tables just like any other table in Cognos Analytics data modules or Framework Manager. You can join the tables of REST API data to your corporate and personal sources of data, augment it with calculations, and apply the same filtering and data preparation actions that you can on any other source of data.

When you define a data server connection to a REST API, you can specify many optional connection properties to accommodate various types of API responses and authentication. For example, properties are available to support a broad range of OAuth 2.0 authentication flows which vary based on the security needs of each web service.

You can satisfy more advanced requirements by defining an optional configuration file referred to in the JDBC URL to the REST API. Through a configuration file you can connect to multiple URLs in a single connection, and pre-define the schema metadata, e.g., how to map the JSON data to tables and columns. Inferring a schema requires the end points to be called, which adds latency to any connection that is created, so you may notice snappier response times by defining a persisted schema of metadata.

To minimize the volume of data transferred from a REST API to the Cognos Analytics server, filters in dashboards and reports can be “pushed down” in a query to a REST end point. This is achieved by specifying dynamic URL or HTTP header rules in the configuration file. In many cases this can improve performance and reduce resource utilization, but not every web service offers the prerequisite functionality.

I should caution that while this is a generic REST API data connector that supports a broad range of responses, not every API response has analytical value and many are not designed to handle the workloads of BI applications like Cognos Analytics. REST API responses are like the wild west as there are few rules or standards that are enforced. Our solution requires that the REST implementation is using HTTP/HTTPS and responses are required to be in JSON; this is true of most API but not all of them.

I encourage you to review your REST end points before connecting to them with Cognos Analytics. What headers, API tokens and authentication mechanisms are required? Does the service assume to use pagination to return large result sets? You may find it helpful to play with the REST API using a tool such as Postman, Curl or a web browser to figure out what configuration options are needed for the connection.






#Admin


#Administration
#CognosAnalyticswithWatson
#home
#LearnCognosAnalytics
#News-BA
8 comments
321 views

Permalink

Comments

Fri June 03, 2022 04:25 PM

Is this capability updated in FP5 of 11.1.7 to improve capabilities?

Wed March 03, 2021 11:03 AM

Hi Jason

Is there a way of maintaining the metadata column headers from the json, in the data module import?  If you look at the nhl api output in a browser the json metadata is id, name etc.  But in the import it is Id, Name.  We tried importing using the API from an Oracle data source and the import removed all underlines and camel scripted the text.  ID_ORG_UNT became Id Org Unt.  RECORD _COUNT became Record Count.

Thu November 26, 2020 08:53 AM

Hi, 

I'm currently facing a problem trying to set the connexion as shown in this article.
When testing the connexion I got "XQE-JDB-0004 problem occurred finding the driver class "com.ddtek.jdbc.autorest.AutoRestDriver"
I made lots of Google research but I find few issues and I didn't succeed to fix it.
I use Cognos 11.1.

Thanks

Elodie

Thu October 29, 2020 11:10 AM

Hi Jason,
I get an error message for this. Any idea what is blocking this connection?

XQE-DS-0006 Unable to logon to the data source.
[IBM Cognos][AutoREST JDBC Driver][AutoREST]com.ibm.cognos.util6_00_000230.ak: SSL handshake failed: Connection reset
com.ibm.cognos.util6_00_000230.ak: SSL handshake failed: Connection reset
com.ibm.cognos.util6_00_000230.ak: SSL handshake failed: Connection reset
SSL handshake failed: Connection reset

Thanks
Sony

Sun October 18, 2020 05:57 AM

Hi,
I have a case with me where I am connecting to the IBM Stream using the Rest API end point http://9.114.60.72:8081/viz/taxi-bookings.html?request=SendTaxiBookingMetrics&popup=true

This is the link I received from the Team

Although the output is in Json Format when I use this link in Cognos Analytics I don’t get the desired output as a table in Cognos. Can you please help.

Tue August 25, 2020 05:42 PM

this is great !

it is working for one date; but just need to know how to pass dynamic parameter filters to API

API='https://abc/reports/users/daily?startdate=2019-09-04&enddate=2019-09-04'
dynamically change below dates like payload.
params=payload
start_date = date1
end_date = date2

Fri August 14, 2020 05:44 AM

Hi @Jason Tavoularis
Do you have an example that shows how you can dynamically change the REST API url with a specific filter value.
For example :
https://www.alphavantage.co/query?function=TIME_SERIES_MONTHLY&symbol=IBM&apikey=demo

returns monthly time series data for symbol=IBM but I want to change the symbol depending on what the user selects in my cognos dashboard.

Is this achievable in this new RESTAPI integration ?
Regards
Nick​

Thu August 13, 2020 03:22 PM

Hi Jason,
After creating the connection and testing it within the Data Server connection what is the next step to this process using the example URL in your blog?
Do you need to then load the meta data via the schema browser or is there some additional configuration required? 
Thanks