MDX Solutions: With Microsoft® SQL Server™ Analysis Services 2005 and Hyperion® Essbase, Second Edition

Chapter 10. Extending MDX through External Functions

In this chapter, we will discuss how to create libraries containing external functions that will work with MDX to perform custom tasks that are not directly supported within MDX. Analysis Services 2005 supports several MDX functions per the MDX specification along with several extensions. In addition to that, Analysis Services supports a subset of VBA (Visual Basic for Applications) and Excel functions, which are useful for most common business scenarios. However, there is always a need to write your function that suits your business application. Analysis Services 2005 provides an extensible architecture by which your external functions can be hosted within Analysis Services so that they can be called via MDX. We refer to the external functions as stored procedures in this chapter and illustrate possible applications for these libraries. Analysis Services 2005 supports two kinds of function libraries, Component Object Model (COM) libraries and .NET common language run-time (CLR) assemblies. COM stored procedures, which are also called user-defined functions (UDFs), are already supported by Analysis Services 2000. Analysis Services 2005 was specifically designed to extend support to .NET stored procedures.

Before we get started, let's clarify some terminology. We will use .NET stored procedure and COM stored procedure to be consistent with the new terminologies introduced by Analysis Services 2005 documentation. The stored procedure ...