More Powerful Data Drilling

liquid
drillmenu
lookml
visualizations
customvisualization

(Bryan Weber) #1

Basic Measure Drilling

One of the unique things about Looker is that it connects directly to your database. That means you can always access the freshest data and that you can always drill down to the most granular level available. So while of course you can see yearly or monthly summaries, Looker gives you the option of drilling in to the day, hour or even second instantly.

We can see this below, where we start with an all-time category overview, then drill to a monthly sales chart of a single category (Jeans).

Let’s get interesting

But Looker’s web-native, modern architecture means that you can do far more than just drill from one level to the next-most granular. You can build out any custom drill path that your heart desires, no matter how sophisticated, with just a few simple parameters.

To demonstrate this, below I’ve built out sample code for some of the most common drill patterns, including:

  • Sorting and limiting (e.g. show me the top 20)
  • Pivoting data
  • Drilling to advanced visualizations with trendlines
  • Drilling to any custom viz you can build with D3 and Javascript
  • Drilling to a table calculation with conditional formatting

Adding Custom Limits (up to 5000)

Just show me the first 20 results

LookML
  measure: returned_count {
    type: count_distinct
    sql: ${id} ;;
    filters: {
      field: is_returned
      value: "yes"
    }
    drill_fields: [detail*]
    link: {label: "Explore Top 20 Results" url: "{{ link }}&limit=20" }
  }

Adding Sorts

Show me the Top 20 by Sale Price

LookML
  measure: returned_count {
    type: count_distinct
    sql: ${id} ;;
    filters: {
      field: is_returned
      value: "yes"
    }
    drill_fields: [detail*]
    link: {label: "Explore Top 20 Results by Sale Price" url: "{{ link }}&sorts=order_items.sale_price+desc&limit=20" }
  }

Adding Pivots

Show me the Year and Gross Margin Percentage Tier for each Age Group

LookML
  measure: order_count {
    type: count_distinct
    drill_fields: [created_year, item_gross_margin_percentage_tier, users.age_tier, total_sale_price]
    link: {label: "Total Sale Price by Month for each Age Tier" url: "{{link}}&pivots=users.age_tier"}
    sql: ${order_id} ;;
  }

##Using Visual Drilling
This has all been good to look at data for, but what if we want to visualize it? Looker has a beta feature called Visual Drilling that allows users to drill into a chart. With 0 customization and a limited drill set, we can get lots of different visualizations.

Show me the how many items we’ve sold by day

LookML
  measure: count {
    type: count_distinct
    sql: ${id} ;;
    drill_fields: [created_date, total_sale_price]
  }

Visual Drilling was based on simple approach, the next question you’re asking is how can I control what visualization Looker shows?

Make Scatter Plot with Limit and Moving Average

Show me the how many items we’ve sold by day with a 30-day moving average

LookML
  measure: count {
    type: count_distinct
    sql: ${id} ;;
    drill_fields: [created_date, total_sale_price]
    link: {
      label: "Show as scatter plot"
      url: "
      {% assign vis_config = '{
  \"stacking\"                  : \"\",
  \"show_value_labels\"         : false,
  \"label_density\"             : 25,
  \"legend_position\"           : \"center\",
  \"x_axis_gridlines\"          : true,
  \"y_axis_gridlines\"          : true,
  \"show_view_names\"           : false,
  \"limit_displayed_rows\"      : false,
  \"y_axis_combined\"           : true,
  \"show_y_axis_labels\"        : true,
  \"show_y_axis_ticks\"         : true,
  \"y_axis_tick_density\"       : \"default\",
  \"y_axis_tick_density_custom\": 5,
  \"show_x_axis_label\"         : false,
  \"show_x_axis_ticks\"         : true,
  \"x_axis_scale\"              : \"auto\",
  \"y_axis_scale_mode\"         : \"linear\",
  \"show_null_points\"          : true,
  \"point_style\"               : \"circle\",
  \"ordering\"                  : \"none\",
  \"show_null_labels\"          : false,
  \"show_totals_labels\"        : false,
  \"show_silhouette\"           : false,
  \"totals_color\"              : \"#808080\",
  \"type\"                      : \"looker_scatter\",
  \"interpolation\"             : \"linear\",
  \"series_types\"              : {},
  \"colors\": [
    \"palette: Santa Cruz\"
  ],
  \"series_colors\"             : {},
  \"x_axis_datetime_tick_count\": null,
  \"trend_lines\": [
    {
      \"color\"             : \"#000000\",
      \"label_position\"    : \"left\",
      \"period\"            : 30,
      \"regression_type\"   : \"average\",
      \"series_index\"      : 1,
      \"show_label\"        : true,
      \"label_type\"        : \"string\",
      \"label\"             : \"30 day moving average\"
    }
  ]
}' %}
        {{ link }}&vis_config={{ vis_config | encode_uri }}&toggle=dat,pik,vis&limit=5000"
    }
  }

