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:
Create new database connection. In the opened dialog, select PostgreSQL
type and enter connection details:
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:
Save the query and call it Employees' salaries.