SQL Query / Native Query Refresh issue

[Imrans123]

Hey all, 

Was wondering if anyone would have a fix or suggestion with the following problem I am facing. 

For background information, I have multiple SQL Database Sources and for each source, I have a dataflow tied in with the gateway where the SQL Database is installed.

I initially used to bring in the raw tables and transform the data from the SQL source to the format that I require (it is the same format for all sources) using power Query. Since they are all the same format after transformation, I append the queries in a separate dataflow and use the appended dataflow as my PBI report source.

I recently started using Native Query to query in data from my SQL database onto Power Query in the format that I want rather than using Power Query transformations which works much faster. However, if for whatever reason, in the scheduled refresh, if the dataflow is unable to refresh for reason like the data gateway being off etc., then the data for that sql source disappears from the report after refresh. Whereas in my old method, if there was a refresh error, I would still retain the data that was there in the last sucessful refresh.

Was wondering if anyone had the same issue and how it was addressed.

[Anonymous]

Hi @Imrans123 ,

Have you ever successfully configured gateway or something else to make the refresh work? 

Or how about try Incremental refresh?

An overview of Power BI Incremental Refresh

Natives Queries or ODBC with Incremental Refresh in Power BI

Configure incremental refresh and real-time data for Power BI datasets - Power BI | Microsoft Docs

Best Regards,
Eyelyn Qin

[Imrans123]

Hi Eyelyn, 

I have sucessfuly configured the gateway and the scheduled refresh works exactly the way I want it to. The only issue is when I cannot refresh for issues outside of Power BI. Say for example if the gateway is not connected to the internet, or off, or something of that nature.

In that case, the data from that gateway disappears from the appended dataflow. Whereas before I started using Native Query, if the data flow refresh failed, the appended dataflow would still retain the data that was there in the last successful refresh. 

I prefer not to use incremental refresh because my previous data changes and I don't have a modified date field.