Dimensions & Measures

Introduction

Each data model in Holistics has two types of field:

Dimensions: These are non-aggregated fields in a model which can refer directly to the underlying table's columns, or is created using non-aggregate functions to transform other dimensions.
Measures: These are created by using aggregate functions (SUM, COUNT, etc) and do not need to pre-define grouping dimension. Instead, the grouping will be added dynamically to the generated SQL when you combine Dimensions and Measures.

Dimensions

Adding new dimensions

When creating a new Table Model or Query Model using Holistics's GUI, the model will be initialized with some dimensions:

In the case of Table Model, the dimensions represent the underlying columns of the table in the database
In the case of Query Model, the dimensions represent the columns in the result of your SQL

When you add a new column to the underlying table or query, you can click Refresh Model to automatically create new dimensions for them.

Dimensions can also be added manually:

Go to the model view UI, click Add -> Custom Dimension,
Input field name and SQL formula.
Click Create, and a new column named will be created

Below is the general form of the dimension's syntax:

dimension dimension_name {
  label: 'Dimension Label'
  type: 'text' | 'number' | 'date' | 'datetime' | 'truefalse'
  description: 'Field Description'
  hidden: true|false
  definition: @sql {{ dimension }};;
}

info

Please refer to AML Dimension Reference to learn more about all available parameters and their example usage.

When to manually add dimensions

Some common scenarios you might want to manually defined dimensions:

To discretize a field with continuous value:

CASE
    WHEN {{ age }} > 30 then 'Older than 30 years old'
    WHEN {{ age }} >= 18 and {{ age }} < 30 then '18 - 30 years old'
    ELSE 'Under 10 years old'
END

To combine values of two fields to create a new value (e.g field_a + field_b)
```
{{ price }} - {{ discount }}
```
To convert the data type of a field, such as converting an integer to a string:
```
CAST( {{ ranking }} AS STRING )
```

Measures

Adding new measures

info

Please refer to AML Measure Reference to learn more about all available parameters and their example usage.

To add a Measure in your Data Model:

Go to the model view UI, click Add -> Measure,
Input field name and SQL formula.
Click Create, and a new measure will be created

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'
 }

When to use measures

Measure is suitable when you have a complicated logic that the basic aggregation in the Exploration UI cannot satisfy. For example:

When your aggregation involves conditions:

// Count only delivered orders
SUM(CASE WHEN {{ status }} == 'delivered' then 1 else 0 end)

When your aggregation involves multiple fields:

// Calculate profit
SUM( {{quantity}} * {{ price }} - {{ cost }})

Important Notes

Manually defined dimensions and measures can only refer to fields within the same model. If you need to create a cross-model field, you can use a Query Model to join the related models and combine fields in the SQL. Alternatively, you can use Holistics's Cross-model Calculation feature with AQL or creating metrics in datasets.
The value of manually defined dimensions and measures are calculated at run time, and when you use Persistence feature, they are not recorded to your database.

Introduction​

Dimensions​

Adding new dimensions​

When to manually add dimensions​

Measures​

Adding new measures​

When to use measures​

Important Notes​