Your Web News in One Place

Help Webnuz

Referal links:

Sign up for GreenGeeks web hosting
February 18, 2022 03:11 pm GMT

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, its 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. Youll also use Cubes 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 finished dashboard

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. Well 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 eventfor 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: Its 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 cant 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 arent available in Bubble. The REST API can be connected to any Bubble app, and Bubbles 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.

Architecture

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 cant self-host Bubble applications, you can deploy your own Cube or PostgreSQL environment and expose it for Bubbles API connector to consume. Youll 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, youll 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:

ElephantSQL connection details

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.

Cube new deployment

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

Cube data source

Finally, youll need the data source connection detailshostname, port, username, password, and database nameall the values you previously copied from the ElephantSQL details.

Cube database details

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

Cube first schema

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.

Cube Developer Playground exploration

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

Bubble create app

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

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

Bubble install plug-in

Now to connect the Cube API. Go to the Add another API button, where youll 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 its what will let you call the query from the Bubble workflows of the application.

Bubble API connection details

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

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 Cubes CLI client tool. Remember to add a shared header, setting the Content-Type as application/json.

Once youve 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

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

Cube API URL

Cubes API reference describes this method as the way to get the data for a queryyou 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

Reduce the query to a minimal expression and set it as the API calls 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

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 theres no segment selected:

Cube advanced query

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

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

Bubble advanced mapping

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

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 youll create a new option set called segments that will have four possible options: All, Customer, Corporate, and Home Office.

Bubble option sets

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.

Bubble new attribute for option set

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.

Bubble drop-down configuration

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

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

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

Bubble full UI

Adding the Workflows to Load the Data

Its time to create the workflows that will fetch the data from Cubes 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-downs 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

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

Fullmeasures action

Now every time the page loads, the first action will be to call Cubes 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 actions step properties box, write the default value for the desired segment, Orders.All. This will cause Cubes query to return the data for all the segments.

Static segment param in action

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

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

Create new page state

This state will contain a list of the FullMeasuress 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 1s data:

Custom state value

Use the same steps to create another workflow, but use the drop-downs 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

Navigate through Index, Fullresults, then each items 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 lists Orders.count attribute.

Subsequent data source selection

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 indexs Fullresults custom state to the chart. In this case, youll create three seriesOrders, Customers, and Itemsand will set the series categories (X axis) from the Index, Fullresults, each items Orders.orderDate.month, formatted as 1/21/22, and the corresponding series data.

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

Bubble debug application

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 somethings gone wrong.

And youre 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 Cubes 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.


Original Link: https://dev.to/cubejs/building-a-bubble-dashboard-with-cube-4mop

Share this article:    Share on Facebook
View Full Article

Dev To

An online community for sharing and discovering great ideas, having debates, and making friends

More About this Source Visit Dev To