Avaimien ja relaatioiden mallinnus

Kirjoittanut Samu Lahdenperä · Julkaistu

Avaimet ovat sarakkeita jotka yksilöivät taulun rivin tai viittaavat toiseen tauluun, ja relaatiot ovat näiden avainten välisiä yhteyksiä. Ne kytkevät faktataulun ja dimensiot toisiinsa ja muodostavat tähtimallin selkärangan. Relaatio rakennetaan dimension pääavaimesta faktan vierasavaimeen yksi-moneen-suhteella, yhteen suuntaan suodattaen ja kokonaislukumuotoisilla surrogaattiavaimilla. Näin mittarit laskevat oikein, summat eivät tuplaannu ja suodatinkonteksti pysyy ennustettavana.

Ennen yksityiskohtia tässä on tähtimallin relaatio pienimmillään: yksi dimensio DimAsiakas ja yksi faktataulu FactMyynti.

erDiagram DimAsiakas { int AsiakasAvain PK string AsiakasNimi string Kaupunki } FactMyynti { int MyyntiAvain PK int AsiakasAvain FK decimal Myyntisumma } DimAsiakas ||--o{ FactMyynti : "1 → *"
Tähtimallin perusrelaatio: dimensiotaulun DimAsiakas pääavain (PK) kytkeytyy faktataulun FactMyynti vierasavaimeen (FK) yksi-moneen-suhteella.

Kuvan relaatio rakentuu näin. DimAsiakas-taulun pääavain AsiakasAvain (PK) yksilöi jokaisen asiakkaan yhden kerran, ja FactMyynti-taulun vierasavain AsiakasAvain (FK) viittaa siihen jokaisella myyntirivillä. Suhde on yksi-moneen (1 → *): yksi asiakasrivi vastaa montaa myyntiriviä, muttei koskaan toisinpäin. Suodatus kulkee oletuksena yhteen suuntaan, dimensiosta faktatauluun.

Tämä rakenne pitää laskennan yksiselitteisenä. Kun pääavain on uniikki, jokainen faktan vierasavain löytää täsmälleen yhden vastinrivin dimensiosta, joten Power BI ratkaisee mittarit ilman moniselitteisyyttä eivätkä summat tuplaannu. Yhteen suuntaan suodattava yksi-moneen-relaatio antaa myös ennustettavan suodatinkontekstin: kun valitset asiakkaan, näet vain hänen myyntinsä.

Mitä eri avaintyyppejä tietomallissa on?

Tietomallin avaimia on viisi: pääavain, vierasavain, surrogaattiavain, luonnollinen avain ja degeneraatioavain. Rooli ratkaisee: yksilöikö avain rivin, viittaako se toiseen tauluun vai säilyttääkö se lähdejärjestelmän tunnisteen.

Taulukko 1, Tietomallin avaintyypit – Avaimen rooli ja esimerkki kustakin tyypistä
Avain Rooli Esimerkki
Pääavain (PK, Primary Key) Yksilöi taulun jokaisen rivin, ei duplikaatteja eikä tyhjiä arvoja. Dimensiotaulussa pääavain on surrogaattiavain. DimAsiakas.AsiakasAvain
Vierasavain (FK, Foreign Key) Faktataulun INT-sarake joka viittaa dimension pääavaimeen ja luo yhteyden taulujen välille. Jokaista dimensiota kohti yksi FK-sarake. FactMyynti.AsiakasAvain
Surrogaattiavain (SK, Surrogate Key) Järjestelmän generoima juokseva kokonaisluku, ei lähdejärjestelmän tunniste. Pakkautuu VertiPaqissa tehokkaasti ja toimii relaatioiden perustana. 1, 2, 3 …
Luonnollinen avain (NK, Natural Key) Lähdejärjestelmän liiketoimintatunniste, jonka surrogaattiavain korvaa relaatioissa. Säilyy dimensiossa omana sarakkeenaan jäljitettävyyttä varten. CUS-1042
Degeneraatioavain (DD, Degenerate Dimension) Lähdejärjestelmän tunniste jolla ei ole omaa dimensiotaulua eikä kuvailevia attribuutteja. Säilytetään faktataulussa hakua ja debug-työtä varten. TilausNumero, LaskuNumero

Miten relaatiot rakennetaan tähtimallissa?

Taulukko kokoaa tähtimallin avain- ja relaatioratkaisut: mitä tehdään, missä ja miksi.

Taulukko 2, Avain- ja relaatioratkaisut – Mitä kannattaa tehdä, missä, miksi ja mikä on riski jos jätetään tekemättä
Toimenpide Missä kannattaa tehdä Miksi kannattaa Riski jos jätetään tekemättä
Surrogaattiavainten mallinnus (INT) Gold-kerros, dimensioiden lataus Mahdollistaa dimensioiden historianseurannan (SCD); analytiikka ei ole kiinni luonnollisesta avaimesta Lähteen NK vaihtuu → relaatiot rikkoutuvat, historia katoaa ja vanhat raportit hajoavat
Relaatiot INT-avaimille (ei teksti/GUID) Kaikki relaatiot Pakkautuu VertiPaqissa tehokkaasti ja nopeuttaa kyselyitä Teksti- ja GUID-avaimet kasvattavat mallin muistikokoa ja hidastavat läpikäyntiä
Yksisuuntainen suodatus Kaikki relaatiot oletuksena Ennustettava suodatinkonteksti Kaksisuuntainen suodatus → odottamattomat, väärät luvut
Yksi-moneen-kardinaliteetti Dimension pääavain → faktan vierasavain Mittarit lasketaan oikein Moni-moneen → summat tuplaantuvat
Tutkittua: INT-avainten nopeusetu pätee moottorista riippumatta

Kokonaislukuavainten etu ei ole vain Power BI:n VertiPaq-ilmiö, sama tulos on mitattu muissakin tietokantamoottoreissa:

Rivi- ja kyselymäärän kasvaessa ero kertautuu: avainten tietotyyppi on skaalautuvuusratkaisu, ei tekninen yksityiskohta.

Mitä tapahtuu, kun vierasavain ei löydä paria dimensiosta?

Kun vierasavain ei löydä vastinetta dimensiosta, Power BI:n VertiPaq luo dimensioon automaattisen (Blank)-rivin, joka niputtaa eri syistä tulleet rivit yhdeksi erottamattomaksi riviksi. Faktataulun kultainen sääntö onkin, että jokainen vierasavain löytää parinsa dimensiosta, mutta raakadatassa näin ei aina ole: asiakkaita poistetaan GDPR-pyynnöistä, kassaostot kirjautuvat ilman asiakastunnusta.

Taulukko 3, Puuttuvien viittausten käsittely – Mitä kannattaa tehdä, missä, miksi ja mikä on riski jos jätetään tekemättä
Toimenpide Missä kannattaa tehdä Miksi kannattaa Riski jos jätetään tekemättä
Sentinel-rivit dimensioon (Anonyymi, [Poistettu], Ei tiedossa) Gold-dimensiot Jokainen "ei tiedossa" -syy saa oman rivin ja surrogaattiavaimen, suodatettavissa erikseen (Blank) niputtaa NULL- ja orpo-rivit yhteen, syitä ei voi erottaa
Sentinel-merkkijonot (_ANON_, _POISTETTU_) + laatusarake Silver-kerros Tekee Gold-muunnoksesta deterministisen: jokainen ongelmatyyppi on nimetty ETL ei osaa ohjata ongelmaa oikeaan sentinel-riviin
FK osoittaa aina dimensioriviin Gold, FactMyynti-lataus Ei orpoja vierasavaimia → VertiPaq ei luo (Blank)-riviä Orpo FK → automaattinen (Blank) vääristää raportit

Seuraavaksi sama myyntidata kulkee medallion-arkkitehtuurin kolmen kerroksen läpi: Bronze, Silver ja Gold.

Bronze: raakadata sellaisenaan

Bronze-kerros lataa datan lähdejärjestelmästä muuttamatta. NULL-arvot ja viittaukset poistettuihin asiakkaisiin jäävät paikoilleen: Bronze ei korjaa dataa, se tallentaa sen.

Taulukko 4, Bronze-tason myyntifakta – Raakadata lähdejärjestelmästä; NULL ja orpo FK näkyvät sellaisinaan
TilausNumero AsiakasTunnus TuoteKoodi MyyntiPvm Myyntisumma FK-ongelma
ORD-5001 CUS-1042 PRD-200 2026-06-01 149,90 —
ORD-5002 NULL PRD-201 2026-06-02 89,50 Kassaosto, asiakas ei kirjautunut
ORD-5003 CUS-9841 PRD-200 2026-06-03 199,90 Asiakas poistettu (GDPR), ei löydy DimAsiakkaasta
(Blank)-ongelma käytännössä — sama raportti kahdella tavalla:

Kun Bronze-data ladataan sellaisenaan Power BI:hin, sekä kassaosto (NULL) että poistetun asiakkaan osto (orpo FK) päätyvät saman (Blank)-rivin alle eikä niitä voi enää erotella:

Taulukko 5, Power BI -asiakasraportti ilman "ei tiedossa" -käsittelyä – NULL ja orpo FK yhdistyvät erottamattomaksi (Blank)-riviksi
Asiakas Myynti
Yritys Oy 149,90 €
(Blank) 289,40 €

Gold-tasolla käsitelty data erittelee jokaisen syyn omalle rivilleen:

Taulukko 6, Power BI -asiakasraportti oikealla "ei tiedossa" -käsittelyllä – Jokainen tilanne omana rivinään, suodatettavissa erikseen
Asiakas Myynti
Yritys Oy 149,90 €
Anonyymi 89,50 €
[Poistettu] 199,90 €

Silver: validointi ja sentinel-merkkijonot

Silver-kerros standardoi NULL-arvot ja orvot viittaukset tunnistettaviksi sentinel-merkkijonoiksi (_ANON_, _POISTETTU_) ja merkitsee jokaisen ongelmarivin tiedonlaatusarakkeeseen. Surrogaattiavaimia ei vielä luoda: Silver vain nimeää ongelmat, jotta Gold-muunnos voi ohjata ne oikeaan sentinel-riviin.

Gold: sentinel-rivit dimensioon ja surrogaattiavaimet

Gold-kerroksessa jokainen dimensio saa omat sentinel-rivit ennen varsinaista dataa: DimAsiakkaaseen erilliset rivit kassaostoille (Anonyymi), poistetuille asiakkaille ([Poistettu]) ja yleinen "Ei tiedossa". Ne saavat INT-surrogaattiavaimen kuten oikeatkin rivit, joten jokainen FactMyynnin AsiakasAvain osoittaa johonkin DimAsiakkaan riviin, NULL-arvoja tai orpoja viittauksia ei enää ole.

Taulukko 7, Gold-tason DimAsiakas – Sentinel-rivit (avaimet 0–2) varsinaisen asiakasdatan edellä
AsiakasAvain (PK) AsiakasTunnus (NK) AsiakasNimi Segmentti Sentinel-syy
0 _TUNTEMATON_ Ei tiedossa — Yleinen fallback, tuntematon ongelmatyyppi
1 _ANON_ Anonyymi Kuluttaja Kassaosto, asiakastunnistautuminen ei tapahtunut
2 _POISTETTU_ [Poistettu] — GDPR-pyyntö: historia säilyy, henkilötiedot poistettu
1042 CUS-1042 Yritys Oy B2B —
Taulukko 8, Gold-tason FactMyynti – Kaikki AsiakasAvain-arvot löytyvät DimAsiakkaasta, NULL-arvoja ei ole
MyyntiAvain (PK) TilausNumero (DD) AsiakasAvain (FK) TuoteAvain (FK) MyyntiPvmAvain (FK) Myyntisumma
1 ORD-5001 1042 201 20260601 149,90
2 ORD-5002 1 202 20260602 89,50
3 ORD-5003 2 201 20260603 199,90

ORD-5002:n AsiakasAvain on 1 (Anonyymi) ja ORD-5003:n on 2 ([Poistettu]). Molemmat löytyvät DimAsiakkaasta, joten VertiPaq ei luo (Blank)-riviä. Raporteissa 289,40 € on nyt eritelty kahdeksi erikseen suodatettavaksi riviksi, joilla on eri merkitys.

Seitsemän vinkkiä parempiin relaatioihin ja avaimiin

Nämä seitsemän käytäntöä pitävät avaimet ja relaatiot kunnossa.

  1. Yksi-moneen on oletuskardinaliteetti. Käytä moni-moneen-relaatiota vain jos sitä ei voi välttää ja silloinkin tietoisena siitä, että summat voivat tuplaantua. Dokumentoi valinta.
  2. Jokainen faktan FK osoittaa dimensioriviin. Ei NULL-arvoja eikä orpoja viittauksia, ohjaa ne sentinel-riveihin.
  3. Suodatus yhteen suuntaan. Kaksisuuntainen suodatus vain perustellusta syystä (esim. bridge-taulu), ei oletuksena.
  4. Relaatiot INT-avaimille. Ei tekstiä eikä GUIDia, kokonaisluku on nopein ja pienin.
  5. Dimension pääavain on uniikki. Tarkista uniikkius ETL:ssä ennen latausta, älä yritä korjata duplikaatteja Power BI:n sisällä.
  6. Ei relaatiota kahden faktataulun välille. Yhdistä faktataulut vain yhteisten dimensioiden kautta (conformed dimensions).
  7. Yksi päivämäärädimensio, merkittynä Date-tauluksi. Älä luo rinnakkaisia kalentereita; merkitse dimensio Power BI:ssä "Mark as Date Table" -toiminnolla, jotta aikatiedustelut (time intelligence) toimivat.
Dataneuvoksen mielipide

Relaatioiden mallinnuksessa kolme asiaa ratkaisee Power BI:ssä enemmän kuin mikään muu:

  1. Yksi-moneen, yhteen suuntaan suodattava relaatio on oletus. Se on ainoa rakenne, jonka suodatinkonteksti on aina ennustettava. Jos poikkeat tästä, perustele ja dokumentoi valinta seuraavia kehittäjiä varten.
  2. Käytä aina INT-muotoisia surrogaattiavaimia. INT-avain pakkautuu VertiPaqissa tehokkaasti ja irrottaa analytiikan lähdejärjestelmän vaihtuvista tunnisteista. Yli miljoonan rivin dimensioissa teksti- ja GUID-relaatiot ovat noin kolmanneksen hitaampia kuin INT-avaimiin perustuvat.
  3. Jokainen vierasavain löytää parinsa dimensiosta. Orpo FK tuottaa automaattisen (Blank)-rivin, joka sulauttaa eri syyt yhteen. Sentinel-rivit pitävät jokaisen "ei tiedossa" -tilanteen erikseen suodatettavana.