Hierarchical Drilling and Drill Menus (3.40+)

#1

As of Looker 3.40, we have introduced dimension drilling and drill menus. Drill menus allow for more data exploration and agility.

What can you do with drill menus?

Drill menus allows you to:

  1. Drill into a row by any other dimension value (i.e. hierarchical drilling). See below.

  2. Drill from any row to a dashboard. This automatically filters that dashboard by that value. Read about how this works here. (currently in labs)

  3. Link to any external or custom explore URL. Read about this here.

  4. All of these drill options will also be used when drilling into visualization. See how here.

Drilling into another dimension

Letā€™s say I have user data by State. I can create a Look that shows the number of users by state like so:

But what if I want to know more about the users in California? Which city has the most users? How many cities are these users spread across?

With hierarchical drilling, I can specify that Users City is a drill field of Users State, meaning that I may want to drill into any state to see data for the cities within that state. This is done using the drill_fields parameter. Each field listed by drill_fields will be added as an individual drill option.

So I can specify Users City as a drill field of Users State like so:

  - dimension: state
    sql: ${TABLE}.state
    drill_fields: [city]

When I add Users State to a Look, I can click on the value to see the drill menu. This drill menu will show any custom drills, as well as a way to filter the current query by the clicked value.

By clicking this drill menu, I can drill into state by city and see all the cities these users are from in California. This will open in the drill overlay (as of Looker 3.42):

Note that this is done by adding Users State = California as a filter.

Drills for dimension groups

Dimension groups have drill fields added to them by default. All timeframes are able to drill to more granular timeframes (i.e. date is more granular than month, which is more granular than year).

Letā€™s say you have the following dimension group:

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

If you add the year timeframe to your Looker, you will automatically be able to drill to month, week, date, or time:

You can also customize which timeframes appear in the drill menu for dimension groups using the drill_fields parameter, like so"

- dimension_group: created                                            
  type: time                                                           
  timeframes: [time, date, week, month, year]
  sql: ${TABLE}.created
  drill_fields: [created_month, created_date]

Note that adding the drill_fields parameter to a dimension group will always bring through the specified timeframes as drill options, rather than only surfacing more granular options.

Other uses of drill menus

Read more about:

  1. Drilling to dashboards (in labs)

  2. Linking to URLs

  3. Drilling in visualizations (in labs)

4 Likes

Dynamically drill within a chart on a dashboard
Linking to URLs in drill menus (3.40+)
Looker 3.40 Release Notes
Subtotals
(Alex Hancock) #2

This is a great step forward. Is it in the roadmap to render these hierarchies in the field picker (so we can group hierarchical dimension levels together in a single place)?

2 Likes

(Morgan Imel) #3

Hi Alex!

Iā€™m happy to pass this idea along to our product team.

Best,
Morgan

0 Likes

(Josh Andrews) #4

Just wanted to upvote Alexā€™s suggestion about a way to represent hierarchies in the field picker. I am working with some Census data which has a hierarchy of State -> County -> Tract -> Block Group -> Block, and it would be nice to be able to communicate this hierarchy intuitively in the field picker to the users.

3 Likes

(Subhasree Chatterjee) #5

Hi Lindsey,

When I included drill_fields to my Date dimension, I am getting the following error:
ā€œERROR: syntax error at or near ā€œdrill_fieldsā€ Position: 1628
raw sql results do not include filled-in values for ā€˜created_dateā€™ā€

Can you please explain what I am doing wrong?

1 Like

(anakarina.lance) #6

Hi Subhasree,

Iā€™d love to investigate why you are seeing this error. I would be able to assist better if you e-mail help.looker.com. It would also be really helpful if you provided a screenshot of the LookML that is returning this error.

Best,
Anakarina

0 Likes