Using Stored Procedures (MDX)
Updated: March 2, 2016
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]
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.