Built-in COM UDFs | Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)

The MDX language provides certain functions (for example, SettoStr, TupletoStr, StrtoTuple, StrtoSet, and StrtoMember) that are helpful in translating MDX objects to external data structures such as string or arrays or from external structures to MDX objects. The MDX language does not support certain common utility functions like performing operations on strings, such as trimming or getting the first substring match, or date operation functions. Such functions are natively supported in SQL, and they might be quite commonly used during MDX queries. Because Visual Basic for Applications (VBA) and Excel contain a rich set of such functions, they are readily available as a COM DLL. Analysis Services 2005 takes advantage of this and exposes certain functions in Excel and VBA out of the box. Analysis Services 2000 also supported certain VBA functions out of the box. However, on 64-bit platforms, VBA functions were not supported because VBA dll is 32 bit. Hence in Analysis Services 2005 the supported VBA functions are implemented using native and managed code instead of delegating the call to the VBA dll. Important functions that can impact performance are implemented natively, while the remaining supported VBA functions are implemented as a built-in .NET assembly. If you want to use the COM VBA functions and do not want to use the VBA functions that have been implemented in .NET language then Analysis Services 2005 provides a configuration setting to switch back to the native VBA. By changing the server property VBANet to 0, you force Analysis Services to load the default VBA dll. However, you would have to delete the entire data folder of Analysis Services and then restart the server.

Calling a user-defined function in MDX is similar to calling a function in most programming languages. You make the call with the function name followed by an opening parenthesis, with each argument in the correct order separated by commas, and finally a closing parenthesis. For example, if you want to get today's date, you can use the VBA function Now (). The following MDX query will retrieve today's date:

     WITH MEMBER Measures.[Today's Date] AS 'Now ()'     SELECT Measures.[Today's Date] on 0     From [Adventure Works DW]

In the above example the value returned by the VBA function Now () is stored as a calculated measure and then retrieved using the MDX query. The functions provided by VBA and Excel will likely help you meet your business application requirements. However, each business need is unique and your business might require special computing that cannot be solved using the MDX, VBA, or Excel functions. You might have to base all your calculations on an external data source that is dynamically changing. In such circumstances you can write a custom COM DLL and add this to Analysis Services 2005.

Creating Your COM UDFs

Creating a COM user-defined function is quite simple. All you have to do is to create an ActiveX DLL using one of the COM languages such as Microsoft Visual Basic or Microsoft Visual C++. Analysis Services 2000 provided support for extending MDX by means of COM UDFs. The arguments that you can pass to COM UDF functions are numbers, strings, and arrays. You cannot use any of the MDX objects within your functions nor can you pass the MDX objects as parameters to your COM UDFs. Your COM UDF can return either strings or numbers that can be stored as calculated members or parameters to other functions in your MDX query. When you create your COM UDF function you would typically declare the types of arguments in the function declaration, such as COMUDFFunction (integer argument1, double argument 2, string argument3). The value passed from Analysis Services for that argument will be coerced into that specific type in the function signature when the function is called.

The Adventure Works DW cube you created in the previous chapters contains the calculated measure profit. Assume you want to see a cumulative sum of the profits based on the ship date along with the net profit for each year. In order to perform a cumulative sum, you can write a function that takes three arguments of the dimension, the measure value, and the location up to which the cumulative sum has to be performed. The dimension members along with their values need to be passed to your UDF. Analysis Services 2005 provides a function called SetToArray, which translates an MDX Set into an Array object. Using this function you can pass the measure values and the dimension members as arrays to the function.

Following is Visual Basic code to perform a cumulative sum. The function takes two arrays and a string. The first argument is an array of numbers, the second contains the dimension members, and the third argument is the member name up to which the cumulative total needs to be applied. The function returns the cumulative sum as the return value.

     Public Function PartialSum (ByRef ArgVals () As Variant, ByRef ArgNames () As Variant,     ByVal StopAt As String) As Double     Dim Start As Integer     Dim Finish As Integer      Dim i As Integer     Dim s As String     Dim SubTotal As Double     Start = 0 ' for safety     Finish = UBound (ArgVals)     PartialSum = 0     'initialize working sum to zero     SubTotal = 0     For i = Start To Finish         'add value for this tuple         SubTotal = SubTotal + ArgVals (i)         s = ArgNames (i)         'leave if we have encountered the stopping point         If s = StopAt Then             PartialSum = SubTotal             Return         End If     Next i     End Function

You can create a new Visual Basic 6.0 project of ActiveX DLL type and enter the preceding code. Once you have entered the code you can create the DDL with the option FileMake DLL. When you are creating the DLL, make sure you select the option to create debug symbols so that you can debug your COM UDF if needed. The preceding Visual Basic code and project are available under Chapter 10 directory in the COMPartialSum folder on the web site that accompanies this book. Once you have created your COM DLL, you need to add this to your database.

