You can use Metabase to create explorable charts, both with questions asked using the query builder and SQL questions that use models as a starting point.
These explorable charts let people click on values to pull up an Action Menu, which gives them different options to drill through the chart’s data.
The Action Menu’s drill-through functionality is available for questions built using the query builder and for SQL questions based on a model — as long as column metadata has been added to that model. You won’t have the option to drill-through on SQL questions that aren’t based on models, or if your models haven’t been annotated with the appropriate metadata.
A quick review of asking questions in Metabase
Under the hood, all questions built using Metabase’s query builder use the same primitives (grouping functions, filtering functions, and so on), whether you start by browsing your data or by building your question from scratch in the Notebook Editor. You can also start with the data exploration (affectionately known as Chill Mode in Metabase), and then customize your question further by moving into Notebook Editor. For even more advanced questions, you have the awesome power of custom expressions at your disposal.
If you prefer SQL (or you want to take advantage of Metabase’s SQL snippets feature), you can take questions composed in the Notebook Editor and convert them to SQL at any time (figure 2).
SQL questions give a lot more freedom of expression — you have all of SQL at your disposal — but unless your SQL query is based on a model with annotated column types, you’ll lose the tight coupling with the metadata that makes drill-through possible. This is because the Action Menu functionality leverages metadata about your tables and columns, and it’s why the Action Menu is only available for questions constructed in the query builder and native queries based on models with annotated metadata.
An example question composed with the Notebook Editor
Let’s tour the drill-through functionality available in the Action Menu. If you’d like to follow along, see figure 3 for a screenshot of the question in the Notebook Editor. The visualization has been set to a stacked bar chart (figure 4).
The Action Menu
The Action Menu presents a few different drill-through options that you can choose from when exploring your data.
We’ll step through each of the options in the popup menu you see above:
Select to zoom
You can click and drag to select an area of a chart to zoom in on.
You can left-click to bring up the Action Menu and select Zoom in and Metabase will create a close-up of the data surrounding the value you selected.
The Zoom in option in the Action Menu will choose an appropriate range of values based on the full range of values in the chart. As shown in figure 7, Zoom in also focuses on the clicked category, so only orders with products in the Widget category are shown. If you’d prefer a different range, or wish to include additional groups, you have the Notebook Editor available to fine tune your chart (and save it as a new question).
View these records
You can click on a value on a chart and select
View these orders to bring up a table with the individual records that compose the value.
Breaking out by a category lets us do things like see the banana cream pie orders in June 2017 broken out by the status of the customer (e.g., new or VIP, etc.) or other different aspects of the order. Different charts will have different breakout options, such as Location and Time.
Figure 9 shows an example breakout looking at orders from
Product.vendor from the Sample Database.
X-rays are automatically-generated explorations of your data. You can click anywhere on a chart to perform an X-ray, and Metabase will generate a dashboard full of different questions about the data. You’ll have an option to save that X-ray as a dashboard, which you can then edit to your liking, by removing irrelevant questions, or adding new questions or text boxes to fill in the story you want to tell.
Clicking on a point or a bar additionally gives you the option to compare data, which will give you another dashboard with auto-generated charts.
Using the Action Menu to find new questions
Drill-through with the action menu is a great way to give viewers of your questions more insight into their data, which makes it an excellent feature for delivering self-service, embedded analytics.
But the action menu is also an excellent tool for exploring your data, especially when you’re starting out with a new dataset and you’re not sure what questions you should be asking. Start with a broad query, then experiment with the options on the action menu. When you discover an insightful cut of the data, save it as a new question, and fine tune the visualization as needed, or convert it a SQL question if you’re more comfortable with code.
Custom destinations and cross-filtering
When working with dashboards, the Action Menu isn’t your only option. On dashboards, you can replace the action menu with a custom destination. Custom destinations are particularly useful if you wrote your question in SQL and don’t benefit from the action menu.
You can also set up crossing-filtering on a dashboard, so that people can click on a chart to update a filter.
Did this article help you?
Thanks for your feedback!