Professional SQL Server™ Analysis Services 2005 with MDX

Chapter 10. Extending MDX using External Functions

The MDX language supports an extensive set of functions for business analysis. In addition to that 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 supported in Analysis Services should meet most of your design and query requirements. However there is always a need for custom operations. Analysis Services provides an extensible architecture by which you can add your custom function to Analysis Services and access them through MDX. These external functions are also referred to as user-defined functions (UDF).

The UDF offers seamless integration with existing Microsoft Business Intelligence technology with all the power to be found in the programming language of your choice. This suggests you can accomplish anything your coding skills will support and that it will be leveraged by the existing benefits of Analysis Services. As an example, it would not be unusual to use recent stock data for calculations within 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.

Two types of external functions are described and demonstrated in this chapter: COM-based UDFs and .NET-based UDFs (commonly referred to as . NET assemblies). There are some crucial nuggets of data any Business Intelligence informavore will want to consume. You should ...