Veiledning for mange-til-mange-relasjoner

Denne artikkelen er rettet mot deg som datamodellerer som arbeider med Power BI Desktop. Den beskriver tre forskjellige mange-til-mange-modelleringsscenarioer. Den gir deg også veiledning om hvordan du kan utforme dem i modellene dine.

Merk

En innføring i modellrelasjoner dekkes ikke i denne artikkelen. Hvis du ikke er helt kjent med relasjoner, egenskaper eller hvordan du konfigurerer dem, anbefaler vi at du først leser modellrelasjonene i Power BI Desktop-artikkelen .

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

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

• Relater to dimensjonstypetabeller
• Relater to faktatypetabeller
• Relater høyere faktatypetabeller, når faktatypetabellen lagrer rader på et høyere nivå enn tabellradene for dimensjonstype

Merk

Power BI støtter nå mange-til-mange-relasjoner. Hvis du vil ha mer informasjon, kan du se Bruke mange-mange-relasjoner i Power BI Desktop.

Relatere mange-til-mange-dimensjoner

La oss vurdere den første mange-til-mange-scenariotypen med et eksempel. Det klassiske scenarioet relaterer 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.

Modellering av disse enhetene er rett frem. Én dimensjonstypetabell lagrer kontoer, og en annen dimensjonstabell lagrer kunder. I likhet med dimensjonstypetabeller er det en ID-kolonne i hver tabell. Hvis du vil modellere relasjonen mellom de to tabellene, kreves en tredje tabell. Denne tabellen kalles ofte en brotabell. I dette eksemplet er det formål å lagre én 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 over de tre tabellene.

Den første tabellen heter Konto, 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 Kunde, og den inneholder to kolonner: Kunde-ID og Kunde. Det finnes ikke relasjoner mellom noen av tabellene.

To én-til-mange-relasjoner legges til for å relatere tabellene. Her er et oppdatert modelldiagram over relaterte tabeller. En faktatypetabell med navnet Transaksjon er lagt til. Den registrerer kontotransaksjoner. Brotabellen og alle ID-kolonnene er skjult.

For å beskrive hvordan overføring av relasjonsfilter fungerer, er modelldiagrammet endret for å vise tabellradene.

Merk

Det er ikke mulig å vise tabellrader i modelldiagrammet for Power BI Desktop. Det gjøres i denne artikkelen for å støtte diskusjonen med klare eksempler.

Raddetaljene for de fire tabellene er beskrevet i følgende punktliste:

• Kontotabellen har to rader:
  • AccountID 1 er for Account-01
  • AccountID 2 er for Account-02
• Kundetabellen har to rader:
  • CustomerID 91 er for Customer-91
  • CustomerID 92 er for Customer-92
• AccountCustomer-tabellen har tre rader:
  • AccountID 1 er knyttet til CustomerID 91
  • AccountID 1 er knyttet til CustomerID 92
  • AccountID 2 er knyttet til CustomerID 92
• Transaksjonstabellen 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 finner du to visualobjekter som oppsummerer Beløp-kolonnen fra Transaksjon-tabellen . Det første visualobjektet grupperer etter konto, og dermed representerer summen av Beløp-kolonnene kontosaldoen. Det andre visualobjektet grupperer etter kunde, og dermed representerer summen av Beløp-kolonnene kundesaldoen.

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

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

Det andre visualobjektet har tittelen Kundesaldo, og den har to kolonner: Kunde og Beløp. Det 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 kontosaldovisualobjektet viser at resultatet er riktig, for hver konto og det totale beløpet. Det er fordi hver kontogruppering resulterer i en filteroverføring til transaksjonstabellen for denne kontoen.

Noe ser imidlertid ikke riktig ut med visualobjektet Kundesaldo . Hver kunde i visualobjektet Kundesaldo har samme saldo som den totale saldoen. Dette resultatet kan bare være riktig hvis hver kunde var en felles kontoinnehaver av hver konto. Det er ikke tilfelle i dette eksemplet. Problemet er relatert til filteroverføring. Den flyter ikke helt til Transaksjon-tabellen .

Følg relasjonsfiltreringsretningene fra Kunde-tabellen til Transaksjon-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 satt til Begge.

Som forventet har det ikke vært noen endring i visualobjektet kontosaldo .

Visualobjektene for kundesaldo viser imidlertid nå følgende resultat:

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

Visualobjektet Kundesaldo viser nå et riktig resultat. Følg filterretningene for deg selv, og se hvordan kundesaldoene ble beregnet. Forstå også at totalen for visualobjektet betyr alle kunder.

