SQL Server 2005 : Beyond OWC: Full-On OLAP Development (part 4) - Analysis Services CLR Support: Server-Side ADO MD.NET

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
7/30/2012 6:15:50 PM

Analysis Services CLR Support: Server-Side ADO MD.NET

Our last stop on this tour through various OLAP programming models brings us back to the server itself.  In the Analysis Services CLR model, you can load .NET assemblies onto your Analysis Services server and call their functions programmatically from ADO MD.NET or XMLA, or manually from Management Studio’s MDX Query window or XMLA Query window.


Unlike SQL CLR integration, Analysis Services CLR support is on by default. In fact, it cannot be disabled. However, only users with administrative permissions on the Analysis Services server or database may load CLR assemblies. This contrasts significantly with the SQL CLR feature, which can be made available to non-administrative users but is disabled by default and must be turned on using T-SQL or the SQL Server Surface Area Configuration tool.

Visual Studio offers no project templates for server-side Analysis Services programming and the programming model does not use code attributes, but the CLR support in Analysis Services is similar to, and about as easy as, SQL CLR programming. In Analysis Services, such functions are referred to as managed stored procedures.

Managed stored procedures can execute without returning anything or they can act as functions that return values or objects. Although these function-like stored procedures can return scalar values, they will typically return and/or accept MDX entities such as tuples, sets, and expressions. This is made possible through the use of the Microsoft.AnalysisServices .AdomdServer assembly, which includes definitions for these types and for special builder objects used to create some of them.

Writing Managed Stored Procedure Code

You can start writing Analysis Services managed stored procedures simply by creating a Class Library project in Visual Studio 2005 and setting a reference to the Microsoft.AnalysisServices.AdomdServer library. Then you simply add the following using statement at the top of a C# class file:

using Microsoft.AnalysisServices.AdomdServer;

and you’re ready to write your first managed stored procedure.

For example, the code in Listing 4  implements a CLR function that accepts an AdomdServer.Set object and a filter expression (as a string), and then returns a subset of that set containing just the tuples that meet the filter condition.

Listing 4. CLR Function FilterSet
public Set FilterSet(Set set, string filterExpression)
  Expression expr = new Expression(filterExpression);
  SetBuilder resultSetBuilder = new SetBuilder();
  foreach (Tuple tuple in set)
     if ((bool)(expr.Calculate(tuple)))
  return resultSetBuilder.ToSet();

Notice the use of the Set, Expression, and SetBuilder objects for typing the function’s parameters, return type, and variables, and notice the Tuple object for the controlling variable within the foreach loop. A string containing the MDX expression we want to use is passed to the Expression object’s constructor. From there, the Expression object’s Calculate method is used to test whether each tuple in the passed-in set meets the filter condition; if it does, the code adds the tuple to the SetBuilder object’s internal collection via its Add method. Finally, the SetBuilder object’s ToSet method is used to generate the Set object that is the function’s return value.

Once the assembly is compiled and loaded into your database, you can call the FilterSet expression from an MDX query as if it were part of the MDX language itself.

Loading the Assembly

To load an assembly, first right-click the Assemblies node underneath your Analysis Services server in Object Explorer and choose New Assembly (Figure 11).

Figure 11. The New Assembly menu option for the Object Explorer’s window’s server Assemblies node

The Register Server Assembly dialog box opens (Figure 12).

Figure 12. The Register Server Assembly dialog box in Management Studio

Select .NET Assembly from the Type drop-down list, and then enter the full path to your assembly in the File Name text box or click the ellipsis (...) button to the right of the text box to specify the assembly through a standard File Open dialog box.


You can also add assemblies to individual databases rather than to the server as a whole. The procedure is identical except for one difference: Rather than right-clicking your server’s Assemblies child node, you right-click the Assemblies child node belonging to a specific database (highlighted in Figure 19-36). The Register Database Assembly dialog box will appear instead of the Register Server Assembly dialog box. The two dialog boxes are identical except for their names.

By default, a “friendly name” is generated for your assembly in the Assembly Name text box. This name will be identical to the file name of your assembly, minus the .dll extension. You can change this to any name you like, but you must remember it because you will need to use it when you call any of the assembly functions from MDX. If you want to debug your assembly, select the Include Debug Information check box. We will provide full details on debugging server-side managed code shortly.

As with SQL CLR assemblies, you can assign the assembly any of three permission sets: Safe (the default), External Access, or Unrestricted (equivalent to the Unsafe permission set for SQL CLR assemblies). You can also set the impersonation technique used by the assembly. Options here include specifying a particular account; using the service account; and impersonating the current user, the anonymous user, or an Analysis Services–defined default. The Impersonation setting is pertinent only when you select the External Access or Unrestricted permission set because the Safe permission set does not grant access to resources outside of Analysis Services, making specific account permissions irrelevant.

