Query folding with Oracle

[Anonymous]

I'm pulling in data from an Oracle database and tracking down when the "View Native Query" is greyed out.  I also believe that my transformations to the Oracle query are preventing scheduled refresh to work when published.  

My first step:

2nd step:

is using the Value.NativeQuery and inserting my SELECT FROM query. In this screenshot, I removed my entire query so I don't have to color it out -  it involves joins with other tables and where clauses.

After this, I have a few transformations. The "view native query" is already greyed out on the 3rd step.  

Is this fixable or not necessary to worry about?

[collinsg · Accepted solution]

Good day @Anonymous ,

This Microsoft article may answer your question, unfortunately not with the answer you would like "Query folding on native queries" - it is dated 17 Feb 2023 and suggests these data connectors are supported using the parameter "EnableFolding" set to true (but, unfortunately Oracle isn't listed): Amazon Redshift, Dataverse (when using enhanced compute), Google BigQuery, PostgreSQL, SAP HANA, Snowflake, SQL Server. 

There are a couple of other articles I read in the past which may be of interest (though not with respect to Oracle) Chris Webb covers the topic in his blog article "Query Folding On SQL Queries In Power Query Using Value.NativeQuery() and EnableFolding=true". Patrick of Guy in a Cube has a video on the same subject "Enable Query Folding for Native Queries in Power Query / Power BI". Patrick speaks of it specifically in reference to Desktop.

Folding may not matter, as you suggest, if your query refresh performance is already adequate. Or...since Dataverse is supported you could consider a staging query (with no or few transformations) to get the data into Dataverse quickly and then take it from there. This introduces two hops but could end up quicker overall.

Hope this helps.

[BA_Pete]

Hi @Anonymous ,

If you want to maintain folding while using the Value.NativeQuery function, you can add the [EnableFolding=true] argument, like this:

Value.NativeQuery(
    previousStep,
    "select * from table etc...",
    null,
    [EnableFolding=true]
)

However, I would personally steer clear of using native queries and would do everything in Power Query via folding.

Pete

[Inácio]

I fot this erro:
Expression.Error: 'EnableFolding' is not a valid expression for this function. the valids options are :(None)