Noen som ikke er kjent med modellrelasjonene, kan konkludere med at resultatet er feil. De kan spørre: 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-forholdet. Hver kundesaldo kan representere tillegg av flere kontosaldoer, og dermed er kundesaldoene ikke-additive.

Relater veiledning for mange-til-mange-dimensjoner

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

• Legg til hver mange-til-mange-relatert enhet som en modelltabell, slik at den har en unik identifikatorkolonne (ID)
• Legge til en brotabell for å lagre tilknyttede enheter
• Opprette én-til-mange-relasjoner mellom de tre tabellene
• Konfigurer én toveis relasjon slik at filteroverføring kan fortsette til faktatypetabellene
• Når det ikke er aktuelt å ha manglende ID-verdier, angir du egenskapen Er nullverdi for ID-kolonner til USANN. Dataoppdateringen vil da mislykkes hvis manglende verdier hentes
• Skjul brotabellen (med mindre den inneholder flere kolonner eller mål som kreves for rapportering)
• Skjul alle ID-kolonner som ikke er egnet for rapportering (for eksempel når ID-er er surrogatnøkler)
• Hvis det er fornuftig å la en ID-kolonne være synlig, må du sørge for at den er på «ett»-lysbildet i relasjonen – alltid skjule «mange»-sidekolonnen. Det resulterer i den beste filterytelsen.
• Hvis du vil unngå forvirring eller feiltolkning, kan du formidle forklaringer til rapportbrukerne – du kan legge til beskrivelser med tekstbokser eller verktøytips for visualobjekthode

Vi anbefaler ikke at du relaterer mange-til-mange dimensjonstypetabeller direkte. Denne utformingstilnærmingen krever konfigurering av en relasjon med en mange-til-mange-kardinalitet. Begrepsmessig kan det oppnås, men det innebærer at de relaterte kolonnene vil inneholde dupliserte verdier. Det er imidlertid en godtatt utformingspraksis at dimensjonstypetabeller har en ID-kolonne. Dimensjonstypetabeller bør alltid bruke ID-kolonnen som «én»-siden i en relasjon.

Relater mange-til-mange-fakta

Den andre mange-til-mange-scenariotypen innebærer å relatere to faktatypetabeller. To faktatypetabeller kan være relatert direkte. Denne utformingsteknikken kan være nyttig for rask og enkel datautforskning. Men for å være klar, anbefaler vi vanligvis ikke denne utformingstilnærmingen. Vi forklarer hvorfor senere i denne delen.

La oss vurdere et eksempel som omfatter to faktatypetabeller: Ordre og oppfyllelse. Ordretabellen inneholder én rad per ordrelinje, og Oppfyllelse-tabellen kan inneholde null eller flere rader per ordrelinje. Rader i Ordre-tabellen representerer salgsordrer. Rader i Oppfyllelse-tabellen representerer ordrevarer som er sendt. En mange-til-mange-relasjon relaterer de to OrderID-kolonnene , med filteroverføring bare fra Ordre-tabellen (OrdrefiltreOppfyllelse).

Relasjonskardinaliteten er satt til mange-til-mange for å støtte lagring av dupliserte OrdreID-verdier i begge tabellene. Dupliserte OrdreID-verdier kan finnes i ordretabellen fordi en ordre kan ha flere linjer. I Oppfyllelse-tabellen kan dupliserte OrdreID-verdier eksistere 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 kan oppfylles av flere forsendelser i oppfyllelsestabellen. (Fraværet av en ordrelinje betyr at ordren ennå ikke er oppfylt.)

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

• Ordretabellen har fem rader:
  • Ordredato 1. januar 2019, OrdreID 1, Ordrelinje 1, ProduktID Prod-A, Ordreantall 5, Salg 50
  • Ordredato 1. januar 2019, OrdreID 1, Ordrelinje 2, ProductID Prod-B, OrderQuantity 10, Salg 80
  • OrderDate February 2 2019, OrderID 2, OrderLine 1, ProductID Prod-B, OrderQuantity 5, Sales 40
  • Ordredato 2. februar 2019, OrdreID 2, Ordrelinje 2, ProduktID Prod-C, Ordreantall 1, Salg 20
  • Ordredato 3. mars 2019, OrdreID 3, Ordrelinje 1, ProductID Prod-C, OrderQuantity 5, Salg 100
