Using the example from the previous post, a PivotTable connected to SSAS MD and the Adventure Works cube (the queries generated for SSAS Tabular, Power Pivot and Power BI may be slightly different and slightly better but the basic problem is the same), if you build the following in Excel: The good news is that you should still be able to get the improved performance with a few minor tweaks. Unfortunately, earlier this year these changes had to be partially rolled back because in some rare cases the queries generated returned incorrect results this means that you may find that values for subtotals and grand totals are again being returned even when they aren’t being displayed. I know it sounds boring and not something you need to worry about but trust me, this is important – these changes solved the vast majority of Excel PivotTable performance problems that I encountered when I was a consultant so you should read the above post before continuing. If your users need to do custom grouping in a report that is usually an indication that you have missed something in your dimension design, and that an extra attribute hierarchy or two is necessary.Back in 2016 I wrote the following blog post about changes to the way Excel 365 generated MDX queries for PivotTables connected to Analysis Services, Power Pivot/the Excel Data Model and Power BI datasets: Yes, your users will lose some functionality and some reports might even break, but you will also save yourself and your users a lot of problems.
My opinion is that it’s a good idea to disable session cubes and custom grouping even if you don’t know whether your users are using these features.
Setting this property to 0 will prevent users from creating session cubes and therefore prevent them from using Excel’s custom grouping functionality. Once the CU has been installed the following new property is added to the msmdrv.ini file: First of all, I encourage you to read the details of the fix because you will probably want to install the relevant CU for security reasons anyway: Up until now it was impossible to prevent users from using custom grouping but in a recent cumulative update this changed. This post from a few years ago gives some more details about why custom grouping, and the CREATE SESSION CUBE MDX statements that it uses behind the scenes, are so expensive. In my experience it is a major cause of query performance problems – not just for the person running the query, but because it is so resource intensive for everyone else trying to query the cube too. The Excel PivotTable grouping functionality that is available when you are connected to an SSAS Multidimensional cube (but not a Tabular model) is a Very Bad Thing indeed.
0 kommentar(er)
