Modelar per a Power BI (II): del data warehouse al model semàntic

A la primera part d'aquesta sèrie vam deixar resolt el més difícil: què vol mesurar el negoci i com es defineix cada indicador, sense ambigüitats. Amb aquest mapa de KPI ja sabem quines dades calen. Ara toca construir: derivar el datamart des del data warehouse, dissenyar-ne l'estructura i convertir-la en un model semàntic de Power BI que qualsevol usuari pugui explorar amb confiança.

Seguim amb l'exemple d'una asseguradora i, al final, ho apliquem tot a un cas complet de sinistres d'automòbils.

Derivar el datamart des del data warehouse

Quan la companyia ja té un data warehouse corporatiu (sobre Exadata, Cloudera, Azure Synapse o Microsoft Fabric), l'estratègia recomanada és top-down: construir el datamart a partir d'aquest magatzem central. Són cinc passos:

1. Seleccionar i filtrar. Només les taules i els camps que necessita l'àrea: per a sinistres, reclamacions, pòlisses relacionades i assegurats, no dades de recursos humans. I només les files útils: els darrers deu anys i les línies de negoci rellevants.
2. Transformar per al context. Neteges pròpies de l'àrea i camps que faciliten l'anàlisi, com la categoria de gravetat del sinistre (lleu, moderat, greu) o el nombre de sinistres per pòlissa. L'equilibri és avançar càlculs sense perdre flexibilitat.
3. Agregar on compensi. Si gairebé tots els informes treballen a nivell mensual per producte, una taula agregada o una vista materialitzada accelera moltíssim les consultes.
4. Carregar de manera incremental. Una càrrega inicial amb tot l'històric i, després, càrregues periòdiques que porten només les novetats: els sinistres del dia anterior, les pòlisses noves o cancel·lades.
5. Aïllar l'entorn. El datamart viu en un esquema o una base de dades propis, amb els seus permisos i índexs, normalment a la mateixa plataforma que el magatzem per no moure grans volums.

L'avantatge és clar en un sector regulat com l'assegurador, on primes, reserves i sinistres s'auditen: el datamart parteix de dades ja validades i conciliades, tots els informes surten de la mateixa base i el llinatge es pot seguir des de l'origen fins al quadre de comandament. Si el data warehouse és el rebost de tota la casa, el datamart és la safata amb els ingredients justos per a una recepta, en les seves porcions i amb algun ja precuinat.

Disseny dimensional: fets, dimensions i granularitat

La majoria de datamarts analítics segueixen el modelatge dimensional de Kimball, que organitza les dades en dos tipus de taules:

• Taula de fets: el centre del model. Cada fila és un esdeveniment de negoci i conté mesures numèriques (imports, recomptes) i claus cap a les dimensions.
• Dimensions: les taules descriptives que donen context (qui, què, quan, on, per què) i permeten filtrar i agrupar.

Es relacionen d'u a molts: cada fila d'una dimensió descriu molts fets. El disseny es fa en tres passos.

Pas 1: decidir la granularitat

La granularitat és el nivell de detall de cada fila de la taula de fets, i és la primera decisió. En un datamart de sinistres:

• Cada fila és un sinistre? Probablement sí, si volem comptar sinistres, veure el detall de cadascun i buscar patrons.
• Cal més detall? Si un sinistre afecta diverses cobertures, la unitat podria ser «cobertura dins d'un sinistre», tot i que l'habitual és treballar amb el sinistre consolidat.
• Podríem agregar més? Una fila per mes i producte alleugereix el model, però elimina l'anàlisi diària i cas per cas.

La regla general: emmagatzemar el nivell més baix que s'hagi d'analitzar o que calgui per recalcular els KPI. Agregar després a les consultes sempre és possible; recuperar el detall perdut, no. En el nostre exemple, la granularitat serà el sinistre individual, identificat pel seu número.

Pas 2: definir la taula de fets

La taula Fets_Sinistres reuniria:

Tipus de camp	Exemples
Identificadors i claus	SinistreID; claus de pòlissa, client, producte, causa, taller i província
Mesures numèriques	ImportPagat (indemnització), ReservaConstituida (provisió tècnica), NumReclamacions
Dates	DataOcurrencia, DataObertura i DataTancament (buida mentre estigui obert)

Pas 3: triar les dimensions

Cada «segons què» de les preguntes de negoci («com varia el cost segons la província?») és una dimensió:

