Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

dataflows: Error native query onprem oracle datasource

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

5 REPLIES 5
MatthewRoche
Employee
Employee

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
Not applicable

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
Not applicable

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.

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
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors