Forstå stjerneskjema og viktigheten for Power BI

Denne artikkelen gjelder for Power BI Desktop datamodellerere. Den beskriver utforming av et stjerneskjema og relevansen for utvikling av Power BI-datamodeller som er optimalisert for ytelse og brukervennlighet.

Denne artikkelen er ikke ment å gi en altomfattende drøftelse av utforming av stjerneskjema. Hvis du vil ha mer informasjon, se publisert innhold som The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (3. utgave, 2013) av Ralph Kimball et al.

Oversikt over stjerneskjema

Stjerneskjema er en velprøvd modelleringsmåte som er mye brukt med relasjonelle datalager. Den krever at modellerere klassifiserer modelltabellene sine som enten dimensjon eller faktum.

Dimensjonstabeller beskriver forretningsenheter – tingene du modellerer. Enheter kan omfatte produkter, personer, steder og konsepter, inkludert selve tiden. Den mest konsekvente tabellen du finner i et stjerneskjema, er en datodimensjonstabell. En dimensjonstabell inneholder en nøkkelkolonne (eller kolonner) som fungerer som en unik identifikator, og beskrivende kolonner.

Faktatabeller lagrer observasjoner eller hendelser og kan være salgordrer, aksjebalanse, valutakurser, temperaturer med mer. En faktatabell inneholder dimensjonsnøkkelkolonner som er relatert til dimensjonstabeller og numeriske målekolonner. Dimensjonsnøkkelkolonnene bestemmer antall dimensjoner for en faktatabell, mens dimensjonsnøkkelverdiene bestemmer detaljnivået for en fakta tabell. Ta for eksempel en faktatabell som er utformet for å lagre salgsmål som har de to dimensjonsnøkkelkolonnene Date og ProductKey. Det er lett å forstå at tabellen har to dimensjoner. Du kan imidlertid ikke fastsette detaljnivået uten å se på dimensjonsnøkkelverdiene. Legg merke til at i dette eksemplet er verdiene som er lagret i Date-kolonnen den første dagen i hver måned. I dette tilfellet er detaljnivået på månedlig produktnivå.

Dimensjonstabeller inneholder vanligvis et relativt lite antall rader. Faktatabeller kan derimot inneholde et stort antall rader og fortsette å vokse over tid.

Bilde av et stjerneskjema

Stjerneskjemaers relevans for Power BI-modeller

Utforming av stjerneskjema og mange relaterte konsepter som introduseres i denne artikkelen, er svært relevante for utvikling av Power BI-modeller som er optimalisert for ytelse og anvendelighet.

Tenk over at hvert Power BI visualobjekt for rapport genererer en spørring som sendes til Power BI-modellen (som Power BI-tjenesten kaller et datasett). Disse spørringene brukes til å filtrere, gruppere og oppsummere modelldata. En godt utformet modell, er en som inneholder tabeller for filtrering og gruppering, og tabeller for oppsummering. Denne utformingen passer bra med prinsippene for stjerneskjema:

  • Dimensjonstabeller støtter filtrering og gruppering
  • Faktatabeller støtter oppsummering

Det finnes ingen tabellegenskaper som utformerne kan bruke til å konfigurere tabelltypen som dimensjon eller fakta. Det bestemmes faktisk av modellrelasjonene. En modellrelasjon etablerer en filteroverføringsbane mellom to tabeller, og det er egenskapen kardinalitet for relasjonen som bestemmer tabelltypen. En vanlig relasjonskardinalitet er en-til-mange eller det motsatte, mange-til-en. «En»-siden er alltid en dimensjonstabell, mens «mange»-siden alltid er en faktatabell. Se modell relasjoner i Power BI Desktop for mer informasjon om relasjoner.

Konseptuelt stjerneskjema

En godt strukturert modellutforming bør inkludere tabeller som er enten dimensjonstabeller eller faktatabeller. Du bør unngå å blande de to typene i en tabell. Vi anbefaler også at du prøver å ha riktig antall tabeller med de rette relasjonene på plass. Det er også viktig at faktatabeller bare laster inn data med et konsekvent detaljnivå.

