Data Analytics con Power BI

8.
Modelo de Datos:
Parte 1. Modelado Dimensional
Estructura Relacional
Normalización de datos
Hechos y Dimensiones
Arquitecturas Relacionales
CASO RAIVEN: diseño e
implementación con Power BI
BI Workflow
1 - Extracción de datos REPORTING

FUENTES DE DATOS PROCESOS ETL REPOSITORIO

DECISIONES
QUERYING

ANALYTICS
EXTRACCION
BI Workflow
2 - Modelización de datos REPORTING

FUENTES DE DATOS PROCESOS ETL REPOSITORIO

DECISIONES
QUERYING

ANALYTICS
MODELIZACIÓN
Modelo de Datos
A mitad de camino entre lo que pasa y lo pensamos que pasa

Procesos de Negocio Roles de Negocio

 Cómo ocurren las cosas Cómo pensamos las cosas 

 Cómo se identifican las cosas Cómo hablamos de las cosas 

Desacople
Traducción
Modelo de Datos
Capas de trabajo

PROCESOS ETL QUERYING

Modelado Dimensional Modelado Funcional

Se enfoca en la normalización y Desarrolla la lógica de relaciones, campos,
reorganización de las fuentes de datos según agrupaciones, cálculos y parámetros necesarios
el modelo lógico de la organización para el desenvolvimiento ágil de los analistas
Modelo de Datos
Capas de trabajo

PROCESOS ETL QUERYING

Modelado Dimensional
Se enfoca en la normalización y
reorganización de las fuentes de datos según
el modelo lógico de la organización
Modelo de Datos
Modelado Dimensional

PROCESOS ETL Modelado Dimensional

 Administración de fuentes
 Identificación de hechos y dimensiones
 Definición de estructura de tablas y relaciones
 Diseño y mantenimiento de la Arquitectura del
Modelo de Datos
 Administración de los procesos ETL
Modelo de Datos
Bases de Datos
Los repositorios son el núcleo de información desde el cual se alimentarán todas las
funciones del BI, por lo que su diseño tanto físico como conceptual resulta crítico para el
desempeño y la escalabilidad contra las demandas de los usuarios.

En los sistema de repositorio basados en bases de datos estructuradas la información

se agrupa en tablas temáticas, organizando internamente los datos en filas y columnas.

Los datos se actualizan, Base de Datos Tabla

expanden y eliminan a medida
que se agrega nueva
información.

Las bases de datos procesan

cargas de trabajo para crear y
actualizarse, consultar los
datos que contienen y ejecutar
aplicaciones contra él.
Bases de Datos
Estructura de una tabla relacional

Las Bases de Datos Relacionales son un Nombres de Campo Campos [Columnas] Tabla
tipo de repositorio en el cual los datos se
agrupan y organizan temáticamente por
tablas indexadas, lo que facilita la Campo Clave
búsqueda o consulta de información.

En la tabla cada registros o fila

representa a un elemento único del
conjunto por lo que es acompañado por
un campo Clave.

Los campos o columnas se usan para

describir las características propias de
cada elemento.

Registro [Fila] Valor del Campo

Bases de Datos
Modelo relacional

El Modelo Relacional propone una

arquitectura de la información basada
en tablas articuladas, cada una de las
cuales se especializa en agrupar
información sobre un ámbito en
específico.

Los campos de índices o claves son el

nexo que permite conectar a distintas
tablas entre sí para producir distintas
combinaciones de información según
el requerimiento de cada consulta.

La buena performance de un modelo

de datos relacional en términos de
mantenimiento y escalabilidad Base de Datos Relacional
requiere que las tablas de la base
sean normalizadas.
Modelado de Datos
Normalización

La normalización de las bases de datos busca cuidar 3 premisas

funcionales en el almacenamiento de la información:

Economía → Minimizar la cantidad de datos duplicados o

redundantes en el sistema
Sustentabilidad → Acompañar la evolución del negocio con
actualizaciones y modificaciones
Consistencia → Cuidar los nodos comunes de la información
entre distintas enfoques de consulta
Modelado de Datos
Normalización vs Denormalización

Tabla Denormalizada id_item cod_item item precio_unitario unitario_costo tipo subtipo

