¿Por qué usar una cross-table?

Porque podemos tener la información resumida y la información resumida es la que preferentemente nos solicitan los gerentes. A ellos no les suele interesar los detalles de cada venta, por ejemplo, sino los totales vendidos. Una cross-table sirve justamente para eso.

Si nos solicitan las ventas de cada producto en cada día del mes podríamos mostrar esa información usando la cláusula GROUP BY pero tendríamos muchas fechas repetidas, algo como:

Producto 1, fecha 1, total ventas del día 1
Producto 1, fecha 2, total ventas del día 2
Producto 1, fecha 3, total ventas del día 3
Producto 2, fecha 1, total ventas del día 1
Producto 2, fecha 2, total ventas del día 2
Producto 2, fecha 3, total ventas del día 3

etc.

Los nombres de los productos (Producto 1, Producto 2, etc.) se repiten y las fechas (fecha 1, fecha 2, fecha 3, etc.) también se repiten.

Si en la consulta deben mostrarse las ventas de 40 productos durante cada uno de los días del mes entonces tendríamos una consulta con 40 x 30 = 1.200 filas. Y si los productos fueran 500 entonces tendríamos 500 * 30 = 15.000 filas. Una exageración, nadie mira una consulta tan larga.

En cambio, si usamos una cross-table el nombre de cada producto se muestra una sola vez y cada fecha también es mostrada una sola vez. Para el caso anterior tendríamos 40 filas (una por cada producto) y 30 columnas (una para cada fecha del mes). Por lo tanto una cross-table es más parecida a una hoja de cálculo (una planilla Excel)

Captura 1. Si haces clic en la imagen la verás más grande

Como puedes ver en la Captura 1. cada fila representa a un producto (y el nombre de cada producto es mostrado una sola vez) y en cada columna se encuentra el total vendido en ese día (y cada fecha se muestra una sola vez).

Por lo tanto, es muy fácil visualizar las ventas de cada producto en cada uno de los días. Esto es mucho más comprensible que su equivalente usando la cláusula GROUP BY.

Conclusión:

Aunque Firebird no dispone nativamente de la opción de crear una cross-table es demasiado fácil crear una como vimos en este artículo:

https://firebird21.wordpress.com/2013/11/18/usando-una-cross-table/

y debemos aprovecharnos de esta herramienta para que nuestras aplicaciones sean más útiles.

Artículos relacionados:

Usando una cross-table

El índice del blog Firebird21

 

Usando una cross-table

En Firebird nativamente no tenemos la posibilidad de crear cross-tables pero es muy fácil simular una.

¿Qué es una cross-table?

Una tabla que puede ser leída de arriba hacia abajo o de izquierda a derecha y en ambos casos nos provee de información relevante. También se la conoce como pivot table y como tabla multidimensional.

Una tabla normal solamente se lee de arriba hacia abajo ya que si la leemos de izquierda a derecha nos estamos refiriendo siempre al mismo registro. En cambio en una cross-table podemos pensar que cada fila es un registro y también que cada columna es un registro.

¿Cuál es la ventaja de usar una cross-table?

Sumarizar y mostrar grandes cantidades de datos. Una cross table siempre tiene menos filas (generalmente muchísimas menos) que las tablas de donde se obtuvieron sus datos.

Ejemplo:

Queremos mostrar las ventas de cada producto que ocurrieron los días 15/02/2011, 16/02/2011 y 17/02/2011. Por supuesto que algunos productos pudieron haberse vendido muchas veces durante esos días, con Facturas diferentes, y algunos productos no haberse vendido.

Tenemos una tabla PRODUCTOS con los datos de los productos que nuestra empresa vende.

Captura 1. Si haces clic en la imagen la verás más grande

Tenemos una tabla MOVIMCAB (cabecera de movimientos) con estos datos, correspondientes a las ventas realizadas:

Captura 2. Si haces clic en la imagen la verás más grande

Y una tabla MOVIMDET (detalles de movimientos) con los datos de los productos vendidos:

Captura 3. Si haces clic en la imagen la verás más grande

