Creando tablas dimensionales en Redshift

Curso de Data Warehousing y Modelado OLAP

Contenido del curso

No sé dónde empezar

Introducción a BI y Data Warehouse

Modelos dimensionales

ETL para inserción en Data Warehouse

Cierre

26
De datos crudos a modelo dimensional listo
01:18 min

Tomar examen

Cursos Empresas Blog Live Conf Eventos Precios

0:007:09

Creando tablas dimensionales en Redshift

Curso de Data Warehousing y Modelado OLAP

Clase 17 de 26

Clase 17 / 26

Recursos

Construir un modelo dimensional en Redshift a partir de una base transaccional en Postgres requiere mapear cada campo, definir tipos de datos correctos y crear las dimensiones antes que la tabla de hechos. Aquí verás cómo se materializa esa lógica en SQL para un data warehouse basado en Adventure Works, útil si trabajas con ETL, modelado de datos o analítica.

¿Cómo se mapean las dimensiones de producto, territorio y vendedores?

Antes de tocar SQL, el documento de mapeo define qué campos de la fuente alimentan cada atributo de cada dimensión. Ese documento es la guía maestra del proceso ETL.

En la dimensión de producto, los datos provienen directamente de la tabla de productos, y la categoría se completa con el nombre y la descripción de una tabla relacionada. La dimensión de territorio se llena con sus campos equivalentes desde la fuente, sin transformaciones complejas.

La dimensión de vendedores es la más interesante porque funciona como dimensión lentamente cambiante tipo 2. Esto significa que cada cambio en los datos del vendedor genera un nuevo registro histórico, y por eso aparecen dos campos extra: fecha de inicio y fecha fin.

¿Qué es una dimensión lentamente cambiante tipo 2? Es una dimensión que conserva el historial de cambios creando un registro nuevo cada vez que un atributo se modifica, usando fechas de vigencia para saber qué versión estaba activa en cada momento.

Estos dos campos no tienen fuente directa: se calculan dentro del proceso ETL. También se aplican transformaciones como concatenar nombre y apellido en un campo de nombre completo, y convertir la letra M y F del género en masculino y femenino.

¿Cómo se construye la tabla de hechos de ventas?

La fact table de ventas se alimenta de las fuentes transaccionales y aplica cálculos que generan las métricas de negocio.

Para el valor de la venta, se multiplica la cantidad por el precio unitario. Para el descuento, se calcula precio unitario por descuento por cantidad. Estas operaciones se ejecutan dentro del ETL antes de insertar el registro.

Los campos ID de territorio, cliente, vendedor y producto no se copian tal cual desde la transaccional. La fuente entrega códigos operativos, y debes cruzarlos contra cada dimensión ya cargada para resolver el ID dimensional correspondiente. Por eso siempre se cargan primero las dimensiones y después la tabla de hechos [03:00].

¿Cómo se crea la dimensión de clientes en Redshift?

El flujo va de Postgres, donde vive Adventure Works como base transaccional, hacia Redshift, que actúa como data warehouse. Dentro de Redshift se trabaja sobre una base de datos llamada Analytics y un esquema DWH Adventure Works [04:25].

La primera tabla a construir es dim_clientes, y los tipos de dato se ajustan al contenido real de cada campo:

ID cliente como integer y primary key.
Código del cliente con tamaño 10.
Nombre y apellido con tamaño 50 cada uno.
Nombre completo con tamaño 100, ya que concatena los dos anteriores.
Teléfonos celular, casa y trabajo con tamaño 20 estándar.
Ciudad de la casa con tamaño 50 por ser descripción.

A esos campos se suman dos columnas de auditoría que aparecen en toda dimensión bien diseñada: fecha de carga y fecha de actualización, ambas como timestamp. Sirven para rastrear cuándo entró un registro y cuándo se modificó por última vez.

sql CREATE TABLE dwh_adventure_works.dim_clientes ( id_cliente INTEGER, codigo_cliente VARCHAR(10), nombre VARCHAR(50), apellido VARCHAR(50), nombre_completo VARCHAR(100), telefono_celular VARCHAR(20), telefono_casa VARCHAR(20), telefono_trabajo VARCHAR(20), ciudad_casa VARCHAR(50), fecha_carga TIMESTAMP, fecha_actualizacion TIMESTAMP, PRIMARY KEY (id_cliente) );

Al ejecutar un SELECT sobre la tabla recién creada, el resultado vendrá vacío pero con la estructura completa lista para recibir datos en el siguiente paso del ETL.

¿Por qué definir tamaños específicos en VARCHAR? Porque ajustar el tamaño al contenido real ahorra almacenamiento, mejora el rendimiento de las consultas y evita reservar espacio innecesario en el data warehouse.

¿Por qué crear primero el esquema y después las transformaciones?

La lógica detrás de este orden es práctica: necesitas el contenedor antes de mover los datos. Crear las tablas físicas establece la base sobre la cual el ETL podrá extraer, transformar y cargar.