Til sist er det viktig å forstå at optimal modellutforming er delvis vitenskap og delvis en kunst. Noen ganger kan du bryte med god veiledning når det er fornuftig å gjøre det.

Det finnes mange flere konsepter relatert til utforming av stjerneskjema som kan brukes på en Power BI-modell. Disse konseptene omfatter følgende:

Målinger

I utforming av stjerneskjema er et mål en faktatabellkolonne som lagrer verdier som skal summeres.

I en Power BI-modell har et mål en annen, men lignende definisjon. Det er en formel skrevet i DAX (Data Analysis Expressions) som gir sammendrag. Måleuttrykk bruker ofte DAX-aggregeringsfunksjoner som SUM, MIN, MAX, AVERAGE og så videre for å produsere et resultat som er en skalarverdi ved spørringstid (verdier lagres aldri i modellen). Måleuttrykk kan være alt fra enkle kolonneaggregasjoner til mer omfattende formler som overstyrer filterkontekst og/eller relasjonsoverføring. Hvis du vil ha mer informasjon, kan du lese artikkelen Grunnleggende om DAX i Power BI Desktop.

Det er viktig å forstå at Power BI-modeller støtter en annen metode for å lage sammendrag. Enhver kolonne – og vanligvis numeriske kolonner – kan summeres av et rapportvisualobjekt eller spørsmål og svar. Disse kolonnene kalles implisitte mål. De er praktiske for deg som modellutvikler, ettersom du i mange tilfeller ikke trenger å opprette mål. For eksempel kan kolonnen for Adventure Works-forhandlerens salg, Salgsbeløp, oppsummeres på mange måter (sum, antall, gjennomsnitt, median, min, maks, osv.), uten at du trenger å opprette et mål for hver mulige aggregasjonstype.

Ikoneksempel i feltliste

Det finnes imidlertid tre gode grunner til å opprette mål, selv for enkle sammendrag på kolonnenivå:

  • Når du vet at rapportforfatterne spør modellen ved hjelp av flerdimensjonale uttrykk (MDX), må modellen inkludere eksplisitte mål. Eksplisitte mål defineres ved hjelp av DAX. En slik tilnærming til utformingen er svært relevant når et Power BI-datasett spørres ved bruk av MDX, ettersom MDX ikke kan oppnå oppsummering av kolonneverdier. MDX brukes spesielt ved utføring av Analyser i Excel siden pivottabeller utsteder MDX-spørringer.
  • Når du vet at rapportforfatterne oppretter Power BI-sideformaterte rapporter ved hjelp av MDX-spørringsutformeren, må modellen inkludere eksplisitte mål. Bare MDX-spørringsutformeren støtter serveraggregater. Så hvis en rapportforfatter trenger å ha evaluere målene med Power BI (i stedet for av motoren for sideformaterte rapporter), må de bruke MDX-spørringsutformeren.
  • Når du trenger å sikre at rapportforfatterne bare kan summere kolonner på bestemte måter. For eksempel kan Enhetspris-kolonnen for forhandlersalg (som representerer en per enhet-sats) oppsummeres, men bare ved å bruke bestemte aggregeringsfunksjoner. Den skal aldri summeres, men det går an å oppsummere ved hjelp av andre aggregeringsfunksjoner (min, max, gjennomsnitt osv.). I dette tilfellet kan modellereren skjule Enhetspris-kolonnen og opprette mål for alle passende aggregeringsfunksjoner.

Denne utformingstilnærmingen fungerer bra for rapporter som er skrevet i Power BI-tjenesten og for spørsmål og svar. Power BI Desktop live-tilkoblinger tillater imidlertid at forfattere av rapporter kan vise skjulte felt i Felter-ruten, noe som kan føre til at de omgår denne fremgangsmåten.

Surrogatnøkler

En surrogatnøkkel er en unik identifikator som du legger til i en tabell for å støtte stjerneskjemamodellering. Per definisjon er den ikke definert eller lagret i kildedataene. Ofte legges surrogatnøkler til i dimensjonstabeller for relasjonelle datalager for å angi en unik identifikator for hver dimensjonstabellrad.

