VBA functions in AS2005 (MDX)

Similarly to Analysis Services 2000, Analysis Services 2005 uses stored procedure technology to extend the number of built-in MDX functions. It automatically registers two libraries: the Visual Basic for Applications library and the Microsoft Excel worksheet library. Visual Basic for Applications library is registered as a CLR assembly and Excel library – as COM assembly. MDX expressions can use any of supported VBA and Excel functions, as if there were native MDX functions.

However there is an important difference between native MDX functions and VBA or Excel functions – performance. In chapter 14 of our book “Microsoft SQL Server 2005 Analysis Services” I explained how CLR and COM assemblies are hosted inside of Analysis Services process. Basically CLR assemblies are hosted in a separate application domain and in addition to marshaling from native to managed code and back, all the calls are also marshaled across application domains. Therefore calling a VBA function is an expensive operation comparing to calling an MDX function.

Read more and download document with the VBA functions in MDX here...

Note: This download is available from "Microsoft SQL Server 2005 Analysis Services" book download page.