Research-Ready

In a flash, it has been almost three years since I left the Centre for Heart Lung Innovation, so it's no surprise that a recent tweet caught my eye and tugged the memory chord. I was introduced to Oracle Application Express (APEX) on August 26, 2010. A year (and a major release) later, I had the opportunity to demonstrate it's capabilities with proof that it could help improve productivity in our research centre. As a sidenote, back then, attendees receive DVDs full of developer software goodies. APEX however, didn't need much, just check out the hands-on lab requirements below.

Yup, nothing! All I needed was a laptop, Internet connection and a modern day web browser!

TL;DR APEX is a Rapid Application Developer (RAD) platform that is secure, robust and extremely flexible for building web-accessible applications to support research, e.g. data collection forms, surveys, freezer/biospecimen inventory management, laboratory inventory management and many more!

Research Data Management

Nearly every form of research would involve some form of data collection. As responsible stewards, researchers must ensure data is acquired ethically and accurately, used appropriately, and archived for future validation and reuse. For clinical researchers, rigorous, well-defined data collection methods are even more critical due to the nature and type of data that is collected.

Clinical research studies that involves human subjects are often governed by Institution Review Boards (IRB). It is the duty of such committees to ensure that the study is ethical and complies with regulatory requirements. That sometimes involves thorough examination of the Data Management Plan (DMP), thereby ensuring that patients'/participants' interests are safeguarded.

A good Research Data Management (RDM) strategy should consider the following:

• Collection
  • Representation - data structures/schemas
  • Storage - #SayNoToSpreadsheets
  • Documentation - maintaining an up-to-date data dictionary
  • Capture - tools for collecting the data
• Maintenance and Compliance
  • Security - storage, access and audits
  • Backups
  • Disaster Recovery
  • Monitoring
• Use
  • Access and sharing
  • Quality control
  • Processing and transformations
• Preservation or Destruction

Why APEX?

One of the more popular software for Electronic Data Capture (EDC) in research is REDCap. The application was first developed and released by Vanderbilt University in 2004, to provide clinical researchers with a secure data collection tool that met regulatory requirements, and yet not needing users to have extensive technical backgrounds.

Unfortunately, the software and learning resources are not readily available. To gain access to the application, aspiring REDCap developers must first belong to an institution that is willing to sign up to be a consortium partner. You can however, sign up for a one-week trial account on their demo server.

To get a sense of how APEX might fit the research environment, I thought it would be fitting to use REDCap's list of software features against what the platform had to offer.