What’s Happening Here?

We’re passing Looker’s visualization settings into a URL using Liquid Variables. These settings will control the Visual Drill modal. Here are some more examples.

Pivots + Stacked Line Chart

LookML
  measure: total_sale_price {
    type: sum
    value_format_name: usd
    sql: ${sale_price} ;;
    drill_fields: [total_sale_price, created_month_name, created_year]
    link: {
      label: "Show as stacked line"
      url: "
      {% assign vis_config = '{
  \"stacking\"              : \"normal\",
  \"legend_position\"       : \"right\",
  \"x_axis_gridlines\"      : false,
  \"y_axis_gridlines\"      : true,
  \"show_view_names\"       : false,
  \"y_axis_combined\"       : true,
  \"show_y_axis_labels\"    : true,
  \"show_y_axis_ticks\"     : true,
  \"y_axis_tick_density\"   : \"default\",
  \"show_x_axis_label\"     : true,
  \"show_x_axis_ticks\"     : true,
  \"show_null_points\"      : false,
  \"interpolation\"         : \"monotone\",
  \"type\"                  : \"looker_line\",
  \"colors\": [
    \"#5245ed\",
    \"#ff8f95\",
    \"#1ea8df\",
    \"#353b49\",
    \"#49cec1\",
    \"#b3a0dd\"
  ],
  \"x_axis_label\"          : \"Month Number\"
}' %}
        {{ link }}&vis_config={{ vis_config | encode_uri }}&sorts=order_items.created_year+asc,order_items.created_month_name+asc&pivots=order_items.created_year&toggle=dat,pik,vis&limit=500&column_limit=15"
    } # NOTE the &pivots=
  }

Drilling to a Custom Visualization

LookML
  measure: average_shipping_time {
    type: average
    value_format_name: decimal_2
    sql: ${shipping_time} ;;
    drill_fields: [products.category, users.age_tier, average_shipping_time]
    link: { label: "See as custom viz (heatmap)"
      url: "
      {% assign vis_config = '{
      \"minColor\"              : \"#d6d6d6\",
      \"maxColor\"              : \"#9a33e3\",
      \"dataLabels\"            : false,
      \"custom_color_enabled\"  : false,
      \"custom_color\"          : \"forestgreen\",
      \"show_single_value_title\": true,
      \"show_comparison\"       : false,
      \"comparison_type\"       : \"value\",
      \"comparison_reverse_colors\": false,
      \"show_comparison_label\" : true,
      \"show_view_names\"       : true,
      \"show_row_numbers\"      : true,
      \"truncate_column_names\" : false,
      \"hide_totals\"           : false,
      \"hide_row_totals\"       : false,
      \"table_theme\"           : \"editable\",
      \"limit_displayed_rows\"  : false,
      \"enable_conditional_formatting\": false,
      \"conditional_formatting_include_totals\": false,
      \"conditional_formatting_include_nulls\": false,
      \"type\"                  : \"highcharts_heatmap\",
      \"stacking\"              : \"\",
      \"show_value_labels\"     : false,
      \"label_density\"         : 25,
      \"legend_position\"       : \"center\",
      \"x_axis_gridlines\"      : false,
      \"y_axis_gridlines\"      : true,
      \"y_axis_combined\"       : true,
      \"show_y_axis_labels\"    : true,
      \"show_y_axis_ticks\"     : true,
      \"y_axis_tick_density\"   : \"default\",
      \"y_axis_tick_density_custom\": 5,
      \"show_x_axis_label\"     : true,
      \"show_x_axis_ticks\"     : true,
      \"x_axis_scale\"          : \"auto\",
      \"y_axis_scale_mode\"     : \"linear\",
      \"ordering\"              : \"none\",
      \"show_null_labels\"      : false,
      \"show_totals_labels\"    : false,
      \"show_silhouette\"       : false,
      \"totals_color\"          : \"#808080\",
      \"series_types\"          : {},
      \"hidden_fields\"         : [
      \"order_items.count\",
      \"order_items.total_sale_price\"
      ]
      }' %}
      {{ link }}&vis_config={{ vis_config | encode_uri }}&sorts=products.category+asc,users.age_tier+asc&toggle=dat,pik,vis&limit=5000"
    }
  }

