Skip to main content

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 nameDescription
dimensionCreate Dimension
labelSpecifies how the dimension will appear in the Ready-to-explore Dataset
typeSpecifies the data type you want to apply to the dimension (currently Holistics only support text/number/date/datetime/truefalse)
descriptionAdd dimension description
hiddenHides dimension from the Exploration interface of Dataset and Report
definitionDetermines 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 nameDescription
measureCreate measure
labelSpecifies how the measure will appear in the Ready-to-explore Dataset
typeSpecifies the data type you want to apply to the measure (currently Holistics only support number/date/datetime)
descriptionAdd measure description
hiddenHides measure from the Exploration interface of Dataset and Report
definitionDetermines how the measure will be define or calculate
aggregation_typeSpecify 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 }};;
}





Let us know what you think about this document :)