DEV Community

Cover image for Deploy a lightweight BI solution with your first dashboard in 5 steps
Jean-Yves Pellé
Jean-Yves Pellé

Posted on • Originally published at pelle.link

Deploy a lightweight BI solution with your first dashboard in 5 steps

This article is aimed at those who know SQL and want to quickly set up a minimalist reporting solution (without going through the heavy artillery of PowerBI, Tableau, Looker Studio, etc.).

Let's start with a database (of the Mysql, Mariadb or Postgresql type) including a monthly_sales table containing monthly sales amounts:

month sales_amount_eur
2024-01-01 8730
2024-02-01 9620
2024-03-01 4210
2024-04-01 6732
2024-05-01 9921
2024-06-01 8176
2024-07-01 7623

Our aim is to:

  • Generate a report showing this information in a graph.
  • Refresh it every 1st of the month.

1 - Installation

Let's start by installing CTFreak.

Under Ubuntu, open your terminal and run:

sudo snap install ctfreak
Enter fullscreen mode Exit fullscreen mode

For alternative installations (Docker, Windows, Freebsd, ...), see here.

2 - Login

Go to http://localhost:6700 and log in via admin / ctfreak.

Login

3 - Adding a database

Go to DatabasesNew Database, select its type (e.g. Postgresql), then fill in its connection parameters:

Add database

Validate to add the database.

4 - Creating a project

A project will group together all our reporting tasks.

Go to ProjectsNew Project:

Create project

Validate to create the project.

5 - Creating a SQL Report task

Our report will be generated via a SQL Report task associated with our project.

To do this, go to ProjectsReportingNew Task, select SQL Report as the task type and then fill in the following information:

Create SQL Report task

In the SQL query, the suffix _c_month allows you to indicate that date values should be formatted as months in the graph.

Validate this form to create a task which, based on an SQL query, will generate a monthly sales report every 1st of the month at 9 a.m., with a 2 year (730 days) retention period for the reports generated.

Let's execute this task right now (via ProjectsReportingSales report -> Execute) to get the expected report:

Execute task

Conclusion

As you can see, this report meets our initial requirements.

Feel free to use CTFreak's other features to make improvements, such as:

  • Add more charts.
  • Add task parameters to define the sales period to be taken into account.
  • Add read-only access for a given user (via the Viewer role).
  • Send an e-mail each time a report is generated, with a link to open it (via a Notifier).
  • Synchronize the generation of multiple reports (via Workflow tasks).

Top comments (0)