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 to Data Model

To extend on base dimensions, you can create Custom Dimensions by supplying a formula. Custom dimensions are created using SQL syntax with non-aggregate operations (e.g CASE WHEN, CONCAT(), field_a + field_b, etc)

To add a custom dimension:

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

You can also make use of my aforementioned fields by creating another field named Age Group based on their age:

Adding Measures to Data Model

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.

Similarly, you can create a simple Measure on contestants model to count the number of winners:

Or something more interesting, like the average gift value of young winners (this will combine a base field Gift Price and the Age Group field/created earlier):

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 transform (SQL) 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 transform models
• When you use Persistence settings, Custom Fields will not be persisted to the database.