Veiledning for mange-til-mange-relasjon

Denne artikkelen gjelder for datamodellerere som arbeider med Power BI Desktop. Den beskriver tre forskjellige mange-til-mange-modelleringsscenarier. Den gir deg også veiledning om hvordan du lykkes med utforming for dem i modellene dine.

Obs!

En introduksjon til modellrelasjoner dekkes ikke i denne artikkelen. Hvis du ikke er så godt kjent med relasjoner, deres egenskaper eller hvordan du konfigurerer dem, anbefaler vi at du først leser Modellrelasjoner i Power BI Desktop-artikkelen.

Det er også viktig at du har forståelse for utforming av stjerneskjema. For mer informasjon, se Forstå stjerneskjema og viktigheten for Power BI.

Det finnes faktisk tre mange-til-mange-scenarier. De kan oppstå når du må:

Relatere mange-til-mange-dimensjoner

La oss vurdere den første mange-til-mange-scenarietypen med et eksempel. Det klassiske scenariet forholder seg til to enheter: bankkunder og bankkontoer. Tenk på at kunder kan ha flere kontoer, og kontoer kan ha flere kunder. Når en konto har flere kunder, kalles de ofte felles kontoinnehavere.

Å modellere disse enhetene er ganske enkelt. En dimensjonstabell lagrer kontoer, og en annen dimensjonstabell lagrer kunder. Det som er karakteristisk for dimensjonstypetabeller, er at det er en ID-kolonne i hver tabell. For å modellere relasjonen mellom de to tabellene, kreves en tredje tabell. Denne tabellen blir ofte referert til som en mellomtabell. I dette eksemplet er formålet å lagre en rad for hver kundekontotilknytning. Interessant nok, når denne tabellen bare inneholder ID-kolonner, kalles den en faktaløs faktatabell.

Her er et forenklet modelldiagram av de tre tabellene.

Diagram som viser en modell som inneholder tre tabeller. Utformingen er beskrevet i det følgende avsnittet.

Den første tabellen heter Account, og den inneholder to kolonner: AccountID og Account. Den andre tabellen heter AccountCustomer, og den inneholder to kolonner: AccountID og CustomerID. Den tredje tabellen heter Customer, og den inneholder to kolonner: CustomerID og Customer. Det finnes ikke relasjoner mellom noen av tabellene.

To en-til-mange-relasjoner er lagt til for å relatere tabellene. Her er et oppdatert modelldiagram over de relaterte tabellene. En faktatypetabell med navnet Transaksjon er lagt til. Den registrerer kontotransaksjoner. Mellomtabellen og alle ID-kolonnene er skjult.

Diagram som viser at modellen nå inneholder fire tabeller. En-til-mange-relasjoner er lagt til for å relatere til alle tabellene.

For å beskrive hvordan overføringen av relasjonsfilteret fungerer, er modelldiagrammet blitt endret for å vise tabellradene.

Obs!

Det er ikke mulig å vise tabellrader i modelldiagrammet til Power BI Desktop. Det er gjort i denne artikkelen for å støtte diskusjonen med tydelige eksempler.

Diagram som viser at modellen nå viser tabellradene. Raddetaljene for de fire tabellene er beskrevet i det følgende avsnittet.

Raddetaljene for de fire tabellene er beskrevet i den følgende punktlisten.

  • Account-tabellen har to rader:
    • AccountID 1 er for Konto-01
    • AccountID 2 er for Konto-02
  • Customer-tabellen har to rader:
    • CustomerID 91 er for Kunde-91
    • CustomerID 92 er for Kunde–92
  • AccountCustomer-tabellen har tre rader:
    • AccountID 1 er tilknyttet CustomerID 91
    • AccountID 1 er tilknyttet CustomerID 92
    • AccountID 2 er tilknyttet CustomerID 92
  • Transaction-tabellen har tre rader:
    • Dato 1. januar 2019, AccountID 1, Beløp 100
    • Dato 2. februar 2019, AccountID 2, Beløp 200
    • Dato 3. mars 2019, AccountID 1, Beløp -25