56 BB-CTT1L Cola TupTap 1 Ltr 42,5 28,67 BEBIDAS GASESOSAS
61 BB-LL-01 Limonada LemonShh 27,96 12,72 BEBIDAS SABORIZADAS
62 GL-CT-02 Caramelos Trix 0,75 0,34 GOLOSINAS CARAMELOS
67 GL-CHA-05 Chicles Adax 0,35 0,21 GOLOSINAS CHICLES
57 BB-CTTB Cola TupTap botellita 23,5 15,05 BEBIDAS GASESOSAS
68 HL-CG-01 Cono Gol 78,2 50,62 HELADOS CREMOSOS
63 GL-CED-01 Caramelos El Dulce 0,55 0,13 GOLOSINAS CARAMELOS

Tablas Normalizadas

id_item cod_item item precio_unitario unitario_costo id_subtipo id_subtipo id_tipo subtipo id_tipo tipo
56 BB-CTT1L Cola TupTap 1 Ltr 42,5 28,67 1 1 1 GASESOSAS 1 BEBIDAS
61 BB-LL-01 Limonada LemonShh 27,96 12,72 2 2 1 SABORIZADAS 2 GOLOSINAS
62 GL-CT-02 Caramelos Trix 0,75 0,34 3 3 2 CARAMELOS 3 HELADOS
67 GL-CHA-05 Chicles Adax 0,35 0,21 4 4 2 CHICLES
57 BB-CTTB Cola TupTap botellita 23,5 15,05 1 CLAVE
5 3 CREMOSOS PRIMARIA
68 HL-CG-01 Cono Gol 78,2 50,62 5
CLAVE
63 GL-CED-01 Caramelos El Dulce 0,55 0,13 3 PRIMARIA
CLAVE CLAVE CLAVE
PRIMARIA FORÁNEA FORÁNEA
Modelado de Datos
Paradigmas de diseño

Normalización Denormalización

Orientado al administrador Orientado al Usuario

Relaciones complejas de la información Relaciones simples de la información

Procesamiento rápido de actualizaciones Procesamiento rápido de la información

Economía de Datos Abundancia de información

¿Cuál te imaginas que es el modelo óptimo?

Modelado de Datos
Reglas de Normalización: 1) Primera Forma Normal (FN1)

1. Los registros de una tabla deben ser únicos

2. Cada atributo/columna tiene un único valor asignado
3. Cada atributo debe representar claramente un concepto del grupo al que pertenece
Id Empresa Empresa Destino_A Destino_B
1 La brillante Buenos Aires Lima
2 Melcon e Hijos Lima La Paz
3 Transponder SRL Lima, La Paz
Tabla Destinos

Id Destino Id Empresa Destino

Tabla Empresas Id Empresa Empresa 1 1 Buenos Aires
1 La brillante 2 1 Lima
2 Melcon e Hijos 3 2 Lima
3 Transponder SRL 4 2 La Paz
5 3 Lima
6 3 La Paz
Modelado de Datos
Reglas de Normalización: 2) Segunda Forma Normal (FN2)

El valor de campo/atributo debe depender únicamente de la llave primaria de la tabla

Tabla Empresa Destinos

Id Destino Id Empresa Destino Id Destino Id Empresa Id Ciudad Id Ciudad Ciudad
1 1 Buenos Aires 1 1 1 1 Buenos Aires
2 1 Lima 2 1 2 2 Lima
3 2 Lima 3 2 3 3 La Paz
4 2 La Paz 4 2 3
5 3 Lima 5 3 2 Tabla Ciudad
6 3 La Paz 6 3 3

Tabla Empresa Destinos

La tabla Ciudad podría ser mejor
desarrollada para aportar datos
sobre la ciudad
Modelado de Datos
Proceso de Normalización: FN1 + FN2
Tabla Denormalizada
Id Empresa Empresa Destino_A Destino_B
1 La brillante Buenos Aires Lima
2 Melcon e Hijos Lima La Paz
3 Transponder SRL Lima, La Paz

Tablas Normalizadas

Id Empresa Empresa Id Destino Id Empresa Id Ciudad Id Ciudad Ciudad

1 La brillante 1 1 1 1 Buenos Aires
2 Melcon e Hijos 2 1 2 2 Lima
3 Transponder SRL 3 2 3 3 La Paz
4 2 3
Tabla Empresas 5 3 2 Tabla Ciudad
6 3 3

