Extending MDX with Stored Procedures - Microsoft SQL Server 2008 Analysis Services - page 234

Command Execution.”) Analysis Services 2008 extends stored procedures functionality by
enabling calling back into stored procedures when certain events on the server occur. You
can use the process shown in Figure 14.1 to create, publish, and use stored procedures.

Database Developer
writes custom code in
procedural language

Code is compiled,
usually using Visual
Studio

Managed or
COM dll(s) is
produced

Client Application
calls the function
using MDX

DLL is deployed
to Analysis
Server

Analysis
Services

FIGURE 14.1

You use this process to create and use stored procedures and user-defined

functions.

As you can see, you start by authoring stored procedures. You can use a common language
runtime language such as C#, Visual Basic .NET, C++, or even COBOL to create a library.
In addition, you can write your library in languages that support COM automation, such
as C++, Visual Basic, Delphi, and others.

You then compile the code and deploy it in units called assemblies. An assembly is a
compiled module that contains both metadata and code. The term assembly usually refers
to managed code. In Analysis Services, we use it to refer to libraries written in managed
code or in native code.

In its simplest form, an assembly is a dynamic link library (DLL) in which all the function-
ality is self-contained. In a more complex form, an assembly could comprise multiple
DLLs and resource files and might depend on other assemblies. Analysis Services can load
and host assemblies. Native code compiles into COM libraries that contain or reference a
type library. That type library contains metadata about the classes, interfaces, and func-
tions that the library implements.

After writing and compiling the code for your stored procedure, you deploy the resulting
DLL to Analysis Services. When you are done, client applications and MDX expressions
can call the functions that the assembly implements.