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.