Tabla Empresa Destinos

Modelado de Datos
Reglas de Normalización: 3) Tercera Forma Normal (FN3)

Ningún atributo No Clave debe depender de otro atributo No Clave

Id Factura Item Precio Impuestos Total

1 Galletitas 105 127,1 232,1
2 Helados 30 36,3 66,3
3 Golosinas 55 66,6 121,6

Id Factura Item Precio Impuestos

1 Galletitas 105 127,1 Si bien esta regla es eficiente en términos de
2 Helados 30 36,3 minimización de datos redundantes puede ser
3 Golosinas 55 66,6 omitida a cambio de mayor performance de
velocidad cuando el espacio de almacenamiento no
es una restricción
Modelado de Datos
Modelo Dimensional

Los criterios de Normalización nos ofrecen un abanico amplio de

posibilidades para el diseño de un sistema relacional de datos.
El modelado dimensional propone una arquitectura en donde
los grupos de datos se organizan en 2 tipos:

Tablas de Hechos Tablas Maestras ó de Dimensiones

(Fact Tables) (Look Up Tables)
Son las que almacenan los eventos/instancias Son las que almacenan los atributos que
de los procesos que se siguen. caracterizan a los eventos

Características: Ejemplos: Características: Ejemplos:

• Identifican a los eventos • Ventas • Caracterizan a los eventos • Clientes
• Campos de Medidas • Inventarios • Campos de Atributos • Productos
• Sirven para Agregar la • Facturación • Sirven para Agrupar la • Geografías
información • Envíos información • Tiempo
Modelado Dimensional
Tablas de Hechos / Fact Tables

Las Tablas de Hechos son las tablas más importantes del modelo ya que son las que
contienen los datos que alimentarán las métricas que necesita medir el negocio.

 Cada tabla de hecho se relaciona a un proceso único y específico del negocio

 Suelen almacenar los datos base provenientes de los sistemas transaccionales (OLTP)
 No suelen contener medidas de tipo texto excepto códigos de identificación
 Los hechos suelen ser numéricos y agregables (suma, cuenta, promedio, etc)
 Están pensadas para la construcción de métricas actuales y futuras
 Todas las medidas de una tabla de hechos deben tener la misma “Granularidad”
 Las tablas de hechos suelen ser las más grandes (95% de la ocupación de la base)
Modelado Dimensional
Tablas de Hechos / Fact Tables
Existen múltiples tipos de Tablas de Hechos:

TRANSACCIONALES ACUMULATIVAS AGREGADAS

Pensadas para la identificación de la Pensadas para el seguimiento Pensadas para la agrupación de
mínima unidad descriptiva del evento en el tiempo de cada evento muchos eventos transaccionales por
(máxima granularidad) individual conceptos (Ej: Tiempo)
(menor granularidad)
Ventas Instalaciones Ventas Mes Canal
Id Venta (PK) Id Venta (PK) Mes
Fecha Cliente (FK) Canal (FK)
Item (FK) Fecha Pedido Monto
Cliente (FK) Fecha Instalación Impuestos
Canal (FK) Fecha Cancelación Monto Total
Monto Estado
Impuestos
Monto Total
Modelado Dimensional
Tablas de Hechos: Granularidad

La granularidad es el nivel de detalle con el que se

registrarán los eventos en una tabla de hechos.
Por ejemplo:
Un inventario de una empresa podría ser registrado en
distintos niveles de granularidad

Por producto, por día

Por producto, por día, por almacén

Por categoria de producto, por mes

El concepto de ‘granularidad’ está
directamente relacionado con el de
‘desagregación’ de los datos
Modelado Dimensional
Tablas de Dimensiones / Maestras / Look Up Tables

Las tablas maestras ó de dimensiones son las que nos permiten administrar
los distintos atributos con los que se caracterizan a los hechos:

 Cada tabla es acompañada de una llave primaria.

 Cada atributo de la tabla describe un aspecto relevante del negocio.
 Los valores suelen ser de tipo texto y se eligen sean palabras claras y concretas.
 Al agrupar los atributos en una tabla permite mayor facilidad para los administradores del sistema
en la actualización o redefinición ágil de los mismos.
 Cada campo atributo puede ser a su vez subdimensionado en otros campos para proveer de
