AML Dimension & Measure
A grasp of these concepts will help you understand this documentation better:
(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.
Parameter Definitionβ
Parameter name | Description |
---|---|
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 defined or calculated based on SQL queries. Learn more about the definition parameter below. π |
SQL Definition of Dimensionβ
The concept of definition: @sql {{ }};;
refers to the way you specify how each dimensionβs values should be retrieved or calculated based on SQL queries.
References: There are two ways to reference columns or dimensions within the SQL definition:
@sql {{ #SOURCE.column_name }};;
: This references a column in the table connected to the current model.@sql {{ dimension_name }};;
: This references another dimension defined within the same model.
Default Behavior: If the SQL definition for a dimension is not explicitly provided, Holistics assumes that there is a column in the underlying table with the same name as the dimension. This is useful for cases where the dimension's name matches a column name, and you want to use that column as the dimension's source.
Example of Dimension Syntaxβ
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'
//to reference the "status" column in the source table
definition: @sql {{ #SOURCE.status }};;
}
dimension created_at {
label: 'Created At'
type: 'datetime'
}
dimension created_at_year {
label: 'Created At Year'
type: 'number'
//to calculate the year from the "created_at" dimension
definition: @sql extract(year from{{ created_at }});;
}
}
Measureβ
Measure syntax defines the measure of a data model. Measure represents an aggregated dimension in a model.
Parameter Definitionβ
Parameter name | Description |
---|---|
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 |
aggregation_type | Specify aggregate function of Measure. Currently we only support: count, count, distinct, sum, avg, max, min, max, median, stdev, stdevp, var, varp, custom |
definition | Determines how the measure will be defined or calculated based on SQL queries. Learn more about the definition parameter below. π |
SQL Definition of Measureβ
Forms: There are two primary forms that definition for measures can take:
Native Holistics Aggregation Type: Use aggregation type that Holistics natively supports such as sum, count, count_distinct, avg, etc.. For example:
measure total_users {
label: 'Total Users'
type: 'number'
// The definition here is the inner expression of the aggregation
definition: @sql {{ user_id }};;
aggregation_type: 'count'
}When used in an explore, this measure will be treated as
COUNT({{ user_id }})
Custom Aggregation Form (
aggregation_type: 'custom'
- this is the default when aggregation_type is not specified): The entire definition is used as the aggregation expression. This allows you to use aggregation functions from the source database that are not supported by Holistics (e.g. PERCENTILE_CONT from Redshift).measure percentile {
label: 'percentile'
type: 'number'
// must be valid aggregation expression that can be run in
// aggregation position of a query
definition: @sql percentile_cont(0.6) within group (order by {{ profit }});;
aggregation_type: 'custom'
}SELECT
col,
-- ...other group by columns
COUNT(*) -- The definition of custom aggregation must be an expression
-- that can be placed here
FROM orders
GROUP BY
1
-- , ...other group by columnsAdditionally, you can write a custom measure with calculations between measures:
measure profit {
label: 'Profit'
type: 'number'
// must be valid aggregation expression that can be run in
// aggregation position of a query
definition: @sql {{ measure_revenue }} - {{ measure_cost }} + sum({{ dimension_discount }});;
aggregation_type: 'custom'
}However, it's important to note that you cannot directly use dimensions without aggregation in a custom measure. For example:
measure profit {
label: 'Profit'
type: 'number'
// top level must be aggregated
definition: @sql {{ measure_revenue }} - {{ measure_cost }} + {{ dimension_discount }};;
aggregation_type: 'custom'
}
Example of Measure Syntaxβ
Model users {
type: 'table'
label: "Users"
description: "This is the AML Users Model"
table_name: '"ecommerce"."users"'
data_source_name: 'demodb'
measure total_users {
label: 'Total Users'
type: 'number'
definition: @sql count({{#SOURCE.id}});;
aggregation_type: 'custom'
}
}
FAQsβ
How should I define aggregate functions for measures, and what are the important considerations?β
Choose between using
aggregation_type
parameter or using aggregation functions from the database withindefinition: @sql ;;
parameter. Itβs important to note that you should not define an aggregate function in both parameters.If you define an aggregate function using the
aggregation_type
, thedefinition: @sql ;;
parameter must not contain any aggregate functions.//What you should write:
measure measure_1 {
...
definition: @sql {{ user_id }};;
aggregation_type: 'count'
}
---------------------------
//What you should NOT write
measure measure_1 {
...
definition: @sql count{{ user_id }};;
aggregation_type: 'count'
}If you define the aggregate function within the
definition: @sql ;;
parameter, make sure to set theaggregation_type
tocustom
.//What you should write:
measure measure_2 {
...
definition: @sql sum({{#SOURCE.id}});;
aggregation_type: 'custom'
}