how to map sql table data to FHIR server cosmos db

[rameshkr1994]

Dear All.

i am moving data from on premise sql server to Azure FHIR cosmos db server.

and we are able to move sql server data inside FHIR cosmos db server but data format is not matching with FHIR server apps.

Example :- we have patient data with sql server side and we want to move all data with FHIR cosmos db ("resource type ":"Patient")and query on it.but within FHIR sever apps/services is not able to map with my sql server data.

https://social.msdn.microsoft.com/Forums/en-US/9916c988-7606-4f45-abd9-c931257e7f15/how-to-map-sql-db-data-format-with-azure-fhir-server-cosmos-db-data-format?forum=azurecosmosdb Please help me anyone !!

Thanks HadoopHelp

[hansenms]

To use the Azure FHIR server, you should load the data through the FHIR API regardless of which backend (cosmos or SQL) you choose. You cannot simply put the FHIR server on top of data with an arbitrary schema. Search parameters will not be extracted appropriately, etc.

The link you are referencing is, I think from a cosmos DB example and it is not a proper FHIR server and not related to the Azure FHIR server.

[rameshkr1994]

Hi @hansenms .

Thanks for sharing valuable answer ! But how can we achieve this use case :- most of the health care domain company has huge data within On Premise sql server and they want to process and point apps within FHIR server ?

Note:- this use case not only for me , this can be applicable with all the health care domain who have old data with sql server and they want to use with FHIR Server Apps to access.

Thanks HadoopHelp

[hansenms]

You really have two options:

1. Transform the data to FHIR resources and the upsert it through the FHIR API and it will be in the FHIR server. This is the cleanest solution, which does not require code changes to the FHIR server, but you will need some ETL process to get the data out of the SQL server.
2. You write a persistence provider for the Microsoft FHIR server that knows the schema of the data. You can leverage much of the stack in this repo, but you will need to implement CRUD operations and search. Search can get complicated in order to satisfy the FHIR spec.

Since you would have to do some ETL process anyway, I would recommend writing something that transforms to FHIR and upoads (through the FHIR API) to the FHIR server. That way you can leverage either the OSS FHIR server or use the managed version (the Azure API for FHIR).

[rameshkr1994]

Hi @hansenms

we are able to transform data as FHIR Resource type like :- "Resource Type="Patient " all related data is available with FHIR cosmos db server but problem here is how we can make this data as Accessible through FHIR server apps. i checked through the POST MAN for POST and GET ,FHIR Server is adding some of extra id and fields with data(this data i added manual through POST MAN) .

my added data from sql sever to FHIR cosmos db server there is such kind of extra fields coming as require by FHIR serve apps .

Note:- how can we implement upsert IN this use case.(still issue is open related with upsert) https://github.com/microsoft/fhir-server/issues/579

Thanks HadoopHelp

[hansenms]

@rameshkr1994, I am struggling a bit to follow what the exact issue is. It would help a lot if you describe the problem exactly including examples. In the meantime, let me see if I can answer what I think you are saying.

As I understand it, you are saying that you convert your data to FHIR resources, i.e. JSON documents that conform to the FHIR specification but when you upsert those to the FHIR server, additional fields are added. When you do a POST to the FHIR server with a FHIR resource, we will add a new id (this is per the FHIR spec). Additional metadata (such as version, lastUpdated) is also added. If you wish to upsert the FHIR resources with a specific id, you must use PUT, e.g.:

PUT //fhirserver/Patient/1234

with a payload that includes that id, e.g.:

{
  "resourceType": "Patient",
  "id": "1234".

   // .. more fields
}

We will then preserve the id.

In terms of the issue you are referencing, I just closed it. Much work has been done since then to make it easier to implement custom persistence providers.

But I don't actually think that is what you would want to do here. You are creating FHIR resources anyway (from the on-prem SQL server) and instead of storing those in a custom cosmos DB schema and building a persistence provider for that, I would recommend you just upsert (POST or PUT as described above) them to the FHIR server and let the existing infrastructure manage search parameters, etc.

If you really want to make a new persistence provider, you can too, but you have a lot more work ahead of you.

[vinayakasy99]

@hansenms : I am facing the same issue in migrating the data from the MySql to the Fhir server of Azure. As per your recommendation I must use the upsert (POST or PUT) to upsert the data. I have almost 10,000 patient data with CCD's and HL7 files. It would be tedious to upsert these data to the FHIR server manually. Please suggest.

[rameshkr1994]

Hi @hansenms .

Thanks for being here !!

My intention is very simple :- 01:- i have data with On Premise sql server data bases and i want to point Azure FHIR server apps within the Sql server data. 02:- as Microsoft Docs suggestions FHIR server uses Cosmos DB internally for fast read and write as compare to others DB.so we are choosing here Cosmos DB for store data on top of FHIR server. 03:- we are looking for build apps/services on top of FHIR Server(Internal db is Cosmos db) and internally SHOULD use My On-Premise SQL server data. 04:- by Di-gram :

[hansenms]

@vinayakasy99: As I understand your problem, you have some data in a MySql server and now you would like a FHIR API exposing the data. Our FHIR server cannot generally pull data from an arbitrary schema, so at some point there will have to be a transform from the schema you have to a schema that will work with a FHIR server. That may or may not be tedious depending on your schema, but there is custom work to be done that relates specifically to you data schema. Once the data is transformed to FHIR, it don't see why it would be particularly tedious to upsert the data through the FHIR API. You can just dump all the resources into say blob storage and have an Azure Function run (in parallel) to upsert all the resources. 10,000 patients is not a lot, it should be very doable.

As I mentioned in a previous comment, you could also create a persistence provider that understands your particular data schema and serve it directly out of your MySql server. For the CRUD operations this may be fairly easy, but I think you will find that implementing FHIR search may be challenging.

The advantage of creating FHIR resources and upserting through the FHIR API is that all the search parameters will get extracted appropriately, etc.

[hansenms]

@rameshkr1994: I am not sure how much more I can add here. The issue remains the same that you have some custom schema and you would like the data exposed through a FHIR API, you need to either implement a persistence provider that understands your schema or you have to transform the data to a schema that the FHIR server understands.

It really doesn't matter if you use Cosmos DB or SQL server on the backend of the FHIR server (we support both in OSS), the hard part is transforming the data schema you have to one that works. I would recommend doing that by forming FHIR resources and upserting through the FHIR API to ensure that the data is valid and search parameters get extracted.

[vinayakasy99]

@hansenms : Thank you so much for taking time to write the detailed explanation. This would really help me to move ahead.

[Kedar782]

@vinayakasy99 @rameshkr1994

Do you found the solution? I have all of my data in the SQL Server. I need to transfer this data into Azure API for FHIR's cosmos DB. Any idea what will be the starting point?

@hansenms I will really appreciate your help on this. Also, I have one more problem. From my EMR we insert data into SQL server every day. Is there any way we can transfer this data also into FHIR? Can I use this repo for it? I'm using Azure API for FHIR managed server. Thanks in advance!