.NET User Defined Functions (Stored Procedures)

If you have been programming on the Microsoft platform, you must be familiar with the .NET framework and the .NET languages. .NET (Dot Net) is Microsoft's framework and strategy to connect people, business, systems, and devices. Several programming languages help in building applications using the framework, which helps in seamless integration with other applications. Analysis Services 2005 couples tightly with UDFs based on .NET languages. In addition to the tight integration, the .NET framework provides leverage through use of its security model. The model is provided by the framework and can be used to support fine-grain security on .NET assemblies added to Analysis Services. We refer to UDFs built using .NET languages as .NET assemblies as well as stored procedures in this book.

Creating Stored Procedures

You can create a .NET assembly using any of the .NET languages, such as C# or VB.Net. Similar to COM UDFs you can create your stored procedures to perform complex business computations catered towards your business applications. A few examples of stored procedures would be to perform custom business computations that will involve business logic based on certain conditions, accessing external resources such as stock price of the company from a web service to perform calculations, and accessing external resources such as data from a SQL Server to apply permissions on Analysis Services. Chapter 19 shows an example of applying permissions on Analysis Services by means of a stored procedure that retrieves data from an external service.

Analysis Services 2005 provides you with much more than fine-grain code access security for administrators when they add .NET assemblies to Analysis Services 2005. Analysis Services 2005 exposes all the MDX objects via a Server object model called ADOMD Server, where you can access all the Analysis Services such as cubes, dimensions, sets, and tuples within the object model. The server-side object model exposed to the programmer is similar to the client-side ADOMD.Net object model, but certain minor differences exist between these two object models. These differences result from the kinds of applications they support. In addition to supporting a complete object model for programming serverside stored procedures, Analysis Services 2005 also allows you to perform management operations via stored procedures using the Analysis Services Management Object, which you learn about in some detail in Chapter 12. In this section you see examples of stored procedures using the object models AMO and ADOMD server. Stored procedures using the AMO are referred to as AMO stored procedures and those using ADOMD server object model as ADOMD stored procedures

