Power BI Incremental refresh
When using ODATA with something like PowerBI, you may need to consider performance as a part of your solution.
When connected to ODATA, PowerBI will by default paginate through the results and download the whole dataset.
When starting with TilliT, this may not be a problem since your database is still small. But after a few months or years your data will grow and you will start to incur in costs and delays when trying to keep that report updated.
The best practice here is to implement a thing called incremental refresh.
This is a feature that Power BI provides, but it needs to be configured when building the report.
1 - Setup RangeStart and RangeEnd parameters
First thing you will have to do is to create two DateTime parameters. These have to be named RangeStart and RangeEnd. This will only work if they have this exact name.
You should be able to share these parameters across all queries.
2 - Editing the query
Now edit your query using Advanced Editor, and adapt it to add a filter on the updatedAt column like this:
let fromDate = DateTime.ToText(RangeStart,"yyyy-MM-dd"),
toDate = DateTime.ToText(RangeEnd,"yyyy-MM-dd"),
Source = OData.Feed("https://tenant.tillit-stage.cloud/api/odata/Orders? $filter=updatedAt gt @from AND updatedAt lt @to", null,
[Implementation="2.0", Query = [#"@from" = fromDate, #"@to" = toDate]])
in Source
It's important to extract the filters using this @ format; otherwise, PowerBI will complain it is a dynamic query and won't allow you to refresh from the cloud services.
3 - Configure incremental refresh
Close PowerQuery, and in your query list, right-click your query and click Incremental refresh:
From here you should activate the incremental refresh for your query, and set a time horizon for data archive and a time horizon for your refresh.
Pay attention to the review portion:
Pay attention to the review portion:
In this example, data older than 3 years would be deleted from Power BI (not TilliT!)
Data from the last 5 days would be refreshed on every scheduled refresh.
Data between 3 years and 5 days would not be touched by the refresh process.
4 - Configure your scheduled refreshes
Finally, Publish your query to Power BI Services. Find your data source and configure your scheduled refreshes.
Last updated
