Data Warehouse
Un Data Warehouse (magatzem de dades, DW) és un sistema d'emmagatzematge analític dissenyat específicament per donar suport a la presa de decisions de negoci. A diferència d'una base de dades operacional que optimitza les escriptures individuals (OLTP), el Data Warehouse optimitza les lectures massives sobre grans volums de dades histórics (OLAP). Entendre el DW és fonamental perquè és la capa "Gold" de quasi totes les arquitectures de Big Data modernes: el lloc on les dades netejades es transformen en coneixement de negoci.
OLTP vs OLAP: dues filosofies oposades
La distinció entre OLTP i OLAP és el primer concepte que cal entendre per comprendre per qué existeix el Data Warehouse.
OLTP — Processament transaccional en línia
Un sistema OLTP (Online Transaction Processing) és la base de dades que suporta les operacions del dia a dia d'una empresa: les comandes d'un e-commerce, les reserves d'un hotel, les transaccions bancàries. Les seves característiques fonamentals són:
- Lectures i escriptures individuals: inserir un registre, actualitzar l'estoc d'un producte, llegir la comanda d'un client concret.
- Dades actuals: conté l'estat present de l'empresa, rarament l'historial complet.
- Esquema normalitzat (3FN): minimitza la redundància de dades i protegeix la integritat referencial.
- Molts usuaris concurrents: centenars o milers d'usuaris fent operacions simultànies.
- Latència molt baixa: cada operació ha de completar-se en mil·lisegons.
Exemple: la base de dades PostgreSQL d'una botiga en línia que gestiona els pedidos, els clients i l'estoc.
OLAP — Processament analític en línia
Un sistema OLAP (Online Analytical Processing) és el sistema que respon preguntes analítiques: quines categories de producte han venut més aquest trimestre? Quin és el marge brut per regió comparant el 2025 amb el 2024? Les seves característiques fonamentals són:
- Lectures massives: una consulta llegeix milions o milers de milions de registres per calcular un agregat.
- Dades histórics: conté anys d'historial per permetre comparatives temporals.
- Esquema desnormalitzat: optimitzat per a la velocitat de lectura, sacrificant l'eficiència d'escriptura.
- Pocs usuaris concurrents: desenes o centenars d'analistes de negoci.
- Latència de consulta de segons o minuts: acceptable per a anàlisi, no per a operació.
| Característica | OLTP | OLAP |
|---|---|---|
| Operació típica | INSERT / UPDATE individual | SELECT amb GROUP BY i JOINs massius |
| Volum de dades llegit | Uns pocs registres | Milions - milers de milions |
| Usuaris | Molts (1.000+) | Pocs (10-100 analistes) |
| Esquema | Normalitzat (3FN) | Desnormalitzat (estrella, floc de neu) |
| Historial | Dades actuals | Anys d'historial |
| Motor típic | PostgreSQL, MySQL, SQL Server | Redshift, Snowflake, BigQuery, Synapse |
| Emmagatzematge | Per files (row-oriented) | Per columnes (columnar) |
Per qué l'emmagatzematge columnar és clau per a OLAP
En un sistema row-oriented, tots els camps d'un registre es guarden consecutivament al disc. Per llegir la columna import_net de 100 milions de registres, cal llegir tot el bloc de dades de cada registre, incloent els camps que no s'usen (id_client, id_producte, data, etc.).
En un sistema column-oriented (columnar), tots els valors de la mateixa columna es guarden junts. Per llegir import_net de 100 milions de registres, el motor llegeix únicament el bloc corresponent a import_net, ignorant completament les altres columnes. Resultat: menys I/O, millor compressió (valors del mateix tipus es comprimen millor), i execució vectoritzada molt més ràpida.
Kimball vs Inmon: dues filosofies de disseny
Hi ha dues escoles de pensament clàssiques sobre com dissenyar un Data Warehouse. Cap de les dues és "correcta" de forma absoluta: la tria depèn del context, la mida de l'organització i la maduresa del equip de dades.
La filosofia de Kimball — Bottom-up
Ralph Kimball va proposar el modelatge dimensional com a base del DW. La seva aproximació és bottom-up (de baix a dalt): es construeix un Data Mart per a cada àrea de negoci (vendes, finances, recursos humans) i s'integren progressivament en un DW complet.
Les unitats bàsiques del modelatge dimensional de Kimball són les taules de fets i les taules de dimensions, organitzades en un esquema estrella.
Avantatges de l'enfocament Kimball: - Resultats ràpids: el primer Data Mart es pot tenir en poques setmanes. - Fàcil d'entendre per als analistes de negoci. - Directament usable per a BI sense transformació addicional.
La filosofia de Inmon — Top-down
Bill Inmon va proposar que un DW ha de ser primer un repositori centralitzat, normalitzat i integrat de totes les dades de l'empresa (Corporate Information Factory), i que els Data Marts per a cada àrea de negoci es creen a partir d'aquest repositori central.
La seva aproximació és top-down (de dalt a baix): primer es dissenya l'arquitectura global i el model de dades corporatiu (normalitzat en 3FN), i després se'n deriven els Data Marts desnormalitzats per a cada àrea.
Avantatges de l'enfocament Inmon: - Consistència total de les dades a tota l'organització. - Flexibilitat per crear nous Data Marts sense reestructurar les dades. - Millor per a grans corporacions amb moltes àrees de negoci.
Desavantatges: el temps fins als primers resultats és molt més llarg i requereix un equip de disseny de dades molt experientat.
Modelatge dimensional de Kimball
El modelatge dimensional organitza les dades al voltant de dos tipus de taules: les taules de fets (que contenen les mètriques) i les taules de dimensions (que contenen el context).
Taules de fets (Fact Tables)
Una taula de fets registra els esdeveniments de negoci mesurables: una venda, un vol, una trucada de telèfon, una sessió web. Cada fila representa una ocurrència d'un fet a un cert nivell de detall (la granularitat).
Característiques d'una taula de fets: - Conté mètriques numèriques (imports, quantitats, durades, distàncies) que se sumen o s'agreguen. - Conté claus foranes (FKs) cap a totes les taules de dimensions relacionades. - Pot ser molt gran (milers de milions de files en sistemes grans). - Les columnes propies solen ser poques (les FK + les mètriques).
Granularitat
La granularitat és el nivell de detall de cada fila de la taula de fets. Triar la granularitat correcta és una de les decisions de disseny més importants:
- Granularitat alta (molts detalls): cada fila és una línia d'una comanda individual. Permet calcular qualsevol agregació, però la taula és molt gran.
- Granularitat baixa (menys detalls): cada fila és el total de vendes d'un dia per una botiga. La taula és petita però no es poden fer certes anàlisis detallades.
La recomanació de Kimball és sempre triar la granularitat més fina possible que sigui còmoda d'emmagatzemar, per tenir màxima flexibilitat analítica.
Taules de dimensions (Dimension Tables)
Les taules de dimensions contenen el context descriptiu dels fets: qui, on, quan, qué. No contenen mètriques numèriques, sinó atributs textuals que permeten filtrar, agrupar i etiquetar els fets.
Característiques d'una taula de dimensions: - Conté atributs descriptius (nom, categoria, adreça, descripció, codi postal). - Normalment és relativament petita (milers o desenes de milers de files, rarament milions). - Usa una surrogate key (clau sintètica, normalment un enter incremental) com a clau primària, independent de la clau de negoci del sistema origen. - Pot contenir jerarquies (país → comunitat → província → municipi) aplanades en columnes separades.
Per qué surrogate keys i no les claus de negoci?
Les claus de negoci (el codi de client del sistema CRM, el codi de producte del ERP) pot canviar, pot tenir formats inconsistents, o pot repetir-se entre sistemes. Les surrogate keys eviten aquests problemes i permeten gestionar els canvis histórics de les dades (Slowly Changing Dimensions).
Esquema estrella (Star Schema)
L'esquema estrella és el patró de modelatge dimensional recomanat per Kimball per a la majoria de casos d'ús. La taula de fets es troba al centre, i les taules de dimensions l'envolten com les puntes d'una estrella.
erDiagram
FACT_VENDES ||--o{ DIM_TEMPS : "sk_temps"
FACT_VENDES ||--o{ DIM_PRODUCTE : "sk_producte"
FACT_VENDES ||--o{ DIM_CLIENT : "sk_client"
FACT_VENDES ||--o{ DIM_BOTIGA : "sk_botiga"
FACT_VENDES ||--o{ DIM_PROMOTIO : "sk_promotio"
FACT_VENDES {
bigint sk_venda PK
int sk_temps FK
int sk_producte FK
int sk_client FK
int sk_botiga FK
int sk_promotio FK
decimal quantitat
decimal preu_unitari
decimal import_net
decimal cost_unitari
decimal marge_brut
}
DIM_TEMPS {
int sk_temps PK
date data_completa
int any
int trimestre
int mes
string nom_mes
int setmana
int dia_setmana
string nom_dia
boolean es_cap_de_setmana
boolean es_festiu
}
DIM_PRODUCTE {
int sk_producte PK
string codi_producte_negoci
string nom_producte
string categoria
string subcategoria
string marca
string unitat_mesura
decimal preu_pvp
}
DIM_CLIENT {
int sk_client PK
string codi_client_negoci
string nom_client
string segment
string pais
string comunitat
string provincia
string municipi
string canal_adquisicio
date data_alta
}
DIM_BOTIGA {
int sk_botiga PK
string codi_botiga
string nom_botiga
string tipus_botiga
string pais
string provincia
string adreca
int any_obertura
}
DIM_PROMOTIO {
int sk_promotio PK
string codi_promotio
string nom_promotio
string tipus_promotio
decimal descompte_pct
date data_inici
date data_fi
}Avantatges de l'esquema estrella
- Simplicitat: un analista pot entendre el model sense documentació exhaustiva.
- Rendiment: un JOIN entre la taula de fets i una sola dimensió és molt eficient, fins i tot sobre milions de files.
- Compatibilitat: tots els motors de DW i eines de BI entenen l'esquema estrella.
- Desnormalitzat: les dimensions aplanades (sense normalitzar) redueixen el nombre de JOINs necessaris per a les consultes.
Exemple SQL: consulta típica de BI sobre el DW
-- Vendes per trimestre, per comunitat i per categoria de producte
-- per al any 2025, comparades amb el 2024
SELECT
t.any,
t.trimestre,
cl.comunitat,
p.categoria,
SUM(f.import_net) AS total_vendes,
SUM(f.marge_brut) AS total_marge,
ROUND(SUM(f.marge_brut) / SUM(f.import_net) * 100, 2) AS pct_marge,
COUNT(DISTINCT f.sk_client) AS clients_actius
FROM fact_vendes f
JOIN dim_temps t ON f.sk_temps = t.sk_temps
JOIN dim_client cl ON f.sk_client = cl.sk_client
JOIN dim_producte p ON f.sk_producte = p.sk_producte
WHERE t.any IN (2024, 2025)
GROUP BY t.any, t.trimestre, cl.comunitat, p.categoria
ORDER BY t.any, t.trimestre, total_vendes DESC;
Esquema en floc de neu (Snowflake Schema)
L'esquema en floc de neu és una variant de l'esquema estrella on les dimensions es normalitzen: les jerarquies es descomponen en taules separades. Per exemple, en lloc d'una sola taula DIM_CLIENT amb pais, provincia i municipi, hi hauria taules DIM_PAIS, DIM_PROVINCIA i DIM_MUNICIPI relacionades entre elles.
Avantatge: menor redundància de dades en les dimensions.
Desavantatge: més JOINs en cada consulta, menor rendiment, major complexitat per als analistes.
La majoria d'equips de dades prefereixen l'esquema estrella per la seva simplicitat i rendiment. L'esquema en floc de neu s'usa ocasionalment per a dimensions molt grans on la redundància és problemàtica.
Slowly Changing Dimensions (SCD)
Les dimensions canvien al llarg del temps: un client canvia d'adreça, un producte canvia de categoria, una botiga canvia de nom. La pregunta clau és: quan canvia un atribut d'una dimensió, com gestionen el canvi?
SCD Tipus 1 — Sobreescriptura (Overwrite)
L'atribut antic s'esborrar i es substitueix pel valor nou. No es conserva cap historial.
Quan usar: quan el valor anterior no té cap valor analític (per exemple, un error d'introducció de dades que cal corregir, o un codi postal mal introduït).
Limitació: les anàlisis histórics que segmentin per l'atribut modificat quedaran inconsistents (les vendes antigues apareixeran associades al valor nou, no al valor que era vàlid quan es va fer la venda).
SCD Tipus 2 — Historial complet (Nova fila)
Quan un atribut canvia, s'insereix una nova fila a la taula de dimensions amb el valor nou i dates de vigència (data_inici i data_fi). La fila antiga es tanca amb la data de canvi. El registre de la taula de fets sempre apunta a la surrogate key de la versió vàlida en el moment de la venda.
-- Exemple de DIM_CLIENT amb SCD Tipus 2
-- El client C001 va viure a Girona fins al 2024-06-30 i des del 2024-07-01 viu a Barcelona
SELECT sk_client, codi_client_negoci, nom_client, provincia, data_inici, data_fi, es_actual
FROM dim_client
WHERE codi_client_negoci = 'C001'
ORDER BY data_inici;
-- sk | codi | nom | provincia | data_inici | data_fi | es_actual
-- 1 | C001 | Maria Puig | Girona | 2020-01-01 | 2024-06-30 | false
-- 47 | C001 | Maria Puig | Barcelona | 2024-07-01 | 9999-12-31 | true
Quan usar: quan cal analitzar el comportament históric amb el valor vàlid en el moment de l'event. És el tipus de SCD més usat en Data Warehouses professionals.
SCD Tipus 3 — Atribut anterior (Columna addicional)
S'afegeix una columna addicional a la taula de dimensions per emmagatzemar el valor anterior: provincia_actual i provincia_anterior. Només es conserva un nivell d'historial.
Quan usar: quan cal comparar el valor actual i l'anterior, però no hi ha necessitat d'un historial complet. Poc usat en pràctica perquè és inflexible (si hi ha un tercer canvi, es perd el primer valor).
Càrrega de dades: ETL tradicional vs ELT modern
ETL (Extract, Transform, Load) tradicional
En l'ETL clàssic, les dades s'extreuen de les fonts, es transformen en un servidor d'ETL intermig (que pot ser un servidor Informatica, SSIS, o un clúster Spark), i finalment es carreguen ja transformades al DW.
Avantatge: el DW no ha de gestionar el cost de la transformació. Desavantatge: requereix infraestructura ETL addicional i la transformació és un coll d'ampolla si el volum creix.
ELT (Extract, Load, Transform) modern
En l'ELT modern, les dades s'extreuen de les fonts, es carreguen en brut al DW (o al lake), i les transformacions s'executen dins del propi DW aprofitant la seva potència de còmput distribuïda. Eines com dbt (data build tool) codifiquen les transformacions com a models SQL que s'executen directament a Snowflake, Redshift o BigQuery.
Avantatge: simplifica l'arquitectura, aprofita la potència dels DW moderns, i les transformacions estan versionades com a codi. Desavantatge: totes les dades brutes arriben al DW, augmentant el cost d'emmagatzematge.
Càrrega incremental vs full load
- Full load: trunca la taula i la reconstrueix completament en cada execució. Simple, però inviable per a taules de fets de milers de milions de files.
- Incremental: insereix o actualitza únicament els registres nous o modificats des de l'última càrrega. Requereix identificar els registres nous (per timestamp, per CDC — Change Data Capture, per watermark).
- Idempotència: una càrrega idempotent produeix el mateix resultat si s'executa una o deu vegades. Essencial per a pipelines resilients: si una execució falla a mig camí i es torna a executar, no s'han de duplicar dades.
Eines modernes de Data Warehouse
| Eina | Proveïdor | Arquitectura | Punt fort |
|---|---|---|---|
| Snowflake | Snowflake Inc. | Separació còmput/emmagatzematge | Escalat independent, multi-cloud (AWS/Azure/GCP) |
| Google BigQuery | Google Cloud | Serverless | Sense gestió de clústers, cost per query |
| Amazon Redshift | AWS | MPP + RA3 nodes | Integrat amb ecosistema AWS |
| Azure Synapse | Microsoft | MPP + Serverless | Integrat amb ecosistema Azure + Power BI |
| Databricks SQL | Databricks | Lakehouse (Delta Lake) | Unifica DW i Data Lake, multi-cloud |
La tendència: del DW al Lakehouse
L'any 2026, la frontera entre Data Warehouse i Data Lake s'ha difuminat significativament. Plataformes com Databricks i Snowflake permeten consultar dades obertes (Parquet, Iceberg) des del DW, i emmagatzemar les dades del DW en formats oberts. El terme "Lakehouse" descriu aquesta convergència. En projectes nous, és cada vegada menys comú construir un DW clàssic separat del lake.
AC5074/04/03 — Miniactivitat
Una aerolínia de baix cost vol construir un Data Warehouse per analitzar el rendiment dels seus vols: taxes d'ocupació, puntualitat, ingressos per ruta, costs operacionals i satisfacció dels passatgers.
Dissenya el model dimensional per a aquest Data Warehouse:
- Identifica la taula de fets principal. Quina és la granularitat adequada (per vol, per passatger i vol, per segment de vol)? Raona la tria en base a les consultes analítiques que s'haurien de poder respondre.
- Identifica almenys cinc taules de dimensions. Per a cada dimensió, llista els 5 atributs més importants.
- Quin atribut de la dimensió
DIM_RUTApodria canviar al llarg del temps (per exemple, si l'aerolínia canvia la categoria d'una ruta de "domèstica" a "europeu")? Quin tipus de SCD usaries i per qué? - Escriu la consulta SQL que respondria: "Quines 10 rutes han tingut la major taxa d'ocupació mitjana durant el segon trimestre de 2025, i quants minuts de retard mig han tingut?"
- Dibuixa l'esquema estrella resultant en format Mermaid (erDiagram).
Format de lliurament: document amb el model i la consulta SQL, entregat al Campus Virtual.
Mòdul M5074 Sistemes de Big Data | Institut Sa Palomera (Blanes) | Curs CEIABD 2026-2027