User-defined functions and stored procedures

Use ADOMD.NET server objects to create user-defined functions (UDFs) and stored procedures that interact with SQL Server Analysis Services metadata and data. Call these in-process methods through Multidimensional Expressions (MDX) or Data Mining Extensions (DMX) statements to provide added functionality without the latencies associated with network communications.

UDF examples

A UDF is a method that you call in an MDX or DMX statement. It accepts any number of parameters and returns any data type.

A UDF created by using MDX is similar to one created for DMX. The main difference is that certain properties of the Microsoft.AnalysisServices.AdomdServer.Context object, such as the Microsoft.AnalysisServices.AdomdServer.Context.CurrentCube and Microsoft.AnalysisServices.AdomdServer.Context.CurrentMiningModel properties, are available only for one scripting language or the other.

The following examples show how to use a UDF to return a node description, filter tuples, and apply a filter to a tuple.

Returning a node description

The following example creates a UDF that returns the node description for a specified node. The UDF uses the current context in which it runs, and uses a DMX FROM clause to retrieve the node from the current mining model.

public string GetNodeDescription(string nodeUniqueName)  
{  
   return Context.CurrentMiningModel.GetNodeFromUniqueName(nodeUniqueName).Description;  
}  

After you deploy the UDF, use the following DMX expression to retrieve the most likely prediction node. The description includes the conditions that make up the prediction node.

select Cluster(), SampleAssembly.GetNodeDescription( PredictNodeId(Cluster()) ) FROM [Customer Clusters]  

Returning tuples

The following example takes a set and a return count. It randomly selects tuples from the set and returns the resulting subset:

public Set RandomSample(Set set, int returnCount)  
{  
   //Return the original set if there are fewer tuples  
   //in the set than the number requested.  
   if (set.Tuples.Count <= returnCount)  
      return set;  
  
   System.Random r = new System.Random();  
   SetBuilder returnSet = new SetBuilder();  
  
   //Retrieve random tuples until the return set is filled.  
   int i = set.Tuples.Count;  
   foreach (Tuple t in set.Tuples)  
   {  
      if (r.Next(i) < returnCount)  
      {  
         returnCount--;  
         returnSet.Add(t);  
      }  
      i--;  
      //Stop the loop if we have enough tuples.  
      if (returnCount == 0)  
         break;  
   }  
   return returnSet.ToSet();  
}  

The following MDX query calls the UDF and retrieves five random states or provinces from the Adventure Works database.

SELECT SampleAssembly.RandomSample([Geography].[State-Province].Members, 5) on ROWS,   
[Date].[Calendar].[Calendar Year] on COLUMNS  
FROM [Adventure Works]  
WHERE [Measures].[Reseller Freight Cost]  

Applying a filter to a tuple

The following example applies an Expression filter to each tuple in a set and returns the matching tuples.

public static Set FilterSet(Set set, String filterExpression)
{
    Expression expr = new Expression(filterExpression);

    SetBuilder resultSetBuilder = new SetBuilder();

    foreach (Tuple tuple in set)
    {
        if ((bool)(expr.Calculate(tuple)))
        {
            resultSetBuilder.Add(tuple);
        }
    }

    return resultSetBuilder.ToSet();
}

The following MDX query calls the UDF to return cities with names that begin with A.

Select Measures.Members on Rows,  
SampleAssembly.FilterSet([Customer].[Customer Geography].[City], "[Customer].[Customer Geography].[City].CurrentMember.Name < 'B'") on Columns  
From [Adventure Works]  

Stored procedure example

In the following example, an MDX-based stored procedure uses AMO to create partitions, if needed, for Internet Sales.

public static void CreateInternetSalesMeasureGroupPartitions()
{
    //Check the current state of the data warehouse and 
    //create partitions with AMO if necessary
    #region Retrieve order date of last sales transaction

    // Open a connection to the data warehouse
    // TODO: Change the first string parameter to reflect the right server\instance.
    SqlConnection conn = new SqlConnection(string.Format("data source={0};initial catalog={1};Integrated Security=SSPI", "server\\instance", Context.CurrentDatabaseName));
    conn.Open();

    // Create a command
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;

    // Get the order date key of the last internet sale
    int lastInternetSaleDateKey = 0;
    cmd.CommandText = "select max(OrderDateKey) from FactInternetSales";
    lastInternetSaleDateKey = (int)cmd.ExecuteScalar();

    // Get the order date key of the last reseller sale
    int lastResellerSaleDateKey = 0;
    cmd.CommandText = "select max(OrderDateKey) from FactResellerSales";
    lastResellerSaleDateKey = (int)cmd.ExecuteScalar();
    #endregion

    #region Create partitions

    // Connect to the calling session
    Server svr = new Server();
    svr.Connect("*");

    // Get the Adventure Works cube
    Database db = svr.Databases.GetByName(Context.CurrentDatabaseName);
    Cube cube = db.Cubes[0];

    MeasureGroup mg;
    int maxOrderDateKey;

    mg = cube.MeasureGroups.GetByName("Internet Sales");
    maxOrderDateKey = 0;
    foreach (Partition part in mg.Partitions)
    {
        maxOrderDateKey = Math.Max(maxOrderDateKey, Convert.ToInt32(
            part.Annotations.Find("LastOrderDateKey").Value.Value,
            System.Globalization.CultureInfo.InvariantCulture));
    }

    if (maxOrderDateKey < lastInternetSaleDateKey)
    {
        Partition part = mg.Partitions.Add("Internet_Sales_"
            + lastInternetSaleDateKey);
        part.StorageMode = StorageMode.Molap;
        part.Source = new QueryBinding(db.DataSources[0].ID,
            "SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey > '"
            + maxOrderDateKey + "' and OrderDateKey <= '" + lastInternetSaleDateKey + "'");
        part.Annotations.Add("LastOrderDateKey", Convert.ToString(lastInternetSaleDateKey,
            System.Globalization.CultureInfo.InvariantCulture));
        part.Update();
        part.Process();
    }

    svr.Disconnect();

    #endregion
}