Funções e procedimentos armazenados definidos pelo usuário

Artigo
08/01/2012

Com os objetos de servidor do ADOMD.NET, você pode criar uma UDF (função definida pelo usuário) ou procedimentos armazenados para o Microsoft SQL Server Analysis Services que interajam com metadados ou dados do servidor. Esses métodos em processo são chamados por meio de instruções MDX (Multidimensional Expressions), DMX (Data Mining Extensions) para fornecerem funcionalidade agregada sem as latências associadas às comunicações de rede.

Exemplos de UDF

Um UDF é um método que pode ser chamado no contexto de uma instrução MDX ou DMX, pode conter qualquer número de parâmetros e pode retornar qualquer tipo de dados.

Um UDF criado com MDX é semelhante a um criado para DMX. A principal diferença é que certas propriedades do objeto Context, como as propriedades CurrentCube e CurrentMiningModel, só estão disponíveis para uma linguagem de scripts ou para a outra.

Os exemplos a seguir mostram como usar um UDF para retornar uma descrição de nó, filtrar tuplas e aplicar um filtro a uma tupla.

Retornando uma descrição de nó

O exemplo a seguir cria um UDF que retorna a descrição de um nó especificado. O UDF usa o contexto atual em que está sendo executado e usa uma cláusula FROM do DMX para recuperar o nó do modelo de mineração atual.

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

Uma vez implantado, o exemplo de UDF anterior pode ser chamado pela expressão DMX a seguir, que recupera o nó de previsão mais provável. A descrição contém informações que descrevem as condições que compõem o nó de previsão.

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

Retornando tuplas

O exemplo a seguir obtém um conjunto e uma contagem de retorno e recupera tuplas do conjunto de forma aleatória, retornando um subconjunto final:

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

O exemplo anterior é chamado no exemplo de MDX a seguir. Neste exemplo de MDX, são recuperados cinco estados ou províncias aleatórios do banco de dados 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]

Aplicando um filtro a uma tupla

No exemplo a seguir, um UDF é definido para obter um conjunto, e aplicar um filtro a cada tupla no conjunto, usando o objeto Expression. Qualquer tupla compatível com o filtro será adicionada a um conjunto retornado.

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

O exemplo anterior é chamado no exemplo de MDX a seguir, que filtra o conjunto para cidades com nomes começando com 'A'.

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

Exemplo de procedimento armazenado

No exemplo a seguir, um procedimento armazenado baseado em MDX usa AMO para criar partições, se necessário, para Vendas na 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
}

Compartilhar via