To customize the dimension, you can do it in your dbt model’s YAML file.If you want to declare multiple dimensions based on the same column, check additional dimensions section.
Copy
Ask AI
models: - name: sales_stats meta: group_details: finance: label: Finance description: Finance-related fields. joins: - join: web_sessions sql_on: ${web_sessions.date} = ${sales_stats.date} columns: - name: revenue_gbp_total_est description: 'Total estimated revenue in GBP based on forecasting done by the finance team.' meta: dimension: type: number label: 'Total revenue' # this is the label you'll see in Lightdash description: 'My custom description' # you can override the description you'll see in Lightdash here sql: 'IF(${TABLE}.revenue_gbp_total_est = NULL, 0, ${registered_user_email})' # custom SQL applied to the column from dbt used to define the dimension hidden: false format: '[$£]#,##0.00' # GBP rounded to two decimal points groups: ['finance'] - name: forecast_date description: 'Date of the forecasting.' meta: dimension: type: date time_intervals: ['DAY', 'WEEK', 'MONTH', 'QUARTER'] # not required: the default time intervals for dates are `['DAY', 'WEEK', 'MONTH', 'YEAR']` urls: - label: 'Open in forecasting tool' url: 'https://finance.com/forceasts/weeks/${ value.raw }' - label: Open in Google Calendar url: 'https://calendar.google.com/calendar/u/0/r/day/${ value.formatted |split: "-" |join: "/"}' required_attributes: is_admin: 'true' - name: date meta: dimension: type: date day_of_week_num: type: number label: 'Day of Week (number)' sql: 'day_of_week(${date})' day_of_week_day: type: date label: 'Day of Week (day)' sql: ${date} format: 'dddd' is_weekday_or_weekend: type: boolean label: 'Weekday or Weekend' sql: "case when day_of_week(${date}) < 5 then 'weekday' else 'weekend' end"
All the properties you can customize:
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.
’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.
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’]
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.`
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.
The types of your dimensions are pulled from your data warehouse, automatically. You can override these types using the 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.
Copy
Ask AI
- name: user_created_date meta: dimension: type: date
When you surround text with double or single quotes it will escape the text between so that any special characters recognized by YAML will still pass through to the Lightdash UI.
Copy
Ask AI
description: 'The contents of this column include this & that.'
When you use >- 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:
Copy
Ask AI
- name: product_tier description: >- This is a longer description... ...that requires multiple lines and it will be combined in the Lightdash UI
Will appear like this in the Lightdash UI:
Copy
Ask AI
This is a longer description......that requires multiple lines and it will be combined in the Lightdash UI
You can use the 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/.
Copy
Ask AI
- name: us_revenue meta: dimension: type: number description: 'Revenue in USD, with two decimal places, compacted to thousands' format: '$#,##0.00," K"' # 505,430 will appear as '$505.43 K' additional_dimensions: percent_of_global_revenue: type: number description: 'Percent of total global revenue coming from US revenue.' sql: ${us_revenue} / ${global_revenue} format: '0.00%' # 0.67895243 will appear as '67.89%
Spreadsheet-style format expressions are the recommended way of adding formatting to your metrics in Lightdash. There are legacy formatting options, listed below, which are less flexible than the spreadsheet-style formatting.
If you use both legacy and spreadsheet-style formatting options for a single dimension, Lightdash will ignore the legacy format and round options and only apply the spreadsheet-style formatting expression.
You can compact values in your YAML. For example, if I wanted all of my revenue values to be shown in thousands (e.g. 1,500 appears as 1.50K), then I would write something like this in my .yml:
Copy
Ask AI
models: - name: sales columns: - name: revenue meta: dimension: compact: thousands # You can also use 'K'
Lightdash automatically adds intervals for dimensions that are timestamps or dates, so you don’t have to!For example, here we have the timestamp dimension created defined in our dbt project:
Copy
Ask AI
- name: created description: 'Timestamp when the user was created.'
Lightdash breaks this out into the default intervals automatically. So, this is how created appears in our Lightdash project:
Formatting added to a date or timestamp dimension will be applied to all of the time intervals for that dimension.If you want to apply different formats for different time intervals, we recommend creating additional dimensions for time intervals where you want to customize the format.
To turn off time intervals for a dimension, set time_intervals: OFF
If you want to turn off time intervals for a dimension, you can simply set the 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:
Copy
Ask AI
- name: created description: 'Timestamp when the user was created.' meta: dimension: time_intervals: OFF
To customize the time intervals for a dimension, you can use the time_intervals parameter.
If you specify time intervals manually, then this overrides the default time intervals used by Lightdash.
Copy
Ask AI
- name: created description: 'Timestamp when the user was created.' meta: dimension: time_intervals: ['DAY', 'DAY_OF_MONTH_NUM', 'MONTH', 'QUARTER_NAME', 'YEAR']
You can see all of the interval options for date and timestamp fields below.
You can group your dimensions and metrics in the sidebar using the 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.
Copy
Ask AI
models: - name: baskets meta: group_details: product_details: label: Product Details description: 'Fields that have information about the products in the basket.' item_details: label: Item Details description: 'Fields that have information about the items in the basket.' columns: - name: basket_item_id description: 'ID for the product item within the basket.' meta: dimension: groups: ['product_details', 'item_details'] # this would add the dimension to a nested group: `product details` --> `item details` - name: product_name description: 'Full name of the product.' meta: dimension: label: 'Product name' groups: ['product_details'] # this would add the dimension under the group label: `product_details`
Lightdash users can interact with dimension values by clicking on them. If you’re already storing URLs in your models, you can create hyperlinks to those URLs in Lightdash, like so:
Copy
Ask AI
columns: - name: candidate_profile_url label: URL of the candidate profile meta: dimension: urls: - label: Open in CRM url: ${ value.raw }
By adding custom urls you can configure the actions available to your users. Like linking to external tools, or taking actions in other tools.
In the example below, users can click on a company name and open a corresponding record in their CRM or search for the company in google or open that company’s Slack channel.
Copy
Ask AI
columns: - name: company_name label: Registered trading name of the company meta: dimension: urls: - label: Search for company in Google url: 'https://google.com/search?${ value.formatted | url_encode }' - label: Open in CRM url: 'https://mycrm.com/companies/${ row.company.company_id.raw | url_encode }'
The ${ 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.
You can reference values from other columns in your URLs
You can reference another dimension from your table in your URL. For these URLs to work, the other column you’ve referenced needs to be included in your results table. For example, say I’ve added a URL to company_name and it uses the field customer_id:
Copy
Ask AI
columns: - name: company_name label: Registered trading name of the company meta: dimension: urls: - label: "Open company" url: "https://example.com/company/${row.customers.customer_id.raw | url_encode }"
This URL will only work if I have customer_id included in my results table.
Lightdash can use 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.
Copy
Ask AI
columns: - name: description: User name - salary: description: User salary meta: dimension: required_attributes: is_admin: "true"
If a user without access to this dimension runs a query that contains this dimension, they will get a Forbidden error.
You can predefine colors for your string type dimensions, these colors will be used instead of your default organization colors for the right value when you use a grouped bar chart or a pie chart.
We recommend using #HEX colors, other color types like rgba,rgba or color name (eg: orange) are also supported on charts, but they are not yet supported on the chart config.
You can manually override these dimension colors by going into the chart config and manually picking a color for that serie.These colors will also take precedence over the organization color palette.
Using special characters or capital letters in your column names
If you use special characters on your column names, you might get errors when using those columns on explore. For example, having a column named Status with capital S on a table named orders in postgres throws the following error:
Copy
Ask AI
column orders.status does not exist
To fix this, we can add the quoted column to our sql meta tag on dimensions
Copy
Ask AI
- name: status meta: dimension: type: string sql: '"orders"."Status"' # you can also use '${TABLE}."Status"'
This will quote the Status columns on the SQL query
Copy
Ask AI
SELECT "orders".order_id AS "orders_order_id", "orders"."Status" AS "orders_status"FROM "postgres"."jaffle"."orders" AS "orders"
Additional dimensions let you define multiple dimensions off of a single column from your dbt model. This is useful when adding different formatting to a column, comparing or combining columns, parsing JSON columns, or creating persisted groups/buckets based off of a column.A “normal” dimension is a column created in your .sql file in dbt that is written to your data warehouse. An additional dimension is not included in your dbt .sql file, so it’s not written to your data warehouse. When used in Lightdash, it just adds the dimension definition to your SQL query (so it’s “created” at runtime).All dimension configurations are available for additional dimensions. You can also use additional dimensions when defining metrics.
Additional dimensions names need to be unique in the model.
When defining additional dimensions, you can reference other dimensions, even from joined tables (organizations is a joined table in the example below).
Copy
Ask AI
columns: - name: created_date meta: dimension: type: date additional_dimensions: days_to_first_query_run: type: number description: 'Number of days between a user being created and their first query run.' sql: ${first_query_date} - ${created_date} days_to_organization_first_payment: type: number description: 'Number of days between a user being created and their organization making its first payment. This will be negative for users who joined after the first payment.' sql: ${created_date} - ${organizations.first_payment_date}
Usually you’ll want to add hidden:true for the main JSON dimension since raw JSON is not useful in charts.
Copy
Ask AI
columns: - name: metadata # this is a jsonb column with metadata meta: dimension: hidden: true additional_dimensions: version: type: number sql: JSON_VALUE(${metadata}, '$.version') # custom SQL applied to get the "version" value inside metadata
You can use additional dimensions to convert a timestamp into multiple timezones:
Copy
Ask AI
columns: - name: created_at description: 'The time that the thing was created' meta: dimension: label: 'Created (UTC)' type: timestamp additional_dimensions: created_at_est: type: timestamp label: 'Created (EST)' description: 'The time that the thing was created, in EST' sql: "convert_timezone('UTC', 'America/New_York', ${TABLE}.created_at)"
To define metrics based on additional dimensions, you need to add them to the model’s meta metrics, or use custom SQL in defining them under the column’s meta.