• Temps: el calendari amb any, trimestre, mes i dia. Pràcticament obligatòria.
• Producte: rams i tipus de pòlissa amb la seva jerarquia (Auto dins d'Assegurances generals).
• Client: tipus, canal de venda, edat, antiguitat, perfil de risc.
• Geografia: país, província i municipi del sinistre o de l'assegurat.
• Causa: col·lisió, robatori, incendi; en salut, el tipus de prestació.
• Proveïdor: tallers o hospitals, per analitzar la despesa i renegociar.

El resultat és un esquema en estrella: la taula de fets al centre i les dimensions connectades directament, en una sola capa. Les jerarquies es desen com a columnes de la mateixa dimensió en lloc de repartir-se en diverses taules; el floc de neu es deixa per a casos extrems.

Bones pràctiques de disseny

• Claus numèriques (surrogate keys). Les relacions van per identificadors enters, no per textos: ocupen menys i les unions són més ràpides.
• Un registre «desconegut» a cada dimensió. Per exemple, ProducteID = −1. Els fets sense correspondència hi apunten, s'eviten nuls i es manté la integritat.
• Cada atribut a la seva dimensió. Si el ram depèn del producte, va a Producte, no repetit a Client.
• Documentar les derivacions. Un camp calculat com l'indicador de sinistre greu porta la seva lògica documentada.

I una regla que ho resumeix tot: el disseny respon als KPI. Si la sinistralitat és sinistres pagats entre primes, el model necessita totes dues coses, normalment amb una segona taula de fets de primes que comparteixi dimensions amb la de sinistres.

Del datamart al model semàntic a Power BI

Amb les taules creades i carregades, construïm el model semàntic: la capa que tradueix el datamart al llenguatge de qui fa informes.

1. Connectar i importar. Mode Import si el volum és manejable; si el datamart és a Microsoft Fabric, Direct Lake dona un rendiment semblant sense copiar dades.
2. Reanomenar per a les persones. Fets_Sinistres passa a ser «Sinistres» i Dim_Producte, «Producte», amb espais i accents.
3. Relacionar. Dimensions a fets, d'u a molts i amb filtre en una sola direcció.
4. Crear les mesures DAX. Una per cada indicador del mapa de KPI.
5. Endreçar. Jerarquies (any, trimestre, mes, dia), camps tècnics ocults i formats de moneda i percentatge.
6. Verificar i publicar. Comprovar que les xifres quadren amb la font i publicar el model com a conjunt de dades central per a tota l'organització.

Les mesures DAX: el cor del model

Les mesures expressen les fórmules acordades, de manera que qualsevol visual mostra el mateix valor:

Núm. Sinistres = COUNTROWS ( Sinistres )
Total Pagat = SUM ( Sinistres[Import Pagat] )
Cost Mitjà = DIVIDE ( [Total Pagat], [Núm. Sinistres] )

% Sinistralitat = DIVIDE ( [Total Pagat], [Total Primes Meritades] )

Taxa de Tancament =
DIVIDE (
    CALCULATE ( [Núm. Sinistres], Sinistres[Estat] = "Tancat" ),
    [Núm. Sinistres]
)

Variació Interanual Pagat =
VAR Actual = [Total Pagat]
VAR Anterior = CALCULATE ( [Total Pagat], SAMEPERIODLASTYEAR ( 'Data'[Data] ) )
RETURN DIVIDE ( Actual - Anterior, Anterior )

Fixa't que les mesures complexes es construeixen sobre les bàsiques: si demà canvia la definició de «Total Pagat», totes les altres l'hereten.

On calcular cada KPI?

És una de les decisions amb més impacte en el rendiment i en el govern del model:

	Al datamart (SQL)	Al model (DAX)
Quan	Càlculs pesats sobre grans volums, lògica que uneix diverses fonts, indicadors que fan servir altres eines, agregats de períodes llargs que gairebé no canvien	Combinacions de mesures que s'han de recalcular amb cada filtre i la màxima flexibilitat d'anàlisi
Exemple	Indicadors regulatoris; una taula mensual per ram amb la sinistralitat precalculada	La sinistralitat que respon en filtrar per producte, any o província sense precalcular cada combinació

La guia pràctica: si el KPI és una agregació de la taula de fets o una combinació senzilla de mesures, en DAX. Si necessita ajuntar fonts o recórrer grans històrics, al datamart. Mantenir el datamart senzill i deixar la personalització al model dona el millor dels dos mons.

Cas pràctic: sinistres d'automòbils

L'àrea de Sinistres d'Automòbils vol entendre l'evolució de reclamacions i costos per gestionar millor les indemnitzacions. Hi participen la direcció de sinistres, el responsable d'indemnitzacions, l'equip antifrau i el control financer. S'analitzen cinc anys d'història amb una càrrega diària incremental.

Les preguntes de negoci són sis: quants sinistres hi ha al mes i com evoluciona l'import pagat; com es reparteixen per tipus i gravetat; quin és el cost mitjà per província; quins tallers concentren més despesa; quin percentatge té frau detectat; i quant es triga a resoldre cada tipus de sinistre.

1. KPI definits al mapa: nombre de sinistres, total pagat, cost mitjà, % de frau i temps mitjà de tancament.
2. Dades d'origen: sinistres i pòlisses del data warehouse corporatiu, amb les seves dimensions de temps, causa, taller, geografia, producte i client.
3. Datamart: un esquema propi amb la taula de sinistres d'Auto (filtrada per producte) i les seves dimensions. El temps de tancament en dies es calcula en SQL, perquè és un atribut fix de cada sinistre.
4. Model dimensional: estrella amb granularitat de sinistre individual i sis dimensions.
5. Model de Power BI: relacions, mesures DAX i jerarquies, i un informe amb la tendència mensual, l'anàlisi per causa, el mapa per províncies i targetes amb els KPI.

Llista de comprovació

• Cada KPI de l'informe és al mapa d'indicadors, amb la seva fórmula i els seus casos especials?
• La granularitat permet respondre totes les preguntes de negoci?
• El model és una estrella, amb claus numèriques i registre «desconegut» a cada dimensió?
• Cada mesura es defineix una sola vegada i les altres es construeixen a sobre?
• Les xifres quadren amb la font abans de publicar?

Modelar bé és el que separa un quadre de comandament que es fa servir d'un en què ningú no confia. Si vols aprofundir en el rendiment del model, consulta les nostres bones pràctiques de modelatge de dades a Power BI. I si estàs preparant un datamart o revisant un model que no quadra, a Bidatia dissenyem quadres de comandament amb Power BI i Microsoft Fabric des del mapa d'indicadors fins a l'informe final.