Powerpivot
Encyclopedia
PowerPivot is a free add-in to the 2010 version of the spreadsheet application Microsoft Excel
. It extends the capabilities of the PivotTable data summarisation and cross-tabulation feature by introducing the ability to import data from multiple sources. As such, PowerPivot falls under Microsoft's Business Intelligence offering, complementing it with its self-service, in-memory capabilities.
Prior to the release of PowerPivot, Microsoft relied heavily on SQL Server Analysis Services as the engine for its Business Intelligence suite. PowerPivot complements the SQL Server core BI components under the vision of one Business Intelligence Semantic Model (BISM), which aims to integrate on-disk multidimensional analytics previously known as Unified Dimensional Model, or UDM, with a more flexible, in-memory "tabular" model.
As a self-service BI product PowerPivot is intended to allow users with no specialised BI or analytics training to develop data models and calculations, sharing them either directly or through SharePoint document libraries.
Microsoft Excel
Microsoft Excel is a proprietary commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications...
. It extends the capabilities of the PivotTable data summarisation and cross-tabulation feature by introducing the ability to import data from multiple sources. As such, PowerPivot falls under Microsoft's Business Intelligence offering, complementing it with its self-service, in-memory capabilities.
Prior to the release of PowerPivot, Microsoft relied heavily on SQL Server Analysis Services as the engine for its Business Intelligence suite. PowerPivot complements the SQL Server core BI components under the vision of one Business Intelligence Semantic Model (BISM), which aims to integrate on-disk multidimensional analytics previously known as Unified Dimensional Model, or UDM, with a more flexible, in-memory "tabular" model.
As a self-service BI product PowerPivot is intended to allow users with no specialised BI or analytics training to develop data models and calculations, sharing them either directly or through SharePoint document libraries.
Key Features
- Warehousing analytics and Business IntelligenceBusiness intelligenceBusiness intelligence mainly refers to computer-based techniques used in identifying, extracting, and analyzing business data, such as sales revenue by products and/or departments, or by associated costs and incomes....
within Excel - A local Analysis Services VertiPaq engine that compresses and loads data and makes it available to data visualizationData visualizationData visualization is the study of the visual representation of data, meaning "information that has been abstracted in some schematic form, including attributes or variables for the units of information"....
objects, such as PivotTablesPivot tableIn data processing, a pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, pivot-table tools can automatically sort, count, total or give the average of the data stored in one table or spreadsheet...
, in a worksheet. Sort and filter are sped up by performing operations with a local Analysis Services VertiPaq processor. - Support for a variety of datasources - Provides the foundation to load and combine source dataSource dataSource data is the origin of information found in electronic media.Often when data is captured in one electronic system and then transferred to another, there is a loss of audit trail or the inherent data cannot be absolutely verified. There are systems that provide for absolute data export but...
from any location for mass data analysis on the desktop, including relational databaseRelational databaseA relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...
s, multidimensional sources, cloud services, data feedData feedData feed is a mechanism for users to receive updated data from data sources. It is commonly used by real-time applications in point-to-point settings as well as on the world-wide web. The latter is also called Web feed. News feed is a popular form of Web feed. RSS feed makes dissemination of blogs...
s, Excel files, text fileText fileA text file is a kind of computer file that is structured as a sequence of lines of electronic text. A text file exists within a computer file system...
s, and data from the Web. - SharePoint Integration - Enables users to share data modelData modelA data model in software engineering is an abstract model, that documents and organizes the business data for communication between team members and is used as a plan for developing applications, specifically how data is stored and accessed....
s and analysis with the solutions in SharePoint; users can configure refresh cycles to ensure the data remains current automatically. - PowerPivot allows creating portable and reusable data; data stays inside the workbook. Users do not have to manage external data connections. The user has a single workbook (.xlsx) file that contains embedded data extracted and processed by an internal processor, but rendered exclusively through Excel.
- When users publish, move, copy, or share a workbook, all the data is transported inclusively; saved data is stored inside the Excel workbook.
- Once the data is loaded the user can work in disconnected mode – no further connectivity is required to any server system.
- The maximum file size of a PowerPivot workbook is 2 GB on disk and 4 GB in memory. File compression reduces the amount of disk space required to save a workbook with typical ratios of 1:10 disk-to-memory or greater.
- Support for Data Analysis eXpressions, which allow users to perform analytical calculations. DAX includes functions dealing with time intelligence, data aggregation and statistical calculations in general.