With [Metabase](https://www.metabase.com/) 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](https://cube.dev/), 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. Check out the [live demo](http://metabase.examplescube.dev/public/dashboard/947a9fa9-0c79-47e8-9cca-55cee7eafbcf) as well! If stuck, you can always refer to the [source code on GitHub](https://github.com/cube-js/cube.js/tree/master/examples/metabase).

![demo](https://ucarecdn.com/4d1d1527-a396-4b3a-8289-33c8812aaee6/)

> In early December, we're hosting a webinar on replacing Looker with Metabase and Cube. [RSVP and save the seat.](https://cube.dev/events/replacing-looker-with-cube-and-metabase)

## What is Metabase?

[Metabase](https://www.metabase.com/) is an [open-source](https://github.com/metabase/metabase) 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](https://www.metabase.com/docs/latest/exploration-and-organization/x-rays) 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](https://cube.dev/) is the [headless BI](http://cube.dev/blog/headless-bi) platform for accessing data from [modern data stores](https://cube.dev/docs/config/databases), organizing it into consistent metrics definitions, and delivering them to downstream applications.

![headless BI](https://ucarecdn.com/dfb299b9-7e27-47f0-a362-0a14c643c740/)

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.

![https://ucarecdn.com/728192a0-d30f-41c9-a8c9-f865361691e5/](https://ucarecdn.com/728192a0-d30f-41c9-a8c9-f865361691e5/)

Conceptually, Cube has four layers:

* **Data modeling.** With dozens of [supported data sources](https://cube.dev/docs/config/databases) and a [centralized data model](https://cube.dev/docs/schema/getting-started) 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](https://cube.dev/docs/security) lets you manage who is able to access data and which data it is. As a part of that, Cube provides [row-level security](https://cube.dev/docs/security/context#security-context) and [multi-tenancy](https://cube.dev/docs/multitenancy-setup) for Metabase users.
* **Performance.** Cube provides a [centralized caching layer](https://cube.dev/docs/caching) which ensures [consistent performance](https://cube.dev/docs/caching/pre-aggregations/getting-started) 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](https://cube.dev/docs/backend/sql) 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:

```sql
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](https://github.com/cube-js/cube.js), and you can self-host it in a cloud of your choice. Also, you can run Cube in [Cube Cloud](https://cube.dev/cloud) which provides fully managed experience with a [free tier](https://cube.dev/pricing).

## Integrate Metabase and Cube

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

<div style={{ position: 'relative', paddingBottom: '54.84375%', height: 0 }}><iframe src="https://www.loom.com/embed/38752bb23e5244d1a43675795c470a53" frameborder="0" webkitallowfullscreen mozallowfullscreen allowfullscreen style={{ position: 'absolute', top: 0, left: 0, width: '100%', height: '100%' }}></iframe></div>

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](https://cube.dev/cloud). There’s a generous free tier for you to try. Go ahead, [sign up](https://cubecloud.dev/auth/signup) and follow the [instructions](https://cube.dev/docs/cloud/getting-started/create) 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.

```bash
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:

![https://ucarecdn.com/4d1cf863-426c-4d40-829c-e3237a8e07d7/](https://ucarecdn.com/4d1cf863-426c-4d40-829c-e3237a8e07d7/)

Working with the data model. To see the data model you created above, navigate to the <Btn>Schema</Btn> 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.

![https://ucarecdn.com/b9d18c13-0d79-4487-9ed5-0a7c7c130db3/](https://ucarecdn.com/b9d18c13-0d79-4487-9ed5-0a7c7c130db3/)

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](https://cube.dev/docs/schema/reference/cube).
- This cube is mapped to the `public.orders` table.
- [Measures](https://cube.dev/docs/schema/reference/measures) are defined as aggregations (e.g. count, sum, etc.) over columns.
- [Dimensions](https://cube.dev/docs/schema/reference/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.

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

To do this, click the <Btn>Enter Development Mode</Btn> 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.

```javascript
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.

![https://ucarecdn.com/30dbcd19-a6c3-4d75-a532-5d4eeaea83f2/](https://ucarecdn.com/30dbcd19-a6c3-4d75-a532-5d4eeaea83f2/)

**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.

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

Here’s what the schema will look like.

![https://ucarecdn.com/3625d23f-91fc-4579-9f76-5d7638db32eb/](https://ucarecdn.com/3625d23f-91fc-4579-9f76-5d7638db32eb/)

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:

![https://ucarecdn.com/b7e1d0ed-b9a9-4396-87fe-e335872382af/](https://ucarecdn.com/b7e1d0ed-b9a9-4396-87fe-e335872382af/)

**Integrating Metabase and building a dashboard.** You can run Metabase either in your infrastructure with the [open-source](https://github.com/metabase/metabase) version or use [Metabase Cloud](https://www.metabase.com/start/). For the sake of simplicity, let's [sign up](https://store.metabase.com/checkout/starter/login-details) for a Metabase free trial. Or, if you rather prefer using Docker, you can [run a Metabase instance yourself with Docker](https://www.metabase.com/docs/latest/installation-and-operation/running-metabase-on-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:

![https://ucarecdn.com/56fad92a-8a97-4e41-a602-4a36947be4e7/](https://ucarecdn.com/56fad92a-8a97-4e41-a602-4a36947be4e7/)

Or, you can use these sample credentials:

```bash
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.

![https://ucarecdn.com/3e4c319f-4708-493e-bd87-4a2a21d2f2ed/](https://ucarecdn.com/3e4c319f-4708-493e-bd87-4a2a21d2f2ed/)

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 <Btn>⚡</Btn> icon to trigger the X-ray.

![https://ucarecdn.com/4db82cca-fae9-41ee-977e-261c5462a2fc/](https://ucarecdn.com/4db82cca-fae9-41ee-977e-261c5462a2fc/)

This will take you to an autogenerated dashboard. Here’s a [live preview](http://metabase.examplescube.dev/public/dashboard/947a9fa9-0c79-47e8-9cca-55cee7eafbcf) of what this dashboard looks like. 

![https://ucarecdn.com/e4b89682-d46a-460d-bcec-7e1a382a7446/](https://ucarecdn.com/e4b89682-d46a-460d-bcec-7e1a382a7446/)

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`.

![https://ucarecdn.com/0dd1a3d0-7e17-4e1f-85e4-374250e4a392/](https://ucarecdn.com/0dd1a3d0-7e17-4e1f-85e4-374250e4a392/)

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

![https://ucarecdn.com/0f24c9ad-dbad-4508-8b79-054e1e5083d4/](https://ucarecdn.com/0f24c9ad-dbad-4508-8b79-054e1e5083d4/)

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.

```sql
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
```

![https://ucarecdn.com/8d2a7839-e14a-40cf-879d-cb1aa56dce57/](https://ucarecdn.com/8d2a7839-e14a-40cf-879d-cb1aa56dce57/)

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

![https://ucarecdn.com/07c02752-3dc1-4a49-8fe3-204d1fa4a99c/](https://ucarecdn.com/07c02752-3dc1-4a49-8fe3-204d1fa4a99c/)

## 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 <Btn>Public Sharing</Btn> and toggle `Enable Public Sharing` to `Enabled`. This will allow you to create public URLs to share dashboards.

![https://ucarecdn.com/85d24d95-3eb8-4a67-b84c-eea5006a6319/](https://ucarecdn.com/85d24d95-3eb8-4a67-b84c-eea5006a6319/)

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

![https://ucarecdn.com/3959935d-b3c0-4000-b40a-2076999f7885/](https://ucarecdn.com/3959935d-b3c0-4000-b40a-2076999f7885/)

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

![https://ucarecdn.com/5fd254e2-566e-4bbe-be0e-0739541da643/](https://ucarecdn.com/5fd254e2-566e-4bbe-be0e-0739541da643/)

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

![https://ucarecdn.com/e84de9a9-680d-47bc-b147-ac6e7e960d72/](https://ucarecdn.com/e84de9a9-680d-47bc-b147-ac6e7e960d72/)

We’ve prepared a [live demo](http://metabase.examplescube.dev/public/dashboard/947a9fa9-0c79-47e8-9cca-55cee7eafbcf) for you to check out. Once you enable public sharing, you’ll have the option to send a link to the dashboard to whoever you want!

If you would rather want to embed the dashboard, using an iframe will look like this.

<iframe
src="https://metabase.examplescube.dev/public/dashboard/947a9fa9-0c79-47e8-9cca-55cee7eafbcf"
frameborder="0"
width="800"
height="600"
allowtransparency
> </iframe>

Check out the [Metabase docs](https://www.metabase.com/docs/latest/embedding/start) 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](https://cube.dev/docs/) and create a free [Cube Cloud account](https://cubecloud.dev/auth/signup) today.

Also, please feel free to [join our community](https://slack.cube.dev/) of almost 7000 data practitioners on Slack, [drop us a line](https://cube.dev/contact), or give Cube a [star on GitHub](https://github.com/cube-js/cube.js). See you there!

Metabase

Self-service analytics with Metabase and Cube