lunes, 23 de abril de 2012

Cuartiles en SSAS

Problema:
 Obtener los cuartiles en un cubo Microsoft Analysys Services.
Solución: La función para calcular cuartiles no existe en MDX. Existen varias formas de calcularlos como puede leerse en este interesante articulo de ElectroVoid, pero la optima es usar una procedimiento almacenado SSAS.
Hay que desarrollarlo en un lenguaje .NET, en el articulo mencionado utilizan C#, yo aportó aquí el código VB.NET.

Imports Microsoft.AnalysisServices
Namespace Percentiles
    Public Class Percentil
         _
        Public Shared Function Cuartil(ByVal conjunto As AdomdServer.Set, _
                                            ByVal expr As AdomdServer.Expression, _
                                            ByVal Qtile As Integer) As AdomdServer.MDXValue

            If Qtile < 0 Or Qtile > 4 Then Throw New ArgumentOutOfRangeException(Qtile)
            Dim valores = New List(Of Double)
            For Each tupla In conjunto.Tuples
                Dim mdxVal = expr.Calculate(tupla)

                If mdxVal.ToChar <> ChrW(0) Then valores.Add(mdxVal.ToDouble)
            Next

            If valores.Count > 0 Then
                valores.Sort()
                Dim cuenta = valores.Count
                If (Qtile = 4) OrElse (cuenta = 1) Then Return (AdomdServer.MDXValue.FromDouble(valores.Item(cuenta - 1)))
                Dim porcentaje As Double = CDbl(Qtile) / 4.0
                Dim qidx As Double = porcentaje * CDbl(cuenta - 1)
                Dim qfloor = Math.Floor(qidx)
                Dim qfrac = qidx - qfloor
                Return AdomdServer.MDXValue.FromDouble( _
                                                        valores.Item(CInt(qfloor)) _
                                                        + (qfrac * (valores.Item(CInt(qfloor + 1)) _
                                                                    - valores.Item(CInt(qfloor)) _
                                                          )) _
                                                        )

            Else
                Return Nothing
            End If

        End Function
         _
        Public Shared Function CuartilOrdenado(ByVal conjunto As AdomdServer.Set, _
                                            ByVal expr As AdomdServer.Expression, _
                                            ByVal Qtile As Integer) As AdomdServer.MDXValue
            'En esta versión la llamada debe realizarse con el conjunto ordenado y sin valores vacios

            If Qtile < 0 Or Qtile > 4 Then Throw New ArgumentOutOfRangeException(Qtile)
            Dim valores = New List(Of Double)
            For Each tupla In conjunto.Tuples
                Dim mdxVal = expr.Calculate(tupla)
                valores.Add(mdxVal.ToDouble)
            Next

            If valores.Count > 0 Then
                Dim cuenta = valores.Count
                If (Qtile = 4) OrElse (cuenta = 1) Then Return (AdomdServer.MDXValue.FromDouble(valores.Item(cuenta - 1)))
                Dim porcentaje As Double = CDbl(Qtile) / 4.0
                Dim qidx As Double = porcentaje * CDbl(cuenta - 1)
                Dim qfloor = Math.Floor(qidx)
                Dim qfrac = qidx - qfloor
                Return AdomdServer.MDXValue.FromDouble( _
                                                        valores.Item(CInt(qfloor)) _
                                                        + (qfrac * (valores.Item(CInt(qfloor + 1)) _
                                                                    - valores.Item(CInt(qfloor)) _
                                                          )) _
                                                        )

            Else
                Return Nothing
            End If

        End Function
    End Class
End Namespace
Observad que he creado dos metodos, en el primero incluyo el ordenado y la eliminación de elementos vacios, en el segundo espero recibir el conjunto con estas tareas realizadas.
percentiles.cuartilOrdenado(
            ORDER(
                  NONEMPTY 
                     (descendants([DimEmpresas].[Facturacion].currentmember, [DimEmpresas].[Facturacion].[Empresa])
                      ,[Measures].[% A-1 VN Euros]
                      )
                  ,[Measures].[% A-1 VN Euros]
                  ,BASC
                  )
            ,[Measures].[% A-1 VN Euros]
            ,3)
Para desarrollar la aplicación instalar SSAS en la máquina de desarrollo y añadi una referencia a msmgdsrv.dll en C:\Archivos de programa\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin
Luego añadir el ensamblado al servidor SSAS, a la base de datos o al proyecto (ver msdn)

Si alguien necesita la ddl que me lo diga.<(p>

jueves, 12 de abril de 2012

Crear una tabla desde una lista separada por comas

La siguiente función devuelve una tabla desde una lista de números separados por comas:
FUNCTION [dbo].[fnSplitCSVxml] 
               (@NumberList VARCHAR(4096)) 
RETURNS @SplitList TABLE( ListMember INT) 
AS 
  BEGIN 
    DECLARE  @xml XML 
    SET @NumberList = LTRIM(RTRIM(@NumberList)) 
    IF LEN(@NumberList) = 0 
      RETURN 
    SET @xml = '' + REPLACE(@NumberList,',','') + '' 
    INSERT INTO @SplitList 
    SELECT x.i.value('.','VARCHAR(MAX)') AS Member 
    FROM   @xml.nodes('//n') x(i) 
    RETURN 
  END 

Tiene diversas aplicaciones del tipo:
SELECT A from B Where B.c in dbo.fnSplitList(@cadena)
Pero es especialmente interesante para informes de Report Server que usen parametros con multiples valores.