After you have specified your assembly’s type, file name, “friendly name,” debug information option, permission set, and impersonation mode, click the OK button and the assembly will be permanently loaded (unless you later delete it). Functions within the assembly are now callable using the naming convention AssemblyName.FullClassName.FunctionName, where AssemblyName is the assembly’s “friendly name,” FullClassName is the fully-namespace-qualified name of the class in which your function is defined, and FunctionName is the name of your function.

More Info

You can also load Database-level assemblies by adding them to Analysis Services projects in Visual Studio. Right-click on the Assemblies node in Visual Studio’s Solution Explorer window and choose New Assembly Reference... from the shortcut menu to invoke the Add Reference dialog box, where you can add one or more assemblies to your project. After you add an assembly, you can change its friendly name, permission set, impersonation mode, and description via the Properties window. Once the project is deployed, the assembly will be physically loaded into the database on the server.

Calling Managed Code from MDX

Calling your function from a query is easy. 

SELECT Chapter19AdomdServer.Chapter19AdomdServer.StoredProcedures.FilterSet(Shippers.
[Shipper Name].MEMBERS, "[Shipper Name].CurrentMember.Name < 'U'") ON COLUMNS,
      Time.Year.MEMBERS ON ROWS
FROM Sales
WHERE Measures.[Total Sales]


You can execute this query from your own application (using ADO MD.NET or XMLA), a Management Studio MDX Query window, or an XMLA Query window (if the query is embedded in the appropriate XML stream). Regardless of the method you use, the query should yield the following results:

 All ShippersSpeedy ExpressFederal Shipping

Notice that the set we submitted to the FilterSet function is Shippers.[Shipper Name].MEMBERS, the MDX expression passed is [Shipper Name].CurrentMember.Name < ‘U’, and the resultant filtered set becomes our query’s ON COLUMNS expression. The result is that only shippers beginning with a letter that comes before U in the alphabet appear across the columns axis (which means that United Package and the Unknown members are skipped).

Debugging Server-Side Managed Code

There is no project type for Analysis Services assemblies, so Visual Studio has no innate awareness of them, but it is still possible to debug your .NET code running in Analysis Services databases. To do so, choose Attach To Process from the Debug menu in Visual Studio to open the Attach To Process dialog box (Figure 13).

Figure 13. The Attach To Process dialog box in Visual Studio

Select the Show Processes From All Users check box in the lower left of the dialog box, and then select the msmdsrv.exe process in the Available Processes list box. The string “Managed Code” should appear in the (grayed out) Attach To text box; if not, click the Select... button to the right and select the Debug These Code Types option button and the Managed check box in the Select Code Type dialog box (Figure 14) and click OK.

Figure 14. The Select Code Type dialog box


The next time you open the Attach To Process dialog box, the Show Processes From All Users check box should be selected by default. Also, when the msmdsrv.exe process is selected, “Managed Code” should appear in the Attach To text box without requiring you to use the Select Code Type dialog box again.

In the Attach To Process dialog box, click the Attach button to place Visual Studio in Debugging mode. You can now set breakpoints in your code, which will be hit whenever the CLR code is called from an MDX query, whether it is run from other .NET code or from an MDX Query window or XMLA Query window in SQL Server Management Studio. To test this, set a breakpoint in the first line of code in the FilterSet function, return to Management Studio, and re-execute the previous MDX query. You will hit your breakpoint and have full access to the Visual Studio debugging tools, including the Locals window and expression visualizers (Figure 15).

Figure 15. Visual Studio in Debugging mode, with Analysis Services CLR stored procedure source code being traced

Void Functions

You can also create void functions (Subs in Visual Basic) that perform various actions and do not return data. Typically, such functions use AMO to perform various data definition tasks, including creating a new database and processing a cube. By combining AMO with the AdomdServer library’s Context object (similar in concept to the SQL CLR SqlContext object), you can create DDL stored procedures that run in the context of the connection you’ve already established. For example, start by inserting the following namespace code at the top of a class file:

using Microsoft.AnalysisServices;
using Microsoft.AnalysisServices.AdomdServer;

Then use the code in Listing 5 (adapted from one of our initial AMO examples) to implement a stored procedure that creates a new database called NewDatabase on the currently connected server. 

