FHIR Connector unable to load large datasets (Pagination and Loading Speed Broken)

[ppalacin]

Dear all,

we are using the FHIR Connector for PowerBI with our own FHIR-Server.

As described here it should work with any standard-conform FHIR-Server on the market:

https://docs.microsoft.com/en-us/power-query/connectors/fhir/fhir

However, we discovered recently that Connector seems to have problems loading larger datasets from t...

We were able to slim down the problem to bad pagination. Let me explain it in more detail:

Use Case

We want to load all Observation Resources from the Server to analyse them

To allow reproduce all steps I will take the public server of HAPI (https://hapi.fhir.org) for my example.

1. Check how many Observation Records are stored on the server

curl -L -X GET 'https://hapi.fhir.org/baseR4/Observation?_count=0'

Dear all,

we are using the FHIR Connector for PowerBI with our own FHIR-Server.

As described here it should work with any standard-conform FHIR-Server on the market:

https://docs.microsoft.com/en-us/power-query/connectors/fhir/fhir

However, we discovered recently that Connector seems to have problems loading larger datasets from the server.

We were able to slim down the problem to bad pagination. Let me explain it in more detail:

Use Case

We want to load all Observation Resources from the Server to analyse them

To allow reproduce all steps I will take the public server of HAPI (https://hapi.fhir.org) for my example.

1. Check how many Observation Records are stored on the server

curl -L -X GET 'https://hapi.fhir.org/baseR4/Observation?_count=0'

{ "resourceType": "Bundle", "id": "a50a5f53-aec9-4646-a791-700dc0b1d575", "meta": { "lastUpdated": "2021-07-09T09:57:10.553+00:00", "tag": [ { "system": "http://terminology.hl7.org/CodeSystem/v3-ObservationValue", "code": "SUBSETTED", "display": "Resource encoded in summary mode" } ] }, "type": "searchset", "total": 941732 }

So we would expect to see 941732 records in PBI

2. Let's load it via PBI

As you can see withing PBI it only returns 12405 - The question is why?

No filters are applied, just raw data.

3.  If you check the network trace u will discover that the connector stops for some non explaining reason after 20? requests.

4. Also the page size / offset seems to be static for some reason. Not paying respect to the settings of the server. Even tough its basically returned with every request not containing a given pageoffset attribute

        {
            "relation": "next",
            "url": "http://hapi.fhir.org/baseR4?_getpages=e8c53ccc-393d-4e6e-8648-8592a49c7fc8&_getpagesoffset=20&_count=20&_pretty=true&_bundletype=searchset"
        }

In case a higher page size should be used (which makes sense on fast connections) the connector should pay respect to max page size of the server but not use sth. random at all...

Don't get me wrong. I like the idea using FHIR withing PBI. Unfortunatley it is not possible for moment as not all data can be loaded to the sheets. I would really like to ask an engineer to take a look on it, after trying to explain the issue to MS

[v-robertq-msft]

Hi, 

I suggest you to go to open a support ticket and ask the technical support team for their opinion about this question.

https://community.powerbi.com/t5/Community-Blog/How-to-create-a-support-ticket-in-Power-BI/ba-p/6830...

https://powerbi.microsoft.com/en-us/support/

Best Regards,

Community Support Team _Robert Qin

[ppalacin]

Hi Robert,

thanks for your reply.

Unfortunatley we have already created a support ticket, but it wasnt very helpful eventually.

From my point of view there is a lack of understanding of the issue as well as how pagination should work the proper way. 

Creating the post in the board was my idea of having a more broad discussion on the topic, so that it might be easier to understand the core of the problem.
Maybe even some engineer of Microsoft might find time to read it. Don't get me wrong but by now I am tired of explaining the issue to people who seems to have close to zero implementation experience against a FHIR API.
However, I am glad to share my findings and insights with any other software engineer working on the component 😉

Best

Patrick

[ppalacin]

Hi @v-robertq-msft 

are there any news on it?

The support for pagination is still very limited, resulting in very long response times.

Best

Patrick