How to handle huge data coming from rest API.

[vikramkumawat]

Hello All,

We are using a REST API as a data source in Power BI, but the data is highly sensitive, and we are unable to get direct access to the underlying database. As a result, we cannot set up incremental refresh in Power BI.

The issue arises when the data from the API exceeds its limits, causing server performance problems. This, in turn, leads to scheduled refresh failures. Currently, we are handling the data using pagination, but this approach is not the most efficient since the data size grows over time, which leads to further issues.

How can we address this problem efficiently without direct database access? Additionally, if there is a way to set up incremental refresh for data coming from a REST API, that would be very helpful. We are looking for a more scalable solution beyond pagination. or is there any setting in power bi itself which can handle this issue.

Thank You

[Deku]

Does the API have a parameter to specify a date range to return?

[vikramkumawat]

Hello @Deku,

Thank you for your response.

Currently, the API does not support a date range filter, but we have the flexibility to add it. However, if we implement this, how can we efficiently archive older data while ensuring that the refresh process works seamlessly in Power BI? Additionally, what would be the best approach to set up and manage the incremental refresh in this scenario?

Would appreciate any guidance on this.

Thanks!

[v-menakakota]

Hi @vikramkumawat ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so other members can easily find it.

Thank you.

[Deku]

Incremental Refresh Docs

For Incremental Refresh to work you need two parameters RangeStart and RangeEnd. You can then define a Incremental Refresh Policy, specifying a time window for active partitions that will request data each refresh, and a archive window, that will not request data. Over time the active partitions will fall into the archive window. This partitions are only created once the model is published to the Power BI service. Each partition will have a start and end date that will be injected via the RangeStart and RangeEnd parameters. Therefore you need to RangeStart and RangeEnd parameters setup to filter data in the table you want incremental refresh applied too. Since you are calling the API the RangeStart and RangeEnd will likely just be appended to the URL string for the API or in the request body