Esta tabla tiene 6 filas porque cada una corresponde a una línea de una Factura. Si nos fijamos en la columna MOV_IDEPRD (identificador del producto) el número 13 está en 3 filas, lo cual significa que ese producto se vendió 3 veces.

Una consulta normal para saber los datos de los productos vendidos sería como ésta:

SELECT
   MVC_FECHAX,
   MVC_NRODOC,
   PRD_NOMBRE,
   MOV_CANTID,
   MOV_PRECIO,
   MOV_CANTID * MOV_PRECIO AS TOTALPRODUCTO
FROM
   MOVIMDET
JOIN
   MOVIMCAB
      ON MOV_IDECAB = MVC_IDENTI
JOIN
   PRODUCTOS
      ON MOV_IDEPRD = PRD_IDENTI

Y así obtendríamos este resultado:

Captura 4. Si haces clic en la imagen la verás más grande

O sea, 6 productos vendidos, 6 filas en el resultado. Si un producto se vendió más de una vez entonces se mostrará más de una vez. Podríamos agrupar esos datos por producto y fecha, como muestra la siguiente consulta:

SELECT
   MVC_FECHAX,
   PRD_NOMBRE,
   SUM(MOV_CANTID * MOV_PRECIO) AS TOTALPRODUCTO
FROM
   MOVIMDET
JOIN
   MOVIMCAB
      ON MOV_IDECAB = MVC_IDENTI
JOIN
   PRODUCTOS
      ON MOV_IDEPRD = PRD_IDENTI
GROUP BY
   2, 1

y éste sería el resultado:

Captura 5. Si haces clic en la imagen la verás más grande

Pero el día 15/02/2011 se vendieron 4 productos distintos y por lo tanto hay 4 filas. Y el producto COCA COLA 350 C.C. se vendió en los dos días y por lo tanto está repetido. Podríamos agregarle la cláusula HAVING a la consulta anterior pero en ese caso las filas estarían agrupadas por fechas o por nombres de los productos y tampoco obtendríamos lo que estamos buscando.

¿Cuál es la solución?

Usar una cross-table.

WITH VENTASDIA AS (
   SELECT
      MOV_IDEPRD,
      MVC_FECHAX,
      SUM(MOV_CANTID * MOV_PRECIO) AS TOTALPRODUCTO
   FROM
      MOVIMDET
   JOIN
      MOVIMCAB
         ON MOV_IDECAB = MVC_IDENTI
   GROUP BY
      1, 2
)

SELECT
   PRD_IDENTI,
   PRD_NOMBRE,
   V1.TOTALPRODUCTO AS "15/02/2011",
   V2.TOTALPRODUCTO AS "16/02/2011",
   V3.TOTALPRODUCTO AS "17/02/2011"
FROM
   PRODUCTOS
LEFT JOIN
   VENTASDIA V1
      ON PRD_IDENTI = V1.MOV_IDEPRD AND
         V1.MVC_FECHAX = '02/15/2011'
LEFT JOIN
   VENTASDIA V2
      ON PRD_IDENTI = V2.MOV_IDEPRD AND
         V2.MVC_FECHAX = '02/16/2011'
LEFT JOIN
   VENTASDIA V3
      ON PRD_IDENTI = V3.MOV_IDEPRD AND
         V3.MVC_FECHAX = '02/17/2011'
WHERE
   V1.TOTALPRODUCTO IS NOT NULL OR
   V2.TOTALPRODUCTO IS NOT NULL OR
   V3.TOTALPRODUCTO IS NOT NULL

Al ejecutar esa consulta, éste será el resultado:

Captura 6. Si haces clic en la imagen la verás más grande

Conclusión:

Las cross-tables pueden ser extremadamente útiles cuando necesitamos mostrar datos sumarizados porque la información que contienen está sumarizada tanto si se lee de arriba hacia abajo como si se lee de izquierda a derecha. Al leer de izquierda a derecha generalmente el valor de las columnas es obtenido a través de alguna función agregada (SUM(), MAX(), MIN(), COUNT(), AVG() aunque por supuesto no siempre debe ser así).

Artículos relacionados:

Tablas agregadas

