Usage

view: view_name {  dimension_group:field_name{ ...}}

Definition

Thedimension_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 differentintervals ortimeframes. For example, you can specify a dimension group oftype: time 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 thetype value of the dimension group:

• Duration type
• Time type

Duration type dimension groups

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

The form of a dimension group oftype: 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, …] # see following explanation for valid intervals}

For dimension groups oftype: duration:

• Thesql_start andsql_end parameters provide SQL expressions defining the start time and end time for the duration. See theDefining the start and end of a duration section on this page for details.

• Theintervals parameter specifies one or more interval units that should be used to measure the time difference. The possible choices are listed in theInterval options section on this page.

• The duration values are floored to the nearest integer.

• Thedatatype parameter is optional. If your dimension group is not based on a datetime you may specify an epoch, timestamp, date, or yyyymmdd format instead. For dimension groups oftype: duration, thedatatype parameter applies to both thesql_start andsql_end parameters, so be sure thesql_start andsql_end are both of the specified data type. Thedatatype parameter is described in greater detail in theSpecifying the databasedatatype section on this page.

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

As an example, if you have columns forenrollment_date andgraduation_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 calledDuration Enrolled, with individual dimensions calledWeeks Enrolled andYears Enrolled.

Interval options

Theintervals parameter tells the dimension group which interval units it should use to measure the time difference between thesql_start time and thesql_end time. Theintervals parameter is supported only for dimension groups oftype: duration.

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

The options for theintervals parameter are:

Defining the start and end of a duration

For dimension groups oftype: duration, thesql_start andsql_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. Thesql_start andsql_end fields can be any of the following:

• A reference to araw timeframe from an existing dimension group oftype: time
• A reference to a dimension oftype: date_raw
• A SQL expression that is a timestamp, such as a reference to a SQL column that is a timestamp
• A SQL expression that pulls a time from your database, using the appropriate expression for your dialect
• A LookML field reference using the::datetime or::datefield type reference

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

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

You can create a dimension group oftype: duration that calculates the amount of time that has passed since the FAA event date. To do this, you can use thefaa_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 calledDuration Since Event, with individual dimensions calledHours Since Event andDays Since Event.

Referencing intervals from another LookML field

To reference aninterval value in adimension_group oftype: duration, use the syntax${interval_fieldname}, using the plural version of theinterval value. For example, in the following LookML example, theaverage_days_since_event measure uses${days_since_event} to reference theday interval in thesince_event dimension group:

dimension_group: since_event {  type: duration  intervals: [hour, day, week, month, quarter, year]  sql_start: ${faa_event_date_raw} ;;  sql_end: CURRENT_TIMESTAMP();;}measure: average_days_since_event {  type: average  sql: ${days_since_event} ;;}

Using LookML field type references with duration fields

To create a custom duration field, you can specify a::date or::datetime reference type for the dimensions referenced in thesql_start andsql_end parameters of a dimension group oftype: duration. Theview_name.field_name::type syntax, described on theIncorporating SQL and referring to LookML objects documentation page, lets you create a::date or::datetime version of a field without casting the references to those dimensions to strings.

For example, suppose you have acreated dimension group oftype: time with timeframes oftime,date,week,month, andraw, defined as follows:

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

Using the dimensionscreated_month andcreated_time, you can create a dimension group oftype: duration that calculates the amount of time between a date from thecreated_date field and the first day of the month in which that date occurred, measured in weeks, days, and hours:

dimension_group: since_first_of_month {  type: duration  intervals: [week, day, hour]  sql_start: ${created_month::datetime} ;;  sql_end: ${created_time::datetime} ;;}

In the Explore UI, this creates a dimension group calledDuration Since First of Month, with individual dimensionsWeeks Since First of Month,Days Since First of Month, andHours Since First of Month. Specifying the::datetime reference type for the fields referenced in thesql_start andsql_end parameters allows thecreated_month andcreated_time dimensions to be treated as timestamps in the generated SQL.

As an example, suppose a user selects theCreated Date andDays Since First of Month dimensions from the field picker. If one of the values returned forCreated Date is2019-03-10, then the value returned forDays Since First of Month will be9 days.

Time type dimension groups

type: time is used in conjunction with adimension_group and thetimeframes 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 oftype: time is:

