PIENSO-PIENSO: Como modelar datos acumulados
Ventas
* Id
Fecha
Cliente
(*Producto
ImporteVenta
)
Esta tabla tiene millones de registros y no están uniformemente distribuidos (algunos clientes compran mucho y otros muy poco).
Se necesitan hacer consultas por
Mes
Trimestre
Año
Producto
Cliente
y cualquier combinación de ellas.
Por ejemplo consultas validas son:
Cuales son las ventas del cliente X en el Año Y?
Cuales son las ventas del cliente X en el mes D del producto Z?
Todas las consultas posibles deben ejecutarse en menos de 1 segundo.
1) Cual seria su modelo de datos para resolver dichas consultas?
2) Si se agrega la dimensión Pais, como se ve afectado el modelo de datos?
El mejor diseño, se gana una tableta de chocolate amargo Lindt de 300gr. Si no le gusta el chocolate amargo, puede ser una de chocolate con leche.
Si el ganador quiere se lo doy en el Encuentro GeneXus. Sino se lo hago llegar a su casa. Si es en el exterior, vemos como hacemos.
Aclaracion: Los datos se pueden actualizar un vez al mes, cuando se termina ese periodo. Va a ser consultado muchas veces por muchos usuarios.
Aclaracion: Los datos se pueden actualizar un vez al mes, cuando se termina ese periodo. Va a ser consultado muchas veces por muchos usuarios.
;)
ResponderBorrarPreguntas: (de cuyas respuestas puede depender el diseño)
Que tamaño tiene el dominio de paises, de productos y clientes? (de que orden es?)
Es un dominio mas o menos estable o crece muy rápidamente?
pd. me gusta el choco amargo ;)
Paises son unos cientos
ResponderBorrarProductos son unas decenas de miles
Empresas son cientos de miles
El dominio es muy estable, pues son todos los paises del mundo, los productos estan definidos internacionalmente a 10 digitos y los clientes son las empresas que importan/exportan en un pais.
Esto es la realidad de un sistema aduanero. :)
Se me ocurre algo asi:
ResponderBorrarventas(ventasid*,fecha,clienteid)
ventaslineas(ventasid*,productoid*,importeventa) (es el 2do nivel de ventas, esto está dado)
acumulado(clienteid*,productoid*,fecha*,importeacumulado)
Cada vez que damos de alta una linea, buscamos el acumulado mas reciente
y generamos un registro acumulado nuevo para esa linea:
GuardarAcumuladoAUnaFecha:
parm(&ProductoId,&fecha,&ImporteVenta)
for each (fecha)
where fecha<=&fecha
where ProductoId=&ProductoId
new
importeacumulado += &ImporteVenta
endnew
exit
endfor
De esta forma tengo el historico de cada cliente/producto.
Luego, para la dimension Pais le agrego el PaisId como parte de la clave a
ventas
acumulado
será la idea????
saludos
Bruno
Una opción seria:
ResponderBorrarId*
Fecha
Cliente
Producto
ImporteVenta
Listo, simple, la más fácil de mantener, poco performante. Si ya se… No te sirve ;)
Para tener performance, hay tener agregaciones ya calculadas.
¿Como definir que agregaciones? No podemos tener todas las combinaciones posibles (porque son demasiadas, que es lo que nos daría la performance óptima)
¿Que agregaciones elegir?
Aquellas que me me den buena performance la gran mayoría de las consultas.
¿Como saberlo?
Una técnica (no es deterministica) es la siguiente:
Primero analizamos esta tabla:
Dimensión Dominio (bajo, medio, alto) Probabilidad de consulta a este nivel
Año bajo alto
Mes bajo alto
Producto alto alto
Empresas alto bajo
En la probabilidad de consultas, también es necesario tener la probabilidad de las combinaciones.
Y las reglas eran algo asi:
Si una dimensión A tienen dominio alto y una B tienen dominio bajo, y son frecuentes las consultas por B, sin que intervenga A entonces debemos tener agregaciones por B que no incluyan a A.
Si dos dimensiones A y B son de dominio alto, pero las consultas que las involucran siempre instancian (en un único valor a A y B) entonces pueden ir juntas.
Si dos dimensiones A y B son de dominio alto, y existen consultas solo por una de ellas (sin instanciar a la otra), entonces deberemos tener una agregación por A y que No tenga a B.
Son todas reglas que salen con sentido común, pero hay algo de material sobre esto (no me acuerdo donde…)
Como el diseño depende de los dominios de las dimensiones (esto se puede saber con cierta certeza a priori) pero además de los tipos de consultas que se hacen, entonces una cosa útil es tener estadísticas de las consultas (dimensiones que se usan y combinaciones que se dan) para validar la estructura, y en todo caso reorganizarla (esto no es nada barato)
No se si hay otras técnicas , yo hace tiempo que no hago esto , pero en una época asi lo hacíamos..
No se si me expliqué muy bien…
Esta podria ser una opción para el ejemplo (los * son llaves candidatas, es mas practico que tengan una surrogate key) Es medio embole porque son muchas tablas, pero si queres buena performance…
Año*
Mes*
Ventas
Año*
Mes*
Producto*
Ventas
Año*
Mes*
Clientes*
Ventas
Año*
Mes*
Producto*
Cliente*
Ventas.
al agregar Pais, se complica mas. Aunque si país tiene dominio medio.chico, capaz que las poder meter en las 4 tablas, y te anda OK. Y no tenes que hacer otras tablas para contemplar la agregación de país.
Otra forma es tener una única tabla física, y manejar con lógica las agregaciones (son esas cosas chanchas pero practicas) Por ejemplo, si el mes = 0, es que es una agregación por mes. En este caso, la tabla es grande, necesitas lógica para hacer las consultas (saber que cuando el mes es 0, es una agregación y no lo podes sumar con otros meses, porque duplicas) , pero con buenos índices quizás funque bien y te ahorras de tener N tablas con la complejidad que eso tiene.
Se puede hacer un algoritmo (o quizás haya…) para con los datos de dominios, y de combinación de consultas, frecuencias, te dé algunas opciones.No parece difícil de hacer. (bah, no parece asi de lejos..)
Siempre lo quisimos hacer, nunca cuadró.
Saludos!
Mayda.
Simplemente móntate un GXPlorer y consulta luego contra el Datawarehouse. ;-)
ResponderBorrarMencionaste algo que te resuelva rápido las consultas, no mencionaste ni que fuera online, ni algo fácil y simple de mantener ;)
El diseño de la tabla lo terminaría haciendo GXPlorer, si te gusta el mismo, envíale el chocolate a sus desarrolladores. :)
Hablando en serio, yo me voy a una implementación con redundancia, simulando algo muy similar a lo que se haría en GXPlorer.
Tendría un único punto de ABM a efectos de controlar la sincronización de la redundancia, sumando o restando de los acumulados que necesito dependiendo de la operación.
¿Cuál es la mejor estructura?
Eso depende en realidad de las consultas, si nos vamos a los casos de ejemplo, lo mejor sería partir la información de la siguiente forma.
Como tienes consultas por Mes, Trimestre, Año, en el fondo todo lo puedes resolver con una combinatoria AÑO/MES con la combinatoria PRODUCTO, otra CLIENTE y otra PRODUCTO CLIENTE
* AÑO
* MES
* PRODUCTO
VALOR
* AÑO
* MES
* CLIENTE
VALOR
* AÑO
* MES
* PRODUCTO
* CLIENTE
VALOR
Los trimestres lo puedes resolver haciendo una consulta sobre los meses que quieres resolver.
Yo no soy experto en fórmulas, pero en una de esas puede resolverse todo con ellas, sin embargo me queda duda de que pueda hacerse con una estructura similar.
La transacción sería así
* AÑO
* MES
(*Producto
ValorP
(*Cliente
ValorPC)
(*Cliente
ValorC)
Ahora me queda una duda, cuando mencionas "Ventas" si te refieres al ID de ventas o al "Total de Ventas"
Si lo que quieres es tener identificado los ID's, pero también quieres tener los Totales por Cliente Producto, prueba lo siguiente
* AÑO
* MES
(*Producto
ValorP
(*Cliente
ValorPC
(*PCVID
ValorPCID
)
)
(*PVID
ValorPID
)
(*Cliente
ValorC
(*CVID
ValorCID
)
)
Puede que logres tenerlo Online, ya que son operaciones matemáticas sobre los acumulados, y para el caso de los valores, en base a validación de existencia o no se incorporan o eliminan (o modifican si es que puede darse la modificación de un valor de una Venta X).
No conozco todas las posibles consultas que quieras resolver, pero si todas van hasta nivel MES/AÑO con un cliente o con un Producto podrías resolverlo con lo anterior.
Y si el tiempo no te importa y quieres cambiar el modelo de consulta o ampliarlo, puedes ver que se podría hacer con un Datawarehouse como GXPlorer.
PD: Perdón, pero el Blogger hace perder los espacios, por lo que imagina la tabulación de los niveles)
Suponiendo que no tenés restricciones en cuanto a la performance de la escritura, ni tampoco en el tamaño, yo diría que:
ResponderBorrarUna sola tabla con {mes, trimestre, año, producto, cliente, importe}, la clave primaria son todas las dimensiones.
Para los acumulados, instancias solo la(s) dimensión(es) que intervienen y dejás todas las demás en nullvalue.
Por ejemplo, las ventas del 2010, sería: {0, 0, 2010, '', '', importe}
Las ventas de software de agosto 2010, sería {8, 0, 2010, "software", "", importe}
Igual tu modelo es medio raro... porque si ponés el mes no deberías poner el trimestre y viceversa. O podés poner solo el mes y calcular para el trimestre cada vez que se pida el dato, si no es demasiado pesado.
De esa forma, siempre las consultas son por clave primaria, tienen que ir a buscar un solo registro.
Para agregar el país, tenés dos posibilidades:
1. lo agregás solo a futuro => es muy fácil, simplemente agregas la columna y cambiás los programas para que lo graben
2. lo querés agregar para todos los datos históricos => no tenés más remedio que recalcular todo.
Bruno:
ResponderBorrarGracias por el aporte.
No veo como poder garantizar que la consulta
Todas las ventas de Producto X (cualquier año, cualquier empresa)
pueda hacerse en menos de un segundo, pues puede haber muchos registros.
Desde mi punto de vista necesita un poco mas de elaboración.
Me gusta la solución de Marcos.
ResponderBorrarRespecto al tema años/trimestres/meses, concuerdo con Marcos en que se podría descartar el trimestre y calcularlo al momento de hacer la consulta (es sumar 3 registros).
Otra cosa que podés hacer es agregarle una columna de trimestre pero que no sea parte de la clave, solo para poder filtrar por trimestre.
Con el mismo criterio, los acumulados por año podrían no ser necesarios, y en ese caso sumás 12 registros, para saber las ventas del año. Aunque no te va a hacer una diferencia significativa en el tamaño de la tabla.
Por último, se me ocurre que un modelo de este tipo podría no ser práctico para hacer consultas del estilo:
* las ventas por cliente del producto X en el trimestre 1 del año 2010.
ya que tendrías que tener los índices adecuados para hacer un corte de control y así sumar los registros del mismo trimestre.
Mayda:
ResponderBorrarEsta bueno eso de plantear tres posibles soluciones en una respuesta, pues te acerca mucho mas a los premios.
Las tres soluciones son buenas para diferentes dimensiones de problemas.
La solucion tipo 1) como vos decis no sirve.
La solucion 2) es la recomendada por la metodologia GXPlorer, esta bien, pero tampoco garantiza que TODAS las consultas se respondan rapido.
La solucion 3) (la de tabla unica) estaria bueno que la expliques un poco mas, pues no me quedo muy clara.
Gracias por el aporte.
David:
ResponderBorrarGxplorer no genera los acumulados en forma automagica. Hay que hacer el diseño de las tablas y los programas de carga. El diseño de dichas tablas es lo que esta pidiendo el problema.
La solucion que planteas es parecida a la de Mayda y creo que sirve para muchas consultas.
Creo que no contestaste como quedaria si agregamos la dimension Pais.
Muchas gracias por tu respuesta.
Marcos:
ResponderBorrarNo lo aclare en la letra, pero no hay problemas de espacio, ni en el tiempo del programa de carga.
En cuanto a tu comentario sobre que el modelo es medio raro, es el problema que tiene la realidad, que muchas veces nos presenta problemas raros.
El problema real, tiene dia/mes/trimestre/Semestre/a#o. No quise complicarla tanto.
Creo que tu modelo se puede simplificar en algo para contemplar esta "desprolijidad".
Hasta el momento, tu modelo es el que mas me gusta.
Gracias por el comentario.
Pablo:
ResponderBorrarVos corres con ventaja!.
Las consultas que hay que optimizar son de la forma que tienen que devolver 1 unico valor.
Total de ventas de Producto X en Año A.
Total de ventas al Pais X en Trimestre T.
Gracias por el aporte.
Hola
ResponderBorrarHacer redundante el tema de la fecha,cliente,Pais(si es necesario)
Luego generar los indices por ejemplo
Cliente,Fecha,Producto
Fecha,Cliente,Producto
Producto,Cliente,Fecha, etc,
Luego resuelves el tema de las consultas con sdt, donde importa
como obtienes los datos
For each, indices, luego con sdt te preocupas de armar el tema
Cliente X Año Y, Producto por Cliente, etc.
Eso,
Saludos
Leonardo Zepeda A.
Enrique: La solución de la única tabla que puse al final es:
ResponderBorrarTener una única tabla con todas las dimensiones posibles.
(d1, d2, d3... dn, ventas)
Si un registro de esa tabla, tiene di=0, implica que el valor de ls ventas corresponde al acumulado para todos lo valores de di.
Creo que es la misma que planteó Marcos
En cualquier caso primero hay que definir qué agregaciones vas a mantener (a menos que decidas que sean todas las combinaciones, en cuyo caso puede ser gigante esa tabla, no?), luego la diferencia está si las materializas en N tablas o en 1.
Otra cosa a considerar es particionar por "datos" el año corriente en una tabla, otros en otra...
Marcos y Mayda se ganaron los chocolates.
ResponderBorrarAmbos van al evento, no? Se los doy ahi.
Me hubiese gustado que participaran gente que usa otras herramientas para consultas de análisis multidimensional o base de datos orientadas a columnas, para aprender como lo soluciona ellso.
Mi blog no tiene una difusión tan amplia (todavia) :).
Mi propuesta de solución (muy mejorable) quedo en otro post http://ealmeida.blogspot.com/2010/09/pienso-pienso-como-modelar-datos.html).