PowerPivot vs本地SSAS

PowerPivot是一种很棒的自助式BI新技术。在之前的博客中，我已经介绍了一些使用SQL Server 2008 R2和Excel 2010的技术。从各种来源访问数据并将它们聚集在PowerPivot模型中的概念非常强大。事实上，这一切都可以在客户端完成，这是真正令人惊奇的。但是，与使用本地分析服务(SSAS)多维数据集并通过Excel 2010客户端远程访问数据相比，这有什么区别呢?对于OLAP系统，我们通常从一个设计良好的数据仓库开始，每天晚上递增地装载最新的操作数据。SSAS将把关系数据库转换为多维“多维数据集”，以便跨大量数据进行快速访问。MDX查询可以提交到多维数据集以进行快速聚合和分析。Excel客户端的作用是允许数据透视表和数据透视图用户界面透明地向用户提交强大的MDX查询。这是通过用于分析服务的OLE DB提供程序完成的。 All the user sees is the data. Enter PowerPivot with Excel 2010. Through the PowerPivot Window, we can build a PowerPivot model that consists of one or more Data Sources and associated relationships. The Data Sources may include relational databases or multi-dimensional cubes as well as data from text files or reports. The key differentiator here is that the data is actually imported into the spreadsheet locally on the client. So you may see the spreadsheet grow accordingly but using the new “VertiPaq” technology the data is massively compressed at the column level. Now we can build a PivotTable or PivotChart in Excel as normal using the PowerPivot model as the source. In my testing, performance using native SSAS was lightning fast even across millions of rows of data. This is because of the pre-processed aggregates in the cube that limit the number-crunching aggregation needed. The multi-tier architecture helps performance too with the great majority of the processing happening on the server. Call me traditional, but that is how it should be. Of course, the trade-off is that you have to design, implement and maintain a Data Warehouse with an SSAS Cube and provide the hardware, services and support that go along with it. PowerPivot allows ad-hoc access to multiple data sources, effectively building a cube on the fly while hiding the complexities of OLAP from the user. Once the PowerPivot model is built and data imported, all the processing is on the client. There is no need for a Data Warehouse or Cube, although if they exist they would be prime candidates for data sources. When stress testing with millions of rows in my PowerPivot model, I did notice sluggish aggregations since they were being performed interactively and could not be pre-processed as is the default in SSAS. However, I did have to pinch myself to realize that this was, yes, Microsoft Excel working alone with millions of rows of data to produce some very powerful and attractive results with minimal effort. Cheers Brian