Veiledning for mange-til-mange-relasjoner

Denne artikkelen er rettet mot deg som en 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.

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

• Relater to dimensjonstabeller
• Relater to faktatabeller
• Relatere faktatabeller med høyere kornnår faktatabellen lagrer rader på et høyere nivå enn dimensjonstabellradene

Relatere mange-til-mange-dimensjoner

Det klassiske mange-til-mange-scenarioet relaterer to enheter, for eksempel 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 vanligvis felles kontoinnehavere.

Modellering av disse enhetene er rett frem. Én dimensjonstabell lagrer kontoer, og en annen dimensjonstabell lagrer kunder. Som det er karakteristisk for dimensjonstabeller, finnes det en unik ID-kolonne (ID) 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 identifikatorkolonner, kalles den en faktaløs faktatabell.

Her er et forenklet diagram over de tre modelltabellene.

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 én-til-mange-relasjoner legges til for å relatere tabellene. Her er et oppdatert modelldiagram over relaterte tabeller. En faktatabell med navnet Transaction er lagt til. Den registrerer kontotransaksjoner. Brotabellen og alle identifikatorkolonnene er skjult.

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

Raddetaljene for de fire tabellene presenteres i følgende punktliste:

• Tabellen Account har to rader:
  • AccountID 1 er for Account-01
  • AccountID 2 er for Account-02
• Tabellen Customer har to rader:
  • CustomerID 91 er for Customer-91
  • CustomerID 92 er for Customer-92
• Tabellen AccountCustomer har tre rader:
  • AccountID 1 er tilknyttet CustomerID91
  • AccountID 1 er tilknyttet CustomerID92
  • AccountID 2 er knyttet til CustomerID92
• Tabellen Transaction har tre rader:
  • Date 1. januar 2019AccountID1, Amount100
  • Date 2. februar 2019AccountID2Amount200
  • Date 3. mars 2019, AccountID1, Amount-25

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

I bildet nedenfor er det to tabellvisualobjekter som oppsummerer Amount kolonnen i Transaction tabellen. Det første visualobjektet grupperer etter konto, og dermed representerer summen av Amount kolonnene kontosaldoen. Det andre visualobjektet grupperer etter kunde, og dermed representerer summen av Amount kolonnene kundesaldoen.

Det første tabellvisualobjektet (Kontosaldo) har to kolonner: Account og Amount. Det viser følgende resultat:

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

Det andre tabellvisualobjektet (Kundesaldo) har to kolonner: Customer og Amount. Det viser følgende resultat:

• saldobeløpet customer-91 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 Transaction-tabellen for denne kontoen.

Noe ser imidlertid ikke riktig ut med visualobjektet Kundesaldo. Hver kunde i dette visualobjektet 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. Det er et problem, og det er relatert til filteroverføring. Filtre flyter ikke helt til Transaction tabellen.

Hvis du følger relasjonsfiltreringsretningene fra tabellen Customer til Transaction tabellen, kan du bestemme at relasjonen mellom Account- og AccountCustomer-tabellen overføres i feil retning. Filterretningen for denne relasjonen må angis til Both.

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

Visualobjektet Kundesaldo viser imidlertid nå følgende resultat:

• saldobeløpet customer-91 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 Customer-91 og Customer-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 derfor er kundesaldoene ikke-additive.

Relater veiledning for mange-til-mange-dimensjoner

Når du har en mange-til-mange-relasjon mellom dimensjonstabeller, følger du denne veiledningen:

• Legg til hver mange-til-mange-relatert enhet som en modelltabell, slik at den har en ID-kolonne.
• Legg til en brotabell for å lagre tilknyttede enheter.
• Opprett én-til-mange-relasjoner mellom de tre tabellene.
• Angi én toveis relasjon for å tillate filteroverføring å fortsette til faktatabellen.
• Når det ikke er aktuelt å ha manglende ID-verdier, deaktiverer du egenskapen Is Nullable – dataoppdatering mislykkes når manglende verdier hentes.
• Skjul brotabellen (med mindre den inneholder andre kolonner eller mål som kreves for rapportering).
• Skjul alle ID-kolonner som ikke er egnet for rapportering (for eksempel når kolonnene lagrer surrogatnøkkelverdier).
• Hvis det er fornuftig å la en ID-kolonne være synlig, må du sørge for at den er på «én»-siden av relasjonen – alltid skjule «mange»-sidekolonnen. Det er fordi filtre brukt på «ett»-lysbildet resulterer i bedre filterytelse.
• Hvis du vil unngå forvirring eller feiltolkning, kan du formidle forklaringer til rapportbrukerne – du kan legge til beskrivelser med tekstbokser eller verktøytips for visuell topptekst.

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

Relater mange-til-mange-fakta

En annen mange-til-mange-scenariotype innebærer å relatere to faktatabeller. To faktatabeller 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 faktatabeller: Order og Fulfillment. Tabellen Order inneholder én rad per ordrelinje, og Fulfillment tabellen kan inneholde null eller flere rader per ordrelinje. Rader i Order tabellen representerer salgsordrer. Rader i Fulfillment-tabellen representerer ordrevarer som er sendt. En mange-til-mange-relasjon relaterer de OrderID kolonnene i hver tabell, med filteroverføring bare fra Order-tabellen (noe som betyr at Order-tabellen filtrerer Fulfillment tabellen).

Relasjonskardinaliteten er satt til Many-to-many for å støtte lagring av dupliserte OrderID kolonneverdier i begge tabellene. Dupliserte ID-verdier kan finnes i tabellen Order fordi en ordre kan ha flere linjer. Dupliserte ID-verdier kan finnes i tabellen Fulfillment 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 tabellen Fulfillment. (Fraværet av en ordrelinje betyr at ordren ennå ikke er oppfylt.)

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

• Tabellen Order har fem rader:
  • OrderDate 1. januar 2019, OrderID1, OrderLine1, ProductIDProd-A, OrderQuantity5, Sales50
  • OrderDate 1. januar 2019, OrderID1, OrderLine2, ProductIDProd-B, OrderQuantity10, Sales80
  • OrderDate 2. februar 2019, OrderID2, OrderLine1, ProductIDProd-B, OrderQuantity5, Sales40
  • OrderDate 2. februar 2019, OrderID2, OrderLine2, ProductIDProd-C, OrderQuantity1, Sales20
  • OrderDate 3. mars 2019, OrderID3, OrderLine1, ProductIDProd-C, OrderQuantity5, Sales100
• Tabellen Fulfillment har fire rader:
  • FulfillmentDate 1. januar 2019FulfillmentID50, OrderID1, OrderLine1, FulfillmentQuantity2
  • FulfillmentDate 2. februar 2019, FulfillmentID51, OrderID2, OrderLine1, FulfillmentQuantity5
  • FulfillmentDate 2. februar 2019, FulfillmentID52, OrderID1, OrderLine1, FulfillmentQuantity3
  • FulfillmentDate 1. januar 2019FulfillmentID53, OrderID1, OrderLine2, FulfillmentQuantity10

La oss se hva som skjer når modellen blir spurt. Her er et tabellvisualobjekt som sammenligner ordre- og oppfyllelsesantall etter Order tabell OrderID kolonne.

Visualobjektet gir et nøyaktig resultat. Nytten av modellen er imidlertid begrenset fordi du bare kan filtrere eller gruppere etter Order tabellen OrderID kolonnen.

Relater veiledning for mange-til-mange-fakta

Vanligvis anbefaler vi ikke at du relaterer to faktatabeller direkte ved hjelp av mange-til-mange-kardinalitet. Hovedårsaken er at modellen ikke gir fleksibilitet i måten visualobjekter i rapporten filtrerer eller grupperer på. I eksemplet er det bare mulig for visualobjekter å filtrere eller gruppere etter Order tabell OrderID kolonne. En annen å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 kardinaliteten for mange-til-mann og begrensede relasjoner.

I stedet for å relatere faktatabeller direkte, anbefaler vi at du implementerer et stjerneskjema utforming. Det betyr at du legger til dimensjonstabeller. Disse dimensjonstabellene relaterer seg deretter til faktatabellene ved hjelp av én-til-mange-relasjoner. Denne utformingstilnærmingen er robust ettersom den effektivt leverer fleksible rapporteringsalternativer. Den lar deg filtrere eller gruppere ved hjelp av en av dimensjonstabellkolonnene, og oppsummere kolonner i en relatert faktatabell.

La oss vurdere en bedre løsning.

Legg merke til følgende endringer i utformingen:

