I’ve found myself working with UDF’s more and more lately and thought it would make a good blog topic. So, if you are interested in creating a UDF to use in your MDX, read on.
I’m using the Adventure Works database which is the sample database Analysis Services 2005. If you have not yet created the Adventure Works database, you can get the source files and installation instructions here.
First, create a class library in Visual Studio.
Here is a very basic sample of a C# class library I created:
using System;
using System.Collections.Generic;
using System.Text;
namespace AWLib
{
public class Utilities
{
public string GetSomeSet(){
int sec = DateTime.Now.TimeOfDay.Milliseconds;
string set;
if (sec < 200) {
set = "[Date].[Fiscal].Children";
}
else if (sec < 400)
{
set = "[Reseller].[Reseller Type].Children";
}
else if (sec < 600)
{
set = "[Employee].[Employees].Children";
}
else if (sec < 800)
{
set = "[Geography].[Geography].Children";
}
else {
set = "[Sales Channel].[Sales Channel].Children";
}
return set;
}
}
}
Next, after you have successfully compiled your class library, you need to add it to the Adventure Works database. You do this by:
1) Opening SQL Server Management Studio
2) Right click on the ‘Assemblies’ folder in the Adventure Works database, then click on ‘New Assembly…’
Note: You can also add your class library to the server assemblies so the UDF’s will be available in all databases on your Analysis Services instance. Also, you can add your assembly to your ‘Business Intelligence Development Studio’ project and deploy your assembly along with your database.
To test the UDF (from above), open a ‘New Query’ window in SQL Server Management Studio, select the Adventure Works database and execute the following MDX.
SELECT Measures.Members ON 0,
STRTOSET(AWLib.GetSomeSet()) ON 1
FROM [Adventure Works]
The AWLib.GetSomeSet() function will return different sets based on the milliseconds from the timestamp on your server.
UDF’s are very powerful and flexible and help to extend the functionality of MDX. Here are just a few examples of how you can use UDF’s 1) implementing dynamic security in Analysis Services, 2) dynamically building sets and tuples, and 3) creating complex mathematical calculations.