Power pivot

from Wikipedia, the free encyclopedia

Power Pivot is a function of the Microsoft Excel spreadsheet software and has been available since version 2010. Power Pivot is intended to give users the ability to evaluate large amounts of data independently. Power Pivot is one of the so-called "Self Service BI " products.

history

Power Pivot was introduced as an additionally installable add-in for Excel 2010. Since the Excel version 2013, Power Pivot is automatically installed with Excel.

technology

The add-in integrates the Microsoft SQL Server Analysis Service ( SSAS ) into the locally started Excel process of the user. This makes it possible to analyze large amounts of data. Data analysis in Excel workbooks is limited to 1 million lines. Power Pivot uses the tabular model introduced in SSAS .

Power Pivot uses the SSAS Vertipaq Engine to evaluate data. All data is kept in main memory. In the 32-bit version of Office, which is still widely used, Power Pivot reaches its technical limits when 2  GB of main memory are used. This restriction no longer applies to a 64-bit installation.

Data Analysis Expressions

Power Pivot DAX was introduced as the query language . DAX stands for Data Analysis Expressions . The language supports the user to execute complex queries on the data model in order to carry out complex evaluations, such as

  • Period comparisons
  • Segment analysis
  • ABC analyzes.

The Excel-like syntax is intended to keep the entry barrier for experienced Excel users as low as possible.

Get & Transform (Power Query)

Get & Transform was introduced with Excel 2013 - still under the name Power Query. Power Query has been integrated into Excel since version 2016. With Excel 2016, Power Query was renamed Get & Transform. Menu functions, help texts and websites (including those from the manufacturer) are not consistent when using the names Power Query and Get & Transform.

With Get & Transform, users can load data into the Power Pivot data model from many different sources. Get & Transform offers the user a graphic interface for various transformations of the data when loading from the respective source. Examples of transformations are:

  • Replacements
  • additions
  • Filterings
  • Combinations with other dates.

The functional script language M was introduced with Get & Transform. All graphic transformations are formulated and stored internally in M. With the advanced editor in Power Query, the user can intervene directly in the script.

Power BI development

Power Pivot was integrated into Excel in order to offer a good alternative, especially for experienced Excel users who are already doing large-scale data analyzes. The sister system Power BI has existed since 2013 and can analyze data on the same basis. The local Power BI Desktop system works independently without Excel integration and brings its own visualizations with it. The results of Power BI Desktop can be published in the Office 365 cloud in order to make the analyzes available to employees in the same company. Microsoft provides a free version.

Web links

Individual evidence

  1. ^ Microsoft: Data Analysis Expressions (DAX). Retrieved April 18, 2019 .
  2. ^ Microsoft: Get & Transform in Excel. Retrieved April 18, 2019 .
  3. ↑ Holger Gubbels: What is Power BI. In: Powerpivot Insights. Retrieved April 18, 2019 .