mejor nivel de detalle a las métricas de la analítica.
 Puede ser práctico aceptar cierto nivel de denormalización.
 Suelen estar pobladas por pocas filas y no se esperan cambios frecuentes.
Modelado Dimensional
Tablas de Dimensiones o Maestras / Look Up Tables
Ejemplos:
(Totalmente DENORMALIZADA)

Dimensión Clientes Canal Región

Id Cliente (PK) Id Canal (PK) Id Region (PK)
Nombre Nombre Canal Región
Apellido Razón Social Provincia
DNI CUIT Pais
Atributos
Domicilio Domicilio
CUIT Tipo (FK)
Condicion (FK) Región (FK)

(Totalmente NORMALIZADA) El grado de Normalización con el que se

deciden diseñar las Tablas Maestras
depende del criterio de simplicidad que
se prefiera en el sistema
Modelado Dimensional
Tablas de Dimensiones: Jerarquías

La denormalización de las Dimensiones produce

cadenas jerárquicas en el modelo de datos:

Ej: Tablas Geográficas Ej: Tablas de Tiempo Año

País
Trimestre

Dimensión
Región Mes Padre

Semana
Ciudad Dimensión
Hija

Día
Modelado Dimensional
Navegabilidad
La definición conjunta de dimensiones, jerarquías y granularidades
determinará las posibilidades del modelo para agregar o desagregar la
información en los distintos niveles de detalles según sea requerido.

País Drill Up
Mayor agrupación
Dimensiones de la información
Geográficas Región
Jerarquizadas

Ciudad Drill Down

Mayor detalle de la
información

Tablas de Un buen modelado dimensional es

hechos Ventas Cobros CRM crítico para el descubrimiento de los
Data Insights
Modelado Dimensional
Tipos de Tablas / Nomenclaturas / Prefijos

Transaccionales / Base (BT_)

Guardan los hechos al máximo nivel de detalle
De Hechos / Fact Tables
Identificación y cuantificación
de eventos
Agregadas / Aggregate (AGG_)
Exclusivamente para reducir el tiempo
de respuesta de las consultas
De Dimensiones ó Maestras /
Tablas Look Up Tables (LK_)
Para descripción cualitativa y
agrupación de los eventos
Nombrar adecuadamente cada tabla
Pivote / Relational Tables (REL_) de nuestro sistema ayuda a la
Se utilizan cuando hay una relación interpretación y administración ágil
muchos a muchos entre dos atributos del mismo
Modelado Dimensional
Diagrama Entidad Relación (DER)

Los Modelos DER son muy utilizados para

el diseño de sistemas de bases de datos
relacionales ya que permiten entender
rápidamente cómo se vinculan las
distintas tablas de un modelo de datos,
identificando claves y atributos.

¿Podrías distinguir cuáles

tablas son de hechos y cuales
son de dimensiones? Y
jerarquías?
Modelado Dimensional
Diseño: una cuestión de criterio…

A considerar…
→ El modelado dimensional se implementa de manera tal de
balancear el diseño de tablas entre totalmente NORMALIZADO y
totalmente DENORMALIZADO.
El diseño que debemos buscar…
→ El objetivo funcional es lograr sentar las bases para el análisis
dinámico de los datos mediante el entrecruzamiento de  Fácil de administrar
dimensiones y la agrupación de la información en distintos niveles.
→ Se busca un modelo simple pero tan complejo como el negocio lo  Veloz para el querying
requiera.  Flexible para la navegación
→ La utilización de tablas de hechos agregadas para reportes analítica
periódicos suma velocidad al diseño.
→ La simplicidad siempre permite adaptarse a los cambios de manera
más rápida.
Modelado Dimensional
Arquitecturas: ESTRELLA / STAR SCHEMA

CANALES FACTURACION

VENTAS

Fact Table
CLIENTES PRODUCTOS
Look Up Table

 Una tabla por dimensión (Dimensiones DENORMALIZADAS).

 Hay menos tablas involucradas.
 Los queries son más fáciles de construir.
 Los queries son resueltos más rápidos porque se necesita acceder a menos tablas
Modelado Dimensional
Arquitecturas: COPO DE NIEVE / SNOW FLAKE

