PIENSO-PIENSO: Como modelar datos acumulados II (Mi Propuesta)

Este post es respuesta de PIENSOPIENSO: Como modelar datos acumulados. Lo iba a poner en los  comentarios, pero quedaba demasiado largo y es difícil.
Supongamos el problema general de tener una tabla de muchos (millones) registros con la estructura
Clave*
Dimension1
Dimension2
….
DimensionN
Indicador1 Sum()
Indicador2 Count()
Podríamos tener otras funciones de agregación como Avg(), Max(), Min(), etc.
Se quieren hacer consultas de resumen sobre los datos de dicha tabla, pero por tener tantos registros no se pueden resolver en forma eficiente en el momento de ejecución del programa.
Una posible solución es crear una tabla que tenga los datos acumulados y que tenga la estructura:
*AcuDimension1
*AcuDimension2
….
*AcuDimensionN
SumIndicador1 Sum(Indicador1)
CountIndicador2 Count(Indicador2)
En los campos DimensionX de la tabla acumulada se almacenaran todos los valores del campo correspondiente de la tabla de arriba, mas un valor que signifique “Todos los valores”. Puede ser el 0 para numéricos y el “” para char.
De esta forma, puedo hacer un programa de carga que recorra la tabla de datos y cargue la estructura, y agregue también todas las combinación de un conjunto parcial de dimensiones.
Por ejemplo, con dos dimensiones el programa de carga quedaría algo así (pseudocódigo)
1: for each Not Cargado
2:   &Dimension1=Dimension1
3:   &Dimension2=Dimension2
4:   &Indicador1=Indicador1
5:   Acumulo.Call(&Dimensio1,         &Dimension2,        &Indicador1)
6:   Acumulo.Call(&Dimension1.Empty(),&Dimension2,        &Indicador1)
7:   Acumulo.Call(&Dimension1,        &Dimension2.Empty(),&Indicador1)
8:   Acumulo.Call(&Dimension1.Empty(),&Dimension2.Empty(),&Indicador1)
9:   Cargado=True
10: endfor
11: 
12: Proc Acumulo
13: parm(in:&Dimension1,in:&Dimension2,in:&Indicador1)
14: 
15: for each //acumulo si ya existe
16:      where AcuDimension1=&Dimension1
17:      where AcuDimension2=&Dimension2
18:           SumIndicador1+=&Indicador1
19:      when none
20:        //Agrego el registro si no existe.
21:        New
22:          AcuDimension1=&Dimension1
23:          AcuDimension2=&Dimension2
24:          SumIndicador1=&Indicador1
25:        Endnew
26: endfor
27: 
28: 
29: EndSub
30: 


Que es lo resultante en dicha tabla?. En realidad, son 4 tablas en una, pues se guardan en ella, los acumulados de {Dimension1,Dimension2}=Tabla 1, {Dimension1}=Tabla 2, {Dimension2}=Tabla 3 y el acumulado total, sin ninguna de las dimensiones {}=Tabla 4.

Las consultas son sumamente rápidas, pues para obtener un acumulado se necesita consultar únicamente un registro con la clave primaria pues todas las combinaciones ya están resueltas.

La consulta quedaría de la forma:
1: for each
2:  where AcuDimension1=&Dimension1  
3:  where AcuDimension2=&Dimension2
4:  &Total = SumIndicador1
5: endfor
6: 
Donde las variables &Dimension1 o &Dimension2 pueden estar con el valor Empty() que significan que no me interesa filtrar por dicha dimensión. Queda sencillo,no?

Que desventajas tiene este modelo?

La tabla no sigue las reglas del modelo relacional. No se puede consultar libremente, pues los datos que tienen estan redundantes. Se podría aclarar un poco, agregándole un tipo de acumulado, que en el ejemplo tendria un valor entre 1 y 4. Las consultas que mezclen el tipo de acumulado, no son válidas.

El tiempo de carga puede ser largo y la cantidad de registros que se almacenan puede ser bastante grande. No mucho mas grande que en otros modelos de datos de Data Warehousing, pero como queda todo en una misma tabla, puede complicarse la performance y los indices.

La cantidad de combinaciones crecen al aumentar el numero de dimensiones.
Con dos dimensiones, se tienen 4 posibles combinaciones, con n, hay 2 elevado a la n. Esto es un problema solamente para la carga y no para las consultas. 

También puede ayudar tener dimensiones que sean obligatorias en las consultas y que haga que no sea necesario calcular un acumulado con esa variable ausente, pues la combinación no seria valida. Por ejemplo, se puede limitar que no se van a poder consultar datos de diferentes años, por lo que puede poner la dimensión año como obligatoria.


Podría hacerse mas fácil?
Esto podría automatizarse y generalizarse con un GeneXus pattern, que permita seleccionar de una transacción los atributos dimensiones, los indicadores que se quieren sumarizar, con sus funciones de agregación.

Con esto, el Pattern podría generar:
  • La tabla de acumulados
  • El programa de carga batch
  • Los programas para actualización online ( si se quiere mantener los acumulados con la transacción o Business Component).
  • Los objetos para recuperar datos (Data providers y Data Selector)
  • Un panel de visualizar los resúmenes (similar al SummarizedBy Pattern)
  • Generacion de objeto Query y un panel para visualizarlo.
Otras mejoras posibles.

En algunas oportunidades que utilice este método, se nos solicitaba también el acumular por SEMESTRES y TRIMESTRES. Si bien esto se puede hacer sumando varios registros, también puede resolverse en el momento de la carga.

Para esto, alcanza con definir una dimensión PERIODO para cada fecha, que codifique la fecha de la forma AAAASSTTMMDD donde :
  • AAAA es el año
  • SS es el semestre S1 y S2
  • TT es el trimestre T1,T2,T3 y T4
  • MM y DD es el mes y el día.
Con esto, puedo seguir manteniendo las consultas únicamente por la clave primaria para retornar un acumulado, aunque sea por semestre. Almacenando únicamente esta dimensión, se agrega una limitación, que no permite consultar tan fácilmente los primeros trimestres de cada año, pero para algunas aplicaciones esto no es importante.

Rankings.

Un uso muy frecuente de estos datos sumarizados es para la presentación en forma de rankings.

Las consultas son del tipo,

Listar los países que compraron el producto X el Año pasado y ordenarlos en forma decreciente.

Para resolver esta consulta en forma eficiente, se pueden seleccionar todos los registros con Producto=X y Periodo=AAAA y luego ordenarlo en forma descendente por la suma del indicador.

Comentarios

  1. ¿O sea que me gané el chocolate? ;)

    Cuando decís que "como queda todo en una misma tabla, puede complicarse la performance y los indices.". En realidad hay un solo índice, clustered por clave primaria, porque siempre vas a tener instanciadas todas las dimensiones (si no la consulta no es válida).

    ResponderEliminar
  2. El resultado del premio, esta en los comentarios del otro post.

    Puede ecesitarse indices, si vas a hacer rankings, que devuelva por ejemplo, todos los paises ordenados por ventas descendentes.

    ResponderEliminar

Publicar un comentario

1) Lee el post
2) Poné tu opinión sobre el mismo.
Todos los comentarios serán leidos y la mayoría son publicados.

Entradas más populares de este blog

El Sordo

Paleta de colores en GeneXus