Step 3:Create models

Introduction

Models are the most important step in creating visuals. They significantly influence what you can do when creating charts and dashboards.

Dimensions and measures

An important distinction to understand is the difference between dimensions and measures.

• Dimensions: A dimension is a categorical or descriptive field (such as "product name" or "date") with which you can group or filter data in a visualization.
• Measures: A measure is a quantitative measurement (such as "revenue" or "number of orders") that contains an aggregation function (for example sum, average, count).

Dimensions determine how your data is grouped or sliced, while measures determine what you measure and analyze in those groups.

Creating a model

Creating a new model starts in the SQL editor. Write an SQL query and test it by executing it. When you are satisfied and have the fields for the required measures (as numeric columns) and dimensions, you click on Promote, choose a name and create a model.

Then you go to the Models tab. Here you see the models and you can choose the dimensions and measures at the bottom. Usually you choose most columns from your query as dimensions and make a measure with sum and sometimes also with average from all numeric fields. Note that the name of a measure can only contain alphanumeric characters, so no #, % or * in the name itself. For each measure you can also use the free text field Display format to control how the value is shown in Lightdash. Examples:

You can also append text after a value with @" followed by the text in quotes, for example @" stuks".

In Lightdash you can compare measures with the previous year and/or make them cumulative. At the top right you can see three helper screens: a data preview, the dependencies and the SQL as it is executed.

Here you can validate the model with the Validate button - if that is successful you can click on Deploy and the status is changed from draft to deployed and a dbt run is executed behind the scenes.

You see the models in two places:

• Lightdash: the most important is that you can use these models in Lightdash. Remarkably, they are called "tables" within the Lightdash interface.
• SQL editor: you can also see these models as a view in the SQL editor and you can therefore also create queries with them. They are visible under analytics_{company_name}, with the tables that have been loaded from the sources found under raw_{company_name}. You can even create models with SQL that calls other models again. This can make it easier to align definitions, but be aware that you are then introducing dependencies.

Joins between models

You can link models together with joins. You do this as follows:

1. First add the primary key to the dimension model.
2. Then add a link in the fact model to that dimension model.

We recommend star modelling: one fact table in the centre, with dimension tables around it. This keeps the structure clear and reusable in Lightdash.

More details on joins in Lightdash: Lightdash: Joins.

Tips for models

• Make generic models: try to make models so that they are generically usable, so in principle one model per granularity. Creating a separate model for each chart or table is not practical in the long run.
• Ensure clear granularity: be clear what one row in the model is, because it often affects the visuals. There is a difference between invoice and invoice lines, and that can make a lot of difference.
• Adjust the measures to this granularity: make sure that your measures match this granularity. For example, if the same customer occurs multiple times in a dataset, an average customer age works slightly differently than people might think.
• Add some extra information: if something is possibly interesting but not directly requested now, it is often useful to add it now. You often make it easier for yourself to create extra charts or tables later.
• Be careful with privacy-sensitive information: this applies to information that is traceable to persons. This applies not only to personal names, but also for example date of birth and exact address. Ask yourself whether you should add these and if it is still useful, try to make it less traceable by choosing birth year or four-digit postal code.
• Fill in the descriptions for measures: something may be clear to you now, but perhaps that will no longer be the case for your successor in a year. Document as much as you can.