IT administrator: An IT administrator has the highest level of all roles and permissions and can:

1. log in using Active Director;
2. grant access to Local Study Lead, Local Study Associate and Local Study Support;
3. select and access all projects;
4. create, find, update, delete, send (to healthdata.be, MyCareNet and other destinations) and correct a record using the form.io component;
5. create, update, send and correct a record using the API data collection;
6. create, update, send and correct a record using CSV upload;
7. create and send a MyCareNet record using MyCareNet XML;
8. view all records for all projects;
9. harvest all records for all projects from the local DWH using the PostgreSQL database.

HD4DP v2 Local is an application installed on the infrastructure of the Health Care Organisation participating in research projects facilitated by healthdata.be.

The installation of HD4DP v2 Local is executed by the DevOps team of healthdata.be.

Server Installation and Configuration

Installing and configuring the server requires the following actions:

• install and configure the server as described in the article "HD4DP 2.0 Infrastructure instructions";
  • complete and return to support.healthdata@sciensano.be the server configuration file "HD4DP v2 Infrastructure Sheet".

The HD4DP v2 application is more modular and will support scaling up to meet the requirements of the various data collection projects we facilitate. It will offer several micro-services that will run concurrently on the same machine.

The server should therefore require more resources than the one currently hosting the HD4DP 1.0 application. Furthermore, the resources allocated should be increased.  It is therefore on the one hand imperative to use virtualization for the creation of the machine. On the other hand. It is also imperative to store files and make regular backups to a file server.

Below we take up our three categories of organizations sending data to healthdata.be and the resources we recommend allocating to their virtual machine:

• "Small": Small data provider;
• "Medium": Medium data provider;
• "Large": Big data provider.

Finally, we also offer the possibility for each hospital to have an integration server and a production server. Healthdata.be will deploy the new release of the application on the integration server. This will allow you to accept or decline the promotion of a new release of the HD4DP 2.0 application to the production server. This option is highly recommended, but not mandatory.

Therefore, could you answer the question: Do you want to first deploy HD4DP on an integration server? Yes/No. If Yes, Could you provide a server whose label used for specifications is "Small" (following the instructions in section 1 of this mail), that is:

• Processors number: 1
• Physical cores/Processor: 8
• RAM memory: 16 Go
• Disk space: 100 Go
• Network Station Mount with Space for Backups
• Operating System: Ubuntu LTS v22.04
• Virtualization

Server installation timing

In order to establish the deployment schedule for the HD4DP 2.0 application within your organization, we would like to know when the server could be installed and configured. To this end, could you give us the 2 dates relating to the installation of the server:

• Starting date;
• Finalization date.

Based on these dates, an employee of healthdata.be will regularly monitor the operations linked to the installation of the server.

For any request for information on installing the HD4DP 2.0 server, please send an email to hd-architecture-20@sciensano.be.

General description of S2S API

• Go here for a general description of the S2S API data submission service.

Training

• Go here to watch the S2S API training organized by healthdata.be.

API endpoint description

• Go here for an overview of API endpoints and the relevant information.

Data submission procedure in Architecture 2 using S2S API

1. Requirements at Data Provider's side

In order to be able to communicate with the healthdata.be S2S API, the IT services of the healthcare organizations must setup their systems as follows:

• Ensure to have the credentials available for the endpoint/URL for which authorization is necessary. If this is not the case, please contact our service portal: https://sciensano.service-now.com/sp.

• Ensure to have the end-to-end API process in place in order to submit data registrations in a fully automated manner.

• Ensure capability of submitted data retrieval in the local database.

2. Preparing the JSON file

• To send data to healthdata.be by S2S API, the file must be in a .json file format.

• Extract the requested data from the electronic patient files and/or other local databases.

• Map the data according to the fields and formats as described in the DCD specifications of this section and the general code lists.
• Go here for additional information on the codes and the formats used for the Author group, SSIN code, Status, "Date" - "Date:Time", Repeatable fields and Multiple choice.

Note: The example files below are only provided as a guideline and do not contain real life data.

Hand Hygiene - PRE CAMPAIGN

• Example
  • HD_DCD_submjson_HDBP0025_HH_HandHygiene_PRE_Campaign_v2.json

Hand Hygiene - POST CAMPAIGN

• Example
  • HD_DCD_submjson_HDBP0025_HH_HandHygiene_POST_Campaign_v2.json

Hand Hygiene - IN and OUT CAMPAIGN

