This post compares Analysis Services and PowerPivot in 30 seconds.
| 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 |
| Browser | SQL Server Management Studio (SSMS), Excel 2003 and upwards | Excel 2010 |
| Publishing | SharePoint, Reporting Services, Performance Point, any BI tool that is XMLA compatible | Excel 2010, SharePoint 2010 |
| 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. |
| 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 |
Analysis Service Vs. PowerPivot. Check it out if you have 30 seconds… http://wp.me/pxNuz-k0 #fb #in—
Benny Austin (@BennyAustin) November 16, 2010
Advertisement
Like this:
2 bloggers like this post.
Hi Benny,
This is very useful for a quick reference of Enterprise and personal BI implementation.
thanks
prav
Posted by praveen | November 21, 2010, 19:57Great comparison! Thanks
Posted by Nicolas M. | April 2, 2011, 01:33