VENDEDORES
CANALES FACTURACION

CATEGORIAS

VENTAS
GEOGRAFIA

SUBCATEGORIAS
CLIENTES PRODUCTOS
Dimensión
Conformada

 Varias tablas por dimensión (Dimensiones NORMALIZADAS). Fact Table

 Son más fáciles de administrar pero más difícil de entender para el usuario final.
 Look Up Table
Mayor número de tablas.
 Más lento para traer datos.
Práctica
Modelado Dimensional - Fundamentos

– Análisis de caso
– Identificación de Hechos y Dimensiones

Ver ejercicios
modelado/1_fundamentos/
Práctica
Modelado Dimensional con Power BI

– Análisis de fuentes Denormalizadas

– Identificación de Procesos
– Categorización en Hechos y Dimensiones
– Definición de granularidad y jerarquías Ver ejercicios
– Creación de tablas y normalización de nomenclaturas
– Creación de índices Modelado/2_caso_raiven/
– Arquitectura de relaciones entre tablas
– Reconstrucción de la información original
Modelado Dimensional
Caso RAIVEN

Ravien Consulting es
una consultora de
negocios que provee
servicios de consultoría
en toda América y España

RAIVEN nos ha encargado construir el Modelo

de Datos de su Negocio para lo cual vamos a
tener que diseñar e implementar el Modelo
Dimensional del mismo
Modelado Dimensional Caso Raiven
Etapas de trabajo

1 2

Diseño del Modelo Implementación ETL

Se realiza el desarrollo conceptual de las Se desarrollan todos los procesos ETL para
distintas tablas de hechos, dimensiones, la implementación bajada a tierra del
atributos y jerarquías que harán a la modelo dimensional a un sistema de
estructura del Modelo de Datos tablas físicas
Modelado Dimensional Caso Raiven
Etapas de trabajo

1. Análisis de los Procesos de Negocio

2. Exploración de las Fuentes

3. Modelado de Hechos y Dimensiones

Diseño del Modelo
4. Arquitectura del Modelo de Datos

1. Análisis del Contexto

2. Definición de Arquitectura ETL

3. Extracción y Normalización de Hechos y Dimensiones

Implementación ETL 4. Administración y Controles

5. Optimización
Modelado Dimensional Caso Raiven
Etapas de trabajo

1. Análisis de los Procesos de Negocio

2. Exploración de las Fuentes

3. Modelado de Hechos y Dimensiones

Diseño del Modelo
4. Arquitectura del Modelo de Datos

1. Análisis del Contexto

2. Definición de Arquitectura ETL

3. Extracción y Normalización de Hechos y Dimensiones

Implementación ETL 4. Administración y Controles

5. Optimización
Modelado Dimensional Caso Raiven
1. Análisis del Proceso de Negocio

El circuito de trabajo de la organización comienza cuando se confecciona la

factura de un servicio vendido. La venta la realizan los vendedores a partir de
1. Identificar los Hechos
la llegada de clientes desde los diversos canales de promoción que la firma
tiene en internet, medios especializados, referidos y otros. Los clientes estar
localizados en distintas ciudades de América y España.
2. Identificar las Dimensiones
Cada factura dispara el comienzo del servicio de consultoría el cual es llevado
a cabo por las distintas Unidades de Negocios que tiene la firma en distintos
países. Las Unidades de Negocio (UN) son las responsables de asentar los
costos de cada proyecto.

A lo largo del servicio se van realizando instancias de cobranzas a los clientes Ver archivo
hasta que finalmente se cubre el saldo total de la factura.
0_caso_raiven.docx
Modelado Dimensional Caso Raiven
Etapas de trabajo

1. Análisis de los Procesos de Negocio

2. Exploración de las Fuentes

3. Modelado de Hechos y Dimensiones

Diseño del Modelo
4. Arquitectura del Modelo de Datos

1. Análisis del Contexto

2. Definición de Arquitectura ETL

3. Extracción y Normalización de Hechos y Dimensiones

Implementación ETL 4. Administración y Controles

