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
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:
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)
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:
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:
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 :
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.
Supongamos el problema general de tener una tabla de muchos (millones) registros con la estructura
| Clave* | |
| Dimension1 | |
| Dimension2 | |
| …. | |
| DimensionN | |
| Indicador1 | Sum() |
| Indicador2 | Count() |
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) |
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 Cargado2: &Dimension1=Dimension13: &Dimension2=Dimension24: &Indicador1=Indicador15: 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=True10: endfor11:12: Proc Acumulo13: parm(in:&Dimension1,in:&Dimension2,in:&Indicador1)14:15: for each //acumulo si ya existe16: where AcuDimension1=&Dimension117: where AcuDimension2=&Dimension218: SumIndicador1+=&Indicador119: when none20: //Agrego el registro si no existe.21: New22: AcuDimension1=&Dimension123: AcuDimension2=&Dimension224: SumIndicador1=&Indicador125: Endnew26: endfor27:28:29: EndSub30:
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:
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?1: for each2: where AcuDimension1=&Dimension13: where AcuDimension2=&Dimension24: &Total = SumIndicador15: endfor6:
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.
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.
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.
¿O sea que me gané el chocolate? ;)
ResponderBorrarCuando 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).
El resultado del premio, esta en los comentarios del otro post.
ResponderBorrarPuede ecesitarse indices, si vas a hacer rankings, que devuelva por ejemplo, todos los paises ordenados por ventas descendentes.