14 abril 2009

Reglas MDX en PEL: Children vs Descendants

En honor al memorable y que en vida fue PerformancePoint Planning les hago llegar un pequeño tip con respecto a la creación de Definition Rules de tipo MDX usando la sintaxis de PEL (PerformancePoint Expression Language) para crear sus reglas de negocio (business rules) dentro del Planning Business Modeler: Además aunque la experiencia fue a través del uso de PEL, eso aplica 100% a su trabajo directamente con consultas basadas en MDX directamente a su cubo de Analysis Services sin usar PPS Plannig ya que los lenguages son bastate similares.

Es importante recordar que los Definition Rule de tipo MDX son reglas que se traducirán físicamente en miembros calculados (calculated members) en su respectivo cubo de Planning. En este punto es tambien importante mencionar que las medidas calculadas son de un alto costo para Analysis Services ya que éstas se ejecutan en tiempo de ejecución o run-time en el momento que el usuario consulte los valores de la celda(s) que se esté(n) calculando. Pueden revisar mayor información entre los tipos de de reglas de negocio en PPS Planning en http://msdn.microsoft.com/en-us/library/bb795345.aspx.

En nuestro caso nuestros formularios y reportes basados en data de los cubos de Planning tenían un rendimiento terrible, se supone que el tiempo de respuesta sería algo más lento que si hubiesemos podido crear esta misma regla pero de tipo Assigment (en la que los valores se guardan directamente en el fact table o measure group table en la base de datos relacional de Planning), pero tampoco era para tanto.

Al final de pasar un buen tiempo tratando de decifrar el problema nos dimos cuenta que la razón de la exagerada lentitud era por un cálculo que teníamos aplicable a la jerarquía de las categorías de artículos existentes para obtener la proyección de ventas en los non-leaf levels o niveles no-granulares. En este caso los niveles granulares ya tenían asignados su valor por medio de otra regla de negocio ejecutada en un paso anterior en una operación simple, pero en los niveles NO granulares debíamos “simplemente” sumar los valores de sus hijos. Ejemplo:

Categoría Cant. Comentario
Zapatos 80 --> Non-Leaf: aquí se requiere el cálculo para sumar los hijos
Zapatos de niño 30 --> Non-Leaf: aquí se requiere el cálculo para sumar los hijos
   Zapato niño tipo 1 10 --> Leaf: aquí existe el valor
   Zapato niño tipo 2 20 --> Leaf: aquí existe el valor
Zapatos de niña 50 --> Non-Leaf: aquí se requiere el cálculo para sumar los hijos
   Zapato niña tipo 1 35 --> Leaf: aquí existe el valor
   Zapato niña tipo 2 15 --> Leaf: aquí existe el valor

 

La regla que habíamos configurado inicialmente era la siguiente, fíjense en la parte resaltada subrayada:

scope (
[Account].[Sales Accounts].[SalesProjection],
[Scenario].[All Members].[Budget],
[BusinessProcess].[Standard].[INPUT],
{[Time].[Monthly View].[FY 2010],Descendants([Time].[Monthly View].[FY 2010], 0, after)}) ;

this = iif([Articles].[Category Hierarchy].CurrentMember.IsLeaf,
(),SUM(([Articles].[Category Hierarchy].CurrentMember.Children)));


La lógica general consiste en definir el scope o ámbito que queremos afectar y luego asignarle a ese scope (representado por this), el cálculo. El cálculo en este caso está definido como la suma de todos los hijos (children) de la categoría en proceso sólo cuando no estemos procesando en el leaf level o nivel granular de nuestros datos. Este cálculo nos estaba tomando más de 15 minutos para obtener los resultados esperados para una de las tiendas.

Entre las muchas pruebas que hicimos y casi cuando estábamos listos para “tirar la toalla”, decidimos hacer un cambio aparentemente menor: reemplazar la llamada a los “hijos” del artículo actual usando la función Children al uso de la función DESCENDANTS según se muestra a continuación:

scope (
[Account].[Sales Accounts].[SalesProjection],
[Scenario].[All Members].[Budget],
[BusinessProcess].[Standard].[INPUT],
{[Time].[Monthly View].[FY 2010],Descendants([Time].[Monthly View].[FY 2010], 0, after)}) ;

this = iif([Articles].[Category Hierarchy].CurrentMember.IsLeaf,
(), SUM(DESCENDANTS([Articles].[Category Hierarchy].CurrentMember,999, LEAVES)));

Para nuestra grata sorpresa este pequeño cambio nos redujo el tiempo de respuesta a menos de 30 segundos lo cuál era bastante aceptable para el volumen de datos que se manejan en esta solución. Para obtener mas detalles acerca de la función DESCENDANTS pueden ver aquí http://msdn.microsoft.com/es-es/library/ms146075.aspx.

