Analysis Services · Power BI · Power Query

View Native Query Now Works For Analysis Services Data Sources

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.

11 thoughts on “View Native Query Now Works For Analysis Services Data Sources

  1. 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

  2. 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.)

  3. 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!

  4. 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].[00O2TH664ZFGCVGJJBDFQJNUZ]}ON 0,
    . 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.

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      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.

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.