Dimension & Measure

Knowledge Checkpoint

We think it would be best if you have a clear understanding of these concepts before reading this documentation:

• Dimensions & Measure Concept
• Create an AML Dimension
• Create an AML Measure

(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 }};;
 }