To PowerPivot, or not to PowerPivot?

We’ve all seen many presentations about PowerPivot and its great features like in-memory processing which enables Excel to handle millions of records. But the question is when to use PowerPivot as opposed to using a SSAS cube?

Here are some points you need to consider when choosing the tool:

Pros:

 

• PowerPivot is a powerful tool to gather data from different data sources. It can be used to create POCs or demos in a quick (and possibly dirty) way.
• It can be used to answer some ad-hoc questions or to play with data (data analysis).

Cons:

• PowerPivot doesn't support data level security. Whoever gets access to the file can see all data inside it. With SSAS you can implement security at data level.
• Complicated dimension types (like parent-child) are not supported in PowerPivot
• Using PowerPivot workbooks as data source for reports may cause version management issues.
• PowerPivot runs in memory, when you use PowerPivot for all your enterprise data you would need a huge RAM
• Saving PowerPivot files in SharePoint may cause some issues when the size is too large.
• SSAS allows you to fine tune your cube but with PowerPivot you can’t.
• Data can easily be out of control as soon as it is saved on workstation. There also might be huge security risks involved. Even with the SharePoint implementation users have to download data to workstation first, and then publish it to SharePoint.
• PowerPivot for SharePoint requires Enterprise license and SQL Server 2008R2 EE license. The cost (which is very high) may affect the SharePoint implementations of PowerPivot. By using PowerPivot without SharePoint (Excel add-in which is free) we may end up having even bigger Excel hell!
• Source data is not always in high quality. We may need to validate and do data cleansing and also more often we need to apply some business rules which can be handled better in SSAS or at ETL level.
• Working with data from different data sources may require high technical knowledge. Some data isn't that easy to join. A key isn't always available that lets you join data from different sources. It will make it hard for a non-technical person to work with the tool without any IT support.

  

From what I've seen so far, PowerPivot is not a replacement for Analysis Services, but it certainly has a role for tactical solutions. It also can be used by power users to quickly analyse data, or perhaps to build prototypes of larger scale SSAS implementations.

So I think PowerPivot is great for certain scenarios, but for Enterprise level data warehouse implementation SSAS is the way to go.