Empowering the User — Making your Looker Dashboards more dynamic
We’ve all been in this situation before. New data has become available and the request to show it sliced and diced every which way have now expanded a once manageable dashboard into something much less so. Now users are dealing with increased load times and visual clutter to get the same data they are used to.
Common visualization tools like Looker have a great benefit of being able to display a variety of KPIs and tracking metrics on a single page, but at a certain point they become limited by 3 main factors.
- The size of the page itself, and/or the screen it is being viewed on
- The amount of data being processed eventually impacting performance
- The human brain’s ability to focus on and discern important information out of a potential smorgasbord of graphs and numbers all put forward at the same time
And while as data analysts we can’t provide every user with a Bloomberg terminal or massively improve cognitive function, we can provide dashboards that are more fluid and adaptable to a user’s needs. And no, this does not mean just making more dashboards.
As you may already be familiar with, Looker uses Liquid language templating to allow users to insert a variety of variables into their queries. This can be used to create flexible filtered measures or adjustable date aggregation levels. You can also use this logic at a dashboard level to grant users a higher level of flexibility in the metrics and granularity of visualizations they are interested in a given dashboard.
Take the following example: You have a dashboard tracking a variety of E-Commerce metrics: revenue, units sold, clicks, etc. As inclusion of metrics pile up, you’re starting to hit the limit of what’s manageable given the available space and maintaining a level of readability. Not only that, users are starting to experience runtime issues as the size of the dashboard is impacting refresh speeds. By taking advantage of dynamic measures and filters, we can greatly condense these views without diluting the available data.
Utilizing Parameters
Parameters here are your best friend. Parameters allow us to have predefined values to be later referenced in our LookML code. These values can then be referenced in multiple ways, from defining case when statements to adjusting the labels of dimensions or measures. What we will be adjusting is the SQL syntax of specific dimensions and parameters to change which fields Looker calls from a given table.
Dynamic Dimensions
Now by referencing the parameters we’ve created, we can create new dimensions that we can control on the fly. While this isn’t the most helpful in an Explore, this allows users to directly adjust metrics on a dashboard or look without editing them. With Dimensions, users can adjust levels of aggregation in Tables of Bar Charts, or adjust the pivot categories of a Line or Stacked Bar. Even with this one change, you’ve potentially consolidated multiple visualizations focusing on location, brand, demographics, etc into a single visualization with the ability to cover all of these and more.
For Dimensions, there are two ways you can you can insert a user selected parameter:
If you’re referencing the same table and planning on only using string values, you can adjust sql for dynamic dimension like so
parameter: dynamic_dimension_parameter_one {
type: unquoted
allowed_value: {
label: “Brand”
value: “product_brand”
}dimension: dynamic_dimension_one {
type: string
sql: ${table}.{% parameter dynamic_dimension_parameter_one %} ;;
}
This makes additions to the parameter a one stop-shop, where you only need to include new values in the parameter and not account for them in the dimension itself. Looker will take the value of the parameter, attach it to your table reference and call the specified field when necessary. The downside to this is that you have to correctly assign the values of the column names, and can only reference fields that are in the same view. It also must be a type: unquoted parameter, to allow you to insert the SQL
The second option is to include an if/else statement within the SQL clause to adjust which dimensions are referenced. This is more code to write, but will also account for any multiple views, value types, or fields that require additional adjustment at the LookML layer.
parameter: dynamic_dimension_parameter_one {
type: string
allow_value: {value: “Brand” }
allowed_value: {value: “Category”}
allowed_value: {value: “State”}
}dimension: dynamic_dimension_one {
label: “1. Dynamic Dimension”
type: string
sql: {% if dynamic_dimension_parameter_one._parameter_value == “‘Brand’” %}
${brand}
{% elsif dynamic_dimension_parameter_one._parameter_value == “‘Category’” %}
${products.category}
{% elsif dynamic_dimension_parameter_one._parameter_value == “‘State’” %}
${users.state}
{% else %}
${brand}
{% endif %}
Here we’re using a type: string parameter, one key point to remember here is that your values will need to be surrounded by “” and ‘’ to be properly recognized. We’re also not using a label syntax as the string we’re passing through will be clear enough for users to understand.
Dynamic Measures
As we’ve adjusted how dimensions are presented within the dashboard, the same can be done for our measures. By creating a dynamic measure, users can switch between several calculations on the fly just via a filter. Just like a dynamic dimension, this works by inserting the user selection into the SQL clause of the measure. And we can do this the same way as before, by inserting an if/else statement to reference existing measures. Directly referencing the column via ${table} is less recommended as you are restricted to aggregations of the same type (sums, avgs, etc).
parameter: dynamic_measure_parameter_one {
type: string
allowed_value: { value: “Sales” }
allowed_value: { value: “Units” }
allowed_value: { value: “ASP” }
allowed_value: { value: “Empty” }
}measure: dynamic_measure_one {
label: “”
type: number
sql: {% if dynamic_measure_parameter_one._parameter_value == “‘Sales’” %}
${sum_sales}
{% elsif dynamic_measure_parameter_one._parameter_value == “‘Units’” %}
${sum_units}
{% elsif dynamic_measure_parameter_one._parameter_value == “‘ASP’” %}
${avg_sale_price}
{% else %}
null
{% endif %}
I’ve only detailed a handful of measures here, but the only true limit is how many measures you want to present to your users. We’ve also included an “Empty” value for users to select, which clears out selection when in a visualization. This is great when you have the option to select multiple metrics to compare, but would like to isolate one.
Dynamic Time Dimensions
Along with more standard string, Dimensions, we can also use parameters to adjust how users visualize time in Looker dashboards. While users can already adjust their time ranges via filters, with an additional parameter and dimension users can also adjust their date aggregation from Day, Week, Month, etc.
parameter: dynamic_date_parameter {
type: string
allowed_value: { value: “Day” }
allowed_value: { value: “Week” }
allowed_value: { value: “Month” }dimension: dynamic_date {
type: date
sql: {% if dynamic_date_parameter._parameter_value == “‘Day’” %}
${date_date}
{% elsif dynamic_date_parameter._parameter_value == “‘Week’” %}
${date_week}
{% elsif dynamic_date_parameter._parameter_value == “‘Month’” %}
${date_month}
{% else %}
${date_date}
{% endif %}
You can even create and include non-standard date time fields, like End of Week or Mid-Month, that are not inherently available within Looker dimension groups.
Putting it All Together
Combining all these capabilities into a Looker dashboard greatly increases its own functionality while staying relatively easy for users to understand. And it directly clears out clutter for users to focus on exactly what they want to know and at what detail.
This is only scratching the surface of what you are able to adjust in your data model with parameters. You can go even further by changing direct sql tables referenced in views to adjusting how derived tables are constructed on the fly. And this can all be controlled at the dashboard level without users needing to understand exactly what is going on behind the scenes. What news ideas can you think of to improve your user’s experience?
