APL - Agility and Ability Today: Application development case
Our client was a major collector of clinical survey data and needed a way of providing, to their clients, a tool to let them intelligently drill down through this vast amount of complex information.
The problem was that each compiled database had a very different structure to the other. The tool that was therefore envisaged needed to allow for these variations and allow the researcher to interrogate through a single, simple interface.
The researcher in some cases was an experienced computer user but often was a manager with limited analytical skills and having little or no knowledge of the underlying database. All of the controls had to be simple to understand but yet give the richness of control which would prevent inappropriate filtering and still allow powerful database exploration.
We came up with a three part system called "The Database Interrogator" (TDI).
- The first part enabled us to take the collected surveys and by using its meta-data compile it into a form better suited to our needs. This format was designed to be the same for all studies. APL gave us the flexibility to manipulate all forms of data with ease and, if necessary, handle large amounts of information as well.
- The second stage was to develop a user interface which would allow an intelligent drill down through the compiled, multi dimensional data. This interface needed to be both rich and easy to use. The interface was designed to be modular and data driven. Multi-dimensional arrays had to be able to be specified and then delivered in an understandable fashion.
- The final stage was to build a generic report generator linked to a graphics engine for graphical output that could display the identified information.
All three parts were built using Dyalog APL for the PC with the graphics engine being derived from "Graphics Server" a third party OCX.
Often the studies we were dealing with were multi-country and could become quite sizable. Some were to be updated on a regular basis and so we had to allow filtering across studies to essentially show time series data.
We did know that we would have a large number of different databases all of which needed to use our standard tool. The central repository of our source data handled many Tb of information so any study had to be made up from an extract taken from this repository.
We essentially built a Multidimensional Online Analytical Processing system (MOLAP) out of a series of Data Cubes. The data volume for each study was typically 100-300Mb. Our design allowed for complex questions to be asked in real time of this data. The filtering and axis generation was fast enough that no pre-compiling of the data for common questions was needed.
APL's ability to handle multi-dimensional arrays without issue meant that we could build a model to meet our exact requirements and deliver a highly customised solution to the ultimate user anywhere in the world without the need for SQL or similar to be available.
The Database Interrogator rapidly became a firm favorite with the customers and was initially built in only 5 months by a single programmer. He and the rest of the team from our client started with a completely blank sheet of paper and built the system with no formal specification only a series of design goals.