• Example
  • HD_DCD_submjson_HDBP0025_HH_HandHygiene_In_And_Out_Campaign_v2.json

Note: Below documentation contains generic example screens with the sole purpose to demonstrate how the process looks like regardless of the registry.

3. Uploading the JSON file

• The IT department of the Data Provider implements a data transmission system (e.g. Postman) to send the API queries by means of a json file. The correct endpoints for uploading of the json file can be found here.

• The URL that needs to be entered after POST (selected method for uploading) should have a syntax similar to the one below:

POST {{baseURL}}/api/dcd/payload/submit?organization-id={organizationId}&dcd-id={dcdId}&dcd-version-id={dcdVersionId}&data-src-type={dataSrcType}

• Whereby:
  • {organizationId} needs to be replaced by the id of your organization; go here for more information
  • {dcdId} needs to be replaced by a register-specific numerical value; go here for more information
    
  • <optional> {dcdVersionId} needs to be replaced by the intended version number; if not provided, the latest version of the dcd specifications is assumed; go here for more information
  • <optional> {dataSrcType} needs to be replaced by the data source type. Permitted values are API or CSV; go here for more information
    
  • a GET method URL should not contain /submit/ in the endpoint or path
  • multiple query parameters need to be connected by an ampersand (&) (see example above)

• In the Authorization tab, select “Basic Auth” in the “Auth Type” drop-down list. Subsequently, fill out the API credentials in the username and password fields.

• In Body tab, select the “raw” option from the top line and paste the content of the JSON file in the box.

• Once the request settings are completed, press the Send button to submit the records.
• After succesful submission of the data, the “Status: 202 Accepted” code will appear below the box where the data was pasted. The bottom box should return a message about the records uploaded or an error message if any.

• A 401 Unauthorized message instead indicates missing or incorrect credentials. Request the correct credentials as described above in the Requirements at Data Provider's side.

• A 400 Bad request might refer to incorrect syntax of the API query. Check the query parameters and ensure to connect multiple parameters with the ampersand (&) character.

4. Validating the JSON Upload

4.1 Validation of the S2S API Upload via the response:

Verify in the same way the request was sent, that the returned response is containing a valid Business key.

If a valid Business key has been provided, the registration upload via System 2 System API was succesful.

4.2 Validation of the S2S API via HD4DP 2.0:

Step 1: Open the HD4DP 2.0 application.

Step 2: Select the organization in the dropdown list and click on Volgende (Next).

Step 3: Fill in the username and password, that has been provided by your IT Department or Healthdata team, and click on Log in to access the HD4DP v2.0 application.

Step 4: Navigate in the menu on the left-hand side panel to the desired study program:

Step 5: Check that the uploaded registration(s) is/are displayed in the overview table:

This documentation is being updated regularly. We try to provide as correct, complete and clear as possible information on these pages. Nevertheless, if you see anything in the documentation that is not correct, does not match your experience or requires further clarification, please create a support ticket via our portal (https://healthdatabe.atlassian.net/servicedesk/customer/portals) or send us an e-mail via support.healthdata@sciensano.be to report this documentation issue. Please, do not forget to mention the URL or web address of the page with the documentation issue. We will then adjust the documentation as soon as possible. Thank you!

General description of CSV Upload

• Go here for a general description of the data submission service via CSV Upload.

Training

• Go here to watch the CSV Upload training organized by healthdata.be.

Data submission procedure in Architecture 2 using CSV Upload

1. Preparing the csv file

• Extract the requested data from the electronic patient files and/or other local databases.
• Map the data according to the fields and formats as described in the DCD specifications of this section and the general code lists.
• Go here for additional information on the codes and the formats used for the Author group, NISS code, Status, "Date" - "Date:Time", Repeatable fields and Multiple choice.
• Make sure the name of the csv test file(s) has the correct format as follows:
  HD_DCD_submcsv_HDBPnumber_HDBPabbreviation_versionnumber_versionreleasedate

• An alternative method for migrated projects would be to export the csv file from Architecture 1 and to manually add the additional fields that are required in Architecture 2. (see Additional fields below, if applicable)

Hand Hygiene - PRE CAMPAIGN

• Example
  • HD_DCD_submcsv_HDBP0025_HH_HandHygiene-Pre-Campaing_01_14112022.csv

Hand Hygiene - POST CAMPAIGN

• Example
  • HD_DCD_submcsv_HDBP0025_HH_HandHygiene-Post-Campaing_01_18102022.csv

Hand Hygiene - IN and OUT CAMPAIGN

• Example
  • HD_DCD_submcsv_HDBP0025_HH_HandHygiene-Inside-Outside-Campaing_01_18102022.csv

Disclaimer: The example files above are only provided as a guideline and do not contain real life data.

2. Uploading the csv file

Step 1: Open the sftp tool like WinSCP

Step 2: Get the credentials (Host name, Port number, User name and Password) from the IT department of the Data Provider, to log on to the sftp server located on the Data Provider side. In case the credenials are unavailable we advise you to request your credentials through our service portal at https://sciensano.service-now.com/sp via the Request something tab and subsequently the Request for Information box.

Step 3: Fill in the credentials into the Login screen and click on Login to be able to access the different upload folders:

Note: a warning might be given, just click on Update

In the CSV Upload folder structure that is displayed on the right-hand side panel, you will notice draftcsv folders and submcsv folders for the diverse HD business projects.

Historically, a directly submitted csv file would end up in the submitted folder (submcsv), whereas a csv file featuring ‘draft’ in the status field would have ended up in the draft folder (draftcsv). At this stage there is no real distinction between both folders, so you can save the csv file in either of them.

Attention: If you wish to create a draft to work on at a later stage, don’t forget to add ‘draft’ in the status column of the csv file.

Below documentation contains generic example screens with the sole purpose to demonstrate how the process looks like. In the subsequent screens the desired Hand Hygiene register (PRE-CAMPAIGN, POST-CAMPAIGN, IN and OUT CAMPAIGN) needs to be selected.

Step 4: Select the desired Hand Hygiene folder and open it by double-clicking on it:

Step 5: Now go to the folder on the left-hand side panel where the csv file to be uploaded is located:

Step 6: Drag the csv file to be uploaded from the left-hand side panel into the folder on the right-hand side panel:

Step 7: Wait until the polling system of the CSV Uploader has picked up the CSV file and has processed it.
Once the csv file has been processed it will disappear from the folder, when the page is manually refreshed!

3. Validate csv upload

Once the csv file has been processed, 3 folders will be created (if they haven't been created already) in the DCD folder:

• ARCHIVE (after a csv file has been processed, the original csv file will be saved in this folder)
• RESULT (when the csv file is placed in this folder, it means that the csv file has been processed, a file will be created (or appended, if the file already existed) with the result of the upload of the csv file).
  All the errors that are described in this file are business related, which means that they are technically correct, but in violation with the business rules or contain wrong values for that field.
• ERROR (when the csv file is placed in this folder, it means that a technical error has occurred like the csv file contained erroneous formatting. The csv file won't get processed and an error file will be created with the errors and reason why the csv file couldn't be processed)

3.1 Validation of the csv upload via sftp tool:

Step 1: Double-click on the ERROR folder to open it, click on the refresh button and verify that there is no error file present.

Step 2: Return to the DCD folder. Now double-click on the RESULT folder to open it, click on the refresh button and verify that the result file is present.

Step 3: Double-click on the result file to open it.

Step 4: If there are multiple records in the result file, scroll to the entry of the current csv upload by looking at the upload date (Started at dd/mm/yyyy hh:mm).
Verify the result file that the upload was successful by searching for the word SUCCESS and having a look at the Status. This Status must contain: Success;Success Count:1;Error Count:0

3.2 Validation of the csv upload via HD4DP 2.0:

Step 1: Open the HD4DP 2.0 web application.

Step 2: Select the organization in the dropdown list and click on Volgende (Next)

Step 3: Fill in the username and password, that has been provided by your IT Department or Healthdata team, and click on Log in to access the HD4DP v2.0 application.

Step 4: Navigate in the menu on the left-hand side panel to the desired study program:

Step 5: Check that the uploaded registration(s) is/are displayed in the overview table.

This documentation is being updated regularly. We try to provide as correct, complete and clear as possible information on these pages. Nevertheless, if you see anything in the documentation that is not correct, does not match your experience or requires further clarification, please create a support ticket via our portal (https://healthdatabe.atlassian.net/servicedesk/customer/portals) or send us an e-mail via support.healthdata@sciensano.be to report this documentation issue. Please, do not forget to mention the URL or web address of the page with the documentation issue. We will then adjust the documentation as soon as possible. Thank you!

Introduction

To support the development and validation of data transfers using S2S API or CSV upload, a central Online Acceptance (OACC) environment has been made available for the IT services or IT partners of data providers. This environment is meant to replace the locally installed acceptance environments at the side of the data providers. In the OACC Environment you can test and validate the three types of data transfer:

• via manual input in the study form
• via SFTP
• via API

Visit the S2S API and CSV Upload documentation for a general description of the respective data transfer methods.

In order to keep the OACC environment light, it is rebuilt once a week (each Saturday). The data are stored locally and will not be sent to healthdata.be infrastructure. The testing is limited to the upload to the HD4DP v2 application.

Additional field information about the codes and the formats used for the Author group, NISS code, status, Country and Postal Code, "Date" - "Date:Time", Repeatable fields and Multiple choice fields is to be found here.

Application URLs and port

• Front-End Application URL: https://hd4dp.acceptance.healthdata.be
• SFTP host name: sftp.acceptance.healthdata.be and port 2220
• S2S API: https://hd4dp.acceptance.healthdata.be/proxy/api/dcd/payload/submit?organization-id=00&dcd-id=00&version=1

Credentials for accessing the Online Acceptance (OACC) environment

Upon requesting credentials to access the OACC environment, you will receive 3 different types of credentials:
⦁ credentials to log in to the front end of the online acceptance environment
⦁ credentials to use the API (-> authorization tab in Postman)
⦁ credentials for the SFTP server you use to test the CSV Upload

If your organization is not available for selection in the drop-down list, we advise you to request your credentials through our service portal at https://sciensano.service-now.com/sp via the Request something tab and subsequently the Request for Information box.

Navigate to the Online Acceptance (OACC) environment

The OACC environment can be found on https://hd4dp.acceptance.healthdata.be, which is a publically accessible URL.

On the homepage you are requested to select your organization from the drop-down list in order to proceed.

Log in with the credentials you have received upon request. The username is test@sciensano.be for all users.

Once logged in, the layout looks very familiar: to the left you will find the navigation panel with all running projects . Note that the list of projects featuring in our Online Acceptance Environment is not filtered out for the organization you have selected.

The data transfer methods

As mentioned above, the Online Acceptance Environment enables the testing of the uploads for the following three types of data transfer. They are described below according to the timeline of developement:

Manual input in the study form

This data transfer method is the form entry, carried out manually. For this you can use a common browser such as Google Chrome. This method can also be used to validate data sent via S2S API or CSV Upload.

Data transfer via an SFTP client

The csv upload is the second type of data transfer. The data are transferred to an SFTP server and subsequently picked up by the healthdata.be system. This transfer method requires the use of an SFTP client, such as WinSCP (freely available).

The Login window will look as follows:

Herein, you enter Host name (sftp.acceptance.healthdata.be) and Port number (2220).

Credentials for the SFTP folder are shared together with the Front-end and API credentials as described in the section “Navigate to and access the Online Acceptance Environment”.

When logging in to WinSCP, you will need to navigate to the correct csv folder : csv/<project>/<dcd>. Here you need to drag and drop the csv you want to upload from the left panel to the right panel. The CSV file will now be picked up by the polling system of the CSV Uploader, which checks for new CSV files every minute.

The folders Archive and Result will only be created after the first CSV file has been uploaded for testing.

The Result folder shows a log file containing CSV Upload reports. The status Error Count shows technical errors such as incorrect name, code …

CSV files that were uploaded in Architecure 1 can be reused in Architecture 2. Prerequisite for this is the addition of necessary fields that are typical for Architecture 2, e.g.:

• Author Group (TX_AUTHOR_GR) with the value "Test group"
• Author (TX_AUTHOR) with the value "test@sciensano.be"
• Coauthor (TX_COAUTHOR) with the value "test@sciensano.be"

To further facilitate the process of reusing CSV files a mapping table with old and new CSV names is provided.

Next to adding fields, you can also leave out fields, which is indicated in the log file reports as a warning.

Once again the download process can be checked in the study forms on the front-end interface. You want to refresh the window to update to the newest status.

Data transfer via an API platform

The data are extracted directly from the EPD systems and sent to HD4DP v2.0 local using S2S API before they are sent to healthdata.be. This third data transfer method requires the use of an API development platform, such as Postman (freely available).

The endpoint (URL) to send your payload to for testing is

https://hd4dp.acceptance.healthdata.be/proxy/api/dcd/payload/submit

This endpoint needs to be completed with some parameters, such as the ID of an organization, the ID of a dcd, the version number:

https://hd4dp.acceptance.healthdata.be/proxy/api/dcd/payload/submit?organization-id=6&dcd-id=18&Version=1

Click on the Send button to post the payload. A succesful submission is indicated with the status message “202 Accepted”. This can also be checked visually in the front end of the Online Acceptance Environment.

The field Data source in the top selection bar indicates whether the data were transferred via S2S API, CSV Upload or manually with HD4DP.

In the production environment, records that are sent via the API platform, go directly to the healthdata.be infrastructure. These records will receive the status “Submitted” in the Progress field.

Next to posting payloads (POST) you can also retrieve information (GET). Examples of such "calls":

• The call https://hd4dp.acceptance.healthdata.be/proxy/api/organization (see below) will return an organization id:

• The call https://hd4dp.acceptance.healthdata.be/proxy/api/dcd/menu/structure?organization-id=6 (see below) will return the menu structure with all projects your organization is registered for.

Go here for a description of the API data transfer method. For additional detailed information about the codes and the formats used for the Author group, NISS code, status, Postal Code, "Date" - "Date:Time", repeatable fields and multiple choice fields please go here.

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

To make sure the data is persistent on both healthdata.be's and data provider's side the back-up plan was confirmed and developed and tested as well, the postgres and the MongoDb will be taken the whole back-up on the daily basis, this will be taken care of the timer-service that has been recently introduced.

Last 30 days back-up will be kept in the same server share or in the local path, to enable the backup we have introduced the new ansible role called “mongo-postgres-backup”, and to invoke the backup option the following variables needs to be defined on the “Host_var” file on hd-inventory to the specific deployment.

Enabling the backup part on the target server: As soon as the ansible role added to the respective playbook and the above backup type added to the hd-inventory host vars to the main/master branch, this can be enabled automatically on the hd-updater daily run or manually triggering the hd-updater on the target side server.

• DP without external share
• DP without external share and VM back-up
• DP with external share (samba)
• DP provides external share (nfs)

• When DP do not have the external share,

then it’s better to keep the backup of the postgres-mongodb dumps, on different directory on the same server. To enable this backup type, we must add the below variable on to the respective host-vars in the hd-inventory.

backup_mount_type: "VmBackup"

----Default backup path is "/hd4dp-backup"

• When DP do not have both external share and Vm backup,

then it’s better to keep backup of the postgres-mongodb dumps, on different directory on the same server. To enable this backup type, we have to add the below variable on to the respective host-vars in the hd-inventory.

backup_mount_type : "local"

local_mount_path : /tmpBackup

• When DP have External share (samba),

then the below variable needs to be added to the respective hd-inventory host_var file.

backup_mount_type: "samba"

backup_mount_type: “samba”

samba_bkup_username: “user_name”

samba_bkup_domain: “example.com”

• When DP provides External share (nfs),

then the below variable needs to be added to the respective hd-inventory host_var file.

backup_mount_type: "nfs"

nfs_bkup_src: "0.0.0.0:/nfs/mount-share"

----Default backup path is "/hd4dp-backup"

Restore:

AS of the agreed approach is to restore the backup-dump manually by using the retention-script (restore_mongodb.sh & restore_postgresdb.sh) it can be found on the target server “/opt/hd-all/hd-backup”, by default it will restore the latest backup but if we wish to restore the particular date back this can be given as a input parameter refer below.

Restore Script Path on the target server:

/opt/hd-all/hd-backup

Mongo Restore Input param:

"Provide bkp file with full path name for restore e.g. ${local_mount_path}/hd4dp-backup/mongodbBackup/mongo-20220510_2004.tar.gz" & ) to the script.

Mongo restore script:

restore_mongodb.sh

Postrges restore input Param:

"Provide bkp file with fullpath name for pg_wal restore e.g. ${local_mount_path}/hd4dp-backup/postgresBackup/pg_wal_latest.tar.gz"

“Provide bkp file with fullpath name for pg_wal restore e.g. {local_mount_path}/hd4dp-backup/postgresBackup/base_latest.tar.gz

Postgres restore script:

restore_postgresdb.sh

Note:

The postgres backup service timer will be scheduled by 15:00 aprox, the Mongodb backup service timer will be scheduled by 22:00, backup retention will perform few basic checks ex it will check if the space is sufficient to store the current day backup.