Modelar para Power BI (II): del data warehouse al modelo semántico

En la primera parte de esta serie dejamos resuelto lo más difícil: qué quiere medir el negocio y cómo se define cada indicador, sin ambigüedades. Con ese mapa de KPIs ya sabemos qué datos hacen falta. Ahora toca construir: derivar el datamart desde el data warehouse, diseñar su estructura y convertirla en un modelo semántico de Power BI que cualquier usuario pueda explorar con confianza.

Seguimos con el ejemplo de una aseguradora y, al final, lo aplicamos todo a un caso completo de siniestros de autos.

Derivar el datamart desde el data warehouse

Cuando la compañía ya tiene un data warehouse corporativo (sobre Exadata, Cloudera, Azure Synapse o Microsoft Fabric), la estrategia recomendada es top-down: construir el datamart a partir de ese almacén central. Son cinco pasos:

1. Seleccionar y filtrar. Solo las tablas y campos que necesita el área: para siniestros, reclamaciones, pólizas relacionadas y asegurados, no datos de recursos humanos. Y solo las filas útiles: los últimos diez años y las líneas de negocio relevantes.
2. Transformar para el contexto. Limpiezas propias del área y campos que facilitan el análisis, como la categoría de gravedad del siniestro (leve, moderado, grave) o el número de siniestros por póliza. El equilibrio está en adelantar cálculos sin perder flexibilidad.
3. Agregar donde compense. Si casi todos los informes trabajan a nivel mensual por producto, una tabla agregada o una vista materializada acelera muchísimo las consultas.
4. Cargar de forma incremental. Una carga inicial con todo el histórico y, después, cargas periódicas que traen solo las novedades: los siniestros del día anterior, las pólizas nuevas o canceladas.
5. Aislar el entorno. El datamart vive en un esquema o base de datos propios, con sus permisos e índices, normalmente en la misma plataforma que el almacén para no mover grandes volúmenes.

La ventaja es clara en un sector regulado como el asegurador, donde primas, reservas y siniestros se auditan: el datamart parte de datos ya validados y conciliados, todos los informes salen de la misma base y el linaje se puede seguir desde el origen hasta el cuadro de mando. Si el data warehouse es la despensa de toda la casa, el datamart es la bandeja con los ingredientes justos para una receta, en sus porciones y con alguno ya precocinado.

Diseño dimensional: hechos, dimensiones y granularidad

La mayoría de datamarts analíticos siguen el modelado dimensional de Kimball, que organiza los datos en dos tipos de tablas:

• Tabla de hechos: el centro del modelo. Cada fila es un evento de negocio y contiene medidas numéricas (importes, conteos) y claves hacia las dimensiones.
• Dimensiones: las tablas descriptivas que dan contexto (quién, qué, cuándo, dónde, por qué) y permiten filtrar y agrupar.

Se relacionan de uno a muchos: cada fila de una dimensión describe muchos hechos. El diseño se hace en tres pasos.

Paso 1: decidir la granularidad

La granularidad es el nivel de detalle de cada fila de la tabla de hechos, y es la primera decisión. En un datamart de siniestros:

• ¿Cada fila es un siniestro? Probablemente sí, si queremos contar siniestros, ver el detalle de cada uno y buscar patrones.
• ¿Hace falta más detalle? Si un siniestro afecta a varias coberturas, la unidad podría ser «cobertura dentro de un siniestro», aunque lo habitual es trabajar con el siniestro consolidado.
• ¿Podríamos agregar más? Una fila por mes y producto aligera el modelo, pero elimina el análisis diario y caso a caso.

La regla general: almacenar el nivel más bajo que se vaya a analizar o que haga falta para recalcular los KPIs. Agregar después en las consultas siempre es posible; recuperar el detalle perdido, no. En nuestro ejemplo, la granularidad será el siniestro individual, identificado por su número.

Paso 2: definir la tabla de hechos

La tabla Hechos_Siniestros reuniría:

Tipo de campo	Ejemplos
Identificadores y claves	SiniestroID; claves de póliza, cliente, producto, causa, taller y provincia
Medidas numéricas	ImportePagado (indemnización), ReservaConstituida (provisión técnica), NumReclamaciones
Fechas	FechaOcurrencia, FechaApertura y FechaCierre (vacía mientras esté abierto)

Paso 3: elegir las dimensiones

Cada «según qué» de las preguntas de negocio («¿cómo varía el coste según la provincia?») es una dimensión:

• Tiempo: el calendario con año, trimestre, mes y día. Prácticamente obligatoria.
• Producto: ramos y tipos de póliza con su jerarquía (Auto dentro de Seguros generales).
• Cliente: tipo, canal de venta, edad, antigüedad, perfil de riesgo.
• Geografía: país, provincia y municipio del siniestro o del asegurado.
• Causa: colisión, robo, incendio; en salud, el tipo de prestación.
• Proveedor: talleres u hospitales, para analizar el gasto y renegociar.

El resultado es un esquema en estrella: la tabla de hechos en el centro y las dimensiones conectadas directamente, en una sola capa. Las jerarquías se guardan como columnas de la propia dimensión en lugar de repartirse en varias tablas; el copo de nieve se deja para casos extremos.

Buenas prácticas de diseño

• Claves numéricas (surrogate keys). Las relaciones van por identificadores enteros, no por textos: ocupan menos y las uniones son más rápidas.
• Un registro «desconocido» en cada dimensión. Por ejemplo, ProductoID = −1. Los hechos sin correspondencia apuntan a él, se evitan nulos y se mantiene la integridad.
• Cada atributo en su dimensión. Si el ramo depende del producto, va en Producto, no repetido en Cliente.
• Documentar las derivaciones. Un campo calculado como el indicador de siniestro grave lleva su lógica documentada.

