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

Figure 15-10: Snowflake schemas are less efficient, causing Power Pivot to perform chain lookups.
Let your back-end database servers do the crunching
Most Excel analysts who are new to Power Pivot have the tendency to pull raw data directly from the
tables on their external database servers. When the raw data is in Power Pivot, they then build calcu-
lated columns and measures to transform and aggregate the data as needed. For example, it's
common to pull revenue and cost data, then create a calculated column in Power Pivot to compute
profit. So why make Power Pivot do this calculation when the back-end server could have handled it?
The reality is that back-end database systems like SQL Server have the ability to shape, aggregate,
clean, and transform data much more efficiently than Power Pivot. Why not utilize their powerful
capabilities to massage and shape your data before importing into Power Pivot?
Instead of pulling raw table data, consider leveraging queries, views, and stored procedures to per-
form much of the data aggregation and crunching work for you. This reduces the amount of process-
ing Power Pivot has to do and naturally improves performance.
Beware of columns with non-distinct values
Columns that have a high number of unique values are particularly hard on Power Pivot perfor-
mance. Columns like Transaction ID, Order ID, and Invoice Number are often unnecessary in high-
level Power Pivot reports and dashboards. So unless they are needed to establish relationships to
other tables, leave them out of your model.
Avoid the excessive use of slicers
Slicers are one of the best BI features added to Excel in recent years. With slicers, you can provide your
audience with an intuitive interface that allows for interactive filtering of your Excel reports and
dashboards.
One of the more attractive benefits of slicers is that they respond to one another, giving you a cascad-
ing filter effect. For example, in Figure 15-11, clicking Midwest in the Region slicer not only filters the
PivotTable, but also the Market slicer responds by highlighting the markets that belong to the
Midwest region. Microsoft calls this behavior cross-filtering.