Watch the introduction video of Power Pivot in Excel 2013.
add-in for Excel 2010/2013 is used to perform powerful data analysis, self-service business intelligence within the familiar framework of Excel on your desktop.
Using Power Pivot you can import data from different source like RDBMS, data feeds, spread-sheets, files. It also support BIG Data and Hadoop. Data is imported into PowerPivot for Excels as Tables which is shown as sheets in the Power Pivot window.
How Data is Stored
The data that you work on in Excel and in the Power Pivot window is stored in an analytical database inside the Excel workbook, and a powerful local engine loads, queries, and updates the data in that database. Because the data is in Excel, it is immediately available to PivotTables, PivotCharts, Power View, and other features in Excel that you use to aggregate and interact with data.
All data presentation and interactivity are provided by Excel; and the data and Excel presentation objects are contained within the same workbook file. Power Pivot supports files up to 2GB in size and enables you to work with up to 4GB of data in memory.
Power Pivot BI Architecture in Excel 2013
Please note that PowerPivot BI Architecture in Excel 2013 is different from Excel 2010. Earlier X-velocity engine was part of add-in however with Excel 2013, it is built-in.
Image source: http://www.powerpivotpro.com/
Note: Power Pivot for Excel and Power Pivot in SharePoint has different architecture.
Power Pivot Limitation
1. Power Pivot for Excel has 2 GB file limitation.
2. Because the size of file can be upto 2GB, it’s difficult to share across mails.
3. In order to share the reports created using Power Pivot Data Model, SharePoint Server 2010/13 with SQL 2012/14 is required which again is the added cost.
4. Another option to share Power Pivot Data Model reports is to use Power BI for Office 365 but has a limitation of 250 MB file size limit.
5. Without using On-premise SharePoint, you cannot connect with existing Power Pivot Model Workbook and create a report in another workbook. Data Model connection to create report is only available through SharePoint (Not Office 365)
Power Pivot OR Tabular Model
In a nutshell both Power Pivot and Tabular uses xVelocity Engine and have same concepts. So what is the difference and which one is better?
Actually Tabular model is for IT professional while Power Pivot is for Business Users. And that is the reason 2GB size limitation is acceptable in most of the business users scenario and quick data analysis. But in case of enterprise wide requirement like scalability, manageability, securability and professional development, tabular model has an edge over power pivot.
Since Power Pivot model can be imported into Tabular Model, a business user might create a solution using Power Pivot and then IT can add features that are not supported in Power Pivot to the tabular model like security and maintainability etc.