CP - OpenI design

High Level Requirements

It should be possible to migrate data for any study from ClinPortal to OpenI. It should be possible to configure which fields are needed from the DE forms, static data and biospecimen data.

OpenI Introduction

OpenI works on classic Snowflake schema where there is a central fact table and multiple dimension tables linked to it.

For example, in case of Participant, there will be a one dimension table each for race, gender, ethnicity, etc. Traditionally the dimension tables are normalized. They do not grow in size since it contains non-repeating data.

Data migration approach

Classic Snowflake design

A classic snowflake schema should be created per study. A XML configuration file has to be prepared to map the columns from study to OpenI. Below are the steps to create the dashboard for particular study:

Step 1: Configuration file

Configure the XML file for setting the fields you would like to set-up as part of OpenI dashboard. 
Please note the following:

1. It is ideal to restrict the number of fields to 20 or less fields. Otherwise the dasboard will show too many items and might not serve the purpose. So choose the fields to migrate carefully.
2. The configuration is based on form name and fields, not the model.
3. It supports participant data, form data, MICS studies and caTissue data  (if the study is integrated with catissue) all in one XML.
4. No dates can be included as dates are considered PHI.

Configuration XML file for a study:

1. The 'srcXMLFieldName' in the configuration XML file needs to be configured for every DE form and static form attribute that should be included in the OpenI dashboard. For reference, export data for this study from ClinPortal->Export Form Data. The field name in the exported file has to be used as 'srcXMLFieldName'.
2. Display Label in the configuration XML is optional. If not given, it will use same as srcXMLFieldName
3. If you specify display label, make sure it is lesser than 27 characters and unique across all attributes configured for the study in the XML.
4. If you are specifying the attributes for sub-form, the sub-form name should be the container label from the data XML.
5. If you specify biospecimen fields in the configuration file, then the 'srcXMLFieldName' name should be same as field name of data XML but in camel case.

Refer to the XSD for the configuration XML. Refer to the sample Sample CV-Aneurysm Config XML.

Step 2: Create Snowflake schema for OpenI dashboard

1. If you are setting up dashboard for the first study, create an empty DB user(schema) for OpenI related schemas. This should not be same as ClinPortal DB user. Configure this DB properties in etl.properties placed in the ClinPortal installable folder. The OpenI schema should be then reused for future studies for which dashboard needs to be set up. 
2. Run the below command to create the schema needed for setting up the dashboard from the ClinPortal installable folder.

ant command : ant -f etl_build.xml  create_openi_schema -DschemaConfigFileName="Schema Configuration XML" -DstudyId="Study ID" -DappURL="<ClinPortal App URL>" -DuserName="ClinPortal UserName" -Dpassword="Password"
Where:
Schema Configuration XML = the configuration XML file in case of non Dystoia studies.
Study ID = ID of the study for which the schema is being generated.
ClinPortal App URL = URL of clinportal application from where the data has to be imported.
ClinPortal UserName = clinportal user name.
Password = clinportal password.
sample ant command :
ant -f etl_build.xml  create_openi_schema -DschemaConfigFileName="/root/Desktop/Docs/schema.xml" -DstudyId="666" -DappURL="{_}{+}http://localhost:28080/clinportal+_" -DuserName="sanjay@sanjay.com" -Dpassword="Admin123"
Once successful, there is a report created within the ClinPortal Installable folder. The report name is same as the main name specified in the configuration file created in configuration XML. It is a text file.
Dystonia dashboard set-up: For Dystonia MICS study, custom script has been created as there were study specific requirements. The study users wanted multiple form attributes combined as PVs under single attribute, values from attributes from multiple forms to be merged into single attribute in OpenI. In order to satisfy such requirements, a separate script had to be developed. Use the below command to create the OpenI schema for OpenI:
ant -f etl_build.xml  create_dystonia_schema  -DstudyId="Study ID" -DappURL="ClinPortal App URL" -DuserName="ClinPortal AppUser" -Dpassword="Password"

Step 3: How to configure the OpenI dashboard?

Configure the OpenI dashboard based on the created schema. Steps are documented here: How to set up OpenI dashboard for Clinportal study?

Step 4: Setting up cron job

To run the dashboard at regular intervals, configure Step 3 in a cron job to run at regular internal (nightly, weekly, etc as per needs). Running the same command multiple times will drop the existing tables and recreate them. So the same command can be used for incremental migration.