dimension_group: dimension_group_name {  type: time  timeframes: [timeframe, timeframe, …] # see following explanation for valid timeframes  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 oftype: time:

• Thetimeframes parameter is optional but is rarely skipped. It specifies one or more timeframes that should be generated by the dimension group. Iftimeframes is not included every timeframe option will be added to the dimension group. The possible choices are listed in theTimeframe options section on this page.

• Thesql parameter fortype: time dimension groups can take any valid SQL expression that contains data in a timestamp, datetime, date, epoch, or yyyymmdd format.

• Thedatatype parameter is optional. If your dimension group is not based on a datetime, you may specify an epoch, timestamp, date, or yyyymmdd format instead. It is described in greater detail in theSpecifying the databasedatatype section on this page.

• Theconvert_tz parameter is optional and lets you prevent automatic time zone conversion. It is described in greater detail in theTime zone conversions andconvert_tz section on this page.

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

As an example, suppose you had a column namedcreated_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 namesCreated Date,Created Week, andCreated Month. Note how thedimension_group name is combined with the timeframes to generate the dimension names.

Timeframe options

Thetimeframes parameter is supported only for dimension groups oftype: time. For dimension groups oftype: duration, use theintervals parameter instead.

Thetimeframes parameter tells the dimension group which dimensions it should produce and includes the following options:

• Special timeframes
• Time timeframes
• Date timeframes
• Week timeframes
• Month timeframes
• Quarter timeframes
• Year timeframes
• hourX timeframes
• minuteX timeframes
• millisecondX timeframes

Special timeframes

Time timeframes

Date timeframes

Week timeframes

Month timeframes

To use thefiscal_month_num timeframes, thefiscal_month_offset parameter must be set in the model.

Quarter timeframes

To use thefiscal_quarter andfiscal_quarter_of_year timeframes, thefiscal_month_offset parameter must be set in the model.

Year timeframes

To use thefiscal_year timeframe, thefiscal_month_offset parameter must be set in the model.

UsinghourX

InhourX theX 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 as follows:

• 2014-09-01 00:00:00
• 2014-09-01 06:00:00
• 2014-09-01 12:00:00
• 2014-09-01 18:00:00

To give an example, a row with atime of2014-09-01 08:03:17 would have ahour6 of2014-09-01 06:00:00.

UsingminuteX

InminuteX theX 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 as follows:

• 2014-09-01 01:00:00
• 2014-09-01 01:15:00
• 2014-09-01 01:30:00
• 2014-09-01 01:45:00

To give an example, a row with atime of2014-09-01 01:17:35 would have aminute15 of2014-09-01 01:15:00.

UsingmillisecondX

InmillisecondX theX 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 as follows:

• 2014-09-01 01:00:00.000
• 2014-09-01 01:00:00.250
• 2014-09-01 01:00:00.500
• 2014-09-01 01:00:00.750

To give an example, a row with atime of2014-09-01 01:00:00.333 would have amillisecond250 of2014-09-01 01:00:00.250.

Time zone conversions andconvert_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 varioustime zone settings that convert time-based data between different time zones. Looker does time zone conversion by default. Theconvert_tz parameter is supported for dimension groups oftype: time. If you don't want Looker to perform a time zone conversion for a particular dimension or dimension group, you can use theconvert_tz parameter described on theconvert_tz parameter documentation page.

Dialect support for milliseconds and microseconds

Looker supports timeframe precision to microseconds; however, some databases support precision only to the second. If a database encounters a timeframe more precise than it can support, it will round up to seconds.

In the latest release of Looker, the following dialects support milliseconds:

In the latest release of Looker, the following dialects support microseconds:

Specifying the databasedatatype

Thedatatype 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 oftype: time, thedatatype parameter applies to thesql parameter of the dimension group.

For dimension groups oftype: duration, thedatatype parameter applies to both thesql_start andsql_end parameters, so be sure thesql_start andsql_end are both of the specified data type.

Thedatatype parameter accepts the following values:

• epoch: A SQL epoch field (i.e., an integer representing the number of seconds from the Unix epoch).
• date: A SQL date field (i.e., one that does not contain time of day information).
• datetime: A SQL datetime field.
• timestamp: A SQL timestamp field.
• yyyymmdd: A SQL field that contains an integer that represents a date of the formYYYYMMDD.

The default value fordatatype istimestamp.

Examples

Suppose you had a column namedcreated_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 namesCreated Date,Created Week, andCreated Month. Note how thedimension_group name is combined with the timeframes to generate the dimension names.

Things to consider

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 ;;}

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

For this same reason, you should not use theprimary_key parameter on a dimension group if you specify more than onetimeframe.

Chat Team Tip: We are frequently asked about the validation error that can occur if you're usingprimary_key on adimension_group with more than onetimeframe. For more information, check out theTimeframes and Dimension Groups in Looker Community post.

Timestamp data that includes time zone information

Some database dialects have timestamp options that include time zone information. This lets you store timestamp data in a single field that may have multiple time zones. One row of data might be stored in UTC, another row in Eastern time. As an example, see theSnowflakeTIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ timestamp documentation for information about the Snowflake dialect timestamp options.

In this case, when Looker performs time zone conversions, errors can occur. To avoid this, in thesql parameter of the dimension, you should explicitly cast the timestamp data to a timestamp type that does not do time zone conversion. For example, in the Snowflake dialect, you could use theTO_TIMESTAMP function to cast the timestamp data.

It is possible to create individual time or duration dimensions

It is possible to create one dimension for each individual timeframe or duration you want to include, instead of generating all of them in a singledimension_group. You can generally avoid creating individual dimensions, unless you want to change Looker's timeframe naming convention, or you have already pre-calculated time columns in your database. For more information, see theDimension, filter, and parameter types documentation page.

You can change the first day of the week

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

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

Custom filters and custom fields don't support all timeframes

Thetimeframesday_of_week,fiscal_quarter_of_year,millisecond,millisecondX,microsecond,month_name,quarter_of_year, andtime_of_day are not supported incustom filters orcustom fields.

Month, quarter, and year intervals only count complete periods

Themonth interval in aduration dimension group only considers a month to have passed if the ending day is greater than or equal to the starting day.For example:

• The difference in months between September 26 and October 25 of the same year is 0.
• The difference in months between September 26 and October 26 of the same year is 1.

Thequarter andyear intervals follow the same logic.