dataflows: Error native query onprem oracle datasource

[Anonymous]

Hi,

Trying to migrate a power query script that works fine in Power BI Desktop to Dataflows, when we try to refresh data we receive the following error: "Native queries are not supported. Please update your query and try again"

The datasource is an on-prem oracle database, and the query we are trying to execute is just a simple query with a couple of joins, nothing very sophisticated.

It seems that creating a view solve the problem, but we can't do that in every scenario. Did something similar happen to you?

Thanks in advance

[MatthewRoche]

HI csacristan - this feature is not yet avialanle in the dataflows preview. Please vote up on the existing feature suggestion if it is important to you.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/35025166-dataflows-from-custom-sq...

[Anonymous]

Hi Matthew,

Thanks for your answer. We didn't know about this limitation until we found it in a client's project. Hopefully it will be available in the short term because it's a pretty big limitation (as well as the fact that the one creating the dataflow has to be also the Gateway administrator, not just access to the datasource: https://docs.microsoft.com/en-us/power-bi/service-dataflows-on-premises-gateways).

BTW, that suggestion (or rather a fix) already has a couple more votes 😉

[Anonymous]

I couldn't agree more with @Anonymous, not supporting native queries basically limits me to exporting entire tables into the PBI dataset and when handling massive amounts of data plus SQL calcs, this is not what we want.

[MatthewRoche]

Hi @Anonymous - Are you running into speficic scenarios where query folding isn't producing efficient native queries when you use PQ and M to build a query? Sharing some specific context in the idea (link above) would help the Power BI team understrand the impact of this gap.

[Anonymous]

Hi @MatthewRoche - just to provide a quick example: when selecting a list of active tickets generated within the last 12 months, my database contains tickets generated since 2001. Instead of extracting 450 rows using my Oracle servers as the first layer, I have to extract a list of all tables, then more than a million rows of tickets and then apply M filtering (and a series of filters at that) to achieve the results I want.

And this is oversimplifying the issues. Sometimes my queries will contain joins, subqueries, CASE statements and aggregates that can be processed server-side for ease of implementation, only sending the data I really need for PowerBI.

I will add this to the idea.