none
Calcular la mediana

    Pregunta

  • Hola,

    Tengo creado un cubo:

    -Tabla de hechos con estos campos:
    NumVenta  CategoriasDW    Cantidad
    1            6459           5
    2            6459           4
    3            6459           9
    4            6460           20
    5            6460           15
    6            6461           8  
    7            6461           16
    8            6461           3
    9            6462           11
    10           6462           6

    -Tabla de dimensiones con estos campos [Dim Categorias]:
    CategoriasDW   Producto Categoria
    6459         A                 1
    6460         A                 2
    6461         B                 1
    6462         B                 2

    Estoy intentando crear un campo calculado que me devuelva la mediana (NO LA MEDIA) del campo cantidad.

    El calculo hecho manualmente sería:

      -Si filtran por producto:
     Producto   Mediana
               A          9 
               B          8

      -Si filtran por categoria:
            Categoria  Mediana
               1          6,5
               2          13

      -Si filtran por producto-categoria:
            Producto   Categoria    Mediana
                A         1           5  
                A         2           17,5
                B         1           8
                B         2           8,5  

    Lo que me interesa es sacar el valor por producto-categoria, por lo tanto me he creado una jerarquia dentro
    de la dimensión con los campos producto-categoria y la instrucción que he puesto en el campo calculado es:

    MEDIAN([DimCategorias].[Producto-Categoria].currentmember.children,[Measures].[Cantidad]),

    pero me encuentro con el siguiente problema:

    El valor que me devuelve es:

          Producto Categoria  Mediana
            A          1        18
            A          2        35
                      Total     26,5
            B          1        27
            B          2        17
                      Total     22

          Lo que esta haciendo es sumar las cantidades de la agrupación y luego calcular la mediana del valor agrupado,
    pero lo que yo necesito es ver el valor como he hecho en el cálculo manual.
    Este es mi problema, no se como hacer para que haga el cálculo de todos los registros y luego me lo enseñe agrupado.  

     Gracias. Belén

    viernes, 25 de marzo de 2011 9:08

Respuestas

  • Tu pregunta es una pregunta bien dificil, y típica en el mundo multidimensional, tu necesitas una operación que se ha de hacer al máximo nivel de granularidad (hecho) y que analisys services te lo hace al nivel que estas mostrando en cada caso, sumariza los valores hasta ese nivel y entonces aplica la fórmula. Hay comandos como generate que permiten simular cosas así, pero sin entrar en complejidades mayores he probado una solución a tu problema. Ojo, cualquier solucion será mala a nivel de rendimiento en este caso porque hay que llegar al máximo nivel de granularidad, es una medida no aditiva  y además para colmo se calculará en tiempo de ejecución y no de procesado.

    Dicho todo esto :) (y perdón por tanto palabreo), la solución que yo he hecho es meter una dimensión adicional que es de máximo nivel de granularidad (la propia tabla de hechos convertida en dimension) y luego he usado esta formula

    with member mediana as 
    MEDIAN(descendants([dimensiones].[Producto-Categoria].currentmember,,leaves),[Measures].[Cantidad])
    member otramediana as 
    (MEDIAN([dimensiones].[Producto-Categoria].currentmember*descendants([Hechos].[Numventa].[All],,leaves),[Measures].[Cantidad]) )
    select 
    descendants([dimensiones].producto,,leaves) on rows,
    {Measures.mediana,measures.cantidad,measures.otramediana} on columns
    from [Sql2k8r2]
    

    No he puesto tus nombres pero vamos, viene a ser lo mismo. el truco es que he puesto el producto cartesiano del currenmember por el descendants de la dimension hechos en su jerarquia de máximagranularidad a nivel de hoja... vamos máximo nivel de granularidad.

    Así ya para productos en lugar de 26.5 y 22 devuelve el 8 y el 9 que esperabas.

    Espero que te sirva

    Saludos

    Miguel Egea

     


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    miércoles, 13 de abril de 2011 16:45
    Moderador