Power BI modellrelasjoner er basert på en unik kolonne i en tabell, som overfører filtre til en enkelt kolonne i en annen tabell. Når en dimensjonstabell i modellen ikke inneholder en unik kolonne, må du legge til en unik identifikator for å bli «en»-siden av en relasjon. I Power BI Desktop kan du enkelt oppfylle dette kravet ved å opprette en Power Query-indekskolonne.

Opprett indekskolonne på Power Query-verktøylinjen

Du må slå sammen denne spørringen med «mange»-spørringen, slik at du også kan legge til indekskolonnen i den. Når du laster disse spørringene til modellen, kan du deretter opprette en en-til-mange-relasjon mellom modelltabellene.

Snøfnuggdimensjoner

En snøfnuggdimensjon er et sett med normaliserte tabeller for en enkelt forretningsenhet. Adventure Works klassifiserer for eksempel produkter etter kategori og underkategori. Kategorier er tilordnet til underkategorier, og produkter er i sin tur tilordnet til underkategorier. I det relasjonelle datalageret for Adventure Works er produktdimensjonen normalisert og lagret i tre relaterte tabeller: DimProductCategory, DimProductSubcategory og DimProduct.

Hvis du bruker fantasien, kan du forestille deg at de normaliserte tabellene plasseres utover fra faktatabellen i en snøfnuggutforming.

Eksempel på snøfnuggdiagram

I Power BI Desktop kan du velge å etterligne en snøfnuggdimensjonsutforming (kanskje fordi kildedataene gjør det) eller integrere (denormalisere) kildetabellene i en modelltabell. Vanligvis er fordelene ved en enkelt modelltabell flere enn fordelene med flere modelltabeller. Den mest optimale avgjørelsen kan være avhengig av datavolumer og krav til anvendelighet for modellen.

Når du velger å etterligne en snøfnuggdimensjonsutforming:

  • Laster Power BI inn flere tabeller, noe som er mindre effektivt fra lagrings- og ytelsesperspektiver. Disse tabellene må inkludere kolonner for å støtte modellrelasjoner, og dette kan føre til en større modellstørrelse.
  • Lengre overføringskjeder for relasjonsfiltre må traverseres, noe som sannsynligvis vil være mindre effektivt enn filtre som brukes på en enkelt tabell.
  • Felter-ruten presenterer flere modelltabeller til rapportforfattere, noe som kan føre til en mindre intuitiv opplevelse, spesielt når snøfnuggdimensjonstabeller inneholder bare en eller to kolonner.
  • Det er ikke mulig å opprette et hierarki som dekker tabellene.

Når du velger å integrere i en modelltabell, kan du også definere et hierarki som omfatter det høyeste og laveste detaljnivået for dimensjonen. Lagring av redundante, denormaliserte data kan muligens gi økt lagringsstørrelse for modellen, spesielt for svært store dimensjonstabeller.

Hierarki i dimensjon

Endringstrege dimensjoner

En endringstreg dimensjon (SCD) er en som håndterer endring av dimensjonsmedlemmer over tid. Den gjelder når verdier for forretningsenheter endres over tid, og på en vilkårlig måte. Et godt eksempel på en langsom endring av dimensjon er en kundedimensjon, spesifikt kontaktdetaljkolonnene, som e-postadresse og telefonnummer. Som en kontrast anses noen dimensjoner som i rask endring når et dimensjonsattributt endres ofte, for eksempel markedspris for en aksje. Den vanligste utformingstilnærmingen i disse tilfellene er å lagre raskt endrende attributtverdier i et faktatabellmål.

Stjerneskjema-designteori refererer til to vanlige SCD-typer: Type 1 og type 2. En tabell med dimensjonstype kan være type 1 eller type 2, eller støtte begge typene samtidig for forskjellige kolonner.

Type 1-SCD

En Type 1 SCD gjenspeiler alltid de nyeste verdiene, og når endringer i kildedata oppdages, overskrives dimensjonstabelldataene. Denne utformingstilnærmingen er vanlig for kolonner som lagrer tilleggsverdier, for eksempel e-postadressen eller telefonnummeret til en kunde. Når en kundes e-postadresse eller telefonnummer endres, oppdaterer dimensjonstabellen kunderaden med de nye verdiene. Det er som om kunden alltid har hatt denne kontaktinformasjonen.