El índice del blog Firebird21

Tablas agregadas

Antes de continuar leyendo este artículo es altamente recomendable que leas:

https://firebird21.wordpress.com/2013/04/27/aplicaciones-oltp-y-aplicaciones-olap/

Si no leíste el artículo de arriba, el actual no tendrá tanto valor.

¿Qué es una tabla agregada?

Es una tabla que no guarda datos en bruto sino datos agrupados. Por ejemplo, en una tabla agregada no se guardan los datos de cada venta realizada sino los totales (diarios/semanales/mensuales/anuales) de esas ventas

¿Para qué se usan las tablas agregadas?

Para que los usuarios puedan obtener los resultados de sus consultas muy rápidamente

¿Cómo se insertan datos a las tablas agregadas?

A través de un programa que realiza un proceso de ETL (Extraction, Transformation, Loading, o en castellano: Extracción, Transformación, Carga)

Primero, se extraen los datos. Eso normalmente implica escribir un SELECT que contiene las funciones agrupadas: COUNT(), SUM(), AVG(), MAX(), MIN()

Segundo, se realizan algunas operaciones con esos datos (si es necesario, claro)

Tercero, se insertan dichos datos en la tabla agregada, la cual se encuentra en otra Base de Datos

¿Cuál es la principal tarea de una tabla agregada?

Guardar los resultados obtenidos al consultar otra/s tabla/s pero en muchísimas menos filas, por lo tanto las consultas a la tabla agregada serán mucho más rápidas que la respectiva consulta a la/s tabla/s original/es

¿Cómo se nombra a las tablas agregadas?

Aunque no es obligatorio hacerlo así, lo normal es que empiecen con el prefijo AGG_ para que todos los programadores puedan saber que se trata de una tabla agregada. Por ejemplo: AGG_VENTAS_MENSUALES, AGG_VENTAS_POR_VENDEDOR

 ¿Se deben usar tablas agregadas para todas las tablas de una Base de Datos OLTP?

No, solamente se justifica hacer el trabajo cuando las tablas OLTP tienen cientos de miles o millones de filas. Si las tablas OLTP tienen pocos miles de filas no se justifica el esfuerzo

¿Qué importante consideración debemos tener siempre en mente?

Que para que las tablas agregadas sean útiles deben tener sus filas actualizadas con los de la tabla (o tablas) OLTP correspondientes. Cada vez que los datos de una tabla OLTP son cambiados (porque se agregaron/borraron/modificaron filas) la tabla agregada relacionada con esa tabla OLTP estará automáticamente desactualizada. Esto implica que consultando a la tabla agregada se podría obtener un resultado diferente que consultando a la tabla OLTP. Y eso muchas veces no es admisible.

¿Cuáles son las estrategias para insertar, borrar o actualizar datos en una tabla agregada?

 En la literatura sobre OLAP y Data Warehousing se nombran tres estrategias:

• • Snapshot
  • Eager
  • Lazy

En snapshot (instantánea, en castellano) los datos de la tabla agregada son totalmente borrados y luego se les insertan los nuevos datos. O sea que cada vez se agregan los datos desde cero. Implementar esta estrategia es muy simple pero tiene un gran problema: la utilización del Servidor durante el tiempo que dura la inserción de las filas será muy intensiva haciendo que el tráfico en la red se vuelva muy lento para las demás aplicaciones. Por ese motivo se lo suele realizar en los horarios en que menos usuarios están conectados a las bases de datos.

En eager (ansioso, en castellano) hay un trigger para cada operación de INSERT/DELETE/UPDATE en la tabla OLTP. Cada vez que en la tabla OLTP se realiza un DELETE o un UPDATE dicho trigger debe recalcular los valores que pondrá en la tabla agregada. Esto significa que no se agregarán los datos desde cero como se hace en snapshot pero implementar esta estrategia es más complicado. La ventaja de usar esta estrategia es que la tabla agregada siempre está actualizada, la desventaja es que las operaciones en la tabla OLTP son más lentas porque cada INSERT/DELETE/UPDATE en ella debe actualizar a la tabla agregada también.

