Querying the data model

Ultimately, after creating a data model, you would like to ask questions to it, i.e., run queries against this data model. This page describes the concepts of querying Cube, common to all or most APIs that you will use to run these queries.

Query defaults

The following defaults apply to all queries run by Cube.

Row limit

By default, any query will return no more than 10,000 rows in the result set. It serves as a safeguard against data scraping and denial-of-service (DoS) attacks if Cube is exposed to untrusted environments.

The maximum allowed limit is 50,000 rows. You can use the CUBEJS_DB_QUERY_LIMIT environment variable to override it. You can also implement pagination to fetch more rows than the maximum limit.

Time zone

All time-based calculations performed by Cube are time zone-aware.

By default, Cube assumes that time values in your queries (e.g., in date range filters) are in the UTC time zone (opens in a new tab). Similarly, it will use the same time zone for time dimension values in result sets.

You can use the timezone option with REST API or GraphQL API to specify the time zone for a query. Also, you can use the SQL_UTILS context variable to apply the time zone conversion to dimensions that are not used as time dimensions in a query.

Additionally, note that time zones have impact on pre-aggregation matching.

Query types

Most commonly, you will run regular queries. See the table and the sections below for details on each query type.

Regular query

This is the most common type of queries. Regular queries include:

• Lists of dimensions and measures that you'd like to see in the result set.
• Optionally, filters to apply before returning the result set.
• Optionally, a row limit and an offset for the result set.

For regular queries, Cube generates the SQL for the upstream data sources that always includes all dimensions in the GROUP BY statement. See ungrouped queries if you'd like to override this behavior.

Example

See an example of a regular query using the SQL API syntax:

SELECT
  city,
  MEASURE(amount)
FROM orders
WHERE status = 'shipped'
GROUP BY 1
LIMIT 100

The same query using the REST API syntax looks as follows:

{
  "dimensions": ["orders.city"],
  "measures": ["orders.amount"],
  "filters": [
    {
      "member": "orders.status",
      "operator": "equals",
      "values": ["shipped"]
    }
  ],
  "limit": 100
}

Ungrouped query

Similarly to regular queries, ungrouped queries include lists of dimensions and measures, filters, etc. and return a result set.

However, unlike for regular queries, Cube will not add the GROUP BY statement when generating the SQL for the upstream data sources. Instead, raw results after filtering and joining will be returned without any grouping. Measures will be rendered as their sql without any aggregation. Time dimensions will be truncated by granularity as usual, however, not grouped by.

You can make a regular query ungrouped by using the ungrouped option with REST API or GraphQL API. For the SQL API, you can omit the GROUP BY statement from the SQL API query.

By default, for security purposes, ungrouped queries require primary keys of all cubes involved in a query to be added as dimensions. You can use the allow_ungrouped_without_primary_key configration option to override this.

Additionally, note that ungrouped queries have additional requirements for pre-aggregation matching.

Compare date range query

Similarly to regular queries, compare date range queries include lists of dimensions and measures, filters, etc. and return a result set.

However, unlike regular queries, they provide a convenient way to retrieve measure values for more than one date range for a time dimension. See this blog post (opens in a new tab) for more details and examples.

You can make a compare date range query by using the compareDateRange option with the REST API. For the SQL API, you can write an equivalent query using the UNION ALL statement.

Total query

Similarly to regular queries, total queries include lists of dimensions and measures, filters, etc. and return a result set.

In addition to that, they provide a convenient way to retrieve the total number of rows in the result set as if no row limit or offset are set in the query. This is useful for creating user interfaces with pagination.

You can make a total query by using the total option with the REST API. For the SQL API, you can write an equivalent query using the UNION ALL statement.