• Oppfyllelse-tabellen har fire rader:
  • Oppfyllelsesdato 1. januar 2019, OppfyllelseID 50, OrdreID 1, Ordrelinje 1, OppfyllelseAntall 2
  • Oppfyllelsesdato 2. februar 2019, OppfyllelseID 51, OrdreID 2, Ordrelinje 1, OppfyllelseAntall 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 et visualobjekt for tabeller som sammenligner ordre- og oppfyllelsesantall etter Ordre-tabellens OrdreID-kolonne.

Visualobjektet gir et nøyaktig resultat. Men nytten av modellen er begrenset – du kan bare filtrere eller gruppere etter ordretabellen OrderID-kolonnen.

Relater veiledning for mange-til-mange-fakta

Vanligvis anbefaler vi ikke å relatere to faktatypetabeller direkte ved hjelp av mange-til-mange kardinalitet. Hovedårsaken er at modellen ikke gir fleksibilitet på måtene du rapporterer visualobjekter filtrerer eller grupperer på. I eksemplet er det bare mulig for visualobjekter å filtrere eller gruppere etter Order-tabellen OrderID-kolonnen. En ekstra årsak er knyttet til kvaliteten på dataene. Hvis dataene har integritetsproblemer, er det mulig at noen rader kan utelates under spørring på grunn av innholdet i den begrensede relasjonen. Hvis du vil ha mer informasjon, kan du se Modellrelasjoner i Power BI Desktop (Relasjonsevaluering).

I stedet for å relatere faktatypetabeller direkte, anbefaler vi at du bruker utformingsprinsipper for Stjerneskjema . Du gjør det ved å legge til dimensjonstypetabeller. Dimensjonstypetabellene relaterer seg deretter til faktatypetabellene ved hjelp av én-til-mange-relasjoner. Denne utformingstilnærmingen er robust ettersom den leverer fleksible rapporteringsalternativer. Den lar deg filtrere eller gruppere ved hjelp av en av dimensjonstypekolonnene, og oppsummere en relatert faktatypetabell.

La oss vurdere en bedre løsning.

Legg merke til følgende endringer i utformingen:

• Modellen har nå fire ekstra tabeller: Ordrelinje, Ordredato, Produkt og Oppfyllelsesdato
• De fire ekstra tabellene er alle dimensjonstypetabeller, og én-til-mange-relasjoner relaterer disse tabellene til faktatypetabellene
• Ordrelinje-tabellen inneholder en OrderLineID-kolonne, som representerer OrdreID-verdien multiplisert med 100, pluss Ordrelinje-verdien – en unik identifikator for hver ordrelinje
• Ordre- og Oppfyllelse-tabellene inneholder nå en OrderLineID-kolonne, og de inneholder ikke lenger kolonnene OrderID og OrderLine
• Oppfyllelse-tabellen inneholder nå OrderDate- og ProductID-kolonner
• Oppfyllelsesdato-tabellen er bare knyttet til Oppfyllelse-tabellen
• Alle unike identifikatorkolonner er skjult

Bruk av utformingsprinsipper for stjerneskjema gir følgende fordeler:

• Rapportvisualobjektene kan filtrere eller gruppere etter en hvilken som helst synlig kolonne fra dimensjonstypetabellene
• Visualobjektene i rapporten kan oppsummere alle synlige kolonner fra faktatypetabellene
• Filtre som brukes i tabellene Ordrelinje, Ordredato eller Produkt overføres til begge faktatypetabellene
• Alle relasjoner er én-til-mange, og hver relasjon er en vanlig relasjon. Dataintegritetsproblemer maskeres ikke. Hvis du vil ha mer informasjon, kan du se Modellrelasjoner i Power BI Desktop (Relasjonsevaluering).

Relatere fakta med høyere korn

Dette mange-til-mange-scenarioet er svært forskjellig fra de to andre som allerede er beskrevet i denne artikkelen.

La oss vurdere et eksempel som involverer fire tabeller: Dato, Salg, Produkt og Mål. Tabellene Dato og Produkt er dimensjonstype, og én-til-mange-relasjoner relaterer hver til faktatypetabellen Salg. Så langt representerer det en god utforming av stjerneskjema. Måltabellen er imidlertid ennå ikke relatert til de andre tabellene.

Måltabellen inneholder tre kolonner: Kategori, Målantall og Målår. Tabellradene viser en detaljnivå for år og produktkategori. Mål – som brukes til å måle salgsytelse – angis med andre ord hvert år for hver produktkategori.

Siden måltabellen lagrer data på et høyere nivå enn dimensjonstypetabellene, kan det ikke opprettes en én-til-mange-relasjon. Vel, det er sant for bare ett av relasjonene. La oss utforske hvordan måltabellen kan være relatert til dimensjonstypetabellene.

