Skip to content

Latest commit

 

History

History
163 lines (112 loc) · 5.9 KB

reporting_querying_database.adoc

File metadata and controls

163 lines (112 loc) · 5.9 KB
Raw
sidebar permalink summary keywords
sidebar
reporting_querying_database.html
Use Data Infrastructure Insights APIs to query the Reporting DB directly.
reporting, cognos, data warehouse, dwh, API

Access the Reporting Database via API

Data Infrastructure Insights' powerful API allows users to query the Data Infrastructure Insights Reporting database directly, without going through the Cognos Reporting environment.

Note
 This documentation refers to the Data Infrastructure Insights Reporting feature, which is available in Data Infrastructure Insights Premium Edition.

Odata

The Data Infrastructure Insights Reporting API follows the OData v4 (Open Data Protocol) standard for its querying of the Reporting database.
For more information or to learn more, check out this tutorial on OData.

All requests will start with the url https://<Data Infrastructure Insights URL>/rest/v1/dwh-management/odata

Generating an APIKey

To generate an API key, do the following:

  • Log into your Data Infrastructure Insights environment and select Admin > API Access.

  • Click “+ API Access Token”.

  • Enter a Name & Description.

  • For type, choose Data Warehouse.

  • Set Permissions as Read/Write.

  • Set a desires Expiration date.

  • Click “Save”, then copy the key and save it somewhere safe. You will not be able to access the full key later.

APIkeys are good for Sync or Async.

Direct query of tables

With the API Key in place, direct queries of the Reporting database are now possible. Long URLs may be simplified to https://…​/odata/ for display purposes rather than the full https://<Data Infrastructure Insights URL>/rest/v1/dwh-management/odata/

Try simple queries like

  • https://<Data Infrastructure Insights URL>/rest/v1/dwh-management/odata/dwh_custom

  • https://<Data Infrastructure Insights URL>/rest/v1/dwh-management/odata/dwh_inventory

  • https://<Data Infrastructure Insights URL>/rest/v1/dwh-management/odata/dwh_inventory/storage

  • https://<Data Infrastructure Insights URL>/rest/v1/dwh-management/odata/dwh_inventory/disk

  • https://…​/odata/dwh_custom/custom_queries

REST API Examples

The URL for all calls is https://<Data Infrastructure Insights URL>/rest/v1/dwh-management/odata.

  • GET /{schema}/** - Retrieves data from the Reporting Database.

Format: https://<Data Infrastructure Insights URL>/rest/v1/dwh-management/odata/<schema_name>/<query>

Example:

https://<domain>/rest/v1/dwh-management/odata/dwh_inventory/fabric?$count=true&$orderby=name

Result:

{
   "@odata.context": "$metadata#fabric",
   "@odata.count": 2,
   "value": [
       {
           "id": 851,
           "identifier": "10:00:50:EB:1A:40:3B:44",
           "wwn": "10:00:50:EB:1A:40:3B:44",
           "name": "10:00:50:EB:1A:40:3B:44",
           "vsanEnabled": "0",
           "vsanId": null,
           "zoningEnabled": "0",
           "url": "https://<domain>/web/#/assets/fabrics/941716"
       },
       {
           "id": 852,
           "identifier": "10:00:50:EB:1A:40:44:0C",
           "wwn": "10:00:50:EB:1A:40:44:0C",
           "name": "10:00:50:EB:1A:40:44:0C",
           "vsanEnabled": "0",
           "vsanId": null,
           "zoningEnabled": "0",
           "url": "https://<domain>/web/#/assets/fabrics/941836"
        }
    ]
}

Helpful Hints

Keep the following in mind when working with Reporting API queries.

  • The query payload must be a valid JSON string

  • The query payload must be contained in a single line

  • Double quotes must be escaped, i.e. \"

  • Tabs are supported as \t

  • Avoid comments

  • Lower-case table names are supported

Additionally:

  • 2 Headers are required:

    • Name “X-CloudInsights-ApiKey”

    • Attribute Value “<apikey>”

Your API key will be specific to your Data Infrastructure Insights environment.

Synchronous or Asynchronous?

By default, an API command will operate in synchronous mode, meaning that you send the request and the response is returned immediately. However, at times a query may take a long time to execute, which could lead to the request timing out. To get around this, you can execute a request asynchronously. In asynchronous mode, the request will return a URL through which the execution can be monitored. The URL will return the result when it is ready.

To execute a query in async mode, add the header Prefer: respond-async to the request. Upon successful execution, the response will contain the following headers:

Status Code: 202 (which means ACCEPTED)
preference-applied: respond-async
location: https://<Data Infrastructure Insights URL>/rest/v1/dwh-management/odata/dwh_custom/asyncStatus/<token>

Querying the location URL will return the same headers if the response is not ready yet, or will return with status 200 if the response is ready. The response content will be of type text and contains the http status of the original query and some metadata, followed by the results of the original query.

HTTP/1.1 200 OK
 OData-Version: 4.0
 Content-Type: application/json;odata.metadata=minimal
 oDataResponseSizeCounted: true

 { <JSON_RESPONSE> }

To see a list of all async queries and which of them are ready, use the following command:

GET https://<Data Infrastructure Insights URL>/rest/v1/dwh-management/odata/dwh_custom/asyncList

The response has the following format:

{
   "queries" : [
       {
           "Query": "https://<Data Infrastructure Insights URL>/rest/v1/dwh-management/odata/dwh_custom/heavy_left_join3?$count=true",
           "Location": "https://<Data Infrastructure Insights URL>/rest/v1/dwh-management/odata/dwh_custom/asyncStatus/<token>",
           "Finished": false
       }
   ]
}