CUBE Functions 3: Formula AutoComplete revisited
Previously when discussing CUBE functions, I showed a couple of examples of reports based on OLAP data that could be built using CUBE functions. Now I’d like to explain how we’ve done something very special with Formula AutoComplete to make it easy to navigate the OLAP namespace while authoring CUBE functions.
Formula Auto-Complete for CUBE function arguments
Formula AutoComplete for CUBE function arguments has some special behaviours relative to the other functions in Excel. As a brief refresher, Formula AutoComplete is a feature that provides a list of values from which to choose as you write formulas … here is a blog post that describes this in detail. In most Formula AutoComplete scenarios, Excel knows the list of values (formulas, named ranges, table names) that it should display because those values are part of the Excel application. For example, when you start typing a function name, Excel can give you a list of all the other functions that start with the same character(s), as is shown in this screenshot.
(Click to enlarge)
In this case, Excel already knows what all the possible function names are that begin with “s”, so it can easily display a list of function names that begin with the specified character(s). Ditto named ranges, UDFs, table names, etc.
For many arguments to the new CUBE functions, however, we have a different scenario. Excel does not inherently know about the multi-dimensional database (OLAP cube) from which data is being fetched. In order to provide an auto-complete dropdown, it is necessary for Excel to query the multi-dimensional database or OLAP cube to find out what the set of valid items will be. Here’s an illustration of how this works. In this example, I have already created a connection named “Adventure Works” to the Adventure Works database on SQL Server 2005 Analysis Services.
I will start by entering a CUBEMEMBER function that uses the Adventure Works connection, and when I get to the second argument, (which calls for a member_expression,) I will only enter the opening quotation marks that tell Excel I am planning to enter a string.
(Click to enlarge)
The list of possible values that appear in the dropdown did not come directly from Excel. How’d that work? Excel issued a query to the Adventure Works OLAP cube and displays a list of dimension names from that cube. Let’s say that I choose the Dimension named “Customer” and then enter a period (which is used as a separator by the MDX language).
(Click to enlarge)
I see that there are several hierarchies in the Customer dimension. Using the arrow and tab keys, I will select the “Customer Geography” hierarchy and enter another period.
(Click to enlarge)
The auto-complete drop down shows me that there is a single member at the top of the Customer Geography hierarchy, and that item is “All Customers”. By choosing this item and entering another period, I will see an auto-complete drop down of the children of “All Customers” (which are the countries in which the customers are located).
(Click to enlarge)
At any point, I can finish the argument by entering the closing quotation marks.
The key point that I want to make here is that the Formula AutoComplete feature is providing a mechanism for users to navigate the hierarchies in the multi-dimensional database. Even if you have no prior knowledge of the multi-dimensional database to which you are connecting, Excel’s Formula AutoComplete feature will show you the dimensions in the cube, the hierarchies in each dimension, and the members (and their children and grandchildren, etc.) that are contained in each hierarchy. Also, it can be much faster to use auto-complete to enter CUBE functions into Excel formulas because you only need to identify the item you want from a drop-down list and then hit the Tab key, as opposed to typing the full MDX name for each function parameter.
The name that you get for a member using Excel’s auto-complete will be a fully qualified name because you make a selection at each level of the hierarchy. It is not the only name that could be used, nor is it the special “member unique name” for a member in the cube. When you know that a shorter MDX expression will resolve to the same member, you are free to enter the shorter expression. It’s just that Excel will help you to navigate the namespace of your database when you’re not already familiar with that data.
The list of items that is displayed in each case is the list of the first fifty (50) items which match the characters that have been entered so far. As you enter more characters, the auto-complete drop down list is automatically updated.
This is a feature I love to demonstrate – for folks that use Analysis Services cubes, it really is going to be a very helpful feature.
Next time, I’ll talk a bit about the work we’ve done in Excel 12 to make connection management easier.
