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