polThe Excel 12 Blog Rides Again, or “CUBE Functions Part 1”
繼續(xù)Excel 12 博客游戲——數(shù)據(jù)集函數(shù) Part 1
OK, I am freshly back from a fascinating couple weeks of visiting a number of customers (thanks to all you that spent time with us over the last few weeks), so it is time to get back to talking about Excel 12 features.
好了，前幾周我做了有幾個有趣的客戶拜訪（感謝客戶們抽了幾周來陪我們），剛才回來?，F(xiàn)在接著談EXCEL 12的特性。
We’ve already talked about using Pivot Tables to bring OLAP data from SQL Server Analysis Services into Excel. In cases where you want to explore the data and look for interesting facts within the data by “slicing and dicing” it, PivotTables are the perfect tool. You can change the fields that are being displayed, drill into the data, change the filtering and sorting that has been applied, and in the case of OLAP data from SQL Server Analysis Services, you can use all the other features I have covered to look at the data your way.
我們曾討論過如何用數(shù)據(jù)透視表從SQL Server Analysis Services取數(shù)到Excel里。如果您想對數(shù)據(jù)進(jìn)行深入挖掘和剖析，找出其中有價值的信息，那么數(shù)據(jù)透視表是一個非常合適的工具。您可以更改數(shù)據(jù)透視表的字段顯示，重排數(shù)據(jù)，改變篩選和排序的應(yīng)用。如果您使用SQL Server Analysis Services的OLAP數(shù)據(jù)源，還可用前面我介紹過的其它功能，以自己的方式來查看數(shù)據(jù)。
There are other scenarios that involve bringing OLAP data from SQL Server Analysis Services into Excel, though, that are not as well suited to using a PivotTable. While PivotTables are an ideal tool for quickly exploring your data and finding a wide variety of answers in an ad hoc manner, they are not as well suited for building fixed reports with irregular or custom layouts. Here are a few quick examples:
還有幾種需要從 SQL Server Analysis Services 的OLAP 數(shù)據(jù)源取數(shù)的情況，但并不適合用數(shù)據(jù)透視表。雖然數(shù)據(jù)透視表是個理想的工具，它可以通過特定的方式，快速地挖掘數(shù)據(jù)，尋求多種答案，但是它不適合用來創(chuàng)建固定版式的不規(guī)則或自定義的報表。舉幾個例子：
·"Asymmetric reports,” wherein the rows or columns don’t follow a fixed pattern in terms of the data they contain (more on this below)
·自由式報告，報告的行列不遵循約定的固定模式。（詳見后敘）
·Reports that mix data from multiple data sources
·混合使用多數(shù)據(jù)源的報告
·Reports that require a lot of custom spacing and/or precise grouping and ordering, often in a different way than the data is stored in the data source
·帶有很多間隔行和/或細(xì)化分組排序的報告，常常不同于數(shù)據(jù)源的數(shù)據(jù)存儲方式。
To address those needs (and others), we wanted to provide a way to place OLAP data from SQL Server Analysis Services directly into specific spreadsheet cells without being constrained in any way by what might be in the adjacent cells. This would provide users with the power of a multi-dimensional data store in conjunction with the power of the Excel formula language and calculation engine. Let’s take a look.
為了滿足這些需求（或者更多），我們考慮提供有一種方法，將SQL Server Analysis Services的OLAP數(shù)據(jù)直接放入特定的工作表單元格中，而不受到周圍單元格的任何限制。它可同時具備多維數(shù)據(jù)存儲和Excel公式化語言及計算引擎的能力。讓我們看個例子。
Here is an example of an asymmetric report that was built using the Excel 12 beta build and the Adventure Works sample database that ships with SQL Server 2005 Analysis Services. 下例使用存放在SQL Server 2005 分析服務(wù)器上的Adventure Works樣本數(shù)據(jù)，在Excel 12 測試版上，創(chuàng)建了一張自由式報表。
Note that the fields across the top axis (on columns) do not come from the same dimension. The sales reasons (‘On Promotion’ and ‘Price’) and the countries (‘Canada’ and ‘United States’) are members from two completely distinct dimensions. Also note that while all the values are based on the items in Column A, this report contains blank “spacer” columns in columns B and E. Finally, you can see the formula for cell C2 which fetches the ‘On Promotion’ member from the ‘Sales Reason’ cube dimension. This formula uses the CUBEMEMBER function (which is one of seven new CUBE functions) to place OLAP data from SQL Server Analysis Services into Excel spreadsheet cells.
請注意標(biāo)題列的字段取自不同的數(shù)據(jù)維度。銷售成因（“促銷”和“價格”）和國家（“加拿大”和“美國”）分別取自兩個維度。另外，可以見到所有數(shù)值的計算都基于A欄中的項目，且報表的B和E兩欄是空列。最后，還可看到單元格C2中的公式從“銷售成因”的維度提取了一個成員——“促銷”。該公式使用CUBEMEMBER函數(shù)（7個新CUBE函數(shù)的一個），將SQL Server Analysis Services的OLAP數(shù)據(jù)，取到Excel電子表格的單元格中。
