The Crate Driver

Metabase let’s you embed your Dashboards on external sites. I created a static website and added some world-time clocks on top from timeanddate.com and below the Metabase dashboard widget. The dashboard fits perfectly on any iPad at best with the FullScreen Browser app. Metabase, a free, self-hosted, open source simple data analytics system with complex capabilities. Dashboards for end users in minutes (Pentaho Open Source) - Duration: 3:58.

We have discovered that many people use Crate not only directly integrated in their applications, but also for analyzing their existing data. To help our users do this, we created a driver for Metabase, an open source database visualization tool focused on providing you with answers created from your data.

As Metabase is written in Clojure, we created a wrapper for our JDBC driver that ships with Metabase 0.18.1. Since the JDBC driver is included in the release, Metabase currently requires Crate 0.54.X as a backend.

UPDATE 26/07/2018: the Metabase wrapper is not currently maintained and does not work with CrateDB versions 2.3 and higher.

Install Metabase

Metabase is easy to install and offers options including a Mac application, a Docker image, cloud images and a jar file. Find them all here. Once you've started Metabase you will be asked for some user information and to select your database, select Crate from the list.

Metabase Concepts

To analyze your data with Metabase, you create questions, a sequence of parameters, filters and sorting options. Metabase then returns answers that you can format and visualize in a variety of ways.

You can then combine questions and answers into dashboards and layer them on top of each other for comparison.

Setup and Configuration

First add a database and table source.

Once configured, you can find settings for the data source in the Admin panel under the Databases tab. Here you can also force sync the database if new tables are missing in question source lists.

You can also view the assumptions Metabase has made about your data schema and manually override them in the Admin panel under the Data Model tab

With all that set up, let's get started creating some questions.

Stock Data

Metabase's analysis and visualization tools are aimed primarily at business intelligence professionals, and thus offer configuration options designed for particular use cases.

For this example, we are using our Stocks data set imported from Yahoo. We will look at the stock price of Exxon petroleum during the 70s and 80s and compare the volume of sales on the average stock price.

We created three questions, one for the average stock price per day in the 1970s, one for the 1980s and then a question to show the volume of sales across both decades. We split the questions into three parts, firstly to show how to layer answers onto one dashboard, and secondly to restrict the number of results each query returns and not overload Metabase.

Average Stock Price per decade

This example uses stock data taken from Yahoo.

We will create two questions that are almost duplicates of each other to see the change in stock price of Exxon mobil across two decades, the 70s and 80s, i.e. from 01/01/1970 - 12/31/1979 and 01/01/1980 - 12/31/1989.

First, select the stocks table from the Crate datasource.

Add a filter to only show the Exxon stock values by selecting the field you want to filter and how to filter it.

Filtering the date is a similar process, but in this case it should be a range between the start and end of the decade.

To plot data in Metabase on a chart we need to reduce the amount of columns and the only way to do this is by using the View values. For this use case we found the best method was to set the 'view' of the data to an 'average of High' and group by 'Date:Day' to get the results desired, a day and a closing stock price.

Finally make sure the data is sorted correctly by clicking on the ... icon and setting the sort order to 'Date: Day ascending'

Now you can change the visualization to a line graph.

Volume of Sales

Creating this question is a similar process but with some different filters and values.

Combine Answers

To compare and contrast data from answers, open the volume question on your dashboard and then add the other questions to it.

Github Data and SQL

For times when Metabase's visual filters and queries are not quite enough, you can jump into SQL edit mode by clicking the View the SQL icon and then Convert this question to SQL. In this example we'll try raw SQL queries with our Github data set.

Earlier this year we created a demo application for showing off graphs generated from queries in Crate. It features queries for Github's API stored in Crate to find out some interesting facts about Github users. With Metabase, this application has become obsolete because by running the queries in Metabase's SQL interface we could create the same results:

Languages in Pull Requests and Repositories

This query returns the number of pull requests grouped by languages:

With this query we retrieve the distinct number of repositories grouped by languages:

Coffee or Tea?

This query utilizes Crate's full text search capabilities to find out whether Github users prefer coffee or tea:

Pull Request Delay

To answer the question how long pull requests usually stay open until they are merged, we have the following query:

From this query, Metabase can construct this nice graphic, and show that most of the PRs are merged within the hour:

Get the Answers you're looking for

Metabase is a powerful visualization tool and with access to Crate it can provide great answers about your (big) data.

Stay up to date

Sign up here to keep informed about CrateDB product news,
events, how-to articles, and community update.

v0.39.0.1 / Users Guide / 08 Dashboard Filters

Dashboard Filters

Have you ever found yourself in a situation where it seems like you need to create nearly identical copies of the same dashboard, with just one different variable? Maybe you have an Earnings dashboard, but you want to see the data for each city your business is in, or maybe you have a KPI dashboard that you want to see broken out by month.

Instead of creating duplicate dashboards, you can use dashboard filters to create simple toggles to change a variable for cards on a dashboard.

Adding a new filter

To add a filter to a dashboard, first click the pencil icon to enter dashboard editing mode, then click the Add a Filter button that appears in the top-right.

You can choose from a number of filter types:

