Login Sign Up

IBM Cloudant Analytics & Reporting - AgenticBI Integration

Cloudant is a leading distributed Database-as-a-Service for fast growing data. AgenticBI enables visualization, reporting automation and analysis of Cloudant data.

Connecting

  1. Log in to AgenticBI and select Queries from the left sidebar.

  2. Click on New Datasource + button and select Cloudant from the list of datasources.

  3. After navigating to the New Datasource page, either use the pre-configured settings into AgenticBI's own demo Cloudant database or follow the prompts and configure the following details to set up connectivity to your own Cloudant database:

    a. Datasource Name: Enter a name for your datasource
    b. Cloudant Host Name: Enter the host name to connect to
    c. Database/Schema Name: Enter the Database/Schema name
    d. User ID: Enter the User ID to connect
    e. Password: Enter the password to connect to the database

  4. Click on the Test Connection to confirm a successful connection to the Cloudant database, hit the Save button, and start Querying.

adding-cloudant

Query

Step 1: Query using a query editor Query Editor: After connecting to the Cloudant datasource, AgenticBI will pull out a list of indexes. Using these indexes, you can write queries directly in the Query Editor, a versatile text editor that offers more advanced editing functionalities like HTTP Query Params/Cloudant JSON Query, support for multiple language modes, Cloud9QL, and more.

query-editor

Step 2: Define data execution strategy by using any of the following two options:

  • Direct Execution: Directly execute the Query on the original Datasource, without any storage in between. In this case, when a widget is displayed, it will fetch the data in real time from the underlying Datasource.

  • Non-Direct Execution: For non-direct queries, results will be stored in AgenticBI's Elastic Store. Benefits include- long-running queries, reduced load on your database, and more.

Non-direct execution can be put into action if you choose to run the Query once or at scheduled intervals. For more information, feel free to check out this documentation- Defining Data Execution Strategy

data-strategy

Step 3: Click on the Preview button to analyze the results of your Query and fine-tune the desired output, if required.

preview

The result of your Query is called Dataset. After reviewing the results, name your dataset and then hit the Save & Run button.

create-and-run

Nested Objects & Arrays

Nested objects and arrays can be queried using Cloud9QL

Query Example:

  [
    {
      "entityName":"Cloudant Nested Demo",
      "dsName":"demoCloudantNested",
      "cloudantIndex":"_all_docs",
      "c9QLFilter":"select nestedObj.a as Nested Object Val, nestedArr[0] as First Item Nested Arr",
      "overrideVals":{
        "replaceAll":true
      }
    }
  ]

Cloudant Joins

Joins enable lookups of data from other Cloudant databases to be merged in with the parent query. Example Query:

{
  "entityName":"Cloudant Join Demo",
  "dsName":"demoCloudant",
  "cloudantIndex":"_all_docs",
  "cloudantQueryParams":"descending=true&limit=1000",
  "cloudantJoin":[
    {
      "dbName":"joindbdemo",
      "lookupKeyField":"deviceId",
      "resultPrefix":"join_",
      "cloud9QLFilter":"select * limit 10"
    }
  ],
  "overrideVals":{
    "replaceAll":true
  }
}

In the above example:

  1. We pull data using _all_docs from a cloudant DB defined in demoCloudant.

  2. A list of all deviceId fields in the results is used as keys to obtain data from another Cloudant DB, joindbdemo:

    i. All fields from the lookup are added on to existing results as new columns.

    ii. The lookup is key based - deviceId in this case must correspond to the _id field in the lookup database.

    iii. The lookup results are prefixed by "join_" in the above example.

    iv. The overall results are further manipulated by the cloud9QLFilter filter.

Multiple joins/lookups example:

  {
    "entityName":"Cloudant Join Demo",
    "dsName":"demoCloudant",
    "cloudantIndex":"_all_docs",
    "cloudantQueryParams":"descending=true&limit=1000",
    "cloudantJoin":[
      {
        "dbName":"joindbdemo",
        "lookupKeyField":"deviceId",
        "resultPrefix":"joinA_",
        "cloud9QLFilter":"select * limit 10"
      },
      {
        "dbName":"joindbdemo",
        "lookupKeyField":"joinA_ipAddress",
        "resultPrefix":"joinB_",
        "cloud9QLFilter":"select hits, joinA_ipAddress, joinB_country"
      }
    ],
    "overrideVals":{
      "replaceAll":true
    }
  }

In the above example, the results after the first lookup is passed into the second lookup section.

Parameters:

Join Options Comments
url Cloudant URL to connect to. Optional - uses the URL of the datasource within the query, if this is not configured
dbName Database name to connect to. Uses the parent database defined in the datasource for the query if this is not configured.
userId Optional userId to connect with. Defaults to the userId in the datasource for the query if this is nt configured.
password Optional. Defaults to the parent datasource password to connect with, if empty.
lookupKeyField Required. The field name in the results to do a secondary lookup against.
resultPrefix Optional, but recommended. A prefix to add to the results of the lookup to be merged into the result.
c9QLFilter Optional cleansing/transformation of the results using Cloud9QL.