Professional SQL Server™ Analysis Services 2005 with MDX

10.2. Built-in COM UDFs

The MDX language provides certain functions (for example, SettoStr, TupletoStr, StrtoTuple, StrtoSet, and StrtoMember) that are helpful in translating MDX objects to external data structures such as string or arrays or from external structures to MDX objects. The MDX language does not support certain common utility functions like performing operations on strings, such as trimming or getting the first substring match, or date operation functions. Such functions are natively supported in SQL, and they might be quite commonly used during MDX queries. Because Visual Basic for Applications (VBA) and Excel contain a rich set of such functions, they are readily available as a COM DLL. Analysis Services 2005 takes advantage of this and exposes certain functions in Excel and VBA out of the box. Analysis Services 2000 also supported certain VBA functions out of the box. However, on 64-bit platforms, VBA functions were not supported because VBA dll is 32 bit. Hence in Analysis Services 2005 the supported VBA functions are implemented using native and managed code instead of delegating the call to the VBA dll. Important functions that can impact performance are implemented natively, while the remaining supported VBA functions are implemented as a built-in .NET assembly. If you want to use the COM VBA functions and do not want to use the VBA functions that have been implemented in .NET language then Analysis Services 2005 provides a configuration setting to switch back ...