none
Agregación personalizada RRS feed

  • Pregunta

  • Buenas,

     Me estoy encontrando con una de esas consultas en MDX que te quita el sueño y tras varios días sin dar con la solución os pregunto a ver si se os ocurre algo.
     
     Tengo varias dimensiones, simplificando, una con estudiantes, otra con asignaturas y rangos de nota posibles. Mi tabla de hechos recoge las notas que cada estudiante saca en cada asignatura.
     
     Lo que quiero sacar es cuantos estudiantes hay en cada rango de datos. Si un alumno tiene dos notas distintas, no estará en dos rangos, sino que la media de las notas que tenga es lo que determina en que rango está.
     
     Os pongo un ejemplo de los datos.
     
     DimEstudiantes
      E1   Estudiante1
      E2   Estudiante2
       
     DimNotas
     A1 Asignatura1
      A1.1 0-1
      A1.2 1-2
      A1.3 2-3
      ......
     A2 Asignatura2 
      A2.1 0-1
      A2.2 1-2
      ......

     FactNotes
     Estudiante , Asignatura Nota 
     E1   , A1.1  , 1   
     E1   , A1.8  , 8
     E1   , A1.8  , 8
     E2   , A1.1  , 1
     E2   , A1.5  , 5
     E2   , A2.5  , 1
     E2   , A2.2  , 3
     E1   , A2.2  , 2
     
     En una tabla pivote debería salir algo así:
     
     Asignaturas  Número de Estudiantes
     Asignaturta1 
      0-1    0
      1-2    0
      2-3    0
      3-4    1
      5-6    1   
     Asignaturta2 
      0-1    0
      1-2    2
      2-3    0
      3-4    0
      4-5    0      
      
    La explicación sería que para el rango de notas 3-4 de la asignatura 1 hay un alumno, E1 tiene 3 notas y su media es 5,6. Para el rango 5-6 de la asignatura 1 hay un estudiante, el E2, que tiene 2 notas y la media es 3. Para la asignatura 2 seguiría la misma lógica. Los dos alumnos tienen de media 2 y se cuentan en ese rango. Independientemente de la ocurrencia de notas.

    No sé si es que es complicado o que no lo enfoco bien, pero no doy con la medida calculada que me de estos valores. ¿Alguna sugerencia?
    Muchas gracias.


    • Editado Trirafa martes, 12 de agosto de 2014 11:43
    martes, 12 de agosto de 2014 10:53

