Power BI Insights: BISM Normalizer; PivotTable performance; Boosting joined, merged tables; Rotating reports; Determining measures

This week, Power BI pros share their insights on the BISM Normalizer, PivotTable query performance, speeding up joined and merged tables and more.

Protecting existing partitions with BISM Normalizer

Gilbert Quevalliers, writing on Four Moo, explained how to protect existing partitions in Azure Analysis Services. It's very easy to accidentally update all partitions, thus prompting a long amount of time to re-process from  large amounts of data or even errors because there is no data in the fact table.  Instead, Quevalliers opted to use the BISM Normalizer tabular compare option, selecting the option to retain partitions, as well as displaying warnings for measure dependencies such as missed DAX references. Once he applied this option, he found that the table was set to skip thus preserving important partitions.

Looking into PivotTable query performance

Chris Webb revisited a blog he wrote in 2016 about the way Excel 365 generated MDX queries. The changes back in 2016 helped to solve a large number of PivotTable performance problems. According to Webb, Microsoft partially rolled back the changes early in 2019 due to rare incorrect results, but users can still make the best of the features that remain.

Webb shared an example PivotTable connected to SSAS MD and showed how the Excel MDX query returns subtotals not shown in the PivotTable. For slow PivotTables, he recommends turning off subtotals and grand totals unless they are actually needed and checking the MDX query coming out of Excel to see if unnecessary totals are running. This should also reveal whether the table may be trying to get results in a different way than expected, such as by not drilling down on certain hierarchies.

Boosting aggregation performance

FREE Membership Required to View Full Content:

Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more. You can also receive periodic email newsletters with the latest relevant articles and content updates.
Learn more about us here

Get full access

or login