Examples and Workflows

Retrieving Metadata

An example request to obtain a list of all schemas with their cubes and available dimensions and measures.

GET /cubejs-api/v1/contexts HTTP/1.1
Accept: application/json
Authorization: YOUR_JWT

Note: the previous request, GET /cubejs-api/v1/meta, is to be deprecated and currently returns an empty list of cubes.

Making a simple REST API call with a query

POST /cubejs-api/v1/load?query={"schema": "application","dimensions":["Application.name","Application.id"],"measures":["Project.count"]} HTTP/1.1
Accept: application/json
Authorization: YOUR JWT TOKEN
HTTP/1.1 200 OK
Content-Type: application/json

{
    "query":{
        "schema": "application",
        "dimensions":[ "Application.name", "Application.id" ],
        "measures":[ "Project.count" ],
        "timezone":"UTC",
        "order":[],
        "filters":[
            {
                "member": "Organization.id",
                "operator": "equals",
                "values": [ 15, null ]
            }
        ],
        "timeDimensions":[]
    },
    "data":[
        { "Application.name": "APP1", "Application.id": 1, "Project.count": "3" },
        { "Application.name": "APP2", "Application.id": 2, "Project.count": "2" },
        { "Application.name": "APP3", "Application.id": 3, "Project.count": "2" },
        { "Application.name": "Archived App", "Application.id": 4, "Project.count": "1" }
    ],
   "lastRefreshTime":"2022-06-06T19:31:11.233Z",
   "annotation":{
        "measures":{
            "Project.count":{
                "title":"Project Count",
                "shortTitle":"Count",
                "type":"number",
                "drillMembers":[],
                "drillMembersGrouped":{ "measures": [], "dimensions": [] }
            }
        },
        "dimensions": {
            "Application.name": {
                "title": "Application Name",
                "shortTitle": "Name",
                "type": "string",
                "meta":{ "link": "url" }
            },
            "Application.id": {
                "title": "Application Id",
                "shortTitle": "Id",
                "type": "number",
                "meta":{ "link": "url" }
            }
        },
        "segments":{},
        "timeDimensions":{}
    },
    "dataSource": "default",
    "dbType": "postgres",
    "external": false,
    "slowQuery": false
}

Writing a query with filters

In order to save a query to SD Elements and have it display properly in the UI you will need to make sure the filters conform to a specific structure.

As described in the Introduction each filter is composed of a member, operator and values. That basic structure looks like this:

{
  member: 'Task.riskRelevant',
  operator: 'equals',
  values: ['true']
}

In order enable logical operator functionality each of these filters will need to be pushed into an OR block. An OR block is simply a JSON object with a single property or, the value of the or property is an array of filters. The OR blocks are then pushed into the filters array. Semantically, filters inside an OR block are joined with an OR operator while the OR blocks are joined with the AND operator.

The logical statement (filter1 OR filter2) AND filter3 can therefore be translated as follows:

filters: [
  { or: [ filter1, filter2 ] },
  { or: [ filter3 ] }
],

Note that the logical statement filter1 AND filter2 needs to be translated as follows:

filters: [
  { or: [ filter1 ] },
  { or: [ filter2 ] }
],

A full as example would then look like this:

filters: [
  {
    or: [
      {
        member: 'Task.riskRelevant',
        operator: 'equals',
        values: ['true']
      }
    ]
  },
  {
    or: [
      {
        member: 'Project.name',
        operator: 'contains',
        values: ['Java']
      },
      {
        member: 'Project.name',
        operator: 'contains',
        values: ['Spring']
      }
    ]
  }
],

Using Cube.js clients

Cube.js offers clients for plain javascript as well as various popular frameworks like React and Vue.

For more information on Cube.js clients: Cube.js Docs: Cube.js Clients

Here is an example using the plain javascript client:

import cubejs from "@cubejs-client/core";

const getJwtToken = async () => {
  (
    await axios.get("http://localhost:3000/api/v2/users/me/auth-token/", {
      headers: {
        Authorization: "Token YOUR_JWT",
      },
    })
  ).data.token;
};

const cubejsApi = cubejs(async () => await getJwtToken(), {
  apiUrl: "http://localhost:4000/cubejs-api/v1",
});

const resultSet = await cubejsApi.load({
  schema: "activity",
  dimensions: ["Activity.actorDesc", "Activity.activityClassName"],
  timeDimensions: [],
  order: {
    "Activity.actorDesc": "asc",
  },
  filters: [],
});

Writing and testing a Cube Query then saving it to SD Elements

The queries you run against the Cube API as shown in the Making a simple REST API call with a query section are not saved. Each call is independant and stateless. If you would like to save a query to SD Elements so others can view the results in the application you will need to make a POST request to the /queries endpoint. The workflow for developing a report through the API could look something like this:

  1. Write a Cube Query.
  2. Send it to the Cube API and verify the desired dataset is returned.
  3. Make any adjustments (add/remove filters, dimensions, measures, etc.).
  4. Send the adjusted query to the Cube API and verify the desired dataset is returned.
  5. Once the desired dataset has been returned make a POST request to the /queries API. In it give the new report a name, description and chart type. The query will now be saved to SD Elements and will be viewable to other users with View Analytics permissions.

results matching ""

    No results matching ""