Fulcrum users often have a need to visualize the data they collect in dashboards to make business decisions. Many solutions are available with Fulcrum’s REST API, such as Google Data Studio, PowerBI, and Tableau. Another tool called Cube.js recently came to my attention which allows you to build dashboards with your Fulcrum data. Cube.js is an open source analytics framework which allows you to create visualizations of the data from a local database.
Fulcrum has a tool called Fulcrum Desktop which synchronizes content from your Fulcrum account into a local database. Fulcrum Desktop is a great way to create a backup of your data and run applications and dashboards from that data. The tool also allows you to synchronize all of your account’s media into local storage or in an S3 bucket. To use Fulcrum Desktop you must have the Developer Pack enabled on your organization, giving you access to all of Fulcrum’s APIs and webhooks.
The following tutorial will show you how to set up and run Cube.js locally using a local Fulcrum Desktop database and visualize your data within it. This post assumes that you have access to Fulcrum’s API and have Fulcrum Desktop installed with your organization synced to a local Postgres database.
Installing Cube.js and Creating a Project
The first step is installing Cube.js. To do this you will run either the npm install -g cubejs-cli command or the yarn global add cubejs-cli command. After a minute or two you will have cubejs installed.
Next you will want to create the project you will be working with. Here I have chosen to call my project fulcrum-query-builder. Running this command cubejs create -d postgres fulcrum-query-builder will create a directory called fulcrum-query-builder where the cubejs program will store all of its files.
Connecting Your Project to Your Database and Starting Cube.js
The next step will be to modify the .env file that is in your fulcrum-query-builder directory to contain the correct database credentials. When connecting to a Postgres or MySQL database you will only need to update the host, database name, user and password fields.
After you have saved the .env file you can run the following command to start Cube.js, npm run dev. This will start the Cube.js server on your local machine on port 4000. From your web browser you can enter localhost:4000 and get the following screen.
Choosing a Table Schema and Creating a Graph
Within the Schema tab you will have a list of all tables and views within your database. Expand the public dropdown and choose a table view you want to work with. In my case I chose view_344_police_calls_downtown_st_pete. After you select a table you can press the blue plus button and press Generate Schema. After it processes you should get a pop up message that says Schema files successfully generated, press the Explore button to continue to the next page.
On this page you can pick and choose different measures, dimensions and filters to make your graphs out of. The first item I chose was a measure of the table we selected. I also changed the by category from day to month. This produced the following line chart showing the frequency of police calls in downtown St. Pete in a month by month basis.
You can add charts to your dashboard by clicking on the + Add to Dashboard button just above the chart that is displayed. Adding charts to your dashboard starts another Cube.js server on port 3050.
After adding that chart to my dashboard I created another chart which shows police calls by event type. I created this chart by selecting the count of the table for the measure and also a dimension of the Type of Event field.
From here you can make up your own dashboard to keep your data charts organized with. The dashboard view will keep a live view of your data as long as your Fulcrum desktop database is being updated with the new data.
Fulcrum is a data collection platform that enables organizations to reduce costs, access critical data in real time, and improve decision making at every level. With Fulcrum, you can create custom apps using our simple drag-and-drop builder to turn your paper documents into digital forms that your field teams can quickly complete on mobile devices.