En ikke-trinnvis oppdatering av en dimensjonstabell for Power BI-modell gir resultatet av en type 1-SCD. Den oppdaterer tabelldataene for å sikre at de siste verdiene er lastet inn.

Type 2-SCD

En type 2-SCD støtter versjonering av dimensjonsmedlemmer. Hvis kildesystemet ikke lagrer versjoner, er det vanligvis innlesingsprosessen for datalageret som oppdager endringer, og som håndterer endringen i en dimensjonstabell. I dette tilfellet må dimensjonstabellen bruke en surrogatnøkkel for å gi en unik referanse til en versjon av dimensjonsmedlemmet. Den inneholder også kolonner som definerer gyldigheten for datointervallet for versjonen (for eksempel StartDate og EndDate), og muligens en flaggkolonne (f.eks. IsCurrent) for å enkelt filtrere etter gjeldende dimensjonsmedlemmer.

Adventure Works tilordner for eksempel selgere til et salgsområde. Når en selger bytter område, må det opprettes en ny versjon av selgeren for å sikre at historiske fakta forblir forbundet med det forrige området. Hvis du vil støtte nøyaktige historiske analyser av salg etter selger, må dimensjonstabellen lagre versjoner av selgere og tilhørende område(r). Tabellen bør også inkludere start- og sluttdato-verdier for å definere tidsgyldigheten. Gjeldende versjoner kan definere en tom sluttdato (eller 31.12.9999), noe som indikerer at raden er den gjeldende versjonen. Tabellen må også definere en surrogatnøkkel fordi forretningsnøkkelen (i dette tilfelle ansatt-ID) ikke er unik.

Det er viktig å forstå at når kildedataene ikke lagrer versjoner, må du bruke et mellomliggende system (som et datalager) til å registrere og lagre endringer. Tabellinnlastingsprosessen må ta vare på eksisterende data og registrere endringer. Når en endring oppdages, må tabellinnlastingsprosessen sette gjeldende versjon som utløpt. Den registrerer disse endringene ved å oppdatere EndDate-verdien og sette inn en ny versjon med StartDate-verdien som begynner fra den forrige EndDate-verdien. I tillegg må relaterte fakta bruke et tidsbasert oppslag for å hente dimensjonsnøkkelverdien som er relevant for faktadatoen. En Power BI-modell som bruker Power Query, kan ikke gi dette resultatet. Den kan imidlertid laste inn data fra en forhåndsinnlastet SCD type 2 dimensjonstabell.

Power BI-modellen bør støtte spørring av historiske data for et medlem, uavhengig av endring, og for en versjon av medlemmet, som representerer en bestemt tilstand for medlemmet i tid. I konteksten til Adventure Works kan du bruke denne utformingen til å spørre selgeren uavhengig av tilordnet salgsområde, eller for en bestemt versjon av selgeren.

For å oppnå dette kravet må Power BI-modellens dimensjonstabell inneholde en kolonne for filtrering av selger og en annen kolonne for filtrering av en bestemt versjon av selger. Det er viktig at versjonskolonnen inneholder en ikke-tvetydig beskrivelse, for eksempel «Michael Blythe (15.12.2008–26.06.2019)» eller «Michael Blythe (nåværende)». Det er også viktig å lære opp rapportforfattere og forbrukere om det grunnleggende om SCD type 2, og hvordan du oppnår riktige rapportutforminger ved å bruke riktige filtre.

Det er også en god utformingspraksis å inkludere et hierarki som lar visualobjekter drille ned på versjonsnivå.

Hierarkieksempel i feltliste

Utdata for hierarkieksempel

Rollespillende dimensjoner

En rollespillende dimensjon er en dimensjon som kan filtrere relaterte fakta på en annen måte. For eksempel, har tabellen for datodimensjon hos Adventure Works tre relasjoner til salgsfakta for forhandleren. Den samme dimensjonstabellen kan brukes til å filtrere fakta etter ordredato, forsendelsesdato eller leveringsdato.