Y una regla que lo resume todo: el diseño responde a los KPIs. Si la siniestralidad es siniestros pagados entre primas, el modelo necesita las dos cosas, normalmente con una segunda tabla de hechos de primas que comparta dimensiones con la de siniestros.

Del datamart al modelo semántico en Power BI

Con las tablas creadas y cargadas, construimos el modelo semántico: la capa que traduce el datamart al lenguaje de quienes hacen informes.

1. Conectar e importar. Modo Import si el volumen es manejable; si el datamart está en Microsoft Fabric, Direct Lake da un rendimiento parecido sin copiar datos.
2. Renombrar para las personas. Hechos_Siniestros pasa a ser «Siniestros» y Dim_Producto, «Producto», con espacios y acentos.
3. Relacionar. Dimensiones a hechos, de uno a muchos y con filtro en una sola dirección.
4. Crear las medidas DAX. Una por cada indicador del mapa de KPIs.
5. Ordenar. Jerarquías (año, trimestre, mes, día), campos técnicos ocultos y formatos de moneda y porcentaje.
6. Verificar y publicar. Comprobar que las cifras cuadran con la fuente y publicar el modelo como conjunto de datos central para toda la organización.

Las medidas DAX: el corazón del modelo

Las medidas expresan las fórmulas acordadas, de forma que cualquier visual muestra el mismo valor:

Nº Siniestros = COUNTROWS ( Siniestros )
Total Pagado = SUM ( Siniestros[Importe Pagado] )
Coste Medio = DIVIDE ( [Total Pagado], [Nº Siniestros] )

% Siniestralidad = DIVIDE ( [Total Pagado], [Total Primas Devengadas] )

Tasa de Cierre =
DIVIDE (
    CALCULATE ( [Nº Siniestros], Siniestros[Estado] = "Cerrado" ),
    [Nº Siniestros]
)

Variación Interanual Pagado =
VAR Actual = [Total Pagado]
VAR Anterior = CALCULATE ( [Total Pagado], SAMEPERIODLASTYEAR ( 'Fecha'[Fecha] ) )
RETURN DIVIDE ( Actual - Anterior, Anterior )

Fíjate en que las medidas complejas se construyen sobre las básicas: si mañana cambia la definición de «Total Pagado», todas las demás la heredan.

¿Dónde calcular cada KPI?

Es una de las decisiones con más impacto en el rendimiento y en el gobierno del modelo:

	En el datamart (SQL)	En el modelo (DAX)
Cuándo	Cálculos pesados sobre grandes volúmenes, lógica que une varias fuentes, indicadores que usan otras herramientas, agregados de periodos largos que casi no cambian	Combinaciones de medidas que deben recalcularse con cada filtro y la máxima flexibilidad de análisis
Ejemplo	Indicadores regulatorios; una tabla mensual por ramo con la siniestralidad precalculada	La siniestralidad que responde al filtrar por producto, año o provincia sin precalcular cada combinación

La guía práctica: si el KPI es una agregación de la tabla de hechos o una combinación sencilla de medidas, en DAX. Si necesita juntar fuentes o recorrer grandes históricos, en el datamart. Mantener el datamart simple y dejar la personalización al modelo da lo mejor de los dos mundos.

Caso práctico: siniestros de autos

El área de Siniestros de Autos quiere entender la evolución de reclamaciones y costes para gestionar mejor las indemnizaciones. Participan la dirección de siniestros, el responsable de indemnizaciones, el equipo antifraude y el control financiero. Se analizan cinco años de historia con una carga diaria incremental.

Las preguntas de negocio son seis: cuántos siniestros hay al mes y cómo evoluciona el importe pagado; cómo se reparten por tipo y gravedad; cuál es el coste medio por provincia; qué talleres concentran más gasto; qué porcentaje tiene fraude detectado; y cuánto se tarda en resolver cada tipo de siniestro.

1. KPIs definidos en el mapa: nº de siniestros, total pagado, coste medio, % de fraude y tiempo medio de cierre.
2. Datos de origen: siniestros y pólizas del data warehouse corporativo, con sus dimensiones de tiempo, causa, taller, geografía, producto y cliente.
3. Datamart: un esquema propio con la tabla de siniestros de Auto (filtrada por producto) y sus dimensiones. El tiempo de cierre en días se calcula en SQL, porque es un atributo fijo de cada siniestro.
4. Modelo dimensional: estrella con granularidad de siniestro individual y seis dimensiones.
5. Modelo de Power BI: relaciones, medidas DAX y jerarquías, y un informe con la tendencia mensual, el análisis por causa, el mapa por provincias y tarjetas con los KPIs.

Lista de comprobación

• ¿Cada KPI del informe está en el mapa de indicadores, con su fórmula y sus casos especiales?
• ¿La granularidad permite responder a todas las preguntas de negocio?
• ¿El modelo es una estrella, con claves numéricas y registro «desconocido» en cada dimensión?
• ¿Cada medida se define una sola vez y las demás se construyen sobre ella?
• ¿Las cifras cuadran con la fuente antes de publicar?

Modelar bien es lo que separa un cuadro de mando que se usa de uno en el que nadie confía. Si quieres profundizar en el rendimiento del modelo, consulta nuestras buenas prácticas de modelado de datos en Power BI. Y si estás preparando un datamart o revisando un modelo que no cuadra, en Bidatia diseñamos cuadros de mando con Power BI y Microsoft Fabric desde el mapa de indicadores hasta el informe final.