dimension_group

LookML
Version

On this Page
Docs Menu

Go to Field Parameter List

Definition

The dimension_group parameter is used to create a set of time-based dimensions all at once. For example, you could easily create a date, week, and month dimension based on a single timestamp column.

The form of a dimension group is:

- dimension_group: dimension_group_name type: time # always use 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: true | false # defaults to true

These parameters work as follows:

  • type: time is required for all dimension groups.

  • The timeframes parameter is optional, but rarely skipped. It specifies one or more timeframes that should be generated by the dimension group. If timeframes is not included every timeframe option will be added to the dimension group. The possible choices are listed below.

  • The sql parameter takes any valid SQL expression that contains data in a timestamp, datetime, date, epoch, or yyyymmdd format.

  • The datatype 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 below.

  • The convert_tz parameter is optional and lets you prevent automatic timezone conversion. It is described in greater detail below.

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

Timeframe Options

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 timezone 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 ADDED3.54 Datetime truncated to the nearest second 2014-09-03 17:15:00
millisecond ADDED3.54 Datetime truncated to the nearest millisecond (not supported in Clustrix, Denodo, or MySQL prior to 5.6.4) 2014-09-03 17:15:00.000
millisecondX ADDED3.54 Splits each second into intervals with the specified number of milliseconds (not supported in Clustrix, Denodo, or MySQL prior to 5.6.4). Requires explanation, see below. See Below
microsecond ADDED3.54 Datetime truncated to the nearest microsecond (not supported in 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 ADDED4.8 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 ADDED4.8 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 ADDED4.8 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 ADDED4.8 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:

  • 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 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:

  • 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 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:

  • 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 a time of 2014-09-01 01:00:00.333 would have a millisecond250 of 2014-09-01 01:00:00.250.

Specifying the Database datatype

The datatype parameter enables you to specify the type of data that you are supplying to a type: time dimension via the sql parameter.

The datatype 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 form YYYYMMDD

The default value for datatype is datetime.

Timezone 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 timezone, so it is important to keep timezones in mind when writing LookML.

The Looker Admin page enables you to set a Database Time Zone and a Query Time Zone (see this Discourse article for more detail). If the same timezone is chosen for both of these settings, Looker does not perform any timezone conversions, and there is no need to use the convert_tz parameter.

However, if different timezones are chosen, Looker performs a timezone conversion for all time-based dimensions by default. If you do not want Looker to perform a timezone conversion for a particular dimension or dimension group, you can use the convert_tz parameter described here.

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 a more than one timeframe. For more information, check out this article.

Things to Know

It is Possible to Create Individual Time Based Dimensions

It is possible to create one dimension for each individual time frame 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.

Still have questions?
Go to Discourse - or - Email Support
Top