How to Test if Incremental Refresh is Working?

[Anonymous]

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).

[amitchandak]

@Anonymous 

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.

[Anonymous]

Thanks @amitchandak , 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.

[v-xuding-msft]

Hi @Anonymous ,

You could enable the option of Detect data changes.  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.

All You Need to Know About the Incremental Refresh in Power BI: Load Changes Only

Power BI Incremental Refresh - Understanding Detect Data Changes 

[pthapa]

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

[Anonymous]

Thanks @v-xuding-msft , 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?

[pthapa]

Hello Anonymous,

For incremental refresh to work, you need to have a datetime field.

If your source has string format, then change it to datetime in PBI.

Thanks,

pthapa

[ctmullins]

That's not the answer to the question.

The question - one which I also have - is:  What monitoring tools/log data do we have access to if we wish to VERIFY that the incremental refresh is indeed doing what we intend for it to do?

The "Monitor" option in the PBI Service is nearly useless.

[v-xuding-msft]

Hi @Anonymous ,

Have you resolved it?

[Anonymous]

Not yet, no - I got pulled off onto something else. Still seeing no performance improvement in time to run and not sure why.

[v-xuding-msft]

Hi @Anonymous ,

Sorry for late back. You could change the data type to Date. It will work. 

Have you implemented it? If yes, please accept the helpful answer as a solution. More people will benefit here. If you have any questions, please feel free to ask.