The conversation about when to use SSAS vs. Power Pivot comes up a lot. Power Pivot is the data modeling component within the Power BI set of tools, and is suitable for certain self-service BI situations. Here's my top reasons when you may consider selecting Analysis Services instead, or when to upsize a Power Pivot model to an SSAS Tabular model.

This information is as of October 2014 and will definitely change as Power BI evolves and matures.

1. Row-Level Security. If the data returned needs to be secured based on what the user is permitted to see, that requirement usually justifies Analysis Services. Power Pivot supports file level security (i.e., either you see the data in the file or you don't). If you have a very small number of user groups, you could possibly still use Power Pivot. For example, let's say you have two divisions. You could secure the two division files differently to accomplish the need, and create two data sources in the Power BI Admin Center which use different credentials for the refresh. The tradeoff here is you'll have multiple workbooks to refresh, and any changes to reports and calculations and such would have to be done in each workbook.

2. High Data Volumes. Currently Power BI supports files at 250MB after the xVelocity compression is applied. Analysis Services can support much larger data volumes.

3. Minimal Data Latency. Currently you can schedule a refresh for a Power Pivot model in Power BI as often as once per day. Analysis Services processing can be scheduled in order to meet near-real-time needs if necessary.

4. Workbooks as a Data Source / Centralization of Data Model. Reports in Power BI currently require an embedded Power Pivot model. Now, this intermediary model can get data from many, many sources -- but currently the Power View, Power Map, and Excel reports need to be sourced from an internal Power Pivot model. This means the reports and data model are confined to one single XLSX file and not available for reuse. Inherently this provides for a limiting situation if you wish to centralize a data model and/or separate the reports from the model.

There are a few additional differences and considerations, but these four are the biggies that I find myself talking about pretty frequently.

Finding More Information

Comparing Analysis Services and Power Pivot

You Might Also Like...

Decisions: Power Pivot, SSAS Tabular, or SSAS Multidimensional Model in SQL Server 2012