Recientemente nos hemos visto involucrados en un proyecto SSAS Multidimensional en el que existía una dimensión de cuentas padre-hijo bastante compleja y con un operador unario por en medio. Es por esto que nos hemos decidido a describir brevemente la implementación por defecto de SSAS y la que utilizamos finalmente para mejorar el rendimiento.
Implementación nativa padre-hijo + operador unario de SSAS
Esta es posiblemente la manera más rápida y sencilla de implementar la jerarquía de cuentas. Por contra, a poco que nuestro proyecto sea algo complejo su rendimiento puede caer drásticamente.
Para usarla solo necesitamos que el origen de la dimensión tenga una relación padre-hijo y por supuesto el operador unario. A modo de ejemplo tenemos la siguiente imagen, en la que podemos ver que las columnas “Account” y “ParentAccount” definen nuestra jerarquía y el grupo de cuentas marcado con un 2 tiene como padre la cuenta AU.
Para definir la jerarquía en SSAS vamos al diseñador de dimensiones en Visual Studio. En las propiedades del atributo “ParentAccount” buscamos “Usage” y seleccionamos “Parent”. El siguiente paso es definir el operador unario que describe como agregan hacia arriba nuestros niveles de la jerarquía. En nuestro caso lo indica la columna “UnaryOperator” y puede tener tres tipos de valor: “+”, “-“ y “~”. Los símbolos de suma y resta indican que la cuenta suma o resta al agregar hacia arriba, mientras que la virgulilla (~) que esa cuenta no agrega hacía arriba. SSAS permite indicar este atributo y se encarga de adaptar las agregaciones en función de su valor. Para configurarlo volvemos al diseñador de dimensiones en Visual Studio y en la propiedad “UnaryOperatorColumn” del atributo padre seleccionamos la columna “Unary Operator”
Si ahora visualizamos el resultado podemos ver como las dos cuentas hijas no suman hacia el padre, si no que la que tenía el operador unario negativo está sustrayendo su valor a la otra.
Jerarquía aplanada + Factor
Si el rendimiento de la implementación nativa de la jerarquía padre-hijo + operador unario se nos antoja insuficiente podemos quitarle el trabajo que se le atraganta a SSAS. Sin embargo, la siguiente aproximación tiene dos problemas, el primero es que perdemos el control sobre las cuentas que tienen el operador unario “~” y que no deben agregar hacia arriba. Si tenemos este tipo de cuentas deberemos crear distintas jerarquías o tratarlas de alguna manera con MDX. El segundo es que unicamente es valido sí las cuentas con operador unario “-” son nodos hoja.
El primer paso será aplanar la jerarquía que tenemos como origen de la dimensión, generando una columna por cada nivel que haya además de atributos que necesitemos (Operador unario para la cuenta concreta, indicadores de orden, etc). Podemos encontrar un ejemplo en este otro artículo. Una vez lo tengamos deberemos sustituir el operador unario por factor que será positivo para cuentas que deban sumar y uno negativo para las que deban restar.
En nuestro ejemplo la tabla se queda de la siguiente manera
Se puede comprobar como volvemos a tener las cuentas 75 y 76 pero no aparece la cuenta 73 que era su padre. Esto es porque todos los hechos están asociados a las cuentas hoja de la jerarquía y los padres aparecen en las columnas DescLVL. En nuestro ejemplo, el padre era la cuenta AU y aparece en la columna DescLVL5.
Volvemos al Visual Studio, creamos la dimensión que sale de la tabla y en el diseñador de jerarquías creamos una nueva jerarquía.
Posteriormente cambiamos a la pestaña Attribute Relationships donde deberemos relacionar los miembros de la jerarquía entre sí. Para ello arrastramos el nivel más bajo a su padre y así sucesivamente hasta que todos estén relacionados en el orden adecuado (por ejemplo del 13 al 12)
Ahora debemos construir una nueva tabla de hechos muy sencilla que tendrá dos campos, el RecId (sk) de la dimensión de cuentas y el Factor que acabamos de construir. En realidad, ni el operador unario ni el factor nos hace falta en la dimensión, pero se ve más sencillo a nivel didáctico de esta manera.
Seguidamente relacionamos la columna RecID de la tabla de factores con la dimensión de cuentas en el DSV y creamos un nuevo grupo de medidas basado en Factor
El último paso para que la magia empiece a aparecer es cambiar en la medida a la que queremos que afecte el operador unario modificar la propiedad “MeasureExpresion” y escribimos el nombre de la medida por el nombre del factor. En el ejemplo he creado una segunda medida llamada Qty Factor a la que he modificado esta propiedad para poder compararla con la medida original sin operador unario.
Si visualizamos el resultado veremos que para la medida Qty, a la que no afecta el factor, las mismas dos cuentas anteriores se están sumando los valores como positivos y, sin embargo, para la medida Qty Factor, que si está afectada por el factor, la cuenta UOosu está restando.
Y con esto ya tenemos implementado un operador unario. Pudiera ser que a nuestros usuarios no les convenza que el valor aparezca como negativo cuando en realidad es positivo y es la cuenta la que resta. No podemos hacer nada para evitar que el valor sea negativo y deberemos tenerlo en cuenta para posibles medidas calculadas etc. pero si podemos maquillar la representación gráfica para que parezca que es un valor positivo. Para ello abriremos el cubo, y en el Script View de la pestaña calculations añadiremos el siguiente código MDX.
scope([Measures].[Qty Factor]);
format_string(this) = IIF(Measures.[Factor]<0 , "-#,##0.00;#,##0.00;0;0", "#,##0.00;-#,##0.00;0;0");
end scope;
Tras desplegar veremos que ahora sí la cuenta aparece como positiva, pero está restando su valor al padre. También se puede comprobar en la parte superior que Excel internamente lo tiene como un valor negativo.