REDCap Feature	APEX
Project design (online)	One of APEX key strengths is its comprehensive, web-based graphical development environment, the App Builder. To get a brief overview of the UI, check out this 9-minute video from Oracle.
Project design (offline)	REDCap allows users to create and edit data collection instruments (forms) using a CSV. Users can modify just about everything including field types, calculation fields and validations. Though APEX applications are declarative and has a rich API, it does not have a matching functionality for creating pages using a configuration file... or do we? Using Application Blueprint, APEX developers can quickly create an application defined in a JSON file. Dimitri Gielis demonstrates how to get this done in this short video tutorial.
Availability	REDCap is available free-of-charge, but as mentioned earlier, you will need to first sign up as an institutional consortium partner. APEX is available to download and install at no cost as well. That's right, no purchase necessary! Oracle offers a free version of their database. Version 18c and later will even include database features otherwise only available in Enterprise Editions of the software. For no-fuss learning and understanding the platform, developers can create a workspace on apex.oracle.com. It's free and the only requirement to keep the "trial" going is to keep using it.
Secure and web-based	APEX runs on top of perhaps the world's most secure database. The Oracle database has built-in features like auditing, secure database connections, cryptographic stored procedures and functions for protecting sensitive information. Other paid options provide additional security features like Transparent Data Encryption, Data Redaction, Fine Grained Auditing (FGA) and much more! APEX applications can also be configured to use a wide-variety of authentication methods, including enterprise systems and protocols like LDAP and OAuth2. Single-sign on (SSO) available out-of-the-box.
Fast and flexible	With APEX, you can develop production-grade, enterprise-ready applications with minimum amount of coding. Users also have at their disposal, tools like Quick SQL and Application Builder for quickly defining data structures and creating applications. Users can also transform spreadsheet data into modern, professional-looking reports and data entry forms in minutes!
Multi-site access	Mutli-tenancy support is available at many levels. For example, research groups could isolate their applications and data using APEX workspaces and database schemas. Access can be managed and federated using enterprise identity management systems cited earlier.
Autonomous utilization	Administrators within a research groups have full autonomy to manage user/developer accounts and access.
Fully customizable	APEX Applications are more than fully customizable. Users/developers can modify the look-and-feel (Themes) and behaviour (Dynamic Actions and Processes). For advanced developers, the sky's the limit!
Audit trails	The Oracle Database provides standard auditing features. Enterprise Edition customers have the option to also enable FGA.
Automated export procedures	The Data Workshop utility in APEX provides the ability to import and export data in XML and CSV formats. Developers can also write PL/SQL routines to generate the data in a suitable download format. Packaged as an APEX plugin, these procedures are then readily reusable in other applications. For example, Didier Bastogne created a Process Type plugin that allows users to download a table of molecules in SDF file format.
A built-in project calendar	APEX comes with a comprehensive Team Development utility that allows developers to work together. Developers can set milestones, define features, track TODO items, file bugs and manage feedback, all within the workspace environment.
A scheduling module	This module is specially geared to help with longitudinal studies. Since APEX is a multi-purpose application development platform, such modules do not exist. Developers will have to create scheduling features within a clinical research application.
Ad hoc reporting tools	User-driven reporting is one of APEX's greatest strengths. There is too much to write about Interactive Reports (IR) and Interactive Grids (IG) to condense in a single blog post. Suffice to say that with IRs and IGs, users can filter, sort, create computations and aggregations, and generate charts, all without developer intervention.
Branching logic	Dynamic actions and page branching in APEX provides developers declaratively define the application's business logic.
File uploading	The Data Load utility described earlier, facilitates the import of data into the system. Developers may also use the File Browse page item to allow users to submit files for upload. Files can be staged and manipulated in the database before it is finally stored. APEX also has a wizard to create a data-loading wizard that allows users to upload, map and import delimited data files.
Calculated fields	Use Dynamic Actions to perform on-the-fly calculations using either JavaScript, PL/SQL or both.
A quick and easy software installation process	Installing the APEX software stack is more involved and probably requires a steeper learning curve. However, with modern DevOps tools available and a lot of helpful fellow developers, there are now Docker images, Vagrant configuration files and build scripts to help setup the stack with minimal user interaction.
Regulatory compliance	The platform itself may not been certified with regulatory authorities, however, infrastructure providers could architect and have their systems validated.
Available in multiple languages	Multiple languages are supported. Community-contributed translation text are also available freely for download and use.

Features of Note

The following are three features that makes it even more compelling to take a deeper look at APEX.

Enterprise-ready Authentication Schemes

Available since version 4.0

The Social Sign In authentication scheme, made available with the 18.1 release, is my favourite. To me, an important consideration when introducing new software or platforms, is its ability to integrate with existing systems, most importantly, security. Let's face it, users do a poor job in maintaining their account credentials and so, the less username-password combos they need to come up with, the better. A SSO setup also allows administrators to introduce more secure policies like requiring the use of two-factor authentication during logins.

The new authentication scheme comes pre-configured to work with Google and Facebook's OAuth2 providers. I have written previously on how to do this. All you need are the correct URLs to use. No coding required. There are two additional setups for OpenID and generic OAuth2 providers like Microsoft.

Besides OAuth2, APEX has supported LDAP and HTTP Header Variable authentication for a while now. I have also written about how SSO can be achieved with SAML2, making it possible for users to seamlessly move between Google G Suite/Microsoft Office 365 environments and corporate APEX applications.

Quick SQL

Available since version 5.1

Quick SQL was first made available in APEX 5.1 as a packaged application. Packaged applications are fully functional applications that you can install in APEX workspaces. These applications come pre-packed with installation scripts and seed data. I digress. When version 18.1 was released, Quick SQL became a feature and is accessed during the process of creating a new application or through the SQL Scripts utility.

Using syntax similar to Markdown, users/developers can use a shorthand to script out a data model and then generate the SQL code needed to create these database objects.

Here's a quick example:

# drop: true
person /auditcols
  personid number /pk
  firstname string
  lastname string
  birthdate date
  height_cm number
  car /auditcols
    carid number /pk
    make string
    model string
    engine_capacity number

And what it looks like in SQL:

-- drop objects
drop table person cascade constraints;
drop table car cascade constraints;

-- create tables
create table person (
    personid                       number not null constraint person_personid_pk primary key,
    firstname                      varchar2(255),
    lastname                       varchar2(255),
    birthdate                      date,
    height_cm                      number,
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

create table car (
    person_id                      number
                                   constraint car_person_id_fk
                                   references person on delete cascade,
    carid                          number not null constraint car_carid_pk primary key,
    make                           varchar2(4000),
    model                          varchar2(4000),
    engine_capacity                number,
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

-- triggers
create or replace trigger person_biu
    before insert or update
    on person
    for each row
begin
    if :new.personid is null then
        :new.personid := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end person_biu;
/

create or replace trigger car_biu
    before insert or update
    on car
    for each row
begin
    if :new.carid is null then
        :new.carid := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end car_biu;
/

-- indexes
create index car_i1 on car (person_id);
-- load data

Check out the video below that demonstrates how this is done with Quick SQL:

Web Source Modules

Available since version 18.1

APEX has had PL/SQL packages (apex_web_service) to work with external REST APIs for a while now, but the new Web Source Modules (WSM) feature found in version 18.1 is a game-changer. Power users and developers configure credentials and WSM references with simply the knowledge of what URLs and parameters to use. Once created, reports can be created using these WSMs as source. The framework performs any necessary authentication, REST calls, result parsing and then renders them as though they were database tables or views.

This feature allows developers to easily integrate and interact with external data available through REST APIs, e.g. Bioontology and MailChimp.

What's Missing

APEX is great, but it's a general purpose tool. One that we can use to create and support a wide variety of business use cases. As a platform for supporting research, here are some areas where it falls a little short. And I emphasised "little" because there are workarounds.

Validated Instruments

REDCap maintains a library/collection of validated data collection instruments. These are pre-baked forms and data structures that have been used in research and peer reviewed in publications. Researchers could use these and avoid reinventing the wheel.

We don't have access to such a resource for APEX yet. However, with Package Applications, I am confident that a similar repository of research-focused package apps could be made available to the community. If this interests you, then ping me. Let's talk.

De-identification

Designed for clinical research, the REDCap Consortium has put much effort in ensuring that the software meets regulatory compliance and patient privacy is protected. Hence, you will find built-in data de-identification tools and are commonly used during data exports. APEX does not facilitate de-identification out-of-the-box. While developers can implement filters that can do the same during data exports, the preferred approach is to use features such as Data Redaction and Data Masking. These unfortunately, are paid options on top of the Enterprise Edition of the database.

Below is an organic list of academic institutions that I have identified through various sources, that use APEX for research, provide an APEX platform and/or services to support research. A few institutions actively use APEX administratively, but to keep the discussion in context, I opted to leave them out for now. They are however, primed to deliver APEX services for the research activities, if any, within their organization.

Institution/Organization	Country	Source	Date Added
Centre for Heart Lung Innovation	Canada	Personal knowledge	2018-09-03
BC Children's Hospital Research Institute	Canada	Personal knowledge	2018-09-03
BC Centre for Excellence	Canada	Job posting	2018-09-03
Indiana University	United States	Press release	2018-09-03
Case Western Reserve University	United States	Resource listing	2018-09-03
Institute for Computer Biology	United States	Resource listing	2018-09-03
NASA Ames Research Center	United States	Presentation/Publication	2018-09-03
Commonwealth Scientific and Industrial Research Organisation	Australia	Resource listing	2018-09-03

Google searches only take you that far, so this is by no means a comprehensive lay of the land. If you are part of a research organization that is actively using and/or offering APEX as a development platform and service, please reach out to me.

Final Word

I had intentionally left out several institutions of higher education out of the list above, as I wanted to focus solely on the use of APEX in academic research. In reality, many institutions have a site licenses and/or significant discounts for using the Oracle Database. If you are endowed with such resources, please reach out to the IT folks and do the right thing. Stop storing and hording data on spreadsheets and file-based, siloed relational databases. Secure them in enterprise-grade software and take data collection to the next level with advanced, web-based forms.