Reports & Dashboards

fastCode reporting add-on uses an open source reporting and analytics frameworks, Cube.js (https://cube.dev). Cube.js is a relational OLAP system and hence uses the concepts of measures and dimensions. This add-on provides the ability to business users to create ad-hoc reports and add them to dashboards.

In order to make the reporting functionality available to application end-users, the Cube.js back-end server needs to be started. To start the server, from the front-end client root directory, perform the following two steps:

1. cd server
2. node index.js

Setting up the Cube.js schema

The first step in enabling end-users to build reports and dashboards is to create cubes, each of which represents a table in the application database. To generate the cubes, from the generated application UI, click on Tools/Reporting/Generate Schema. The screenshot below shows the result

The application's list of tables can be viewed from the Tables List tab. Select all the tables and click on "Generate Schema" button. The cube files are generated and displayed under the Files tab.

Each cube file generated has dimensions, measures, joins, and other elements. More information about the contents of a cube can be obtained from this page. You can view and update the cube file by selecting a cube file in the Files tab, making updates, and then saving the file.

Note: The cube.js schema generator is not yet a mature enough component, although the cube.js team is actively working on making it more robust. Therefore, the schema files you generate from the UI may not work as expected. In such cases, you need to manually make changes to these schema files to enable your application end-users to create reports and dashboards.

Luckily, this needs to be done only once. Therefore, as a developer, you need to be familiar with Cube.js schemas. You can get more information from this Cube.js website page.

Creating reports

In order to create a report, click on Tools/Ad-hoc Reports/My Reports.

Click on the Add button to add a report. We now have to select measures and dimensions. The drop-down for measures and dimensions are automatically populated from the cube files we generated in the previous step. The time dimension is always included.

The sample database used in this example is for a Timesheet application. Below, we created a report that displays the number of tasks per project.

The report is generated and displayed. To save the report, click on the Save Report button in the chart and provide the information.

The reporting front-end generates a query, expressed via dimensions, measures, and filters. You can view the query that is generated by clicking on the JSON Query button. The reporting back-end uses the cube files (schema) to generate a SQL query, which is executed by your application database. The SQL query generated can be seen by clicking the SQL Query button. The result from the reporting back-end is then sent back to the reporting front-end.

Once you save the report and click on the Tools/Ad-hoc Reports/My Reports link on the left nav-bar, you will be taken to the reports page. If you select the report you just generated and saved, you will see a page similar to the one below:

On the top of the chart, you see two buttons: Running and Published.

The running button tracks and displays the current state of the report. The published button tracks and displays the last published state of the report. For example, if we publish the current report using the menu (shown in the screenshot below) to the right of the report name, then change the type of chart we use for the report, the running report will display the new chart, whereas the published report displays the old chart.

From the report menu, we can also add the report to a dashboard, edit a report, refresh the report to get the latest data from the application database and delete the report.

Creating dashboards

From the report's menu, we can add the report to either an existing dashboard or a new dashboard.

Similar to reports, dashboards also keep track of the Running and Published states. From the Dashboard menu, we can preview, save, publish, and delete the dashboard.

Cube.js Caching Mechanisms

Cube.js provides two different caching mechanisms to improve the performance of report queries.

The first mechanism, either an in-memory cache or a Redis cache, acts as a buffer for your database when there's a burst of requests hitting the same data from multiple concurrent users. The in-memory cache is turned on by default in development mode.

The second level of caching is called pre-aggregations, and requires explicit configuration to activate. The pre-aggregation engine builds a layer of aggregated data in your database during runtime and maintains it to be up-to-date. Pre-aggregations are materialized query results persisted as tables. In order to start using pre-aggregations, Cube.js should have write access to the stb_pre_aggregations schema where pre-aggregation tables will be stored.

You need to manually create the pre_aggregations schema in the same application database that has been used during application generation/updates. Once you include pre-aggregations in your Cube.js schema files, Cube.js will automatically create a table under the pre_aggregations schema for each pre-aggregation and store the materialized query results of pre-aggregations in these tables.

For more detailed information on Cube.js caching, please refer to this webpage.