If you’re familiar with the topic of query folding in Power Query, you’ll know that the View Native Query right-click option in the Applied Steps pane of the Power Query Editor can be used to show the native query that is run against the data source. You may also know that there are some data sources where query folding does take place but where View Native Query remains greyed out. One of those used to be Analysis Services, but the good news is that that is no longer the case: you can use View Native Query when importing data from Analysis Services! Look:
I’m told it also now works for SAP BW, but I haven’t tested it.
It also works for Salesforce Objects, at least for some basic transforms like filters and row limits.
Just confirming that query folding works for SAP as well. Seeing the native SAP BW code will also make it easier to have a good communication with the SAP technicians
It would be great to see this feature with Spark/Databricks connector!
It’s greyed out for MS SQL Server queries for me!? Even though all steps (aside from Source) show the gear. Source is greyed out (presumably) because it’s a reference query to my raw data query. Is there no way around this other than to re-apply all the steps onto the raw data query (where View Native is not greyed out.)
I was barking up the wrong tree. I had a Transformation to Date Only in one of the reference queries. Turns out that breaks the chain, and using Change Type – Date preserves it!
Hey Chris. Thank you for describing this feature, it was very helpful for me. Right now I’m trying to get data from SAP BW through the power BI connector. (it’s a very large table) And my query looks like this: SELECT{
[Measures].[00O2TH664ZFGCS73QHRU9IQ2Z],
[Measures].[00O2TH664ZFGCVGJJBDFQJNUZ]}ON 0,
NON EMPTY CROSSJOIN(CROSSJOIN(CROSSJOIN(CROSSJOIN(
DISTINCT([0COMP_CODE].[LEVEL01].ALLMEMBERS), DISTINCT([ZMMTRGR01].[LEVEL01].ALLMEMBERS)),
DISTINCT( [0MATERIAL__ZCR_CODE].[LEVEL01].ALLMEMBERS))….
. I’m confused by the presence of many crossjoins in this query.(in full query is 6 crossjoins) Does this affect query performance? Is it possible to replace the crossjoin expression with something else? I can’t get data from SAP BW due to out of memory error. There are no other steps except loading data into power query.
Don’t worry about the crossjoins – that’s the normal way you put multiple dimensions on an axis in MDX. Are you requesting a lot of data? That’s more likely to be the problem.