Dataset

In this section, you will create a database connection and a dataset in Apache Superset.

Create Database Connection

When Superset is started, you can create a connection to the database that contains data for dashboards. In the previous steps, when you configured Superset containers in docker-compose-non-dev.yml file, you created the jmix_database service which starts the PostgreSQL. This database is used in the Jmix application, and now you will configure a connection to this database in Superset.

Click on the Database Connections item in Superset settings:

settings databsase connections

Create new database connection. In the opened dialog, select PostgreSQL type and enter connection details:

new database connection

Click to CONNECT button and then to FINISH button. Now the connection to the jmix_database service is created.

Create Dataset

In Apache Superset, a dataset refers to a collection of data that can be used for creating charts. It can be either a physical dataset, which represents a table or view in a database, or a virtual dataset, which is a query that can be saved and reused as a dataset.

In the Onboarding database, the USER_ table has a foreign key to DEPARTMENT. To visualize users data by departments, you have to use the second option - a query that will be reused as a dataset.

In the top menu, navigate to SQL and select SQL Lab. In the opened screen fill the fields as follows:

  • DATABASE: Jmix Onboarding

  • SCHEMA: public

Now you should write a query that will be used as a dataset. Since you need a dashboard to visualize employee salaries including the department of each employee, you need to select the following columns:

  • user’s full name

  • department

  • salary

So the resulting query will look like this:

SELECT CONCAT(first_name, ' ', last_name) full_name, dpr.name department_name, usr.salary
FROM public.user_ usr
INNER join department dpr on usr.department_id = dpr.id

Use INNER JOIN here to avoid users without department (admin user). If you run this query you will see the following result:

sql lab

Save the query and call it Employees' salaries.