I et datalager er den aksepterte utformingstilnærmingen å definere en enkelt datodimensjonstabell. Under spørringstidspunktet er «rollen» for datodimensjonen fastsatt av hvilken faktakolonne du bruker til å føye sammen tabellene. Når du for eksempel analyserer salg etter ordredato, knyttes tabellsammenføyningen til forhandlerens datokolonne for salgsordrer.

I en Power BI-modell kan denne utformingen etterlignes ved å opprette flere relasjoner mellom to tabeller. I eksemplet med Adventure Works har salgstabeller for dato og forhandler tre relasjoner. Selv om denne utformingen er mulig, er det viktig å forstå at det bare kan være én aktiv relasjon mellom to Power BI-modelltabeller. Alle gjenstående relasjoner må settes inaktive. Når du har en aktiv relasjon, betyr det at det er en standard overføring fra dato til forhandlerens salg. I dette tilfellet er den aktive relasjonen satt til det vanligste filteret som brukes av rapporter, som hos Adventure Works er bestillingsdatorelasjonen.

Eksempel på en enkelt rollespillende dimensjon og relasjoner

Den eneste måten å bruke en inaktiv relasjon på er å definere et DAX-uttrykk som bruker USERELATIONSHIP-funksjonen. I vårt eksempel må modellutvikleren opprette mål for å muliggjøre analyser av forhandlers salg etter forsendelsesdato og leveringsdato. Dette kan være møysommelig arbeid, spesielt når tabellen for forhandleren definerer mange mål. Den lager også rot i Felter-ruten, med en stor mengde mål. Det finnes også andre begrensninger:

  • Når rapport forfattere bruker oppsummering av kolonner, i stedet for å definere mål, kan de ikke få sammendrag for de inaktive relasjonene uten å skrive et mål på rapportnivå. Mål på rapportnivå kan bare defineres når du redigerer rapporter i Power BI Desktop.
  • Med bare en aktiv relasjonsbane mellom dato og forhandlersalg, er det ikke mulig å filtrere forhandlersalg samtidig etter ulike typer datoer. Du kan for eksempel ikke lage et visualobjekt som plotter bestillingsdatosalg etter sendte salg.

For å omgå disse begrensningene er en vanlig modelleringsteknikk i Power BI å opprette en dimensjonstabell for hver rollespillende forekomst. Du oppretter vanligvis de ekstra dimensjonstabellene som beregnede tabeller ved hjelp av DAX. Ved hjelp av beregnede tabeller kan modellen inneholde en Dato-tabell, en Forsendelsesdato-tabell og en Leveringsdato-tabell, hver med en enkelt og aktiv relasjon til respektive tabellkolonner for forhandlersalg.

Eksempel på rollespillende dimensjoner og relasjoner

Denne utformingstilnærmingen krever ikke at du definerer flere mål for forskjellige datoroller, og den tillater samtidig filtrering etter ulike datoroller. Det er en mindre pris å betale – med denne utformingstilnærmingen er det en duplikat av datodimensjonstabellen som resulterer i en økt modelllagringsstørrelse. Siden dimensjonstabeller vanligvis lagrer færre rader i forhold til faktatabeller, er dette sjelden et problem.

Følg disse gode utformingspraksisene når du oppretter modellens dimensjonstabeller for hver rolle:

  • Sørg for at kolonnenavnene er selvbeskrivende. Selv om det er mulig å ha en År-kolonne i alle datotabeller (kolonnenavn er unike i tabellen), er det ikke selvbeskrivende som standard visuelle titler. Vurder å gi nytt navn til kolonner i hver dimensjonsrolletabell, slik at tabellen for Forsendelsesdato har en årskolonne som heter Forsendelsesår og så videre.
  • Hvis det er relevant, må du sørge for at tabellbeskrivelsene gir tilbakemelding til rapportforfattere (gjennom verktøytips i Felt-ruten) om hvordan filteroverføring er konfigurert. Denne klarheten er viktig når modellen inneholder en generisk navngitt tabell, for eksempel Dato, som brukes til å filtrere mange faktatabeller. I tilfeller der denne tabellen for eksempel har en aktiv relasjon til kolonnen for forhandlerens salgsordredato, bør du vurdere å gi en tabellbeskrivelse som «Filtrerer forhandlersalg etter bestillingsdato».

