Some good news: an important optimisation has rolled out for Excel PivotTables connected to Power BI semantic models! Back in 2019 I wrote about a very common problem affecting the MDX generated by PivotTables connected to Analysis Services where, when subtotals are turned off and grand totals are turned on, the query nevertheless returns the subtotal values. This led to extremely slow, expensive MDX queries being run and a lot of complaints. The nice people on the Excel team have now fixed this problem and PivotTables connected to Power BI semantic models generate MDX queries that only return the values needed by the PivotTable.
Here’s an example of a PivotTable connected to a published Power BI semantic model:
Note that the subtotals have been turned off but the grand totals are displayed – this is important. Here’s the MDX query generated for this PivotTable:
SELECT NON EMPTY
{ /* GTOPT-BEGIN CSECTIONS=2 */
/* GTOPT-SECT-BEGIN-1 Desc:GrandTotal */
{([Property Transactions].[New].[All],[Property Type].[Property Type Name].[All])}
/* GTOPT-SECT-END-1 */
,
/* GTOPT-SECT-BEGIN-2 Desc:Detailed */
{Hierarchize(CrossJoin({[Property Transactions].[New].[New].AllMembers},
{([Property Type].[Property Type Name].[Property Type Name].AllMembers)}))}
/* GTOPT-SECT-END-2 */
} /* GTOPT-END */
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS
FROM [Model]
WHERE ([Measures].[Count Of Sales])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGSAnd here’s what this query returns:
There are 11 values displayed in the PivotTable and the MDX query returns 11 values. It’s what you’d expect but as I said, up to now, Excel would have generated an MDX query that returned 13 values – a query that also requested the subtotal values that aren’t displayed.
This optimisation should now be rolled out to 100% of Excel users. You can tell if you are using the new query pattern by looking for comments in the MDX code with the text “GTOPT” in – they’re easy to spot in the query shown above. Right now the optimisation only happens for PivotTables connected to Power BI semantic models but I’ve been told that in future it should also happen for PivotTables connected to Azure Analysis Services and SSAS; this is because some server-side optimisations are necessary to make the new MDX perform as well as possible.
You might be thinking that, despite my excitement, this is a very niche scenario but I assure you it’s not: Excel users very frequently create PivotTables that are formatted to look like tables, and having subtotals turned off and grand totals turned on is a key part of this. The more fields that are put on rows the more subtotals there are to calculate and the more the overhead increases; it’s not uncommon to find situations where the number of subtotal values is much greater than the number of values actually displayed in the PivotTable.
This doesn’t solve all the performance problems associated with PivotTables and Power BI though and more work is planned for the future.
[Thanks to Yaakov Ben Noon for driving this work!]
Amazing. I agree that this is not niche at all. So grateful for this. I would love to see other improvements for excel pivot tables connected to semantic models. We are a 20k person org and use this as part of our self service platform. For example if I have invoice number with 3 million distinct values it takes for ever (15 seconds) to open the filter in a pivot table. BUT in power bi it only takes 1 or 2 seconds. In other words… Filtering any column with high cardinality in power bi is still relatively fast but doing this for any column with high cardinality in excel pivot is painfully slow. It’s the whole experience. When I click on the drop down the filter selection window takes 15 seconds to load before I can even search for an invoice number to filter on.
One additional point. We used to use multi dimensional cube and we did not have this problem. It seems to be a problem with semantic models connected to a pivot table in excel.
This is a change in the right direction, Chris! And this is certainly not a niche scenario. My users’ productivity depends on leveraging Excel Pivots atop very powerful PowerBI Data Models we have built. This is their way of working, and is a testament of how resourceful our users are in leveraging a powerful tool (a weapon) called Excel. Eagerly looking forward for further optimizations.
Is there a reason why Excel Pivot Tables, when connected to Models cannot sent DAX quaries instead of MDX? At least it should be an option to select in preferences
Is there a reason we should send DAX quaries instead of MDX?
If you like you could use a Power Query import to send DAX to the service, an then present the PQ data to the end user.
MDX is very well suited to the pivot table requests. If they thought DAX was the better of the two, they would have made the transition for their own sake.
There’s no reason why it wouldn’t be possible but it would be a lot of work and it wouldn’t enable any new functionality (apart from possible performance gains), which is why it hasn’t been done yet. In fact PivotTables running on DAX would lose functionality: named sets and calculated members would not work.
I’ve connected Google Sheet to Power Bi and the refresh fails every now and then.
Is the situation any better with Excel? (the online version I mean)
I have a bevy of Finance department users that are going to be very excited about this! Looking forward to further optimizations in the future.
Cheers. There are other features in Excel that aren’t considered niche IMO, yet they don’t always get TLC they need. Like the “analysis” ribbon stuff in excel : sets and calculations. I really hoping they won’t break those things as they push ahead with the DL-on-OL. Shouldn’t have to sacrifice one of these for the other, since they are both mission-critical.
Wow, we have been waiting for this for years!
Hey, thanks – this comes up really frequently and has for years – it is fantastic to see development effort going into this issue!
I’m pretty sure though while recently trying to fix an issue with Excel Pivot tables connected to a Power BI Semantic Model, I saw the GTOPT code already and I was curious what it was all about, however the Pivot Table still needed some work to get it to run.
The unfortunately reality is that users do use Excel as a tool to dump data from semantic models. We do what we can on this end to discourage it… In the past sometimes I have worked around the performance issues of Excel generated MDX by handcrafting DAX queries and embedding them into Excel connections as a nuclear workaround.
“From Excel you came, and into Excel you shall return”..
Just give us back Multidimensional OLAP and MDX 🙂 I am still struggling to adjust my mind to master the “tables”, “relationships” and “filters” over much more logical and understandable “dimensions”, “members”, “hierarchies”, “levels” etc. Most importantly, “calculation groups” still is very artificial and limited construction over the MDX calculations and scoped MDX assignments.
Thanks once again for staying on top of this.
Your post here is what encouraged me to debug MDX problems against tabular (now Power BI).
https://blog.crossjoin.co.uk/2011/10/07/excel-subtotals-when-querying-multidimensional-and-tabular-models/
I dumped the MDX and filed a report with Microsoft that confirmed the issue.
For everyone else, the workaround if you hit that issue is to turn off OLAP Server Formatting under Connection Properties
Thank you Prasad!
Disabling OLAP Server Formatting saved my life!
Is it there dependency with M365 Excel version?
Hi Chris (and community),
I’m testing the recent Excel Pivot optimization (GTOPT) for PivotTables connected to a Power BI semantic model, but I’m not seeing any change in the generated MDX.
Setup:
* Excel 365
* PivotTable via Analyze in Excel
* Premium workspace (XMLA enabled)
* Subtotals OFF, Grand Total ON
* Simple Year + Year-Month hierarchy
* Measure: sum of fact table (classic)
I captured the query using DAX Studio (All Queries) and still get a classic pattern with:
“`
Hierarchize(CrossJoin(…ALLMEMBERS…))
“`
No `GTOPT` markers, no structural differences, and performance looks unchanged.
Is there a specific Excel build, pivot configuration, or additional condition required for the optimization to trigger?
Thanks in advance.
I just heard that rollout was temporarily delayed to fix a bug, so maybe wait a week or two and see if you see the new query pattern before trying again
OK let’s see, thanks a lot !