El reto siguiente es replicar este mismo proceso para las dimensiones de productos, vendedores y territorio, además de la fact de ventas. Aún no toca preocuparse por las transformaciones; el foco está en el script DDL que materializa el modelo dimensional en la base de datos.

Después vendrá la extracción desde Postgres, las transformaciones definidas en el documento de mapeo y el poblado real de las tablas. ¿Cómo vas a estructurar tu script para las otras cuatro tablas? Cuéntame en los comentarios.

david.parra

Hace 3 años

Creación de tablas:

CREATE TABLE dim_clientes(
    id_cliente	integer,
    codigo_cliente	varchar(10),
    nombre	varchar(50),
    apellido	varchar(50),
    nombre_completo	varchar(100),
    numero_telefono_celular	varchar(20),
    numero_telefono_casa	varchar(20),
    numero_telefono_trabajo	varchar(20),
    ciudad_casa	varchar(50),
    fecha_carga timestamp,
    fecha_actualizacion timestamp,
    primary key (id_cliente)
);

CREATE TABLE dim_producto(
    id_producto	integer,
    codigo_producto	varchar(10),
    nombre	varchar(50),
    color	varchar(30),
    tamaño	varchar(20),
    categoria	varchar(50),
    fecha_carga timestamp,
    fecha_actualizacion timestamp,
    primary key (id_producto)
);

CREATE TABLE dim_territorio(
    id_territorio	integer,
    codigo_territorio	varchar(20),
    nombre	varchar(50),
    continente	varchar(30),
    fecha_carga timestamp,
    fecha_actualizacion timestamp,
    primary key (id_territorio)
);

CREATE TABLE dim_vendedor(
    id_vendedor	integer,
    codigo_vendedor	varchar(20),
    identificación	varchar(20),
    nombre	varchar(50),
    apellido	varchar(50),
    nombre_completo	varchar(100),
    rol	varchar(30),
    fecha_nacimiento	date,
    genero	varchar(12),
    ind_activo	boolean,
    ind_bono	boolean,
    fecha_inicio	date,
    fecha_fin	date,
    fecha_carga timestamp,
    fecha_actualizacion timestamp,
    primary key (id_vendedor)
);


CREATE TABLE fact_ventas(
    id_venta	integer,
    codigo_venta_detalle	varchar(20),
    codigo_venta_encabezado	varchar(20),
    id_fecha	integer,
    id_territorio	integer,
    id_cliente	integer,
    id_vendedor	integer,
    id_producto	integer,
    cantidad	integer,
    valor	decimal,
    descuento	decimal,
    fecha_carga timestamp,
    fecha_actualizacion timestamp,
    primary key (id_venta)
);

ALTER TABLE fact_ventas
    ADD CONSTRAINT territorio_ventas_fk FOREIGN KEY ( id_territorio )
        REFERENCES dim_territorio ( id_territorio );

ALTER TABLE fact_ventas
    ADD CONSTRAINT clientes_ventas_fk FOREIGN KEY ( id_cliente )
        REFERENCES dim_clientes ( id_cliente );

ALTER TABLE fact_ventas
    ADD CONSTRAINT vendedores_ventas_fk FOREIGN KEY ( id_vendedor )
        REFERENCES dim_vendedor ( id_vendedor );

ALTER TABLE fact_ventas
    ADD CONSTRAINT productos_ventas_fk FOREIGN KEY ( id_producto )
        REFERENCES dim_producto ( id_producto );

Introducción a BI y Data Warehouse

Qué es Business Intelligence y Data Warehousing

Niveles de analítica y jerarquía de datos

Qué son data warehouse, data mart y dimensiones

OLTP vs OLAP: diferencias clave

Inmon, Kimball y Hefesto en BI

Modelos dimensionales

Data Warehouse, Data Lake y Lakehouse

Modelo estrella vs copo de nieve en datos

Tipos de dimensiones lentamente cambiantes

Dimensión tipo 1: sobrescribir sin guardar historia

Dimensión tipo 2

Dimensión tipo 3: historia en columnas

Tabla de hechos (fact)

Configuración de herramientas para Data Warehouse y ETL

Cómo extraer dimensiones de preguntas de negocio

Diseño de tablas en un modelo dimensional

ETL para inserción en Data Warehouse

Documento de mapeo en ETL para data warehouse

Creando tablas dimensionales en Redshift

Extracción: querys en SQL

Cruce de fuentes en Pentaho con Stream Lookup

Transformación ETL con Pentaho paso a paso

Carga de datos transformados a Redshift con Pentaho

Cómo cargar la tabla de hechos con Pentaho

Cómo calcular MaxID y MaxDate en Pentaho

Orquestar ETL en Pentaho: job

Solución ETL con dimensiones en paralelo en Redshift

Cierre

De datos crudos a modelo dimensional listo

Creando tablas dimensionales en Redshift

Progreso del curso

Introducción a BI y Data Warehouse

Modelos dimensionales

ETL para inserción en Data Warehouse

Cierre

Recursos

Recursos