La moraleja de todo esto es que en el caso del procesamiento de MDX en Analysis Services no se aplican las mismas reglas que en el mundo relacional o de procesamiento de T-SQL sobre SQL y me explico a continuación. En el caso del motor relacional nosotros podemos enviar determinada consulta y a pesar de que la misma no sea lo más optima (redundancia en el número de tablas, JOINS, Order by, etc.)  podemos confiar en que el QUERY ENGINE de SQL Server nos dará una gran ayudita optimizando nuestra consulta antes de ejecutarla. En el caso de MDX y las fuentes OLAP esto no sucede de tal forma, por tanto a pesar de que aparentemente Children y Descendants nos darían los mismos resultados de manera visual, el uso de Descendants en este caso es mucho más eficiente a la hora de procesar nuestro proceso. Esta misma regla pueden aplicarla en los casos en que sus consultas de MDX se tornen demasiado lentas, traten de explorar las posibilidades que el lenguaje les da para obtener los mismos resultados de distintas maneras pero en menor tiempo.

 

Alan

3 comentarios:

Jaime Mendoza dijo...

Hola, tengo un problema parecido con la eficiencia de un calculo en mi proyecto.

La cuestion es la siguiente, en mi proyecto tengo las respuestas de una encuesta de docentes, segun esto se miden las competencias de los mismos.

En el hecho tengo las respuestas de un docente a cada pregunta de la encuesta, con un calculo obtengo el valor de la respuesta (si, no, frecuencia, opcion multiple...) segun sea el caso.

Por otra parte tengo una jerarquia de la siguiente manera: Competencia->Desempeño->Indicador->Pregunta, con el valor calculado es el valor para las preguntas. Para calcular el valor de un indicador es la suma del valor de las preguntas por su respectivo peso, de la misma manera para desempeños y competencias.

Tengo cerca de 40.000 hechos de las respuestas, al momento de revisar el calculo en el explorador de Analysis, se tarda mucho en votar el resultado, puesto que el primer nivel mostrado de la jerarquia es Competencia y este depende del valor de sus hijos (implica calcular desde pregunta que es el nivel mas bajo) para mostrar el valor.

El calculo que realizo esta planteado de una manera generica (recursivo) con el caso base en pregunta que es quien tiene valor al realizar el calculo de las respuestas.

Soy nuevo utilizando MDX, mis dudas para mejorar el rendimiento son: algu tipo de configuracion de la herramienta o en la jerarquia, optimizacion en el calculo, alguna manera de almacenar el valor de los calculos para luego simplemente cargar estos datos.

Espero haberme hecho entender, gracias por tu atencion y respuesta.

att:
Jaime Mendoza
Estudiante Ing. de Sistemas
jmendoza@unicauca.edu.co

Alan Koo dijo...

Hola Jaime,

Desafortunadamente en el caso de MDX, mucho tiene que ver cómo diseñamos nuestro cubo, las funciones MDX que usemos y la consulta en su totalidad. Esto versus T-SQL en donde a pesar del query que tu le mandes, el motor del servidor aplicará su "inteligencia" de optimización para resolver el query de la mejor manera dejándole mucho menos tarea al usuario.
Por otro lado el tener medidas calculadas en Analysis Services es una de las cosas más costosas en términos de tiempo de procesamiento, ya que a diferencia de las medidas, el resultado de los cálculos no se pre-calcula (y persiste).
Mis sugerencias serían las siguientes:
* Revisar el diseño de tu fact-dimensiones a ver si hay oportunidades de optimización.
* Precalcular y guarda en el fact table, todo lo que se pueda hacer en este nivel, esto evitará tener más cálculos en el cubo.
* Jugar con las funciones, DESCENDANTS, EXISTS, NON EMTPY, etc.

En teoría 40,000 filas en el fact table, no deben ser mayor problema para el motor de Analysis Services, claro, siempre y cuando lleguemos a ese punto óptimo entre el diseño y el query MDX, he ahí lo interesante del asunto.

Alan

Jaime Mendoza dijo...

Gracias por la respuesta, con mi compañero estamos teniendo en cuenta el costo de los calculos MDX, estamos analizando y probando la posibilidad de realizar los calculos en la BD, por medio de las vistas materializadas, ahorrando todo el proceso de calculo en el Analysis, estamos aprendiendo esto sobre las vistas...tenemos una duda sobre la actualizacion de los valores o las situaciones de nuevos registros (nuevas encuestas, cambios en las preguntas de la encuesta, nuevos pesos de las preguntas...), como seria este proceso. teniendo en cuenta tambien tus recomendaciones.

Gracias.