Business intelligence: modelado dimensional, ETL y dashboards

Tarde o temprano, toda organización se hace el mismo tipo de pregunta: cuántos, cuánto, comparado con cuándo y por qué se está moviendo. Los datos para responderla ya existen en los sistemas operativos, repartidos por esquemas diseñados para algo completamente distinto. La business intelligence es la disciplina de remodelar esos datos para que esas preguntas se respondan con rapidez, de forma coherente y sin que haga falta saber SQL.

Las herramientas cambian constantemente; el modelado, no. Un esquema en estrella bien diseñado hace veinte años funcionaría perfectamente en un data warehouse moderno en la nube, y uno mal diseñado decepcionará en cualquier plataforma. Esta guía se centra en esa parte duradera, con ejemplos de SQL que funcionan en PostgreSQL u Oracle.

Dos trabajos distintos: OLTP y OLAP

Los sistemas transaccionales y los analíticos tienen prioridades opuestas, y por eso un mismo esquema no puede servir bien a ambos.

Un esquema operativo almacena cada dato una sola vez y lo recompone mediante joins, la disciplina que se trata en diseño de bases de datos y normalización. Es exactamente lo correcto cuando el objetivo son escrituras concurrentes correctas. Para el análisis, en cambio, genera dos problemas: las consultas necesitan diez joins que solo un especialista sabe escribir bien, y las actualizaciones sobrescriben el pasado, así que «¿a qué segmento pertenecía este cliente cuando hizo ese pedido?» se vuelve imposible de responder.

Por eso los modelos analíticos hacen un intercambio deliberado: aceptan redundancia, conservan el historial y optimizan para la comprensión.

Hechos y dimensiones

El modelado dimensional divide el mundo en mediciones y en el contexto de esas mediciones.

Una tabla de hechos contiene las mediciones (cantidades, importes, duraciones, recuentos) junto con claves foráneas a las dimensiones que las describen. Es larga y estrecha, y es donde vive casi la totalidad de las filas.

Una tabla de dimensión contiene los atributos descriptivos por los que filtras, agrupas y etiquetas: cliente, producto, tienda, fecha, canal. Es corta y ancha, a menudo muy desnormalizada, y es donde vive casi toda la legibilidad.

Todo empieza por el grano: una frase sencilla que dice exactamente qué representa una fila de la tabla de hechos. «Una fila por línea de pedido» es un grano. «Datos de ventas» no lo es. Decláralo, escríbelo en la documentación y rechaza cualquier columna que no encaje con él.

CREATE TABLE dim_date (
  date_key     integer PRIMARY KEY,          -- 20260907
  full_date    date NOT NULL,
  day_of_week  varchar(9) NOT NULL,
  month_name   varchar(9) NOT NULL,
  quarter      smallint NOT NULL,
  year         smallint NOT NULL,
  is_weekend   boolean NOT NULL,
  is_holiday   boolean NOT NULL
);

CREATE TABLE dim_product (
  product_key  bigint PRIMARY KEY,           -- clave subrogada
  sku          varchar(20) NOT NULL,         -- clave natural del sistema origen
  title        varchar(200) NOT NULL,
  category     varchar(60) NOT NULL,
  brand        varchar(60) NOT NULL,
  valid_from   date NOT NULL,
  valid_to     date,
  is_current   boolean NOT NULL
);

CREATE TABLE fact_sales_line (
  date_key     integer NOT NULL REFERENCES dim_date(date_key),
  product_key  bigint  NOT NULL REFERENCES dim_product(product_key),
  customer_key bigint  NOT NULL REFERENCES dim_customer(customer_key),
  store_key    integer NOT NULL REFERENCES dim_store(store_key),
  order_number varchar(20) NOT NULL,         -- dimensión degenerada
  quantity     integer NOT NULL,
  net_amount   numeric(12,2) NOT NULL,
  tax_amount   numeric(12,2) NOT NULL,
  discount     numeric(12,2) NOT NULL
) PARTITION BY RANGE (date_key);

Tres detalles de este modelo son convenciones que merece la pena adoptar. Las dimensiones usan claves subrogadas en lugar de los identificadores del sistema origen, para poder versionar el historial y para que, si el origen cambia su numeración de claves, el data warehouse no se corrompa. La dimensión fecha es una tabla real, no una expresión calculada, porque permite filtrar por «festivo» o «trimestre fiscal» sin codificar un calendario en cada consulta. Y un identificador sin atributos propios, como el número de pedido, se queda en la tabla de hechos como dimensión degenerada en lugar de tener una tabla con la que nadie haría join.

Los hechos se presentan en tres variantes, y mezclarlas produce cifras erróneas. Los hechos aditivos (importe neto, cantidad) se pueden sumar por todas las dimensiones. Los semiaditivos (el saldo de una cuenta, el nivel de stock) se pueden sumar en algunas dimensiones pero no en el tiempo: en su lugar se promedia o se toma el último valor. Los no aditivos (un porcentaje de margen, un precio unitario) no se pueden sumar en absoluto; guarda el numerador y el denominador y calcula el ratio al consultar.

Estrella y copo de nieve

