Self-service analytics with Metabase and Cube

With Metabase as the business intelligence platform, your team doesn’t need to speak SQL. It’s the easy, open-source way to help everyone in your company work with data like an analyst. Pairing it with Cube, the headless BI platform, will provide a consistent data model making it easy to build self-service analytics, shared across multiple teams.

Here’s what your Metabase dashboard will look like after we integrate it with Cube. If stuck, you can always refer to the source code on GitHub.

We've hosted a webinar on replacing Looker with Metabase and Cube. Watch on-demand.

What is Metabase?

Metabase is an open-source business intelligence platform with a low entry barrier, smooth visual interface, and generally pleasant user experience. It provides an easy way for everyone in your company to work with data like an analyst.

You can use Metabase to ask questions about your data, build self-service analytics, or even embed Metabase in your front-end data application to let your customers explore data on their own. You can save questions, group them into dashboards, and share both questions and dashboards with the your team.

Metabase enables three main scenarios:

• Query and visualize your data. Use the query builder to filter and summarize data. Choose between either the Notebook editor to ask sophisticated questions with joins, filters, and aggregations, or Native queries to compose questions in the database’s native query languages. Awesome if you're proficient with SQL!

• Embed charts and share your results. You can add questions to a dashboard and include filters and connect them to fields on questions to narrow the results. You can share the dashboard by sending the URL to your co-workers, or by embedding them using iframes. You can even embed the full Metabase app, which allows you to do things like delivering multi-tenant, self-service analytics.

• Find things and stay organized. Collections organize questions, models, dashboards, and other collections. They work like folders on a file system. Or, use the search bar to find data, metrics, segments, dashboards, models, and questions. But, X-ray is by far the most amazing feature in Metabase. X-raying a table will antomagically generate a bunch of questions that slice the table’s records in different ways and save it as a dashboard.

What is Cube?

Cube is the headless BI platform for accessing data from modern data stores, organizing it into consistent metrics definitions, and delivering them to downstream applications.

Why would you want to have Cube in your data pipeline? Data consistency.

Cube will deliver consistent data to all teams using Metabase. Serving as a source of truth for the metrics definitions, access control rules, and caching settings, it solves the many-to-many problem many data teams have. Regardless of how many data sources (like Snowflake or Redshift) or data consumers (like Metabase) you have, Cube allows to get rid of siloed data pipelines.

Conceptually, Cube has four layers:

• Data modeling. With dozens of supported data sources and a centralized data model for defining metrics definitions, you can make sure every team has access to exactly the same data, in Metabase and beyond.
• Access control. Cube’s security configuration lets you manage who is able to access data and which data it is. As a part of that, Cube provides row-level security and multi-tenancy for Metabase users.
• Performance. Cube provides a centralized caching layer which ensures consistent performance and data freshness across use cases or teams. As a result, your Metabase dashboards will load in under a second.
• APIs. Cube exposes three APIs: REST API, GraphQL API, and SQL API. Cube's SQL API is fully Postgres-compliant, enabling connectivity to Metabase and, actually, any downstream tool that works with Postgres.

So, with Cube, you can use native, syntactically correct SQL queries, similar to how you’d query a data source directly:

SELECT
  DATE_TRUNC('day', createdAt) AS created_at_per_day,
  SUM(count) AS orders
FROM Orders
GROUP BY 1
ORDER BY 1 ASC

Cube is open source, and you can self-host it in a cloud of your choice. Also, you can run Cube in Cube Cloud which provides fully managed experience with a free tier.

Integrate Metabase and Cube

Take a look a short video showcasing how Cube and Metabase work together:

You can also follow more elaborate instruction below:

Running Cube. You can run Cube in two ways: either self-host it on your infrastructure or use the fully managed version in Cube Cloud.

For the sake of simplicity, we'll run Cube Cloud. There’s a generous free tier for you to try. Go ahead, sign up and follow the instructions on connecting to your data source and generating an initial data model.

Use these credentials to connect a sample e-commerce database to your Cube instance.

Hostname:  demo-db-examples.cube.dev
Port:      5432
Database:  ecom
Username:  cube
Password:  12345

This is what your deployment in Cube Cloud will look like:

Working with the data model. To see the data model you created above, navigate to the Schema tab. You will see files named LineItems.js, Orders.js, Users.js, etc. under the schema folder which defines the cubes in your data model.

Key ideas on what we’re seeing above:

• A cube is mapped to a table in your database to categorize measures (quantitative data) and dimensions (qualitative data) into metrics definitions.
• This cube is mapped to the public.orders table.
• Measures are defined as aggregations (e.g. count, sum, etc.) over columns.
• Dimensions are mapped to columns.
• You can define complex measures and dimensions with custom SQL statements and references to other measures and dimensions.

Customizing the data model. You can add custom measures and dimensions, and edit them with filters or segments. In this example let’s update the schema by adding the completedCount measure to the Orders cube.

completedCount: {
  type: `count`,
  filters: [{
    sql: `${CUBE}.status = 'completed'`
  }],
}

To do this, click the Enter Development Mode button in Cube Cloud to unlock the schema files for edits. This creates a development environment on a separate branch from your main branch. Here you can make changes to the data model before committing them to the main branch.

Here’s the Orders.js schema file, but with the added completedOrders measure. Paste this snippet into your Orders.js file.

