The person with the login can see all the data stored in the database. This means the personal data of the patient is VISIBLE to that user.

Superset

Sciensano uses Apache Superset to provide the Data provider with a user-friendly UI for data exploration and visualization from the Local DWH database. Superset allows the user to export data into a CSV, and create their own queries, …

Prerequirements

URL: https://superset.<domain> or http://<ip>:3030 is available. If this is not the case, your IT department needs to open the port or add the domain to the DNS server.

Username/Password: Our Servicedesk will forward, via a secure link, the username and password.

Login

Superset is available on the local installation via the URL: https://superset.<domain> or via http://<ip>:3030.

Use the credentials provided by the servicedesk of Healthdata.

Home page

The first time you login to the application, an empty home folder will be shown. The more you use the application, the more tiles will be shown in your home dashoard. You see multiple subcategories like Favorite, Mine and Examples.

Favorite will show all the Dashboards, Charts or Queries you marked as a favorite.

Mine will show all the Dashboards, Charts or Queries you have created.

Examples leave it as is. You don't need to use this.

Dashboards

Dashboards are created under the dashboard menu (on the top menu). A list of shared and personal dashboard will be visible.

Default the dashboard Local DWH will be visible at first login.

Local DWH

The local DWH dashboard will provide you a dashboard with some information on how particular features are working inside Superset and an option to show registrations based on the data collection name.

Remark: if you are not able to see the Filters column, click on the "wifi" icon or arrow on the left side of your screen.

Under the Data tab you will see 3 tables that are empty. With the filter (on the left side), you need to select what you want to see. You can select more Data collection at once and click on Apply FILTERS (bottom of the page).

After applying the filter, the data is now visible.

You can easily download each table by clicking on the 3 dots next to the filter icon in table:

A zip file will be created with the data provided in the table.

Remark: only the last 10000 records will be visible in the table. Downloading a higher amount of data can be done via the SQL Lab editor.

SQL Editor

The SQL Editor allows you to run your own query on the database.

Go to SQL Lab > SQL editor

On the left side you can choose the database, the schema and the table.

Database Local DWH is the database you want to use. Use the Public schema and select one of the tables. You can choose between local_dwhmessage, local_dwhmessage_key_value and local_dwhmessage_key_value_plus.

The preview of the data is something you are not able to export. You still need to write your own query before you can export data from the database. You can limit the number of records (with a max of 100 000 records at once). After running your query, the button DOWNLOAD TO CSV will become available.

Remark: you can save your query by clicking on SAVE AS. With a proper name and description you can easily find the query back in you home or under SQL LAB > Saved Queries.

Query examples

With the following information, you will be able to link multiple tables with eachother.

local_dwhmessage_key_value column msg_document_id refer to the document_id of local_dwhmessage.

local_dwhmessage_key_value_plus column key_value_id refer to the id of local_dwhmessage_key_value.

Query 1: Get all registrations from Orthopride Knee resection from the last 15 days.

SELECT * from local_dwhmessage WHERE data_collection_name = 'OP_KNEE_RESEC' and created_on > current_date - interval '15' day;

Query 2: Get all registrations and key value from Orthopride Knee resection.

SELECT * from local_dwhmessage as ldm left join local_dwhmessage_key_value as ldmkv on ldmkv.msg_document_id = ldm.document_id WHERE data_collection_name = 'OP_KNEE_RESEC';

Query 3: Get all registrations, key value and key value plus from Orthopride Knee resection.

SELECT * from local_dwhmessage as ldm left join local_dwhmessage_key_value as ldmkv on ldmkv.msg_document_id = ldm.document_id left join local_dwhmessage_key_value_plus as ldmkvp on ldmkvp.key_value_id = ldmkv.id WHERE data_collection_name = 'OP_KNEE_RESEC';