Drilling to a Table Calculation with Conditional Formatting

LookML
  measure: total_sale_price {
    type: sum
    value_format_name: usd
    sql: ${sale_price} ;;
    drill_fields: [created_month, users.gender, total_sale_price]
    link: {
      label: "Table Calc & Total"
      url: "
      {% assign table_calc = '[
  { \"table_calculation\": \"percent_of_total\",
    \"label\": \"Percent of Total\",
    \"expression\": \"${order_items.total_sale_price:row_total} / sum(${order_items.total_sale_price:row_total})\",
    \"value_format\": null,
    \"value_format_name\": \"percent_2\",
    \"_kind_hint\": \"supermeasure\",
    \"_type_hint\": \"number\"
  },
  { \"table_calculation\": \"growth_rate\",
    \"label\": \"Growth Rate\",
    \"expression\": \"${order_items.total_sale_price} / offset(${order_items.total_sale_price},1) - 1\",
    \"value_format\": null,
    \"value_format_name\": \"percent_2\",
    \"_kind_hint\": \"measure\",
    \"_type_hint\": \"number\"
  }]' %} 
      {% assign vis_config = '{
  \"type\": \"table\",
  \"show_view_names\": false,
  \"show_row_numbers\": false,
  \"truncate_column_names\": false,
  \"table_theme\": \"gray\",
  \"enable_conditional_formatting\": true,
  \"conditional_formatting\": [
    {
      \"type\": \"low to high\",
      \"value\": null,
      \"background_color\": null,
      \"font_color\": null,
      \"palette\": {
        \"name\": \"Custom\",
        \"colors\": [
          \"#FFFFFF\",
          \"#6e00ff\"
        ]},
      \"bold\": false,
      \"italic\": false,
      \"strikethrough\": false,
      \"fields\": [
        \"growth_rate\"
      ]},{
      \"type\": \"low to high\",
      \"value\": null,
      \"background_color\": null,
      \"font_color\": null,
      \"palette\": {
        \"name\": \"Custom\",
        \"colors\": [
          \"#FFFFFF\",
          \"#88ff78\"
        ]},
      \"bold\": false,
      \"italic\": false,
      \"strikethrough\": false,
      \"fields\": [
        \"percent_of_total\"
      ]}]}' %}
{{link}}&total=on&row_total=right&dynamic_fields={{ table_calc | replace: '  ', '' | encode_uri }}&pivots=users.gender&vis_config={{ vis_config | replace: '  ', '' | encode_uri }}"
    }
  }

Generating a PowerPoint presentation from all Looks in a Space
A drill that takes me to a pivot table
How to hide view label/group label from drill fields when display to the user
Drill Fields - Pivot
How to hide view label/group label from drill fields when display to the user
Dashboard Dynamic sort
(Tom Pakeman) #2

Here is how to generate the correct LookML for a visual drill down from an existing visualisation:

  1. Create the visualisation you want to drill down to in Looker
  2. Go to ‘Share’ and copy the Expanded URL
  3. In your LookML go the measure you want to create the visual drill for and create a link parameter. We are going to amend the expanded URL and put it inside the url parameter in the link.
  4. The URL will begin with your Looker hostname and the explore path:
    https://company.looker.com/explore/model_n/explore_n
    This will be followed by a ?to start the URL ‘query’ which will contain elements separated by &
    ?fields=view.field_1,view.field_2&pivots=view.field_1&fill_fields=view.field_1...
    This is followed by things like visualisation settings and table calculations in a much harder to read format, but we don’t need to worry about changing those here.
  5. We will replace the host and path with {{link}} and move the fields section of the query to the drill_fields LookML parameter. The rest of the URL query is preserved, including the leading &

