Introduction

Cube is an analytics API system that allows users to query for data in their system based on a defined schema of exposed data points. Queries are written in JSON and must conform to a specific format which can then be interpreted by the API.

Core concepts

To talk about and write Cube Queries, you will first need to become familiar with the structure in which the data is exposed.

Cubes

The highest-level object in the Cube API is a cube. A cube can be loosely equated to a table in a relational database. That is, it is a collection of properties of a business object. Some examples of cubes are:

Each cube is made up of dimensions and measures.

Dimensions

Dimensions expose discrete pieces of infromation. If a cube is similar to a table in a relational database then a dimension is loosely equivalent to a column of that table. Dimensions can be things like:

  • Name
  • ID
  • Priority
  • Boolean flag

Measures

Measures are a little more complex than dimensions. They are fundamentally aggregates, things like "count" and "sum" applied over the data that the cube is exposing. For example, you will find in the SD Elements Cube API measures like Task.count and Project.count. These are about as simple as they sound, by themselves they will produce the total number of tasks and projects found within your organization's SD Elements instance. When combined with dimensions they will adjust their values to produce a single value per unique combination of dimensions. For example, a query for Project.name and Task.count will produce the number of tasks per project.

For those familiar with SQL: a measure is an aggregate function applied the set of records represented by the cube it is part of, with the group by clause being the full list of dimensions in the query.

Some measures have filters built into them so they will only aggregate over relevant data. For example Task.completeCount will return the number of complete Tasks instead of the total number of Tasks.

Queries

Queries are plain JSON objects representing specific desired datasets. They are sent to the REST API via HTTP requests, with the reponse becoming the dataset.

A query consists of the following properties:

  • schema: A string representing a Reporting Context. Should be one of the following:
    • all (to be deprecated)
    • activity
    • application
    • countermeasure
    • integration
    • library
    • project_survey_answers
    • training
    • trend_application
    • trend_projects
    • trend_tasks
    • user
  • dimensions: Array of strings. Each string represents a valid dimension. Strings are composed as CubeName.dimensionName.
  • measures: Array of strings. Each string represents a valid measure. As for dimensions, strings are composed as CubeName.measureName.

  • time dimensions: Array of strings. Each string represents a valid snapshot date for a Trend Report. Strings are composed as CubeName.snapshotDate. NOTE: These are exclusive to Trend Reports

  • filters: Array of filters applied to the dataset returned. Each filter is made up for the following properties: (See examples for concrete use case.)

    • member: A string representing a dimension or measure.
    • operator: A comparison operator. Should be one of the following:
      • equals
      • notEquals
      • contains
      • notContains
      • gt (strictly greater then)
      • gte (greater then or equal to)
      • lt (strictly less then)
      • lte (less then or equal to)
      • set (member value is not null, should omit values array if using this operator)
      • notSet (member value is null, should omit values array if using this operator)
      • inDateRange (takes two date string in the format YYYY-MM-DD)
      • notInDateRange (takes two date string in the format YYYY-MM-DD)
    • values: An array of usually a single string value which the member will be compared to.
  • order: A two dimensional array of strings used to order query results by the measures or dimenions in the query. Each internal array should be made of a valid measure or dimension followed by either asc or desc. An example would be order: [["Application.id", "asc"], ["Project.id", "asc"]], The order of measures and dimensions will determine the ordering priority.
  • limit: A number. Limits the number of results returned by the query. For example: 5. It is applied after the order so if you have order: [["Project.updated", "asc"]], limit: 5 then the query will return the 5 most recently updated projects.

Additional resources

The Cube API is built on top of the Cube framework. Its documentation is linked here for reference, but be aware that the SD Elements Advanced Reporting feature does not support all Cube framework functionality.

results matching ""

    No results matching ""