Dimensions are the columns in your table. They are the “attributes” of your data. For example, user_id
in your users table is a dimension.
Property | Required | Value | Description |
---|---|---|---|
label | No | string | Custom label. If you set this property, this is what you’ll see in Lightdash instead of the dimension name. |
type | No | Dimension type | The dimension type is automatically pulled from your table schemas in Lightdash but you can override the type using this property. |
description | No | string | Description of the dimension in Lightdash. You can use this to override the description you have for the dimension in dbt. |
sql | No | string | Custom SQL applied to the column used to define the dimension. |
time_intervals | No | ’default’ or OFF or an array[] containing elements of date, numeric or string options | ’default’ (or not setting the time_intervals property) will be converted into [‘DAY’, ‘WEEK’, ‘MONTH’, ‘QUARTER’, ‘YEAR’] for dates and [‘RAW’, ‘DAY’, ‘WEEK’, ‘MONTH’, ‘QUARTER’, ‘YEAR’] for timestamps; if you want no time intervals set ‘OFF’. |
hidden | No | boolean | If set to true, the dimension is hidden from Lightdash. By default, this is set to false if you don’t include this property. |
compact | No | string | This option will compact the number value (e.g. 1,500 to 1.50K). Currently supports one of the following: [‘thousands’, ‘millions’, ‘billions’, ‘trillions’, ‘kilobytes’, ‘megabytes’, ‘gigabytes’, ‘terabytes’, ‘petabytes’, ‘kibibytes’, ‘mebibytes’, ‘gibibytes’, ‘tebibytes’, ‘pebibytes’] |
format | No | string | This option will format the output value on the results table and CSV export. Supports spreadsheet-style formatting (e.g. #,##0.00). Use this website to help build your custom format.` |
groups | No | string or string[] | If you set this property, the dimension will be grouped in the sidebar with other dimensions with the same group label. |
urls | No | Array of { url, label } | Adding urls to a dimension allows your users to click dimension values in the UI and take actions, like opening an external tool with a url, or open at a website. You can use liquid templates to customise the link based on the value of the dimension. |
required_attributes | No | Object with { user_attribute, value } | Limits access to users with those attributes |
colors | No | Object with { value, color } | Color for the values in the chart |
type
meta tag in your .yml file. If you run lightdash generate
to generate your .yml files, then Lightdash will add the type
from your data warehouse to your .yml files automatically.
Dimension Types |
---|
string |
number |
timestamp |
date |
boolean |
>-
it allows you to type descriptions that are multiple lines long in the YAML file, but the text will be combined into a single line when parsed. The lightdash generate
command will automatically add this to keep YAML files easy to read.
This description in YAML:
|
character like this:
format
parameter to have your dimensions show in a particular format in Lightdash. Lightdash supports spreadsheet-style format expressions for all dimension types.
To help you build your format expression, we recommend using https://customformats.com/.
Description | Format Expression | Raw Value | Formatted Output |
---|---|---|---|
Adds “km” suffix to the value | #,##0.00" km" | 100000.00 | 100,000.00 km |
15000.25 | 15,000.25 km | ||
500 | 500.00 km | ||
Format date with 12-hour clock | m/d/yyyy h:mm AM/PM | 2023-09-05T15:45:00Z | 9/5/2023 3:45 PM |
2024-01-20T08:30:00Z | 1/20/2024 8:30 AM | ||
Display the full name of the day | dddd | 2023-09-05T15:45:00Z | Tuesday |
2024-01-20T08:30:00Z | Saturday | ||
Format positive, negative, and zero values | "⬆️ "0;"⬇️ "0;0 | -500 | ⬇️ 500 |
200 | ⬆️ 200 | ||
0 | 0 | ||
Text formatting | "Delivered in "@ | 2 weeks | Delivered in 2 weeks |
18 hours | Delivered in 18 hours | ||
Percentage formatting | #,##0.00% | 0.6758 | 67.58% |
0.1 | 10.00% | ||
0.002 | 0.20% | ||
No formatting | 0 | 12345232 | 12345232 |
56.7856 | 57 | ||
Currency formatting | [$]#,##0.00 | 15430.75436 | $15,430.75 |
15430.75436 | $15,430.75 | ||
Compact currency in thousands | [$]#,##0,"K" | 15430.75436 | $15K |
15430.75436 | $15.43K | ||
Compact currency in millions | [$]#,##0.00,,"M" | 13334567 | $13.33M |
120000000 | $120.00M |
(Legacy) format and round options
format
and round
options and only apply the spreadsheet-style formatting expression.Option | Equivalent format expression | Description | Raw value | Displayed value |
---|---|---|---|---|
km | ’#,##0.00” km“‘ | Adds the suffix km to your value | 10 | 10 km |
mi | ’#,##0.00” mi“‘ | Adds the suffix mile to your value | 10 | 10 mi |
usd | ’[$]#,##0.00’ | Adds the $ symbol to your number value | 10 | $10.00 |
gbp | ’[$£]#,##0.00’ | Adds the £ symbol to your number value | 10 | £10.00 |
eur | ’[$€]#,##0.00’ | Adds the € symbol to your number value | 10 | €10.00 |
jpy | ’[$¥]#,##0.00’ | Adds the ¥ symbol to your number value | 10 | ¥10 |
percent | ’#,##0.00%‘ | Adds the % symbol and multiplies your value by 100 | 0.1 | %10 |
id | ’0’ | Removes commas and spaces from number or string types so that they appear like IDs. | 12389572 | 12389572 |
1,500
appears as 1.50K
), then I would write something like this in my .yml:
Value | Alias | Equivalent format expression | Example output |
---|---|---|---|
thousands | ”K” and “thousand” | ’#,##0,” K”’ or ’#,##0.00,” K“‘ | 1K |
millions | ”M” and “million” | ’#,##0,,” M”’ or ’#,##0.00,,” M“‘ | 1M |
billions | ”B” and “billion” | ’#,##0,,,” B”’ or ’#,##0.00,,,” B“‘ | 1B |
trillions | ”T” and “trillion” | ’#,##0,,,,” T”’ or ’#,##0.00,,,,” T“‘ | 1T |
kilobytes | ”KB” and “kilobyte” | 1KB | |
megabytes | ”MB” and “megabyte” | 1MB | |
gigabytes | ”GB” and “gigabyte” | 1GB | |
terabytes | ”TB” and “terabyte” | 1TB | |
petabytes | ”PB” and “petabyte” | 1PB | |
kibibytes | ”KiB” and “kibibyte” | 1KiB | |
mebibytes | ”MiB” and “mebibyte” | 1MiB | |
gibibytes | ”GiB” and “gibibyte” | 1GiB | |
tebibytes | ”TiB” and “tebibyte” | 1TiB | |
pebibytes | ”PiB” and “pebibyte” | 1PiB |
created
defined in our dbt project:
created
appears in our Lightdash project:
time_intervals: OFF
time_intervals
property to OFF
.
In this example, created
would now appear as a single, timestamp dimension without a drop-down list of time intervals in Lightdash:
time_intervals
parameter.Option | Description | Type | Displayed value | Notes |
---|---|---|---|---|
RAW | Original value | Date / DateTime | 2019-01-01 / 2019-01-01, 09:30:30:300 UTC | |
YEAR | Date truncated to the nearest year | Date | 2019 | |
QUARTER | Date truncated to the nearest quarter | Date | 2019-Q1 | |
MONTH | Date truncated to the nearest month | Date | 2019-01-01 | |
WEEK | Date truncated to the nearest week | Date | 2019-01-01 | The start of the week depends on your warehouse configuration |
DAY | Date truncated to the nearest day | Date | 2019-01-01 | |
HOUR | Datetime truncated to the nearest hour | DateTime | 2019-01-01, 09 UTC | |
MINUTE | Datetime truncated to the nearest minute | DateTime | 2019-01-01, 09:30 UTC | |
SECOND | Datetime truncated to the nearest second | DateTime | 2019-01-01, 09:30:30 UTC | |
MILLISECOND | Datetime truncated to the nearest millisecond | DateTime | 2019-01-01, 09:30:30:300 UTC |
Option | Description | Type | Displayed value | Notes |
---|---|---|---|---|
DAY_OF_WEEK_INDEX | Index of the day of the week | Number | 0 | The value range and start of the week depends on your warehouse configuration |
DAY_OF_MONTH_NUM | Day of the month | Number | 21 | |
DAY_OF_YEAR_NUM | Day of the year | Number | 127 | |
WEEK_NUM | Week number | Number | 37 | |
MONTH_NUM | Month number | Number | 7 | |
QUARTER_NUM | Quarter number | Number | 3 | |
YEAR_NUM | Year number | Number | 2019 | |
MINUTE_OF_HOUR_NUM | Minute number | Number | 50 | |
HOUR_OF_DAY_NUM | Hour number | Number | 22 |
Option | Description | Type | Displayed value |
---|---|---|---|
DAY_OF_WEEK_NAME | Day of the week | String | Monday |
MONTH_NAME | Month name | String | March |
QUARTER_NAME | Quarter name | String | Q3 |
groups
parameter.
To do this, you need to set up group_details
in the model’s configuration. Then, you can use these groups to organize metrics and dimensions. You can create nested groups up to 3 levels.
${ value.formatted }
will be replaced with the value of the company name in the Lightdash UI at query run time. The ${ row.company.company_id.raw }
will be replaced with the value of the company id in the Lightdash UI at query run time. The action will be disabled if the column “company_id” from table “company” is not part of the query.
company_name
and it uses the field customer_id
:
customer_id
included in my results table.
Tag | Description |
---|---|
${ value.formatted } | The exact value of the dimension as seen in the Lightdash UI. For example “$1,427.20” |
${ value.raw } | The raw value of the dimension returned from the underlying SQL query. For example “1427.2 “ |
${ row.table_name.column_name.formatted } | The exact value of the column as seen in the Lightdash UI. For example “$1,427.20” |
${ row.table_name.column_name.raw } | The raw value of the dimension returned from the underlying SQL query. For example “1427.2 ” |
Filter | Description | Example usage | |
---|---|---|---|
url_encode | Encode a string as url safe, for example it replaces spaces with %20 | ${value.formatted | url_encode } | |
downcase | Convert string to all lowercase | ${value.formatted | downcase } | |
append | Append a string to another | ${value.formatted | append: “.html”} |
user attributes
to limit some dimensions to some users.
In the example below, only users with is_admin
attribute true
can use the salary
dimension on user
table. Users without access to this dimension will not see it or the custom metrics created from this dimension on the explore page
.
Forbidden
error.
table calculations
Status
with capital S on a table named orders
in postgres throws the following error:
sql
meta tag on dimensions
Status
columns on the SQL query
organizations
is a joined table in the example below).
hidden:true
for the main JSON dimension since raw JSON is not useful in charts.