PowerPivot is an amazing tool for performing ad hoc querying of data sources, creating dynamic models and empower business users to manage their own data feeds. For all those scenarios where your Excel power users are saying, “Just give me the data”, PowerPivot provides a very robust solution. Combined with SharePoint, collaborating, sharing and publishing of PowerPivot models is really easy as well – just upload it to SharePoint and distribute to teams through your intranet.
Having now worked with both PowerPivot and SSAS cubes, here are some suggestions as to when using cube is still a better option. (Another option is to use both – use an underlying cube as a pre-aggregated, high performance data repository and then use PowerPivot to just grab a piece of the data set for analytics purposes).
Your Desktop Clients are SLOW, Old or 32 Bit
PowerPivot works amazingly well on my machine – but I have a 64 bit Windows laptop with 32 GB of RAM. The available RAM is what makes PowerPivot fast and able to handle millions of rows.
If you’re running on an older machine, still running 32 bit windows or have limited RAM available then PowerPivot performance will likely suffer.
One alternative solution is to use SharePoint and render Excel files using Office Web Apps. This is a good work around for read only access to dashboards, reports, etc. but this doesn’t help with editing workbooks, creating new models or performing ad hoc queries.
Your Data is Incrementally Growing
One of the key weaknesses in PowerPivot is there is no support for incremental refresh.
When you refresh data, the data in the PowerPivot window is replaced with new data. You cannot import just new rows of data or just changed data. PowerPivot for Excel does not track which rows were added previously.
We have many clients who have a large volume of historical records and they are adding a couple hundred thousand rows every month. In PowerPivot, I cannot just grab the new rows in my refresh – it grabs the entire data set from the beginning. If you have millions of rows, this can be very slow.
You Need to Analyze TENS of Millions of Rows
I have PowerPivot models that are in the millions of rows (see my blog post here) and they work quite nicely. The data refresh is quite slow (e.g. 15-20 minutes) but I can create PowerView dashboards and they are quite responsive even with millions of rows.
If your dataset is tens of millions of rows, then having a cube to aggregate and crunch the data is going to make a massive difference in performance.
You have more than 250 MB of data and You want to Share via Office 365
With 64 Bit Excel 2013, file size limitations have been removed (they used to be 2 GB on disk and 4 GB in memory). However, there are still size limitations with SharePoint – SharePoint Server has a 2 GB file size limitation for uploading any file to a document library.
In addition, Power BI for Office 365 has a 250 MB limitation for Excel Online and Office 365 alone has a limitation of 30 MB for a maximum workbook size.
You Need Granular Security Over Your Data
SQL Server Analysis Services provides a role based security model that allows you to restrict access to particular dimensions and cells within the cube. Imagine you have a cube that you have rows organized by geography, sales person and product dimensions. In SSAS you can create rules so that users can only access cells that match particular slices of the cube based on MDX expressions. For example, you could create a rule that users in the “Michigan” group can only see data from their state.
PowerPivot is an all or nothing security model – queries and data refreshes are governed by a single user account that has access to all the data. In addition, once the data has been refreshed it sits locally in the excel file and as long as I can open the file I have access to the data.