Incremental Refresh when using a native SQL query

[chad_oge]

Hello,

I am executing a custom native SQL query to a PosgreSQL database through an ODBC connection.  I am trying to apply Incremental Refresh to the results of this query.  I have read query folding is not possible when starting with a custom native SQL query.

But, when applying the Incremental Refresh steps and posting to PowerBI service, the inital dataset load takes around 5 minutes, and every refresh after takes only 20 seconds.  Is this not some confirmation the Incremental Refresh is working?  It defitnetly does not seem to be reloading the entire dataset again.

Can anyone provide me some assistance with this?  

Thank you.

[edhans]

Does your report have all of the data?
It may be that it is cached on your server so it runs faster the second time.

You can use folding with PostgreSQL if you are using the PostgreSQL connector. Not sure about the ODBC connector. See this blog article for enabling query folding after you have entered a manual query in the advanced section.
You can also check the server profiling for your server. See what query it is processing. It is the native query you typed in and that is it, or is it also filtering the data based on date ranges? If folding is happening, you will see filters on the date being inserted by Power Query.

[chad_oge]

Thank you for the reply.  I may try to solve my issue with creating a database view and importing the view into PoweBI.

[edhans · Accepted solution]

I agree @chad_oge . I almost never use the advanced query box. Use M code that folds or set up a view. 

If this thread is resolved for you, please consider marking one or more answer as "the solution"