More Powerful Data Drilling

  • 21 February 2018
  • 15 replies
  • 31108 views


The content of this post has been migrated to a Looker Help Center Article. The comments will remain here, as there’s useful information contained within them.



15 replies

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..." }

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))

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 }}"
}
}

These screenshots are a bit misleading.


When you use “drill_fields: [ ]” with “link: { label:“Report” url:”"}

and the user clicks on the measure, they will see a 'show all #" AND “Report”.

“Show all” and “Report” will both have the same fields selected, but “Report” will have that customization you just added. This user experience is confusing and redundant.

Furthermore, if I did get ride of the “Show all” by removing the drill down and adding all the fields in the link - then when the user clicks on the measure - they will see a new tab open up. Which isn’t consistant behaviour with everything else on our dashboard. Sorry I’m a bit disappointed that this feature isn’t useable due to confusing user behaviour.

Hey @Ally ,

Thanks for the feedback. Did you happen to try the comment that @Hugo_Selbie made? (see here) In particular #4 from his post.


I agree that the redundancy can be confusing for end users, but Hugo’s comment can be used to make one drill path for your users. As for your comment regarding a new tab, the link property shouldn’t open in a new tab if your link url begins with {{measure._link}}, {{link}}, or any relative explore link like /explore/thelook/order_items?fields=products.category,order_items.sale_price. If its opening in a new url, that indicates to me there is something wrong with the URL in your link; Looker’s default behavior is to open the visualizations and drill menus in a pop-up modal, not a new tab.


I hope this helps.

~Bryan

I think I’m having the same issue as Ally. This is my desired user experience:



  1. click on a measure

  2. drill overlay immediately pops up showing my customized drill view


I used Tom’s method from his first comment and it results in this user experience:



  1. click on a measure

  2. Explore menu offers choice of “Select All 9,000” or “[Custom drill label]”

  3. Select All takes them to normal drill


They will only see the customized view if they click on the correct label. Most will just click Select All and see the un-formatted drill view. I tried Hugo’s method like you suggested, but it had no effect because I’m drilling on a measure already, not a dimension, so the Explore menu still appears. Also tried removing the label from my link, but that just makes the drill go straight to the un-formatted drill view.


Is it possible to remove “Select All” as an option?

Hey @haleyb


Hugo’s method, particularly Step 4, mentions making a dummy measure for your drill. Here’s a quick example of how that dummy link can be used to get you to your ideal state.


  measure: dummy {
hidden: yes
type: sum
sql: 0 ;;
drill_fields: [order_date,cases,count]
}

measure: cases {
type: sum
sql: ${case} ;;
link: {
label: "Breakdown by Day"
url: "{% assign vis_config="....."%} # see examples above for vis property
{{dummy._link}}&fill_fields=order_items.order_date&vis={{vis_config}}"
icon_url: "/favicon.ico"
}
}


Notice two things, my normal measure does NOT have a drill fields property, instead it only has one link. The one link references {{dummy._link}} from the hidden measure with the proper drill fields. When clicking, your users will not be presented with the box with multiple drills.


I hope this helps.

~Bryan

Thanks for clarifying, Bryan! I wasn’t removing the drill_fields from the measure. One other issue that tripped me up: the measure I’m drilling into is a count_distinct and the dummy was using a sum, which was giving me SQL errors. When I changed the dummy type to count, it started working. So using the code from Hugo’s comment #4 and Bryan’s clarification, I now have a custom drill and when the user clicks on the measure the only menu option that appears is the custom label.


Thanks!

Hi everyone, I created this one pager tool for converting explore expanded url to the format that suitable for more powerful drilling:


xin-looker.github.io


In the result section there are two columns, the left one is the url you can copy and paste into the lookml. The right one is the just a full decoded url, you can use that to validate the result.


For step 1, ideally you can paste a url of the exact url with the visualization you need for the drilling, if not, you can still use the step 2 to add the fields, however it might not work correctly at the moment.


Feedback is welcomed!

Just a note that the {% assign vis= '{\"type\": portion of the tool, the lefthand column of results, does not seem to correctly add order/sorts/column limits/limits and a few other items on a vis. I’ve been able to successfully copy those same portions from the right hand portion (just the https:// link, where appropriate, usually after ampersands) and they have successfully limited/sorted as needed - but it’d be great if the tool worked on both sides properly.


Awesome tool! Has been super helpful! Thanks Xin!


EDIT: Just to say that Xin already updated the tool, and now takes care of those cases. This tool has been super helpful, thanks again Xin!!

Thanks for pointing it out @Andrea_Ellison!

Is there a plan to make visual drilling available in dashboards next? We love the features of beta dashboards, but visual drilling would be a huge win for our dashboard experiences.

Is there a plan to make visual drilling available in dashboards next? We love the features of beta dashboards, but visual drilling would be a huge win for our dashboard experiences.

I’d like to know if there anything being planned on this.
@xin tool is fantastic but not fully working with new dashboards

Badge

Is there a plan to make visual drilling available in dashboards next? We love the features of beta dashboards, but visual drilling would be a huge win for our dashboard experiences.

Same as @graydon and @Andre_Forte_Sepulved  -- We would really like to leverage all the good stuff that the New Dashboard Experience provides, but not having Visual Drilling is a deal breaker for us. We cannot make the jump to the new dashboards yet because of the lack of this feature, which is kind of a bummer.

Userlevel 7
Badge

Noted, and communicated to our product team. We think this is a bug (re: visual drilling not working) and so will try to fix it up, though we don’t have a clear timeline yet.

Reply