Creating and using MDX Stored Procedures - ADOMD.NET Server Programming

In this session we'll show you how to extend the functionality of Analysis Services and MDX by developing .NET stored procedures (previously called user-defined-functions). We will be using the ADOMD.NET server components to develop our custom Multidimensional Expressions (MDX) functions and stored procedures, which will run on an instance of SQL Server Analysis Services (SSAS).

What are MDX Stored Procedures?

MDX Stored Procedure is a term used in Analysis Services for referencing custom CLR Assemblies. The CLR Assembly will have functions developed in .NET, using the ADOMD.NET server components and registered through SQL Server Management Studio (SSMS). For example, you can call a method from within your MDX expression, like so:

WITH MEMBER Measures.BreakEvenPoint AS

SP_DEMO.Infotoad.MDX.SP.BreakEvenAnalysis(

SUM([Account].[Accounts].[Total Cost of Sales],[Measures].[Amount]),

SUM([Account].[Accounts].[Gross Margin],[Measures].[Amount]))

SELECT Measures.BreakEvenPoint ON 0

FROM [Adventure Works]

What we'll cover in this session:

  • Develop Custom CLR Assembly
  • Register CLR Assembly
  • Script an MDX Expression and reference a method (Stored Procedure)
  • Debug the CLR Assembly

This article assumes you have the following:

  • Installed Microsoft Visual Studio 2010.
  • Experience developing with C#.
  • Installed SQL Server 2012 or 2008 R2.
  • Downloaded and implemented sample AdventureWorksDW2008R2 Data File and AdventureWorksDW2008R2 cube for SSAS 2008 R2.
  • Experience with SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS). Note: For this session we'll be using SSMS.
  • Experience with attaching to an exisitng database using SQL Server Management Studio.
  • Experience with T-SQL and MDX scripting language.

Note: A download of the demo is available at the end of this session. 

Let's get started!

 

Step 1 - Creating our custom CLR Assembly 

1) Open Visual Studio 2010 and create a New Project - Visual C#, Class Library. For this demo, you can name the solution SP_DEMO.

2) Delete the Class1.cs file.

3) Add a new class file. Name it StoredProce.cs

4) Right click on the References folder >> Add Reference... >> Browse tab and add the following assemblies:

1) Microsoft.AnalysisServices - Analysis Management Objects

Location: ...\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.AnalysisServices.DLL

2) MSMGDSRV - ADOMD.Net interfaces

Location: ...\Microsoft Analysis Services\AS OLEDB\10\msmgdsrv.dll

5) Open the SoredProcs.cs file.

6) Replace all content of the file with the code below.

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

using Microsoft.AnalysisServices;

using Microsoft.AnalysisServices.AdomdServer;

namespace Infotoad.MDX

{

    public sealed class SP

    {

        private SP()

        {

        }

        public static float AcidTestRatio(float assetAmt, float inventoryAmt, float currentLiabilitiesAmt)

        {

            return ((assetAmt - inventoryAmt) / currentLiabilitiesAmt);

        }

        public static float BreakEvenAnalysis(float fixedCosts, float grossMarginPct)

        {

            return (fixedCosts / grossMarginPct);

        }

        public static string ParseMonthName(Set monthName)

        {

            string[] month = monthName.Tuples[0].Members[0].Name.Split(' ');

            if (month.Length <= 0)

                return "Blank";

            else

                return month[0];

        }

    }

}

7) From the menu bar, click Build >> Build SP_DEMO. You should see somethng similar.

Note: if you have issues compiling the code, try changing the Target Framework to .NET Framework 3.5 from the Solution Properties windows. You may also want to create a Strong Name Key file by clicking on the Signing tab and checking the Sign the assembly checkbox. Just select <New...> and follow the steps.

 

8) Rebuild and Save your work.

 

Step 2 - Registering the CLR Assembly in SSMS

1) Open SQL Server Management Studio and connect to your Analysis Services instance.

2) Expand the Adventure Works DW 2008R2 database.

3) Right click on the Assemblies folder and select New Assembly...

   

4) The Register Database Assembly window renders. Configure the following:

  • Type = .NET Assembly
  • File Name = your path to the SP_DEMO.dll. Note: This will commonly be located in the /bin/debug folder of your Visual Studio 2010 project you created in Step 1.
  • Assembly Name = SP_DEMO
  • Check the Include debug information. Note: checking this option allows for us to debug the code later.
  • Permissions = Unrestricted
  • Impersonation = Use the service account

Your configuration should look similar to the image bleow.

5) Click OK to close the Register Database Assembly window. You will notice the SP_DEMO assembly is added under the Assemblies folder.


Step 3 - Using your MDX Stored Procedure

1) Right click on the Adventure Works DW 2008R2 database >> New Query >> MDX

2) In Query pane, copy and past the following code:

WITH MEMBER Measures.BreakEvenPoint AS

SP_DEMO.Infotoad.MDX.SP.BreakEvenAnalysis(

SUM([Account].[Accounts].[Total Cost of Sales],[Measures].[Amount]),

SUM([Account].[Accounts].[Gross Margin],[Measures].[Amount]))

SELECT Measures.BreakEvenPoint ON 0

FROM [Adventure Works]

Note: In the code above, notice we are calling the BreakEvenAnalysis method from our assembly.

3) Execute the code. You should recieve a resut of 0.4558756.

4) Leave your SQL Server Management Studio session open, as we will debug the code in our next step.

 

Step 4 - Debugging the MDX Stored Procedure

1) Open your SP_DEMO project in Visual Studio 2010.

2) From the menu bar, click Debug >> Attach to process...

3) The Attach Process window renders. Check the Show processes from all users checkbox.

4) Scroll down the Available Processes and select the msmdsrv.exe executable.

5) Click Attach to close the window.

6) Find the BreakEvenAnalysis method and place a breakpoint on the line holding the following code:

7) Go back your SSMS session and execute the MDX query again. Now go back to your Visual Studio 2010 project, you will notice the breakpoint in the code gets a hit and allows you to break through the code.

 

 

You have completed creating a custom MDX Stored Procedure using ADOMD.NET server components and registering the assembly to an Analysis Services database.

 

Download:  MDX Stored Procedure Demo

Cheers!

 

Comments are closed