Respuestas

  • Buenas,

    a ver, he simplificado el caso para que se entendiera mejor. La media no la puedo calcular antes por que depende de la selección que haga el usuario al pedir la información. Me explico, si tengo una dimensión fecha, la media de un alumno dependería de las notas que estuvieran en ese rango de fecha, de ahí que sea un miembro calculado lo que estoy buscando.

    Estudiando el post Implementing buckets in OLAP (tomislav.piasevoli.com/2009/08/24/implementing-buckets-in-olap) , lo que necesito sería muy parecido. Algo así como: 

    CREATE MEMBER CURRENTCUBE.[Measures].[Conteo de Estudiantes por Nota]
     AS 0 ,
    VISIBLE = 1;   

    Scope ([DimAsignaturas].[RangoNotas].MEMBERS);
    This = Sum( iif( [DimAsignaturas].[RangoNotas.CurrentMember.MemberValue =
                     ([Set de estudiantes con nota media al membervalue del currentMember]
                      ),
                     1,
                      0
                    )
              );
    End Scope;

    Algo que al iterar por lo miembros de la jerarquía Asignatura - nota me diga cuantos estudiantes tengan esa nota.

    Pero no consigo dar con la tecla.

    De momento lo que sí he conseguido es hacer una función, o store procedure de SSAS que me devuelve este dato para cada rango de notas. Está claro que no es una solución elegante pero mientras el rendimiento no baje mucho servirá. La idea la vi en el post Calculating Quartiles in Analysis Services (electrovoid.wordpress.com/2011/06/24/ssas-quartile/). A la función le paso un set de alumno y asignatura y la Asignatura-rango que quiero obtener.

    En la función se obtiene la media de cada alumno en cada asignatura, y cuenta cuantos tienen la que le he pasado por parámetro devolviendo este valor.

    Como digo, me vale para dar el dato pero está claro que el rendimiento es peor que con una consulta mdx y funciones nativas. Aparte de no tener que andar desplegando dll en servidores. Todavía no he hecho pruebas de stress pero si finalmente los resultados descartan esta solución lo comentaré por aquí.

    Saludos

    Rafa

    P.D. Siento lo de los enlaces, pero no me deja pegarlos de otra manera.

    viernes, 22 de agosto de 2014 14:22

Todas las respuestas

  • Hola.

    Obtén primero la nota media y luego aplícale el rango a esa media y no al revés.


    Alberto López Grande
    SQL Server MVP
    Visita mi blog en http://qwalgrande.com
    Sígueme en twitter en http://twitter.com/qwalgrande

    sábado, 16 de agosto de 2014 8:32
    Moderador
  •  Hola Alberto,

    La nota media no me sirve, ya que al cruzarla con los miembros de los rangos de notas, para cada rango obtengo la ocurrencia de notas, o la nota media.

    Select {[Measures].[Nota],[Measures].[CNota]} ON 0,
    NON EMPTY [DimAsignaturas].[RangoNotas].MEMBERS on 1
    from [Notas]
    	Nota		CNota
    ----------------------------------		
    All	3		7
    A1	3.75		4
    A1.1	1		2
    A1.5	5		1
    A1.8	8		1
    A2	2		3
    A2.2	2.5		2
    A2.5	1		1

    El requerimiento de negocio es para un rango determinado, cuantos estudiantes tiene como nota media este rango, siendo esta nota media la media de todas sus notas.

    Se parecería mas a esta sentencia de SQL:

    ; WITH cte AS ( 
    		  SELECT r.idRango,t.idEstudiante,
    			    t.Asignatura,Nota,avg(nota) over (Partition by idEstudiante,t.Asignatura) Media
    			  FROM #t2  t
    			  cross join #r1 r
     )
     select  asignatura,idRango, 
     count(distinct idEstudiante) [Nº de Estudiantes]
     from cte
     where idRango = Asignatura+'.'+cast(Media as nvarchar)
     group by  asignatura,idrango
    asignatura idRango Nº de Estudiantes
    A1 		A1.3 		1
    A1 		A1.4 		1
    A2 		A2.2  		2

    Por la forma que va teniendo, no sé una opción sería hacer un procedimiento almacenado de SSAS que me haga el cálculo para cada rango, a modo de función de T-SQL.

    Saludos y gracias!

     

    P.D. He utilizado estos datos:

    SELECT * INTO #T1 FROM (
    SELECT        'E1' AS idEstudiante, 'A1.1' AS idRango,'A1' AS Asignatura, 1 AS Nota UNION
    SELECT        'E1' AS Expr1, 'A1.8' AS Expr2,'A1' AS Asignatura, 8 AS Expr3 UNION
    SELECT        'E1' AS Expr1, 'A1.8' AS Expr2, 'A1' AS Asignatura,8 AS Expr3 UNION
    SELECT        'E2' AS Expr1, 'A1.1' AS Expr2, 'A1' AS Asignatura,1 AS Expr3 UNION
    SELECT        'E2' AS Expr1, 'A1.5' AS Expr2, 'A1' AS Asignatura,5 AS Expr3 UNION
    SELECT        'E2' AS Expr1, 'A2.5' AS Expr2, 'A2' AS Asignatura,1 AS Expr3 UNION
    SELECT        'E2' AS Expr1, 'A2.2' AS Expr2, 'A2' AS Asignatura,3 AS Expr3 UNION
    SELECT        'E1' AS Expr1, 'A2.2' AS Expr2, 'A2' AS Asignatura,2 AS Expr3 ) T
    
    select * into #A from (
    SELECT        'A1' AS idAsignatura, 'Subject 1' AS Asignatura, 'A1.1' AS idRango, '1-2' AS Rango UNION
    SELECT        'A1' AS Expr1, 'Subject 1' AS Expr2, 'A1.2' AS Expr3, '2-3' AS Expr4 UNION
    SELECT        'A1' AS Expr1, 'Subject 1' AS Expr2, 'A1.3' AS Expr3, '3-4' AS Expr4 UNION
    SELECT        'A1' AS Expr1, 'Subject 1' AS Expr2, 'A1.4' AS Expr3, '4-5' AS Expr4 UNION
    SELECT        'A1' AS Expr1, 'Subject 1' AS Expr2, 'A1.5' AS Expr3, '5-6' AS Expr4 UNION
    SELECT        'A1' AS Expr1, 'Subject 1' AS Expr2, 'A1.6' AS Expr3, '6-7' AS Expr4 UNION
    SELECT        'A1' AS Expr1, 'Subject 1' AS Expr2, 'A1.7' AS Expr3, '7-8' AS Expr4 UNION
    SELECT        'A1' AS Expr1, 'Subject 1' AS Expr2, 'A1.8' AS Expr3, '8-9' AS Expr4 UNION
    SELECT        'A2' AS Expr1, 'Subject 2' AS Expr2, 'A2.1' AS Expr3, '1-2 ' AS Expr4 UNION
    SELECT        'A2' AS Expr1, 'Subject 2' AS Expr2, 'A2.2 ' AS Expr3, '2-3' AS Expr4 UNION
    SELECT        'A2' AS Expr1, 'Subject 2' AS Expr2, 'A2.3' AS Expr3, '3-4' AS Expr4 UNION
    SELECT        'A2' AS Expr1, 'Subject 2' AS Expr2, 'A2.4' AS Expr3, '4-5' AS Expr4 UNION
    SELECT        'A2' AS Expr1, 'Subject 2' AS Expr2, 'A2.5' AS Expr3, '5-6' AS Expr4) A
    

    lunes, 18 de agosto de 2014 14:23
  • Hola.

    Insisto, porque lo confirmas al indicar el requerimiento del negocio, que primero se debería obtener la nota media del alumno y después determinar el rango en el que está esa nota media.


    Alberto López Grande
    SQL Server MVP
    Visita mi blog en http://qwalgrande.com
    Sígueme en twitter en http://twitter.com/qwalgrande

    miércoles, 20 de agosto de 2014 9:57
    Moderador
  • Buenas,

    a ver, he simplificado el caso para que se entendiera mejor. La media no la puedo calcular antes por que depende de la selección que haga el usuario al pedir la información. Me explico, si tengo una dimensión fecha, la media de un alumno dependería de las notas que estuvieran en ese rango de fecha, de ahí que sea un miembro calculado lo que estoy buscando.

    Estudiando el post Implementing buckets in OLAP (tomislav.piasevoli.com/2009/08/24/implementing-buckets-in-olap) , lo que necesito sería muy parecido. Algo así como: 

    CREATE MEMBER CURRENTCUBE.[Measures].[Conteo de Estudiantes por Nota]
     AS 0 ,
    VISIBLE = 1;   

    Scope ([DimAsignaturas].[RangoNotas].MEMBERS);
    This = Sum( iif( [DimAsignaturas].[RangoNotas.CurrentMember.MemberValue =
                     ([Set de estudiantes con nota media al membervalue del currentMember]
                      ),
                     1,
                      0
                    )
              );
    End Scope;

    Algo que al iterar por lo miembros de la jerarquía Asignatura - nota me diga cuantos estudiantes tengan esa nota.

    Pero no consigo dar con la tecla.

    De momento lo que sí he conseguido es hacer una función, o store procedure de SSAS que me devuelve este dato para cada rango de notas. Está claro que no es una solución elegante pero mientras el rendimiento no baje mucho servirá. La idea la vi en el post Calculating Quartiles in Analysis Services (electrovoid.wordpress.com/2011/06/24/ssas-quartile/). A la función le paso un set de alumno y asignatura y la Asignatura-rango que quiero obtener.

    En la función se obtiene la media de cada alumno en cada asignatura, y cuenta cuantos tienen la que le he pasado por parámetro devolviendo este valor.

    Como digo, me vale para dar el dato pero está claro que el rendimiento es peor que con una consulta mdx y funciones nativas. Aparte de no tener que andar desplegando dll en servidores. Todavía no he hecho pruebas de stress pero si finalmente los resultados descartan esta solución lo comentaré por aquí.

    Saludos

    Rafa

    P.D. Siento lo de los enlaces, pero no me deja pegarlos de otra manera.

    viernes, 22 de agosto de 2014 14:22
  • Buenas,

    A mí me pasó una cosa parecida...

    Y la asignación de un hecho a una dimensión de una manera dinámica, por diversas razones, no se podía calcular en la ETL...

    Al final, como walkaround, la asignación de la medida a la dimensión se hizo en la aplicación cliente.

    ¿Has probado a bajarte todas las notas y hacer la agrupación en la aplicación?

    Un saludo

    miércoles, 27 de agosto de 2014 14:31
  • Hola,

    desgraciadamente los componentes que utilizamos a la hora de la presentación no nos permiten cálculos en el cliente, tenemos que mapear directamente el modelo del cubo, algo así como una tabla dinámica de Excel. Para un desarrollo a medida, esta sería la opción elegida.

    saludos

    miércoles, 10 de septiembre de 2014 11:00