PIENSO-PIENSO: Como modelar datos acumulados

Tengo la tabla con las ventas de mi empresa que tiene la estructura

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. 

Comentarios

  1. ;)
    Preguntas: (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 ;)

    ResponderEliminar
  2. Paises son unos cientos
    Productos 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. :)

    ResponderEliminar
  3. Se me ocurre algo asi:

    ventas(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

    ResponderEliminar
  4. Una opción seria:

    Id*
    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.

    ResponderEliminar
  5. Simplemente móntate un GXPlorer y consulta luego contra el Datawarehouse. ;-)

    Mencionaste 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)

    ResponderEliminar
  6. Suponiendo que no tenés restricciones en cuanto a la performance de la escritura, ni tampoco en el tamaño, yo diría que:

    Una 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.

    ResponderEliminar
  7. Bruno:
    Gracias 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.

    ResponderEliminar
  8. Me gusta la solución de Marcos.

    Respecto 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.

    ResponderEliminar
  9. Mayda:
    Esta 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.

    ResponderEliminar
  10. David:
    Gxplorer 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.

    ResponderEliminar
  11. Marcos:
    No 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.

    ResponderEliminar
  12. Pablo:
    Vos 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.

    ResponderEliminar
  13. Hola

    Hacer 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.

    ResponderEliminar
  14. Enrique: La solución de la única tabla que puse al final es:

    Tener 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...

    ResponderEliminar
  15. Marcos y Mayda se ganaron los chocolates.
    Ambos 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).

    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