Using Inc Load using native SQL Query

[ppvinsights]

Hi Community

I want to use Incremental Load for an ODBC-Query. Unfortunately Query Folding is not supported. But why is it not possible to create a sql query like

"SELECT * FOR..... WHERE updateTS >= " & RangeStart & " AND updateTS < " & RangeEnd

I found a lot of blog entries where it is just mentioned, that if using native sql queries IncLoad is not supported anymore (due to the missing query fodling). But creating my own query - it should work. Or am I wrong?

Thanks a lot

Holger

[Mariusz]

Hi @ppvinsights 

Writing explicit SQL brakes query folding, but you should be able to recreate this statement by using column filters and later adjusting the code to replace ranges with variables.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[ppvinsights]

Hi @Mariusz ,

thanks for your answer. I know this is breaking query folding. But the source is not able to support query folding anyway. So IMHO I have no chance to use IncLoad using query folding. But I can create a dynamic SQL statement using "RangeStart" "RangeEnd" as parameters. Those parameters are injected by Power BI Service. So I do not understand why it shouldnt be possible using dynamic SQL queries with IncLoad. Am I missing anything?

Thanks

Holger

[ppvinsights]

Hi again,

I tried a little bit with a small report and a native query - and I have strange behaviours.

To connect to my datasource I have to use an ODBC-connector. The connector is not able to "fold" any filter criterias - so filtering for RangeEnd/RangeStart is not possible. So I build my own SQL-statement dynamically (using RangeStat/End in my sqlQuery).

Now the problem is, that my native query changes every time it is executed - because RangeStart/RangeEnd is changing. So after publishing to PowerBIService I get strange errors (something like "credentials needed"). If I remove the RangeStart/End filter and publish the same report to the service everything works fine. So I do not have any problems with credentials.

In PowerBI Desktop I always have to confirm the native query, after changing the parameters. So I deactivated the option in PowerBIDesktop to allow native queries without user request. Again I published the report (I forgot to configure Incremental Load in the IncLoad dialog). Everything went fine. I yould configure the gateway in PowerBIDesktop. But I saw the two parameters "RangeStart/End" in the PBIService - which shouldnt be visible if configured for IncLoad. So I opened my PBIDesktop again, configured the IncLoad parameters and published again. 

And now: There is not datasource which I could configure using the GW. PBI-Service tells me, that there are only cloud datasources included.

For sure I made the test vice versa. I deactivated IncLoad, published again and...the datasource to connect via GW is back again...

I do not understand, why this is not working. Anyone a clue?

Thanks

Holger