Timeframes and Dimension Groups in Looker

(Carter Moar) #1

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:


Keep this in mind and it’ll help you avoid messages like [warning field not found] (Warning: Field Not Found) and [unknown field substitution] (Error: Unknown field substitution).

How to perform date operations on a Looker timeframe
Hierarchical Drilling and Drill Menus (3.40+)
LookML Best Practices
Error: Unknown or inaccessible field
Display latest month data and same month data of previous years
Type 'time' Should Be Used with Dimension Groups Only
Using always_filter on a PDT column
Filtering a date to the current date and the first of the month
[Analytic Block] Cohorting Users by Creation Date and Order Transactions

Readers should note that this UI is now possible not only for type: time dimensions: see Grouping Fields Within Views (3.44+).

(Ben Cipollini) #3

Is there way to define timeframes like sets, where I can use the same timeframes over all dimensions without having to list them out for every dimension?

We have so many date fields, and we’re constantly missing some of the timeframes that we want on them. Dealing with this manually is tiresome and error-prone.


(diego.campos) #4

Hi @bcipollini,
Thanks for your request.

Being able to use the dimension_gorup in a set rather than having to specify every single timeframe individually is not currently possible in Looker. I am passing your thoughts to the Product team in order to consider this functionality on future releases.