The type of filter you choose will determine what the filter widget will look like, as well as which fields you’ll be able to filter your cards by:

Time filters

When picking a Time filter, Metabase will prompt you to pick a specific type of filter widget:

• Month and Year
• Quarter and Year
• Single Date
• Date Range
• Relative Date
• All Options

Single Date and Date Range will provide a calendar widget, while the other options all provide slightly different dropdown interfaces for picking values. To get a widget that’s just like the time filter in the graphical query builder, choose All options.

Location filters

There are four types of Location filters to choose from:

• City
• State
• ZIP or Postal Code
• Country

ID filter

The ID filter provides a simple input box where you can type the ID of a user, order, etc.

Other Categories

The Other Categories filter is a flexible filter type that will let you create either a dropdown menu or an input box to filter on any category field in your cards.

Note: If you’re trying to filter Native/SQL questions, you’ll need to add a bit of additional markup to your query in order to use a dashboard filter with that question. For an in-depth article on this, check out Adding filters to dashboards with SQL questions.

Example filter

Let’s add a filter widget to our dashboard. We’ll select a Time filter, and then select the Month and Year option.

Metabase Example Dashboards Template

Metabase will display a filter editing interface where you can wire up your new filter to each applicable card.

Each card will feature a dropdown menu where you can select the column to filter. The sidebar on the right displays the settings for the current filter. If there’s a card on your dashboard that you don’t want to use with the filter, or that doesn’t make sense to use with the filter, that’s okay — the filter will only be applied to the cards you select.

So here’s what we’re doing — when we pick a month and year with our new filter, the filter needs to know which column in the card to filter on. For example, if we have a Total Orders card, and each order has a Date Ordered as well as a Date Delivered column, we have to pick which of those columns to filter — do we want to see all the orders placed in January, or do we want to see all the orders delivered in January? So, for each card on our dashboard, we’ll pick a date column to connect to the filter. If one of your cards says there aren’t any valid columns, that just means that card doesn’t contain any columns that match the kind of filter you chose.

Before we Save our changes, we can use the right sidebar to customize the Label of our new filter, or set a Default value.

When you’re finished wiring up the filter, click Done at the bottom of the sidebar, then click on Save in the top right to save the dashboard with your new filter.

Editing a filter

• To edit a filter: click the pencil icon to enter dashboard editing mode, then click the gears icon button on the filter you want to change. You can wire up individual cards to the filter, and use the sidebar to update the label.
• To reorder your filters: click on the grabber handle on the left side of a filter, then drag the filter to a different position.
• To set a default value: click on the filter’s gears icon to open the filter sidebar. Under the Settings tab, enter a value in the Default value input field.
• To remove a filter: click on the filter’s gears icon to open the filter sidebar. Under the Settings tab, click Remove in the sidebar. If you accidentally remove a filter, just click Cancel in the top-right to exit dashboard editing mode without saving your changes.

Using filters

Once you’ve added a filter to your dashboard, just click on the filter to select a value and activate the filter. To stop filtering, just click the blue X.

You can also set up a dashboard question to update a filter on click.

Choosing between a dropdown or autocomplete for your filter

If the column you’re using for a filter has more than 100 unique values, you’ll now automatically see a search box with autocomplete suggestions:

Columns with fewer than 100 distinct values will list all options:

In both cases, you can pick one or multiple selections for your filter.

What Is Metabase

If Metabase somehow picked the wrong behavior for your field, admins can go to the Data Model section of the admin panel and click on the gear icon by the field in question to specify a list, search box, or plain input box.

Linking filters

You can also link filters so that a child filter knows to limit its choices based on the activation of a parent filter.

Say you have two filters, one to filter by state, the other to filter by city. You can link the city filter to the state filter so that when someone filters by California, the city filter will “know” to only show cities in California. In this case, state is the parent filter, and city is the child filter.

To link filters, you’ll need to set up this parent-child relationship. And you set up this relationship through the child filter. In the above scenario, with a state and city filter, we’d edit the child filter, city, by clicking on the gears icon on the city filter. From the filter sidebar on the right, select the Linked filters tab.

Here you can limit the current filter’s choices. If you toggle on one of these dashboard filters, selecting a value for that filter will limit the available choices for this filter. In this case, we toggle on the state filter (the parent), to limit the choices for the city filter. When states are selected, the city filter will limit its choices to cities in those states. Click Done, then Save to save the dashboard.

To learn more, check out Linking filters in dashboards.

Best practices

Here are a few tips to get the most out of dashboard filters:

• Limit filters to 3 or fewer. Limiting filters will make it easier for your teammates to quickly understand what options are available to them when viewing your dashboard.
• Start with a new dashboard. While you can add dashboard filters to an existing dashboard with a bunch of cards in it, it can be easier to start a new dashboard and think about what filters you intend to add to it. That way you can make sure that you only put cards in that dashboard that can be used with the filters. Alternatively, you could duplicate an existing dashboard, and pare down the number of cards.
• Link filters so people don’t have to sift through irrelevant filter options (like cities not in a filtered state).

Next: Interactive dashboards

Metabase Mobile

We’ll learn how to make our dashboards interactive.