24 abril 2012

Agregaciones personalizadas en Analysis Services: Eventos no agregables en el tiempo (Event Snapshots)

Probablemente alguna vez hayan tenido o tendrán con lidiar algún requerimiento de negocios que les pida algo parecido a:

  • Cuál es el número de miembros (socios / clientes / matrícula) por mes (los clientes se dar de alta o baja en cualquier momento o según un contrato).
  • Cuál es el monto de reclamaciones según su estatus (Ej. abiertas, en proceso, pagadas, etc.) por mes.

En todos estos casos, el usuario requiere medir un determinado evento dentro de un mes o frecuencia de tiempo requerida, cuyo resultado no es agregable (sumarizable) con otra instancia de tiempo. Por ejemplo, no podemos sumar la membresía (total de socios) de Enero con la de Febrero, ya que podríamos estar duplicando o contando incorrectamente, socios que estaban afiliados durante ambos meses o a quienes estuvieron sólo uno de los dos meses.

De igual manera, sucede mucho en la industria de seguros o en algún proceso que conlleve diferentes estados para una transacción o evento, donde se requiere contar la cantidad de eventos de acuerdo al estado. Por ejemplo, una pregunta típica de negocios en seguros de salud es saber cuál es el inventario de reclamaciones abiertas (que no han sido adjudicadas o pagadas) mes tras mes, ya que esto permite hacer una proyección del flujo de caja requerido para cubrir dichos compromisos en el futuro cercano.

Este tipo de preguntas (en donde los eventos no son agregables), es referido en algunos libros o documentación en inglés como “event snapshots” lo que sería como una foto en el tiempo de dicho evento.

El objetivo de este post es mostrarles cómo he manejado estas situaciones usando Analysis Services 2005, 2008 o 2012 (si usan SQL Server 2012, sería apropiado decir específicamente Analysis Services Multidimensional).

Analysis Services incluye “out-of-the-box” varios tipos de agregaciones para medidas que no pueden ser sumadas directamente (semi-additive measures), esto incluye Average of Children, LastNonEmpty, Distinct Count, etc., pero para nuestro caso, ninguna de las existentes nos resuelve la pregunta de negocios y es por ello que debemos recurrir a las medidas calculadas usando MDX.

Hay por lo menos hay dos opciones para obtener los resultados requeridos usando Analysis Services y MDX para cada mes (podría ser día, año o cualquier otra unidad de tiempo), basándonos en una tabla de hechos (fact table) previamente cargada en nuestro Data Warehouse (DW) relacional :

  1. Basado en una tabla de hechos que guarde las transacciones de matrícula o registro (FactMatricula o FactEnrollment):
    Esta tabla tendría algo muy similar a la transacción original que dio de alta a ese miembro (por ejemplo: [FechaDeRegistro], [MiembroID], [FechaDeBaja]).
  2. Basado en una tabla de snapshot (FactEnrollmentSnapshot
    Esta tabla guardaría la “foto” de los miembros existentes en cada mes.

Podemos discutir los pros y contras de cada una de las opciones anteriores, como por ejemplo:

  • La opción 1 consumiría menor espacio en el DW.
  • La opción 1 es solamente accesible desde herramientas que se puedan conectar a cubos de Analysis Services. (Claro, algún usuario intrépido, podría usar T-SQL para hacer consultas a nuestro DW directamente, pero requeriría el uso de BETWEEN en la sentencia).
  • La opción 2 puede ser más rápida para generar resultados ya que los valores persisten ya en disco (menos procesamiento).
  • La opción 2 puede ser más simple de implementar en MDX versus la opción 1.
  • La opción 2 no requiere una consulta muy sofisticada (uso de BETWEEN) si se usa alguna herramienta que conecte al DW.

En mi caso, les voy a presentar a continuación cómo solucionar el problema usando la opción 2, pues en la mayoría de los proyecto en los que he trabajado, el poder obtener los resultados desde los dos mundos (relacional y cubos / multidimensional) representa una gran ventaja. La segunda razón para seguir esta estrategia es que en el caso de soluciones de BI o Data Warehouse, el consumo de espacio en disco  puede ser de menor preocupación que los tiempos de respuesta (resultados rápidos).  Este último paradigma puede cambiar si usamos bases de datos en memoria como PowerPivot o Analysis Services en modo Tabular (pero eso es cuestión de otro post interesante).

El siguiente diagrama muestra el esquema relacional en mi DW, usando modelamiento dimensional:

image

Por tanto, en este ejemplo tenemos 4 dimensiones (DimMember, DimGroup, DimLineOfBusiness y DimDate), y un fact table que guarda un snapshot mensual de los miembros registrados por cada grupo y línea de negocios. Un extracto de los datos que guarda nuestro fact tables se muestra a continuación usando T-SQL (pueden ignorar las tres últimas columnas del query):

SELECT TOP 1000 [MonthUniqueID], [DimMemberID], [DimGroupID], [DimLineOfBusinessID]
                            ,[ETLExecutionLogID], [ETLBatchLogID],[ETLLastUpdate]
FROM [DW_DDS].[member].[FactEnrollmentSnapshot]

image

Como se puede apreciar en la imagen anterior, tenemos una línea para cada año-mes (yyyymm) en donde tuvimos miembros registrados (incluyendo el grupo al que pertenecían y la línea de negocios).

El siguiente paso sería agregar dichas tablas a mi proyecto de Analysis Services en forma de dimensiones y measure groups para los fact tables. No voy a profundizar en los detalles de cómo hacerlo, ya que esto es la parte básica de Analysis Services (pueden revisar el video de Fundamentos de Analysis Services si necesitan ayuda en este punto). Lo único importante de mencionar aquí, es que ya que estamos contando la cantidad de miembros existentes para cada mes, podemos agregar una columna calculada [_Enrollment Count] en nuestro Data Source View que guarde la cantidad de miembros en cada línea, es decir 1. Esto nos facilitará el conteo.

image

Luego de haber hecho todo el trabajo básico para agregar el fact table y la nueva medida a nuestro cubo, podemos examinar los resultados y veremos que por defecto Analysis Services agrega (suma) todos los registros sin “entender” que la membresía no se puede agregar por encima de año-mes (el número debe estar alrededor de 200 a 300 mil miembros en cada mes, trimestre o año):

image image

Si filtramos o expandimos los resultados a nivel de mes (año-mes), tenemos los resultados correctos en términos de negocios, pero no a nivel de Trimestre o Año :

image

Ya que nuestra consigna siempre es hacerle la vida más fácil para el usuario de negocios, vamos a usar un poco de MDX para hacer que los resultados siempre se muestren “correctamente”.

Traduciendo esto, debemos lograr: 1) que la membresía sólo se muestre si se selecciona el periodo de tiempo; 2) si el usuario pide la membresía en una frecuencia de tiempo mayor a mes (trimestre o año), se debe mostrar el resultado del último mes con respecto a dicho periodo de tiempo (membresía al cierre del periodo).

