Building an internal dashboard with Retool and Cube

Have you ever been asked to build a metrics dashboard for displaying internal performance metrics like HTTP response times? Maybe even showing metrics for something business-related like new users visiting a landing page every day?

Don't you love it!? 😕

Excuse my irony, but building actionable graphs and charts is not easy. At Cube, we tried making it easier for you by gathering all the best data visualization tools in one place. However, this still means you need to choose a tool and spend hours writing code.

Using low-code solutions to build internal tools and metrics dashboards is more convenient, a lot faster, and removes strain from your development process.

In this tutorial, I want to build a metrics dashboard for the sales team of an e-commerce company to gain insight into their KPIs. Sounds daunting. 😬

After you're done reading this, you'll be blown away by how simple it is. I'll have a dashboard ready in less than 10 minutes. Yes, this is what you'll get! 👇

I'll be using a hosted Cube deployment on Cube Cloud to get aggregated data from an e-commerce dataset and Retool as the visualization tool to generate a metrics dashboard.

What is Cube Cloud

Cube Cloud is a fully managed API layer for building your Cube apps. It lets you deploy fully managed Cube apps in the cloud of your choice, either AWS, Azure, or GCP.

Cube Cloud enables you to deploy, scale, and optimize Cube apps with best‑in‑class infrastructure and proven best practices. It guarantees high availability, scalability, and improved performance over self-hosted Cube clusters.

Have a look at a video explaining Cube Cloud. ➡️

Anything you can do with self-hosted Cube you can do with Cube Cloud. However, you do get a few more niceties with Cube Cloud. Primarily, query optimization, where you can drill down into queries and spot bottlenecks. You also get pre-aggregations and query acceleration supported out-of-the-box with a Cube Store cluster.

Another awesome feature is pre-aggregation introspection where you can preview all pre-aggregation builds, partitions, indices, and what their build history looks like to make sure there is no stale data.

In this tutorial, I'll use Cube Cloud to set up an analytical API. It will act as the data layer and API for accessing the data I need to populate a metrics dashboard.

You can follow along with either a self-hosted Cube deployment or Cube Cloud. Both options will work the same way.

What is Retool

Retool is the fast way to build internal tools. It lets you visually design apps that interface with any database or API. It lets you create tables, charts, and graphs by providing elegant components for all the UI primitives you need to build robust B2B dashboards.

Check out Retool's video showing how it works. ➡️

When building both internal and user-facing dashboards there are many building blocks that remain the same. They are Tables, Lists, Charts, Forms, Wizards, Maps, and so on.

But it can be hard to find everything you need in single component library, and even when you do, you’ll need to customize components and even make design changes to make your charts look cohesive.

Retool provides a complete set of powerful building blocks out of the box. You should spend your time getting a UI in front of stakeholders and users, not hunting down the best JavaScript charting library especially when it comes to internal-facing tools and apps.

In this tutorial, I'll show you how to assemble a metrics dashboard in a few minutes by dragging and dropping charts and tables from Retool's pre-built components. Retool will act as the visualization library, but without actually using any code. It's like music to my ears.

Why Cube ❤️ Retool

Cube exposes an HTTP API because it's essentially an API server. Everything you need in order to interact with Cube is a single API handler.

That's where Retool comes into play. It has great support for HTTP APIs and supports a low-code/no-code solution to building metrics dashboards and internal tools.

With Cube and Retool working in tandem, I'll be able to create a dashboard in less than 10 minutes. Don't believe me? Hold my 🍺.

Set Up Cube

To configure Cube, I first needed to connect a database. I used a sample PostgreSQL database with e-commerce data.

The process of connecting a database is identical whether you're using a self-hosted instance of Cube, or Cube Cloud.

I selected PostgreSQL from the list of database options. Cube supports dozens of databases, you can select from any mainstream database or data warehouse on the market.

Next, I had to add values to connect to my sample PostgreSQL e-commerce database. If you're using a self-hosted Cube instance, you need to configure a few environment variables.

In Cube Cloud, it looks like this.

Once the connection is established, I have to generate a data schema. Selecting the public table and clicking generate will build a Cube data schema for me to work with.

Now I have access to my Cube cluster. In Cube Cloud you should always enable cluster mode for improved performance if you want to run reliably in production.

This will make sure your resources will autoscale based on the load. There's no additional cost for autoscaling, and the pricing model for Cube Cloud is based on throughput.

Next up, I ran a set of queries in the Cube Playground to determine what data I need to make the charts and graphs in Retool.

First of all, I want to know the number of orders created per day grouped by their status.

By clicking on the JSON Query tab, you'll see a JSON object. I'll use this in Retool to access the data through a Retool integration once I add it.

{
  "measures": [
    "Orders.count"
  ],
  "timeDimensions": [
    {
      "dimension": "Orders.createdAt",
      "granularity": "day",
      "dateRange": "This year"
    }
  ],
  "order": {
    "Orders.count": "desc"
  },
  "dimensions": [
    "Orders.status"
  ],
  "filters": []
}

Lastly, I added query acceleration with a simple pre-aggregation. I did this through the Rollup Designer. You open it by clicking the Query was not accelerated with pre-aggregation → link. Clicking the Add to the Data Schema button is everything you need to do.

You can read more about pre-aggregations and how to configure them here in the Cube docs.

Running accelerated queries clearly shows a performance improvement. This is what I see in the Cube Cloud queries tab after adding a pre-aggregation.

