User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
  
English
日本語
dimension_group

Usage

view: view_name {
  dimension_group: field_name {}
}

Hierarchy

dimension_group

Accepts

A Looker identifier (to serve as the first part of the name for each dimension created by the dimension group)

Special Rules

Definition

The dimension_group parameter is used to create a set of time-based or duration-based dimensions all at once. You define the dimension group, and the dimension group will create a set of individual dimensions for different intervals or timeframes. For example, you can specify a time type dimension group based on a timestamp column, and the dimension group will create corresponding dimensions to express the data in time, date, week, hour, quarter, and year.

The form and function of the dimension group are different depending the dimension group’s type value:

Duration Type

type: duration is used in conjunction with a dimension_group to calculate a set of interval-based duration dimensions.

The form of a dimension group with type: duration is:

dimension_group: dimension_group_name { type: duration sql_start: SQL expression ;; # often this is a single database column sql_end: SQL expression ;; # often this is a single database column intervals: [interval, interval, …] # valid intervals described below }

For dimension groups with type: duration:

Although they are not listed here, many of the field-level parameters can be used with dimension groups as well.

As an example, if you have columns for enrollment_date and graduation_date, you can create a duration dimension group to see how much time students spent in school, calculated in week and year intervals:

dimension_group: enrolled { type: duration intervals: [week, year] sql_start: ${TABLE}.enrollment_date ;; sql_end: ${TABLE}.graduation_date ;; }

In the Explore UI, this would generate a dimension group called Duration Enrolled, with individual dimensions called Weeks Enrolled and Years Enrolled.

Interval Options

The intervals parameter tells the dimension group which interval units it should use to measure the time difference between the sql_start time and the sql_end time. The intervals parameter is supported only for dimension groups with type: duration.

If intervals is not included, the dimension group will include all possible intervals.

The options for the intervals parameter are:

Interval Description Example Output
day Calculates a time difference in days. 9 days
hour Calculates a time difference in hours. 171 hours
minute Calculates a time difference in minutes. 10305 minutes
month Calculates a time difference in months. 3 months
quarter Calculates a time difference in quarters of the year. 2 quarters
second Calculates a time difference in seconds. 606770 seconds
week Calculates a time difference in weeks. 6 weeks
year Calculates a time difference in years. 2 years

Defining the Start and End of a Duration

For dimension groups with type: duration, the sql_start and sql_end parameters provide the start and end information used to calculate a time difference. These fields can take any valid SQL expression that contains data in a timestamp, datetime, date, epoch, or yyyymmdd format. The sql_start and sql_end fields can be any of the following:

As an example, suppose you have a dimension named faa_event_date_raw that contains datetime information:

dimension: faa_event_date_raw { type: date_raw sql: ${TABLE}.event_date ;; }

You can create a type: duration dimension group that calculates the amount of time that has passed since the FAA event date. To do this, you can use the faa_event_date_raw dimension as the start time for the calculation, and then for the end time of the calculation you can use your dialect’s SQL expression for the current time. This example is for a MySQL database:

dimension_group: since_event { type: duration intervals: [hour, day] sql_start: ${faa_event_date_raw} ;; sql_end: CURRENT_TIMESTAMP();; }

In the Explore UI, this would generate a dimension group called Duration Since Event, with individual dimensions called Hours Since Event and Days Since Event.

Time Type

type: time is used in conjunction with a dimension_group and the timeframes parameter to create a set of time-based dimensions. For example, you could easily create a date, week, and month dimension based on a single timestamp column.

The form of a dimension group of type: time is:

dimension_group: dimension_group_name { type: time timeframes: [timeframe, timeframe, …] # valid timeframes described below sql: SQL expression ;; # often this is a single database column datatype: epoch| timestamp | datetime | date | yyyymmdd # defaults to datetime convert_tz: yes | no # defaults to yes }

For dimension groups with type: time:

Although they are not listed here, many of the field-level parameters can be used with dimension groups as well.

As an example, suppose you had a column named created_at that contained datetime information. You want to create a date, week, and month dimension based on this datetime. You could use:

dimension_group: created { type: time timeframes: [date, week, month] sql: ${TABLE}.created_at ;; }

In the Explore UI this would generate three dimensions with the names Created Date, Created Week, and Created Month. Note how the dimension_group name is combined with the timeframes to generate the dimension names.

Timeframe Options

The timeframes parameter is supported only for dimension groups with type: time. For dimension groups with type: duration, use instead the intervals parameter.

The timeframes parameter tells the dimension group which dimensions it should produce. The options are:

Special Timeframes

Timeframe Description Example Output
raw The raw value from your database, without casting or time zone conversion, will not show up on Explore page (typically not needed except in joins or time comparisons) 2014-09-03 17:15:00 +0000
yesno A yesno dimension, returning “Yes” if the datetime has a value, otherwise “No” Yes

Time Timeframes

Timeframe Description Example Output
time Datetime of the underlying field (some SQL dialects show as much precision as your database contains, while others show only to seconds) 2014-09-03 17:15:00
time_of_day Time of day 17:15
hour Datetime truncated to the nearest hour 2014-09-03 17
hour_of_day Integer hour of day of the underlying field 17
hourX Splits each day into intervals with the specified number of hours. Requires explanation, see below. See Below
minute Datetime truncated to the nearest minute 2014-09-03 17:15
minuteX Splits each hour into intervals with the specified number of minutes. Requires explanation, see below. See Below
second Datetime truncated to the nearest second 2014-09-03 17:15:00
millisecond Datetime truncated to the nearest millisecond (not supported in BigQuery, Clustrix, Denodo, or MySQL prior to 5.6.4) 2014-09-03 17:15:00.000
millisecondX Splits each second into intervals with the specified number of milliseconds (not supported in BigQuery, Clustrix, Denodo, or MySQL prior to 5.6.4). Requires explanation, see below. See Below
microsecond Datetime truncated to the nearest microsecond (not supported in BigQuery, Clustrix, Denodo, or MySQL prior to 5.6.4) 2014-09-03 17:15:00.000000

Date Timeframes

Timeframe Description Example Output
date Date of the underlying field 2017-09-03

Week Timeframes

Timeframe Description Example Output
week Date of the week starting on a Monday of the underlying datetime 2017-09-01
day_of_week Day of week alone Wednesday
day_of_week_index Day of week index (0 = Monday, 6 = Sunday) 2

Month Timeframes

Timeframe Description Example Output
month Year and month of the underlying datetime 2014-09
month_num Integer number of the month of the underlying datetime 9
fiscal_month_num Integer number of the fiscal month of the underlying datetime 6
month_name Name of the month September
day_of_month Day of month 3

To use the fiscal_month_num timeframes, the fiscal_month_offset parameter must be set in the model.

Quarter Timeframes

Timeframe Description Example Output
quarter Year and quarter of the underlying datetime 2017-Q3
fiscal_quarter Fiscal year and quarter of the underlying datetime 2017-Q3
quarter_of_year Quarter of the year preceded by a “Q” Q3
fiscal_quarter_of_year Fiscal quarter of the year preceded by a “Q” Q3

To use the fiscal_quarter and fiscal_quarter_of_year timeframes, the fiscal_month_offset parameter must be set in the model.

Year Timeframes

Timeframe Description Example Output
year Integer year of the underlying datetime 2017
fiscal_year Integer fiscal year of the underlying datetime 2017
day_of_year Day of year 143
week_of_year Week of the year as a number 17

To use the fiscal_year timeframe, the fiscal_month_offset parameter must be set in the model.

Using hourX

In hourX the X is replaced with 2, 3, 4, 6, 8, or 12.

This will split up each day into intervals with the specified number of hours. For example hour6 will split each day into 6 hour segments, which will appear like:

To give an example, a row with a time of 2014-09-01 08:03:17 would have a hour6 of 2014-09-01 06:00:00.

Using minuteX

In minuteX the X is replaced with 2, 3, 4, 5, 6, 10, 12, 15, 20, or 30.

This will split up each hour into intervals with the specified number of minutes. For example minute15 will split each hour into 15 minute segments, which will appear like:

To give an example, a row with a time of 2014-09-01 01:17:35 would have a minute15 of 2014-09-01 01:15:00.

Using millisecondX

In millisecondX the X is replaced with 2, 4, 5, 8, 10, 20, 25, 40, 50, 100, 125, 200, 250, or 500.

This will split up each second into intervals with the specified number of milliseconds. For example millisecond250 will split each second into 250 millisecond segments, which will appear like:

To give an example, a row with a time of 2014-09-01 01:00:00.333 would have a millisecond250 of 2014-09-01 01:00:00.250.

Time Zone Conversions and convert_tz

In general, time computations (differences, durations, etc.) only work correctly when you operate on time values that are all converted to the same time zone, so it is important to keep time zones in mind when writing LookML.

Looker has various time zone settings that convert time-based data between different time zones. Looker does time zone conversion by default. The convert_tz parameter is supported for dimension groups with type: time. If you do not want Looker to perform a time zone conversion for a particular dimension or dimension group, you can use the convert_tz parameter described on this documentation page.

Specifying the Database datatype

The datatype parameter lets you specify the type of time data in your database table that you are supplying to the dimension group, which can increase query performance.

For dimension groups with type: time, the datatype parameter applies to the sql parameter of the dimension group

For dimension groups with type: duration, the datatype parameter applies to both the sql_start and sql_end parameters, so be sure the sql_start and sql_end are both of the specified data type.

The datatype parameter accepts the following values:

The default value for datatype is datetime.

Examples

Suppose you had a column named created_at that contained datetime information. You want to create a date, week, and month dimension based on this datetime. You could use:

dimension_group: created { type: time timeframes: [date, week, month] sql: ${TABLE}.created_at ;; }

In the Explore UI this would generate three dimensions with the names Created Date, Created Week, and Created Month. Note how the dimension_group name is combined with the timeframes to generate the dimension names.

Common Challenges

Dimension Groups Must Be Referenced by Their Individual Dimensions

Because a dimension group represents a group of dimensions, instead of just one dimension, you cannot refer to it directly in LookML. Instead, you’ll need to refer to the dimensions it creates.

For example, consider this dimension group:

dimension_group: created { type: time timeframes: [date, week, month] sql: ${TABLE}.created_at ;; }

If you want to refer to one of these dimensions in another LookML field, you would use the reference ${created_date}, ${created_week}, or {$created_month}. If you try to use just ${created}, Looker will not know which timeframe you are referring to and an error will result.

For this same reason, you should not use the primary_key parameter on a dimension group if you specify more than one timeframe.

Chat Team Tip: We are frequently asked about the validation error that occurs if using primary_key on a dimension_group with more than one timeframe. For more information, check out this Community topic.

Things to Know

It is Possible to Create Individual Time or Duration Dimensions

It is possible to create one dimension for each individual time frame or duration you want to include, instead of generating all of them in a single dimension_group. This is generally avoided, unless you want to change Looker’s timeframe naming convention, or if you have already pre-calculated time columns in your database. For more information, see this page.

You Can Change the First Day of the Week

By default, weeks in Looker start on Monday. You can change this by using the the week_start_day parameter at the model level.

Just keep in mind that week_start_day does not work with the week_of_year timeframe because that timeframe is based on the ISO standard, which uses Monday weeks.

Top