Timeframes and Dimension Groups in Looker

[cutler]

Read about dimension groups in our docs here as well as available timeframes here.

When analyzing time-based data, it’s not uncommon to want to be able to think in terms of several different time frames. Say you’re looking at a field like order_created_at and want to be able to think in terms of date, week, and month. You could create three dimensions: one for each time frame. This is something of a pain, though, and can open up room for error (typographical or otherwise).

Introducing: The dimension_group

Fortunately, we created the dimension_group to simplify the process of defining logically associated dimensions.

  - dimension_group: order_created_at
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.order_created_at

This code is equivalent to writing out each of the dimensions individually, but has a few advantages.

• It’s less prone to error
• It’s more flexible
• It’s easier to write
• It’s easier to read
• It’s already organized

You could always write things out if you really wanted to. Let’s run through that process.

The Long Way

Three Dimensions

Writing out a few time-based dimensions is easy enough. Just create dimensions for each timeframe and you’re set. Maybe you’ll most commonly analyze by date, so it’ll be the default. It makes referencing the order_created_at date time frame a little easier. This would work, and look something like:

  - dimension: order_created_at
    type: date
    sql: ${TABLE}.order_created_at

  - dimension: order_created_at_month
    type: date_month
    sql: ${TABLE}.order_created_at

  - dimension: order_created_at_week
    type: date_week
    sql: ${TABLE}.order_created_at

Things Get Messy

This is all fine and dandy, but what happens if you want to start thinking about the time an order was placed? In order to pull that field up in an Explore or visualization, you’d have to create a new dimension:

  - dimension: order_created_at_time
    type: date_time
    sql: ${TABLE}.order_created_at

As you flesh out your analyses, you’d have to keep on defining new dimensions, which can be a pain. It’s a lot to read through, and might even mess with your code organization. Even worse, what if you accidentally pick the wrong type?

Things get messy and tedious quickly. Plus, as your group of time dimensions grows, the margin for error does too. No good.

Back to the Group

Adding to your dimension group is as simple as attaching another [timeframe] (http://www.looker.com/docs/reference/lookml-reference/dimension-lookml-reference#timeframes) to the list.

 - dimension_group: order_created_at
    type: time
    timeframes: [date, week, month, time]
    sql: ${TABLE}.order_created_at

This (much simpler) block of LookML is nearly equivalent to the two blocks outlined above. It’s easier to write, the code organization is taken care of, and, as you can see, it’s much cleaner. Everyone’s better off, right? Almost.

Calling upon Your Dimension Group

Referencing these dimensions takes some care. You won’t be able to reference your friend order_created_at. It doesn’t exist the same way as when we declared it like dimension: order_created_at (it’s now order_created_at_date). In order to call the rest of the gang, you’ll have to append the appropriate timeframe.

In order to reference the members of the dimension group in your LookML, you’ll need to ask for them like this:

${order_created_at_time}
${order_created_at_date}
${order_created_at_week}
${order_created_at_month}

Keep this in mind and it’ll help you avoid messages like [warning field not found] (https://discourse.looker.com/t/warning-field-not-found/171) and [unknown field substitution] (https://discourse.looker.com/t/error-unknown-field-substitution/151).