Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)

Overview

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 builtin 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 already be aware that COM UDFs are created using C or C++ or even VB6, the output of which are COM DLLs. This functionality was available in Analysis Services 2000 — so there is nothing new there and is supported in Analysis Services 2005 for backwards compatibility of existing applications. On the other hand, the relatively new technology called .NET assemblies is supported with Analysis Services 2005. Your custom function written in .NET language, like VB.Net, C#, or even managed C++ for that matter needs to be compiled as a DLL (Dynamic Link Library).

Analysis Services provides native support for .NET assemblies and therefore debugging these procedures is quite straightforward within an instance of Analysis Services. Typically a person other than the administrator of the instance creates the UDFs. In such a case the administrator might not trust the coding abilities of the person — not an usual state of affairs. The good news is .NET assemblies provide several levels of code security hence, constraints are made on what the code can do. You see that concept demonstrated in the .NET assembly example in this chapter.

Microsoft has shipped a client object model called ADOMD.Net (ActiveX Data Objects for Multidimensional Databases), which is a programming interface for querying multidimensional data from Analysis Services. The client object model has classes for the objects on the server, which help in easy traversal of the objects at the client side. In addition to that the object model supports querying data and traversing through the result set. Analysis Services 2005 provides you with an equivalent ADOMD Server object model that can be used within your UDFs for custom operations on the server. One example is when the result set is really large and you are working on a low-bandwidth network. Where you would want to restrict the data on the server side rather than on the client. You can also include custom calculations for your business being applied to the objects directly on the server side with your custom C# code.

All told, Analysis Services 2005 allows you to write your custom code as a COM (Component Object Model) UDF or as an assembly with one of the .NET languages. Further, you can add them to the database so long as the dot net assembly has the appropriate credentials — for extra security only an Analysis Services administrator is allowed to add all assemblies. In this chapter you see examples of COM UDF creation in Visual Basic 6.0 and .NET assemblies using C#.