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

Creating Stored Procedures
The first step in creating a stored procedure (illustrated in Figure 14.1) is to write your
code in a procedural language and compile it into a binary file—an assembly.
Before you can use an assembly, you have to deploy it to Analysis Services. You can use
either the Data Definition Language (DDL) command <Create Assembly> or the Analysis
Management Objects (AMO) model to deploy your assembly. (See Chapter 34, “Analysis
Management Objects,” for more information about AMO.) You can also use the tools
shipped with Analysis Services: SQL Server Management Studio and Business Intelligence
Development Studio (BI Dev Studio). These tools use AMO under the covers to create the
DDL and send it to Analysis Services. When the DDL executes, it creates the assembly
object on the server.
An Assembly object is a major object. In the current release, Analysis Services supports
only server- and database-based assemblies. Each assembly is available to all users of a
system or users of any cube in a database.
As do most other metadata objects, the Assembly object has Name , ID , Description , and
other properties. However, the way of creating and storing an Assembly on the server
depends on the type of the assembly; a difference exists between common language
runtime and COM assemblies. Properties of the Assembly object also vary for different
types of the Assembly object. Analysis Services internally has two different Assembly
objects: ClrAssembly and ComAssembly .
Creating Common Language Runtime Assemblies
An assembly usually is created on a development computer and then moved to a server
computer. It is quite common for a service account, under which the server runs, not to
have access to the directory where an assembly is located on the development computer
and vice versa. Therefore, we decided not to rely on using file APIs to copy files, but to
embed the binary representation of the files into the DDL request.
When a client application deploys an Assembly object, it should detect all files that are
part of the assembly, read those files, and write the bytes into the DDL request. This might
seem like a cumbersome process, but you do not need to do it manually if you are using
AMO, which will do the job for you. (SQL Server Management Studio and BI Dev Studio
use AMO to deploy assemblies; so if you use a user interface, you don't need to deploy
dependencies manually.) Under the covers, AMO analyzes the root DLL of the assembly,
determines the list of files the assembly depends on, filters out the system assemblies
already installed on the server by the .NET Framework, and transfers the remaining files to
the server. For example, if your assembly A depends on the assembly System.Data and the
assembly B (written by you), AMO packs assemblies A and B into the DDL request.
Optionally, you can embed the bits of the files that contain debug information (PDB files)
in the request.
When Analysis Services receives a request to create or alter an assembly, it decodes the
binary representation of each file and creates files that are stored in a data directory of