Dimension & Measure
Knowledge Checkpoint
We think it would be best if you have a clear understanding of these concepts before reading this documentation:
(This feature is currently in active development. Please reach out if you want to become beta tester)
Dimensionβ
Dimension syntax defines the dimension of a data model. Dimension represents a column in a table or a calculation.
Syntaxβ
dimension dimension_name {
label: 'Dimension Label'
type: 'text' | 'number' | 'date' | 'datetime' | 'truefalse'
description: 'Field Description'
hidden: true|false
definition: @sql {{ dimension }};;
}
Dimension Syntax Exampleβ
Model orders {
type: 'table'
label: "Orders"
table_name: 'ecommerce.orders'
data_source_name: 'mydemodb'
description: "This is the AML Orders Model"
dimension status {
label: 'Status'
type: 'text'
// If you don't include this line, Holistics will automatically use the table's column that bear the
// same name with the dimension (i.e status).
definition: @sql {{ #SOURCE.status }};;
}
dimension created_at {
label: 'Created At'
type: 'datetime'
}
dimension created_at_year {
label: 'Created At Year'
type: 'number'
definition: @sql extract(year from{{ created_at }});;
}
}
SQL Definition of Dimensionβ
The sql definition for dimensions can generally take any valid SQL that would go into a single column of a SELECT statement which rely on Holistics operation. There are two forms of the operation:
@sql {{ #SOURCE.column_name }};;
references a column in the table that is connected to the table model youβre working on.@sql {{ dimension_name }};;
references a dimension within the model youβre working on.
If the field definition is left unspecified, then Holistics will assume that there is a column in the underlying table with the same name as the field. For example, selecting a field called email
without a sql parameter would be equivalent to specifying sql: @sql {{ #SOURCE.email }};;
.
Parameter definitionβ
Parameter name | Description |
---|---|
dimension | Create Dimension |
label | Specifies how the dimension will appear in the Ready-to-explore Dataset |
type | Specifies the data type you want to apply to the dimension (currently Holistics only support text/number/date/datetime/truefalse) |
description | Add dimension description |
hidden | Hides dimension from the Exploration interface of Dataset and Report |
definition | Determines how the dimension will be define or calculate |
Measureβ
Measure syntax defines the measure of a data model. Measure represents an aggregated dimension in a model.
Syntaxβ
measure measure_name {
label: 'Measure Name'
type: 'number' | 'date' | 'datetime'
definition: @sql count( {{ dimension }} );;
}
measure measure_name {
label: 'Measure Name'
type: 'number | date | datetime'
definition: @sql {{ dimension }};;
aggregation_type: 'count' | 'count distinct' | 'sum' | 'avg' | 'max' | 'min'
}
Measure Syntax Exampleβ
Model users {
type: 'table'
label: ""
description: ""
owner: '[email protected]'
table_name: '"ecommerce"."users"'
data_source_name: 'demodb'
measure total_users {
label: 'Total Users'
type: 'number'
definition: @sql count({{#SOURCE.id}});;
aggregation_type: 'custom'
}
}
Parameter definitionβ
Parameter name | Description |
---|---|
measure | Create measure |
label | Specifies how the measure will appear in the Ready-to-explore Dataset |
type | Specifies the data type you want to apply to the measure (currently Holistics only support number/date/datetime) |
description | Add measure description |
hidden | Hides measure from the Exploration interface of Dataset and Report |
definition | Determines how the measure will be define or calculate |
aggregation_type | Specify the aggregate function of the measure (sum/count/count_distinct/avg/...) |
SQL Definition of Measureβ
The SQL definition for measures takes one of two forms:
- Performing aggregate function (sum, count, count_distinct, avg, etc.) using our operation as described in dimension section above
- A value which is calculated based on several other measures
For example, if we wanted to calculate the total users:
measure total_users {
label: 'Total Users'
type: 'number'
definition: @sql {{ user_id }};;
aggregation_type: 'count'
}
Or if we wanted to calculate our total profit, we might use:
measure profit {
label: 'Profit'
type: 'number'
definition: @sql {{ revenue }} - {{ cost }};;
}