Skip to content

Healthy Athlete System (HAS)

has

Contact

  • Product Owner: Brittany Routh
  • Technology: Abir Salah
  • Data: John Hanley, Karly Jerman, Jackie Palmer, Jacob Neely
  • Vendor: VecnaCares

Description

The Healthy Athletes System (HAS) enables the electronic capture of screening data across the Healthy Athletes disciplines during Healthy Athlete events.

HAS is built on top of OpenMRS. OpenMRS is an open source program for the electronic capture of health information.

Our vendor that built this platform is VecnaCares. They currently maintain our platform, servers, and the Vecna data warehouse.

For access and more information, please contact Abir Salah.

Application

Main Site Training Site

VecnaCares

Our vendor, VecnaCares is a company that we partner with that handles the HAS forms and data. However, the data is in a difficult to use format, so we bring it into the data warehouse in our HAS pipeline.

Contact

  • Mohit Kulkarni: mohit.kulkarni@vecna.com. Mohit is the software engineer we work with. He is knowledgeable about the data and process. If there is a data error or change, this is who to talk to. Also to get access to the warehouse, Mohit will whitelist your IP address.

Documentation

VecnaCares developed a product documentation site for HAS. Please click below to access the gitbook.

Vecna Documentation

Access to VecnaCares backend

The database is Postgres SQL. You access the data using DataGrip or through the HAS data pipeline. Follow the below directions to get access to the Vecna data warehouse.

  1. Message Mohit Kulkarni mohit.kulkarni@vecna.com with your IP address.
  2. Find the document with credentials for Vecna located at soda_team_documents -> HAS -> power bi desktop setup.
  3. Use the following information to access the database.
Input Response
Server so-dw.vecnacares.org
Port 1433
Database data_warehouse
User specialolympics
Password See document above

How we use data from VecnaCares

The main use of the Vecna data is in the HAS pipeline. The queries that extract this data are located here: GitHub/soi-dwh/soidatawarehouse/soload/soload/sql/extract_openmrs.py

The main tables we utilize are the migration views (ex: special_smiles_migration_view). However, strong minds does not have a migration view and instead we use its report view (strong_minds_report_view). The migration views will have many of the same columns that we have in the data warehouse.

The database (data_warehouse@so-dw.vecnacares.org -> data_warehouse -> dw) has 5 schemas: tables, materialized views, views, routines and sequences. However, we only look at a small number of these tables.

You can ignore mostly everything else except when you need to debug something. To debug something, you want to view the DDL to be able to understand what tables feed into that view. On DataGrip, go to the table on the left hand side (for example, health_promotion_migration_view). Right click and select “Go to DDL”. This will show you how that view is created. From there, you can see the tables that create the view, which is helpful to understand where a column comes from.

Example

Vecna uses postgres sql and our warehouse uses azure sql server, so the SQL syntax is slightly different when querying vecna directly versus our warehouse. To get the columns and their data types for the vecna openmrs data (special smiles example):

SELECT
    attr.attnum,
    ns.nspname AS schema_name,
    cls.relname AS table_name,
    attr.attname AS column_name,
    tp.typname AS datatype
FROM pg_catalog.pg_attribute AS attr
JOIN pg_catalog.pg_class AS cls ON cls.oid = attr.attrelid
JOIN pg_catalog.pg_namespace AS ns ON ns.oid = cls.relnamespace
JOIN pg_catalog.pg_type AS tp ON tp.oid = attr.atttypid
WHERE
    ns.nspname = 'dw'
    AND cls.relname = 'special_smiles_migration_view'
    AND attr.attnum >= 1
ORDER BY
    attr.attnum

Resources

For general information on HAS please see the below resources:

  • SOI Resources Page
  • SOI Data Warehouse Schema
  • Dropbox: Please request access from Abir to "#Health Technology" for HAS documents in general
  • OneDrive: Please request access from Jake to "SODA_team_documents/HAS" for HAS data information
  • OneDrive: Please request access from Jake to "SODA_team_documents/HAS_Metadata" for HAS data information

Using HAS

Please view our YouTube Playlist for videos on how to use HAS:

YouTube Playlist

Link: https://www.youtube.com/playlist?list=PLrBf35xtHQUqmnR_-vu-_ND4Yu3SJJh7y

Requesting an event creation

Programs will request to create an event in HAS prior to their Health Athlete event. The below diagram demonstrates the workflow from request to confirmation.

Pre-populating: Submitting athlete registration

We encourage programs to submit a list of athletes that will be attending the Healthy Athlete event. The below diagram demonstrates the workflow from request to confirmation.

Pre-populating Athletes

User accounts for data entry

Programs may request for user accounts to HAS for entering data depending on their role. The below diagram demonstrates the workflow from request to confirmation.

Tablet Loaner Program

Programs may request for tablets to be used at their Healthy Athlete events. Please contact Abir for more information. The below diagram demonstrates the workflow from request to item return.

Data Requests

Past Data Requests

Internal:

Summary Statistics:

  1. Program or Regional Health Manager contacted the Research and Evaluation team to Research and Evaluation to pull summary statistics for an event.
  2. A master Excel file of all HAS events was kept and then rows were manually counted to fill out a word document with the summary statistics.
  3. Summary statistics were then emailed to the program or Regional Health Manager.

