Some DB

• schema
• use
• endpoint
• libraries

DB as RDF

share it
by supplying a URL
my="http://x.example/DB/"

DB as RDF

share it
by supplying ...
my="http://x.example/DB/"

DB as RDF

share it
by supplying a URL
my="http://x.example/DB/"

DB as RDF

share it
by supplying a URL
my="http://x.example/DB/"

Triples Transformation

Triples Transformation

CONSTRUCT Expression

   PREFIX :mydb <http://cityhospital.example/dbs>
CONSTRUCT { ?o a          :PatientObservation .
            ?o :patient   ?p .
            ?o :doctor    ?d }
            ?d :foaf:name ?dName }

    WHERE { ?o mydb:patient ?p .
            ?o mydb:doctor  ?d .
            ?d mydb:name    ?dName }

3+ Copies

Query Transformation

... to SQL

Instead of Copies

Query Pipelines

Trivial Configuration

   PREFIX :mydb <http://cityhospital.example/dbs>
CONSTRUCT { ?o a          :PatientObservation .
            ?o :patient   ?p .
            ?o :doctor    ?d }
            ?d :foaf:name ?dName }

    WHERE { ?o mydb:patient ?p .
            ?o mydb:doctor  ?d .
            ?d mydb:name    ?dName }

SDTM Query

PREFIX sdtm: <http://www.sdtm.org/vocabulary#>
PREFIX spl: <http://www.hl7.org/v3ballot/xml/infrastructure/vocabulary/vocabulary#>

SELECT ?patient ?dob ?sex ?takes ?incluDate
 WHERE {
  ?patient a sdtm:Patient ;
          sdtm:middleName ?middleName ;
          sdtm:dateTimeOfBirth ?dob ;
          sdtm:sex ?sex .

  ?ip	  sdtm:subject ?patient ;
	  sdtm:standardizedMedicationName ?takes ;

          sdtm:startDateTimeOfMedication ?incluDate ;
	  spl:activeIngredient ?ic . ?ic spl:classCode 6809 .
  OPTIONAL {
  ?cp	  sdtm:subject ?patient ;
	  sdtm:standardizedMedicationName ?exclu ;
 	  sdtm:startDateTimeOfMedication ?excluDate ;
	  spl:activeIngredient ?cc . ?cc spl:classCode 11289 .
  }
  FILTER (!BOUND(?cp))
} LIMIT 30

Pipeline

  cat sdtm.rq | \
  SWtransformer -q - hl7-sdtm.rq | \
  SWtransformer -q - db-hl7.rq -s http://hospital.example/DB/ | \
  mysql -u root DiabeticPatientsDataSet --table

The debugging narrative describes the process.

Final SQL Query

SELECT patient.id AS patient, patient.DateOfBirth AS dob, dbhl_sexEntry_gen1.EntryName AS sex, dbhl_indicItem_gen3.EntryName AS takes, NULL AS indicDate
       FROM Person AS patient
            INNER JOIN Sex_DE AS dbhl_sexEntry_gen1 ON dbhl_sexEntry_gen1.id=patient.SexDE
            INNER JOIN Item_Medication AS dbhl_indicItem_gen3 ON dbhl_indicItem_gen3.PatientID=patient.id
            INNER JOIN Medication AS dbhl_indicMed_gen0 ON dbhl_indicMed_gen0.ItemID=dbhl_indicItem_gen3.id
            INNER JOIN Medication_DE AS dbhl_indicDE_gen5 ON dbhl_indicDE_gen5.id=dbhl_indicMed_gen0.MedDictDE
            INNER JOIN NDCcodes AS dbhl_indicCode_gen2 ON dbhl_indicCode_gen2.NDC=dbhl_indicDE_gen5.NDC AND dbhl_indicCode_gen2.ingredient=6809
            LEFT OUTER JOIN (
    SELECT dbhl_indicItem_gen4.PatientID AS patient, dbhl_indicItem_gen4.EntryName AS contra
           FROM Item_Medication AS dbhl_indicItem_gen4
                INNER JOIN Medication AS dbhl_indicMed_gen1 ON dbhl_indicMed_gen1.ItemID=dbhl_indicItem_gen4.id
                INNER JOIN Medication_DE AS dbhl_indicDE_gen6 ON dbhl_indicDE_gen6.id=dbhl_indicMed_gen1.MedDictDE
                INNER JOIN NDCcodes AS dbhl_indicCode_gen3 ON dbhl_indicCode_gen3.NDC=dbhl_indicDE_gen6.NDC AND dbhl_indicCode_gen3.ingredient=11289
             ) AS opt1 ON opt1.patient=patient.id
 WHERE !(opt1.contra IS NOT NULL)
 LIMIT 30

Results

+---------+---------------------+--------+-------------------------------------------+---------------------+
| patient | dob                 | sex    | takes                                     | hlsd_start_gen1     |
+---------+---------------------+--------+-------------------------------------------+---------------------+
| 1517441 | 1964-09-01 00:00:00 | Female | Glucophage TABS                           | 2007-07-23 00:00:00 | 
| 1517441 | 1964-09-01 00:00:00 | Female | Glucophage TABS                           | 2007-07-23 00:00:00 | 
| 1517000 | 1958-01-01 00:00:00 | Male   | Metformin HCl 500 MG Tablet               | 2007-06-29 00:00:00 | 
| 1517000 | 1958-01-01 00:00:00 | Male   | Metformin HCl 500 MG Tablet               | 2007-06-29 00:00:00 | 
| 1517403 | 1981-05-11 00:00:00 | Male   | Metformin HCl 500 MG Tablet               | 2008-01-03 00:00:00 | 
| 1517441 | 1964-09-01 00:00:00 | Female | Glucophage TABS                           | 2007-07-23 00:00:00 | 
| 1517441 | 1964-09-01 00:00:00 | Female | Glucophage TABS                           | 2007-07-23 00:00:00 | 
| 1516986 | 1995-06-13 00:00:00 | Male   | Metformin HCl 1000 MG Tablet              | 2007-12-04 00:00:00 | 
| 1505583 | 1950-02-25 00:00:00 | Female | Metformin HCl 500 MG Tablet               | 2008-01-14 00:00:00 | 
| 1234561 | 1983-01-02 00:00:00 | Male   | GlipiZIDE-Metformin HCl 2.5-250 MG Tablet | 2007-09-28 00:00:00 | 
| 1234561 | 1983-01-02 00:00:00 | Male   | GlipiZIDE-Metformin HCl 2.5-250 MG Tablet | 2007-09-28 00:00:00 | 
| 1234562 | 1963-12-27 00:00:00 | Female | GlipiZIDE-Metformin HCl 2.5-250 MG Tablet | 2007-09-28 00:00:00 | 
| 1234562 | 1963-12-27 00:00:00 | Female | GlipiZIDE-Metformin HCl 2.5-250 MG Tablet | 2008-07-28 00:00:00 | 
+---------+---------------------+--------+-------------------------------------------+---------------------+
      

Translation Languages

• SWObjects - SPARQL CONSTRUCT.
• d2r - RDF schema and conf in RDF.
• Virtuoso - DDL.
• squirrelRDF, et al, - ???.

Kudos

thanks to Lilly and Lincoln Labs for funding this work

• source: http://www.w3.org/2008/04/SPARQLfed/
• slides: http://www.w3.org/2008/Talks/1022-SWObjects/