You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Last time, I explained the seven new CUBE functions that are available in Excel 12 to fetch data from Analysis Services cubes. With the theory that a picture is worth a thousand words, I would like to walk through a few examples that illustrate how these functions can be used to produce reports based on OLAP data (data that comes from SQL Server Analysis Services).
Example 1 – Basic ReportLet’s start by taking a look at this report which shows some data from the Adventure Works sample cube (which is included with SQL Server 2005 Analysis Services).
(Click to enlarge)
Here’s the same spreadsheet, configured to display the formulas in each cell (using Formula Audting Mode or CTRL + `).
Among other things, this report does a good job of demonstrating how easy it is to combine data out of a database with Excel’s formula language.
Example 2 - Report Filters Many different OLAP reports call for dynamic filtering. When you use a PivotTable, you do this using Report Filters (the drop downs at the top of the PivotTable). We did the work so that these Report Filters can be used by CUBE functions as well so that you can filter the values in your CUBE functions with one or two clicks. Report Filters behave just as though they had been authored using the CUBESET function. This means that you can use CUBERANKEDMEMBER functions to enumerate the items that have been selected in the filter and that you can pass the report filter as an argument to the CUBEVALUE and CUBESETCOUNT functions.
Here’s a report that is filtered by a PivotTable Report Filter.
Here’s the same spreadsheet, configured to display the formulas in each cell.
Hopefully these examples help illustrate how these formulas can be used. Next time, I’ll talk about how Formula AutoComplete simplifies the task of authoring CUBE functions.
Will there be any improvements in the Data Analysis ToolPak? I feel (everyone does) Excel has a lot to improve on that side, and I am sure there is plenty of potential to do so. Thanks
Interesting question about the Data Analysis ToolPak. Since the Analysis ToolPak functions are being incorporated into Excel 12 as built-in functions (so breaking all non-English language workbooks that have used the English function names which presumably would now be changing? or would they all retain the English language function names in all other supported languages?), whither the Data Analysis ToolPak? Personally, I'd argue putting a bullett through its metaphorical brain and pushing users who need to perform real statistical analysis into using real stats packages would be a very good thing. Will XL12 include a stripped-down DATP as an add-in, or will it also be built into XL12?
As Harlan mentions, the Data Analysis ToolPak functions have been built in this version. See blogs.msdn.com/.../483205.aspx for more information. The remainder of the DATP will be shipped as an add-in. If you have specific feedback of things you would like to see us think about in future versions, I am all ears. Thanks!