Optima Systems, a Dyalog Ltd Application Development Partner, had a client who 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 ones. The tool that was envisaged needed to allow for these variations and enable researchers to interrogate all these databases 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 little or no knowledge of the underlying database. All of the controls had to be simple to understand but 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 that 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 for the PC with the graphics engine being derived from "Graphics Server", a third-party ActiveX control.
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 terabytes of information, so any study had to be made up from an extract taken from this repository.
We essentially built a Multi-dimensional Online Analytical Processing system (MOLAP) out of a series of Data Cubes. The data volume for each study was typically 100-300 MB. Our design allowed for complex questions to be asked of this data in real time. 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 favourite 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.
Download this case study as a PDF