PowerPivot数据在工作表中

在以前的博客条目中，我谈到了开发PowerPivot模型。该模型定义了在PowerPivot应用程序中使用的数据和关系。在定义PowerPivot模型时，数据实际上使用称为Vertipaq的压缩技术导入Excel电子表格。你注意到的第一件事是.xlsx文件变得非常大。但不如数据源中的数据那么大。这是因为PowerPivot Model使用称为Vertipaq的技术将数据以压缩格式存储。您可以通过制作电子表格的副本来查看Vertipaq数据库，然后将扩展更改为.zip（from.xlsx），然后打开.zip文件。您应该看到名为XL \ CustomData的子文件夹。在该文件夹中，您应该根据您的数据查看一个或多个.data文件的一个或多个.data文件。这是Vertipaq数据库。 In my testing, I compared using the PowerPivot approach versus the native Analysis Services Cube stored remotely. With PowerPivot, once you’ve defined the PowerPivot model and imported the data, all processing is local to the client. This can be good and bad. Obviously, there is no further strain on the servers after import but any complex aggregation has to be handled by the client. Effectively, Office 2010 is building a temporary cube on the fly and performing the aggregations itself. Performance-wise there was no comparison. Running SSAS on a separate server with a purpose-built cube using the Excel 2010 client to submit MDX queries under the hood worked very efficiently. This is the OLAP architecture of choice. Working with the same data using PowerPivot produced the same results but in much slower response time – seconds vs minutes. The fact that I was dealing with millions of rows was obviously a factor so it is truly amazing technology that the client can handle this processing at all. But don’t take my word for it. Test it out yourself. Here are the samples I used in testing: Contoso Retail BI samples (Data warehouse and Cube): http://www.microsoft.com/downloads/details.aspx?familyid=868662DC-187A-4A85-B611-B7DF7DC909FC&displaylang=en Contoso Retail PowerPivot samples: http://www.microsoft.com/downloads/details.aspx?familyid=1AE63BFB-C303-44E3-AE44-7413D499495D&displaylang=en PowerPivot was developed with the objective of “Self-Service BI”, so in situations where there are various data sources that need to be analyzed on an ad-hoc basis with the power in the hands of the client, this will be a very useful innovation. The big payoff is that Excel 2010 now can handle hundreds of millions of rows of data so whether you go with PowerPivot or straight SSAS or a combination of both there are major benefits with the latest release. More on PowerPivot in the next Blog entry. Cheers Brian