Diseño de tablas en un modelo dimensional

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:0011:23

Diseño de tablas en un modelo dimensional

Curso de Data Warehousing y Modelado OLAP

Clase 15 de 26

Clase 15 / 26

Recursos

Diseñar un modelo dimensional consiste en traducir las preguntas del negocio a tablas concretas: dimensiones que describen el contexto y una tabla de hechos que guarda las métricas. Aquí verás cómo armar ese diseño paso a paso, identificar relaciones y aplicar reglas de negocio para que tu data warehouse responda lo que el negocio realmente necesita.

¿Qué son las reglas de negocio en un modelo dimensional?

Las reglas de negocio son las transformaciones que el negocio te pide aplicar sobre los datos para que sirvan al momento de tomar decisiones [0:30]. No son opcionales: marcan cómo debe verse cada campo antes de llegar al modelo final.

Un ejemplo típico: tienes campos separados de nombre, segundo nombre, apellido y segundo apellido, pero el área de análisis necesita un solo campo de nombre completo. Esa unión es una regla que aplicas en el ETL y que luego se refleja en tu dimensión.

Otros casos comunes que vas a encontrar:

Recortar campos de observación muy extensos porque el data warehouse no necesita tanto detalle.
Marcar vendedores que cumplen un criterio con un atributo adicional.
Agrupar datos transaccionales bajo categorías que el negocio define.

¿Dónde se aplican las reglas de negocio? En el proceso ETL, antes de cargar al modelo dimensional. El modelo solo refleja el resultado ya transformado.

¿Cómo se diseña una dimensión paso a paso?

Para diseñar una dimensión necesitas un nombre claro, una llave propia del data warehouse y los atributos que respondan a las preguntas de negocio. La herramienta usada en la clase es DB Diagram, que te permite escribir las tablas y verlas dibujadas al instante [2:30].

¿Cómo construir la dimensión de clientes?

La dimensión dim_clientes se crea bajo el esquema dw e incluye los campos que el negocio necesita para identificar y contactar a un cliente [3:00]:

id_cliente como entero y llave primaria.
codigo_cliente como varchar (el ID original del sistema transaccional).
nombre y apellido como varchar.
nombre_completo como varchar, cumpliendo la regla de negocio.
numero_celular, numero_casa y numero_trabajo como varchar.
ciudad_vivienda como varchar.

Fíjate en un detalle clave: el id_cliente del modelo no es el mismo del sistema transaccional. Creas un entero nuevo porque los IDs originales suelen ser varchar y no son óptimos para cruces ni indexaciones [6:30].

¿Cómo construir la dimensión de producto?

La dimensión dim_productos sigue la misma lógica. Llevas un id_producto entero como llave, un codigo_producto varchar que conserva el identificador transaccional, y los atributos que pidió el negocio:

nombre_producto como varchar.
color como varchar.
tamano como varchar.
categoria como varchar.

La categoría aparece porque el negocio pidió analizar ventas por categoría. Esa es la pista directa para saber qué atributos incluir: cada pregunta de negocio se traduce en un campo dentro de una dimensión.

¿Qué lleva la tabla de hechos y cómo se conecta con las dimensiones?

La tabla de hechos guarda los IDs de las dimensiones y las métricas que el negocio quiere medir. En este caso se llama fact_ventas porque las preguntas giran alrededor del proceso de ventas [7:30].

La estructura queda así:

id_venta como entero y llave primaria incremental.
id_cliente como entero, traído desde la dimensión de clientes.
id_producto como entero, traído desde la dimensión de productos.
cantidad como entero.
valor como decimal.
descuento como decimal.
valor_neto como decimal, calculado como valor menos descuento.

¿Qué diferencia hay entre dimensión y tabla de hechos? La dimensión describe el contexto (quién, qué, dónde) con atributos descriptivos. La tabla de hechos guarda los IDs de esas dimensiones y los números que mides: cantidades, valores, descuentos.

¿Necesitas crear relaciones físicas entre tablas?

No. En un modelo dimensional las relaciones no se crean físicamente en la base de datos como en un modelo relacional tradicional [9:30]. Las relaciones existen de forma virtual: la tabla de hechos comparte los IDs con cada dimensión, y eso basta para hacer los cruces analíticos.

¿Por qué se hace así? Porque una misma dimensión puede reutilizarse en varios modelos. La dimensión de clientes puede aparecer en fact_ventas, en un modelo de despachos o en uno de rentas. La dimensión de productos puede vivir también en un modelo de inventarios. Forzar relaciones físicas limitaría esa reutilización.

