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 (@BennyAustin) November 16, 2010