Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

As presented in Input data model wiki page, the AI algorithm input data is represented as FHIR R4 models. This is why, to develop the AI algorithm, we need to as FHIR R4 models. First we anonymize the openIMIS database and then we migrate it to FHIR.

Database anonymization

The following database tables and (not required) fields are anonymized (openIMIS-AI_Anonymization.sql):

  • tblFamilies: FamilyAddress

  • tblInsuree: CHFID, LastName, OtherNames, passport, Phone, CurrentAddress, GeoLocation

  • tblClaimAdmin: LastName, OtherNames, DOB, Phone, EmailId

  • tblOfficer: LastName, OtherNames, DOB, Phone, EmailId, permanentaddress, VEOLastName, VEOOtherNames, VEODOB, VEOPhone

  • tblUsers: LastName, OtherNames, DOB, Phone, EmailId

  • tblPayer: PayerName, PayerAddress, Phone, eMail, Fax

  • tblPhotos: CHFID, PhotoFileName

Extracted data

In order to develop an AI algorithm for claim categorization, we need to have access to a database of labeled claims (after static validation or manual evaluation). This data, represented in FHIR JSON format, correspond to resource related to ClaimResponse, Patient, Location, HeathcareService, Condition, ActivityDefinition and Medication. Not all the fields from openIMIS database tables are mapped to FHIR. However, we are considering that all the necessary fields for the AI model were selected. This will be further validated in collaboration with Nepal Medical Officers.

The available openIMIS database received from Nepal openIMIS implementation contains 531900 claim submitted from May 2016 to June 2018.

To migrate the openIMIS data to FHIR. For this, we use the openIMIS to FHIR migration tool. For large data sets, ClaimResponse and Patient, we developed SQL scripts (GetClaimResponseJSON1Line.sql, GetPatientJSON1Line.sql) to generate the FHIR data directly from database.

...

FHIR R4 Resource

Count

File size

ClaimResponse

531 900 records

2,713,324,028 bytes

Expand
titleClaimResponse Output (1 resource example)

{ "entry": [ { "fullUrl": "http://127.0.0.1:8000/api_fhir_r4/ClaimResponse/FCD34CB1-2630-4E21-88EE-C38521C09F0D", "resource": { "resourceType": "ClaimResponse", "extension": [ { "url": "billablePeriod", "valuePeriod": { "end": "2016-05-16", "start": "2016-05-16" } }, { "url": "icd_0", "valueReference": { "reference": "Condition/M13" } } ], "enterer": { "reference": "Practitioner/73371CFC-9C4D-4F71-B64E-6C858E0B9876" }, "created": "2020-07-27", "id": "FCD34CB1-2630-4E21-88EE-C38521C09F0D", "identifier": [ { "type": { "coding": [ { "code": "UUID", "system": "https://hl7.org/fhir/valueset-identifier-type.html" } ] }, "use": "usual", "value": "FCD34CB1-2630-4E21-88EE-C38521C09F0D" }, { "type": { "coding": [ { "code": "MR", "system": "https://hl7.org/fhir/valueset-identifier-type.html" } ] }, "use": "usual", "value": "601" } ], "insurer": { "reference": "Organization/openIMIS" }, "item": [ { "adjudication": [ { "amount": { "currency": "USD", "value": 80.00 }, "category": { "coding": [ { "code": "2" } ], "text": "entered" }, "reason": { "coding": [ { "code": "0" } ] }, "value": 1.00 }, { "amount": { "currency": "USD", "value": 80.00 }, "category": { "coding": [ { "code": "4" } ], "text": "checked" }, "reason": { "coding": [ { "code": "0" } ] }, "value": 1.00 }, { "amount": { "currency": "USD", "value": 80.00 }, "category": { "coding": [ { "code": "8" } ], "text": "processed" }, "reason": { "coding": [ { "code": "0" } ] }, "value": 1.00 }, { "amount": { "currency": "USD", "value": 80.00 }, "category": { "coding": [ { "code": "16" } ], "text": "valuated" }, "reason": { "coding": [ { "code": "0" } ] } } ], "extension": [ { "url": "ActivityDefinition", "valueReference": { "reference": "ActivityDefinition/3B4193F7-37EA-4B6E-BD5E-87F08752526A" } } ], "itemSequence": 1 } ], "outcome": "valuated", "patient": { "reference": "Patient/39418469-FC67-4363-BB51-B59B19FDBB47" }, "request": { "reference": "Claim/FCD34CB1-2630-4E21-88EE-C38521C09F0D" }, "requestor": { "reference": "HealthcareService/36ECB0DB-E942-4A2D-A0C5-33ED4D00419D" }, "status": "Not Selected", "total": [ { "amount": { "currency": "USD", "value": 80.00 }, "category": { "coding": [ { "code": "submitted", "display": "Submitted Amount", "system": "http://terminology.hl7.org/CodeSystem/adjudication.html" } ], "text": "Claimed" } }, { "amount": { "currency": "USD", "value": 80.00 }, "category": { "coding": [ { "code": "benefit", "display": "Benefit Amount", "system": "http://terminology.hl7.org/CodeSystem/adjudication.html" } ], "text": "Approved" } } ], "type": { "text": "O" }, "use": "claim" } } ] }

