Hi,
I’ve created a dataset with incremental refresh. I’ve set up everything, resolved all errors that have shown up, but now there is nothing incremental about the data load in Power BI service. It just does a normal import data load instead.
Here is what I did:
Configure query in Power BI desktop with RangeStart and RangeEnd datetime parameters to filter the date column
Initial data load to Power BI Desktop
Configure incremental refresh for that table in Power BI Desktop to load new data for 1 day (which hopefully is today and not yesturday or today and yesturday!?) and keep data for last 10 years
Publish to Power BI service: Now the same data that I have in the pbix file is available in the service, which is what I wanted.
Setup gateway and credentials for data source
Setup daily scheduled refresh for dataset
Run a manual refresh: Now all old data is deleted and only todays data is available (if RangeStart in the pbix file includes today, otherwise even no new data is loaded!)
So far, no errors, everything looks fine. Now, when I run a refresh of this dataset, again no errors. But Power BI Service does an ordinary import refresh, no difference between normal import in incremental: Power BI deletes all data and refreshes the query, even applying the RangeStart and RangeEnd values from the file, not the current ones according to incremental refresh settings and today’s date. There is no dynamic use of these parameters by Power BI service.
Does anyone have an idea how to fix this? I’ve attached the pbix file. Corona Deutschland 2020-03-21.pbix (106.9 KB)
Thank you very much in advance!
Hi @Melissa,
thank you for having a look at this issue!
My understanding of the Microsoft documentation is a bit different. It says: “In such cases [no query folding], the mashup engine compensates and applies the filter locally, which may require retrieving the full dataset from the data source.” But I know that the data source will never return more than 16 records because I’ve built it So this shouldn’t cause a problem.
Technically, the data source for the incremental load is the data flow, which wraps the necessary date column around a web-source.
Now I have read twice through the Micrososft documentation, and maybe this is what happens and it would explain the problem at least partially:
Power BI recognizes that the data source does not support query folding.
Power BI does a data load an considers this data load to be a full data load (which is not the case with my data source, because it does not keep data from past days in the source)
Since Power BI thinks it does a full load anyway it throws away all old data.
It applies the defined filters to the “considered as full” data: start 10 years ago, end today. But since the latest load only has data from today the result is - voilà: only data from today
What still makes me wonder the most is that the refresh behaves differently, depending on what value is set for RangeStart in the file. But that should not be the case. Power BI Service should apply the RangeStart date dynamically, depending on the current date. But anyway, I might have to look for a different solution.
My goal is to append daily new records, enriched with the current date (which currently is done by my data flow), to a table with the exisiting records of the past. The data source only keeps values of the day, no older values. So a full load every day is not an option because full data is not avialable in the source anymore the next day. Do you have any other strategy in mind for solving this in Power BI? Of course I could use Data Factory to assemble the table I want in Azure Analysis Services and load it from there into Power BI. But for me this seems like too many tools for a pretty small problem.
Best regards,
Martin
Hi @Martin, we’ve noticed that no response has been received from you since the 22nd of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
So this shouldn’t cause a problem.