AGENDA

• Possible to import millions of rows of data.
• Link many different tables together and create one pivot table.
• Create calculated columns and calculated fields using DAX formulas.
• Many new and powerful DAX functions (Data Analysis Expressions)
• Connect to different data sources (databases, text files Excel files etc.)
• Very efficient compression algorithms to handle very large data sets in computer memory – overcomes existing limitations for massive data analysis.

PowerPivot for Excel is a free add-in that you can use for powerful data analysis in Excel 2010 & Excel 2013. This brings “self-service” business intelligence to your personal computer. Excel PowerPivot has a window for importing and preparing data plus an additional window (PowerPivot tab) on the ribbon that you can use to manipulate your data.

Excel PowerPivot includes a wizard that you can use to import data from many different sources, such as; databases on an intranet, public data feeds, to Excel spreadsheets and/or text files on your own PC. Data is imported into PowerPivot for Excel as tables. The imported tables are shown as separate sheets in the PowerPivot tab (very similar to worksheets in an Excel workbook). Note that PowerPivot for Excel provides significantly different and enhanced functionality from what is available in an Excel worksheet.

The tables that you work on in the PowerPivot tab window is stored in a “Data Model” (an analytical database) which is inside the Excel workbook. Another very powerful feature is that the PowerPivot data tables can be connected by creating relationships between the various tables using a common field. Because PowerPivot data is in Excel, it is immediately available to PivotTables and PivotCharts in Excel.  PowerPivot supports files up to 2GB in size and enables you to work with up to 4GB of data in memory.

As well as the pivot tables & charts that help you to analyze your data, Excel PowerPivot also has Data Analysis Expressions (DAX) formulas. DAX is a new formula language that extends the data manipulation capabilities of Excel to enable more sophisticated and complex grouping, calculation, and analysis. Creating DAX formulas is very similar to creating Excel formulas, using arguments, operators and values.

Calculated fields (used to be called measures) are a very powerful feature in Power Pivot. You will definitely use them many times in data analysis. Calculated fields are usually aggregate formulas such as total sales calculated for time, region or product.

Many calculated fields are easy to create, such as those using a standard aggregation such as SUM, AVERAGE, MAX & MIN and are created automatically by using the AutoSum feature in the Power Pivot window or by simply dragging a field into the Values area (these are implicit calculations).  Other calculated fields can be more complex, such as filtering results, these require a formula you create using DAX.

As noted above, there are differences between the PowerPivot tab window and the regular Excel window in terms of how you work with data in each. There are some also other important differences that we want to call out specifically:

• PowerPivot data tables can only be saved in workbooks with the following file types: Excel Workbook (*.xlsx), Excel Macro-Enabled Workbook (*.xlsm), and Excel Binary Workbook (*.xlsb). PowerPivot data is not supported in workbooks with other formats.
• The PowerPivot window does not support Visual Basic for Applications (VBA). You can use VBA in the Excel window of a PowerPivot workbook.
• In regular Excel PivotTables, you can group data by right-clicking a column heading and selecting Group (often for dates). In PowerPivot PivotTables you have to use calculated columns to create groups.