4 Aggregation Designer, MDX y SQL3

En esta actividad vamos a trabajar sobre diseños multidimensionales para Mondrian implementados sobre BD Relacionales, desarrollados en las actividades anteriores.

Objetivos del capítulo

  • Formular consultas mediante MDX.

  • Formular consultas mediante SQL3.

  • Usar una herramienta de definición de agregados.

4.1 Punto de partida

Tenemos dos BD OLAP en PostgreSQL:

  • Ventas, a nivel de día y línea de ticket.

  • Padrón Municipal de Habitantes: cada persona con los datos de la provincia asignada.

En la actividad anterior hemos definido el esquema multidimensional para cada una de ellas (archivos en formato XML).

4.2 Vídeo de soporte

En el vídeo https://youtu.be/yWYoNV00NM0 se muestra cómo realizar las operaciones siguientes:

4.2.1 Consultas MDX

Importe total por año

SELECT
  {[Measures].[Importe]} ON COLUMNS,
  [Cuando].[Anio].Members ON ROWS
FROM [Ventas dia]

Cantidad vendida por comunidad autónoma y año

SELECT
  {[Measures].[Cantidad]} ON COLUMNS,
  NON EMPTY
  CrossJoin(
    [Cuando].[Anio].Members,
    [Donde].[CA].Members
  ) ON ROWS
FROM [Ventas dia]

Ventas por departamento y provincia en 2024

SELECT
  {[Measures].[Importe]} ON COLUMNS,
  NON EMPTY
  CrossJoin(
    [Donde].[Provincia].Members,
    [Que].[Departamento].Members
  ) ON ROWS
FROM [Ventas dia]
WHERE ([Cuando].[Anio].[2024])

4.2.2 Consultas SQL3

Cantidad por departamento y tipo de producto

GROUP BY ROLLUP
SELECT
  q.departamento,
  q.tipo,
  SUM(f.cantidad) AS total_cantidad
FROM
  fact_ventas_dia f
JOIN dim_que q ON f.dim_que_key = q.dim_que_key
GROUP BY ROLLUP (q.departamento, q.tipo)
ORDER BY q.departamento, q.tipo;
GROUP BY CUBE
SELECT
  q.departamento,
  q.tipo,
  SUM(f.cantidad) AS total_cantidad
FROM
  fact_ventas_dia f
JOIN dim_que q ON f.dim_que_key = q.dim_que_key
GROUP BY CUBE (q.departamento, q.tipo)
ORDER BY q.departamento, q.tipo;

Importe total por año y comunidad autónoma

GROUP BY ROLLUP
SELECT
  c.anio,
  d.c_autonoma,
  SUM(f.cantidad * f.pvp) AS importe
FROM
  fact_ventas_dia f
JOIN dim_cuando c ON f.dim_cuando_key = c.dim_cuando_key
JOIN dim_donde d ON f.dim_donde_key = d.dim_donde_key
GROUP BY ROLLUP (c.anio, d.c_autonoma)
ORDER BY c.anio, d.c_autonoma;
GROUP BY CUBE
SELECT
  c.anio,
  d.c_autonoma,
  SUM(f.cantidad * f.pvp) AS importe
FROM
  fact_ventas_dia f
JOIN dim_cuando c ON f.dim_cuando_key = c.dim_cuando_key
JOIN dim_donde d ON f.dim_donde_key = d.dim_donde_key
GROUP BY CUBE (c.anio, d.c_autonoma)
ORDER BY c.anio, d.c_autonoma;

4.2.3 Definición de Agregados

Mediante Pentaho Aggregation Designer se definen agregados para el diseño multidimensional.

Ejercicios

Ejercicio: MDX, SQL3 y Agregados

Todos los apartados valen igual.

Usando la BD del Padrón Municipal de Habitantes, de forma similar a los ejemplos que se facilitan en el vídeo y en este documento:

  1. Define una consulta MDX.

  2. Define una consulta SQL3.

  3. Obtén todos los agregados que proponga Pentaho Aggregation Designer (no es necesario implementarlos en la BD).

Documentación a entregar:

Genera un documento en formato PDF con la estructura y el contenido que se indica a continuación:

  • Para las consultas, indica el título, incluye la consulta e incluye captura de pantalla completa donde se pueda ver la consulta y el resultado obtenido.

  • Para los agregados, incluye captura de pantalla completa donde se vean los agregados definidos, de manera que se muestren los niveles del agregado con mayor número de instancias.

Back, William D., Nicholas Goodman, and Julian Hyde. 2014. Mondrian in Action. Open Source Business Analytics. Manning.
Hyde, Julian. 2009. Mondrian 3.0.4 Technical Guide. Developing OLAP Solutions with Mondrian/JasperAnalysis. Mondrian.