• Modellen har nå fire ekstra tabeller: OrderLine, OrderDate, Productog FulfillmentDate.
• De fire ekstra tabellene er alle dimensjonstabeller der én-til-mange-relasjoner relaterer dem til faktatabellene.
• Tabellen OrderLine inneholder OrderLineID kolonnen, som lagrer den OrderID verdien multiplisert med 100, pluss den OrderLine kolonneverdien – en ID for hver ordrelinje.
• Tabellene Order og Fulfillment inneholder nå en OrderLineID kolonne, og de inneholder ikke lenger kolonnene OrderID og OrderLine.
• Tabellen Fulfillment inneholder nå OrderDate og ProductID kolonner.
• Tabellen FulfillmentDate har bare en relasjon til Fulfillment tabellen.
• Alle ID-kolonner er skjult.

Hvis du tar deg tid til å ta i bruk en stjerneskjemautforming, kan du gi følgende fordeler:

• Visualobjektene i rapporten kan filtrere eller gruppere etter en hvilken som helst synlig kolonne fra dimensjonstabellene.
• Visualobjektene i rapporten kan oppsummere alle synlige kolonner fra faktatabellene.
• Filtre som brukes på tabellene OrderLine, OrderDateeller Product overføres til begge faktatabellene.
• Alle relasjoner er én-til-mange, og hver relasjon er en vanlig relasjon. Dataintegritetsproblemer maskeres ikke. Hvis du vil ha mer informasjon om relasjonsevaluering, kan du se Modellrelasjoner i Power BI Desktop.

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: Date, Sales, Productog Target. Tabellene Date og Product er dimensjonstabeller, og én-til-mange-relasjoner relaterer hver til den Sales faktatabellen. Så langt representerer det en god utforming av stjerneskjema. Tabellen Target er imidlertid ennå ikke relatert til de andre tabellene.

Tabellen Target inneholder tre kolonner: Category, TargetQuantityog TargetYear. 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.

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

Relatere tidsperioder for høyere korn

En relasjon mellom Date og Target tabeller bør være en én-til-mange-relasjon. Det er fordi de TargetYear kolonneverdiene er datoer. I dette eksemplet lagrer hver TargetYear kolonne den første datoen i målåret.

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 ned fra et år inn i månedene. Visualobjektet oppsummerer kolonnen TargetQuantity. (Alternativet Vis elementer uten data er aktivert for matriseradene.)

For å 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 ISFILTERED DAX-funksjonen. Den returnerer bare en verdi når Date og Month kolonnene ikke filtreres.

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

Følgende matrisevisualobjekt bruker Target Quantity mål. 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 dimensjonstabell til en faktatabell (og den er høyere enn dimensjonstabellen).

Kolonnene Category (fra både Product- og Target-tabellene) inneholder dupliserte verdier. Så det er ingen «én»-side 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 dimensjonstabellen til faktatabellen.

La oss nå ta en titt på tabellradene.

I tabellen Target 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 tabellen Product. 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 Category-kolonnen fra Product tabellen gir følgende resultat. Dette visualobjektet gir imidlertid det riktige resultatet. La oss nå vurdere hva som skjer når den Color kolonnen fra Product tabellen brukes til å gruppere målantall.

Visualobjektet gir en feilaktig fremstilling av dataene. Hva skjer her?

Et filter på den Color kolonnen fra Product 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 Target tabellen. Med andre ord, fordi fargen blå brukes av produkter fra to kategorier, disse kategoriene brukes 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 Product tabellkolonner 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 Target Quantity mål. 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 dimensjonstabell til en faktatabell, og faktatabellen lagrer rader på et høyere nivå enn dimensjonstabellradene, følger du denne veiledningen:

• For høyere kornfaktadatoer
  • Lagre den første datoen i tidsperioden i faktatabellen.
  • Opprett en én-til-mange-relasjon mellom datotabellen og faktatabellen.
• For andre høyere korn fakta
  • Opprett en mange-til-mange-relasjon mellom dimensjonstabellen og faktatabellen.
• For begge typer
  • Kontrolloppsummering med mållogikk – returner BLANK når dimensjonskolonner på lavere nivå brukes til å filtrere eller gruppere.
  • Skjul sammendragbare faktatabellkolonner – som sikrer at bare mål kan brukes til å oppsummere faktatabellen.

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 Fabric Community
• Forslag? Bidra med ideer for å forbedre Fabric