This way you have more options to present your data without directly modifying the underlying table columns:
- Name: This is the true name of the field which analysts will see when managing modeling. Only lowercase alpha-numeric characters (a-z, 0 - 9) and underscores (_) are allowed. If the name starts with a numeric character, the name will be prepended with an underscore (
- Label: This is what normal users see when exploring a Model/Dataset. You should make the Field Label as descriptive as possible.
- Field Description: This provides more context to understand the field, for example how the field is calculated, or what this field should be used for.
- Visibility: If an analyst chooses to hide a field or measure, it will not be visible to business users exploring a dataset containing that field, It is still visible in Data Modeling interface and accessible in SQL interfaces (when writing adhoc SQL, SQL report, or creating Calculated Fields / measures)
Base Dimensions are the "original" dimensions that you got when you first create your model.
- In Base Models, Dimensions map directly to the underlying data table's columns.
- In Transform Models, they map to the resulted fields of the SQL.
- In Import Models, they map to the fields that you included in your data import.
Base dimensions can only be hidden, and cannot be removed via the UI. These are the only fields that can be persisted into the database.
To extend on the base dimensions, users can create Custom Dimensions and Measures. Properties of these custom fields:
- Custom Dimensions and Measures are only calculated when users explore a dataset, and cannot be persisted (in other words, pre-calculated) into the database.
- If you want to persist the result of Custom Dimensions and Measures, you will have to create a derived SQL model using those fields and persist this model.
From Data Model's Structure tab, simply clicking on Add→ Measure or Calculated Field.
The formula of your Custom Dimensions and Measures depends entirely on your database's SQL flavor. These are the code snippets that are inserted between the
SELECT ... FROM ... keywords in your final query.
Custom Dimensions (or Calculated Fields) are created by using non-aggregate (scalar) functions and operations to transform one or multiple columns, for example:
CASE ... WHEN,
field_a + field_b... In the model's structure view, Calculated Fields are represented with the Function Icon (fx) right next to Field Name.
When creating Custom Dimensions (Calculated Fields), you can reuse other dimensions that you created in the same model:
Aggregate functions and Measures are not allowed within Custom Dimensions.
Measures are created from SQL's aggregate functions and operations such as
SUM(field_a) + SUM(field_b)... Measures have the Sigma (Σ) icon on the left of the Field Name and are colored blue.
In Measures' formula, you can refer to any base dimensions, custom dimensions and measures you created in the same model. For example, in the formula for Cancelled Value Ratio we used measure
cancelled_value and dimension
Measures must be aggregated, therefore simply using scalar operations or referring only dimensions are not allowed:
Updated 6 months ago