INF: Visual Basic for Applications and Excel Library Functions Are Not listed in MDX Builder (322966)

SUMMARY

The "Register Function Libraries Dialog Box" topic in SQL Server Books Online indicates that the Microsoft Visual Basic for Applications (VBA) and Microsoft Excel worksheet function libraries are automatically registered for use in Multidimensional Expressions (MDX).

While these function libraries are automatically registered and available for use in MDX expressions, they are not automatically listed in the MDX Builder dialog box. The functions from these libraries may or may not be listed in the MDX Builder dialog box depending upon what functions, if any, have been used from the VBA and Excel libraries in the current cube.

For details about how the list of available functions is generated for the MDX Builder dialog box, see the "More Information" section of this article.

MORE INFORMATION

The MDSCHEMA_FUNCTIONS rowset of the IDBSchemaRowset interface, in the OLE DB for OLAP provider, is used to query the Analysis Server for the list of functions available when the MDX Builder dialog box is displayed. The rowset returned is made up of the native MDX functions and any user-defined functions (UDFs) that have been registered. This is the method used by the MDX Builder to determine the functions to display in the function list.

One exception to the list of UDFs returned by the MDSCHEMA_FUNCTIONS rowset is the Excel and VBA function libraries. Although the functions in these libraries are automatically registered by Analysis Services, the MDSCHEMA_FUNCTIONS rowset will not list these functions unless a function from one of these libraries is already in use in the cube. This is a performance optimization designed to reduce the time that it takes to enumerate and return the available functions and also to reduce the size of the rowset returned to the client.

The following section goes into a little more detail about how the MDSCHEMA_FUNCTIONS rowset is built.

When a request is received for the MDSCHEMA_FUNCTIONS rowset, the OLE DB for OLAP provider reads the existing calculated members for the current cube. These are stored in the model as commands. After these cube commands are read, they are parsed and then verified. During the verification process, the provider checks the functions in the command to determine if they are valid.

The verification process first loads the built-in MDX functions, and then any user registered UDFs. After these libraries are loaded, the functions from the commands are verified. The functions used in the commands are stored on a stack and verified from the innermost function to the outermost function. The built-in MDX functions are searched first. If the OLE DB provider finds a function that is not recognized as a built-in MDX function, the OLE DB provider searches any user registered UDF libraries. If the function is not found in the built-in MDX or user registered libraries, the provider loads the list of functions available in the VBA library, and then searches for the function in that library. If the function is found, the search stops and the validation continues with the next function in the current command or with the next command. However, if the function is not found in the VBA library, the Excel library is loaded and the provider checks for the function in the Excel function library. If the function still has not been found, an error is raised.

The result of this search order means that if an Excel function is used in a cube command, both the Excel and VBA function libraries are loaded and are returned in the MDSCHEMA_FUNCTIONS rowset. However, if a VBA function is used, the Excel library is not loaded and the Excel functions will not appear in the MDSCHEMA_FUNCTIONS rowset. All user registered libraries and built-in MDX functions are always returned by the MDSCHEMA_FUNCTIONS rowset.