# Dimension Groups

Dimension Groups are a particular type of dimension used for timeframes (referencing the same date column but having slices for it daily, weekly, monthly, etc), and for intervals (referencing the difference between two date columns and slicing it days between, weeks between, months between, etc).

{% hint style="warning" %}
**Avoid reserved words in dimension group names.** Dimension groups generate sub-fields for each timeframe (e.g., `date`, `week`, `month`). If you name a dimension group generically — for example `__time` or `date` — the generated sub-fields will be aliased as `day`, `month`, `time`, `order`, etc., which are reserved words in some SQL dialects and will produce query errors. Use descriptive names like `order_date`, `created_at`, or `shipped_at` instead.
{% endhint %}

## Properties

`name`: (Required) The name of the dimension group. If you reference this dimension group in the `default_date` property you will use this name. If you reference this dimension group elsewhere, in sets, other dimensions, etc you will use syntax as follows: `name_timeframe`. Like all names, it follows [Zenlytic naming conventions](/data-modeling/data_modeling.md#naming-conventions)

`field_type`: (Required) The field type of the field. For dimension groups this is always `dimension_group`.

`type`: (Required) The type of the field. For dimension groups this is one of `time` or `duration`.

`label`: The label of the dimension group is what shows up to the end users of your data model. If not specified it defaults to the name of the dimension group.

`description`: The description of the dimension group. This is shown in the UI and can help end users understand what the field represents. When this is set and the `zoe_description` property is not set, this will be shown to Zoë. Use this to provide context to Zoë on how to use the field correctly.

`zoe_description`: The description of the dimension group shown to Zoë. If not set, Zoë uses `description` instead. If set, this replaces `description` for Zoë only. End users will still see `description` in the UI. Use this to provide context to Zoë on how to use the field correctly.

`group_label`: The label of the view the field is put into in the left hand sidebar menu. If not specified it defaults to the name of the view the field is present in.

`hidden`: A `true` indicates that this field should be hidden in the user interface. If a field is hidden it can still be referenced in the data model, despite not appearing to end users as a selectable field. The default is false which shows the field in the UI.

`sql`: (Required, only for `type` = time) This is the SQL expression that generates the field value. It can be as simple as `${TABLE}.my_field_name` which just references a column in the database table, or something more advanced that references previously defined fields, like `case when ${channel} ilike '%owned' then 'Yes' else 'No' end`.

You can also reference any [referenceable attributes](/data-modeling/referenceable_attributes.md) and drop them into the `sql` statement here. For example, you can use the query attribute for which dimension group is selected to take advantage of specialized database extensions, like Timescale DB.

{% code overflow="wrap" %}

```yaml
- name: rainfall_at
  field_type: dimension_group
  type: time
  timeframes:
    - raw
    - date
    - week
    - month
  sql: >
    case 
      when '{{ query_attributes['dimension_group'] }}' = 'raw' then ${TABLE}.rain_date 
      when '{{ query_attributes['dimension_group'] }}' = 'date' then time_bucket('1 day', ${TABLE}.rain_date) 
      when '{{ query_attributes['dimension_group'] }}' = 'week' then time_bucket('1 week', ${TABLE}.rain_date) 
      when '{{ query_attributes['dimension_group'] }}' = 'month' then time_bucket('1 month', ${TABLE}.rain_date) 
      else null
    end
```

{% endcode %}

`required_access_grants`: This is a list of [access grant](/data-modeling/access_grants.md) names that are required to access this field. If you list multiple grants, they must all pass for the user to access this field. A missing user attribute on a grant is non-blocking for that grant, because the grant is not triggered. Note, if the user has access to the field but does NOT have access to the view the field is defined in, the user will not be able to see the field.

`synonyms`: This is a list of strings phrases or words that you want to act as synonyms for natural language search. For example, if your measure is named `total_revenue` you might have synonyms of `['total sales', 'income']`. This works like a keyword search under the hood, to make fields with synonyms related to the question asked show up in context for Zoë.

`timeframes`: (Required, only for `type` = time) This property is only for dimension groups of type `time`. It's a list of values which you want to make available to the end user. The options are [listed below](#timeframes).

`intervals`: (Required, only for `type` = duration) This property is only for dimension groups of type `duration`. It's a list of values which you want to make available to the end user. The options are [listed below](#intervals).

`convert_tz`: A yes (or true) indicates that you want the timezone to be converted, a no (or false) indicates you do not want to convert the timezone from the native timezone to the timezone you specified in your model.

`datatype`: This indicates the database type of the date column referenced by the dimension group. The options are `timestamp`, `datetime` and `date`. The default is `timestamp`.

`sql_start`: (Required, only for `type` = duration) This is the SQL expression that generates the field value for the start of the duration.

`sql_end`: (Required, only for `type` = duration) This is the SQL expression that generates the field value for the end of the duration.

`extra`: The extra property is like dbt `meta` property, and you can put whatever additional properties you want in here. For example, under this property you could add a property like this `maintainer: "jane doe"`

## Timeframes

{% hint style="info" %}
Fiscal date reporting

All `fiscal_` timeframes are based on the model's `fiscal_month_offset` property. This let's you set up your fiscal year/quarter/month reporting right in Zenlytic! Find out more information [here](/data-modeling/model.md).
{% endhint %}

The available timeframe options are:

<table><thead><tr><th width="242.55078125">Options</th><th width="503.6015625">Description</th></tr></thead><tbody><tr><td>raw</td><td>Shows the raw value and can be used in references, but is not used in the Zenlytic UI</td></tr><tr><td>time</td><td>Shows the raw timestamp</td></tr><tr><td>second</td><td>Shows the value truncated to the second</td></tr><tr><td>minute</td><td>Shows the value truncated to the minute</td></tr><tr><td>hour</td><td>Shows the value truncated to the hour</td></tr><tr><td>date</td><td>Shows the value truncated to the day</td></tr><tr><td>week</td><td>Shows the value truncated to the week</td></tr><tr><td>month</td><td>Shows the value truncated to the month</td></tr><tr><td>quarter</td><td>Shows the value truncated to the quarter</td></tr><tr><td>year</td><td>Shows the value truncated to the year</td></tr><tr><td>fiscal_month</td><td>Shows the value truncated to the fiscal month</td></tr><tr><td>fiscal_quarter</td><td>Shows the value truncated to the fiscal quarter.</td></tr><tr><td>fiscal_year</td><td>Shows the value truncated to the fiscal year</td></tr><tr><td>week_index</td><td>Shows the week of the year as an integer (alias of week_of_year below)</td></tr><tr><td>week_of_year</td><td>Shows the week of the year as an integer</td></tr><tr><td>week_of_month</td><td>Shows the week of the month as an integer</td></tr><tr><td>month_of_year</td><td>Shows the month of the year as a 3 character string ('Jan', 'Feb', 'Mar', etc)</td></tr><tr><td>month_of_year_full_name</td><td>Shows the month of the year as a string ('January', 'February', 'March', etc)</td></tr><tr><td>month_of_year_index</td><td>Shows the month of the year as an integer</td></tr><tr><td>fiscal_month_index</td><td>Shows the fiscal month of the year as an integer. The first fiscal month is the first month of the fiscal year.</td></tr><tr><td>fiscal_month_of_year_index</td><td>Shows the fiscal month of the year as an integer (alias of the fiscal_month_index)</td></tr><tr><td>month_name</td><td>Shows the month of the year as a 3 character string (alias of month_of_year)</td></tr><tr><td>month_index</td><td>Shows the month of the year as an integer (alias of month_of_year_index)</td></tr><tr><td>quarter_of_year</td><td>Shows the quarter of the year as an integer</td></tr><tr><td>fiscal_quarter_of_year</td><td>Shows the fiscal quarter of the year as an integer</td></tr><tr><td>hour_of_day</td><td>Shows the hour of the day as an integer</td></tr><tr><td>day_of_week</td><td>Shows the day of the week as a 3 character string ('Mon', 'Tue', 'Wed', etc)</td></tr><tr><td>day_of_month</td><td>Shows the day of the month as an integer</td></tr><tr><td>day_of_year</td><td>Shows the day of the year as an integer</td></tr></tbody></table>

## Intervals

The available interval options are:

<table><thead><tr><th width="159.71875">Options</th><th>Description</th></tr></thead><tbody><tr><td>second</td><td>The number of seconds between the <code>sql_start</code> and the <code>sql_end</code></td></tr><tr><td>minute</td><td>The number of minutes between the <code>sql_start</code> and the <code>sql_end</code></td></tr><tr><td>hour</td><td>The number of hours between the <code>sql_start</code> and the <code>sql_end</code></td></tr><tr><td>day</td><td>The number of days between the <code>sql_start</code> and the <code>sql_end</code></td></tr><tr><td>week</td><td>The number of weeks between the <code>sql_start</code> and the <code>sql_end</code></td></tr><tr><td>month</td><td>The number of months between the <code>sql_start</code> and the <code>sql_end</code></td></tr><tr><td>quarter</td><td>The number of quarters between the <code>sql_start</code> and the <code>sql_end</code></td></tr><tr><td>year</td><td>The number of years between the <code>sql_start</code> and the <code>sql_end</code></td></tr></tbody></table>

## Examples

This example shows several fields, the first of which is the table's primary key and the second of which is a dimension group for a date and the third of which is a duration dimension group. In the Zenlytic interface, you'll reference the dates defines in the second field like `order_date`, `order_month`, etc. You'll reference the third field like `days_between_first_order_and_this_order`, `months_between_first_order_and_this_order`, etc.

```yaml
version: 1
type: view
name: order_lines

sql_table_name: prod.order_lines
default_date: order

fields:
- name: order_line_id
  field_type: dimension
  type: string
  sql: ${TABLE}.order_line_id
  primary_key: yes
  hidden: yes

- name: order
  sql: ${TABLE}.order_at
  field_type: dimension_group
  type: time
  timeframes:
  - raw
  - date
  - week
  - month
  - quarter
  - year
  datatype: timestamp

- name: between_first_order_and_this_order
  field_type: dimension_group
  type: duration
  sql_start: ${TABLE}.first_order_date
  sql_end: ${order_raw}
  intervals: [day, week, month, quarter]
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.zenlytic.com/data-modeling/dimension_group.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.