Power Pivot, formerly known as PowerPivot (without spacing), is a self-service business intelligence feature of Microsoft Excel which facilitates the creation of a tabular model to import, relate, and analyze data from a variety of sources.
| Developer(s) | Microsoft |
|---|---|
| Operating system | Microsoft Windows |
| Type | OLAP, Data analysis, Business intelligence |
| License | Microsoft EULA |
Power Pivot extends a local instance of Microsoft Analysis Services tabular that is embedded directly into an Excel workbook, facilitating the creation of a ROLAP 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 across Microsoft Power BI and SQL Server Analysis Server (SSAS), and may be referred to as xVelocity, VertiPaq, SSAS Tabular, and Power Pivot.[1]
Product history and naming
editPrior to the release of Power Pivot, the engine for Microsoft's Business Intelligence suite was exclusively contained within SQL Server Analysis Services. In 2006, an initiative was launched by Amir Netz of the SQL 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 the SQL 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 for SQL 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 new Microsoft 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]
Features
editPower Pivot expands on the standard pivot table functionality in Excel. In the Power Pivot editor, relationships can be established between multiple tables to effectively create foreign 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] The VertiPaq 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 via Multi 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 named Power Query may be used to perform ETL processes prior to analysis.[2]
References
edit- ^ Allington, Matt (January 2021). Supercharge Power BI: Power BI Is Better When You Learn to Write DAX (3rd ed.). Merritt Island, FL: Holy Macro! Books. ISBN 978-1-61547-069-3.
- ^ a b c Collie, Rob; Singh, Avi (2016). Power Pivot and Power Bi: the Excel user's guide to Dax, Power Query, Power Bi & Power Pivot in Excel 2010-2016 (2nd ed.). Merritt Island, FL: Holy Macro! Books. ISBN 978-1-61547-039-6.
- ^ "PowerPivot for Excel and SharePoint - A Brief History of PowerPoint". 2011-04-14.
- ^ "Download Microsoft® SQL Server® 2012 SP1 PowerPivot for Microsoft Excel® 2010 from Official Microsoft Download Center". Microsoft.
- ^ What’s new in Power Pivot in Excel 2013 Archived 2012-10-01 at the Wayback Machine
- ^ "Microsoft 365 Blog - Latest News, Updates, and Insights". 12 June 2024.
- ^ "Make Power Pivot available in all versions of Excel – Welcome to Excel's Suggestion Box!". 2018-07-07. Archived from the original on 2018-07-07. Retrieved 2024-04-23.
- ^ Ferrari, Alberto; Russo, Marco (2017-04-28). Analyzing Data with Power BI and Power Pivot for Excel. Microsoft Press. ISBN 978-1-5093-0281-9.
- ^ Chen, Zhimin; Narasayya, Vivek; Chaudhuri, Surajit (August 2014). "Fast foreign-key detection in Microsoft SQL server PowerPivot for Excel" (PDF). Proceedings of the VLDB Endowment. 7 (13): 1417–1428. doi:10.14778/2733004.2733014. ISSN 2150-8097.
- ^ "How to Master Power Pivot in Excel". Accelerate Excel. Retrieved 27 August 2024.
- ^ Russo, Marco; Ferrari, Alberto (2020). The Definitive Guide to DAX: Business Intelligence with Microsoft Power BI, SQL Server Analysis Services, and Excel (2nd ed.). Pearson Education, Inc. ISBN 978-1-5093-0697-8.