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.