En lazy (perezozo, en castellano) hay un trigger en la tabla OLTP que guarda los cambios en otra tabla, en una tabla separada. Cada una de las filas de la tabla separada tiene una columna que nos indica si ya fue procesada o aún no. Periódicamente un stored procedure lee el contenido de la tabla separada y por cada fila que aún no fue procesada realiza el eager y marca a la fila como ya procesada. Esto implica que al igual que en la estrategia eager se necesita de operaciones de escritura pero la actualización se puede realizar en un mejor momento, normalmente cuando hay pocos usuarios conectados. Esta estrategia es una mezcla de las dos anteriores: no se satura al Servidor porque nunca se procesan todas las filas de la tabla OLTP y las filas de la tabla agregada están casi, casi, actualizadas con los de la tabla OLTP.

Conclusión:

Usar tablas agregadas es muy útil para aumentar la velocidad de respuesta a las consultas. En Firebird las podemos implementar utilizando stored procedures y triggers. También podemos utilizar cualquiera de las tres estrategias (snapshot, eager, lazy) mencionadas más arriba. Por supuesto que tendremos datos redundantes pero en general no deberíamos tener problemas por eso.

Aplicaciones OLTP y aplicaciones OLAP

Las aplicaciones que normalmente desarrollamos son del tipo OLTP (On-Line Transaction Processing o en castellano: Procesamiento de Transacciones en Línea)

En este tipo de aplicaciones se insertan/borran/modifican datos en gran cantidad y más raramente se los consulta.

Como ejemplos tenemos: contabilidad, ventas, facturación, sueldos, producción

En todos esos casos lo normal es que se inserte una fila (un registro) por cada «movimiento» ocurrido. ¿Vendimos algo? insertamos una fila en la tabla de ventas. ¿Pagamos sueldo a un empleado? insertamos una fila en la tabla de pagos de sueldos.

Eso está muy bien y es lo correcto. Cuando alguien desea ver lo que se vendió o los sueldos que se pagaron con un SELECT se soluciona.

Sin embargo, empieza a complicarse cuando las consultas son muchas, ocurren muy frecuentemente y las filas que deben ser procesadas no se cuentan por miles sino por millones, por muchos millones.

Imagínate una empresa grande, con sucursales en varios países, que el año pasado ha realizado 500.000.000 de ventas o sea que  tiene 500.000.000 de Facturas en un solo año.

Si nuestra aplicación es OLTP entonces cada vez que un usuario desea realizar una consulta a la tabla de ventas tendrán que procesarse 500.000.000 de filas, lo cual es una exageración y por supuesto que será muy lento. Dependiendo de la complejidad de lo que el usuario desee y de la velocidad de conexión, tal consulta podría insumir horas, lo cual es totalmente impracticable.

¿Cuál es la solución?

Construir aplicaciones OLAP.

¿Qué significa OLAP?

Son las siglas de On-Line Analytic Processing o en castellano: Procesamiento Analítico en Línea.

¿Para qué se usa OLAP?

Para consultar los datos que las aplicaciones OLTP guardaron en las bases de datos, las cuales se han vuelto inmensas porque algunas de sus tablas tienen muchos millones de filas.

¿Eso significa que hay relación entre las aplicaciones OLTP y las aplicaciones OLAP?

Sí, el paradigma funciona de la siguiente manera:

• • Una aplicación OLTP se encarga de insertar/borrar/modificar las filas que se guardarán en las tablas de una Base de Datos OLTP
  • Un programa agrupa esas filas y el resultado lo guarda en una Base de Datos OLAP
  • Cuando la consulta de un usuario envuelve a tablas pequeñas (de pocos miles de filas) se utiliza la Base de Datos OLTP, en cambio cuando envuelve a tablas que en la Base de Datos OLTP tienen millones de filas, se usa la Base de Datos OLAP

¿Qué tienen de especial las tablas OLAP?

Que no guardan datos individuales sino datos agrupados. En nuestro ejemplo, la tabla de ventas en una Base de Datos OLTP tenía 500.000.000 de filas. Supongamos que un Gerente necesite consultar las ventas del año pasado, clasificadas por mes (o sea: cuanto se vendió en enero, cuanto se vendió en febrero, etc.)

Siendo así, podríamos tener una tabla llamada AGG_VENTAS con las siguientes columnas:

• Año
• Mes
• Sucursal
• CantidadFilas
• SumaVentasDelMes
• MaximaVenta
• MinimaVenta

Supongamos ahora que esta empresa tiene 3.000 sucursales

Como habrá una fila por cada mes y por cada sucursal, tendremos 12 meses por 3.000 sucursales, lo cual nos da un total de 36.000 filas

Por lo tanto, todas las ventas del año pasado las tenemos registradas en solamente 36.000 filas y cualquier SELECT a esa tabla será muchísimo más rápido que un SELECT equivalente a la tabla de 500.000.000 de filas.

Recuerda que las filas de las tablas de una Base de Datos OLAP siempre tienen datos agrupados que provienen de las tablas de una Base de Datos OLTP.

Eso significa que no puedes construir una aplicación OLAP (que sí o sí debería usar una Base de Datos OLAP), si no tienes una Base de Datos OLTP.

¿Puedo construir aplicaciones OLAP con Firebird?

Sí, aunque nativamente el Firebird carece de las herramientas que otros SGBDR como Oracle o DB2 proveen, es perfectamente posible construir aplicaciones OLAP usando Firebird.

¿Qué estructuras usan las aplicaciones OLAP?

Para poder agilizar las consultas de grandes cantidades de datos se utilizan estructuras multidimensionales (las tablas de Firebird son bi-dimensionales) llamadas cubos OLAP que contienen los datos resumidos de grandes bases de datos OLTP.

¿Cuál es la principal razón de usar aplicaciones OLAP?

La gran velocidad de respuesta que se consigue.

¿Por qué se usan dos bases de datos, una para OLTP y otra para OLAP?

Aunque las tablas agrupadas podrían estar dentro de una Base de Datos OLTP eso no se hace así por las siguientes razones:

1. 1. La cantidad de conexiones concurrentes suele ser muy distinta. Mientras a una Base de Datos OLTP podrían estar conectados miles de usuarios a una Base de Datos OLAP generalmente no se conectan más que unas decenas de usuarios
   2. En las aplicaciones OLAP lo que se busca es conseguir la máxima velocidad, por lo tanto cuanto más pequeña sea la Base de Datos, mucho mejor. Las bases de datos OLTP equivalentes son inmensas
   3. La configuración también es distinta porque quienes se conectan a bases de datos OLAP generalmente disponen de computadoras muy rápidas (no te olvides que suelen ser gerentes, presidentes, etc.) en cambio a las bases de datos OLTP podrían conectarse con computadoras lentas

¿Vale la pena construir aplicaciones OLAP?

Si quieres ganar mucho dinero la respuesta es un rotundo sí. Las aplicaciones OLAP se venden en muchos miles de dólares porque quienes compran esta clase de aplicaciones son empresas grandes.

¿Qué debe tener una aplicación OLAP?

Las aplicaciones OLAP se usan para ayudarles a los usuarios en la toma de decisiones. Por lo tanto es imprescindible que le provean de muchísimos gráficos. También la posibilidad de exportar sus datos a Excel, a PDF, a HTML y a cualquier otro formato que el usuario use en sus demás aplicaciones. Por supuesto muchas grillas y lo más importante: la facilidad de buscar y visualizar cualquier dato que precise, en cuestión de segundos.

Conclusión:

Las aplicaciones OLTP fueron construídas desde los inicios de la Informática y siempre existirán y siempre serán muy útiles pero las empresas grandes actualmente tienen tablas con muchos millones de filas y necesitan gran rapidez en las consultas, y esa rapidez no la pueden obtener con aplicaciones OLTP. La rapidez que necesitan solamente la pueden conseguir con aplicaciones OLAP. Como quienes compran estas aplicaciones son empresas grandes, pagan muy buen dinero por ellas.

Con Firebird se pueden desarrollar aplicaciones OLAP y en siguientes artículos veremos como.