In this BI Blog, we have covered many different ways to use PowerPivot as a rich business intelligence solution. Derek has demonstrated some very impressive uses of PowerPivot that he’s done with BI Voyage.
When SQL Server 2008 R2 was released in 2010, Microsoft hatched the idea of “self-service BI” within the SQL Server product stack and PowerPivot was the primary driver behind that position. A typical pattern with PowerPivot that I’ve witnessed is that BI developers or business users will play around with building data models in PowerPivot on a small scale, usually on their laptop. PowerPivot is simply an add-in to Excel 2010 that you can download free from Microsoft. PowerPivot takes your data sources and puts them into a column-based compression format called Vertipaq and generates and in-memory analysis services cube for you, without a developer needing to go into Visual Studio and build a complex SSAS cube with UDM technology.
Where SQL Server 2008 R2 Enterprise Edition comes into play is that once you’ve built your business data models and Excel reports from that model, you will want to share that knowledge and decision-making power that you’ve just spent hours creating, with the rest of the organization so that others can benefit from it and improve the decision-making capabilities of your company. In today’s SQL Server BI world, your next step would be to upload that PowerPivot Excel workbook to SharePoint 2010. SharePoint has the ability to deconstruct that Excel workbook into data connections, PowerPivot cubes and reports, thereby giving you the ability to use PowerPivot as a complete BI solution tool with SharePoint based security features. But instead of spinning-up the in-memory cubes on your laptop as PowerPivot does with the Excel version, SharePoint will utilize SQL Server’s Analysis Service for the in-memory analytics. It’s all really pretty simple and straight-forward.
SQL Server 2012 adds even more enterprise capabilities to this PowerPivot model of Microsoft BI solutions. In the screen capture below of my SQL Server 2012 RC0 SSMS, you can see that I’ve connected to a database in SSAS that is called SalesPP. That is my sales Excel spreadsheet where I modeled my business data in PowerPivot right in Excel. In SQL 2012, when you right-click on on Databases, you will see an option to “Restore from PowerPivot”. This will take your PowerPivot Excel sheet and generate an SSAS database from that file. Once you have generated the SSAS database, you have now created and Enterprise-ready version of your PowerPivot model that will now allow users to connect to it to browse and generate reports from Excel, Report Builder, Power View (see samples of my reports from my PowerPivot model in SSAS at the bottom of this posting using Report Builder 3.0).
This power and ease-of-use of this model has become so successful for business users, business analysts and power users, that Microsoft has essentially made the modeling and column compression capabilities in Vertipaq part of the SQL Server Analysis Services engine with SQL Server 2012. When you create a BI solution in SQL Server 2012 through Visual Studio (now call it SSDT, not BIDS) you will make a choice between the classic SSAS cube with the UDM modeling using ROLAP or MOLAP vs. the PowerPivot style of in-memory cubes generated by Analysis Services, which is called Tabular Model. When you make a tabular model data model, SSAS will store your model and then spin-up the cube in memory for you when it is accessed for querying, giving you the same PowerPivot compression and in-memory analytics on an enterprise scale.
Lastly, there are number of additional new enterprise features added to PowerPivot 2.0 (SQL Server 2012 version) that you can use today by downloading PowerPivot 2.0.
When I use Report Builder 3.0 to build a report from my PowerPivot model that has been stored in SSAS 2012, I get the classic MDX cube style query builder.