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.