Patient

914 388 records

1,500478,408481,361 289 bytes

Expand
titlePatient Output (1 resource example)

{ "entry": [ { "fullUrl": "http://127.0.0.1:8000/api_fhir_r4/Patient/32B91C73-6CEC-4F28-A4B0-A3A23721B3FA", "resource": { "resourceType": "Patient", "address": [ { "text": "NA", "type": "physical", "use": "temp" }, { "text": "0.0 0.0", "type": "gps", "use": "biling" } ], "birthDate": "1964-02-19", "extension": [ { "url": "https://openimis.atlassian.net/wiki/spaces/OP/pages/960069653/isHead", "valueBoolean": true }, { "url": "https://openimis.atlassian.net/wiki/spaces/OP/pages/960331779/registrationDate", "valueDateTime": "2016-05-05T14:57:03.46000" }, { "url": "https://openimis.atlassian.net/wiki/spaces/OP/pages/960495619/locationCode", "valueReference": { "reference": "Location/AFC978A5-3895-41AB-8D6B-44A467B1338A" } }, { "url": "https://openimis.atlassian.net/wiki/spaces/OP/pages/960331788/educationCode", "valueCoding": { "code": "0", "display": "0" } }, { "url": "https://openimis.atlassian.net/wiki/spaces/OP/pages/960135203/professionCode", "valueCoding": { "code": "0", "display": "0" } }, { "url": "https://openimis.atlassian.net/wiki/spaces/OP/pages/1556643849/povertyStatus", "valueBoolean": true } ], "gender": "M", "generalPractitioner": [ { "reference": "HealthcareService/40D0D885-442D-4631-9869-C4D11BEEDB42" } ], "id": "32B91C73-6CEC-4F28-A4B0-A3A23721B3FA", "identifier": [ { "type": { "coding": [ { "code": "UUID", "system": "https://hl7.org/fhir/valueset-identifier-type.html" } ] }, "use": "usual", "value": "32B91C73-6CEC-4F28-A4B0-A3A23721B3FA" }, { "type": { "coding": [ { "code": "SB", "system": "https://hl7.org/fhir/valueset-identifier-type.html" } ] }, "use": "usual", "value": "019857364" }, { "type": { "coding": [ { "code": "PPN", "system": "https://hl7.org/fhir/valueset-identifier-type.html" } ] }, "use": "usual", "value": "NA" } ], "link": [ { "other": { "reference": "Patient/32B91C73-6CEC-4F28-A4B0-A3A23721B3FA" }, "type": "0" } ], "name": [ { "family": "Wilson 7461", "given": [ "John 6228" ], "use": "usual" } ], "photo": [ { "creation": "2016-04-07", "url": "Images\\Updated\\019857364_k114_20160407_0.0_0.0.jpg" } ], "telecom": [ { "system": "phone", "use": "home", "value": "234537061" }, { "system": "email", "use": "home", "value": "" } ] } } ] }

