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

Warning

The person with the login for the local datawarehouse 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: postgresql://<server_ip>:5432/localdwh. If this is not the case, the IT department hosting HD4DP v2 needs to open the port and allow traffic to this port.

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.

Query examples

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

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