Query Editor

Use the query editor to build your queries either by using the visual drag-and-drop interface or writing them in JSON in the code view.

The query editor is split up into the following panels:

  • Top-level navigation

  • Lists of available measures and dimensions, rows, or tables (depending on selected query type)

  • Query fields used to build the query visually or a code field to enter JSON code to build the query

  • Query preview panel

Top-level Navigation

Element

Description

Schema

Lists available schemas

Query type

Switches interface view to different query types (aggregate, record, SQL)

Visual

Switches interface to visual view

Code

Switches interface to code view

Clear

Resets interface and clears current query

Undo

Reverts latest change

Redo

Cancels last undo

Query history

Lists a record of queries by type and timestamp in a separate dialog

Create Endpoint

Creates an endpoint to expose the query for consumption

Query Types and Lists

Contiamo Data Hub supports three query types: aggregate, record, and SQL.

The Query Editor interface changes depending on the selected query type.

  • For aggregate queries, the list shows Available Measures and Available Dimensions with dimension attributes.

  • For record queries, the list shows Rows.

  • For SQL queries, the list shows Tables.

Query Fields

Measures

  • Field for available measures

Rows

  • Field for available dimension attributes to list values per row

Top N Rows

  • Limit rows within column (dimension) groupings

  • n is the number of rows per grouping to limit by

  • dimensions is an array of dimension attributes to group by and limit in, these must be present in the query rows

  • orderBy is an array of objects containing information about which measures to order by, these must be present in the query

Example:

{
"n": 10,
"dimensions": ["Customer.city"],
"orderBy": [{"name":"sales","order":"Desc"}, {"name":"cost","order":"Asc"}]
}

Columns

  • Field for available dimension attributes to list values per column.

Top N Columns

  • Limits number of columns returned within dimensions present in columns

  • n is the number of rows per grouping to limit by

  • dimensions is an array of dimension attributes to group by and limit in, these must be present in the query columns

  • orderBy is an array of objects containing information about which measures to order by, these must be present in the query

Filter

Parameterizing Filters

  • Filter can also be used to parameterize the filter, which allows you to add custom filters to saved queries in the form of endpoints.

  • To parameterize a filter, add ":" before the relevant dimension attribute, for example: customer.city=:city.

Post aggregate filter

  • Filters measures after aggregation. Can also be applied to dimensions.

Order

  • Orders by column value post-aggregation

Limit

  • Limits number of rows post-aggregation

Offset

  • Skips number of rows post-aggregation

Code Examples

  • Top N Rows/Columns:

    {
    "dimensions": ["Store.city"],
    "n": 5,
    "orderBy": [{"name": "revenue", "order": "Desc"}]
    }
  • Filter:

    "Store.city = 'Liberty'"
  • Post aggregate filter:

    unitSales > 50
  • Order:

    [{"name": "revenue", "order": "Desc"}]

Note

Dimension attributes can be dragged-and-dropped into Rows or Columns. The usual approach is to drop attributes into rows, where values will be displayed per row. For a pivoted view, drop attributes into columns.