Power Pivot

Last updated
Microsoft Power Pivot
Developer(s) Microsoft
Operating system Microsoft Windows
Type OLAP, Data analysis, Business intelligence
License Microsoft EULA

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.

Contents

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

Prior 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

Power 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] 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. [10]

A companion feature to Power Pivot named Power Query may be used to perform ETL processes prior to analysis. [2]

Related Research Articles

<span class="mw-page-title-main">Microsoft Access</span> Database manager part of the Microsoft 365 package

Microsoft Access is a database management system (DBMS) from Microsoft that combines the relational Access Database Engine (ACE) with a graphical user interface and software-development tools. It is a member of the Microsoft 365 suite of applications, included in the Professional and higher editions or sold separately.

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, report writing and data mining. Typical applications of OLAP include business reporting for sales, marketing, management reporting, business process management (BPM), budgeting and forecasting, financial reporting and similar areas, with new applications emerging, such as agriculture.

SAP ASE (Adaptive Server Enterprise), originally known as Sybase SQL Server, and also commonly known as Sybase DB or Sybase ASE, is a relational model database server developed by Sybase Corporation, which later became part of SAP SE. ASE was developed for the Unix operating system, and is also available for Microsoft Windows.

Essbase is a multidimensional database management system (MDBMS) that provides a platform upon which to build analytic applications. Essbase began as a product from Arbor Software, which merged with Hyperion Software in 1998. Oracle Corporation acquired Hyperion Solutions Corporation in 2007. Until late 2005 IBM also marketed an OEM version of Essbase as DB2 OLAP Server.

The Access Database Engine is a database engine on which several Microsoft products have been built. The first version of Jet was developed in 1992, consisting of three modules which could be used to manipulate a database.

A pivot table is a table of values which are aggregations of groups of individual values from a more extensive table within one or more discrete categories. The aggregations or summaries of the groups of the individual terms might include sums, averages, counts, or other statistics. A pivot table is the outcome of the statistical processing of tabularized raw data and can be used for decision-making.

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 multiple databases, or in disparate tables or files. Microsoft has included a number of services in SQL Server related to business intelligence and data warehousing. These services include Integration Services, Reporting Services and Analysis Services. Analysis Services includes a group of OLAP and data mining capabilities and comes in two flavors multidimensional and tabular, where the difference between the two is how the data is presented. In a tabular model, the information is arranged in two-dimensional tables which can thus be more readable for a human. A multidimensional model can contain information with many degrees of freedom, and must be unfolded to increase readability by a human.

SQL Server Reporting Services (SSRS) is a server-based report generating software system from Microsoft. It is part of a suite of Microsoft SQL Server services, including SSAS and SSIS.

Microsoft SQL Server Express is a version of Microsoft's SQL Server relational database management system that is free to download, distribute and use. It comprises a database specifically targeted for embedded and smaller-scale applications. The product traces its roots to the Microsoft Database Engine (MSDE) product, which was shipped with SQL Server 2000. The "Express" branding has been used since the release of SQL Server 2005.

Azure DevOps Server, formerly known as Team Foundation Server (TFS) and Visual Studio Team System (VSTS), is a Microsoft product that provides version control, reporting, requirements management, project management, automated builds, testing and release management capabilities. It covers the entire application lifecycle and enables DevOps capabilities. Azure DevOps can be used as a back-end to numerous integrated development environments (IDEs) but is tailored for Microsoft Visual Studio and Eclipse on all platforms.

Microsoft SQL Server is a proprietary relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network. Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.

<span class="mw-page-title-main">XLeratorDB</span>

XLeratorDB is a suite of database function libraries that enable Microsoft SQL Server to perform a wide range of additional (non-native) business intelligence and ad hoc analytics. The libraries, which are embedded and run centrally on the database, include more than 450 individual functions similar to those found in Microsoft Excel spreadsheets. The individual functions are grouped and sold as six separate libraries based on usage: finance, statistics, math, engineering, unit conversions and strings. WestClinTech, the company that developed XLeratorDB, claims it is "the first commercial function package add-in for Microsoft SQL Server."

The following tables compare general and technical information for a number of online analytical processing (OLAP) servers. Please see the individual products articles for further information.

<span class="mw-page-title-main">BIDS Helper</span>

BIDS Helper is a Visual Studio open source extension with multiple features that extend and enhance business intelligence development functionality in all editions of Microsoft's SQL Server 2005, 2008, 2008 R2 and 2012. BIDS Helper improves the development environment for integration, analysis and reporting services. BIDS Helper is hosted on GitHub.

Data Analysis Expressions (DAX) is the native formula and query language for Microsoft PowerPivot, Power BI Desktop and SQL Server Analysis Services (SSAS) Tabular models. DAX includes some of the functions that are used in Excel formulas with additional functions that are designed to work with relational data and perform dynamic aggregation. It is, in part, an evolution of the Multidimensional Expression (MDX) language developed by Microsoft for Analysis Services multidimensional models combined with Excel formula functions. It is designed to be simple and easy to learn, while exposing the power and flexibility of PowerPivot and SSAS tabular models.

Microsoft 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 that work together to turn various sources of data into static and interactive data visualizations. Data may be input by reading directly from a database, webpage, PDF, or structured files such as spreadsheets, CSV, XML, JSON, XLSX, and SharePoint.

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.

Kyvos is a business intelligence acceleration platform for cloud and big data platforms developed by an American privately held company named Kyvos Insights. The company, headquartered in Los Gatos, California, was founded by Praveen Kankariya, CEO of Impetus Technologies. The software provides OLAP-based multidimensional analysis on big data and cloud platforms and was launched officially in June 2015. In December the same year, the company was listed among the 10 Coolest Big Data Startups of 2015 by CRN Magazine.

Azure Data Explorer is a fully-managed big data analytics cloud platform and data-exploration service, developed by Microsoft, that ingests structured, semi-structured and unstructured data. The service then stores this data and answers analytic ad hoc queries on it with seconds of latency. It is a full text indexing and retrieval database, including time series analysis capabilities and regular expression evaluation and text parsing.

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 variations within the Microsoft Power Platform, and is used for business intelligence on fully or partially self-service platforms. It is found in software such as Excel, Power BI, Analysis Services, Dataverse, Power Apps, Azure Data Factory, SSIS, Dynamics 365, and in cloud services such as Microsoft Dataflows, including Power BI Dataflow used with the online Power BI Service or the somewhat more generic version of Microsoft Dataflow used with Power Automate.

References

  1. ↑ 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.
  2. 1 2 3 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.
  3. ↑ "PowerPivot for Excel and SharePoint - A Brief History of PowerPoint". 2011-04-14.
  4. ↑ "Download Microsoft® SQL Server® 2012 SP1 PowerPivot for Microsoft Excel® 2010 from Official Microsoft Download Center". Microsoft .
  5. ↑ What’s new in Power Pivot in Excel 2013 Archived 2012-10-01 at the Wayback Machine
  6. ↑ "Microsoft 365 Blog - Latest News, Updates, and Insights".
  7. ↑ "Make Power Pivot available in all versions of Excel – Welcome to Excel's Suggestion Box!". web.archive.org. 2018-07-07. Retrieved 2024-04-23.
  8. ↑ 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.
  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. ISSN   2150-8097.
  10. ↑ 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.
This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.