Un esquema en estrella coloca la tabla de hechos en el centro, con un join a cada dimensión. Ese es todo el diseño, y sus virtudes son prácticas: cualquier pregunta se resuelve con un join por dimensión, las herramientas de BI pueden recorrerlo automáticamente y el SQL lo pueden leer personas no especialistas.

SELECT d.year, d.quarter, p.category,
       SUM(f.net_amount) AS revenue,
       SUM(f.quantity)   AS units
FROM   fact_sales_line f
JOIN   dim_date    d ON d.date_key    = f.date_key
JOIN   dim_product p ON p.product_key = f.product_key
WHERE  d.year = 2026 AND p.brand = 'Northwind'
GROUP  BY d.year, d.quarter, p.category
ORDER  BY revenue DESC;

Un esquema en copo de nieve normaliza las dimensiones y separa categoría y marca en sus propias tablas. Ahorra algo de espacio y añade joins a cada consulta. Úsalo de forma selectiva (para una jerarquía realmente profunda o una tabla de referencia compartida entre dimensiones) y deja el resto desnormalizado.

Cuando varias tablas de hechos comparten las mismas tablas de dimensión, esas dimensiones están conformadas, y eso es lo que hace posible el análisis entre procesos: ventas y devoluciones se pueden comparar por producto y mes porque ambas usan las mismas dimensiones de producto y fecha. Las dimensiones conformadas son la columna vertebral de un data warehouse; sin ellas tienes una colección de data marts inconexos.

Como las tablas de hechos son grandes y casi siempre se filtran por tiempo, particiónalas por la clave de fecha y deja que el motor descarte particiones. La indexación también difiere de la de OLTP: los índices bitmap sobre claves de dimensión de baja cardinalidad funcionan bien en los data warehouses de Oracle, y el almacenamiento columnar hace innecesarios muchos índices. El razonamiento detrás de estas decisiones está en la guía de índices y planes de ejecución.

Conservar el historial: dimensiones lentamente cambiantes

Un cliente se muda de ciudad. Un producto cambia de categoría. ¿Qué debería mostrar el informe del año pasado? La respuesta es una decisión de modelado, y el vocabulario estándar va numerado (se habla de SCD, slowly changing dimensions).

• Tipo 0: conservar el original. El atributo no cambia nunca tras la primera carga. Adecuado para una fecha de nacimiento o el canal de alta original.
• Tipo 1: sobrescribir. Se actualiza en el sitio y se pierde el valor anterior. Correcto para corregir errores reales, como un nombre mal escrito. Los informes históricos cambian con carácter retroactivo, lo que a veces es lo correcto y a menudo sorprende.
• Tipo 2: añadir una fila. Se inserta una nueva fila de dimensión con una nueva clave subrogada, se cierra la fila anterior con valid_to y se cambia is_current. Los hechos siguen apuntando a la fila vigente cuando ocurrieron, así que el historial se conserva con exactitud. Es la opción por defecto para cualquier atributo que se use en el análisis.
• Tipo 3: añadir una columna. Se mantienen current_region y previous_region una junto a otra. Solo resulta útil para una reorganización puntual en la que se quieren comparar ambas vistas.

La actualización de tipo 2 es la que hay que hacer bien:

-- Cerrar la versión saliente
UPDATE dim_product
SET    valid_to = current_date - 1, is_current = false
WHERE  sku = 'BK-1042' AND is_current;

-- Abrir la nueva con una clave subrogada nueva
INSERT INTO dim_product (product_key, sku, title, category, brand, valid_from, valid_to, is_current)
VALUES (nextval('dim_product_seq'), 'BK-1042', 'Field Guide to Estuaries',
        'Natural history', 'Northwind', current_date, NULL, true);

Dos notas prácticas. En cada ejecución, carga los hechos después de las dimensiones, para que la búsqueda de claves encuentre la versión correcta. Y añade un miembro «desconocido» (clave 0 o -1) a cada dimensión, para que un hecho que llegue con una referencia ausente pueda cargarse y contarse igualmente en lugar de descartarse sin avisar.

Cómo entran los datos: ETL y ELT

El ETL extrae de las fuentes, transforma en una capa de procesamiento dedicada y carga el resultado. El ELT carga primero los datos en bruto en la plataforma de destino y los transforma con la capacidad de cómputo de la propia plataforma. El ELT se ha convertido en la opción por defecto allí donde el almacenamiento es barato y el motor es elástico, porque se conserva la copia en bruto, las transformaciones pasan a ser SQL bajo control de versiones y no hay que escalar una capa de transformación aparte.

En cualquiera de los dos casos, se repiten las mismas preocupaciones:

• Extracción incremental. Releer una fuente entera cada noche deja de escalar muy pronto. Usa una columna de marca de agua (watermark) o change data capture a partir del log de transacciones del origen.
• Idempotencia. Un job debe producir el mismo resultado si se vuelve a lanzar tras un fallo parcial. Carga en una tabla de staging y después intercambia o fusiona de forma atómica.
• Búsqueda de claves subrogadas. Asocia cada clave natural del origen con la clave de dimensión vigente, tratando de forma explícita los miembros que llegan tarde y los desconocidos.
• Auditoría. Registra en cada carga las filas de entrada y de salida, las filas rechazadas y la duración de la ejecución. Cuando alguien cuestione una cifra tres semanas después, será la auditoría de carga la que responda.
• Planificación y dependencias. Las cargas forman un grafo, no una lista. Orquéstalas para que una dimensión nunca se cargue después de los hechos que la referencian. Para pipelines batch escritos en Java, Spring Batch ofrece el procesamiento por chunks, la capacidad de reinicio y el job repository que esto requiere.

Cuando el volumen o la variedad de fuentes superan lo que un data warehouse maneja con holgura, el pipeline suele depositar primero los datos en un lake; los patrones para ello están en la guía de arquitectura big data.

Data marts, cubos y capa semántica

Un data mart es un subconjunto del data warehouse orientado a un área (finanzas, cadena de suministro, marketing) construido a partir de dimensiones conformadas. Construidos así, los data marts son vistas sobre un único modelo. Construidos por separado en cada departamento, se convierten en el problema clásico de tres equipos que presentan tres cifras de ingresos distintas en la misma reunión.

Los cubos OLAP preagregan las medidas por las jerarquías de dimensiones para que bajar de año a trimestre y a mes sea instantáneo, y se consultan con MDX en lugar de SQL. Los cubos multidimensionales clásicos han cedido gran parte del terreno a los motores columnares en memoria y a los agregados materializados, pero su vocabulario sobrevive en todas partes: slice, dice, drill down, roll up, pivot. En SQL, GROUPING SETS, ROLLUP y CUBE generan filas de subtotales a varios niveles en una sola pasada, y las funciones de ventana resuelven acumulados, medias móviles y comparaciones entre periodos sin self-joins.

La capa semántica es donde viven las definiciones de las métricas: los ingresos son el importe neto excluyendo las líneas canceladas; un cliente activo es el que tiene algún pedido en los últimos 90 días. Definirlas una sola vez (en el modelo de la herramienta de BI, en una capa de métricas o en vistas de base de datos gobernadas) es lo que produce una única versión de la verdad. Cuando cada autor de dashboards vuelve a implementar la definición, la divergencia está garantizada. Las vistas materializadas son aquí un detalle de implementación útil: precalculan los agregados pesados y se refrescan según una planificación.

Dashboards y KPI que de verdad se usan

La mayoría de los dashboards fracasan por motivos de diseño, no de datos.

• Parte de una decisión. Cada gráfico debe responder a una pregunta sobre la que alguien actúa. Si nadie sabe nombrar la acción, elimina el gráfico.
• Da contexto a las cifras. «1,4 M€» no significa nada por sí solo. Frente al objetivo, frente al año pasado, frente al mes anterior y con una tendencia: eso sí es un KPI.
• Elige medidas en las que la gente pueda influir. Una métrica en la que nadie puede influir genera resignación, no mejora.
• Defínelo con precisión y publica la definición. Incluye las condiciones de filtrado, la zona horaria y la hora de actualización en el propio dashboard.
• Muestra la frescura y la completitud. Un dashboard que muestra datos obsoletos sin avisar es peor que uno caído.
• Que quepa en una pantalla. El detalle debe estar a un clic, no en un muro de mosaicos.

Las herramientas de BI de autoservicio permiten a los analistas construir sus propias vistas sobre el modelo gobernado, y ese es el reparto de trabajo correcto: el equipo de datos es responsable de las dimensiones conformadas, del grano de los hechos y de las definiciones de las métricas; los analistas, de las preguntas y la presentación. Sin el modelo gobernado debajo, el autoservicio produce una proliferación de extracciones contradictorias, y los hábitos de consulta de la guía de buenas prácticas de SQL importan más, no menos, cuando cientos de consultas ad hoc caen sobre el data warehouse.

Por último, trata la calidad de los datos como parte del producto. La completitud, la validez, la coherencia entre fuentes, la puntualidad y la unicidad se pueden probar, y las pruebas que se ejecutan con cada carga detectan los problemas antes de que lo haga un director.

Puntos clave

• Los esquemas OLTP optimizan las escrituras concurrentes correctas; los modelos analíticos aceptan redundancia y conservan el historial para que las preguntas sean fáciles de formular y las respuestas se mantengan estables.
• Declara el grano de una tabla de hechos en una frase antes de elegir columnas, y nunca mezcles en una misma tabla filas con significados distintos.
• Prefiere un esquema en estrella con dimensiones desnormalizadas, conformadas y con claves subrogadas; usa copo de nieve solo para jerarquías realmente grandes o compartidas.
• Ten claro si cada hecho es aditivo, semiaditivo o no aditivo, y guarda los ratios como sus componentes.
• Usa la dimensión lentamente cambiante de tipo 2 para los atributos que se usan en el análisis, el tipo 1 solo para corregir errores, y ofrece siempre un miembro desconocido.
• Define cada métrica una sola vez en una capa semántica y diseña los dashboards en torno a decisiones, con contexto, frescura y una definición publicada.