Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

Chapter 11. Extending MDX Using External Functions

The MDX language supports an extensive set of functions for business analysis. In addition, Analysis Services exposes certain VBA (Visual Basic for Applications) and Excel functions as built-in external functions, which can be accessed through MDX. These functions already supported in Analysis Services should meet most of your design and query requirements. However, you may sometimes have a need for custom operations. Analysis Services provides an extensible architecture by which you can add your own custom functions to Analysis Services and access them through MDX. These external functions are referred to as user-defined functions (UDFs).

UDFs offer the power of the programming language of your choice seamlessly integrated into Analysis Services. This allows you to extend the MDX language to support any business needs not covered out-of-the-box such as accessing external data or computing complex calculations. As an example, you could write a function to retrieve live stock data for calculations in MDX queries or expressions. By building a UDF to collect and pass along relevant data, your MDX query can reflect such up-to-date information.

Three types of external functions are described and demonstrated in this chapter: built-in UDFs, .NET-based UDFs (commonly referred to as stored procedures), and COM-based UDFs. Built-in UDFs are VBA and Excel functions that are exposed automatically in Analysis Services. .NET UDFs can be written ...