openIMIS-AI - 2. FHIR claim input data for AI
As presented in Input data model, the AI algorithm input data is represented 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, 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.
The following table represents the number of resources that are extracted from the available database. From some resources (Practitioner) we are using only the UUID identifier which is present as reference, so these JSON resources are not generated from openIMIS.
FHIR R4 Resource | Count | File size |
|---|---|---|
531 900 records | 2,713,324,028 bytes | |
{ "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" } } ] } | ||
914 388 records | 1,478,481,289 bytes | |
{ "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": "" } ] } } ] } | ||
7 953 records | 14,738,149 bytes | |
{ "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" } } } ] } | ||
226 records | 697,240 bytes | |
{ "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" } ] } } ] } | ||
1 959 records | 3,260,375 bytes | |
{ "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" } } } ] } | ||
1 506 records | 7,497,732 bytes | |
1 184 records | 6,284,180 bytes | |
0 records | 21 bytes | |
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.
Did you encounter a problem or do you have a suggestion?
Please contact our Service Desk
This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. https://creativecommons.org/licenses/by-sa/4.0/