Métricas y KPIs: diseñar facts que respondan preguntas de negocio

1-- SEMI-ADITIVA: balance de cuenta (aditivo por cuenta, NO por tiempo)
2
3-- ✅ CORRECTO: sumar saldos de varias cuentas (mismo día)
4-- "¿Cuál es el saldo total del banco al cierre del 15 de marzo?"
5SELECT SUM(balance) AS total_bank_balance
6FROM fact_daily_balance
7WHERE date_key = 20240315;
8-- Resultado: suma de todos los saldos = total del banco. Correcto.
9
10-- ❌ INCORRECTO: sumar saldos de varios días (misma cuenta)
11-- "¿Cuál es el saldo total de la cuenta #123 en Q1?"
12SELECT SUM(balance) AS total_balance_q1  -- ¡¡¡ESTO NO TIENE SENTIDO!!!
13FROM fact_daily_balance
14WHERE account_key = 123 AND date_key BETWEEN 20240101 AND 20240331;
15-- Resultado: suma de 90 saldos diarios = un número absurdo
16
17-- ✅ CORRECTO: usar AVG o LAST para la dimensión tiempo
18SELECT AVG(balance) AS avg_balance_q1
19FROM fact_daily_balance
20WHERE account_key = 123 AND date_key BETWEEN 20240101 AND 20240331;
21
22-- O el último valor del período:
23SELECT balance AS end_of_quarter_balance
24FROM fact_daily_balance
25WHERE account_key = 123 AND date_key = 20240331;

El saldo es semi-aditivo: sumable entre cuentas, pero para tiempo necesitas AVG o último valor.

1-- NO ADITIVAS: ratios y porcentajes
2
3-- ❌ MAL: guardar el margen como porcentaje en la fact table
4-- fact_sales: margin_pct = 30%
5-- Si sumas margin_pct de 100 filas, obtienes 3000% — absurdo
6
7-- ✅ BIEN: guardar los componentes (revenue y cost) por separado
8-- El ratio se CALCULA en la query, no se almacena
9SELECT
10    p.category,
11    SUM(f.net_amount) AS revenue,
12    SUM(f.cost) AS total_cost,
13    -- El margen se CALCULA, no se suma:
14    (SUM(f.net_amount) - SUM(f.cost)) / NULLIF(SUM(f.net_amount), 0) * 100
15        AS margin_pct
16FROM fact_sales f
17JOIN dim_product p ON f.product_key = p.product_key
18GROUP BY p.category;
19
20-- Otros ejemplos de no-aditivas que NO debes guardar pre-calculadas:
21-- conversion_rate = conversions / visits (guarda ambas por separado)
22-- avg_order_value = total_revenue / num_orders (guarda ambas)
23-- bounce_rate = bounces / sessions (guarda ambas)

Regla: nunca guardes ratios precalculados en la fact table. Guarda los componentes y calcula en la query.

1-- SNAPSHOT PERIÓDICO: inventario diario
2CREATE TABLE fact_daily_inventory (
3    date_key            INTEGER NOT NULL,
4    product_key         INTEGER NOT NULL,
5    warehouse_key       INTEGER NOT NULL,
6    -- Métricas del snapshot
7    quantity_on_hand    INTEGER,        -- stock disponible
8    quantity_reserved   INTEGER,        -- reservado para pedidos
9    quantity_in_transit INTEGER,        -- en camino
10    reorder_point       INTEGER,        -- punto de reorden
11    days_of_supply      DECIMAL(5,1),   -- días que dura el stock actual
12    PRIMARY KEY (date_key, product_key, warehouse_key)
13);
14
15-- Este snapshot crece date × products × warehouses por DÍA
16-- Con 10K productos y 5 almacenes: 50K filas por día = 18M al año
17-- Semi-aditiva: puedes sumar quantity_on_hand entre almacenes,
18-- pero NO entre días (sumar stock de lunes + martes no tiene sentido)

El snapshot periódico responde "¿cómo estaban las cosas en ese momento?" — esencial para inventario, saldos, pipelines.

1-- ACCUMULATING SNAPSHOT: ciclo de vida de un pedido
2CREATE TABLE fact_order_lifecycle (
3    order_key               BIGINT PRIMARY KEY,
4    order_number            VARCHAR(20),
5    customer_key            INTEGER,
6    -- Múltiples date_keys (uno por hito)
7    order_date_key          INTEGER,    -- cuándo se creó
8    payment_date_key        INTEGER,    -- cuándo se pagó (NULL si pendiente)
9    ship_date_key           INTEGER,    -- cuándo se envió (NULL si no enviado)
10    delivery_date_key       INTEGER,    -- cuándo se entregó (NULL si no entregado)
11    -- Métricas
12    order_amount            DECIMAL(12,2),
13    -- Métricas CALCULADAS de duración (se actualizan al completar cada hito)
14    days_to_payment         INTEGER,    -- payment_date - order_date
15    days_to_ship            INTEGER,    -- ship_date - payment_date
16    days_to_delivery        INTEGER,    -- delivery_date - ship_date
17    total_cycle_days        INTEGER     -- delivery_date - order_date
18);
19
20-- Esta tabla se ACTUALIZA conforme avanza el pedido:
21-- Día 1: INSERT con order_date, todo lo demás NULL
22-- Día 2: UPDATE payment_date_key, days_to_payment
23-- Día 4: UPDATE ship_date_key, days_to_ship
24-- Día 7: UPDATE delivery_date_key, days_to_delivery, total_cycle_days
25
26-- Query: "¿Cuál es el tiempo medio de entrega por región?"
27SELECT c.country, AVG(f.total_cycle_days) AS avg_delivery_days
28FROM fact_order_lifecycle f
29JOIN dim_customer c ON f.customer_key = c.customer_key
30WHERE f.delivery_date_key IS NOT NULL  -- solo pedidos completados
31GROUP BY c.country;

El accumulating snapshot mide CUÁNTO TARDA cada fase. Perfecto para SLAs y cuellos de botella.