Power BI incremental refresh policy limitation

[ArturB]

Hi Guys,

I am struggeling with such problem. I have a power BI report which is configured to pull the data from REST API and has defined incremental refresh policy. API however has a limitation of pulling amount of data to 10k records and timeout limit set to 30 seconds and this cannot be changed. That's why I can only set the incremental refresh policy to daily refreshes. And here is the problem. Power BI policy has a hard limit to 120 days behind so I can only build 120 days history report on my premium capacity workspace. The worse part is that with every new day, datetime frame also moves on and data previously gathered on workspace, which becomes older then 120 days, disappear. Is there a solution for this or some workaround? Why there is such 120 days limitation? Is there a way to configure it that old data does not disappear? I've been looking for solution for a quite long time and haven't found any. The only solution which I am thinking of and which I don't really like, is to add middle layer like some database. I would like really avoid that. Any ideas?

Regards,

Artur

[ArturB]

Please read my answer again.

Setting up last 3 years causes that API is being requested in 1 year chunks in the initial dataset build, which causes API faults.  To prove it I could send yt video.

As I wrote in the first post, API is limited (!) to 10k records and 30secs timeout, so the question is: is it possile to achive some kind of solution with that setup which I got? 

The conclusion is that API is serving small portion of data and the desired resolution would be how to catch those small chunks in dataset and build on that data analysis.

Regards,

Artur

[ArturB]

Hi @lbendlin 

I've read it carefully several times but I don't see an answer for my problem here. However, this part I thought would work for me but I don't know how to access merged, historical partitions:

On my report I can only see the data from last 120 days and when datetime window moves on I am lossing day by day from m report. I don't know how to access historical days older then 120 days, which were previously on my report.

I was thinking that I could setup policy like this:

But in this case historical data are pulled monthly which ends up with API timeout.

Your last answer gave me to think so I thought that maybe I would be able to update incremental policy with ALM toolkit. I was thinking that maybe if I have such 120 days history all I could do is to change "Archive date starting" to months and this will automatically enlarge datetime window. but unfortunatelly this breaks report because it messes up partitions setup. So cannot do much in changing invremental policy.

So, now my question would be is it even possible to achive my initial goal with current setup and tools? Am I missing something? Your last answer suggest that you may know how to do it but sorry I don't see a solution and I tried many different approaches.

Regards,

Artur

[lbendlin]

Let's ignore the "incremental"  part for a second and think of it as a standard SSAS cube. You can create whatever partitions you want, with any type of granularity.  You can then individually refresh these partitions. You can merge these partitions.  Now what I don't know is if the partition merge requires a data pull. If it doesn't then you can create your historical partitions in whatever size is still small enough to not time out, and then merge them into larger historical  partitions for  consumption.  

[lbendlin]

Power BI policy has a hard limit to 120 days

I am not aware of such a limit.  Did you mean 120 partitions? But even that is not a limit mentioned anywhere.  Maybe you are trying to store daily partitions?  If so then change that to monthly, quarterly or yearly.

Advanced incremental refresh and real-time data with the XMLA endpoint in Power BI - Power BI | Micr...

For example - you can choose to store the last 10 years and refresh the last one day.  Power BI service will automatically maintain and consolidate the required partitions.

I think your bigger issue is the passive-aggressive behavior of the API.  You may want to consider a different process to fetch the data (via Power Automate, or PowerShell)  to make sure you don't miss any data.

[ArturB]

Hi @lbendlin

Thanks for reply.

Yes, exactly I am trying to set it up using daily partitions. Because of API data size and timeout limitations I could only set up daily refreshes. That's why I was wondering if there is some easy workaround for this because there is no option to increase days behind longer then 120 days.

From my point of view such limitation is really annoying - older data should be not erased but be still visible on reports. Older data, instead of of being deleted, should be merged into some bigger partitions like weekly or monthly and still be available since those were already transformed and recalculated. Deleting it is such a waste.

As I already wrote, in my current setup I cannot use monthly refresh because of API limitation of data size and timeouts. However even if that would be possible I my opinion setting it to monthly refresh would be very inefficient since only data from last day or two are being changed. So for example, on 30th day of some month, pulling again mostly the same, not changed data doesn't make sense. Better option would be to still have a daily refresh strategy but storing it in monthy partittions. But, as I suppose, that's not possible withing Power BI.

[lbendlin]

Please read the article again, and then set your incremental refresh to store the last three years and to refresh the last day.

[ArturB]

Please read my answer again.

Setting up last 3 years causes that API is being requested in 1 year chunks in the initial dataset build, which causes API faults.  To prove it I could send yt video.

As I wrote in the first post, API is limited (!) to 10k records and 30secs timeout, so the question is: is it possile to achive some kind of solution with that setup which I got? 

The conclusion is that API is serving small portion of data and the desired resolution would be how to catch those small chunks in dataset and build on that data analysis.

Regards,

Artur

[lbendlin]

Store these replies as Parquet files and then ingest the Parquet files into a dataset.