La oss se hva som skjer når modellen blir spurt.

Nedenfor er to visualobjekter som oppsummerer Beløp-kolonnen fra Transaksjons-tabellen. Det første visualobjektet grupperes etter konto, og dermed representerer summen av Beløp-kolonnene kontosaldoen. Det andre visualobjektet grupperes etter kunde, og dermed representerer summen av Beløp-kolonnene kundesaldoen.

Diagram som viser to visualobjekter som sitter side ved side i rapporten. Visualobjektene er beskrevet i det følgende avsnittet.

Det første visualobjektet heter Kontosaldo, og har to kolonner: Konto og Beløp. Den viser følgende resultat:

  • Konto-01 saldobeløp er 75
  • Konto-02 saldobeløp er 200
  • Summen er 275

Det andre visualobjektet heter KundeSaldo, og har to kolonner: Kunde og Beløp. Den viser følgende resultat:

  • Kunde-91 saldobeløp er 275
  • Kunde-92 saldobeløp er 275
  • Summen er 275

Et raskt blikk på tabellradene og Kontosaldo-visualobjektet viser at resultatet er riktig, for hver konto og det totale beløpet. Det er fordi hver kontogruppering resulterer i en filteroverføring til Transaksjons-tabellen for den kontoen.

Noe virker imidlertid ikke riktig med Kundesaldo-visualobjektet. Hver kunde i Kundesaldo-visualobjektet har samme saldo som den totale saldoen. Dette resultatet kunne bare være riktig hvis hver kunde var felles kontoinnehaver for hver konto. Det er ikke tilfelle i dette eksempelet. Problemet er relatert til filteroverføring. Det flyter ikke hele veien til Transaksjons-tabellen.

Følg beskrivelsene for relasjonsfilteret fra Kunde-tabellen til Transaksjons-tabellen. Det bør være tydelig at relasjonen mellom Konto- og AccountCustomer-tabellen overføres i feil retning. Filterretningen for denne relasjonen må være angitt for Begge.

Diagram som viser at modellen er oppdatert. Den filtrerer nå i begge retninger.

Diagram som viser de samme to visualobjektene som sitter side ved side. Det første visualobjektet er ikke endret, men det har det andre visualobjektet.

Som forventet har det ikke skjedd noen endringer i Kontosaldo-visualobjektet.

Kundesaldo-visualobjektene viser imidlertid følgende resultat:

  • Kunde-91 saldobeløp er 75
  • Kunde-92 saldobeløp er 275
  • Summen er 275

Kundesaldo-visualobjektet viser nå et riktig resultat: Følg filteranvisningene på egenhånd, og se hvordan kundesaldoen ble beregnet. Forstå også at visualobjekt totalt betyr alle kunder.

Noen som ikke er kjent med modellrelasjonene, kan konkludere med at resultatet er feil. De spør kanskje: Hvorfor er ikke den totale saldoen for Kunde-91 og Kunde-92 lik 350 (75 + 275)?

Svaret på spørsmålet deres ligger i å forstå mange-til-mange-relasjonen. Hver kundesaldo kan representere tillegget av flere kontosaldoer, og kundesaldoen er derfor ikke-additiv.

Relater mange-til-mange-dimensjonsveiledning

Når du har et mange-til-mange-relasjon mellom dimensjonstypetabeller, gir vi følgende veiledning:

  • Legg til hver en-til-mange-relatert enhet som en modelltabell, og forsikre deg om at den har en unik identifikator (ID)-kolonne
  • Legg til en mellomtabell for å lagre tilknyttede enheter
  • Opprett en-til-mange-relasjon mellom de tre tabellene
  • Konfigurer en toveis relasjon for å la filteroverføringen fortsette til faktatypetabellene
  • Når det ikke er passende å ha manglende ID-verdier, setter du Kan nullstilles-egenskapen i ID-kolonnene til FALSE – dataoppdateringen vil da mislykkes hvis manglende verdier er kildereferert
  • Skjul mellomtabellen (med mindre den inneholder flere kolonner eller tiltak som kreves for rapportering)
  • Skjul ID-kolonner som ikke er egnet for rapportering (eksempel, når ID-er er surrogatnøkler)
  • Hvis det er fornuftig å la en ID-kolonne være synlig, må du forsikre deg om at den er på den «ene» skyvningen i relasjonen—skjul alltid «mange»-sidekolonnen. Det gir den beste filterytelsen.
  • For å unngå forvirring eller feiltolkning, kommuniser forklaringer til rapportbrukerne dine – du kan legge til beskrivelser med tekstbokser eller verktøytips for visualobjekthode

