Building a Budibase 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 using Budibase, the Cube REST API, and a data store like PostgreSQL.

The end result should look like this:

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.

Why Build with Budibase?

Budibase 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
• Appsmith
• NocoDB
• ToolJet
• Retool

Why use Cube as an Analytics API Layer?

Cube 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)
• A Cube schema
• A Budibase application

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

For this tutorial, you can use an ElephantSQL free instance, a free Cube cloud account, and a free Budibase Cloud hosted application.

If you want to self-host, this project's repository 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:

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:

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.

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.

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.

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

After you've connected your database in Cube, generate the first Cube schema for the table orders. This is a JavaScript file of the data model that will be available to query based on the data source.

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

In order to answer the business questions mentioned earlier in this tutorial, 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`
},

...

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

...

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.

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

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.

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

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.

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.

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

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

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

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

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:

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.

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:

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

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.

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

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.

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.

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:

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.

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:

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

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:

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

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 to build an analytics dashboard that aggregates data from a relational database and displays it using the low-code tool Budibase.

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 as well as the code in GitHub.

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