Se Veiledning for aktive kontra inaktive relasjoner for mer informasjon.

Søppeldimensjoner

En søppeldimensjon er nyttig når det finnes mange dimensjoner, som særlig består av få attributter (kanskje en), og når disse attributtene har få verdier. Gode kandidater inkluderer ordrestatuskolonner, eller kundedemografiske kolonner (kjønn, aldersgruppe osv.).

Målet med en søppeldimensjon er å konsolidere mange «små» dimensjoner til en dimensjon for både å redusere lagringsstørrelsen for modellen og også redusere Felter-ruten ved å vise færre modelltabeller.

En søppeldimensjonstabell er vanligvis det kartesiske produktet for alle medlemmer av dimensjonsattributt, med en surrogatnøkkelkolonne. Surrogatnøkkelen gir en unik referanse til hver rad i tabellen. Du kan bygge dimensjonen i et datalager, eller ved å bruke Power Query til å opprette en spørring som utfører fulle ytre spørringssammenføyninger, og deretter legge til en surrogatnøkkel (indekskolonne).

Eksempel på søppeldimensjon

Du laster denne spørringen til modellen som en dimensjonstabell. Du må også slå sammen denne spørringen med faktaspørringen, slik at indekskolonnen lastes inn i modellen for å støtte oppretting av en «en-til-mange»-relasjon.

Degenererte dimensjoner

En degenerert dimensjon refererer til et attributt for faktatabellen som kreves for filtrering. Hos Adventure Works er salgsordrenummeret til forhandleren et godt eksempel. I dette tilfellet er det ikke lurt å opprette en uavhengig tabell som består av bare denne ene kolonnen, fordi det vil øke lagringsstørrelsen på modellen og resultere i rot i Felter-ruten.

I Power BI-modellen kan det være hensiktsmessig å legge til kolonnen for salgsordrenummer i faktatabellen for å tillate filtrering eller gruppering etter salgsordrenummer. Dette er et unntak fra den tidligere introduserte regelen om at du ikke bør blande tabelltyper (det vil si at modelltabeller må være enten av typen dimensjon eller fakta).

Eksempel på degenerert dimensjon

Men hvis salgs tabellen til reforhandlere i Adventure Works har ordre nummer og ordrelinjenummer-kolonner, og de er nødvendige for filtrering, vil en dimensjonstabell for degenerering av data være en god utforming. Se Veiledning for en-til-en-relasjoner (degenererte dimensjoner) for mer informasjon.

Faktaløse faktatabeller

En faktaløs fakta tabell inneholder ingen målkolonner. Den inneholder bare dimensjonsnøkler.

En faktaløs faktatabell kan lagre observasjoner som er definert av dimensjonsnøkler. For eksempel, på en bestemt dato og klokkeslett, logget en bestemt kunde på webområdet ditt. Du kan definere et mål for å telle radene i den faktaløse faktatabellen for å utføre analyse av når og hvor mange kunder som har logget på.

En mer nyttig bruk av en faktaløs faktatabell er å lagre relasjoner mellom dimensjoner, og det er den tilnærmingen til utforming av Power BI-modell vi anbefaler for å definere mange-til-mange-dimensjonsrelasjoner. I en mange-til-mange-dimensjons relasjonutformings, kalles den faktaløse tabellen for en brotabell.

Tenk deg for eksempel at selgere kan tilordnes til ett eller flere salgsområder. Brotabellen vil utformes som en faktaløs faktatabell som består av to kolonner: selgernøkkel og områdenøkkel. Dupliserte verdier kan lagres i begge kolonner.

Eksempel på faktaløs faktatabell

Denne mange-til-mange-tilnærmingen er godt dokumentert, og den kan oppnås uten en brotabell. Imidlertid regnes tilnærmingen med brotabell som den beste fremgangsmåten for å relatere to dimensjoner. Se Veiledning for mange-til-mange-relasjoner (relater to dimensjonstype-tabeller) for mer informasjon.

Neste trinn

Hvis du vil ha mer informasjon om utforming av stjerneskjema eller modellutforming i Power BI, kan du ta en titt på følgende artikler: