Building a Bubble Dashboard with Cube

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!

Bubble 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, Cube, and PostgreSQL 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 or see a video of what it will look like in action.

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 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 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 is used in the tutorial, but you can use MySQL, MongoDB, or any other database supported by Cube), a Cube schema, and a Bubble application. The following diagram shows you the expected interactions between the components.

For this tutorial, you can use a free instance of ElephantSQL, a free Cube Cloud account, and a free Bubble hosted application. The dashboard will use data from the US Superstore data set available on Kaggle, 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 and containers in order to properly access and connect each component.

Just follow the Cube Docker Compose installation instructions or use the docker-compose.yaml file from the repository for this project 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:

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 is a great open source option) with the details from the ElephantSQL page, which will look like this:

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

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.

Creating a Cube Deployment

Once you have the data loaded, create a new deployment in Cube 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.

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.

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.

Once you’ve connected your database in Cube, you can generate the first Cube schema 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.

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, 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, 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 to create some exploratory queries, like the number of rows in each state dimension.

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:

    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 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 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 section to the schema.

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.

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.

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.

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.

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:

Notice the default header included “Authorization”. This contains the value of the JSON web token copied from Cube. You can secure your API access by generating specific tokens for each application using Cube’s CLI client 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.

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’s API reference 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.

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.

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:

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:

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

{ "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.

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.

To fill the options of the drop-down with (key,value) tuples, you need to create an option set. 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.

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.

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

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.

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.

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

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:

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.

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.

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.

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

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”:

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.

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.

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.

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.

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, and see all the code in the GitHub repository for this project.

Conclusion

In this tutorial, you learned how to leverage the power of Cube 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 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!

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