用户定义函数和存储过程

项目
10/31/2023

使用 ADOMD.NET 对象，可以在 UDF (或存储) 创建用户定义函数，Microsoft SQL Server Analysis Services与服务器中的元数据和数据进行交互。这些进程内方法是通过多维表达式 (MDX) 或数据挖掘扩展插件 (DMX) 语句调用的，可以提供附加功能而不会有网络通信的延迟。

UDF 示例

UDF 是一种可在 MDX 或 DMX 语句上下文中调用的方法，可具有任意数目的参数，并可返回任意类型的数据。

使用 MDX 创建的 UDF 与用 DMX 创建的 UDF 类似。主要区别在于 Microsoft.AnalysisServices.AdomdServer.Context 对象的某些属性（如 Microsoft.AnalysisServices.AdomdServer.Context.CurrentCube 和 Microsoft.AnalysisServices.AdomdServer.Context.CurrentMiningModel 属性）仅适用于一种脚本语言或其他语言。

下面的示例演示如何使用 UDF 返回节点说明、筛选元组并对元组应用筛选器。

返回节点说明

下面的示例创建一个返回指定节点的节点说明的 UDF。该 UDF 使用它正在其中运行的当前上下文，并使用 DMX FROM 子句从当前挖掘模型检索节点。

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

部署后，下面的 DMX 表达式（检索最有可能的预测节点）就可以调用上面的 UDF 示例了。节点的说明包含描述组成预测节点的条件的信息。

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

返回元组

下面的示例使用一个集和一个返回计数，它从该集中随机检索元组，并返回一个最终子集：

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();  
}

下面的 MDX 示例中调用了上面的示例。在此 MDX 示例中，从 Adventure Works 数据库中检索五个随机状态或省/自治区。

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

对元组应用筛选器

下面的示例中定义了一个 UDF，该 UDF 使用一个集，并使用 Expression 对象对集中的每个元组应用筛选器。所有符合筛选条件的元组都将被添加到返回的集中。

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();
}

下面的 MDX 示例调用了上面的示例，该 MDX 示例从集中筛选名称以“A”开头的市县。

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

存储过程示例

在下面的示例中，基于 MDX 的存储过程使用 AMO 来为 Internet 销售创建分区（如果需要）。

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
}

通过