cube(`Orders`, {
  sql: `SELECT * FROM public.orders`,

  preAggregations: {
    // Pre-Aggregations definitions go here
    // Learn more here: https://cube.dev/docs/caching/pre-aggregations/getting-started  
  },

  joins: {
    Users: {
      sql: `${CUBE}.user_id = ${Users}.id`,
      relationship: `belongsTo`
    },

    Products: {
      sql: `${CUBE}.product_id = ${Products}.id`,
      relationship: `belongsTo`
    }
  },

  measures: {
    count: {
      type: `count`,
      drillMembers: [id, createdAt]
    },

    number: {
      sql: `number`,
      type: `sum`
    },

    completedCount: {
      type: `count`,
      filters: [{
        sql: `${CUBE}.status = 'completed'`
      }],
    }
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true
    },

    status: {
      sql: `status`,
      type: `string`
    },

    createdAt: {
      sql: `created_at`,
      type: `time`
    },

    completedAt: {
      sql: `completed_at`,
      type: `time`
    }
  }
});

Once you commit and push this change, jump over to the Playground and run a query to get a response for the custom measure.

Adding pre-aggregations. Pre-aggregations are intermediate representation of the data, defined declaratively, computed in advance, persisted as tables, and used to accelerate queries. Cube has the ability to analyze queries against a defined set of pre-aggregations in order to serve the query from cache and not hit the data source directly.

Pre-aggregations physically reduce the size of the data. Fewer bytes to read, less time to return a response. This will dramatically improve query performance and provide a higher concurrency.

Let’s add a pre-aggregation definition to speed up the query above even more. Enter development mode once again, and edit the Orders.js file by pasting this piece of code in the pre-aggregations section.

preAggregations: {
  completedOrdersByDay: {
    measures: [
      Orders.completedCount
    ],
    timeDimension: Orders.createdAt,
    granularity: `day`
  }
},

Here’s what the schema will look like.

Once you commit and push this change, jump over to the Playground and run the same query as you did above. This time you’ll see it return a response even faster:

Integrating Metabase and building a dashboard. You can run Metabase either in your infrastructure with the open-source version or use Metabase Cloud. For the sake of simplicity, let's sign up for a Metabase free trial. Or, if you rather prefer using Docker, you can run a Metabase instance yourself with Docker.

Once you have signed in to your Metabase instance, go ahead and add a PostgreSQL data source. Cube’s SQL API speaks the PostgreSQL dialect of SQL which makes it compatible with all kinds of data tools, Metabase included. Copy the credentials from the SQL API section on the Overview page of Cube Cloud:

Or, you can use these sample credentials:

Host:      tan-ptarmigan.sql.gcp-us-central1.cubecloudapp.dev
Port:      5432
Database:  tan-ptarmigan
User:      cube
Password:  544056feb07f485e96913fc66b0404b4

Then, enter the credentials as a new PostgreSQL data source in Metabase.

Make sure to enable the "Use a secure connection (SSL)" toggle.

Now you can run queries to Cube from Metabase. By either using the Notebook editor to ask questions or Native queries, we can easily build a dashboard.

Self-service analytics with Metabase

By far the most awesome feature in Metabase is the ability to X-ray a table and autogenerate a dashboard. Click the ⚡ icon to trigger the X-ray.

This will take you to an autogenerated dashboard. Here’s a live preview of what this dashboard looks like.

Running an X-ray is an awesome way of hitting the ground running. But, let’s move on to more complex topics, building a custom dashboard.

Let's use the Notebook editor to ask a question. Select the Orders table. Summarize the Sum of CompletedCount by CreatedAt: Day.

This will visualize the same chart as we did before in the Cube Cloud Playground.

Using the Notebook editor is an awesome way of executing SQL without writing SQL. It’s great for team members that don’t speak SQL.

However, what about the ones that do? Let’s write a native SQL query next. Views in Cube can be queried the same way as cubes. This snippet below will return the same data as the Notebook query from above.

SELECT
  DATE_TRUNC('day', createdAt) AS created_at_per_day,
  SUM(completedCount) AS completed_orders
FROM Orders
GROUP BY 1
ORDER BY 1 ASC
LIMIT 10000

Once you’re happy with the questions, save them, and add them to a dashboard.

Sharing and embedding Metabase dashboards

Metabase gives you multiple options to share dashboards. You can embed them by using iframes. You can share the dashboard by sending the URL to your co-workers. You can even embed the full Metabase app, which allows you to do things like delivering multi-tenant, self-service analytics.

In the Admin settings, click Public Sharing and toggle Enable Public Sharing to Enabled. This will allow you to create public URLs to share dashboards.

To embed dashboards jump below to the Embedding tab. Toggle it to Enabled, and you’re good to go.

To select a specific dashboard you want to share, go to the dashboard and click the Sharing button in the top right.

This will open up a prompt to select how to share the dashboard.

Check out the Metabase docs to learn more about embedding and sharing dashboards.

Wrapping up

Using Metabase with Cube as a Headless BI layer will ensure data uniformity and consistent metrics definitions for your self-service analytics.

Would you like to explore more about Cube? Check the docs and create a free Cube Cloud account today.

Also, please feel free to join our community of almost 7000 data practitioners on Slack, drop us a line, or give Cube a star on GitHub. See you there!