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:

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

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

2 - Login

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

3 - Adding a database

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

Validate to add the database.

4 - Creating a project

A project will group together all our reporting tasks.

Go to Projects → New 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 Projects → Reporting → New Task, select SQL Report as the task type and then fill in the following information:

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 Projects → Reporting → Sales report -> Execute) to get the expected report:

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