Redash Dashboard Tutorial with Cube.js
Tags: webdev, tutorials, opensource, javascript
Published: 2019-05-16 14:59:16

---
title: Redash Dashboard Tutorial with Cube.js
published: true
description: Building Redash Dashboard with Cube.js
tags: webdev, tutorials, opensource, javascript
cover_image: https://thepracticaldev.s3.amazonaws.com/i/sgerpuf7n1z8vlbrk80a.png
canonical_url: https://cube.dev/blog/redash-dashboard-tutorial-with-cubejs/
---

We recently covered how to build an analytics dashboard using react, vue, or angular. But if you don't want to code and host your own dashboard, you have some open-source alternatives. Today I'll show you how to build an analytics dashboard using [Cube.js](https://cube.dev) Backend and [Redash](https://redash.io/).
You can find the resulting dashboard live [here](https://app.redash.io/statsbot/public/dashboards/Wq8iPcQwYDGTF3GqMSAQGP10Mg6mgTxl1r5C6MKH).

![Dashboard](https://thepracticaldev.s3.amazonaws.com/i/ft65145gqwdjqwol7ms0.png)

## Why Cube.js?
You can always connect Redash directly to your database. You can then write queries in pure SQL and build the same dashboard. It's easy to manage if you have one dashboard and just a couple of queries. But usually, a small dashboard is just a first step in building a complex analytics solution. As your business grows, your volume of data also grows. And soon, you may find a hundred dashboards with thousands of queries used by multiple departments. Now imagine you changed a column in the database. All of the queries should be checked and updated.

Another problem may be the data volume. Say you have a large dashboard powered by some complex queries that take some time to execute. Once you change a filter value on that dashboard, all queries will get executed on the database. In a simple case, it may take a long time to update the dashboard, but if you're using a per-query billed database like Google Bigquery or AWS Athena, this will also hit your monthly bill. 

Cube.js solves both of these problems. The queries used in Redash may be updated independently, so updating a column name or adding a join condition is a simple update in the data schema file. Also, Cube.js Backend manages [pre-aggregations](https://cube.dev/blog/high-performance-data-analytics-with-cubejs-pre-aggregations/), which helps with loading lots of data fast and efficiently. Cube.js pre-aggregates data for huge queries in separate tables and updates them in the background. This is efficient in terms of performance and cost. [Here](https://cube.dev/docs/getting-started-cubejs-schema) you can find a guide on Cube.js Data Schema to understand core concepts better.

## Setting up Cube.js Backend

To launch a Cube.js Backend, you'll need to provide a database where your analytics data is stored and a hosting option. In this demo, we'll use Postgresql database with our demo dataset. If you don't have access to your analytics database right now, you can use this dataset:

```bash
curl http://cube.dev/downloads/ecom-dump.sql > ecom-dump.sql
createdb ecom
psql --dbname ecom -f ecom-dump.sql
```

Now when you have your database running, we have to choose a deployment option. For this demo, we'll host our backend on [Heroku](https://heroku.com/). There's a guide for other deployment options in the [documentation](https://cube.dev/docs/deployment).

Let's install `cubejs-cli` and create a new Cube.js app:

```bash
npm install -g cubejs-cli
cubejs new redash-demo -d postgres
cd redash-demo
```

Now we have to set a data model. For this demo, we'll define only one cube and skip advanced options, which are covered in the [documentation](https://cube.dev/docs/getting-started-cubejs-schema).

```javascript
cube(`Users`, {
  sql: `SELECT * FROM users`,

  measures: {
    count: {
      sql: `id`,
      type: `count`
    }
  },

  dimensions: {
    city: {
      sql: `city`,
      type: `string`
    },

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

    companyName: {
      sql: `company_name`,
      type: `string`
    }
  }
});

```

Now when we have our data model set we can deploy this app to Heroku:


```bash
git init
git add -A
git commit -am "Initial commit"
heroku create cubejs-redash-demo
git push heroku master

```

## Creating a Redash Dashboard

To use Cube.js Backend with Redash, you need to create a [JSON Data Source](https://redash.io/help/data-sources/querying/urls).


Once you've done that, you can create a query. There are a couple of options like URL and Authorization Headers that will be shared across all of the queries, so you can create one query and then fork it to create others. Here's a simple query that will count all Orders in our demo dataset:

```yaml
url: "YOUR-CUBEJS-URL/cubejs-api/v1/load"
headers: 
    Authorization: YOUR-API-TOKEN
path: data
params:
    query: '{"measures": ["Orders.count"]}'
```


Here you need to provide your API URL, which depends on the deployment option you chose. Also, you need to create an [API Token](https://cube.dev/docs/security) to authenticate requests. 
Now click "Save" and "Execute." You should get a response that looks like this:

![Query results](https://thepracticaldev.s3.amazonaws.com/i/jrqjj92mx1nefhs6jugz.png)


Now you can add a visualization. For this query, we'll use a simple "counter" option:

![Counter](https://thepracticaldev.s3.amazonaws.com/i/ygl5j571qe5qf6wwurzp.png)

Let's create a more sophisticated query. We'll create a Stacked Bar Chart for the orders. First, let's define the query: 

```yaml
url: "https://react-query-builder.herokuapp.com/cubejs-api/v1/load"
headers: 
    Authorization: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.e30.K9PiJkjegbhnw4Ca5pPlkTmZihoOm42w8bja9Qs2qJg
path: data
params:
    query: '{
      "measures":["Orders.count"],
      "dimensions":["Orders.status"],
      "timeDimensions":[{"dimension":"Orders.createdAt",
        "dateRange":["2017-01-01","2018-12-31"],
        "granularity":"month"
      }]
}'
```

Save and execute the query and you'll get a table, which is the default visualization. Let's create a Bar Chart:

![Stacked Bar Chart](https://thepracticaldev.s3.amazonaws.com/i/xd9z7fkp3ewa0mem5uo1.png)

Now we have enough queries to create a dashboard. Click "Create" -> "Dashboard," choose a name, and go ahead adding your query widgets.

And the dashboard is done!

## Setting Filters

There's also a Redash option called Parameters. We can set some query values using it. Let's add a daterange filter to our queries:

```yaml
url: "https://react-query-builder.herokuapp.com/cubejs-api/v1/load"
headers: 
    Authorization: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.e30.K9PiJkjegbhnw4Ca5pPlkTmZihoOm42w8bja9Qs2qJg
path: data
params:
    query: '{"measures":["Orders.count"],
        "timeDimensions": [{
          "dimension" : "Orders.createdAt",
          "dateRange": ["{{ daterange.start }}","{{ daterange.end }}"]
        }]
    }'
```

You can see we've added params using the `{{ }}` expression. Now let's change the parameter type to daterange:

![Daterange Filter](https://thepracticaldev.s3.amazonaws.com/i/apq9smcpw35fv8zojv74.png)

Click "Save" and "Execute." Now this query has a daterange filter. You can add other parameters to the query in the same manner. Also, you can set multiple queries to use one dashboard-level filter value using the "edit Parameters" menu. Here's an example:

![Dashboard with filters](https://thepracticaldev.s3.amazonaws.com/i/17wbnsakge7l603k5sbr.png)

And it's done! Now we have a live dashboard in Redash, which you can publish, query, or even run on a display with auto-update.