PowerPivot is a powerful data analysis tool with advanced reporting; it leverages self-service business intelligence for everyone. PowerPivot is a free add-in for Microsoft Excel 2010 that enables the user to import massive amounts of data from a variety of sources and manipulates it into one workbook, which means PowerPivot helps in solving heterogeneous problems. Millions of rows of data can be accessed in seconds.
PowerPivot is a business intelligence tool of Microsoft, known as Microsoft Business Intelligence (MSBI). In PowerPivot, the features, functionalities, calculations, and report visualization are like other traditional BI tools. PowerPivot is based on xVelocity in-memory technologies, providing unmatched analytical performance and calculation capabilities to process billions of rows at the speed of thought using Data Analysis Expressions (DAX) functions. Basically, DAX is a business logic of PowerPivot and is used for data manipulation. DAX functions are very basic and easy to learn, even for a person who doesn't have a technical background.
The following figure explains the architecture of PowerPivot and how it works in Excel:
Architecture of PowerPivot in Excel
PowerPivot for Excel uses the Analysis Services xVelocity in-memory analytics engine (VertiPaq) that runs in-process in Excel and helps in importing data from a variety of data sources. The data that the user adds to the workbook is stored internally just like an embedded PowerPivot database inside the .xlsx file and data processing is done through this engine; it compresses and loads the data and makes it available as visualization objects, such as PivotTables and PivotCharts, in a worksheet. There are no administration or configuration tasks that need to be performed in PowerPivot. The engine is an internal component of PowerPivot, which is an add-in to Excel.
PowerPivot gives drag-and-drop methods to users. Users can perform aggregation, filtering, create metrics by using DAX, and create diverse reports like PivotTables, PivotCharts, and KPIs. There are three principles, namely, preparation, integration and aggregation, and data analysis. There will be brief explanations about PowerPivot in this book, based on the following figure:
Principles of PowerPivot