User Defined Functions and Stored Procedures

Neu: 05. Dezember 2005

With ADOMD.NET server objects, you can create user defined function (UDF) or stored procedures for Microsoft SQL Server 2005 Analysis Services (SSAS) that interact with metadata and data from the server. These in-process methods are called 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 can be called in the context of an MDX or DMX statement, can take any number of parameters, and can return any type of data.

A UDF created using MDX is similar to one created for DMX. The main difference is that certain properties of the Context object, such as the CurrentCube and 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 is being run, 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;
}

Once deployed, the previous UDF example can be called by the following DMX expression, which retrieves the most-likely prediction node. The description contains information that describes 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, and randomly retrieves tuples from the set, returning a final 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 previous example is called in the following MDX example. In this MDX example, five random states or provinces are retrieved 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

In the following example, a UDF is defined that takes a set, and applies a filter to each tuple in the set, using the Expression object. Any tuples that conform to the filter will be added to a set that is returned.

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 previous example is called in the following MDX example, which filters the set to cities with names beginning 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
}