Building a Bubble Dashboard with Cube
Tags: bigdata, businessintelligence, webdev, tutorial
Published: 2022-02-18 15:11:11

This guest post was written by Nicolas Bohorquez.
Nicolas Bohorquez is a data architect at Merqueo, has been part of development teams in a handful of startups, and has founded three companies in the Americas. He is passionate about the modeling of complexity and the use of data science to improve the world. You can get in touch with him through his [Twitter](https://twitter.com/nickmancol)!

---

[Bubble](https://bubble.io/) is one of the oldest and most comprehensive low-code app-building platforms on the market. Founded in 2012, it’s grown to have one of the broadest offerings in terms of functionality and offers application templates, a custom data layer that can be exposed as an API, authentication and authorization flows, responsive applications, form handling, internal workflows, and a large plug-in marketplace that extends the platform into an entire ecosystem.

In this tutorial, you will build a simple metrics dashboard for displaying data extracted from a business database using [Bubble](https://bubble.io), [Cube](https://cube.dev), and [PostgreSQL](https://www.postgresql.org) as the database. You’ll also use Cube’s segments feature to create a simple filter that will dynamically reload data from the REST API. The image below shows you the final result. You can also check out the [live demo](https://bubble.io/page?id=cubebubble) or [see a video of what it will look like in action](https://i.imgur.com/MsPDuIh.mp4).

![The finished dashboard](https://i.imgur.com/4fjknDt.png)

The dashboard app will display basic business information, like the number of orders received, the customers who created the orders, and the quantity of items requested. We’ll also take advantage of the [Cube Segments](https://cube.dev/docs/schema/reference/segments#top) definition to include a simple filter that will dynamically update the view based on the customer segmentation that the original data set provides.

## What Is Bubble

Bubble is an ecosystem of tools for easily creating applications. These apps are usually composed of a user interface (UI) paired with data and *workflows*, which are actions that are executed in a sequence after a trigger event—for example, refreshing data after applying a filter. This makes Bubble suitable for a large number of use cases, including admin panels, internal tools, and client portals.

Bubble has many strong selling points: It’s a fully managed SaaS tool, freeing developers from deployment administration; it offers application versioning, allowing you to incrementally update your app; the apps you create will be responsive, look good, and work correctly on both web and mobile; and there are numerous plug-ins available to extend the built-in components. The primary downside to Bubble is that you can’t self host, but this frees you up to worry about your application, not its deployment.

Another great thing about Bubble is how well it works with Cube. The Cube REST API easily integrates with Bubble applications and provides analytical capabilities that aren’t available in Bubble. The REST API can be connected to any Bubble app, and Bubble’s visual components can then effortlessly display and manipulate the data extracted from Cube.

## What Is Cube

[Cube](https://cube.dev/) is a powerful open-source analytics tool that provides an API layer to connect your data warehouse to your front end. The REST API provided by Cube allows you to generate complex analysis and to connect with many other tools to display your insights.

## Implementing a Bubble Dashboard with Cube

The sample project is built with three main components: a relational database ([PostgreSQL](https://www.postgresql.org) is used in the tutorial, but you can use [MySQL](https://www.mysql.com/), [MongoDB](https://www.mongodb.com/), or any other database supported by [Cube](https://cube.dev/docs/config/databases)), a Cube schema, and a Bubble application. The following diagram shows you the expected interactions between the components.

![Architecture](https://i.imgur.com/4wdJZE9.png)

For this tutorial, you can use a free instance of [ElephantSQL](https://www.elephantsql.com/plans.html), a free [Cube Cloud](https://cube.dev/pricing/) account, and a free [Bubble](https://bubble.io/pricing) hosted application. The [dashboard](https://cubebubble.bubbleapps.io/version-test) will use data from the US Superstore data set available on [Kaggle](https://www.kaggle.com/juhi1994/superstore), which contains 9,994 rows of detailed information about e-commerce orders.

### Launching Local Infrastructure

While you can’t self-host Bubble applications, you *can* deploy your own Cube or PostgreSQL environment and expose it for Bubble’s API connector to consume. You’ll need basic knowledge about [Docker](https://www.docker.com/) and containers in order to properly access and connect each component.

Just follow the Cube Docker Compose [installation instructions](https://cube.dev/docs/getting-started/docker/compose) or use the `docker-compose.yaml` file from the [repository for this project](https://github.com/nickmancol/cube_bubble.io) to launch a local development environment that contains Cube and PostgreSQL, along with the original data and two SQL scripts to create and load the data table into PostgreSQL. You can launch the local environment using the following command:

```sh
docker-compose up
```

### Loading Data into PostgreSQL

If you choose to use the free cloud instance of PostgreSQL, you’ll need a little SQL to load the data into the ElephantSQL instance once your infrastructure is up and running. Start by connecting to the instance using a SQL client ([DBeaver](https://dbeaver.io/) is a great open source option) with the details from the ElephantSQL page, which will look like this:

![ElephantSQL connection details](https://i.imgur.com/w0jTu9F.png)

Then create a table with the same structure as the original data set using the following SQL script:

```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
);
```

With the table created, you can 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). 

### Creating a Cube Deployment

Once you have the data loaded, create a new deployment in [Cube Cloud](https://cube.dev/cloud) by clicking the **Create Deployment** button on the upper-right corner, selecting the cloud provider of your preference, and giving the project a name.

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

Now you can import a GitHub repository, or create a new one, to store your Cube schemas. For this tutorial, click on the **Create** button. After that, select the type of data source; in this case, it’s PostgreSQL.

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

Finally, you’ll need the data source connection details—hostname, port, username, password, and database name—all the values you previously copied from the ElephantSQL details.

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

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

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

### Defining a Cube Schema

The model that you created includes three main sections. The first section defines the raw data that will be used as the source with a SQL query (all the rows from the `orders` table). The second specifies a set of [measures](https://cube.dev/docs/schema/reference/measures), which are quantitative evaluations of the data, such as counting the number of rows or the sum of the total units sold. The third section establishes the [dimensions](https://cube.dev/docs/schema/reference/dimensions), which are attributes, like the product category, the location of the customer, or the ship mode of the order. You can go to the [Cube Developer Playground](https://cube.dev/docs/dev-tools/dev-playground) to create some exploratory queries, like the number of rows in each state dimension.

![Cube Developer Playground exploration](https://i.imgur.com/mVuAZgR.png)

In order to address measures that are broader than a single order, click on **Enter Development Mode** and edit the **Orders** schema to include the following measures:

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

	customers: {
  	sql: 'customer_id',
  	type: `countDistinct`
	},
    
	items: {
  	sql: `quantity`,
  	type: `sum`
	}, 
```

- **`uniqueOrders`:** Each row in the data set corresponds to a product sold, but a single order can have many products. Using Cube’s [`countDistinct`](https://cube.dev/docs/schema/reference/types-and-formats#count-distinct) measure type in the `order_id` column will let you group product rows by order.
- **`customers`:** Similar to `uniqueOrders`, you want to count the distinct customers, even if they have several orders.
- **`items`:** The number of units sold per product can be summed up to get the total number of items in each order. This measure uses Cube’s [sum](https://cube.dev/docs/schema/reference/types-and-formats#measures-types-sum) type.

Additionally, the data set is already segmented by the classification of each customer; the segment attribute can be used to filter the possible values by adding the [segments](https://cube.dev/docs/schema/reference/segments#top) section to the schema.

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

Notice that an additional `All` segment not containing any SQL definition was included. This will be useful in the Bubble application to set a default value for the filter.

Hit the **Commit & Push** button to apply the changes to the Cube project and make it available through the REST API.

### Using the Bubble API connector

Once you have an updated data schema, create a new application in Bubble via the **New app** button on the homepage of your account. Name the application, select dashboard as the type, fill the other details, and hit the **Create a new app** button.

![Bubble create app](https://i.imgur.com/aYpgBAu.png)

In order to connect to an external REST API in Bubble, you need to install the API Connector plug-in. Go to the plug-ins panel on the left side of the project and select **Add plugins**.

![Bubble add plug-ins](https://i.imgur.com/ULQhL8j.png)

In the pop-up, search for “API” and choose the Bubble API Connector free plug-in. To install it, just click **Install**; and once that’s done, you can leave the plug-ins installation page by clicking **Done**.

![Bubble install plug-in](https://i.imgur.com/uDqhjmT.png)

Now to connect the Cube API. Go to the **Add another API** button, where you’ll set the values. For the API name, enter “Cubedev” and select **Private key in header** as the authentication method. Set the key name as “Authorization” and select **Action** in the **Use as** drop-down. This is important because it’s what will let you call the query from the Bubble workflows of the application.

![Bubble API connection details](https://i.imgur.com/dCzO0fB.png)

You can copy the key value from the Cube connection details. To locate it, go to the **Overview** page of the deployment and copy the endpoint URL. In order to get the default authorization token, click on the **How to connect** link and copy the long string after the “Authorization” header:

![Cube how to connect](https://i.imgur.com/f1RcYyY.png)

Notice the default header included “Authorization”. This contains the value of the [JSON web token](https://jwt.io/introduction) copied from Cube. You can secure your API access by generating specific tokens for each application using Cube’s CLI [client](https://cube.dev/docs/security#generating-json-web-tokens-jwt) tool. Remember to add a shared header, setting the `Content-Type` as `application/json`.

Once you’ve created the data source, you can run a validation query against the REST API. Press the **Add another call** button and complete the details to call the Cube REST API load endpoint.

![Bubble new call](https://i.imgur.com/LAR0eyJ.png)

Give the query a name, tell it to use the POST method, and set the path to Cube’s API URL concatenated with the `/load` suffix. You can get the API URL from Cube’s **Copy API URL** button. Select **Action** in the **Use as** drop-down.

![Cube API URL](https://i.imgur.com/oxzMgNC.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 have to pass the query as a parameter in order to generate the properly formatted query. Use the Cube Developer Playground to graphically generate the desired query using `Orders.count` and click on the **JSON Query** button to show the syntax.

![Cube base query](https://i.imgur.com/yuR8ZuO.png)

Reduce the query to a minimal expression and set it as the API call’s body.

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

You can execute a test for the query by pressing the **Initialize call** button and checking the result.

![Bubble query result](https://i.imgur.com/b3PXufG.png)

The response is a JSON object that contains not only the data but also a fair amount of detail. The most important one is the body, which contains the value of the measure. Change the type from “text” to “number” and hit the **SAVE** button.

Now, in order to answer the business-related questions, go back to the Cube Developer 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 there’s no segment selected:

![Cube advanced query](https://i.imgur.com/uDk7yWU.png)

This is a longer query that includes several sections. To map this query as an API call in Bubble, go back to the API Connector and hit the **Add another call** button:

![Bubble advanced call](https://i.imgur.com/iGneR6S.png)

The endpoint is the same as the previously configured API call, but in this case, uncheck the **Private** checkbox to pass a dynamic parameter to the body JSON payload. Setting the [segment](https://cube.dev/docs/schema/reference/segments#top) of the query as a filter in the application’s UI will be useful. It makes the payload a little more complex, and the `segments` value uses special `<` and `>` markers to introduce the parameter `Segment`.

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

Once you initialize the query, remember to update the mapping of the types of the data returned by the query.

![Bubble advanced mapping](https://i.imgur.com/sEJ7XRo.png)

### Designing the Application

Now that you have the data layer connected and mapped, go to the **Design** tab and add the elements required to build the user interface. Start by placing a text label and a drop-down for the filter at the top of the UI.

![Filter UI](https://i.imgur.com/33YmJA2.png)

To fill the options of the drop-down with `(key,value)` tuples, you need to create an [option set](https://bubble.io/video-course/intro-to-option-sets-1610554750471x681145058184921100). Go to **Data** in your application and then to the **Option sets** tab, where you’ll create a new option set called segments that will have four possible options: All, Customer, Corporate, and Home Office.

![Bubble option sets](https://i.imgur.com/t83slMf.png)

By default, it has only a display attribute of type text. Add a new attribute named “Value” of type text by clicking the **Create new attribute** button.

<div style={{ overflow: "hidden" }}>
<img style={{ display: "block", width: "400px" }} src="https://i.imgur.com/BL3j3AR.png" alt="Bubble new attribute for option set" />
</div>

Then add the four options with the proper values:

- All -> `Orders.All`
- Customer -> `Orders.Customer`
- Corporate -> `Orders.Corporate`
- Home Office -> `Orders.HomeOffice`

Return to the drop-down properties in the **Design** tab to set the **Choices style** to “Dynamic choices” and **Type of choices** to “Segments.”

<div style={{ overflow: "hidden" }}>
<img style={{ display: "block", width: "400px" }} src="https://i.imgur.com/VvmAReL.png" alt="Bubble drop-down configuration" />
</div>

Now add four more text fields to the UI. These fields will hold the dynamic aggregated values of the number of unique orders, unique customers, and items.

![Bubble texts for agg](https://i.imgur.com/86H8r1F.png)

To complete the UI, install a new plug-in called Simple LineChart from ApexCharts. This will be used to display several data series (orders, customers, and items) as a single line chart.

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

With the plug-in installed, add a line chart to complete the user interface of the dashboard.

![Bubble full UI](https://i.imgur.com/uMEBgrv.png)

### Adding the Workflows to Load the Data

It’s time to create the workflows that will fetch the data from Cube’s REST API through the API calls configured earlier. A *workflow* is a sequence of automated actions that occurs when a trigger is activated. In this case, two types of events will be used as triggers: first, when the page is loaded; and second, when the drop-down’s value is changed. Go to the **Workflow** pane and click where it says **Click here to add an event**. Select **General** and **Page is loaded** from the pop-up as your first event trigger:

![Page load event](https://i.imgur.com/MhBHu6W.png)

Use the **Click here to add an action** and the pop-up filter to search for the **Cubedev - FullMeasures** action. If it doesn’t appear, you may not have set the **Use as** property of the API call to action—go back and correct that, then try again.

![Fullmeasures action](https://i.imgur.com/b2KoUW9.png)

Now every time the page loads, the first action will be to call Cube’s API, which returns several pieces of data. If you recall, this API call requires a dynamic parameter called segment in order to set it statically. In the action’s step properties box, write the default value for the desired segment, `Orders.All`. This will cause Cube’s query to return the data for all the segments.

![Static segment param in action](https://i.imgur.com/IfbQnk8.png)

Then to save a local copy of the results returned by the query, add a new action (filter by “Set state of an element”) and select the **Index** page as the holder of the new state.

![New state holder](https://i.imgur.com/KykgBcS.png)

The custom state property of the dialog lets you create a new state.

<div style={{ overflow: "hidden" }}>
<img style={{ display: "block", width: "400px" }} src="https://i.imgur.com/hHAuLzH.png" alt="Create new page state" />
</div>

This state will contain a list of the FullMeasures’s data, properly mapped for the previous API call, in a backend operation that will allow Bubble to populate this list with the values returned for each call to the API. To do this, complete the value property of the set state action with the “Result of step 1’s data”:

<div style={{ overflow: "hidden" }}>
<img style={{ display: "block", width: "400px" }} src="https://i.imgur.com/mJnqnjJ.png" alt="Custom state value" />
</div>

Use the same steps to create another workflow, but use the **drop-down’s value is changed** as the trigger. This way, every time a user selects a different segment from the drop-down in the UI, the data will be updated and created as a state of the index page.

### Linking the UI with the Data

With all your workflows defined, you have to link the data. You need to set the dynamic text for each label in the UI and then for each series of the chart. Starting with the label for the total number of orders, select the *Text* on the **Design** tab and remove the “...edit me…” value; then click on the blue **Insert dynamic data** pop-up on the right of the properties pop-up.

![Text dynamic data](https://i.imgur.com/ePDILjb.png)

Navigate through **Index**, **Fullresults**, then **each item’s Orders.count** to select `sum`. You can probably already guess that this will read the custom state Fullresults from the index page, then sum all the values of the list’s `Orders.count` attribute.

![Subsequent data source selection](https://i.imgur.com/zJypIEb.png)

You can select the `Orders.uniqueOrders`, `Orders.customers`, and `Orders.items` values for the other text fields in the UI. Last but not least, you have to link the data from the index’s Fullresults custom state to the chart. In this case, you’ll create three series—Orders, Customers, and Items—and will set the series categories (X axis) from the **Index**, **Fullresults**, **each item’s Orders.orderDate.month**, **formatted as 1/21/22**, and the corresponding series data.

<div style={{ overflow: "hidden" }}>
<img style={{ display: "block", width: "400px" }} src="https://i.imgur.com/CVlO5Na.png" alt="Chart series data" />
</div>

You can debug your application at any point using the **Preview** button on the upper-right corner. This will open a new browser tab that will show the development version of the app. If all goes well, you can check each workflow, step by step.

![Bubble debug application](https://i.imgur.com/pxt9yB1.png)

You can check the properties of the controls and the results of each action defined in the workflows. If any errors occur, the right-side caution icon will turn red, making it easy to see that something’s gone wrong.

And you’re done! You can check out the inner workings of the application on [Bubble](https://bubble.io/page?id=cubebubble), and see all the code in the GitHub [repository for this project](https://github.com/nickmancol/cube_bubble.io).

## Conclusion

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 Bubble. In the process, you learned how to easily create measures, dimensions, and segments from raw data, and query your data using the Cube Developer Playground.

To learn more, visit [Cube’s site](https://cube.dev/cloud/) and sign up for a free account.

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. ✌️