| Developer(s) | Microsoft |
|---|---|
| Operating system | Microsoft Windows |
| Type | OLAP,Data analysis,Business intelligence |
| License | Microsoft EULA |
Power Pivot, formerly known asPowerPivot (without spacing), is a self-servicebusiness intelligence feature ofMicrosoft Excel which facilitates the creation of a tabular model to import, relate, and analyze data from a variety of sources.
Power Pivot extends a local instance ofMicrosoft Analysis Services tabular that is embedded directly into an Excel workbook, facilitating the creation of aROLAP model inside the workbook. Power Pivot supports the use of expression languages to query the model and calculate advanced measures.Pivot tables or pivot charts may be used to explore the model once built.
It is available as an add-in in Excel 2010, as a separate download for Excel 2013, and is included by default since Excel 2016. The data modelling engine inside Power Pivot is shared acrossMicrosoft Power BI andSQL Server Analysis Server (SSAS), and may be referred to asxVelocity,VertiPaq,SSAS Tabular, andPower Pivot.[1]
Prior to the release of Power Pivot, the engine for Microsoft's Business Intelligence suite was exclusively contained withinSQL Server Analysis Services. In 2006, an initiative was launched by Amir Netz of theSQL Server Reporting Services team at Microsoft, codenamed Project Gemini, with the goal of making the analytical features of SSAS available within Excel.[2]
Power Pivot first appeared around May 2010 as part of theSQL Server 2008 R2 product line. It included "Power Pivot for Excel" and "Power Pivot for SharePoint"[3] While the product was associated with SQL Server, the add-in for Excel could be used independent of any server, and could connect to various types of data sources. This version was superseded with an update forSQL Server 2012. Along with this the Power Pivot add-in was made available as a free download for Microsoft Excel 2010.[4]
Power Pivot 2013, released along with Excel 2013, was initially released only with the Professional Plus version of Office 2013, only available to volume or subscription licensing.[5] This was revised to eventually include Power Pivot with Excel 2013 standalone. Unlike with Excel 2010, there was no version of Power Pivot that could be independently downloaded and added to Excel 2013. As part of the July 2013 announcement of the newMicrosoft Power BI suite of self-service tools, Microsoft renamed PowerPivot as "Power Pivot" (note the spacing in the name) in order to match the naming convention of other tools in the suite.[6]
Power Pivot 2016 was released with Excel 2016 and was additionally included in the Pro version of Office, in addition to the standalone and Professional Plus versions available in 2013.[2] In April 2018 an update was released to add Power Pivot to all Excel 2016 SKUs.[7] Beginning in 2016, "Excel Data Model" began appearing as a new name for the Power Pivot model, though many references to the Power Pivot name remain.[8]
Power Pivot expands on the standardpivot table functionality in Excel. In the Power Pivot editor, relationships can be established between multiple tables to effectively createforeign key joins. Power Pivot can scale to process very large datasets in memory, which allows users to analyze datasets that would otherwise surpass Excel's limit of one million rows.[9] Power Pivot allows for importing data from multiple sources, such as databases (SQL Server, Microsoft Access, etc.), OData data feeds, Excel files, and other sources, facilitating comprehensive data analysis within a single environment.[10] TheVertiPaq compression engine is used to hold the data model in memory on the client computer. Practically, this means that Power Pivot is acting as an Analysis Services Server instance on the local workstation. As a result, larger data models may not be compatible with the 32-bit version of Excel.
Data Analysis Expressions (DAX) is the primary expression language, although the model can also be queried viaMulti Dimensional Expressions (MDX). DAX expressions allow a user to create calculated columns and measures to summarize and aggregate large quantities of data. Queries in the model are reduced to xmSQL, a pseudo-SQL language in the storage engines that drive the data model.[11]
A companion feature to Power Pivot namedPower Query may be used to performETL processes prior to analysis.[2]