Before you look at AMO and ADOMD stored procedures you will look at the COM example in the previous section being written as a .NET stored procedure using C#. Open Visual Studio 2005 and create a C# class library application called PartialSumSproc. Enter the code shown below and compile the class library.

     using System;     using System.Collections.Generic;     using System.Text;     namespace PartialSumSproc     {         public class PratialSumSproc         {             public static double PartialSum (double [] Val, string [] Member,             string stopMember)             {                 double PartialSum = 0;                 int l = 0; //Lower bound value of the array                 int u = Val.Length; //upper bound value of the array.                 for (int i=l;i<u;i++)                 {                     //add tuple into the return set                     PartialSum = PartialSum + Val[i];                     //if the unique name of both tuple is same then break and return                     if (string.Compare (Member[i], stopMember) == 0)                     break;                 }                 //return the set back to server return PartialSum;             }         }     } 

The .NET assembly needs to be added to the database and you can use the function PartialSum in your queries. If you already have your COM UDF added to your database then you need to access the function PartialSum in the .NET assembly along with the assembly name as PartialSumSproc.PartialSum.

AMO Stored Procedures

Analysis Management Objects (AMO) is the object model used to perform management operations on an Analysis Services object. You will learn more about AMO in Chapter 12. One of the common operations in any database is backup. Assume you are the DBA for Analysis Services in your company and you want to leave on vacation. Obviously you want to enjoy your vacation and not worry about work. But if there is a mission-critical need and you are in the middle of, say, the Tasmanian ocean and are called for help, you would want to have the flexibility and control required to remotely perform operations on your Analysis Services instance. Analysis Services 2005 allows you to query the server over an HTTP connection. With Analysis Services 2005 you can send XMLA requests to the server. These requests can be for management operations, but you'll want to know the disposition of those requests, that is, pass or fail status. An easier way to do a management operation is to create an AMO-stored procedure, which can be launched via an MDX query for which you can code a return value as a status indicator.

The following code is an AMO-stored procedure in C# that contains a function that does a backup of a database. This function takes the name of the database to backup and the name of the file under which the backup has to be taken as parameters. The stored procedure connects to the Analysis Server instance via a stored procedure. You can then access the databases on the server, and iterate through the entire database till you find the database passed as the parameter. Once you have identified the database, you just need to issue a backup command. The backup of the database is taken on the default backup database directory of Analysis Services instance.

     #region Using directives     using System;     using System.Collections.Generic;     using System.Text;     using AMO = Microsoft.AnalysisServices;     #endregion     namespace AmoSprocExample     {         public class AmoSprocExample         {             public AmoSprocExample () {}             public static bool Backup (string sDatabaseName, string backupFileName)             {                 try                 {                     AMO.Server asServer = new Microsoft.AnalysisServices.Server ();                     // Connect through AMO with connection string that has server name                     // Here . indicates connecting to itself.                   asServer.Connect (".");                     //Iterate through the databases on the server till you                     //find the specified database and then perform backup                     for (int i = 0; i < asServer.Databases.Count; i++)                     {                         AMO.Database asDB =     asServer.Databases.FindByName (sDatabaseName);                         if ( asDB != null)                         {                             asDB.Backup (backupFileName + ".abf");                             return true;                             }                         }                     }                 catch (AMO.AmoException e)                 {                     System.Console.WriteLine (e.Message);                 }                 return false;             }         }     } 

To create this stored procedure, first create a new C# project for a class library using Visual Studio. Then add the .NET assembly Analysis Management Objects (Microsoft.AnalysisServices.dll) to the references as shown in Figure 10-2. Add the preceding code to your project and compile the project. Congratulations, you just created a dot net assembly. You can write similar functions within the same stored procedure that can perform various management operations on your server.

Figure 10-2

ADOMD Server Stored Procedure

A key attribute of Analysis Services 2005 stored procedures is the server object model called ADOMD Server, which is exposed by Analysis Services. You can now leverage the server ADOMD object model and create a stored procedure that will perform some of your custom business operations that were previously done on the client side. You can restrict the amount of data being retrieved from the server as well as perform many of the operations on the server, thereby getting improved performance.

Following is a C# code that uses the Server ADOMD object model. This stored procedure contains a function called custom filter that filters a set of tuples based on a sampling percentage. This is a stored procedure that is useful when you want a sample of customers for whom you want to do a marketing study. For example, you identify that there are one million customers who are extremely important for your business. Assume they are highly valued customers based on the purchases they make. Now you want to do a marketing study of a sample of such customers or send surveys to a subset of these customers to expand your line of business. How would you go about getting the sample of customers? You can retrieve all the customers, pick the top N percentage of customers, and perform a sampling operation on the server side using a stored procedure. The following stored procedure shows how this is done. The stored procedure contains a function called CustomFilter that takes an MDX Set and a sampling percentage as inputs and returns an MDX Set as an output. The CustomFilter function is simple in the sense that it takes the every Nth member based on the sampling percentage from the Set and adds them to a new Set. You can definitely write your own custom filter function based on your requirements.

     #region Using directives     using System;     using System.Collections.Generic;     using System.Text;     using AdomdServer = Microsoft.AnalysisServices.AdomdServer;     #endregion      namespace AdoMdServerExample     {         public class AdoMdServerExample         {             public AdoMdServerExample ()             {             }             Public static AdomdServer.Set CustomFilter (AdomdServer.Set mdxSet,                                                 int samplingPercentage)             {                 AdomdServer.SetBuilder sampleSet = new                         Microsoft.AnalysisServices.AdomdServer.SetBuilder ();                 int iTupleCount = mdxSet.Tuples.Count;                 int iTupleSample = iTupleCount * samplingPercentage/100;                 for (int i = 0; i < iTupleCount; i++)                 {                     if (i % (samplingPercentage) == 0)                     {                         sampleSet.Add (mdxSet.Tuples[i]);                     }                 }                 return sampleSet.ToSet ();             }         }     } 

This code is just an example of how to create a stored procedure that uses the ADOMD Server object model. Using the server object model you can execute MDX expressions and, based on the result, you can perform custom operations. Analysis Services 2005 uses the ADOMD server object model for some of the viewers used in Data mining (you will learn about data mining and its viewers in Chapter 14). There are built-in system stored procedures that make use of the ADOMD Server object model to restrict the amount of data being sent to the server to achieve the best performance and user experience in the viewers. To create an ADOMD stored procedure you need to include the assembly msmgdsrv.dll inside your C# class library references. You can find msmgdsrv.dll under the Analysis Services bin directory.

Static vs Non-Static Functions

Analysis Services 2005 supports using static as well as non-static functions. If a function is defined as non-static (static keyword is not defined in function definition) then each time the function is called Analysis Services needs to create an instance of the class and then needs to load the function. This could be quite expensive if the function is called several times within a query. Unless and until you absolutely need non-static functions we recommend you to define static functions. When static functions of a class are called then Analysis Services directly invokes the function after loading the assembly. The assembly load operation occurs only once and hence you do not incur performance degradation as compared to non-static functions when the function is called several times within a query.

Adding Stored Procedures

Once you have created the stored procedures you can add them to Server scope assemblies or database scope assemblies. Similar to adding the COM assembly, you need to use the SQL Server Management Studio to add the stored procedures. The following steps show how to add a stored procedure:

1. Connect to your database using SQL Server Management Studio.

2. Navigate to your database, right-click the Assemblies folder, and select New Assembly.

3. You will now be in the register assembly dialog that you used earlier for adding COM assemblies. Leave the type of the assembly as .NET assembly and specify the assembly file as shown in Figure 10-3.

   
   Figure 10-3

4. Select the option to include debug information so that you can debug the assembly within the Analysis Server in case you run into problems. If you click the Details button you will see all the functions that are part of the assembly as shown in Figure 10-4. This is because .NET assemblies are compiled to an intermediate language and, using reflection, Analysis Services Tools is able to retrieve the functions available in the assembly. During runtime this code is compiled into machine language and then executed by the .NET framework.

   
   Figure 10-4

5. Select a permission set option for the assemblies you are adding. Analysis Services leverages permission sets from the .NET framework to provide fine-grain security on the assemblies being added to Analysis Services. You can select three different permission set options:

   1. Safe: The Safe permission set forces the assembly to only perform computation operations. Whenever a stored procedure marked with the Safe permission set tries to access external resources, the .NET framework restricts the permission and throws an error.

   2. External Access: The External Access permission set is intended to allow managed code to access external resources from the server, without compromising the reliability of the server. You can use this if you as the DBA trust the programmer's ability to write good code and if there is a need to access external resources such as data from an outside file.

   3. Unrestricted: If an assembly has been marked with the Unrestricted permission set, it can perform any operations on the server or on any external resources. The Unrestricted permission set should be used with caution and only for operations that need it. If you are the DBA and you have full confidence in the programmer and treat him equivalent to a DBA of the Analysis Server, you can give the Unrestricted permission set.

   Note

   Only database administrators can add assemblies to Analysis Server.

6. The PartialSumSproc and AdomdSproc stored procedures only perform computation, hence the Safe permission set is sufficient.

7. Next, you need to specify the impersonation of the assembly. Don't let the verbiage throw you; it just means you need to indicate what credentials Analysis Services will execute this assembly under. Analysis Services 2005 provides five options for impersonation modes:

   1. Use a Specific user name and password: This option should be used whenever you have an assembly that has to be executed only under certain account credentials because that specific account has access to an external source that is used within the stored procedure. This is typically used whenever you want to read some data from a web service or from a relational data source or any other external resource.

   2. Use the service account: Whenever you have operations that need to be performed under the credentials of the Analysis Services instance, you need to specify this operation. An example of a stored procedure that would need this is an AMO stored procedure that does management operations on the server.

   3. Use the credentials of the current user: This option is recommended when you want the stored procedure to be executed under the credentials of the user accessing it. This is a safe option to select but if your stored procedure accesses external resources and the user does not have permissions the stored procedure would be executed.

   4. Run the assembly under an anonymous account. This is the least-privileged account and will not have any access to any data. Typically, stored procedures that are computationintensive can be specified with this option.

   5. Default. When the default option is chosen it is actually translated to one of the four options you just learned based on the selected permission set. If the permission set is Safe, selecting the default option for impersonation will set the impersonation mode to Impersonate Service Account. If the permission set is Unrestricted or External Access, selecting the default impersonation mode results in the impersonation mode being set as Impersonate current user.

8. Select the default option and click OK.

9. Following steps 2 through 6, add the AMO stored procedure AMOSproc but select the permission as Unrestricted. You will learn why Unrestricted permission is used for AMOSproc in the next section.

You have successfully created stored procedures and added them to the AnalysisServices2005Tutorial database. Next you see examples of using them in queries.

Querying Stored Procedures

Similar to COM UDFs, you can call the stored procedures using the assembly name, interface name and function name. However, for .NET assemblies you need to specify the assembly name followed by the fully qualified class name and then the function name. You can just specify the assembly name followed by function name if there is no ambiguity in the function name.

You can use the PartialSumSproc assembly's PartialSum function to calculate the cumulative profit as shown in Figure 10-5.

Now that you have added the ADOMD stored procedure, you'll want to see how to use it. Assume you want to see all the cities that contributed to a profit of $1000 or more; you would use the following MDX Query.

     SELECT measures.profit ON 0,     FILTER ([Dim Geography].[City].[City].MEMBERS, measures.profit > 1000) ON 1     FROM [Adventure Works DW] 

The query results in 287 cities that have a profit greater than $1000. If you want to do a marketing campaign in only 10 percent of those cities, you can use the CustomFilter function in addition to the MDX Filter function as follows:

     SELECT measures.profit ON 0,     AdoMdServerExample.CustomFilter (     FILTER ([Dim Geography].[City].[City].MEMBERS, measures.profit > 1000),     10)     ON 1     FROM [Adventure Works DW] 

Figure 10-5

As you can see, your stored procedure takes the results of an MDX function because the Filter function returns an MDX set. That set is the parameter taken by your stored procedure. Vice versa, your stored procedure also has the capability of taking in MDX objects as parameters and returning MDX objects. Stored procedures are powerful features in Analysis Services 2005.

You have seen how stored procedures can be used within MDX queries. Another way of invoking stored procedures being executed in Analysis Services 2005 is via the CALL statement. You have added the AMO Stored procedure assembly to your database. If you are not concerned about the return result of the stored procedure and just have to execute it, you can use the CALL statement. To take a backup of the AnalysisServices2005Tutorial database you can send the following MDX statement:

     CALL AmoSprocExample.Backup ("AnalysisServices2005Tutorial","AS2K5") 

Executing the query results in the backup of the database AnalysisServices2005Tutorial under the name AS2K5.abf in the Analysis Services backup directory.

You have added the AMO stored procedure with Unrestricted permission set access. Now see what happens if you change the permission set to External Access. You can change the permission set of an assembly by selecting the properties of the assembly and then changing the appropriate permission set.

Executing the preceding CALL statement to perform a backup now results in the following error message:

     Execution of the managed stored procedure Backup failed with the following error:     Exception has been thrown by the target of an invocation.That assembly does not     allow partially trusted callers. 

As mentioned earlier, when you specify the external access permission set, the .NET framework only allows assemblies to have external access. In this case your assembly AMOExample uses an assembly Microsoft.AnalysisServices that is not fully trusted by the .NET framework and does not have the full privilege of performing any operation in the specified permission set. Therefore, the .NET framework raises the error and does not execute the assembly. If you are a DBA and you restrict access to assemblies that you do not fully trust, your Analysis Server will be well secured and reliable.

Debugging Stored Procedures

Whenever you write code there are bound to be instances or conditions under which your stored procedure might not operate in accord with expectations. You can obviously test your stored procedure external to Analysis Services in conditions where you do not use the object models of Analysis Services. If you are using the ADOMD Server object model, debugging the stored procedure external to Analysis Services is difficult especially in the conditions where differences between client and server models exist. For example, the server ADOMD has an object called MDXExpression that helps you to evaluate MDX expressions within your stored procedure. This is not available in the client object model, so you will need a different method for debugging your stored procedure. The new method is not that complicated and is similar to debugging any program, but you need to debug within Analysis Services. Assume you want to debug your ADOMD stored procedure example in this chapter because you are not getting expected results. The following steps show how to debug it:

1. Execute the MDX query using the stored procedure.

2. Launch Visual Studio and attach it to your Analysis Server instance. You can attach the debugger to Analysis Services by selecting ToolsAttach to Process. In the Attach to process window search for the process msmdsrv.exe, select the process. Select Managed and Native options for Attach to as shown in Figure 10-6 and click Attach.

   
   Figure 10-6

3. Now you need to make sure you load all the symbol information of your stored procedure. For this, select DebugWindowModules in Visual Studio. You will now see the modules window that shows all the DLLs loaded by Analysis Services executable msmdsrv.exe. Search for the DLL AdoMdServerExample.DLL, right-click and select Load Symbols. In the find symbol dialog, point to the folder that contains the symbol information for the AdoMdServerExample.DLL (that is, the file AdoMDServerExample.pdb).

4. In Visual Studio select FileOpen and select the source file for AdoMDServerExample, which is AdoMDServerExample.cs.

5. Select break points at certain lines within the CustomFilter function.

6. Execute the MDX query once again. You will now hit the break point within Visual Studio as shown in Figure 10-7.

   
   Figure 10-7

7. You can see all the local variables and you will be able to perform all the debugging operations that are feasible within Visual Studio. If your function calls other functions within the same stored procedures or other stored procedures, you will be able to debug those similar to debugging any of your programs.

8. Once you have identified your problem and made changes to the code and a new version of the binary, add the new version of the binary to Analysis Services by deleting the older version and adding the new version using the Register assembly dialog.