Syntax
Entities within the data model (e.g., cubes, views, etc.) should be placed under
the model
folder, follow naming
conventions, and be defined using a supported
syntax.
Folder structure
Data model files should be placed inside the model
folder. You can use the
schemaPath
configuration option to override the
folder name or the repositoryFactory
configuration
option to dynamically define the folder name and
data model file contents.
It's recommended to place each cube or view in a separate file, in model/cubes
and model/views
folders, respectively. Example:
model
├── cubes
│ ├── orders.yml
│ ├── products.yml
│ └── users.yml
└── views
└── revenue.yml
Model syntax
Cube supports two ways to define data model files: with YAML (opens in a new tab) or JavaScript syntax.
YAML syntax is supported since v0.31.0.
YAML data model files should have the .yml
extension, whereas JavaScript data
model files should end with .js
.
cubes:
- name: orders
sql: >
SELECT * FROM orders, line_items WHERE orders.id = line_items.order_id
Naming
Common rules apply to names of entities within the data model. All names must:
- start with a letter
- consist of letters, numbers, and underscore (
_
) symbols only
It is also recommended that names use snake case (opens in a new tab).
Good examples of names:
orders
,stripe_invoices
, orbase_payments
(cubes)opportunities
,cloud_accounts
, orarr
(views)count
,avg_price
, ortotal_amount_shipped
(measures)name
,is_shipped
, orcreated_at
(dimensions)main
,orders_by_status
, orlambda_invoices
(pre-aggregations)
Scoping
Cubes have three types of members: measures, dimensions and segments. Members have full names and short names:
- Each member can be referenced either by its full name
(
<cube_name>.<member_name>
) - or by its short name (
<member_name>
) if member exists within the same cube.
There is also a handy CUBE
context variable which references the current cube.
Important difference between same cube references is CUBE.<member-Name>
references are resolved runtime as opposed to compile time <member_name>
references.
Using the CUBE
referencing causes its alias to be used in SQL queries. For
example, to avoid ambiguity in the name
dimension in the following example, we
use the CUBE
context variable to explicitly reference the name
property from
the users
cube:
cubes:
- name: users
sql_table: users
joins:
- name: contacts
sql: "{CUBE}.contact_id = {contacts.id}"
relationship: one_to_one
dimensions:
- name: id
sql: "{CUBE}.id"
type: number
primary_key: true
- name: name
sql: "COALESCE({CUBE.name}, {contacts.name})"
type: string
- name: contacts
sql_table: contacts
dimensions:
- name: id
sql: "{CUBE}.id"
type: number
primary_key: true
- name: name
sql: "{CUBE}.name"
type: string
Referencing a foreign cube in the sql
parameter instructs Cube to build an
implicit join to this cube. Using the data model above, we'll use a query as an
example:
{
"dimensions": ["users.name"]
}
The resulting SQL output from [/v1/sql
][ref-restapi-sql] would look like:
SELECT COALESCE("users".name, "contacts".name) "users__name"
FROM users "users"
LEFT JOIN contacts "contacts"
ON "users".contact_id = "contacts".id
SQL blocks
Cube requires you to provide SQL snippets in sql
and sql_table
parameters.
The SQL code you provide in these parameters should match your database SQL
dialect. Cube enables you to reference existing objects in SQL blocks with
Python-like {}
interpolation syntax in YAML models and ${}
interpolation
operator in JS models.
cubes:
- name: orders
sql_table: public.orders
measures:
- name: total_amount_shipped
sql: "{CUBE.amount}"
type: sum
filters:
- sql: "{CUBE.status} = 'shipped'"
dimensions:
- name: status
sql: "{CUBE}.status"
type: string
- name: amount
sql: "{line_items.total_amount}"
type: number
sub_query: true
Referring to objects
To write reusable data models, it is important to be able to reference existing objects, such as measures or dimensions.
In most cases you can just list objects as-is.
cubes:
- name: orders
sql_table: orders
pre_aggregations:
- name: orders_by_status
dimensions:
- CUBE.status
measures:
- CUBE.count
Inside SQL blocks you need to use interpolation syntax, {}
for YAML models and ${}
for JS models.
Context Variables
Cube provides the following context variables you can access inside your data model
- FILTER_PARAMS: It helps to optimize SQL that powers Cube, e.g. hint your database optimizer to use a specific index or filter out partitions or shards in your cloud data warehouse so you won't be billed for scanning those.
- SQL_UTILS: Provides useful utils functions.
- CUBE: Handy helper to reference members via full names. Learn more about its usage in scoping section.
- COMPILE_CONTEXT: it is being used to create dynamic data models. It is advanced concept and you can read about it here.
You can find Context variables reference here