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.
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:
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:
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:
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:
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).
The model that you've created includes three main sections:
- It defines the raw data that will be used as a source with a SQL query (all the rows from the Orders table).
- 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).
- 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:
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_idcolumn. 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:
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
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:
Execute the query by clicking Run Query and check the result:
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:
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:
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
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.
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:
- Items columns
The result of the transformation is a more readable object you can use along with the components of the user interface.
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:
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.
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.
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 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.
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. ✌️