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.
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.
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).
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.
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).
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.