Vi anbefaler ikke at du relaterer mange-til-mange-dimensjonstypetabeller direkte. Denne utformings-tilnærmingen krever at du konfigurerer en relasjon med mange-til-mange-kardinalitet. På konseptnivå kan dette oppnås, men det innebærer at de relaterte kolonnene inneholder dupliserte verdier. Det er en godtatt fremgangsmåte, men disse dimensjonstypetabellene har da en ID-kolonne. Dimensjonstypetabeller skal alltid bruke ID-kolonnen som den «ene» siden av en relasjon.

Relatere mange-til-mange-fakta

Det andre mange-til-mange-scenariet omfatter to faktatypetabeller. To faktatypetabeller kan relateres direkte. Denne utformingsteknikken kan være nyttig ved rask og enkel datautforskning. Imidlertid, og for å være tydelig, anbefaler vi generelt ikke denne utformingstilnærmingen. Hvorfor det er slik vil vi forklare senere i denne seksjonen.

La oss vurdere et eksempel som involverer to faktatypetabeller: Ordre og Oppfyllelse. Ordre-tabellen inneholder en rad per ordrelinje, Oppfyllelse-tabellen kan inneholde null eller flere rader per ordrelinje. Radene i Ordre-tabellen representerer salgsordrer. Radene i Oppfyllelse-tabellen representerer ordreelementer som er sendt. En mange-til-mange-relasjon relaterer de to OrdreID-kolonnene, med filteroverføring bare fra Ordre-tabellen (Ordre-filtrene Oppfyllelse).

Diagram som viser en modell som inneholder to tabeller: Ordre og Oppfyllelse.

Relasjonskardinaliteten er angitt for mange-til-mange for å støtte lagring av dupliserte OrdreID-verdier i begge tabellene. I Ordre-tabellen kan dupliserte OrdreID-verdier eksistere fordi en ordre kan ha flere linjer. I Oppfyllelse-tabellen kan det finnes duplikate OrdreID-verdier fordi ordrer kan ha flere linjer, og ordrelinjer kan oppfylles av mange forsendelser.

La oss nå ta en titt på tabellradene: Legg merke til at ordrelinjer i Oppfyllelse-tabellen kan oppfylles av flere forsendelser. (Fraværet av en ordrelinje betyr at ordren ennå ikke skal oppfylles.)

Diagram som viser at modellen nå viser tabellradene. Raddetaljene for de to tabellene er beskrevet i det følgende avsnittet.

Raddetaljene for de to tabellene er beskrevet i den følgende punktlisten:

  • Ordre-tabellen har fem rader:
    • Ordredato 1. januar 2019, OrdreID 1, Ordrelinje 1, ProduktID Produkt-A, Ordreantall 5, Salg 50
    • Ordredato 1. januar 2019, OrdreID 1, Ordrelinje 2, ProduktID Produkt-B, Ordreantall 10, Salg 80
    • Ordredato 2. februar 2019, OrdreID 2, Ordrelinje 1, ProduktID Produkt-B, Ordreantall 5, Salg 40
    • Ordredato 2. februar 2019, OrdreID 2, Ordrelinje 2, ProduktID Produkt-C, Ordreantall 1, Salg 20
    • Ordredato 3. mars 2019, OrdreID 3, Ordrelinje 1, ProduktID Produkt-C, Ordreantall 5, Salg 100
  • Oppfyllelse-tabellen har fire rader:
    • Oppfyllelsesdato 1. januar 2019, OppfyllelseID 50, OrdreID 1, Ordrelinje 1, Oppfyllelsesantall 2
    • Oppfyllelsesdato 2. februar 2019, OppfyllelseID 51, OrdreID 2, Ordrelinje 1, Oppfyllelsesantall 5
    • Oppfyllelsesdato 2. februar 2019, OppfyllelseID 52, OrdreID 1, Ordrelinje 1, Oppfyllelsesantall 3
    • Oppfyllelsesdato 1. januar 2019, OppfyllelseID 53, OrdreID 1, Ordrelinje 2, Oppfyllelsesantall 10

