Nicolas Bohorquez

Low-Code Tools

Building a Bubble Dashboard with Cube

Sometimes, you just need an internal tool built quickly, without a lot of fuss, and you can't afford to put too many man-hours on it.

In this tutorial, you'll learn how to develop a business [dashboard](https://qbuddy.budibase.app/app_qbuddy_9a073b8133e443de8205628a1a7a5b96#/dash) using [Budibase](https://budibase.com/), the [Cube REST API](https://cube.dev/docs/rest-api), and a data store like PostgreSQL.

The end result should look like this:

![https://i.imgur.com/h5UuGVE.png](https://i.imgur.com/h5UuGVE.png)

This dashboard will help you answer some typical questions for an e-commerce business:

- How many orders were sold?
- How many products were sold?
- How many items were sold?
- How many customers bought?
- What is the behavior of sales over time?
- What are the trends per customer segment (Consumer, Corporate, Home Office)?

With little effort, you can extend the dashboard to answer more complex questions that include more attributes and even combine them to get useful insights like [cohort analysis](https://www.activestate.com/blog/cohort-analysis-with-python/).

## Why Build with Budibase?

[Budibase](https://budibase.com/) is a low-code app-building platform that helps developers create responsive web applications. It provides access methods to databases, APIs, and common services like authentication and user management with little effort. You can use it in managed and self-hosted apps and include visual components like charts, tables, and forms.

Budibase is well-suited to build applications that are composed of three typical components:

- Web interface
- Data providers
- Automatic actions

That's enough to cover the majority of use cases for non-specialized developers, especially:

- Admin panels
- Internal tools
- Client portals

Budibase's strengths include an open-source license, user administration, responsiveness, connectors to several data providers (relational databases, non-relational databases, graph databases, Airtable, Amazon S3, REST APIs, and even an internal database), email notifications, cron triggers, and Webhooks. Just like any other low-code platform, it has a low barrier to entry and the free layer allows you to experiment with up to four applications.

Other low-code tools available with similar value-offerings include:

- [OpenXava](https://www.openxava.org/en/ate/budibase-alternative)
- [Appsmith](https://www.appsmith.com/)
- [NocoDB](https://nocodb.com/)
- [ToolJet](https://tooljet.com/)
- [Retool](https://retool.com/)

## Why use Cube as an Analytics API Layer?

[Cube](https://cube.dev/) is a great companion for Budibase. It can connect to the same data source as Budibase, but it also provides analytical capabilities and a query API that aren't available in the low-code platform.

Cube's API supports REST, GraphQL, and SQL queries. But, more important for our use-case is that Cube enables query acceleration, aggregating data for quicker querying as well as role-based access control, security, and much more, built-in by default. This drastically cuts down the required man-hours to build your internal tools.

This expands the scope of possible applications that can be developed, from CRUD-based (Create, Update, Retrieve, Delete) to business intelligence solutions. 

## Implementing a Budibase Dashboard with Cube

The sample project has three main components:

- A relational database (PostgreSQL in this tutorial, but you can use MySQL, MongoDB, or any other database supported by [Cube](https://file+.vscode-resource.vscode-webview.net/Users/adnanrahic/Documents/Work/Articles/%5Bhttps://cube.dev/docs/config/databases%5D(https://cube.dev/docs/config/databases)))
- A Cube schema
- A Budibase application

The following diagram shows you the expected interaction between each component:

![https://i.imgur.com/9SR6BQ7.jpeg](https://i.imgur.com/9SR6BQ7.jpeg)

For this tutorial, you can use an [ElephantSQL](https://www.elephantsql.com/plans.html) free instance, a free [Cube cloud](https://cube.dev/pricing/) account, and a free [Budibase Cloud](https://budibase.com/pricing) hosted application.

If you want to self-host, this [project's repository](https://github.com/nickmancol/cube_budibase) includes a `docker-compose.yaml` file you can use to launch a local development environment containing all the necessary tools. You'll need to know about Docker and containers in order to properly access and connect each component.

You can launch the local environment using:

```bash
docker-compose --env-file hosting.properties up
```

Once you have your infrastructure up and running (self-hosted or cloud-based), you'll need a little SQL knowledge to load the data into your PostgreSQL instance. First, create a table in PostgreSQL with the same structure as the original dataset:

```sql
create table orders (
	row_num    	 int    primary key
	, order_id   	 text
	, order_date    timestamp without time zone
	, ship_date   	 timestamp without time zone
	, ship_mode   	 text
	, customer_id    text
	, customer_name    text
	, segment   	 text
	, country   	 text
	, city   		 text
	, state   		 text
	, postal_code    text
	, region   	 text
	, product_id    text
	, category   	 text
	, sub_category    text
	, product_name    text
	, sales   		 numeric
	, quantity   	 numeric
	, discount   	 numeric
	, profit   	 numeric
);
```

Load the data into the table using the SQL script file located in the [repository](https://raw.githubusercontent.com/nickmancol/cube_budibase/main/sql/fill_tables.sql).

Once you have the data loaded, click **Create Deployment** to create a new deployment in Cube. Select the cloud provider of your preference and give your project a name.

![https://i.imgur.com/HyjzB5i.png](https://i.imgur.com/HyjzB5i.png)

After that, you can import a GitHub repository or create a new one to store your Cube schemas. Select the type of data source—in this case, it's PostgreSQL.

![https://i.imgur.com/69g9poM.png](https://i.imgur.com/69g9poM.png)

Finally, make a note of the data source connection details (hostname, port, username, password, and database name).

![https://i.imgur.com/ILByqB4.png](https://i.imgur.com/ILByqB4.png)

After you've connected your database in Cube, generate the first Cube [schema](https://cube.dev/docs/schema/getting-started) for the table orders. This is a JavaScript file of the data model that will be available to query based on the data source.

![https://i.imgur.com/jxW1p6c.png](https://i.imgur.com/jxW1p6c.png)

### Schema Definition

The model that you've created includes three main sections:

1. It defines the raw data that will be used as a source with a SQL query (all the rows from the *Orders* table).
2. It specifies a set of [measures](https://cube.dev/docs/schema/reference/measures), quantitative calculations that you can make over the data (like counting the number of rows, sum the total of units sold, etc).
3. It specifies the [dimensions](https://cube.dev/docs/schema/reference/dimensions), attributes like the category of the product sold, the country, state, and region of the customer, or the shipping mode of the order.

Go to the Cube playground to create some exploratory queries, like counting the number of rows grouped by the state dimension.

![https://i.imgur.com/mVuAZgR.png](https://i.imgur.com/mVuAZgR.png)

In order to answer the business questions mentioned earlier in this tutorial, edit the *Orders* schema to include the following measures:

```jsx
...

uniqueOrders: {
	sql: `order_id`,
	type: 'countDistinct',
},

customers: {
	sql: 'customer_id',
	type: `countDistinct`
},

items: {
	sql: `quantity`,
	type: `sum`
},

...
```

Breaking that down:

- `uniqueOrders` — Each row in the dataset corresponds to a product sold. You can group several products in a single order using Cube's [countDistinct](https://cube.dev/docs/schema/reference/types-and-formats#count-distinct) measure type over the `order_id` column. This lets you group all the product rows into a single order.
- `customers` — In the same way as `uniqueOrders`, you want to count the distinct customers even if they place several orders.
- `items` — Sum up the number of units sold per product to get the total number of items on each order. This uses Cube's [sum](https://cube.dev/docs/schema/reference/types-and-formats#measures-types-sum) type.

The dataset is also already segmented by the classification of each customer. You can use the `segment` attribute to define a filter over the possible values by adding the [segments](https://cube.dev/docs/reference/data-model/segments) section to the schema:

```jsx
...

segments: {
	Consumer: {
		sql: `${CUBE}.segment = 'Consumer'`
	},
	Corporate: {
		sql: `${CUBE}.segment = 'Corporate'`
	},
	HomeOffice: {
		sql: `${CUBE}.segment = 'Home Office'`
	},
	All: {
		sql: ``
	},
}

...
```

Notice the inclusion of an additional `All` segment that doesn't contain any  SQL definition. This will be useful in the Budibase application to set a default value for the filter.

### Defining the Budibase Data Layer

Once you have an updated data schema, click **Create app** in Budibase to create a new application. Click **Start from scratch** in the popup and name your application.

![https://i.imgur.com/DOMFWRk.png](https://i.imgur.com/DOMFWRk.png)

Next, add a REST data source by clicking the **+** button in the **Data**. In the popup, select the REST type of data source.

![https://i.imgur.com/K8xrIJk.png](https://i.imgur.com/K8xrIJk.png)

You will need the details of Cube's REST API. In Cube, go to the **Overview of the Deployment** and copy the endpoint URL. To get the default-created authorization token, click **How to connect** and copy the long string after the **Authorization** header.

![https://i.imgur.com/rZz2CDS.png](https://i.imgur.com/rZz2CDS.png)

Use the values (URL and authorization token) to complete the Budibase details of the REST data source.

![https://i.imgur.com/2HICNxP.png](https://i.imgur.com/2HICNxP.png)

Notice the default header included **Authorization**. This contains the value of the JWT access token copied from Cube. You can secure your API access by generating specific tokens for each application with Cube's CLI [client](https://file+.vscode-resource.vscode-webview.net/Users/adnanrahic/Documents/Work/Articles/%5Bhttps://cube.dev/docs/security#generating-json-web-tokens-jwt%5D(https://cube.dev/docs/security#generating-json-web-tokens-jwt)).

Once you have created the data source, add a validation query against the REST API. On the **Data** tab, click on REST source and then **Add Query**.

Add a name to the query, use the GET method, and set the path to `/load`.

![https://i.imgur.com/ESGhlXW.png](https://i.imgur.com/ESGhlXW.png)

Cube's API [reference](https://cube.dev/docs/rest-api#api-reference-v-1-load) describes this method as the way to get the data for a query. You pass the query as a parameter, use Cube's Playground again in order to generate the properly formatted query, use the `Orders.count` measure, and click **JSON Query** to show the syntax.

![https://i.imgur.com/fKUiN3X.png](https://i.imgur.com/fKUiN3X.png)

You can reduce the query to a minimal expression in order to set it as Budibase's queryString:

```json
query={"measures":["Orders.count"]}
```

Execute the query by clicking **Run Query** and check the result:

![https://i.imgur.com/6BrhZG8.png](https://i.imgur.com/6BrhZG8.png)

The response is a JSON object that contains not only the data but a fair amount of details. You can use a JavaScript transformer to modify the response payload. The following code extracts only the result of the query:

```jsx
return data.data
```

This transformation is executed as a JavaScript function, so be sure to include a `return` statement.

Now, in order to answer the business questions that are at the heart of this whole exercise, go back to Cube's Playground and create a query that includes the following measures:

- `Orders.count`
- `Orders.uniqueOrders`
- `Orders.customers`
- `Orders.items`

Also, select the `Orders.orderDate` dimension with a monthly granularity. Notice that no segment is selected.

![https://i.imgur.com/6pxirej.png](https://i.imgur.com/6pxirej.png)

This is a longer query that includes several sections. Create a new query in Budibase and select the `POST` method instead of `GET`. This way you can include this larger query as a JSON payload, but you must format it a little bit beforehand:

```json

{
	"query": {
		"measures": [
			"Orders.count",
			"Orders.uniqueOrders",
			"Orders.customers",
			"Orders.items"
		],
		"timeDimensions": [{
			"dimension": "Orders.orderDate",
			"granularity": "month"
		}],
		"order": {
			"Orders.orderDate": "asc"
		},
		"segments": ["{{Segment}}"]
	}
}
```

The name of the queryString parameter `query` has been added as the root of the JSON object. The segments section includes a dynamic value called `Segment` that Budibase will replace with a default value or an assigned one.

Add the parameter `Segment` and set the default value as `Orders.All`.

![https://i.imgur.com/IABCLIA.png](https://i.imgur.com/IABCLIA.png)

With the configuration set, you can execute the query and preview the response. The data payload contains the measures and the dimension values per month.

```json
{
	"Orders.orderDate.month": "2014-01-01T00:00:00.000",
	"Orders.orderDate": "2014-01-01T00:00:00.000",
	"Orders.count": "79",
	"Orders.uniqueOrders": "32",
	"Orders.customers": "32",
	"Orders.items": "284"
}
```

The Budibase [Chart](https://docs.budibase.com/docs/components) component requires that the source data contains a JavaScript Number Y-axis value. In order to simplify the response payload and format the column names and values, you can add a transformation to the Budibase REST query like below.

```jsx
const d = Array(data.data)

return d[0].map(v => {
	let o = {}
	o['x'] = v["Orders.orderDate"].substr(0,7)
	o['customers'] = v["Orders.customers"]
	o['orders'] = v["Orders.uniqueOrders"]
	o['products'] = v["Orders.count"]
	o['items'] = v["Orders.items"]

	return o
})
```

This code will iterate over each result and create a new object with five properties:

- The X-axis value (which is the orderDate month)

The Number values for the:

- Customers
- Orders
- Products
- Items columns

The result of the transformation is a more readable object you can use along with the components of the user interface.

![https://i.imgur.com/BsJFVLl.png](https://i.imgur.com/BsJFVLl.png)

### Application Design

Now that you have the data layer connected and transformed, add a new screen in Budibase by clicking the **Design** tab, then **Add Screen**.

![https://i.imgur.com/X9enTKQ.png](https://i.imgur.com/X9enTKQ.png)

The screens in Budibase are the root of the user interface component tree. The dashboard is designed to have a chart, a selection box filter, and several cards that show information extracted from a data provider that will execute the previously created REST query. The following diagram shows the component hierarchy:

![https://i.imgur.com/sb0nz56.jpeg](https://i.imgur.com/sb0nz56.jpeg)

It's important to note that each leaf in the component tree can access only their parents' exposed properties. In this case, if you define the data provider at the same level as the form, the components inside the form will not be able to interact with the data provider.

The binding between the data layer and the UI components is handled by assigning some properties to each component. For example, the data provider `DpOrdersOverTime`'s data comes from the `Orders over time` REST query.

But given that this REST query requires a parameter (remember the `Segment` parameter used to filter results?), you'll need to click the **Settings** gear icon on the right side of the data property to add the default value for the parameter in the data provider.

![https://i.imgur.com/ce0E984.png](https://i.imgur.com/ce0E984.png)

The default value is the `Segment` field of the global form. This field is assigned to the `SelSegment` selection box; this way, once you select a different value, the REST query is updated and the UI refreshed with the new values.

The following image shows you the `SelSegment` selection box design:

![https://i.imgur.com/vqENrMm.png](https://i.imgur.com/vqENrMm.png)

In order to set the options for the selection box, click **Define options** in the **Settings** panel. Add the four segments:

- Customer
- Corporate
- HomeOffice
- All

![https://i.imgur.com/Q9Tv1u3.png](https://i.imgur.com/Q9Tv1u3.png)

To display the results of the query, you can use a Chart. The chart component `ChCustomers` uses the `DpOrdersOverTime` data provider. You also have to set which X-axis column to use and which Y-axis column values will be used to render the line chart.

Select the chart component and update the properties in the Settings panel.

You can also use other components to render the results of the query. The right side cards in the dashboard show information that's calculated after the query is executed; for example, you can modify the source expression of the `CdOrdersPerProduct` card by clicking the lightning icon in the **Settings** panel.

Add an expression in JavaScript that will return the sum of orders divided by the sum of customers from the data provider:

```JavaScript
return ($("DpOrdersOverTime.Rows").reduce((partial_sum, a) => partial_sum + Number(a["orders"]),0)

/

$("DpOrdersOverTime.Rows").reduce((partial_sum, a) => partial_sum + Number(a["customers"]),0)).toFixed(2);
```

![https://i.imgur.com/goTtuTW.png](https://i.imgur.com/goTtuTW.png)

You can add other charts and cards to show more data or create new filters. Once you've finished, you can change the screen access level in the **Settings** panel and publish your application.

## Conclusion

Sometimes, you don't want to reinvent the wheel, you just need to get a tool up and running for the sake of internal efficiency in your team.

In this tutorial, you learned how to leverage the power of [Cube](https://cube.dev/) to build an analytics dashboard that aggregates data from a relational database and displays it using the low-code tool [Budibase](https://budibase.com).

In this way, you learned how to easily create measures, dimensions, and segments from raw data and query it using Cube's Playground. The REST API provided by Cube allows you to generate more complex analysis and also connect with many other tools to render your insights.

Don't forget, you can check out the full [application here](https://qbuddy.budibase.app/app_qbuddy_9a073b8133e443de8205628a1a7a5b96#/dash) as well as the [code in GitHub](https://github.com/nickmancol/cube_budibase).

I'd love to hear your feedback about building metrics dashboards with Cube Cloud in the Cube Community Slack. [Click here to join](https://slack.cube.dev/)!

Until next time, stay curious, and have fun coding. Also, feel free to [leave Cube a ⭐ on GitHub](https://github.com/cube-js/cube.js) if you liked this article. ✌️

Building a Budibase Dashboard with Cube

Every project contains several moving parts. We developers call them features. They can be dashboards, performance overviews, a blog, or web pages that include forms. Features take time to develop from scratch. That can impact delivery and deadlines.

In this article, I want to create a metrics dashboard with Appsmith. I'll use API endpoints generated from [Cube](https://cube.dev/) with a public dataset from the Museum of Modern Art (MoMA).

Here's what the end-product will look like:

![dashboard-image](https://i.imgur.com/u2O0rHg.png)

I want to showcase how it would be more efficient for your team to have a tool to create custom dashboards for internal metrics. A huge bonus point is that it can be used by anyone, including non-devs in your team. This is where [low-code tools](https://en.wikipedia.org/wiki/Low-code_development_platform) come into play.

[Appsmith](https://www.appsmith.com/) is an open-source low-code platform that lets you create web applications like dashboards and admin panels by using drag-and-drop widgets. With Appsmith, you can connect multiple data sources like MySQL, MongoDB, REST API, Google Sheets, and perform CRUD operations. 

I'll be using a hosted [Cube](https://github.com/cube-js/cube.js) deployment on [Cube Cloud](https://cube.dev/cloud) to get data from the Museum of Modern Art (MoMA) dataset.

## A Quick Look at Appsmith

You don't need to be a developer to create metrics dashboards using Appsmith. It comes with a set of drag-and-drop widgets for forms, charts, images, and more, that you can use in your application to enhance its functionalities. 

A few of the benefits of Appsmith include:

* The ability to create apps without development knowledge
* Saving the developer team's time
* Saving your company's money by saving man-hours
* Increased productivity
* Drag-and-drop widgets

> However, keep in mind that despite all of its functionality, Appsmith can't compare to the customization level of a custom-built app.

## A Quick Look at Cube

Cube is an open-source analytics API platform that lets you connect with data sources and then access the data through an API. You can connect to data sources like MySQL, AWS Athena, and Google BigQuery, among others. It's agnostic for visualization tools, meaning you can connect to any front-end charting framework and build your own customized UI. 

The Cube API lets you sort, group, and filter through the dataset itself. It acts as a centralized back-end metrics layer for any dataset. The multi-staging query system allows Cube to handle trillions of data points.

## Creating a Dashboard with Appsmith and Cube

You're going to display a [public dataset from the MoMA](https://github.com/MuseumofModernArt/collection) on the dashboard you're creating on Appsmith. The [MoMA](https://www.moma.org/) is one of the largest and most influential museums in the world, with around 300,000 books and catalogs, and more than a thousand periodic files. 

This is how your dashboard will look:

![dashboard-image](https://i.imgur.com/u2O0rHg.png)

You'll use [db4free.net](https://db4free.net/), a free database service, to upload and host the database. After uploading the data to the database, you'll connect to the database using Cube. You'll be able to integrate the API generated through Cube into the different widgets of Appsmith.

### Prerequisites

* Any public dataset (in this case we're using [one from MoMA](https://github.com/MuseumofModernArt/collection))
* A Cube account
* An Appsmith account
* Any database service; this tutorial will be using [db4free.net](https://db4free.net/)

### 1: Clone the Dataset and Host It on Your Database

Once you clone the dataset, use the PHPMyAdmin or the cPanel of the database hosting service and import the data via CSV.

### 2: Connect Cube to Your Database

Now log in to your Cube account and click **Create deployment**. Give the deployment a name; the images in this example will use `Appsmith Dashboard`.

![setup-1](https://i.imgur.com/zTzxaTn.png)

On the next screen, select **MySQL** from the data source list. If you're using some other data source like Google BigQuery or AWS Athena, select the option accordingly.

![step-2](https://i.imgur.com/iPv7Mef.png)

Now, just fill in your database credentials, and you're good to go!

![step-3](https://i.imgur.com/xcrcC4J.png)

Once you've generated the data schema of your dataset, you can create REST APIs.

### 3: Get API Credentials and API Endpoints from Cube

Once you have created a deployment, you'll be redirected to the overview page of the deployment. 

![overview-page](https://i.imgur.com/c1q5lKO.png)

Copy the API endpoint and click **How to connect your application** to get the Authorization key.

And you're done creating Cube's API. It's time to move ahead with Appsmith to create your dashboard.

### 4: Set Up Appsmith 

Log in to your Appsmith account, create a new application, and select **Build with drag & drop**.

![create-app-1](https://i.imgur.com/gZbKRY5.png)

### 5: Create the First Widget

Once you see an empty canvas, click the **+** button next to **Datasources** in the sidebar. Select **Create new API**.

![create-app-2](https://i.imgur.com/giBWq1e.png)

You should see the following page:

![create-app-3](https://i.imgur.com/fTILtZj.png)

For the URL, paste the REST API endpoint, and in the Authorization header, paste your API code. In the case of this tutorial, the REST API endpoint is `https://fuchsia-shark.aws-eu-central-1.cubecloudapp.dev/cubejs-api/v1/load`.

Pass the following JSON as a `GET` param:

```json
{
  "dimensions": [
    "Artists.begindate",
    "Artists.displayname"
  ],
  "timeDimensions": [],
  "order": [
    [
      "Artists.begindate",
      "desc"
    ]
  ],
  "limit": 10
}
```

In this code block, you're asking the REST API to return the top ten begin dates and display name. The rows are in descending order.

Once you start to get the data, you'll see a list of widgets on the right side of the page. Select **Table** from this list:

<div style={{ overflow: "hidden" }}>
<img style={{ display: "block", width: "300px" }} src="https://i.imgur.com/0ipSuYn.png" />
</div>

The table will appear in the canvas.

### 6: Create the Second Widget

Repeat the previous step but with a different query. Just add another API data source with the following query:

```json
{
    "dimensions": [     
        "Artists.nationality"   
    ],   
    "timeDimensions": [],   
    "order": {     
        "Artists.count": "desc"   
    },   
    "ungrouped": false,   
    "measures": [     
        "Artists.count"   
    ] 
}
```

This query will return the count of artists according to country.

Again, select **Table** from the widget list. Your canvas should look something like this:

![create-app-6](https://ucarecdn.com/3bbc1772-b24c-4a88-b05c-b56738adffb6/-/preview/500x500/-/quality/smart_retina/-/format/auto/)

### 7: Add Different Widgets as Per Your Requirements

In this example, I've experimented with different widgets and data customizations. I've added three more charts using this query.

#### Query 1

This query will return the count of all artists with their begin date.

```json
{   
    "dimensions": [     
        "Artists.gender", 
        "Artists.begindate"   
    ], 
    "timeDimensions": [],   
    "order": {     
        "Artists.count": "desc"   
    },   
    "ungrouped": false,   
    "measures": [     
        "Artists.count"   
    ], 
    "filters": [
        {
            "member": "Artists.gender",
            "operator": "set"
        },
        {
            "member": "Artists.begindate",
            "operator": "notEquals",
            "values": [
                "0"
            ]
        }
    ]
}
```

#### Query 2

This query will return the gender breakdown of the complete dataset. I have used the data coming from this API in the pie chart.

```json
{
  "dimensions": [
    "Artists.gender"
  ],
  "timeDimensions": [],
  "order": {
    "Artists.count": "desc"
  },
  "measures": [
    "Artists.count"
  ],
  "filters": []
}
```

#### Query 3

This query will return the count of artists from each country listed in the dataset.

```json
{
  "dimensions": [
    "Artists.nationality"
  ],
  "timeDimensions": [],
  "order": {
    "Artists.count": "desc"
  },
  "limit": 10,
  "measures": [
    "Artists.count"
  ],
  "filters": []
}
```

You can use this query with any widget to represent the data efficiently.

## Create Your Own Internal Apps with Appsmith

In this article, you learned how to create a metrics dashboard with Appsmith and Cube to display data from the public dataset from the Museum of Modern Art (MoMA). You now know how to use low-code tools to save time and money on development.

Check out the [live example here](https://app.appsmith.com/applications/61b8ddaa1f4a0a565c025e76/pages/61b8ddaa1f4a0a565c025e78?isCommentMode=false​​) and try adding some more widgets to your metrics dashboard with Appsmith.

I think Cube and Appsmith are a great match. With Cube, you focus on creating business value without wasting time and effort on maintaining infrastructure.

Appsmith lets you focus on creating business value with metrics dashboards with minimal effort to both create and maintain.

You can [register for Cube Cloud](https://cube.dev/cloud) right away, and check out [Appsmith](https://www.appsmith.com/) 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](https://slack.cube.dev/)!

Until next time, stay curious, and have fun coding. Also, feel free to [leave Cube a ⭐ on GitHub](https://github.com/cube-js/cube.js) if you liked this article. ✌️

Vyom Srivastava

Building an Appsmith Dashboard with 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!? 😕

![iron-e.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/5ce0a309-ee49-422c-b990-3368d93d0474.png)

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](https://awesome.cube.dev/). 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! 👇

![frame_chrome_mac_light_(44).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/bbe1f014-69db-4521-8b51-fa8f40baae63.png)

I'll be using a hosted [Cube](https://github.com/cube-js/cube.js) deployment on [Cube Cloud](https://cube.dev/cloud) to get aggregated data from an e-commerce dataset and [Retool](https://retool.com/) 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](https://cube.dev/docs/product/getting-started/cloud/create-data-model) in the cloud of your choice, either AWS, Azure, or GCP.

![how_cube_cloud_works.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/f9fe8ccf-8d70-4386-8431-6f4f9f3891e1.png)

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.

<div style={{ overflow: "hidden" }}>
<iframe style={{ display: "block", width: "100%", height: "400px" }} src="https://cube.dev/video/cube-cloud-demo.mp4" frameborder="0" allow="fullscreen"></iframe>
</div>

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](https://cube.dev/docs/cloud/inspecting-queries), 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](https://cube.dev/docs/deployment/production-checklist#set-up-cube-store).

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](https://cube.dev/docs/getting-started/docker) deployment or [Cube Cloud](https://cube.dev/docs/product/getting-started). 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.

<div style={{ overflow: "hidden" }}>
<iframe style={{ display: "block", width: "100%", height: "400px" }} src="https://d3399nw8s4ngfo.cloudfront.net/videos/component-assemble.mp4" frameborder="0" allow="fullscreen"></iframe>
</div>

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](https://cube.dev/docs/config/databases), you can select from any mainstream database or data warehouse on the market.

![frame_chrome_mac_light_(37).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9ca37d9b-cdc4-4ebd-a41b-31b1ded693e5.png)

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](https://cube.dev/docs/config/databases/postgres).

In Cube Cloud, it looks like this.

![frame_chrome_mac_light_(50).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/f44924c6-b117-4d50-9d78-11625238b4b9.png)

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.

![frame_chrome_mac_light_(51).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/ec5c7697-c698-4d8c-938e-8fdeb2193b7f.png)

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](https://cube.dev/pricing) is based on throughput.

![frame_chrome_mac_light_(22).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/00d52f17-d1c7-4768-b692-6fbb78954d33.png)

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.

![frame_chrome_mac_light_(24).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/247919d7-68ea-4edf-a60c-43d2b9d5c34b.png)

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.

```json
{
  "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](https://cube.dev/docs/caching/pre-aggregations/getting-started) 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](https://cube.dev/docs/caching/using-pre-aggregations).

![frame_chrome_mac_light_(26).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/1d6190e2-8d21-4f6f-897d-54df10141110.png)

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

![frame_chrome_mac_light_(31).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/1bcb0ed4-a782-4048-a04f-78a1edf5c69f.png)

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.

![frame_chrome_mac_light_(53).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/09374bcc-1cc9-4169-91ac-78f7cc0ec101.png)

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

![frame_chrome_mac_light_(52).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/41b30b97-2b2c-46af-becc-912a7c666784.png)

Then I selected the REST API integration.

![frame_chrome_mac_light_(54).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/cb6c291c-cf42-44cc-803c-761b92387dc9.png)

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.

![frame_chrome_mac_light_(40).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/d4cee3df-0945-43d2-9afe-8acf883626e2.png)

Here's the curl connection.

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

![frame_chrome_mac_light_(55).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/acec9699-cd6c-4c1e-aeb7-0db2feba1e76.png)

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

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

![frame_chrome_mac_light_(56).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/c466d0a8-21ff-48de-be3d-81d2daf63454.png)

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.

<div style={{ overflow: "hidden" }}>
<iframe style={{ display: "block", width: "100%", height: "400px" }} src="https://d3399nw8s4ngfo.cloudfront.net/videos/component-assemble.mp4" frameborder="0" allow="fullscreen"></iframe>
</div>

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.

```json
{{ 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**.

![2021-11-10_12-21.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/709000be-2118-4fdb-9c12-6bf71ac10452.png)

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

![2021-11-10_12-21_1.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/8c1d8d1a-7a7b-4726-afb1-df1833fdf223.png)

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

![2021-11-10_12-22.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/35e55d26-3f28-4719-9103-e49f23e4d343.png)

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

![cubedev.retool.com_apps_DevRel_App201__releaseVersionlatest_(1).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/6d80c3f6-af69-4f30-9dca-aa8343a3b16f.png)

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.

![image (23).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/f5c988ae-b6a7-49f6-be17-2c2d641b4a5e.png)

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.

![frame_chrome_mac_light_(46).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/2c9f31af-e9d1-458d-8ed0-c4362a5b2dea.png)

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:

```json
{
  "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](https://cubedev.retool.com/embedded/public/945c174d-566e-42f6-b33c-73052847e483).

![frame_chrome_mac_light_(44).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/e30c51c1-2d7b-4227-b9a0-f0969aab917d.png)

### 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](https://cube.dev/cloud) right away, and check out [Retool](https://retool.com/) 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](https://slack.cube.dev/)!

Until next time, stay curious, and have fun coding. Also, feel free to [leave Cube a ⭐ on GitHub](https://github.com/cube-js/cube.js) if you liked this article. ✌️

Low-Code Tools

Building a Bubble Dashboard with Cube

Building a Budibase Dashboard with Cube

Get Cube updates to your inbox

Building an Appsmith Dashboard with Cube

Building an internal dashboard with Retool and Cube

Upgrade your data stack today