Using Stored Procedures (MDX)
APPLIES TO:SQL Server Analysis ServicesAzure Analysis Services
You can extend the functionality of Analysis Services and Multidimensional Expressions (MDX) by writing .NET stored procedures or user-defined functions. For more information, see ADOMD.NET Server Programming
When you reference or call a stored procedure, you specify the function name followed by parentheses. Within the parentheses, you can specify expressions called arguments that provide the data to be passed into the parameters. When you call a function, you must supply argument values for all of the parameters, and you must specify the argument values in the same sequence in which the parameters are defined in the user-defined function.
The following example query assumes that you have an assembly named SampleAssembly registered on your Analysis Services Server:
SELECT SampleAssembly.RandomSample([Geography].[State-Province].Members, 5) on ROWS, [Date].[Calendar].[Calendar Year] on COLUMNS FROM [Adventure Works] WHERE [Measures].[Reseller Freight Cost]
Stored procedure is the terminology used in Microsoft SQL Server Analysis Services for these types of functions. Earlier versions of Analysis Services called these types of functions as user-defined functions.
Types of stored procedures
Analysis Services supports both COM and CLR assemblies. CLR assemblies are recommended because of the enhanced security available to CLR assemblies. If Microsoft Office Excel is installed on the server, Excel functions are also available.
Microsoft Visual Basic for Applications (VBA) COM Assemblies are registered automatically.