Dimensions & Measures

Introduction

Each data model contains fields (similar to data table and their respective columns). There are 3 types of fields:

• Base Dimension: the "original" fields in your source data.
• Custom Dimension: created by using non-aggregate functions to transform one or multiple dimensions.
• Custom Measure: created by using aggregate functions (SUM, COUNT, etc)

Base Dimension

Base Dimensions are the "original" dimensions that you got when you first create your model. Think of this as, when you "load" your database table into a model, the table's columns become base dimensions.

Base dimensions can only be hidden, and cannot be removed via the UI.

Adding Custom Dimensions

info

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

To add a Custom Dimension in your Data Model:

1. Go to your AML Data Model file.
2. Add the dimension using AML Dimension syntax.
3. (Optional) Click Preview to enter Preview mode. This is useful to verify that your Dimension behaves correctly.
4. Click Save.
5. Click Deploy to production to make your new dimension live.

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

Adding Measures

Measures are similar to Custom Dimensions, except that they're created using aggregate functions/operations.

Think of Measures as the aggregate expression of your typical SQL statement. They do not need a table to be aggregated beforehand and will automatically apply GROUP BY clauses based on the reports you build.

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 your AML Data Model file.
2. Add the measure using AML Measure syntax.
3. (Optional) Click Preview to enter Preview mode. This is useful to verify that your Dimension behaves correctly.
4. Click Save.
5. Click Deploy to production to make your new dimension live.

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

Getting the best out of Holistics Custom Measures and Dimensions

If your base data doesn't include all of the fields you need to answer your questions, you can create custom fields in Holistics and then re-use them easily across all reports with central definitions.

Best practices for creating Custom Dimension

• Use custom dimension that transforms existing model fields when the new field is created from non-aggregate functions.

• Some common scenarios you might use custom dimensions include:

  • To segment or categorize data

    CASE
        WHEN {{ #THIS.quantity }} > 30 then 'The quantity is greater than 30'
        WHEN {{ #THIS.quantity }} = 30 then 'The quantity is 30'
        ELSE 'The quantity is under 30'
    END

  • To perform the calculation at the row level of the data source (e.g field_a + field_b)

    {{ #THIS.price }} - {{ #THIS.discount }}

  • To convert the data type of a field, such as converting a string to a date.

    You can use your SQL DB's equivalent of the CAST() operation in a custom dimension to create a new field with the new data type. e.g. COUNT(CAST( {{ #THIS.field_a }} AS INTEGER ))

What is {{ #SOURCE.field_a }} syntax?

This is a Model Field Expression used to refer to a custom dimension/measure defined within the same model. For more information, refer to {{SOURCE.field_a}} usage.

Best practices for creating Custom Measure

• Use custom measure when the new field is created from Basic Aggregation functions likes SUM, COUNT, MIN, MAX… that summarizes your data across some dimensions.

  Basic aggregations are provided in Holistics's Data Exploration UI, but with Custom Measure, you can specify more complicated calculations like conditional SUM

• Some common scenarios you might use custom measures include:

  • To aggregate data

  • To calculate ratios or a value based on several other measures. For example, if you need to show a profit for an order, you might create a formula similar to the following:

    SUM (Sales) - SUM (Cost)

• Custom measures do not accept pre-defined GROUP BY clauses.

  Our thinking behind custom measures is to allow for a global definition of the desired aggregation on column fields that can be reused across a variety of use cases. When you drag a measure into the visualization combined with a dimension, Holistics automatically applies an aggregation to that measure (by default) grouping by the dimension.

How to convert a SQL statement to a custom measure?

Holistics also has the SQL Editor to run SQL queries for ad-hoc analysis. After perfectly executing SQL queries to do a calculation, you want to put it into a custom measure. Please see how to do it via the below example:

Use-case: You want to create a custom measure to find the total number of rows created in the last 30 days from a specific table.

Here's the SQL query written and executes perfectly,

SELECT COUNT(*) FROM company_checklists 
WHERE created_at between (NOW() - INTERVAL 1 MONTH) AND NOW()

But when putting it into a custom measure (without the SELECT word) gives you an error:

Solution:

The Custom Measure can only be an expression that will be put in the SELECT ****clause of the Model's query. Therefore, you should use this expression:

COUNT(
  CASE {{ #THIS.created_at }} BETWEEN (NOW() - INTERVAL '1 MONTH') AND NOW()
  WHEN TRUE THEN 1
  ELSE NULL END  
)

Important Notes

• SQL based custom fields can only refer to fields within the same model. If you need to create a cross-model field, you can create a query model to join the related models first, then create the custom fields within the model's code or as a custom field.
• If you want to do complex transformation, pre-aggregation, or some window functions, we suggest using query models and AQL
• When you use Persistence settings, Custom Fields will not be persisted to the database.