Here is an example:

Expanded URL:
https://company.looker.com/explore/model_n/explore_n?fields=view.field_1,view.field_2&pivots=view.field_1&fill_fields=view.field_1... etc.

Visual Drill LookML:

drill_fields: [view.field_1, view.field_2]
link: {
url: "{{link}}&pivots=view.field_1&fill_fields=view.field_1..."  }

(Tom Pakeman) #3

If you don’t want to just copy the expanded URL link into your LookML (as detailed above) but instead you want to unpack and display all of the different visualisation options inside the LookML so you can edit them manually (as displayed in this discourse article), then you can use the Python 3 script below instead.

Please note that this has not been extensively tested and may break - especially when you have complex functionality in your vis, such as trend lines, table calculations and custom formatting.

def convert_url_to_vis_string(expanded_url):
    """Input an expanded Looker Visualisation URL and this will print
    the relevant url param to generate a custom drill down.
    Note that this function prints the output rather than returning it"""
    from urllib.parse import urlparse, unquote_plus as unquote
    parsed_url = urlparse(expanded_url)
    query_components = unquote(parsed_url.query).split('&')
    query_dict = {}
    has_filter_config = False
    filter_config_url = ''
    for c in query_components:
        if c[:13] == 'filter_config':
            has_filter_config=True
            filter_config = c[14:].replace("'", '"').replace('"', '\\"')
            filter_config_url = "&filter_config={{ filter_config | encode_uri }}"
        else:
            c = c.split('=')
            query_dict[c[0]] = c[1]
    vis_string = query_dict['vis'].replace("'", '"').replace('"', '\\"')
    try:
        dynamic_fields = query_dict['dynamic_fields']
        dynamic_fields = dynamic_fields.replace("'", '"').replace('"', '\\"')
        table_calc_url = "&dynamic_fields={{ table_calc | replace: '  ', '' | encode_uri }}"
        has_table_calc = True
    except KeyError as e:
        has_table_calc = False
        table_calc_url = ''
    ignores = ['fields', 'vis', 'origin', 'dynamic_fields']
    other_params = ["&{}={}".format(k, v.replace("'", '"').replace('"', '\\"'))
        for k, v in query_dict.items() if k not in ignores and k[:2] != 'f[' and v != '']
    print("url: \"")
    if has_table_calc:
        print("{{% assign table_calc = '{}' %}} ".format(dynamic_fields))
    if has_filter_config:
        print("{{% assign filter_config = '{}' %}} ".format(filter_config))
    print("{% assign vis_config = '")
    lines = vis_string.split(',')
    for line in lines[:-1]:
        print("\t{} ,".format(line))
    print("\t{}' %}}\n\n{{{{ link }}}}&vis_config={{{{ vis_config | encode_uri }}}}"
        "{}{}{}\"".format(lines[-1], ''.join(other_params), filter_config_url, table_calc_url))

(Hugo Selbie) #4

Drilling takes place on measures, however if you enable the visual drilling labs feature the typical measure drill overlay will appear on a dimension. If you would like to customise those drilling modals, it’s possible using the technique above but we need to create a dummy measure and then reference that regarding some liquid magic.

  1. Create your dummy measures with the drill fields declared that you would like to drill by

  2. Using the fields you declared in the drill fields create the visualization you would like to drill to.

  3. Using @Tom_Pakeman great ways to generate the url above (e.g. get the expanded share url etc.)

  4. Replace the{{ link }} with the dummy measure {{ dummy._link }} and you should be good to go.

The advantage of this method with dimensions is that users who are view only can still see a drill overlay assuming they have the see_drill_overlay permission and don’t need explore access to drill on a dimension.

The code example below

  measure: dummy {
     type: number
     sql: 1=1 ;;
     drill_fields: [first_name, state, count]
  }

  dimension: first_name {
     type: string
     drill_fields: [gender, count]
     sql: ${TABLE}.first_name ;;
     link: {
         label: "tetsy"
         url: "
             {% assign filter_config = '{}' %}
             {% assign vis_config = '
             {\"type\":\"table\"}' %}

             {{ dummy._link }}&vis_config={{ vis_config | encode_uri }}&pivots=users.state&filter_config={{ filter_config | encode_uri }}"
    }
  }