Para obtener la funcionalidad requerida, vamos ocultar la medida por defecto y en su reemplazo crear una medida calculada llamada [Enrollment Snapshot Count]:

image  image

La magia viene ahora, usando MDX para crear la medida calculada e inicializarla como vacía (NULL):

//*********** Member Snapshot Calculations ****************************//
CREATE MEMBER CURRENTCUBE.[Measures].[Enrollment Snapshot Count]
AS NULL,
FORMAT_STRING = "#,##0",
NON_EMPTY_BEHAVIOR = { [_Enrollment Count] },
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Enrollment Snapshot' ;



Luego en un segundo paso asignamos los valores requeridos (el conteo de miembros) usando la función SCOPE(mucho más eficiente que si lo hacemos usando la sentencia IIF):



//Assign last non-empty value for current member period (Year, Quarter or MonthPeriod)
SCOPE ([Month Period].[Calendar by Period].members ,[Measures].[Enrollment Snapshot Count]);
THIS = SUM(TAIL(DESCENDANTS([Month Period].[Calendar by Period],
[Month Period].[Calendar by Period].[Month Period])
,1)
,[Measures].[_Enrollment Count]);
END SCOPE;


Descomponiendo la asignación tenemos:




  1. Para todos los miembros existentes de mi jerarquía [Calendar by Period] en mi dimensión de tiempo [Month Period]


  2. …asignar valor a la medida calculada [Measures].[Enrollment Snapshot Count]


  3. …desde el último mes [Month Period] existente para el periodo de tiempo seleccionado (o el último mes si no ha sido seleccionado alguno)


  4. …igual a la suma de la cantidad de registros existente en la medida oculta (SUM(…, [measures].[_Enrollment Count])



La función DESCENDANTS nos trae los miembros del nivel deseado (en este caso al nivel [Month Period]), relativo a la posición actual dentro de la jerarquía de tiempo ([Calendar by Period]) . Pero como sólo queremos el último mes, usamos la funcion TAIL, la cual nos trae los últimos N miembros de la dimensión, en nuestro caso N es igual a 1.



Los resultados obtenidos con nuestra nueva medida calculada se muestran a continuación:



image



image



image



  image



Para concluir, hoy hemos visto cómo el uso de las técnicas de modelaje dimensional y el uso de MDX nos pueden ayudar a resolver preguntas en donde las agregaciones no sean una simple suma o alguna de las opciones que presenta por defecto Analysis Services en modo Multidimensional, buscando que la experiencia del usuario sea la más sencilla para ayudar a la usabilidad de las soluciones que creemos para ellos.



Alan