It seems many BI pros do not realize the wonderful large scale potential for PowerPivot projects using the easy SSAS upgrade path in SQL Server 2012. Most of the enterprise customers I help do enjoy PowerPivot for self-service BI but thought that PowerPivot was limited to the desktop Excel file size limits. No, it is not limited – PowerPivot is highly scalable if you upgrade it to the server SSAS Tabular Mode version! Even better unlike other platforms that “lock” you into their limited tools, PowerPivot models on the server can be used in various Microsoft and non-Microsoft reporting tools as slice/dice OLAP cubes. How cool is that?
If you have a project with say with billions of records, PowerPivot can be directly upgraded to a server version called SSAS Tabular Mode. The upgrade retains all the business user logic and calculations to enable that model to be used on large scale data sets far beyond the 80-100 million record data sets of PowerPivot. You can also partition that PowerPivot model for highly performing incremental uploads after the upgrade, optionally use the extremely fast “in-memory” partitions or Direct Query partitions features to leverage the database engine, apply security and so forth.
How To: You can upgrade PowerPivot to the server version by either directly importing the Excel file into Visual Studio OR directly restoring the Excel file on the SSAS server. The entire model, calculations, KPIs and hierarchies are retained. Pictures of these two upgrade options are below. From here you can now schedule processing and refresh just like other enterprise server BI processes.
PowerPivot for Large Scale projects
The PowerPivot to server upgrade approach is a great way to maximize BI projects ROI by reducing the back and forth of the BI team and the Excel Power User in requirements gathering, prototypes and build out. Some vendors claim this same exact process saves 100-300x ROI. Simply let the Excel Power User build their own model on a smaller data set and then have the BI team upgrade it to be a true enterprise capable model with hierarchies, KPIs and all the crazy calculations business users dream up.