This post compares Analysis Services and PowerPivot.
| Analysis Service (SSAS) | PowerPivot | |
| What is it? | Analysis Service is Microsoft’s Online Analytical Processing (OLAP) Engine that resides on an Enterprise Infrastructure | PowerPivot is in-memory Analysis Service with column-based data store that typically resides on a user’s machine or on a SharePoint Infrastructure. |
| Target Audience | Enterprise BI | Personal BI |
| Data Sources | Supports a variety of data sources | Supports a variety of data sources including Atom feeds. |
| Development Tool | Business Intelligence Development Studio | Excel 2010 and upwards |
| Browser | SQL Server Management Studio (SSMS), Excel 2003 and upwards | Excel 2010 and upwards |
| Publishing | SharePoint, Reporting Services, Performance Point, any BI tool that is XMLA compatible | Excel 2010 and upwards, SharePoint 2010 and upwards |
| Security | Role based security model that supports Cell and Dimension Data Access | No security model exists for PowerPivot (unless hosted in SharePoint infrastructure). |
| Hierarchy | OLAP cubes have well defined Hierarchies | Hierarchies cannot be created in PowerPivot in Excel 2010.
UPDATE: Since Excel 2013, PowerPivot allows creation of hierarchies. |
| Query Language | Multi-Dimensional eXpression (MDX) | Data Analysis eXpression (DAX) |
| Calculated Members | Supported in OLAP cube and must be created at design time. Does not support creation of calculated members in pivot table. | Extensively Supported in PowerPivot. Allows creation of Calculated Measures in pivot table created from PowerPivot. |
| Programmability | Analysis Services can be programmatically managed using Analysis Management Objects(AMO) and ActiveX Data Objects Multi-Dimensional (ADO MD) | No such API’s |
| Supported Relationship Types | OLAP cubes support many-to-one, many-to-many and degenerate relationships between fact and dimension tables. | PowerPivot tables support only many-to-one relationship. |
| Role Playing Dimensions | Supported in OLAP cube | PowerPivot does not support Role Playing Relationships since a table can have only one relationship with the lookup table. |
| Parent-child relationship | Parent-child relationship can be established in dimension if the data exhibits such a relationship | Relationship cannot be established with the same table, so Parent-child relationship is not supported |
Benny Austin 
Nice comparison, very helpful!
However, it _is_ possible to create hierarchies in the PowerPivot window. They can be used for simple drilldowns when you narrow the results using the slicers.
Yes you are right. The capability to create hierarchy in PowerPivot first came with current version – Excel 2013. At the time of writing this post, the version of Excel was 2010 which did not have this capability.Thanks for your feedback. I have updated this post with a note to mention this.
Useful blog post. Thanks!
Great comparison! Thanks
Hi Benny,
This is very useful for a quick reference of Enterprise and personal BI implementation.
thanks
prav