Enabling Query Folding for Power Query of SSAS Source

[Jerry Sullivan]

I'm using Power Query to access data from an OLAP source. Some queries work, but are extremely slow. Other queries timeout after 60 minutes before completing.

I've found several articles that discuss query folding, which allows processing of the query to occur on the server instead of the local computer.

https://blog.crossjoin.co.uk/2015/01/13/a-closer-look-at-power-queryssas-integration/

https://devinknightsql.com/2016/07/03/power-bi-checking-query-folding-with-view-native-query/

https://social.technet.microsoft.co...-slow-via-powerpivot-is-fast?forum=powerquery

These articles and others all recommend checking each Applied Step of the query to see if the View Native Query option is enabled, then modifying (or moving down in the sequence) commands that are breaking the query folding.

Unfortunately, none of the steps that I have in my short test query have the Native Query option enabled.

My test query looks like this...

let
    Source = AnalysisServices.Databases("https://mydomain.com/olap/msmdpump.dll", [TypedMeasureColumns=true]),
    #"My Database" = Source{[Name="Database_Name"]}[Data],
    #"OLAP Cube1" = #"My Database"{[Id="OLAP Cube"]}[Data],
    #"OLAP Cube2" = #"OLAP Cube1"{[Id="OLAP Cube"]}[Data],
    #"Added Items" = Cube.Transform(#"OLAP Cube2",
        {
            {Cube.AddAndExpandDimensionColumn, "[Fiscal Periods]", {"[Fiscal Periods].[Fiscal Year].[Fiscal Year]"}, {"Fiscal Periods.Fiscal Year"}}
        })
in
    #"Added Items"

The Applied Steps pane displays this code in 3 steps: Source, Navigation and Added Items. None of these steps has View Native Code enabled.

The same occurs on longer queries where speed becomes an issue.

Can anyone explain why View Native Code is disabled for all steps and how I might remedy that?

 

[VBA Geek]

This may be a tough nut to crack. I am dealing with 2 Oracle servers right now from the same company, one generates Query Folding and the other is totally "unresponsive" that I had to go down the route of generating custom SQL to retrieve data. Maybe you could ask the people who manage this server?

 

[Jerry Sullivan]

Thanks for your response!

I wanted to check to see if I was missing something obvious before asking my Database Administrator (who is not familiar with Power Query) to spend time on this. He wasn't familiar with the term "Query Folding". Based on the top hits for a web search on that expression, it appears this term is usually associated with Power BI/ Power Query-although the concept of offloading processing to the server is obviously not unique to these products.

I'm not really understanding how the server would either support or not support query folding. It seems like Power Query is taking the M Language code and converting it to an MDX query (for my SSAS source) which is sent to the server. If Power Query sends MDX code that is compatible with the server, it seems like it should work regardless of whether or not the Server knows that the MDX was generated from M Code. Am I missing something there?