Excel Power Pivot - Features

The most important and powerful feature of Power Pivot is its database − Data Model. The next significant feature is the xVelocity in-memory analytics engine that makes it possible to work on large multiple databases in a matter of few minutes. There are some more important features that come with the PowerPivot Add-in.

In this chapter, you will get a brief overview of the features of Power Pivot, which are illustrated in detail later.

Loading Data from External Sources

You can load data into Data Model from external sources in two ways −

Load data into Excel and then create a Power Pivot Data Model.

Load data directly into Power Pivot Data Model.

The second way is more efficient because of the efficient way Power Pivot handles the data in memory.

For more details, refer to chapter − Loading Data into Power Pivot.

Excel Window and Power Pivot Window

When you start working with Power Pivot, two windows will open simultaneously − Excel window and Power Pivot window. It is through PowerPivot window that you can load data into Data Model directly, view the data in Data View and Diagram View, Create relationships between tables, manage the relationships, and create the Power PivotTable and/or PowerPivot Chart reports.

You need not have the data in Excel tables when you are importing data from external sources. If you have data as Excel tables in the workbook, you can add them to Data Model, creating data tables in Data Model that are pnked to the Excel tables.

When you create a PivotTable or PivotChart from Power Pivot window, they are created in the Excel window. However, the data is still managed from Data Model.

You can always switch between the Excel window and Power Pivot window anytime, easily.

Data Model

The Data Model is the most powerful feature of Power Pivot. The data that is obtained from various data sources is maintained in Data Model as data tables. You can create relationships between the data tables so that you can combine the data in the tables for analysis and reporting.

You will learn in detail about the Data Model in the chapter − Understanding Data Model (Power Pivot Database).

Memory Optimization

Power Pivot Data Model uses xVelocity storage, which is highly compressed when data is loaded into memory that makes it possible to store hundreds of milpons of rows in memory.

Thus, if you load data directly into Data Model, you will be doing it in the efficient highly compressed form.

Compact File Size

If the data is loaded directly into Data Model, when you save the Excel file, it occupies very less space on the hard disk. You can compare the Excel file sizes, the first one with loading data into Excel and then creating the Data Model and the second with loading data directly into the Data Model skipping the first step. The second one will be up to 10 times smaller than the first one.

Power PivotTables

You can create the Power PivotTables from Power Pivot window. The PivotTables so created are based on the data tables in the Data Model, making it possible to combine data from the related tables for analysis and reporting.

Power PivotCharts

You can create the Power PivotCharts from Power Pivot window. The PivotCharts so created are based on the data tables in the Data Model, making it possible to combine data from the related tables for analysis and reporting. The Power PivotCharts have all the features of Excel PivotCharts and many more such as field buttons.

You can also have combinations of Power PivotTable and Power PivotChart.

DAX Language

The strength of Power Pivot comes from the DAX Language that can be used effectively on the Data Model to perform calculations on the data in the data tables. You can have Calculated Columns and Calculated Fields defined by DAX that can be used in the Power PivotTables and Power PivotCharts.