Power Pivot, formerly known as PowerPivot (without spacing), is a feature of
Microsoft Excel
Microsoft Excel is a spreadsheet developed by Microsoft for Microsoft Windows, Windows, macOS, Android (operating system), Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro (comp ...
, a
computer software
Software is a set of computer programs and associated documentation and data. This is in contrast to hardware, from which the system is built and which actually performs the work.
At the lowest programming level, executable code consists ...
spreadsheet
A spreadsheet is a computer application for computation, organization, analysis and storage of data in tabular form. Spreadsheets were developed as computerized analogs of paper accounting worksheets. The program operates on data entered in cel ...
. It is available as an add-in in Excel 2010, 2013 in separate downloads, and as an add-in included with the Excel 2016 program. Power Pivot extends a local instance of
Microsoft Analysis Services
Microsoft SQL Server Analysis Services (SSAS) is an online analytical processing (OLAP) and data mining tool in Microsoft SQL Server. SSAS is used as a tool by organizations to analyze and make sense of information possibly spread out across mul ...
tabular that is embedded directly into an Excel Workbook. This allows a user to build a
ROLAP
Online analytical processing, or OLAP (), is an approach to answer multi-dimensional analytical (MDA) queries swiftly in computing. OLAP is part of the broader category of business intelligence, which also encompasses relational databases, re ...
model in Power Pivot, and use
pivot tables to explore the model once it is built. This allows Excel to act as a self-service
business intelligence
Business intelligence (BI) comprises the strategies and technologies used by enterprises for the data analysis and management of business information. Common functions of business intelligence technologies include reporting, online analytical pr ...
(BI) platform, implementing professional expression languages to query the model and calculate advanced measures.
Power Pivot primarily uses
Data Analysis Expressions (DAX) as its expression language, although the model can be queried via
Multidimensional Expressions
Multidimensional Expressions (MDX) is a query language for online analytical processing (OLAP) using a database management system. Much like SQL, it is a query language for OLAP cubes. It is also a calculation language, with syntax similar to sp ...
(MDX) in a row set expression. DAX expressions allow a user to create measures based on the data model, which can summarize and aggregate millions of rows of table data in seconds. DAX expressions resolve to
T-SQL
Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to the SQL (Structured Query Language) used to interact with relational databases. T-SQL expands on the SQL standard to include procedural programming, local variables, var ...
queries in the Formula and Storage Engines that drive the data model, abstracting the more verbose and tedious work of writing formal queries to excel-like formula expressions.
Power Pivot uses the
SSAS Vertipaq compression engine 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.
Prior to the release of Power Pivot, Microsoft relied heavily on
SQL Server Analysis Services as the engine for its Business Intelligence suite. Power Pivot complements the SQL Server core business intelligence components under the vision of one
Business Intelligence Semantic Model
Business is the practice of making one's living or making money by producing or buying and selling products (such as goods and services). It is also "any activity or enterprise entered into for profit."
Having a business name does not separat ...
(
BISM), which aims to integrate on-disk multidimensional analytics previously known as
Unified Dimensional Model
Unified may refer to:
* The Unified, a wine symposium held in Sacramento, California, USA
* ''Unified'', the official student newspaper of Canterbury Christ Church University
* UNFD, an Australian record label
* ''Unified'' (Sweet & Lynch album), ...
(UDM), with a more flexible, in-memory "tabular" model.
As a self-service business intelligence product, Power Pivot is intended to allow users with no specialized business intelligence or analytics training to develop data models and calculations, sharing them either directly or through
SharePoint
SharePoint is a web-based collaborative platform that integrates natively with Microsoft Office. Launched in 2001, SharePoint is primarily sold as a document management and storage system, but the product is highly configurable and its usage v ...
document libraries.
Product history and naming
Power Pivot first appeared around May 2010 as part of the
SQL Server 2008 R2 The history of Microsoft SQL Server begins with the first Microsoft SQL Server database product – SQL Server v1.0, a 16-bit relational database for the OS/2 operating system, released in 1989.
Versions
Detailed history Genesis
On June 12, 198 ...
product line. It included "Power Pivot for Excel" and "Power Pivot for SharePoint" While the product was associated with SQL Server, the add-in for Excel could be used independent of any server, and with various types of data sources.
SQL Server 2012 The history of Microsoft SQL Server begins with the first Microsoft SQL Server database product – SQL Server v1.0, a 16-bit relational database for the OS/2 operating system, released in 1989.
Versions
Detailed history Genesis
On June 12, 198 ...
contained the add-in PowerPivot for Microsoft Excel 2010, this was also made available as a free download for Microsoft Excel 2010. Sometime after that, the PowerPivot followed its own release cadence separate from SQL Server.
As part of the July 8, 2013, announcement of the new
Power BI
Power BI is an interactive data visualization software product developed by Microsoft with a primary focus on business intelligence. It is part of the Microsoft Power Platform.
Power BI is a collection of software services, apps, and connectors t ...
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. In Excel 2013, Power Pivot is only available for certain versions of Office.
What’s new in Power Pivot in Excel 2013
In Excel 2016, it is included natively in the application in the data tab on the ribbon.
A companion feature to Power Pivot named Power Query
Power Query is an ETL tool created by Microsoft for data extraction, loading and transformation, and is used to retrieve data from sources, process it, and load them into one or more target systems. Power Query is available in several variation ...
, in Excel 2010 and 2013, was renamed to "Get & Transform" in Excel 2016. It has since been renamed back to Power Query.
See also
* Power Query
Power Query is an ETL tool created by Microsoft for data extraction, loading and transformation, and is used to retrieve data from sources, process it, and load them into one or more target systems. Power Query is available in several variation ...
, an ETL tool used with several pieces of software published by Microsoft
* Microsoft Excel
Microsoft Excel is a spreadsheet developed by Microsoft for Microsoft Windows, Windows, macOS, Android (operating system), Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro (comp ...
* Power BI
Power BI is an interactive data visualization software product developed by Microsoft with a primary focus on business intelligence. It is part of the Microsoft Power Platform.
Power BI is a collection of software services, apps, and connectors t ...
References
{{Microsoft Office
Microsoft Office