Location

7 953 records

14,738,149 bytes

Expand
titleLocation Output (1 resource example)

{ "entry": [ { "fullUrl": "http://localhost:8000/api_fhir_r4/Location/534C6BE0-41E4-4569-A7A5-21E228EA61F6", "resource": { "resourceType": "Location", "id": "534C6BE0-41E4-4569-A7A5-21E228EA61F6", "identifier": [ { "type": { "coding": [ { "code": "UUID", "system": "https://hl7.org/fhir/valueset-identifier-type.html" } ] }, "use": "usual", "value": "534C6BE0-41E4-4569-A7A5-21E228EA61F6" }, { "type": { "coding": [ { "code": "LC", "system": "https://hl7.org/fhir/valueset-identifier-type.html" } ] }, "use": "usual", "value": "P6" } ], "name": "Karnali", "physicalType": { "coding": [ { "code": "R", "system": "http://terminology.hl7.org/CodeSystem/location-physical-type.html" } ], "text": "region" } } } ] }

HealthcareService

226 records

697,240 bytes

Expand
titleHealthcareService Output (1 resource example)

{ "entry": [ { "fullUrl": "http://localhost:8000/api_fhir_r4/HealthcareService/8956D0ED-42D2-4E20-9B73-9EE9A604C109", "resource": { "resourceType": "HealthcareService", "category": [ { "coding": [ { "code": "H", "system": "http://hl7.org/fhir/v3/ServiceDeliveryLocationRoleType/vs.html" } ], "text": "Hospital" } ], "extraDetails": "Ilam", "id": "8956D0ED-42D2-4E20-9B73-9EE9A604C109", "identifier": [ { "type": { "coding": [ { "code": "UUID", "system": "https://hl7.org/fhir/valueset-identifier-type.html" } ] }, "use": "usual", "value": "8956D0ED-42D2-4E20-9B73-9EE9A604C109" }, { "type": { "coding": [ { "code": "FI", "system": "https://hl7.org/fhir/valueset-identifier-type.html" } ] }, "use": "usual", "value": "H0301547" } ], "location": [ { "reference": "Location/51FFC070-BB60-4CD1-BAF9-4CF5AA2F11D1" } ], "name": "IlamDistrictHospital", "program": [ { "coding": [ { "code": "G" } ], "text": "G" } ], "speciality": [ { "coding": [ { "code": "I" } ], "text": "Integrated" } ], "type": [ { "coding": [ { "code": "B", "system": "http://hl7.org/fhir/valueset-service-type.html" } ], "text": "Both" } ] } } ] }

Condition

1 959 records

3,260,375 bytes

Expand
titleCondition Output (1 resource example)

{ "entry": [ { "fullUrl": "http://localhost:8000/api_fhir_r4/Condition/46", "resource": { "resourceType": "Condition", "code": { "coding": [ { "code": "A00" } ], "text": "A00Cholera" }, "id": "46", "identifier": [ { "type": { "coding": [ { "code": "ACSN", "system": "https://hl7.org/fhir/valueset-identifier-type.html" } ] }, "use": "usual", "value": "46" }, { "type": { "coding": [ { "code": "DC", "system": "https://hl7.org/fhir/valueset-identifier-type.html" } ] }, "use": "usual", "value": "A00" } ], "recordedDate": "2016-04-03T00:00:00", "subject": { "type": "Patient" } } } ] }

ActivityDefinition

1 506 records

7,499497,776 732 bytes

Expand
titleActivityDefinition Output (1 resource example)