En DB Diagram sí dibujas las relaciones para visualizar cómo se conectan los IDs, pero ese diagrama es documentación, no una restricción de base de datos.

¿Cómo traducir preguntas de negocio en atributos del modelo?

Cada atributo de tu modelo debe existir porque alguien lo va a preguntar. Si el negocio quiere ventas por categoría, categoria vive en dim_productos. Si quiere identificar un cliente puntual, los datos de contacto viven en dim_clientes. Y si quiere medir cantidades, valores y descuentos, esas métricas viven en fact_ventas.

Esta es la regla simple que ordena todo el diseño: los atributos descriptivos van a las dimensiones, los números medibles van a la tabla de hechos, y los IDs enteros creados en el data warehouse son los que conectan ambas.

Ahora te toca a ti: completa el modelo agregando la dimensión de vendedores y la de geografía, y cuéntanos en los comentarios cómo te quedó tu propuesta.

Mario Alexander Vargas Celis

Hace 2 años

El diseño de un modelo dimensional se centra en estructurar los datos para facilitar la consulta y el análisis, utilizando un enfoque orientado al usuario final. A continuación, se describen los pasos principales para diseñar un modelo dimensional:

1. Definir el propósito del modelo

Objetivo: Comprender las preguntas de negocio que el modelo debe responder. Por ejemplo:
- ¿Qué métricas se analizarán (ventas, ingresos, costos)?
- ¿Qué dimensiones (perspectivas) interesan (tiempo, producto, región)?
Esto ayuda a determinar las métricas clave y las dimensiones necesarias.

2. Identificar los procesos de negocio

Paso: Identificar los procesos principales de la organización que generan datos para el análisis (ventas, inventario, finanzas).
Ejemplo: Un negocio de retail podría enfocarse en las ventas y el inventario como procesos clave.

3. Determinar las métricas (hechos)

Definición: Las métricas (o hechos) son los valores cuantitativos que se analizan.
Ejemplo:
- Ventas totales, cantidad de productos vendidos, ingresos generados.
Nota: Asegúrate de que los hechos sean numéricos y agregables.

4. Identificar las dimensiones

Definición: Las dimensiones son los atributos que contextualizan las métricas.
Ejemplo:
- Tiempo (año, mes, día)
- Producto (categoría, marca, nombre)
- Cliente (edad, región, género)
Las dimensiones deben estar diseñadas para responder preguntas como: "¿Qué productos se venden más en cada región?"

5. Elegir el esquema dimensional

Opciones comunes:
- Esquema estrella (Star Schema): Una tabla de hechos en el centro conectada directamente a las tablas de dimensiones.
- Esquema copo de nieve (Snowflake Schema): Extiende las tablas de dimensiones en subdimensiones para normalización.
Ejemplo de esquema estrella:
- Tabla de hechos: ventas
- Dimensiones: dim_producto, dim_cliente, dim_tiempo.

6. Diseñar las tablas de hechos

Incluir:
- Clave primaria compuesta: Llaves foráneas de las dimensiones.
- Métricas: Las medidas a analizar.
Ejemplo:
- Tabla fact_ventas:
  - Claves foráneas: id_producto, id_tiempo, id_cliente.
  - Métricas: cantidad_vendida, ingreso_total.

7. Diseñar las tablas de dimensiones

Cada dimensión debe incluir:
- Clave primaria: Identificador único (id_tiempo, id_producto).
- Atributos descriptivos: Detalles útiles para el análisis (nombre_producto, marca, año, mes).
Ejemplo:
- Tabla dim_producto:
  - id_producto, nombre, marca, categoría.

8. Establecer relaciones entre tablas

Definir las claves foráneas en la tabla de hechos para conectar con las tablas de dimensiones.
Asegurarse de mantener la integridad referencial.

9. Validar el modelo

Reunirse con los interesados para asegurarse de que las necesidades del negocio están cubiertas.
Ejecutar consultas de prueba para verificar que el modelo responde correctamente.

10. Optimizar el modelo

Desnormalizar si es necesario para mejorar el rendimiento de las consultas.
Crear índices en claves foráneas y columnas más consultadas.

Este enfoque garantiza que el modelo dimensional sea efectivo para análisis rápidos y centrado en el usuario final. 😊

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

Diseño de tablas en un modelo dimensional

Progreso del curso

Introducción a BI y Data Warehouse

Modelos dimensionales

ETL para inserción en Data Warehouse

Cierre

Recursos

Recursos