Listing 5. Server-Side AMO
public void CreateNewDB()
   Server s = new Server();
   s.Connect("Data Source=" + Context.CurrentServerID + ";Initial Catalog=" +
   Database dbNew = new Database("NewDatabase");


Notice the use of the Context object’s CurrentServerId and CurrentDatabaseName properties to build the context-sensitive connection string used in the AMO Server object’s Connect method. It is also permissible to reference a context connection using a connection string of “*”, as is done in the commented-out line of code.

Void functions are executed using the special MDX CALL command rather than the SELECT command. So, to call the previous stored procedure, you would use the following MDX code:

CALL Chapter19AdomdServer.Chapter19AdomdServer.StoredProcedures.CreateNewDB();


Try executing the previous command from Management Studio (by simply opening the file CLR-DDL.mdx from the CLR Demos project and executing it), right-click on your server’s Databases node in the Object Explorer window, choose Refresh from the shortcut menu, and then expand the node (if necessary). You should see a NewDatabase node appear; you might want to delete it after completing this test.

Result Set–Returning Functions

Although you should not think of Analysis Services managed stored procedures as being designed to return result sets, it is in fact possible to return tabular results sets from them, in the form of AdomdServer.AdomdDataReader or System.Data.DataTable objects. For example, the code in Listing 6 (also found in StoredProcedures.cs) implements a CLR stored procedure that returns a simple two-column, two-row result set:

Listing 6. Returning a DataTable
public System.Data.DataTable GetDataTable()
   System.Data.DataTable dt = new System.Data.DataTable();
   dt.Columns.Add("ID", typeof(int));
   dt.Columns.Add("Name", typeof(string));

   object[] row = new object[2];

   row[0] = 1;
   row[1] = "Andrew";

   row[0] = 2;
   row[1] = "Steve";

   return dt;

When called with the following MDX command (found in GetDataTable.mdx), the code in Listing 19-6 returns the tabular result set shown following the command.

CALL Chapter19AdomdServer.Chapter19AdomdServer.StoredProcedures.GetDataTable();



This example is trivial, but the ability to return result sets can indeed be helpful. You can, for example, use actual ADO.NET code to return data from an SQL Server relational database based on input values from a passed MDX set.

Mixing Client and Server to Return Flattened Cell Sets

Unlike with SQL CLR programming, you will typically not use the ADO MD.NET client library (AdomdClient) in your server-side code; instead, you’ll use the AdomdServer library in server-side programming, as it replicates most AdomdClient objects. However, if you want your stored procedure to return data from an MDX query (for example, as an AdomdDataReader), you must use client-side ADO MD.NET (AdomdClient) objects in your stored procedure. This is illustrated in Listing 7 (also found in StoredProcedures.cs).

Listing 7. Returning a DataReader
public Microsoft.AnalysisServices.AdomdClient.AdomdDataReader
   Microsoft.AnalysisServices.AdomdClient.AdomdConnection conContext = new
      Microsoft.AnalysisServices.AdomdClient.AdomdConnection("data source=" +
      Context.CurrentServerID + ";initial catalog=" +
   Microsoft.AnalysisServices.AdomdClient.AdomdCommand comContext = new
      Shippers.[Shipper Name].MEMBERS ON COLUMNS, Time.Year.MEMBERS ON ROWS
      FROM Sales WHERE Measures.[Total Sales]", conContext);
      return comContext.ExecuteReader();
      return null;


When this code is called with the following MDX query (found in GetDataClient.mdx), it yields the tabular results shown following the query.

CALL Chapter19AdomdServer.Chapter19AdomdServer.StoredProcedures.GetDataClient()


[Time].[Year].[Year].[MEMBER_CAPTION][Shippers].[ShipperName].[All Shippers][Shippers].[ShipperName].&[1][Shippers].[ShipperName].&[2][Shippers].[ShipperName].&[3][Shippers].

The stored procedure shown will execute properly only if the assembly containing it is assigned Unrestricted permissions. This is because the AdomdClient assembly is not callable from partially trusted callers, and only assemblies with the Unrestricted permission set have full trust. You should therefore use this technique only as a last resort, when MDX queries cannot be executed from the calling application (for whatever reason). The scenarios for creating (flattened) cell set–returning stored procedures are thus limited, but the possibility is nonetheless intriguing.

Analysis Services CLR features are at least as compelling as, if not more so than, the SQL CLR functionality offered in SQL Server relational databases. They extend the capabilities of MDX, add easy-to-use DDL commands, provide end-to-end debugging, and serve as a unifying environment for ADO MD.NET, AMO, ADO.NET, and mainstream .NET Framework programming.

  •  Microsoft SQL Server 2008 R2 : Using FILESTREAM Storage (part 2) - Using FILESTREAM Storage for Data Columns
  •  Microsoft SQL Server 2008 R2 : Using FILESTREAM Storage (part 1) - Enabling FILESTREAM Storage
  •  SQL Server 2005 : Using Excel (part 2) - Using PivotTables and Charts in Applications and Web Pages
  •  SQL Server 2005 : Using Excel (part 1) - Working Within Excel
  •  Microsoft SQL Server 2008 R2 : Hierarchyid Data Type (part 2) - Modifying the Hierarchy
  •  Microsoft SQL Server 2008 R2 : Hierarchyid Data Type (part 1) - Creating a Hierarchy, Populating the Hierarchy, Querying the Hierarchy
  •  Using SQL Server 2005 Integration Services : Extensibility (part 4) - Custom Connection Managers
  •  Using SQL Server 2005 Integration Services : Extensibility (part 3) - Script Components
  •  Using SQL Server 2005 Integration Services : Extensibility (part 2) - Custom Components
  •  Using SQL Server 2005 Integration Services : Extensibility (part 1) - Script Tasks
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us