{ "entry": [ { "fullUrl": "http://localhost:8000/api_fhir_r4/ActivityDefinition/3B4193F7-37EA-4B6E-BD5E-87F08752526A", "resource": { "resourceType": "ActivityDefinition", "date": "2017-02-12T10:23:48.130000", "extension": [ { "url": "unitPrice", "valueMoney": { "currency": "$", "value": 100.0 } }, { "url": "frequency", "valueInteger": 0 } ], "id": "3B4193F7-37EA-4B6E-BD5E-87F08752526A", "identifier": [ { "type": { "coding": [ { "code": "UUID", "system": "https://hl7.org/fhir/valueset-identifier-type.html" } ] }, "use": "usual", "value": "3B4193F7-37EA-4B6E-BD5E-87F08752526A" }, { "type": { "coding": [ { "code": "SC", "system": "https://hl7.org/fhir/valueset-identifier-type.html" } ] }, "use": "usual", "value": "OPD1" } ], "name": "OPD1", "status": "active", "title": "OPDPHC", "topic": [ { "coding": [ { "code": "DefinitionTopic", "system": "http://terminology.hl7.org/CodeSystem/definition-topic" } ], "text": "C" } ], "useContext": [ { "code": { "code": "useContextGender" }, "valueCodeableConcept": { "coding": [ { "code": "M", "display": "Male" }, { "code": "F", "display": "Female" } ], "text": "MaleorFemale" } }, { "code": { "code": "useContextAge" }, "valueCodeableConcept": { "coding": [ { "code": "A", "display": "Adult" }, { "code": "K", "display": "Kid" } ], "text": "AdultorKid" } }, { "code": { "code": "useContextWorkflow" }, "valueCodeableConcept": { "coding": [ { "code": "O", "display": "Other" } ], "text": "WorkflowSetting" } }, { "code": { "code": "useContextVenue" }, "valueCodeableConcept": { "coding": [ { "code": "O", "display": "Out-patient" } ], "text": "ClinicalVenue" } } ] } } ] }

Medication

1 184 records

6,284,180 bytes

Expand
titleActivityDefinition Output (1 resource example)

{ "entry": [ { "fullUrl": "http://localhost:8000/api_fhir_r4/Medication/8FBDB916-7B6F-44FD-AFE0-ABE24EDF0F64", "resource": { "resourceType": "Medication", "code": { "coding": [ { "code": "A01C1R" } ], "text": "AmphotericinB50mg" }, "extension": [ { "url": "unitPrice", "valueMoney": { "currency": "$", "value": 450.0 } }, { "url": "frequency", "valueInteger": 0 }, { "url": "topic", "valueCodeableConcept": { "coding": [ { "code": "DefinitionTopic", "system": "http://terminology.hl7.org/CodeSystem/definition-topic" } ], "text": "D" } }, { "url": "useContextGender", "valueUsageContext": { "code": { "code": "gender" }, "valueCodeableConcept": { "coding": [ { "code": "M", "display": "Male" }, { "code": "F", "display": "Female" } ], "text": "MaleorFemale" } } }, { "url": "useContextAge", "valueUsageContext": { "code": { "code": "age" }, "valueCodeableConcept": { "coding": [ { "code": "A", "display": "Adult" }, { "code": "K", "display": "Kid" } ], "text": "AdultorKid" } } }, { "url": "useContextVenue", "valueUsageContext": { "code": { "code": "venue" }, "valueCodeableConcept": { "coding": [ { "code": "B", "display": "Both" } ], "text": "ClinicalVenue" } } } ], "form": { "coding": [ { "code": "package" } ], "text": "SOLUTION" }, "id": "8FBDB916-7B6F-44FD-AFE0-ABE24EDF0F64", "identifier": [ { "type": { "coding": [ { "code": "UUID", "system": "https://hl7.org/fhir/valueset-identifier-type.html" } ] }, "use": "usual", "value": "8FBDB916-7B6F-44FD-AFE0-ABE24EDF0F64" }, { "type": { "coding": [ { "code": "IC", "system": "https://hl7.org/fhir/valueset-identifier-type.html" } ] }, "use": "usual", "value": "A01C1R" } ] } } ] }

CommunicationRequest

0 records

21 bytes

Expand
titleActivityDefinition Output

{ "entry": [] }

Database cleaning for CSV extract

In case you need to extract the data from the database as comma (,) delimited CSV files, the database need to be cleaned before to remove any undesired characters (commas, new lines, etc.). For this you can use the CleanDatabase.sql script.