Microsoft Office Tutorials and References
In Depth Information
Chapter 8 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
Mastering PivotTables .................................. 251
Now that you are acquainted with the DAX language and Microsoft SQL Server PowerPivot
for Excel basics, it is time to move into some advanced topics. In this chapter, you are going
to learn in deep detail most of the advanced features of PivotTables.
Some of the features we analyze are not exclusive to PowerPivot—they are Excel features that
might be applied to any PivotTable. Nevertheless, we feel that it is very important to see how
these features can improve the reporting capabilities of PowerPivot.
Please note that this chapter is quite an advanced one, so we will describe formulas and
scenarios taking for granted that the reader has sufficient knowledge of DAX and data
models. If, during some scenario, you feel the need to better understand a topic, we
suggest going back to the chapter where we have explained it, moving forward and
backward until you understand the scenario well.
understanding Different Types of PivotTables
Excel is able to handle three different types of PivotTables, which seems identical but have
some differences that are worth noting. These are the three PivotTables:
■ Classical PivotTable A PivotTable that uses an Excel worksheet as the data source.
This PivotTable has basic functionalities. The calculations are carried on in Excel, and
its main limitation is that you can have a single worksheet as the source.
■ PivotTable on an OLAP cube A PivotTable that uses an OLAP cube running
under Microsoft SQL Server Analysis Services (SSAS). This PivotTable has very powerful
functionalities that are exposed by the SSAS server. The calculations are made by the
Analysis Services server; Excel acts as an interface between the user and the server,
and it has no control over how values are computed. The functionalities of this type
of PivotTable strongly depend on the Analysis Services data model.
■ PowerPivot PivotTable This PivotTable is linked to the PowerPivot database engine. In
this case, Excel uses the internal PowerPivot engine to perform calculations. The engine
is very similar to Analysis Services, although it runs in Excel and not on a remote server.
Moreover, in PowerPivot tables, the data model is stored in the Excel workbook, and
Excel defines and maintains it.
So even if the user interface of the three PivotTables looks similar, they actually run on
completely different engines and provide very different performance and features. Let
us review the differences in higher detail.