How to Test if Incremental Refresh is Working?

Question

I've set up incremental refresh on a dashboard linking to Athena data source. It refreshes and looks fine, but takes a little longer to refresh than the same report without incremental refresh on (which loads a full week each refresh instead of 1 day).

How can I test that it is using the incremental property on the query to the Athena data source, and is not loading it all in and only doing incremental at that point? I'm wondering if I can use the different messages during refresh eg. is only the part that says 'waiting for dsn Ahtena' relevant in terms of time it takes for this question? My main goal is to query less data more than reduce refresh time, so that I am spending less money on cloud querying. But as I can't see the amount of data the query is using, I'm using time to refresh as a proxy.

I've tried 'viewing native query' but this is greyed out. However the data source and query should support query folding, as it is SQL based and a very simple query with a string formatted date field (that is converted to date time once in PBI).

anonymous · Answer

Thanks&nbsp;amitchandak&nbsp;, the data has come through, but I'm trying to see if it's improved performance or not. ie. Was it able to *only* query the new data? Currently speed of querying is same as without incremental update so I'm trying to figure out why that is.

pthapa · Answer

I am still confused to the topic. If my dataset has a 'Posting Date' as date/time field and how do I check if incremental refresh is actually working? In other word, we just want to refresh the data within incremental refresh window and the rest of the data should be historical.What is the easy way to monitor that?Thanks for your help.pthapa&nbsp;

amitchandak · Answer

Anonymous&nbsp;
Not sure I got it. But Typically we check of lastest day weeks data has come into the system or not and match those values with source data sources values.

v-xuding-msft · Answer

Hi&nbsp;Anonymous&nbsp;,
You could enable the option of Detect data changes.&nbsp; If you have a modified DateTime (or updated DateTime) in your table, then the process of incremental refresh can monitor that field, and only get rows that their date/time is after the latest date/time in that field in the previous refresh. To enable this process, you can enable the Detect Data Changes, and then choose the modified date or update date from the table. Notice that this is different from the OrderDate or transaction date. And not all tables have such a field.
&nbsp;
All You Need to Know About the Incremental Refresh in Power BI: Load Changes Only
Power BI Incremental Refresh - Understanding Detect Data Changes&nbsp;

anonymous · Answer

Thanks&nbsp;v-xuding-msft&nbsp;, I'll give that a go. The table I'm working with (at the source) only has dates in string format - I convert these into date-time format in PBI. Will this still work in that case, or is there a way I can make it work despite the difference in formats?

Forum Discussion

How to Test if Incremental Refresh is Working?

10 Replies

Related content

Increment Refresh

Incremental refresh

Incremental Refresh

Incremental Refresh on Dataflow sourced from SharePoint

Incremental refresh