Adding a COM UDF to an Analysis Services Database

You can add a COM UDF to your Analysis Services database using SQL Server Management Studio, through scripting or programmatically using the object model AMO that is used for managing your Analysis Server. We recommend that you add assemblies using the SQL Server Management Studio because it is easy to do.

You can add a COM UDF to Analysis Services at the scope of the Analysis Services instance or at the scope of an existing database. If your UDF is added at the server scope, it can be shared across multiple databases similar to the Excel and VBA DLLs that default to server-level scope. The following steps show you how to add the COM UDF you created.

Deploy the AnalysisServices2005Tutorial project provided under the Chapter 10 directory available on the accompanying web site for this book to your Analysis Services instance. (If you already have the AnalysisServices2005Tutorial project deployed on your server you can skip this step.)
Connect to the Analysis Services instance using SQL Server Management Studio.
Expand the object AnalysisServices2005Tutorial database.
Right-click the node assemblies and select New Assembly.
You will now see the register assembly dialog shown in Figure 10-1. Change the type of assembly from .NET to COM. Under the file name, browse through the directory where the COM assembly is located and select the assembly file PartialSum.dll. The PartialSum.dll is also available under the Chapter 10 directory on the accompanying web site for this book.

image from book
Figure 10-1

All COM assemblies added to Analysis Services have unrestricted permissions, which mean they can access files or network services in addition to any computation. You can have a finer grain of permissions and you can allow for .NET assemblies, which you learn more about in the next section. Analysis Services runs under a specific account credential. However, the assembly being added to Analysis Services might need a specific set of credentials because it needs to access specific external resources. Analysis Services allows you to specify the credential under which the COM UDF needs to be executed. The various options in the dialog are self-explanatory, but you will learn each option in detail when you are adding .NET assemblies to the Analysis Services instance. Select the default option and click OK. You can now use the assembly in your MDX Query.

Note

Analysis Services 2000 provided syntax to add assemblies called "USE LIBRRARY <library name>." The majority of computations were done on the client side in Analysis Services 2000 and hence this syntax helped in loading libraries on the client side. Analysis Services 2005 performs all the operations on the server side. Therefore this syntax has been deprecated. You do not have to specify this syntax in Analysis Services 2005 since the DLL gets loaded when a query uses a function in the DLL. For backward compatibility reasons the USE LIBRARY syntax is accepted by Analysis Services 2005, but it is just a NO OP (no operations are executed when the statement is encountered).

Accessing COM UDFs in MDX Queries

In order to retrieve the cumulative profit along with profit for various years, you need to use the MDX function SettoArray to translate a set of members in Analysis Services to an array that can be passed to a UDF. The following query calculates the cumulative profit across various years of ship date:

     WITH     Member [Measures].[Year] AS '[Ship Date].[CalendarYear - CalendarSemesterCalendarQuarter - EnglishMonthName].Currentmember.UniqueName'     member [Measures].[Cum Profit] AS '     PartialSum (     SettoArray ([Ship Date].[CalendarYear - CalendarSemester - CalendarQuarter -     EnglishMonthName].[CalendarYear],[Measures].[Profit]),     SetToArray ( [Ship Date].[CalendarYear - CalendarSemester - CalendarQuarter -     EnglishMonthName].[CalendarYear],[Measures].[Year] ),     [Ship Date].[CalendarYear - CalendarSemester - CalendarQuarter -     EnglishMonthName].CurrentMember.Uniquename)'     SELECT { [Measures].[Profit], [Measures].[Cum Profit]} on columns,     [Ship Date].[CalendarYear - CalendarSemester - CalendarQuarter -     EnglishMonthName].[CalendarYear] ON ROWS     from [Adventure Works DW]

You will see the results shown in the table below when you execute this query against the Adventure Works DW database.

	Profit	Cum Profit
2001	1,576,032.17	1576032.1682
2002	2,705,927.15	4281959.3152
2003	−168,557.73	4113401.5814
2004	−13,288.53	4100112.0547

Analysis Services calls the UDF for each member of the ship date to calculate the cumulative sum up to that year, thereby resulting in calculating the cumulative profit for each year. In this query the UDF is called four times because there are four members returned in the result.

Disambiguating Between Functions

You can add multiple COM UDFs that contain the same function names to a database or Analysis Services instance. If you have more than one function with the same name within Analysis Services, any query using the function name will result in an error. This is due to the inability to resolve to the correct function name. In such a circumstance you can explicitly specify the fully qualified name of the function by pre-pending it with the classID of the library. The classID of a function looks like this: AssemblyName.ClassName. If you have created your UDF using a Visual Basic project that contains a package name called COMExample and a class name COMExampleClass, you need to access the function as COMExample!_COMExampleClass.Function.