3 + 1 Looker Pitfalls to Avoid
- Don´t make the same mistakes as I did!
During the last months, I have transitioned from creating dashboards and data models in Power BI to doing it in Looker instead. In this process, I have stumbled upon 3 Looker pitfalls. These are features with a bit different logic than one might expect. Let´s look at them and how not to be tricked by them.
0. What is Looker?
Before we go into the topic, let´s introduce the tool itself. Looker (not Looker Studio) is an enterprise BI platform owned by Google. It has features for data modeling and the creation and sharing of reports and dashboards. It uses LookML, Looker Modeling Language, to create data models.
1. Saving Dashboards
The most annoying pitfall I, and many other people I know, keep falling into time after time is how Looker handles updates to dashboards.
Editing visualizations in Looker dashboards is not done by simply pressing Edit and then Save. No, it has to be done in several steps in different places. Overseeing this means that all your cool dashboard changes will be lost. Bummer.
To do it properly, open the dashboard you want to edit and press the three dots in the top right corner. Out of the given options, choose Edit dashboard.
After this, hover over the visualization you want to update. When you do this, you will see another three dots appearing in the top right corner of the visualization — not in the dashboard itself. Press those and choose Edit.
Make the dashboard changes in the window that appears and then click Save in the top right corner.
Now you are taken back to the original dashboard window. Here you again have to go to the top right corner and click Save.
Skipping any of these steps means that you either won´t be able to edit the visualization at all or that none of your changes will be saved.
2. Working with Table Calculations
Looker enables non-developer users to create their own custom fields. There are three kinds of these: custom dimension, custom measure, and table calculations. Custom dimensions are built with Looker expressions but can, as the name suggests, only be defined per row level, ie not over several rows, the way sums and counts do.
Custom measures provide the possibility to create measures like sum, average, and alike over a specific dataset. They are created with the help of drag-and-drop alternatives in the user interface.
Table calculations are somewhat different from the two other custom fields. On the one hand, they provide more freedom to use Looker expressions and to create both dimensions and measures, including SQL window-type functions like SUM over (partition by…). On the other hand, they are applied only to the data you already fetched from the database, not to the whole data in the database. This comes with some consequences:
- You need to include all data points that you will base the table calculations on in the Data section. You also have to run the query before creating the Table Calculation. Otherwise, these data points will not be accessible to the locally run table calculation
- If you hit a row limit in your visualization (think tables with over 5,000 rows), the table calculation cannot be used for sorting. This is because it won´t know the real value distribution in the database. Practically, it would mean that, for example, a descending sort could show 100 as the first row, even though there is a value of 150 in the database, which was left out of the visualization due to the row limit.
3. Picking and Ordering Data
When creating a visualization out of a Looker Explore, you start by adding data points to the Data Section. After this, you can run the query and get a result, which you might build visualizations upon. If you choose to show the result as a table, you can easily change the order of the columns in the Visualization Section with the help of drag-and-drop.
However, if you are like me, you might want to decide the order of the columns already when selecting the data. This is only partially possible. Data points in the Data Section will appear in the order listed below and you can only change the order within each group.
- Dimensions
- Dimension table calculations
- Measures
- Measure table calculations
- Row totals
This means you might end up with one column order in the Data Section and another in the Visualization Section. Sounds a bit inconsistent to me.
Table Calculations might also bring you into a situation where you have to include a column in the Data Section to use it in the calculation, but you don´t want it to show in the visualization itself. In these situations, you can hide it by clicking the gear of the column in question in the Data Section and selecting Hide from visualization.
3+1. Min and Max for Dates
As an extra pitfall, I will briefly mention the challenge of calculating minimum or maximum for dates. Neither Looker custom measures nor table calculations provide a function for this. Instead, a LookML measure must be created in the backend. This measure needs to use MAX or MIN as part of the SQL query sent to the database.
Will not work
It is not possible to define type: max for a date field
measure: last_updated_date {
type: max
sql: ${TABLE}.updated_at ;;
}Will work
If we have already defined a time dimension for which we want to create a max measure, we can use this time dimension as a base for the measure. The new measure will have type: date and MAX() applied to the sql value. In this example, the value is the raw version of the original time dimension. The reason updated_raw and not updated_date has to be used is because the first returns a date, while the second returns a string.
dimension_group: updated {
type: time
timeframes: [time, date, week, month, raw]
sql: ${TABLE}.updated_at ;;
}measure: last_updated_date {
type: date
sql: MAX(${updated_raw}) ;;
convert_tz: no
}Wrap UP
Looker is a great tool for data modeling and reporting. However, as with all tools, it has its challenges and shortcomings. I hope this overview has helped you on your journey as a Looker user.
*Screenshots are from Looker´s test dataset fruit basket
