Get BSACC-ME data from the local datawarehouse (LocalDWH) of HD4DP v2

Dernière mise à jour: 2023-03-13 06:29

Warning

The person with the login for the local database of "HD4DP v2 local" has access to all the data stored in the database. This means that the personal data of the patients will be VISIBLE to that user.

Requirements

URL Local DWH Database: postgresql://<server_ip>:5432/localdwh. If this is not the case, the IT department hosting HD4DP v2.0 needs to open the port and allow traffic to this port.

URL NIPPIN Database: postgresql://<server_ip>:5432/nippin

Username/Password: The service desk of healthdata.be will forward, via a secure link, the username and password.

Client: Download one of the clients that support PostgreSQL . A list is available here.

IP network/subnet: Provide us with the IP network/subnet from where you will contact the database. The database only accepts incoming traffic of known IP networks/subnets. example: 0.0.0.0/32

Granted privileges

database	user	privileges
localdwh	dpuser	CONNECT/local_dwhmessage:SELECT/local_dwhmessage_key_value:SELECT/local_dwhmessage_key_value_plus:SELECT
nippin	dpuser	CONNECT/nippin_message:SELECT/nippin_cleanup:SELECT

Privileges table

Query 1: Get all registrations from BSACC-ME from the last 15 days.

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

Query 2: Get all registrations and key value from BSACC-ME.

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 = '******';

Query 3: Get all registrations, key value and key value plus from BSACC-ME 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 = '******';