Tuning PowerPivot Performance

PowerPivot for Excel's features for loading and combining data can be dazzling. In certain cases, these very data loading features can also lead to slow-performing PowerPivot for Excel solutions.

Slicers: Less Is More

In my opinion, slicers are the most visually unique element of PowerPivot. However, they can greatly impact the performance of report (PivotTable and PivotChart) updates. If you incur this type of performance issue in your PowerPivot solutions, here are a couple of suggestions for decreasing update runtimes.

First, endeavor to source slicers from dimension tables as opposed to fact tables in your solution. For example, consider an organization that works with a product set that contains 200 distinct products, and therefore only 200 distinct product names. The fact table for the same solution contains 500 million orders. The PowerPivot engine will more quickly update a slicer based on the 200-row product table as opposed to determining the distinct products using the 500-million–row fact table.

Second, consider eliminating the relationship between slicers. Recall how, by default, multiple slicers on the same PivotTable or PivotChart structure interrelate to visually indicate slicer tiles for which no fact data exists. While removing this feature is not applicable for all solutions, the reduction in workload for the PowerPivot engine is significant. Disabling this feature is a matter of right-clicking the slicer, choosing Slicer Settings from the context menu, and then unchecking the items highlighted in the resulting Slicer Settings dialog, illustrated in Figure 11-10.

Figure 11-10. Disabling slicer relationships

PowerPivot and SSAS Interaction

PowerPivot for Excel is an environment for developing solutions using the SQL Server Analysis Services (SSAS) in-memory runtime. Because the language for querying SSAS databases is Multidimensional Expressions (MDX), you may find it useful in your PowerPivot tuning efforts to examine the queries being sent from PowerPivot for Excel to SSAS. This tactic requires SQL Server Profiler, in order to use the trace files that are generated from PowerPivot for Excel. To begin generating trace files, follow these steps.

First, open a PowerPivot solution from Microsoft Excel. Next, select the PowerPivot ribbon menu and then the Settings selection, as illustrated in Figure 11-11.

Figure 11-11. PowerPivot Settings menu

The Settings dialog illustrated in Figure 11-12 will be displayed. Check the “Client tracing is enabled” check box, and select a location for the resulting trace file. The default location for our purposes will be fine.

Figure 11-12. PowerPivot settings

Interact with the PowerPivot for Excel slicers or alter the composition of a PivotChart or PivotTable in your solution. By doing this, several queries are sent to the PowerPivot engine (SSAS). Return to the PowerPivot settings, and disable the client tracing feature by unchecking the check box illustrated in Figure 11-12. This is very important, as otherwise all PowerPivot activity will continue to be logged to the trace file.

The trace file created by PowerPivot requires SQL Server Profiler in order to actually open and view the contents. Figure 11-13 illustrates a portion of a trace file, highlighting the Query End event.

Figure 11-13. Trace file in SQL Profiler

Combined with SQL Profiler, the client trace files can be used to determine the actual query execution times and data volumes being utilized in the PowerPivot engine.