Relevant biomedical research relies on finding enough subjects matching inclusion criteria. Researchers struggle to find eligible patients due to: information scattered in many different databases, incompatible data representation, and the technical knowledge required to work directly with databases. We identified the required features of a clinical data search system and used it to design and evaluate Ontocloud, a prototype based on open source software and open standards of a dynamic ontology based database integration system with inference capabilities. A comparison between Ontocloud and three other database integration system showed that our prototype fulfilled its purpose and can be improved to be used in production.
1 A.C. Camargo Cancer Center
The technology to quickly retrieve patient information from the Electronic Health
Record is crucial to biomedical research. Traditional term based search
techniques have been failing to bring accurate and precise results, due to the high
complexity of this knowledge domain[
Data collection for cancer research in a large hospital such as A.C. Camargo Cancer Center is hindered by a series of factors, the most important being: (1) Data is stored in many different databases in diverse ways, constantly changing and evolving; (2) Data is represented in a computer friendly format, hard to understand by physicians and scientists; (3) Collecting data manually is a time-consuming task, and clinical research projects need speed and accuracy on the recruit phase, (4) the same information may be present in different levels of detail, and (5) certain information is not explicitly asserted, but may be inferred based on indirect data.
In this work, we designed, implemented and evaluated a prototype of a database integration system called Ontocloud, based on open source software and standards. It addresses the issues (1)-(5), by providing some key features: dynamic access to data on source databases; ontologies as the medium for data integration; and inference of concepts, harmonizing the detail level of similar information (the semantic mismatch issue), independence of source databases and data annotation. We describe how we implemented Ontocloud to solve a use case of integrating medical document metadata, and compare its characteristics against three other database integration architectures.
A database integration problem is described as taking several sources of
complementary data and providing a single view for those sources[
There are two methods for providing the global view G: dynamic or static. Dynamic methods translate a query on global view G to queries on the relevant source databases S and translate back the answers using the mappings M. Static methods (or data warehouse methods) create a materialized global view, by translating and copying all data from the sources S into a new database G.
Both methods have their benefits and drawbacks. Dynamic methods rely on query
rewriting or query answering, which are hard computational problems and therefore
imply on slower performance. As they directly query the source databases, results are always
up to date. Static methods are easier to set up and faster to query, however there is the need
to translate all data on the sources and construct a new database before any queries can
be answered. This procedure may require a higher level of access on the source databases
and may take a great deal of time and disk space. Also, results are mostly always outdated,
and the global database needs to be refreshed periodically[
Regarding the mappings, database integration systems can be classified as global
as view (GAV) or local as view (LAV). Mappings on GAV systems transforms the
source database into the global view, and queries are answered by several different
algorithms[
An ontology represents knowledge in a formal framework, as concepts and relationships
between pairs of concepts. Ontologies have been considered in heterogeneous database
integration due to their ability to perform inferences and potential to deal correctly with
the semantic mismatch problem [
Semantic mismatch is a problem that is intrinsic to data integration that usually
leads to loss of specificity [
Ontologies can be represented in RDF/XML1 format or in triplestores, which can be thought of as an equivalent of a database for ontologies. SPARQL2 is the query language defined for querying data in an ontology. The SPARQL 1.1 specification allows for joining remote endpoints and thus integrating different datasets.
Inference is the process by means of which new information is derived from
existing data from an ontology. Given abstract concepts, general rules can be added to a
1http://www.w3.org/TR/PR-rdf-syntax/
2http://www.w3.org/TR/rdf-sparql-query/
knowledge base to allow for new facts to be inferred[
Query expansion [
DBOM [
The Query Integrator System (QIS) [
Analyzing the available tools, none of them has features allowing to solve all of the clinical database integration issues we verified, except for Mastro-I and DBOM. However, the first relies on non-free software and it requires that relational sources are integrated first on a relational layer (the Infosphere Federation Server), and then on the ontology layer (Mastro). DBOM seems to be an interesting take on the subject, however it is not available anywhere for download. QIS has very interesting features but is based on obsolete standards and software.
Ontocloud was designed to provide dynamic access to a consolidated database global view of several database sources, using ontologies to consolidate heterogeneous data. Given a set of source databases S1::n, a set of source endpoint E1::n should be provided. Each source publishes its objects of interest concepts of the global view G by means of a SPARQL endpoint. In order to get answers to a query qG over the global database G, the query must go through two transformation steps: the query expansion step accounts for inference, substituting terms not directly defined on the source endpoints; then the query federator step provides the query with SERVICE clauses that indicate in which source endpoint each concept is to be found (Figure 3).
Ontocloud uses four ontologies. The global ontology lists the classes and properties in which the global database will be represented, as well as annotations. The federation ontology specifies the source databases and which classes and properties of the global ontology they implement. The mapping ontology relates tables and columns from a source database to basic concepts on the global ontology. The inference ontology maps derived concepts to basic concepts through an ontology alignment file.
The global ontology should be the starting point when designing an ontology based database integration system, as the queries to be issued will refer to this ontology. It should be well anotated and descriptive, and should comprise the high-level concepts that will be queried as well as the ones actually on the source databases. Those are called base concepts, because they are directly related to a database object. The others are called derived concepts and should be related to base concepts by rules on the inference ontology.
Each relational source database is required to have its own mapping file, which will translate access to the partial RDF graph of the global database G to SQL queries on the actual database objects.
The federation ontology lists all source databases and which concepts from the global ontology they provide. It is used by the federator step to translate a query qG to a query qE over the source endpoints.
Those characteristics make Ontocloud an adequate solution to integrating clinical databases, as stated in the introduction: (1) Integrated sources are independent, so adding, modifying or removing sources does not interfere with other sources; (2) The usage of ontologies allows for annotation of concepts, making it easier for a non-technically trained user to understand it; (3) Data is accessed directly from the sources, yielding always upto-date results; (4) Ontologies provides tools for dealing with semantic mismatch; and (5) Inference of higher level concepts based on raw data, making all assumptions about data explicit and easy to audit.
Ontocloud implementation was based on open standards and open source software. Its
implementation is described in this section (illustrated in Figure 3). To map source
databases as a SPARQL endpoint, we used D2R-server[
D2RQ [
The Query Expansion Step used the inference ontology to translate queries using
derived concepts into base concepts. We used the Mediation9 library, which translates
queries on an ontology A to an ontology B by means of an EDOAL [
The Query Federator Step used the federation ontology to translate a query over the global ontology to the source endpoints. For each triple specified in the SPARQL query, it checks in which sources the concepts involved are present, and surrounds the triple with a SERVICE clause. If a concept is present in more than one source, it replaces the triple with a UNION of all SERVICE clauses. The software was written in Java using Jena library.
We selected as use case the problem of integrating clinical documents metadata from four information systems used at A.C. Camargo Cancer Center: EHR, which contains most data from clinic services; Pathology, that contains reports from anatomic pathology tests (visual inspection of sample tissues); Image, that contains reports from imaging tests; and Prescriptions, that contains both inpatient evolution (texts describing the patient’s day-to-day evolution) and prescriptions of drugs and procedures.
We retrospectively consulted the Medical Informatics Laboratory ticket system, in which all query request made by doctors, managers and researchers are registered. Based on it, we compiled 17 queries of varying complexity to benchmark our integration system 10. The Ethics Committee of A. C. Camargo Cancer Center, where this research was conducted, granted a waiver on informed consent. To answer those queries, we designed the global schema layout as depicted on Figure 4 and created the mappings accordingly.
We looked into the source databases for tables and columns that contained the needed information required by the defined global schema. Most databases contained all fields needed for the desired integration, except for the type of document on Pathology, Image and Prescription databases and the brazilian person registry number (CPF) for physicians on the Prescription database. We inquired physicians and discovered that documents on Pathology and Image databases are always reports and the documents on Prescription database can be a evolution or a prescription, depending whether a field is blank or not; the CPF number could be found for physicians which were linked to another database table, but not all of them (in this case, we simply created a new record without the CPF number).
To account for missing data, we created simple rules of inference based on knowledge provided by physicians. For Pathology and Image, all documents were stated to have
Global ontology <<owlClass>>
Patient +id: integer +name: string +gender: genderEnum ++hdaastDeoOcfuBmierntth:: Ddoactuement <<enumeration>> FGenderEnum M
I <<owlClass>>
Document +type: string +dateOfCreation: datetime +systemId: string +title: string +originalId: integer +author: User
User +universalId: integer +id: integer +name: string
Global relational database 1 pnaatmieent_idPatienvitansrtcehgaerr(255) date_of_birth date gender char(1)
N Documents original_document_id integer optddaraiaouitttctgile_hiee_tonnoyraf_tpl_u_e_csisredyersa_ttieidmo_nid vivvdvanaaaartrrtrceccechghhthaeaaiarrrrmr(((e(19669000)))) N id vUarscehrasr(60) name1varchar(255) “PATHOLOGY REPORT” and “IMAGING EXAM REPORT” type. For Prescription, a conditional rule (based on whether a text field has data or not) was used to determine if a document belonged to “PRESCRIPTION” or “INPATIENT EVOLUTION” type. These rules were embedded on the mapping files.
It was possible to infer patient class based on the presence of certain types of documents on the patient’s EHR; we implemented inference rules on the query expansion step using EDOAL ontology alignment file format. Examples of those rules can be found on Suplementary Table 211.
We replicated the original databases, by retrieving pertinent tables and columns
and storing them into a single MySQL server. To extract the original sources into the
MySQL database we used Pentaho Data Integration Community Edition[
In order to assess performance and accuracy of Ontocloud we have set up three other database integration systems, which exhausts all combinations of the main database integration architecture characteristics: dynamic or static data acess, and relational database or ontology data representation. We evaluated accuracy in a qualitative way, by making sure that all 17 queries yielded equivalent results on all database integration systems evaluated. Query performance was evaluated as the total clock time a query took for completion on a integration system.
After replicating the source databases, we proceeded to set up all four database integration systems. The Supplementary Figure 1 depicts the experimental setup, and Supplementary Table 3 the database size and extraction times.
The tools used to set up the other integration systems are as follows: Triplestore: Openlink Virtuoso Universal Server Open Source Edition provides, among many other things, an RDF triple store and a SPARQL endpoint. We chose Virtuoso to implement Triplestore, the static access ontology based integration method. For each of the four source databases, we used D2R to dump data into an N3 file. Those files were imported using Virtuoso Bulk Loader12.
Federation: Teiid13 is an open-source, dynamic relational database integrator system; it allows the creation of views over database resources published on a JBoss14 server, and it is accessible as a JDBC resource. Federation, the dynamic database integration architecture, was designed as a Teiid instance. For each table in the global schema, we wrote a consolidated view, composed of queries over each source database joined by UNION clauses. Those queries did all necessary mapping to provide the required information, even if it was spread in different tables on the source database. The missing document type of Pathology, Image and Prescription databases was inferred directly on the view statement as a SQL constant or expression.
Replication: The Replication architecture was created by materializing the Federation queries (translated to MySQL dialect) into tables. As in the source databases, every column in each database was indexed.
The 17 queries were transcribed to each integration system language (SPARQL for ontology based systems and SQL for the others) and dialect (function names and namespaces were slightly different between MySQL and Teiid, and between Virtuoso and ARQ). There is no SERVICE specific optimizations on Jena, and we have not implemented it for Ontocloud. In contrast, Teiid, the software we chose for implementing Federation, was highly optimized for this type of queries. To account for this difference, we implemented two sets of queries for Ontocloud: one using both query expansion and query federator step, and other querying directly the sources with queries tuned by hand. This way, we get the actual running time for current software and an estimation of what the timing would be if there was an optimization step. We ran one single round of all 17 queries in all systems, without time limit and saving the results. To avoid server resource competition, only one one query on a single integration system was executed at a given time.
The computer server in which the experimental setup was created and tests were performed had 4 cores with 3.00GHz, 64bits, and 8GB of RAM, running CentOS 5. The database software installed was MySQL server version 5.0.95. We also used Pentaho Data Integration Community Edition version 4.0.1, ARQ-2.8.8, PHP 5.2.5, Virtuoso Open Source Edition 6.1.4.3127, D2R-Server 0.8, Java 1.6.0.23, Teiid 7.7 and JBoss 5.1.0 GA.
A functional comparison between all systems can be seen on Table 1. All four integration systems were successfully configured and deployed. Except for queries 14 and 17 on Ontocloud Optimized, and queries 10-17 on Ontocloud Unoptimized, which were not 12http://www.openlinksw.com/dataspace/dav/wiki/Main/VirtBulkRDFLoader 13http://www.jboss.org/teiid/ 14http://www.jboss.org/ completed due to lack of memory, all other queries on all evaluated systems completed successfully and yielded the same results. Ontocloud Optimized performed better than Federation on 7 queries out of 17, and was 15% faster than Ontoclound Raw (without optimizations). Replication was the fastest method of all, followed by Triplestore which performed better than Federation and Ontocloud on 13 queries. Time measurements for all database integration systems can be seen on Figure 5 and Supplementary Table 4.
system Ontocloud Federation Triplestore Replication
strategy Dynamic Dynamic Static Static
solving method By ontology Least detailed By ontology Least detailed
Yes No Yes No
No No
The implementation of Ontocloud and the use case experiments showed that it is an adequate database integration system for clinical data, as it accomplish the five objectives: (1) The configuration of source databases was completely independent, except for the Federation Ontology, which lists the URL of each endpoint and the concepts each implements; (2) The global ontology contained human-readable descriptions, so data would be easily understandable by non-technical personnel; (3) Data is accessed directly from the sources, yielding always up-to-date results; (4) Mappings provideded missing data in a way that is transparent to the end user and (5) Higher level concepts like TratedPatient and InPatient are easily understood by physicians and managers, while being translated by the query expansion step to its definition on raw data, allowing the query to be performed.
As we set up the integration systems, fundamental differences between Federation and Ontocloud arised. Federation requires that the developer explicitly join all sources in a single database view. That makes adding a new source to it a difficult and risky task, as it is required to work on a SQL statement that involves several different source databases and any mistake may compromise the whole integration system. Each Ontocloud source is configured without the need to take other sources in consideration. Instead, it relies on the Query Federator step, which adds to the original query clauses indicating in which source endpoint each triple will be resolved. Therefore, by keeping the mapping files separated, Ontocloud facilitates the maintenance of source databases.
Inference on Ontocloud was based on the Mediation library, which allowed us to implement rules by expanding each query term. The inference rules are detached from the database integration itself, and can be maintened independently of the sources. Also, as those rules are represented on an ontology language, it is more suitable for domain experts to maintain it than on the relational methods, in which rules should be implemented on SQL language. It also improves the information management of such a system, as it keeps the raw data (on the mapping ontologies) apart from the higher level concepts (on the inference ontology).
Ontocloud performance suffered on queries with aggregation or that dealt with date operations. This occurs because SPARQL aggregation keywords and date manipulation functions are not translated directly to SQL, instead all results are retrieved and transformations are performed in memory. That both hindered performance and required a lot of memory. Also the queries generated by Query Federator step contained a lot of SERVICE keywords, each containing only one triple. An important optimization would be to join triples on the same SERVICE pattern, minimizing the access to source endpoints. Also, the order of triples and filters on the SPARQL query is crucial to determine the performance. Those optimizations are beyond the scope of this work, but would certainly put Ontocloud on a par with the other methods. For the purpose stated in this work, the speed of Ontocloud seems a fair tradeoff for the ability of yielding up-to-date results at any time and performing inference.
We have successfully designed and implemented Ontocloud to perform ontology-based database integration. It implements important features in an clinical data integration system: The sources are loosely coupled, favoring distributed and dynamic management of sources; uses ontologies to integrate data, which is prone to reuse and more human readable; has dynamic access to sources, always yielding up-to-date results; and allows inference. We believe that this system architecture can be extended and improved, as indicated in the discussion, to become a production level tool very useful in the medical informatics context.