La oss se hva som skjer når modellen blir spurt. Her er en visuell tabell som sammenligner ordre- og oppfyllelsesantall etter Ordre-tabell OrdreID-kolonne.

Diagram som viser et tabell-visualobjekt med tre kolonner: OrdreID, Ordreantall og Oppfyllelsesantall.

Visualobjektet presenterer et nøyaktig resultat. Nytten av modellen er imidlertid begrenset – du kan bare filtrere eller gruppere etter Ordre-tabellen OrdreID-kolonnen.

Relatere mange-til-mange-faktaveiledning

Generelt anbefaler vi ikke å relatere to faktatypetabeller direkte ved å bruke mange-til-mange-kardinalitet. Hovedårsaken er fordi modellen ikke gir fleksibilitet på måtene du rapporterer visualobjektfilter eller -gruppe. I eksemplet er det bare mulig for visualobjekter å filtrere eller gruppere etter Ordre-tabellen OrdreID-kolonnen. En annen grunn relaterer seg til kvaliteten på dataene dine. Hvis dataene dine har integritetsproblemer, er det mulig at noen rader kan bli utelatt under spørring på grunn av den begrensede relasjonen. Se Modellrelasjoner i Power BI Desktop (relasjonsevaluering) for mer informasjon.

I stedet for å relatere faktatypetabeller direkte, anbefaler vi at du tar i bruk Stjerneskjema-utformingsprinsippene. Dette gjør du ved å legge til dimensjonstypetabeller. Dimensjonstypetabellene relaterer seg deretter til faktatypetabellene ved å bruke en-til-mange-relasjoner. Denne tilnærmingen til utformingen er robust da den leverer fleksible rapporteringsalternativer. Den lar deg filtrere eller gruppere ved hjelp av en hvilken som helst av dimensjonstypekolonnene, og oppsummerer en hvilken som helst relatert faktatypetabell.

La oss vurdere en bedre løsning.

Diagram som viser en modell som inkluderer seks tabeller: Ordrelinje, Ordredato, Ordre, Oppfyllelse, Produkt og Oppfyllelsesdato.

Legg merke til følgende endringer av utforming:

  • Modellen har nå fire tilleggstabeller: Ordrelinje, Orderdato, Produkt, og Oppfyllelsesdato
  • De fire tilleggstabellene er alle dimensjonstypetabeller, og en-til-mange-relasjoner relaterer disse tabellene til faktatypetabellene
  • Ordrelinje-tabellen inneholder en OrdrelinjeID-kolonne, som representerer OrdreID-verdien multiplisert med 100, pluss Ordrelinje-verdien – en unik identifikator for hver ordrelinje
  • Ordre and Oppfyllelse-tabeller inneholder nå en OrdrelinjeID-kolonne, og inneholder ikke lenger OrdreID og Ordrelinje-kolonner
  • Oppfyllelse-tabellen inneholder nå Ordredato og ProduktID-kolonner
  • Oppfyllelsesdato-tabellen relaterer seg bare til Oppfyllelse-tabellen
  • Alle unike identifikatorkolonner er skjult

Å ta seg tid til å bruk stjerneskjema-utformingsprinsipper gir følgende fordeler:

  • Dine rapportvisualiseringer kan filtrere eller gruppere etter en hvilken som helst synlig kolonne fra dimensjonstypetabellene
  • Dine rapportvisualiseringer kan filtrere eller gruppere etter en hvilken som helst synlig kolonne fra dimensjonstypetabellene
  • Filtre brukt på Ordrelinje, Ordredato eller Produkt-tabellene vil overføres til begge faktatypetabellene
  • Alle relasjoner er en-til-mange, og hver relasjon er en vanlig relasjon. Dataintegreitetsproblemer vil ikke bli maskert. Se Modellrelasjoner i Power BI Desktop (relasjonsevaluering) for mer informasjon.

