Forum Discussion

ppvinsights's avatar
ppvinsights
Helper III
6 years ago

Using Inc Load using native SQL Query

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 upd...
Need Help
ppvinsights's avatar
ppvinsights
Helper III
6 years ago

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's avatar
ppvinsights
Helper III
6 years ago

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