Todas las respuestas

  • Hola.

    Según lo has comentado, no tienes una dimensión producto y una dimensión categoría, sino una única dimensión, con lo que el cálculo no te lo va a hacer por separado. Adicionalmente, ¿qué mdx estás empleando para que no te muestre los resultados que esperas?


    Alberto López Grande
    SQL Server MVP
    Visita mi blog en http://qwalgrande.blogspot.es/

    viernes, 25 de marzo de 2011 21:49
    Moderador
  • Hola, unicamente tengo una tabla y el resultado lo quiero por producto-categoria.

    Si hago a mano el cálculo de la mediana por producto-categoria sería:

     Producto   Categoria    Mediana
                A         1           5  
                A         2           17,5
                B         1           8
                B         2           8,5  

    El problema el que agrupa los datos por producto-categoria sumando la cantidad y luego calcula la mediana, pero de los datos acumulados, no se si me estoy explicando bien, haria algo así (lo explico en dos pasos)

       Paso 1:

              Producto   Categoria    SumaCantidad
                A                1           18  
                A                2           35
                B                1           27
                B                2           17

      Paso 2:

               Calcula la mediana de cada producto, pero cogiendo el campo SumaCantidad

    Para calcular este valor utilizo un campo calculado con este código:    

    MEDIAN([DimCategorias].[Producto-Categoria].currentmember.children,[Measures].[Cantidad]),

      Saludos.

    lunes, 28 de marzo de 2011 6:28
  • Hola.

    ¿Y la sentencia MDX completa que estás usando?


    Alberto López Grande
    SQL Server MVP
    Visita mi blog en http://qwalgrande.blogspot.es/

    lunes, 28 de marzo de 2011 18:31
    Moderador
  • Hola, te paso el código mdx que me genera al crear el campo calculado.

    CREATE

    MEMBER CURRENTCUBE.[MEASURES].[Mediana_Cantidad]

     

    AS MEDIAN([DimCategorias].[Producto-Categoria].currentmember.children,[Measures].[Cantidad]),

     

    FORMAT_STRING

    = "#,#.0",

    VISIBLE

    = 1;

    ¿Te referias a este código?. El cubo lo he creado con el visual studio 2005 y el asistente tiene una pestaña para crear campos calculados, y la función de calcular la mediana ya viene predeterminada, el problema me lo encuentro en las agrupaciones. ??¿¿¿¿¿

    Saludos.

     

    martes, 29 de marzo de 2011 6:22
  • Hola.

    Eso es el cálculo del miembro, pero no la sentencia MDX que ejecutas y que es la que no muestra los resultados que esperas.


    Alberto López Grande
    SQL Server MVP
    Visita mi blog en http://qwalgrande.blogspot.es/

    martes, 29 de marzo de 2011 19:49
    Moderador
  • Hola, creo que ya he dado con la consulta mdx:

     

    SELECT NON EMPTY { [Measures].[Cantidad] } ON COLUMNS, NON EMPTY { ([DimCategorias].[Producto].[Producto].ALLMEMBERS * [Dim Sub Conceptos].[Categoria].[Categoria].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [PruebaDW] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    Saludos.

    miércoles, 30 de marzo de 2011 6:33
  • Hola.

    No se parece en nada a lo que veníamos comentado. No usas el campo calculado ni la dimensión Producto-Categoría. Hay una medida de cantidad y dos dimensiones independientes.

    En cualquier caso, te sugiero ejecutar lo siguiente (que seguramente no funcione porque la infomación aportada es algo confusa):

    SELECT 
     [Measures].[Mediana_Cantidad] ON COLUMNS, 
     {([DimCategorias].[Producto].[Producto], [Dim Sub Conceptos].[Categoria].[Categoria]} ON ROWS
    FROM [PruebaDW] 
    
    Si no es lo que buscas, nos dices.


    Alberto López Grande
    SQL Server MVP
    Visita mi blog en http://qwalgrande.blogspot.es/

    sábado, 02 de abril de 2011 15:09
    Moderador
  • Perdona, no habia escrito bien la consulta, pero creo que igual me has entendido, te paso lo que he probado:

    SELECT
    MEDIAN([DimCategorias].[Producto-Categoria].currentmember.children,[Measures].[Cantidad])
       ON COLUMNS,
     {([DimCategorias].[Producto].[Producto],[DimCategorias].[Categoria].[Categoria] )} ON ROWS
    FROM [PruebaDW]

    Pero esto me da un error, creo que por la función median, el error es el siguiente:

    "La función Axis0 espera una expresión de conjunto de tupla para el argumento. En su lugar se ha utilizado una expresión de cadena o numérica"

    Saludos.

     

    lunes, 04 de abril de 2011 7:57
  • Hola.

    ¿Probaste la que te pasé?


    Alberto López Grande
    SQL Server MVP
    Visita mi blog en http://qwalgrande.blogspot.es/

    lunes, 04 de abril de 2011 12:50
    Moderador
  • Hola, si probe la consulta que me pasaste, pero el campo [Measures].[Mediana_Cantidad] me lo devuelve a NULL, por eso te puse el mensaje de ayer, es lo mismo que me dijiste que probara, pero con la función Median en lugar de poner el campo calculado. Si se te ocurre alguna cosa más tu dime, yo ya no se que más probar.

    Gracias por las molestias que te estas tomando.

    martes, 05 de abril de 2011 6:25
  • Tu pregunta es una pregunta bien dificil, y típica en el mundo multidimensional, tu necesitas una operación que se ha de hacer al máximo nivel de granularidad (hecho) y que analisys services te lo hace al nivel que estas mostrando en cada caso, sumariza los valores hasta ese nivel y entonces aplica la fórmula. Hay comandos como generate que permiten simular cosas así, pero sin entrar en complejidades mayores he probado una solución a tu problema. Ojo, cualquier solucion será mala a nivel de rendimiento en este caso porque hay que llegar al máximo nivel de granularidad, es una medida no aditiva  y además para colmo se calculará en tiempo de ejecución y no de procesado.

    Dicho todo esto :) (y perdón por tanto palabreo), la solución que yo he hecho es meter una dimensión adicional que es de máximo nivel de granularidad (la propia tabla de hechos convertida en dimension) y luego he usado esta formula

    with member mediana as 
    MEDIAN(descendants([dimensiones].[Producto-Categoria].currentmember,,leaves),[Measures].[Cantidad])
    member otramediana as 
    (MEDIAN([dimensiones].[Producto-Categoria].currentmember*descendants([Hechos].[Numventa].[All],,leaves),[Measures].[Cantidad]) )
    select 
    descendants([dimensiones].producto,,leaves) on rows,
    {Measures.mediana,measures.cantidad,measures.otramediana} on columns
    from [Sql2k8r2]
    

    No he puesto tus nombres pero vamos, viene a ser lo mismo. el truco es que he puesto el producto cartesiano del currenmember por el descendants de la dimension hechos en su jerarquia de máximagranularidad a nivel de hoja... vamos máximo nivel de granularidad.

    Así ya para productos en lugar de 26.5 y 22 devuelve el 8 y el 9 que esperabas.

    Espero que te sirva

    Saludos

    Miguel Egea

     


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    miércoles, 13 de abril de 2011 16:45
    Moderador
  • Muchas gracias por el tiempo que habeis dedicado a mi consulta, vuestra respuesta era lo que me temía, pero me quedo mucho más tranquila sabiendo que no hay una buena solución posible a nivel de rendimiento, pensaba que estaba haciendo algo mal.

    Muchas gracias otra vez por vuestra  labor.

    Saludos. Belén

    martes, 19 de abril de 2011 6:37
  • Si necesitas obtener rendimiento en esa consulta, quizá podamos hacerla de otra forma, mezclando diseño multidimensional y mdx.  Aqui estamos si nos necesitas


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    martes, 19 de abril de 2011 7:17
    Moderador