Relater høyere nivå fakta

Dette mange-til-mange-scenariet er veldig forskjellig fra de andre to, som allerede er beskrevet i denne artikkelen.

La oss vurdere et eksempel som involverer fire tabeller: Dato, Salg, Produkt, og Mål. Dato og Produkt er dimensjonstypetabeller, og en-til-mange-relasjoner relateres til Salg-faktatypetabeller. Så langt representerer det en god stjerneskjema-utforming. Mål-tabellen relaterer seg imidlertid ennå ikke til de andre tabellene.

Diagram som viser en modell som inneholder fire tabeller: Dato, Salg, Produkt og Mål.

Mål-tabellen inneholder tre kolonner: Kategori, Antall måll, og Mål-år. Tabellradene avdekker en årlig kornethet og produktkategori. Med andre ord settes mål – som brukes til å måle salgsytelsen – hvert år for hver produktkategori.

Diagram som viser at måltabellen har tre kolonner: Målår, Kategori og Antallmål.

Fordi Mål-tabellen lagrer data på et høyere nivå enn dimensjonstypetabellene, kan det ikke opprettes en en-til-mange-relasjon. Vel, det er sant for bare en av relasjonene. La oss utforske hvordan Mål-tabellen kan relateres til dimensjonstypetabellene.

Relatere tidsperioder for høyere nivå

En relasjon mellom Dato og Mål-tabellene bør være en en-til-mange-relasjon. Det er fordi Målår-kolonneverdiene er datoer. I dette eksemplet er hver Målår-kolonneverdi den første datoen for målåret.

Tips!

Når du lagrer fakta på en høyere tidstetthet enn dag, angir du kolonnedatatypen til Dato (eller Hele nummer hvis du bruker datanøkler). Lagre en verdi i kolonnen som representerer den første dagen i tidsperioden. Eksempel: En årstid blir registrert som 1. januar i året, og en månedsperiode registreres som den første dagen i den måneden.

Imidlertid må man sørge for at måneds- eller datonivåfiltre gir et meningsfullt resultat. Uten noen spesiell beregningslogikk kan rapportvisualiseringer rapportere at måldatoer bokstavelig talt er den første dagen i hvert år. Alle andre dager – og alle måneder unntatt januar – vil oppsummere målantallet som TOM.

Følgende matrisevisualisering viser hva som skjer når rapportbrukeren går fra et år til månedene. Visualiseringen oppsummerer Målantall-kolonnen. (Alternativet Vis elementer uten data er aktivert for matriseradene.)

Diagram som viser et matrise-visualobjekt som viser måltallet for 2020 er 270.

For å unngå denne oppførselen, anbefaler vi deg å kontrollere oppsummeringen av faktadataene dine ved å bruke mål. En måte å kontrollere oppsummeringen på er å returnere TOM når tidsperioder på lavere nivå spørres. En annen måte – definert med noe sofistikert DAX – er å fordele verdier over tidsperioder på lavere nivå.

Vurder følgende måledefinisjon som bruker DAX-funksjonen ISFILTERED. Det returnerer bare en verdi når Dato eller Måned-kolonnene ikke er filtrert.

Target Quantity =
IF(
    NOT ISFILTERED('Date'[Date])
        && NOT ISFILTERED('Date'[Month]),
    SUM(Target[TargetQuantity])
)

Følgende matrisevisualisering bruker nå Målantall-mål. Den viser at alle månedlige målantall er TOMME.

Diagram som viser et matrisevisualobjekt som viser at måltallet for 2020 er 270, med tomme månedsverdier.

Relater høyere nivå (ikke-dato)

En annen tilnærming til utforming er påkrevd når du kobler en ikke-dato-kolonne fra en dimensjonstypetabell til en faktatypetabell (og den har et høyere nivå enn dimensjonstypetabellen).

