Publishing Your BI Tools to SharePoint - Microsoft Business Intelligence Tools for Excel Analysts - page 301

Figure 15-11: Slicers work together to show you relevant data items based on your selection.
As useful as slicers are, they are, unfortunately, extremely bad for Power Pivot performance. Take a
moment to think about this. Each time a slicer is changed, Power Pivot must recalculate all the values
and measures in the PivotTable. In order to do that, Power Pivot must evaluate each tile in the
selected slicer and process the appropriate calculations based on the selection.
Take that a step further and think about what happens when you add a second slicer. Because slicers
cross-filter, each time you click one slicer, the other slicer also changes, so it's almost as if you clicked
both of them. Power Pivot must respond to both slicers, evaluating each tile in both slicers for each
calculated measure in the PivotTable. So adding a second slicer is tantamount to doubling the pro-
cessing time. Add a third slicer, and you have tripled the processing time.
In short, slicers are bad for Power Pivot performance. However, the functionality slicers bring to Excel
BI solutions is too good to give up completely.
The following is a list of actions you can take to mitigate any performance issues you may be having
with your Power Pivot reports:
➤ Limit the number of slicers in your report. Remove the slicers one at a time, testing the
performance of your Power Pivot report after each removal. You'll often find that removing
one of your slicers is enough to correct performance issues.
➤ Only create slicers on dimension fields. Consider building slicers using the fields in smaller
dimension tables — not the considerably larger fact tables in your model. Note that slicers
with many tiles often cause a larger performance hit than those containing only a handful of
tiles. Avoid slicers that contain a large number of tiles, opting instead to use a PivotTable
Filter drop-down menu instead.
➤ Remove slicers that aren't clicked very often. Some slicers hold filter values that frankly
aren't touched very often. For example, you may have a slicer that allows users to select cur-
rent year or last year. If the last year view is not often called up, consider removing the slicer
or using PivotTable Filter drop-down menu instead.