Relatere tidsperioder for høyere korn

En relasjon mellom dato- og måltabellene bør være en én-til-mange-relasjon. Det er fordi verdiene for TargetYear-kolonnen er datoer. I dette eksemplet er hver TargetYear-kolonneverdi den første datoen i målåret.

Tips

Når du lagrer fakta med høyere tidstetthet enn dag, angir du kolonnedatatypen til Dato (eller Hvem nummer hvis du bruker datonøkler). I kolonnen lagrer du en verdi som representerer den første dagen i tidsperioden. En årsperiode registreres for eksempel som 1. januar i året, og en månedsperiode registreres som den første dagen i denne måneden.

Forsiktighet må imidlertid tas for å sikre at filtre på måneds- eller datonivå gir et meningsfylt resultat. Uten spesiell beregningslogikk kan rapportvisualobjekter rapportere at måldatoer bokstavelig talt er den første dagen i hvert år. Alle andre dager – og alle måneder unntatt januar – oppsummerer målantallet som BLANK.

Følgende matrisevisualobjekt viser hva som skjer når rapportbrukeren driller fra et år inn i månedene. Visualobjektet oppsummerer TargetQuantity-kolonnen . (Vis elementer uten dataalternativ er aktivert for matriseradene.)

Hvis du vil unngå denne virkemåten, anbefaler vi at du kontrollerer sammendraget av faktadataene ved hjelp av mål. Én måte å kontrollere sammendraget på er å returnere BLANK når tidsperioder på lavere nivå spørres. En annen måte – definert med noen avanserte DAX– er å fordele verdier på tvers av tidsperioder på lavere nivå.

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

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

Følgende matrisevisualobjekt bruker nå målet mål for målantall . Den viser at alle månedlige målantall er BLANK.

Relatere høyere korn (ikke-dato)

En annen utformingstilnærming kreves når du relaterer en ikke-datokolonne fra en dimensjonstypetabell til en faktatypetabell (og den er på et høyere nivå enn dimensjonstypetabellen).

Kategorikolonnene (fra både produkt- og måltabellene) inneholder dupliserte verdier. Så det er ingen «én» for en én-til-mange-relasjon. I dette tilfellet må du opprette en mange-til-mange-relasjon. Relasjonen skal overføre filtre i én retning, fra dimensjonstypetabellen til faktatypetabellen.

La oss nå ta en titt på tabellradene.

I måltabellen er det fire rader: to rader for hvert målår (2019 og 2020) og to kategorier (Klær og tilbehør). Det finnes tre produkter i produkttabellen. To tilhører kleskategorien, og én tilhører tilbehørskategorien. En av klesfargene er grønn, og de resterende to er blå.

En tabellvisualobjektgruppering etter Kategori-kolonnen fra Produkt-tabellen gir følgende resultat.

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

Visualobjektet gir en feilaktig 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 overføres som filtre til måltabellen. Med andre ord, fordi fargen blå brukes av produkter fra to kategorier, brukes disse kategoriene til å filtrere målene.

For å unngå denne virkemåten, som beskrevet tidligere, anbefaler vi at du kontrollerer sammendraget av faktadataene ved hjelp av mål.

Vurder følgende måldefinisjon. Legg merke til at alle produkttabellkolonner som er under kategorinivået, testes for filtre.

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

Tabellvisualobjektet nedenfor bruker nå målet mål for målantall . Den viser at alle fargemålantall er BLANK.

Den endelige modellutformingen ser slik ut.

Relatere veiledning for høyere kornfakta

Når du må 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 faktadatoer med høyere korn:
  • Lagre den første datoen i tidsperioden i faktatypetabellen
  • Opprette en én-til-mange-relasjon mellom datotabellen og faktatypetabellen
• For andre høyere korn fakta:
  • Opprett en mange-til-mange-relasjon mellom dimensjonstypetabellen og faktatypetabellen
• For begge typer:
  • Kontrolloppsummering med mållogikk – returner BLANK når dimensjonstypekolonner på lavere nivå brukes til å filtrere eller gruppere
  • Skjul sammendragbare faktatypetabellkolonner – på denne måten kan bare målinger brukes til å oppsummere faktatypetabellen

Relatert innhold

Hvis du vil ha mer informasjon om denne artikkelen, kan du se følgende ressurser:

• Modellrelasjoner i Power BI Desktop
• Forstå stjerneskjema og viktigheten for Power BI
• Veiledning for feilsøking av relasjoner
• Spørsmål? Prøv å spørre Power BI-fellesskap
• Forslag? Bidra med ideer for å forbedre Power BI