Kategori-kolonnene (fra både Produkt og Mål-tabellene) inneholder dupliserte verdier. Så det er ingen «en» til en en-til-mange-relasjon. I dette tilfellet må du opprette en mange-til-mange-relasjon. Relasjonen skal opprette filtre i én enkelt retning, fra dimensjonstypetabellen til faktatypetabellen.

Diagram som viser en modell av mål- og produkttabellene. En mange-til-mange-relasjon relaterer de to tabellene.

La oss nå ta en titt på tabellradene:

Diagram som viser en modell som inneholder to tabeller: Mål og Produkt. En mange-til-mange-relasjon relaterer de to kategorikolonnene.

I Mål-tabellen er det nå fire rader: To rader for hvert mål-år (2019 og 2020), og to kategorier (Klær og tilbehør) I Produkt-tabellen er det tre produkter. To tilhører kleskategorien, og en tilhører kategorien tilbehør. En av klesfargene er grønn, og de andre to er blå.

Et visualobjekt for tabeller gruppert etter Kategori-kolonnen fra Produkt-tabellen gir følgende resultat.

Diagram som viser et tabell-visualobjekt med to kolonner: Kategori og Målantall. Tilbehør er 60, Klær er 40 og sluttsummen er 100.

Dette visualobjektet gir det riktige resultatet. La oss nå vurdere hva som skjer når Farge-kolonnen fra Produkt-tabellen brukes til å gruppere målantall.

Diagram som viser et tabell-visualobjekt med to kolonner: Farge og Målantall. Blå er 100, Grønn er 40 og sluttsummen er 100.

Visualobjektet produserer en uriktig fremstilling av dataene. Hva skjer her?

Et filter i Farge-kolonnen fra Produkt-tabellen resulterer i to rader. En av radene er for kategorien Klær, og den andre er for kategorien Tilbehør. Disse to kategoriverdiene blir overført som filtre til Mål-tabellen. Med andre ord, fordi fargen blå brukes av produkter fra to kategorier, brukes disse kategoriene til å filtrere målene.

For å unngå denne oppførselen, anbefaler vi at du kontrollerer oppsummeringen av faktadataene ved å bruke mål.

Vurder følgende måldefinisjon. Legg merke til at alle Produkt-tabellkolonner under kategorinivået er testet for filtre.

Target Quantity =
IF(
    NOT ISFILTERED('Product'[ProductID])
        && NOT ISFILTERED('Product'[Product])
        && NOT ISFILTERED('Product'[Color]),
    SUM(Target[TargetQuantity])
)

Følgende visualobjekt for tabeller bruker nå Målantall-mål. Den viser at alle målantall for farge er TOMME.

Diagram som viser et tabell-visualobjekt med to kolonner: Farge og Målantall. Blå er TOM, Grønn er TOM og sluttsummen er 100.

Den endelige modellutformingen ser ut som følger.

Diagram som viser en modell med dato- og måltabeller relatert til en én-til-mange-relasjon.

Relater høyere nivå fakta-veiledning

Når du trenger å relatere en dimensjonstypetabell til en faktatypetabell, og faktatypetabellen lagrer rader på et høyere nivå enn tabellradene med dimensjonstype, gir vi følgende veiledning:

  • For høyere nivå fakta-datoer:
    • I faktatypetabellen lagrer du den første datoen for tidsperioden
    • Opprett en en-til-mange-relasjon mellom datotabellen og faktatypetabellen
  • For andre høyere nivå fakta:
    • Opprett en mange-til-mange-relasjon mellom dimensjonstypetabellen og faktatypetabellen
  • For begge typer:
    • Kontroll oppsummeringen med mållogikk – returner TOM når dimensjonstypekolonner på lavere nivå brukes til å filtrere eller gruppere
    • Skjul oppsummerbare kolonner av faktatypetabell Skjul oppsummerbare tabellkolonner av faktatypen – på denne måten er det bare mål som kan brukes til å oppsummere faktatypetabellen

Neste trinn

Du finner mer informasjon relatert til denne artikkelen i følgende ressurser: