SQL sin repeticiones (y 5)

Crono
miércoles, 19 de abril de 2017

Toda tecnología suficientemente avanzada es indistinguible de la magia

Introducción

Termino hoy, por fin, esta serie de 5 artículos. En los anteriores artículos criticaba la verbosidad del SQL y proponía Crono SQL como un lenguaje sin estas deficiencias:

En el último artículo mostraba que gracias a Crono SQL podíamos eliminar los GROUP BY y simplificar el SELECT, pero quedó pendiente resolver la repetición más grave de todas. ¿Por qué debemos escribir una y otra vez los JOIN en cada consulta que realicemos sobre el mismo modelo de datos? ¿Acaso el modelo de datos no es algo básicamente estático y que no cambiará entre consulta y consulta?

En nuestro caso, tenemos un modelo de Internet Sales y otro de Reseller Sales. Son los mismos diagramas que mostramos al inicio de esta serie:

Adventure Works 2008

Sin más préambulos, vamos a ver como se escribirían las consultas que nos traemos entre manos...

Ejemplo 1

En el primer ejemplo, necesitábamos “el detalle de las bicicletas vendidas en enero de 2008, ordenadas en función de su precio”. Esta es la consulta equivalente en Crono SQL:

--Ejemplo 1
SELECT
  DimProduct.EnglishProductName AS Producto,
  sum(sales.OrderQuantity) AS Unidades,
  sum(sales.SalesAmount) AS Importe,
  Importe/Unidades AS [Precio Medio]
FROM DATABASE 'http://bit.ly/2oP72d6'
WHERE
  DimProductCategory.SpanishProductCategoryName='Bicicleta'
  AND DimDate.SpanishMonthName='Enero'
  AND DimDate.CalendarYear=2008
ORDER BY [Precio Medio] DESC 

Miremos de nuevo la consulta, ¿Dónde están los JOIN? ¡No están!

Ejemplo 2

La segunda consulta era acerca de "las bicicletas vendidas en Alemania durante el 2008". Tachán:

--Ejemplo 2
SELECT
  DimProduct.EnglishProductName AS EnglishProductName,
  sum(sales.OrderQuantity) AS Unidades
FROM DATABASE 'http://bit.ly/2oP72d6'
WHERE
  DimGeography.SpanishCountryRegionName='Alemania'
  AND DimProductCategory.SpanishProductCategoryName='Bicicleta'
  AND DimDate.CalendarYear=2008

Ejemplo 3

El tercer ejemplo era "el total de unidades vendidas en 2008 por países". También, sin rastro de los JOIN:

--Ejemplo 3  
SELECT
  DimGeography.SpanishCountryRegionName AS SpanishCountryRegionName,
  sum(sales.OrderQuantity) AS Unidades
FROM DATABASE 'http://bit.ly/2oP72d6'
WHERE DimDate.CalendarYear=2008
GROUP BY DimGeography.SpanishCountryRegionName  

Observad en el SQL generado que Crono SQL selecciona las tablas y las relaciones necesarias en cada caso (¡y solo esas!)

Ejemplo 4

El cuarto ejemplo es más espectacular. Nos pedían "el detalle de las bicicletas vendidas en enero de 2008 y en enero del 2007". Como comentamos previamente, el especificación del lenguaje SQL no es especialmente brillante a la hora de realizar operaciones entre filas. En cambio, con Crono SQL es posible aplicar filtros distintos en cada columna de la consulta, y operar entre ellos... Esta es la consulta escrita en lenguaje Crono SQL:

--Ejemplo 4
SELECT
   DimProduct.EnglishProductName AS EnglishProductName,
   sum(sales.OrderQuantity) WHERE (DimDate.CalendarYear=2008) [Ventas 2008],
   sum(sales.OrderQuantity) WHERE (DimDate.CalendarYear=2007) [Ventas 2007],
   [Ventas 2008]-[Ventas 2007] Diferencia
FROM DATABASE 'http://bit.ly/2oP72d6'
WHERE
  DimProductCategory.SpanishProductCategoryName='Bicicleta' 
  AND DimDate.SpanishMonthName='Enero'

Como se muestra en la anterior consulta, Crono SQL permite incluir predicados WHERE a nivel de columna. Tampoco es necesario incluir las tablas ni los JOIN de la query (o queries, en este caso).

Ejemplo 5

El quinto y último ejemplo era una consulta entre distintas tablas de hecho. Queríamos "comparar o sumar las ventas de internet con las ventas en tiendas". El lenguaje SQL, en este tipo de casos, te obliga a realizar dos consultas independientes y unirlas de algún modo. Crono SQL, en cambio, incluye la inteligencia para reconocer la necesidad de utilizar distintas tablas de hechos, y unirlas en un único resultado. Así de fácil:

-- Ejemplo 5
SELECT
  DimDate.CalendarYear AS CalendarYear,
  sum(isales.SalesAmount) AS ventasInternet,
  sum(sales.SalesAmount) AS ventasReseller,
  ventasInternet+ventasReseller VentasTotales
FROM DATABASE 'http://bit.ly/2oP72d6'
WHERE DimGeography.SpanishCountryRegionName='Francia'

No busques ahora tampoco ni los JOIN ni los GROUP BY.

En todos estos ejemplos, pulsando el botoncito azul, podéis ver el código SQL generado. ¿Es magia o no es magia? :-)

Juan Tamatiz tocando el violín

Hemos visto en estos 5 ejemplos como es posible escribir consultas complejas sin incurrir en las redundancias típicas del SQL. Lo único que hemos hecho ha sido pasar la segadora por encima de cada una de las repeticiones que encontrábamos en el código SQL.

Conclusiones

Crono SQL es un lenguaje de programación basado en SQL, pero que añade algunas variantes para evitar las repeticiones de código. Crono permite crear la "capa semántica" de tu DWH o de tu ERP, y luego aprovecharla para generar informes dinámicos o para crear procesos ETL.

Lo que hemos hecho es "desacoplar" el potente motor generador de consultas que siempre ha caracterizado a Crono Analysis para poder aprovecharlo en otros usos (ya sea crear informes HTML, o procesos ETL...). Por el camino, además, hemos enriquecido el lenguaje para permitir la manipulación de datos (INSERT, UPDATE, MERGE...). Tenemos, ahora sí, un lenguaje DRY apropiado para implementar proyectos de la envergadura de un Data Warehouse corporativo.

Don't repeat yourself

El resultado es algo revolucionario, ya que nos permite implementar proyectos ETL/DWH con el mejor rendimiento y con una fracción del esfuerzo requerido con otros acercamientos tradicionales. Crono ya no es solo Crono Analysis. Ahora es una completa suite de Business Intelligence que abarca desde la construcción del DWH hasta la explotación por parte del usuario. Si miras nuestra web businessintelligence.es verás que la solución se compone de:

  • Crono ETL (para el diseño y construcción del DWH)
  • Crono Metadata (para la definición de la capa semántica)
  • Crono Analysis (para que el usuario de negocio pueda hacer informes, análisis y autoservicio)
  • Crono Excel (para los hooligans más incondicionales del Excel)

Pero eso serán motivos para nuevos artículos.

Mientras tanto, si queréis seguir descubriendo Crono SQL podéis consultar la documentación en la web de soporte de Crono:

Recuerda que podéis apuntaros a nuestra newletter sobre Business Intelligence o seguirnos en redes sociales: