Microsoft’s PowerPivot add-in to Excel (2010 and 2013) is a powerful tool for business users to combine multiple types of data sources into a single pivot table. Leveraging technology shared by Microsoft SQL Server Analysis Services (SSAS) OLAP solutions, PowerPivot provides aggregated data filterable by a common set of attributes, which can be designed by users without having to engage an IT professional (click here to read my previous post on the possibilities of “self-serve” business intelligence).
Once the data is combined in PowerPivot, users can easily generate visualizations using out of the box charts, spark lines, and color-coded custom KPI calculations for dashboard-like displays.
When the individual user posts the Excel workbook to a SharePoint PowerPivot gallery, it then becomes available for others to use and for Microsoft UI reporting tools to connect to, such as SQL Reporting Services and Power View. There are a few limitations of the Power Pivot workbook solution; however, these are easily addressed by deploying it to an Analysis Services instance.
There are at least three important advantages of moving the Excel PowerPivot (or “tabular model”) to the server:
1.) Large data sets: The Excel workbook cannot exceed a file size over 2 GBs, so if you have a significantly large set of data, you’ll need to deploy to SSAS. As your data set approaches the terabyte level size, you’d even want to consider shifting to a traditional multidimensional SSAS cube and move away from tabular models all together.
2.) More security options: By deploying to the server, security can be managed centrally and applied at a highly-granular, row-based level. In Analysis Services, you manage security by creating a role and granting permissions to the role, then adding users (managed via Windows Active Directory). You implement row-level security by granting access to rows in a table. Permission is granted by entering a DAX expression that filters the rows in a table. MSDN provides a helpful tutorial on granting permissions.
3.) Multiple model developers working in parallel: Even with hosting your PowerPivot workbook on SharePoint, the ability for more than one person at a time to work on the creation of new KPIs, bring in new data, and other modifications may not be practical. Once the model is deployed to the Analysis Services server, then parallel development (and better version control) is more feasible. With the deployed instance, you work on the model through SQL Data Tools (what used to be Visual Studio BI Development environment or BIDS), although the experience is very similar and just as friendly as working in the Excel Power Pivot window.
There are at least 2 easy ways to deploy an existing PowerPivot to the server. The easiest is simply to drop the Excel file into the location where Analysis Services stores database back-ups. This option makes sense because PowerPivot essentially contains an Analysis Services database. Another option is to import and then deploy it from a solution in Visual Studio (the new BIDS in SQL 2012 is known as SQL Server Data Tools). Once deployed on the server, you’ll have all of the benefits of the server memory, central management of permissions, and ability to implement version control. And of course, your reporting tools (such as SQL Reporting Services) can point to the model on the server just as they would a traditional multidimensional cube.