5. Optimización
Modelado Dimensional
2. Exploración de fuentes
El equipo de Soporte IT de Raiven nos ha realizado bajadas de datos
sobre los distintos procesos de la firma.
1. Levantar la información mediante Query Editor
2. Estudiar las fuentes de datos identificando todos los elementos
3. Identificar los elementos que describen hechos
4. Por cada grupo de elementos de hechos se identificar atributos y grupos de dimensiones:
-Sirve para identificar un evento  hecho
-Sirve para describir a un evento?  atributo
-Los atributos son agrupables en una categoría común?  dimensión

4. Una vez relevados cada ecosistema de hechos se unificar los atributos dimensionales
requeridos por el modelo total
Ver archivo
5. Estudiar todos los conceptos que son relevantes al modelo
6. Comprender y nombrar los atributos y se busca una terminología adecuada y común 1_bajada_raiven.xlsx
7. De dónde se extrae la información para las dimensiones?
Modelado Dimensional Caso Raiven
Etapas de trabajo

1. Análisis de los Procesos de Negocio

2. Exploración de las Fuentes

3. Modelado de Hechos y Dimensiones

Diseño del Modelo
4. Arquitectura del Modelo de Datos

1. Análisis del Contexto

2. Definición de Arquitectura ETL

3. Extracción y Normalización de Hechos y Dimensiones

Implementación ETL 4. Administración y Controles

5. Optimización
Modelado Dimensional Caso Raiven
3. Modelado de Hechos y Dimensiones
Con los resultados de nuestra primer exploración identificar la estructura de Hechos,
Dimensiones, Atributos y Jerarquías que caracterizarán al Modelo de Datos de RAIVEN.

1. Identificar los Hechos

2. Identificar las Dimensiones y

sus Atributos

3. Esquematizar el Modelo
Dimensional
Modelado Dimensional Caso Raiven
3.1 Identificación y separación de Hechos y Dimensiones

Dimensiones

Unidad Operativa
Cliente Hechos Id Unidad Operativa (PK)
Id Cliente (PK) Descripcion
Nombre
Pais
Servicios
Ciudad
Id Servicio (PK)
Fecha Inicio
Fecha Fin
Costo Operacion
Id Unidad Operativa (FK)
Id Cliente (FK)
Modelado Dimensional Caso Raiven
3.1 Identificación y separación de Hechos y Dimensiones

Cliente
Id Cliente (PK)
Nombre

Cobranzas Facturación
Id Cobro (PK) Id Factura (PK)
Fecha
Monto Cobrado
Id Cliente (FK)
Id Factura (FK)
Modelado Dimensional Caso Raiven
3.1 Identificación y separación de Hechos y Dimensiones

Cliente Canal Vendedor

Id Cliente (PK) Id Canal (PK) Id Vendedor (PK)
Nombre Descripcion Nombre
Tipo Contrato
Rubro
Locacion Venta
Id Venta (PK)
Fecha
Monto Sin Impuestos
Monto Impuestos
Monto Total
Id Cliente (FK)
Id Vendedor (FK)
Id Canal (FK)
Modelado Dimensional Caso Raiven
3.2 Consolidación de Dimensiones

1) Identificación de atributos y dimensiones 2) Unificación 3) Normalización

Cliente País
Cobros •Id País (PK)
•Id Cliente (PK)
•Nombre •País

Cliente Cliente
Facturación •Id Cliente (PK)
Cliente Ciudad
•Id Cliente (PK) •Id Cliente (PK) •Id Ciudad (PK)
•Nombre
•Nombre •Nombre •Ciudad
•Tipo
•Tipo •Id Tipo (FK) •Id Pais (FK)
•Rubro
•Rubro •Id Rubro (FK)
•Pais
•Locacion •Id Ciudad (FK)
•Ciudad
Rubro
Cliente •Id Rubro (PK)
Operaciones •Id Cliente (PK) •Descripcion
Tipo
•Nombre •Id Tipo (PK)
•Pais •Descripcion
•Ciudad
Modelado Dimensional Caso Raiven
Etapas de trabajo

1. Análisis de los Procesos de Negocio

2. Exploración de las Fuentes

3. Modelado de Hechos y Dimensiones

Diseño del Modelo
4. Arquitectura del Modelo de Datos

1. Análisis del Contexto

2. Definición de Arquitectura ETL

3. Extracción y Normalización de Hechos y Dimensiones

Implementación ETL 4. Administración y Controles

5. Optimización
Tarea
Armar el modelo dimensional de RAIVEN