Row Level Data:

  1. Program or Regional Health Manager contacted the Research and Evaluation team to pull row level data for an event.
  2. Rows of data were manually pulled from a master Excel file that contained all the events.
  3. Row level data was then shared with the program or Regional Health Manager.

External:

Row Level Data:

  1. Researchers external to Special Olympics (usually an academic researcher) filled out a form (https://resources.specialolympics.org/health/requesting-special-olympics-data) and sent it to Research and Evaluation.
  2. The form was manually shared via email internally for approval by the various stakeholders.
  3. If approved, the row level data was manually pulled from an Excel file and shared with researchers via DropBox.
  4. Before a scientific manuscript could be submitted, it needed to be sent to SOI for review to ensure the findings were reported accurately.
  5. These data requests needed to be renewed on an annual basis if the researcher wished to continue using the data.
  6. At the end of the data use agreement, the external researchers sign the original form saying that they have destroyed the row level data.

Present Data Requests

Internal:

Summary Statistics:

  1. Program or Regional Health Manager contacts Research and evaluation or healthdata@spcialolympics.org to pull summary statistics from an event.
  2. Summary statistics are pulled from the HAS Power BI report and downloaded into an Excel file.
  3. The Excel file is then emailed to the program or Regional Health Manager.

Row Level Data:

  1. Program or Regional Health Manager contacts Research and evaluation or healthdata@spcialolympics.org to pull row level data from an event.
  2. SOI verifies the reason and use case of the row level data.
  3. If the row level data, is truly needed then it is either pulled from the HAS Power BI dashboard or the SOI data warehouse.
  4. The data is then shared with the program or Regional Health Messenger using DropBox.

External:

Row Level Data:

  1. Researchers external to Special Olympics (usually academic researchers) fill out a form (https://resources.specialolympics.org/health/requesting-special-olympics-data) and send it to Research and Evaluation.
  2. The form is manually shared via email internally for approval by Andy Lincoln and Brittany Routh or the discipline managers of the Healthy Athlete Data being requested. If the row level data is not from an SOI event, then approval is also sought from the program(s) whose data is being requested.
  3. If approved, the row level data is manually pulled from the SOI data warehouse and shared with researchers via DropBox.
  4. These data requests need to be renewed on an annual basis if the researcher wishes to continue using the data.
  5. At the end of the data use agreement, the external researchers sign the original form saying that they have destroyed the row level data.
  6. Researchers are asked to share all citations of their work whether they be conference abstracts or peer-reviewed journal articles with SOI.

Future Data Requests

Info

PROPOSED: 3/1/2023 This process will be revisited to ensure it is up-to-date.

Internal:

Summary Statistics:

  1. Programs and Regional Health Managers will register for access to the HAS Power BI report.
  2. Once registered, Programs and Regional Health Managers will receive training materials on how to use the Power BI report.
  3. Programs and Regional Health Managers will pull summary statistics as needed from the HAS Power BI report for their respective program or region.
  4. Row Level Data:
  5. Programs or Regional Health Managers will register for access to the SOI data warehouse.
  6. Once registered, Programs and Regional Health Managers will have to sign or be educated on the data use agreement established by SOI.
  7. Programs and Regional Health Managers will receive training on interacting with personally identifiable information (PII) and protected/private health information (PHI).
  8. Programs and Regional Health Managers will then receive training on how to interact with the data warehouse.
  9. Programs and Regional Health Managers will then be able to pull row level data for their program or region as needed.

External:

Row Level Data:

  1. A PowerApp will be developed to handle all aspects of the data request, approval, and termination of the data use agreement.
  2. Researchers will fill out the online form and once it is completed, the document will be automatically shared with SOI stakeholders for their approval. As part of the approval process, researchers will need to document their data management plan (We may borrow the infrastructure already used for the Genomic Data Commons).
  3. Once approved, researchers will hopefully have access to a limited view of the data warehouse based on our data use agreements. They will then pull data directly from the warehouse into statistical programs such as R and perform all analysis without directly downloading excel spreadsheets.
  4. Every year, the PowerApp will send an automatic message reminding researchers to either renew their access or terminate their access to the data warehouse and destroy any records that were downloaded.
  5. The app will also ask authors to provide PMIDs, DOIs or citations when the former two are not available to track the scientific literature produced using SOI data.

HAS Dashboards

A HAS Dashboard is available in PowerBi. These dashboards provide programs with information and analytics about data coming from the health screenings.

Please look at the instructions under Python automation to add users here https://specialolympics.github.io/soi-docs/data_best_practices/external_access/ for access.

hasd

HAS Dashboard Overview

Link: https://youtu.be/U_X8riID7h8?si=L3P6YQZIi5GxI1mn

Acronym List

Acronym Description
HA Healthy Athletes
HAS Healthy Athlete System
OE Opening Eyes
FFt Fit Feet
HH Healthy Hearing
SS Special Smiles
HP Health Promotion
SM Strong Minds
FF FUNfitness
HYA Healthy Young Athletes