Are you tired of holding my 🍺 yet? I told you I'd only need a few minutes. I'm only halfway there though, bear with me. Next up, let's integrate Cube with Retool.

Integrate Cube with Retool

Now, once Cube is configured, the next step is to create a new app and a new resource in Retool and add Cube as a REST API integration.

I started by creating a blank app.

Next, once in the app, I created a new resource.

Then I selected the REST API integration.

To configure the Retool resource, I need to use the URL of my Cube app deployment. Because I'm using Cube Cloud, it's simple. I click the How to connect button on the Overview tab.

Here's the curl connection.

curl \
  -H "Authorization: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjE2MzI4NDQ0MDF9.e4guK7xbqVHGpnZPdIbkLvVBalpgShfut3JmFwMHTsU" \
  -G \
  --data-urlencode 'query={"measures":["LineItems.count"]}' \
  https://thirsty-raccoon.aws-eu-central-1.cubecloudapp.dev/cubejs-api/v1/load

I copied the URL and the Authorization token and pasted them into the Retool resource configuration. The URL should end with v1. It's crucial to make sure not to include the /load part of the URL as it's an API path, not part of the base URL. I'll add the /load part later when I create the Retool Query for fetching data from Cube.

That's it! I have a resource ready to use.

Now comes the fun part. Creating a new query in Retool. Once I created a Retool query I had to select the resource I just created to fetch data from.

As the action type, I selected GET and added load as the URL path. Then, I copied the Cube JSON Query from the previous section above and pasted it as a URL parameter called query.

// Cube JSON Query

{
  "measures": [
    "Orders.count"
  ],
  "timeDimensions": [
    {
      "dimension": "Orders.createdAt",
      "granularity": "day",
      "dateRange": "This year"
    }
  ],
  "order": {
    "Orders.count": "desc"
  },
  "dimensions": [
    "Orders.status"
  ],
  "filters": []
}

Here you can see the Retool Query I created.

I saved this query and ran a preview to see if the data matches what I saw in Cube. Everything looks great. 👌

Create Retool Low-Code Charts and Graphs

With the back-end part of fetching data working nicely, let me shift my focus to building the Retool metrics dashboard.

Retool has an amazing feature where you can drag and drop UI components into the dashboard. I used this to add a table, a bar chart, and a pie chart. For reference, here's a video showing how the drag-and-drop feature works.

Check out Retool's components here. ➡️

The next step I had to do was to bind the data from the Retool Query to the actual charts.

Because the name of my Retool Query is OrdersCreatedByDayPerStatus, using the data binding curly brackets like in the example below will populate the charts correctly.

{{ OrdersCreatedByDayPerStatus.data.data }}

Here's what my table component looks like alongside the data binding. In the columns section, I selected Orders.status, Orders.createdAt, and Orders.count as the columns to display.

The table is simple. Things get a bit more complicated with the charts below.

With the Bar Chart, I wanted to stack the status of every created order while counting them per day.

To get the UI layout just the way I want it, I had to set the Orders.createdAt field as the X-axis value, and the **Order.status as the Group by definition.** I also enabled the Stack grouped data toggle and chose *the *Orders.count sum as the Dataset.

Lastly, with the Pie Chart, I wanted to show the percentage of created orders grouped by their status displayed as a circle.

For the Pie Chart, I selected the Value labels to be Order.status with the Dataset to be a sum of Order.status.

The data will be loaded automatically from the Retool query that fetches data from Cube. The end result of my metrics dashboard looks quite stylish if I can say so myself. 💃

I feel it's missing one key component though. It's static. Let me add a date picker to introduce a dynamic element to my metrics dashboard.

Add Dynamic Retool Charts and Graphs

I dragged a date picker onto my dashboard and configured it to load default dates.

Next up, I updated the query to access Cube with dynamic date values. I did this by using the dateRange1.startValue and dateRange1.endValue data bindings from the date picker component.

This configuration change made sure the values from the date picker are populating the query and fetching the correct data for that date range. The exact query looks like this:

{
  "measures": [    "Orders.count"  ],
  "timeDimensions": [
    {
      "dimension": "Orders.createdAt",
      "granularity": "day",
      "dateRange": [ // This dateRange is dynamic
        "{{ dateRange1.startValue }}",
        "{{ dateRange1.endValue }}"
      ]
    }
  ],
  "order": {
    "Orders.count": "desc"
  },
  "dimensions": [
    "Orders.status"
  ],
  "filters": []
}

By using data bindings in the dateRange field I can dynamically change the query by changing the dates in the date picker component.

Here's what the final dashboard looks like. You can also preview a live version here.

Conclusion

I promised to create a fully functional dynamic metrics dashboard displaying e-commerce KPIs. Here we are, 10 minutes later, sitting with a lovely finished product.

Now give me back my 🍺, thanks!

I think Cube Cloud and Retool are a great match. With Cube Cloud, we want you to focus on creating business value without wasting time and effort on maintaining infrastructure.

Retool has the same philosophy, building business applications for complex workflows with minimal effort to both create and maintain.

Working in unison, they enable you to create breathtaking metrics dashboards and internal tools with almost no code whatsoever.

You can register for Cube Cloud right away, and check out Retool as well!

I'd love to hear your feedback about building metrics dashboards with Cube Cloud in the Cube Community Slack. Click here to join!

Until next time, stay curious, and have fun coding. Also, feel free to leave Cube a ⭐ on GitHub if you liked this article. ✌️