Analysis Service (SSAS) Vs. PowerPivot

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!/BennyAustin/status/4493056422187008

6 thoughts on “Analysis Service (SSAS) Vs. PowerPivot

  1. 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.

    1. 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.

  2. Hi Benny,

    This is very useful for a quick reference of Enterprise and personal BI implementation.


Leave a Reply to praveen Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s