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.

info

In the Business Intelligence (BI) world, the words "metric" and "measure" are often used interchangeably. Some BI tools use the term "metric", while others use "measure". However, in general, these terms are intended to convey the same meaning. This makes sense, as the word "metric" is derived from the Greek word "metron", which translates to "a means of measure".

In Holistics, we have both "metric" and "measure", where "measure" is defined in a model, while "metric" is defined at the dataset level.

Dimensions

Dimensions are the building blocks of your data model. Here's how to add and configure them.

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:

1. Go to the model view UI, click Add -> Custom Dimension,
2. Input field name and definition (SQL or AQL formula).
3. Click Create, and a new column named will be created

Below is the general form of the dimension's syntax. You can define dimension in either SQL or AQL. For more details on how to define dimension with AQL, please refer to single model dimension.

model_name.model.aml

Model  model_name {
  ...

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

  // AQL based dimension
  dimension aql_dimension_name {
    label: 'Dimension Label'
    type: 'text' | 'number' | 'date' | 'datetime' | 'truefalse'
    description: 'Field Description'
    hidden: true | false
    definition: @aql aql_expression ;;
  }

}

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:

  definition: @aql case(
    when: users.age > 30, then: 'Older than 30 years old',
    when: users.age >= 18 and users.age < 30, then: '18 - 30 years old',
    else: 'Under 10 years old'
  ) ;;

• To combine values of two fields to create a new value (e.g field_a + field_b)

  definition: @aql orders.price - orders.discount ;;

• To convert the data type of a field, such as converting an integer to a string:

  definition: @aql cast(users.ranking, 'text') ;;

Measures

Measures let you define reusable aggregation logic. Here's how to create them.

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:

1. Go to the model view UI, click Add -> Measure,
2. Input field name and definition (SQL or AQL formula).
3. Click Create, and a new measure will be created

You can define a measure using either SQL or AQL, but we recommend using AQL for most cases as it provides better syntax suggestions, typechecking, and validation. For more details, refer to how to define metrics in AQL.

model_name.model.aml

Model model_name {

  ...

  // Simple aggregation
  measure total_orders {
    label: 'Total Orders'
    type: 'number'
    definition: @aql count(orders.id) ;;
  }

  // AQL measures support more advanced use cases, such as percent of total
  measure total_orders_of_all_countries {
    label: 'Total Orders of all Countries'
    type: 'number'
    definition: @aql orders.total_orders | of_all(countries) ;;
  }

  measure country_orders_pct {
    label: 'Country Orders Pct'
    type: 'number'
    definition: @aql orders.total_orders * 1.0 / orders.total_orders_of_all_countries ;;
  }

}

When to use measures

Measures are useful when you have aggregation logic that the basic aggregation in the Exploration UI cannot satisfy. For example:

• When your aggregation involves conditions:

  // Count only delivered orders
  measure delivered_orders {
    label: 'Delivered Orders'
    type: 'number'
    definition: @aql count_if(orders, orders.status == 'delivered') ;;
  }

• When your aggregation involves multiple fields:

  // Calculate profit
  measure profit {
    label: 'Profit'
    type: 'number'
    definition: @aql sum(orders, orders.quantity * orders.price - orders.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, use Holistics's Cross-model Calculation feature with AQL metrics at the Dataset level. More details can be found in 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.

Advanced Use Case

If you need to set up Dynamic Dimensions and Measures Selection in your dashboard, please refer to the following documentation:

• Parameter Fields
• Dynamic Dimensions Selection
• Dynamic Metrics Selection