Cálculos de tiempo personalizados en SSAS Multidimensional

Siguiendo con el post que publicamos en mayo sobre jerarquías padre – hijo y su implementación en SSAS MD explicaremos como definir agregaciones “personalizadas” para los cálculos temporales.

Si para ciertas cuentas en lugar de sumar en los cálculos temporales queremos hacer otra operación, por ejemplo, la media, podemos definir una columna en la tabla con la jerarquía (que llamaremos TBAverage) la cuál  funcionaría como un “flag” que para cada cuenta indique si suma (0) o hace la media (1). La usaremos en el cubo como una medida que comprobaremos en cada nivel para detectar si esa cuenta debe sumar o hacer la media para los cálculos temporales. De esta manera, cuando seleccionemos en el report el YTD de la cuenta seleccionada hará la media de los meses anteriores en ligar de la suma.Partiendo de una jerarquía ya aplanada nuestro flag se vería como se muestra en la imagen:

Las celdas amarillas indican las cuentas que harán una suma al calcular agregados de tiempo y la verde la que hará una media.

Crearemos tantas vistas como niveles tengamos en nuestra jerarquía de cuentas para usar en el cubo como grupos de medidas. Las vistas tendrán dos campos: el id de la cuenta y la columna TBAverage.

Una vez creadas las vistas las añadimos al cubo como grupos de medidas y creamos una medida para cada uno de estos grupos sobre la columna TBAverage indicando “min” como función de agregación:

Después, las relacionamos con la dimensión account por cada nivel de la jerarquía. Por ejemplo la vista fact.TBAverageLvl4 se relacionará con la dimensión Account por la columna de ID lvl 4.

Para poder implementar los cálculos temporales crearemos un miembro calculado en la dimensión tiempo. Como ejemplo podemos hacer este cambio en el DSV. Para ello en el DSV hacemos click derecho sobre la tabla Date y seleccionamos “New named calculation”, le ponemos un nombre identificativo, en nuestro caso “Calendar Hierarchy Date Calculations” y como valor por defecto “Current Date”.

En el script MDX crearemos un nuevo miembro para el cálculo del year to date (YTD):

Create Member
    CurrentCube.[Date].[Calendar Hierarchy Date Calculations].[Year to Date]
As "NA" ;

Y si lo analizamos sobre Excel, esto es lo que nos devuelve:

Como vemos en la imagen, el YTD de febrero es la suma del dato de enero y febrero.

Si queremos que tenga en cuenta si debe sumar o agregar debemos comprobar el valor de las medidas “TBAverage” que hemos creado. Para ello usaremos este código MDX:

scope ( [Date].[Calendar Hierarchy Date Calculations].[Year to Date]);
    //Valor por defecto del YTD
    this=sum(YTD([Date].[Calendar Hierarchy].currentmember)
            ,([measures].currentmember,[Date].[Calendar Hierarchy Date Calculations].&[Current Date]));

   scope ([Account Factor].[Account Factor Hierarchy].[Desc LVL1].members);
    this=iif(([Measures].[TBAVERAGELVL1],[Date].[Calendar Hierarchy Date Calculations].&[Current Date])=1,
           avg(YTD([Date].[Calendar Hierarchy].currentmember)
                ,([measures].currentmember,[Date].[Calendar Hierarchy Date Calculations].&[Current Date]))
           ,sum(YTD([Date].[Calendar Hierarchy].currentmember)
                ,([measures].currentmember,[Date].[Calendar Hierarchy Date Calculations].&[Current Date]))
           );
  end scope;

  scope ([Account Factor].[Account Factor Hierarchy].[Desc LVL2].members);
   this=iif(([Measures].[TBAVERAGELVL2],[Date].[Calendar Hierarchy Date Calculations].&[Current Date])=1,
           avg(YTD([Date].[Calendar Hierarchy].currentmember)
                ,([measures].currentmember,[Date].[Calendar Hierarchy Date Calculations].&[Current Date]))
          ,sum(YTD([Date].[Calendar Hierarchy].currentmember)
                ,([measures].currentmember,[Date].[Calendar Hierarchy Date Calculations].&[Current Date]))
         );
   end scope;
   //Repetir para todos los niveles de la jerarquías de cuentas
end scope;

Así, evaluará el valor de TBAverage para cada nivel de la jerarquía y hará la suma o la media en función del valor de esta columna.

En el ejemplo vemos que la media de enero y febrero es 118,88 y es mismo valor que muestra en la columna de Year to Date para febrero.

Hemos incluido subscopes dentro del scope de Year to Date porque es más eficiente que poner varios iifs anidados con la comprobación del nivel en el que estamos + el tbAverage dentro de un mismo scope. De esta forma solo analizará los niveles necesarios. Además, usamos iif en lugar de case porque es más eficiente (case siempre evalúa todos los casos).

Esta implementación permite que en cualquier momento se pueda cambiar el tipo de agregación que hace una cuenta, simplemente reprocesando la dimensión de cuentas sin necesidad de modificar el código del cubo.