Bridge table (brugtabel)

Wat is een bridge table?

Een bridge table, in het Nederlands een brugtabel, is een tussentabel die een veel-op-veel-relatie tussen twee tabellen oplost. In een ster-schema hangt elke dimensie normaal via één vreemde sleutel aan de feitentabel: één order hoort bij één klant, één product, één datum. Maar soms hoort één rij bij meerdere waarden tegelijk. Eén bankrekening heeft twee houders, één ziekenhuisopname heeft meerdere diagnoses. Een gewone vreemde sleutel wijst maar naar één rij, dus die relatie past er niet in.

De brugtabel lost dat op door tussen de twee tabellen te gaan zitten en de paren te bewaren. Eén rij per combinatie: rekening 1 met klant 91, rekening 1 met klant 92, rekening 2 met klant 92. Zo hangt aan één rekening zoveel klanten als nodig, zonder dat je de structuur van je model moet aanpassen als er een houder bijkomt.

Bevat de brug enkel de sleutels van beide kanten en geen eigen meetwaarden, dan noemt Kimball dat een factless fact table: een feitentabel zonder cijfers, die enkel het bestaan van een verband registreert.

Wanneer heb je een brugtabel nodig?

Je herkent de situatie aan één vraag: kan één rij aan de ene kant bij meerdere rijen aan de andere kant horen, en omgekeerd? Zo ja, dan zit je met een veel-op-veel. De klassiekers:

• Een bankrekening met meerdere houders. Een gezamenlijke rekening heeft twee of meer eigenaars, en elke klant kan meerdere rekeningen hebben. De brugtabel tussen rekening en klant legt vast wie welke rekening deelt.

• Een patiënt met meerdere diagnoses tegelijk. Bij één opname stelt een arts vaak meerdere diagnoses vast. Een nieuwe diagnose komt er zo bij als een extra rij in de brug, zonder herontwerp.

• Een product in meerdere categorieën. Een fiets kan tegelijk onder "sport", "outdoor" en "cadeau" vallen. De brug tussen product en categorie bewaart elke combinatie.

Een brug kan tussen een feit en een dimensie liggen (opname en diagnose) of tussen twee dimensies (rekening en klant). Het werk is hetzelfde: ze splitst één onmogelijke relatie in twee gewone één-op-veel-relaties, met de brug in het midden.

De dubbeltelling en de verdeelfactor

Hier zit het venijn. Een brugtabel maakt de veel-op-veel mogelijk, maar ze introduceert meteen een valstrik: zodra je een bedrag optelt en groepeert op de meervoudige kant, tel je dat bedrag te veel keer.

Neem een ziekenhuisopname die 3.000 euro kost, met drie diagnoses. Groepeer je de kost per diagnose via de brug, dan koppelt de join die 3.000 euro aan elk van de drie diagnoses. Tel je daarna op, dan staat er 9.000 euro in je rapport terwijl de opname maar 3.000 euro kostte. De brug heeft het bedrag drie keer gekopieerd, één keer per diagnose.

De oplossing die Kimball voorstelt is een verdeelfactor (in het Engels een allocation of weighting factor): een extra kolom in de brugtabel die per rij een deel van het geheel toekent. Bij drie diagnoses krijgt elke rij factor een derde; bij tien diagnoses krijgt elke rij factor een tiende. De som van de factoren per opname is altijd 1.

Vermenigvuldig je de kost met die factor voor je optelt, dan klopt het weer. Elke diagnose krijgt een derde van 3.000 euro toegewezen, dus 1.000 euro, en de drie samen tellen op tot 3.000 euro. Wil je het totaal over alle opnames zonder op diagnose te splitsen, dan gebruik je de factor gewoon niet en tel je de kost rechtstreeks. De factor is er enkel voor wanneer je op de meervoudige kant groepeert.

Werkt een verdeelfactor je te veel tegen, dan kan je ook één waarde als hoofdwaarde aanduiden en enkel de hoofddiagnose bewaren. Eenvoudiger, maar je vergeet dan de rest.

De grain van een brugtabel

Zoals bij elke tabel in een dimensioneel model moet je de granulariteit van je brug scherp hebben: wat stelt één rij voor? Bij een brugtabel is dat antwoord bijna altijd "één paar": één rekening-klant-koppeling, één opname-diagnose-koppeling.

Die grain bepaalt ook waar de verdeelfactor thuishoort. De factor leeft op het niveau van de groep (de opname, de rekening), niet op de losse diagnose. Daarom bereken je hem per groep: tel hoeveel rijen een groep heeft en verdeel 1 over die rijen.

Een brugtabel in Power BI

In Power BI bouw je een brug niet als één veel-op-veel-relatie, maar als drie tabellen met twee gewone één-op-veel-relaties: de brug in het midden, aan weerszijden een dimensie of feit. Microsoft raadt dat expliciet aan boven een rechtstreekse veel-op-veel-relatie, omdat je zo de controle houdt over hoe filters lopen.

Standaard laat Power BI een filter maar één kant op lopen. Bij een brug moet je op één van de twee relaties de filterrichting op beide zetten, anders raakt het filter niet tot bij de feitentabel en toont elke klant hetzelfde totaal. De measure is dan niet stuk, de filter geraakt gewoon niet door de brug.

Let ook op wat Microsoft non-additive noemt. Klant 91 en klant 92 delen een rekening, dus hun saldo's mag je niet zomaar optellen tot een klantentotaal: dat zou de gedeelde rekening dubbel tellen. Een cijfer dat niet netjes optelt, oogt fout terwijl het correct is, dus leg dat uit aan je rapportgebruikers.

FAQ over bridge tables

1. Wat is het verschil tussen een brugtabel en een gewone dimensietabel?
Een dimensietabel beschrijft één ding (een klant, een product) met één rij per uniek item. Een brugtabel beschrijft niets, ze legt enkel verbanden vast: welk item bij welk ander item hoort. Ze staat tussen twee tabellen in plaats van aan de rand.

2. Is een brugtabel hetzelfde als een snowflake?
Nee. Snowflaken splitst een dimensie in kleinere dimensies langs een één-op-veel-lijn. Een brugtabel lost een veel-op-veel op die je met splitsen nooit weg krijgt. De twee lossen verschillende problemen op.