Vejledning til mange-til-mange-relation

Denne artikel henvender sig til designere af datamodeller, der arbejder med Power BI Desktop. Den indeholder en beskrivelse af tre forskellige scenarier med udformning af mange til mange-relationer. Den indeholder også en vejledning i, hvordan du skaber vellykkede design med dem i dine modeller.

Faktisk er der tre mange til mange-scenarier. De kan forekomme, når du skal:

• Relatere to tabeller af dimensionstypen
• Relatere to tabeller af faktatypen
• Relatere tabeller med fakta på højere detaljeringsniveau, når tabeller af faktatypen gemmer rækker på højere detaljeringsniveau end rækkerne i tabellen af dimensionstypen

Relater mange til mange-dimensioner

Lad os kigge på den første type med et mange til mange-scenarie ved hjælp af et eksempel. I det klassiske scenarie relateres to enheder: bankkunder og bankkonti. Tænk på, at kunderne kan have flere konti, og at konti kan have flere kunder. Når en konto har flere kunder, kaldes de ofte indehavere af fælles konto.

Udformning af disse enheder er ligetil. I en tabel af dimensionstypen gemmes konti, og i en anden tabel af dimensionstypen gemmes kunder. Som det er karakteristisk for tabeller af dimensionstypen, er der en id-kolonne i hver tabel. Der kræves en tredje tabel for at udforme relationen mellem de to tabeller. Denne tabel kaldes ofte for en mellemtabel. I dette eksempel er dets formål at gemme én række for hver kunde-konto-tilknytning. Når denne tabel kun indeholder id-kolonner, kaldes den sjovt nok for en faktatabel uden fakta.

Her er et forenklet modeldiagram over de tre tabeller.

Den første tabel kaldes for Konto og indeholder to kolonner: Konto-id og Konto. Den anden tabel kaldes for KontoKunde og indeholder to kolonner: Konto-id og Kunde-id. Den tredje tabel kaldes for Kunde og indeholder to kolonner: Kunde-id og Kunde. Der findes ingen relationer mellem nogen af tabellerne.

Der tilføjes to én til mange-relationer for at relatere tabellerne. Her er et opdateret modeldiagram over de relaterede tabeller. En tabel af faktatypen med navnet Transaktion er blevet tilføjet. Den registrerer kontotransaktioner. Mellemtabellen og alle id-kolonner er blevet skjult.

For at hjælpe med at beskrive, hvordan filteroverførslen af relationen fungerer, er modeldiagrammet blevet redigeret for at vise tabelrækkerne.

Rækkedetaljerne for de fire tabeller er beskrevet i følgende punktopstilling:

• Tabellen Konto indeholder to rækker:
  • Konto-id 1 er for Konto-01
  • Konto-id 2 er for Konto-02
• Tabellen Kunde indeholder to rækker:
  • Kunde-id 91 er for Kunde-91
  • Kunde-id 92 er for Kunde-92
• Tabellen KontoKunde indeholder tre rækker:
  • Konto-id 1 er knyttet til Kunde-id 91
  • Konto-id 1 er knyttet til Kunde-id 92
  • Konto-id 2 er knyttet til Kunde-id 92
• Tabellen Transaktion indeholder tre rækker:
  • Dato 1. januar 2019, Konto-id 1, Beløb 100
  • Dato 2. februar 2019, Konto-id 2, Beløb 200
  • Dato 3. marts 2019, Konto-id 1, Beløb -25

Lad os se, hvad der sker, når der sendes en forespørgsel til modellen.

Herunder er to visualiseringer, hvor kolonnen Beløb fra tabellen Transaktion opsummeres. Den første visualisering grupperes efter konto, så summen af kolonnerne Beløb repræsenterer kontosaldoen. Den anden visualisering grupperes efter kunde, så summen af kolonnerne Beløb repræsenterer kundesaldoen.

Den første visualisering kaldes for Kontosaldo og indeholder to kolonner: Konto og Beløb. Følgende resultat vises:

• Konto-01 saldobeløbet er 75
• Konto-02 saldobeløbet er 200
• Totalen er 275

Den anden visualisering kaldes for Kundesaldo og indeholder to kolonner: Kunde og Beløb. Følgende resultat vises:

• Kunde-91 saldobeløbet er 275
• Kunde-92 saldobeløbet er 275
• Totalen er 275

Et hurtigt overblik over tabelrækkerne og visualiseringen Kontosaldo viser, at resultatet er korrekt for hver konto og det samlede beløb. Det skyldes, at hver kontogruppering resulterer i en filteroverførsel til tabellen Transaktion for den pågældende konto.

Noget ser dog ikke korrekt ud i visualiseringen Kundesaldo. Hver kunde i visualiseringen Kundesaldo har den samme saldo som den samlede saldo. Dette resultat kan kun være korrekt, hvis hver kunde var indehaver af en fælles konto for hver konto. Det er ikke tilfældet i dette eksempel. Problemet er relateret til filteroverførsel. Det overføres ikke helt til tabellen Transaktion.

Følg filterretninger for relationen fra tabellen Kunde til tabellen Transaktion. Det bør være tydeligt, at relationen mellem tabellen Konto og KontoKunde overføres i den forkerte retning. Filterretningen for denne relation skal være angivet til Begge.

Som forventet er der ingen ændring af visualiseringen Kontosaldo.

Følgende resultat vises nu i visualiseringen Kundesaldo:

• Kunde-91 saldobeløbet er 75
• Kunde-92 saldobeløbet er 275
• Totalen er 275

Det korrekte resultat vises nu i visualiseringen Kundesaldo. Følg selv filterretningerne, og se, hvordan kundesaldiene blev beregnet. Forstå også, at visualiseringens total betyder alle kunder.

En person, der ikke er bekendt med modelrelationer, ville måske konkludere, at resultatet er forkert. De spørger måske: Hvorfor er totalen for Kunde-91 og Kunde-92 ikke lig med 350 (75 + 275)?

Svaret på spørgsmålet ligger i forståelsen af mange til mange-relationen. Hver kundesaldo kan repræsentere tilføjelsen af flere kontosaldi, så kundesaldiene er ikke additiv.

Vejledning til relatering af mange til mange-dimensioner

Når du har mange til mange-relationer mellem tabeller af dimensionstypen, giver vi følgende vejledning:

• Tilføj hver mange til mange-relaterede enhed som en model, og kontrollér, at hver indeholder en unik identifikatorkolonne (id)
• Tilføj en mellemtabel for at gemme tilknyttede enheder
• Opret én til mange-relationer mellem de tre tabeller
• Konfigurer én tovejsrelation, så filteroverførsel kan fortsætte til tabeller af faktatypen
• Når det ikke er passende at have manglende id-værdier, skal du angive egenskaben Er nullable for id-kolonnerne til FALSK. Hvis der refereres til manglende værdier, vil dataopdateringen mislykkes
• Skjul mellemtabellen (medmindre den indeholder yderligere kolonner eller målinger, der kræves til rapportering)
• Skjul alle id-kolonner, der ikke er egnet til rapportering (f.eks. når id'erne er surrogatnøgler)
• Hvis det giver mening, at lade en id-kolonne være synlig, skal du sikre, at den er på "én"-siden af relationen – skjul altid "mange"-siden af kolonnen. Det resulterer i den bedste filterfunktion.
• Hvis du vil undgå forvirring eller misforståelser, kan du videregive forklaringer til dine rapportbrugere. Du kan tilføje beskrivelser med tekstfelter eller værktøjstip til visuelle overskrifter

Vi anbefaler ikke, at du relaterer tabeller af dimensionstypen med mange til mange-relationer direkte. Denne designmetode kræver konfiguration af en relation med mange til mange-kardinalitet. Det kan opnås konceptuelt, men det forudsætter, at de relaterede kolonner indeholder dubletværdier. Det er dog en velaccepteret designpraksis, at tabeller af dimensionstypen indeholder en id-kolonne. Tabeller af dimensionstypen skal altid bruge id-kolonnen som "én"-siden af en relation.

Relater fakta med mange til mange-relation

Det andet mange til mange-scenarie omfatter relatering af to tabeller af faktatypen. To tabeller af faktatypen kan relateres direkte. Denne designteknik kan være nyttig til hurtig og nem udforskning af data. Men generelt anbefaler vi dog ikke denne designmetode. Vi forklarer hvorfor senere i dette afsnit.

Lad os se på et eksempel, der omfatter to tabeller af faktatypen: Ordre og Opfyldelse. Tabellen Ordre indeholder én række pr. ordrelinje, og tabellen Opfyldelse kan indeholde ingen eller flere rækker pr. ordrelinje. Rækkerne i tabellen Ordre repræsenterer salgsordrer. Rækker i tabellen Opfyldelse repræsenterer ordreelementer, der er blevet sendt. I en mange til mange-relation relateres de to kolonner Ordre-id kun ved hjælp af filteroverførsel fra tabellen Ordre (Ordre filtrerer Opfyldelse).

Relationskardinaliteten er angivet til mange-til-mange for at understøtte lagring af dubletværdier i Ordre-id i begge tabeller. I tabellen Ordre kan der findes dubletværdier i Ordre-id, da en ordre kan have flere linjer. I tabellen Opfyldelse kan der findes dubletværdier i Ordre-id, da ordrer kan have flere linjer, og ordrelinjer kan opfyldes af flere forsendelser.

Lad os nu se på tabelrækkerne. I tabellen Opfyldelse vil du bemærke, at ordrelinjer kan opfyldes af flere forsendelser. (Manglen på en ordrelinje betyder, at ordren endnu ikke er opfyldt.)

Rækkedetaljerne for de to tabeller er beskrevet i følgende punktopstilling:

• Tabellen Ordre indeholder fem rækker:
  • Ordredato 1. januar 2019, Ordre-id 1, Ordrelinje 1, Produkt-id Prod-A, Ordreantal 5, Salg 50
  • Ordredato 1. januar 2019, Ordre-id 1, Ordrelinje 2, Produkt-id Prod-B, Ordreantal 10, Salg 80
  • Ordredato 2. februar 2019, Ordre-id 2, Ordrelinje 1, Produkt-id Prod-B, Ordreantal 5, Salg 40
  • Ordredato 2. februar 2019, Ordre-id 2, Ordrelinje 2, Produkt-id Prod-C, Ordreantal 1, Salg 20
  • Ordredato 3. marts 2019, Ordre-id 3, Ordrelinje 1, Produkt-id Prod-C, Ordreantal 5, Salg 100
• Tabellen Opfyldelse indeholder fire rækker:
  • Opfyldelsesdato 1. januar 2019, Opfyldelses-id 50, Ordre-id 1, Ordrelinje 1, Opfyldelsesantal 2
  • Opfyldelsesdato 2. februar 2019, Opfyldelses-id 51, Ordre-id 2, Ordrelinje 1, Opfyldelsesantal 5
  • Opfyldelsesdato 2. februar 2019, Opfyldelses-id 52, Ordre-id 1, Ordrelinje 1, Opfyldelsesantal 3
  • Opfyldelsesdato 1. januar 2019, Opfyldelses-id 53, Ordre-id 1, Ordrelinje 2, Opfyldelsesantal 10

Lad os se, hvad der sker, når der sendes en forespørgsel til modellen. Her er en tabelvisualisering, hvor ordre- og opfyldelsesantal sammenlignes efter tabellen Ordre og kolonnen Ordre-id.

Der præsenteres et nøjagtigt resultat i visualiseringen. Modellens anvendelighed er dog begrænset. Du kan kun filtrere eller gruppere efter tabellen Ordre og kolonnen Ordre-id.

Vejledning til relatering af fakta med mange til mange-relation

Generelt anbefaler vi ikke, at du relaterer to tabeller af faktatypen direkte ved hjælp af mange til mange-kardinalitet. Den primære årsag er, at modellen ikke giver fleksibilitet i den måde, som rapportvisualiseringerne filtrerer eller grupperer på. I eksemplet er det kun muligt for visualiseringer at filtrere eller gruppere efter tabellen Ordre og kolonnen Ordre-id. En yderligere årsag vedrører kvaliteten af dine data. Hvis der er integritetsproblemer med dine data, udelades nogle rækker muligvis under forespørgsler på grund af den begrænsede relation. Du kan finde flere oplysninger i Modelrelationer i Power BI Desktop (Evaluering af relationer).

I stedet for at relatere tabeller af faktatypen direkte, anbefaler vi, at du indfører principper for design af et stjerneskema. Det gør du ved at tilføje tabeller af dimensionstypen. Tabeller af dimensionstypen relateres derefter til tabeller af faktatypen ved hjælp af én til mange-relationer. Denne designmetode er robust, da den giver fleksible rapporteringsmuligheder. Den giver dig mulighed for at filtrere eller gruppere ved hjælp af en hvilken som helst af kolonnerne i dimensionstypen og opsummere en hvilken som helst tabel af faktatypen.

Lad os se på en bedre løsning.

Bemærk følgende designændringer:

• Modellen indeholder nu fire ekstra tabeller: Ordrelinje, Ordredato, Produkt og Opfyldelsesdato
• De fire ekstra tabeller er tabeller af dimensionstypen, og disse tabeller er relateret til tabeller af faktatypen ved hjælp af én til mange-relationer
• Tabellen Ordrelinje indeholder kolonnen Ordrelinje-id, der repræsenterer værdien Ordre-id ganget med 100 samt værdien Ordrelinje – et entydigt id for hver ordrelinje
• Tabellerne Ordre og Opfyldelse indeholder nu kolonnen Ordrelinje-id, og de indeholder ikke længere kolonnerne Ordre-id og Ordrelinje
• Tabellen Opfyldelse indeholder nu kolonnerne Ordredato og Produkt-id
• Tabellen Opfyldelsesdato er kun relateret til tabellen Opfyldelse
• Alle kolonner med entydige id'er er skjulte

Hvis du tager dig tid til at anvende principper for design af et stjerneskema, får du følgende fordele:

• Dine rapportvisualiseringer kan filtrere eller gruppere efter en hvilken som helst synlig kolonne fra tabeller af dimensionstypen
• Dine rapportvisualiseringer kan opsummere en hvilken som helst synlig kolonne fra tabeller af faktatypen
• Filtre, der er anvendt på tabellerne Ordrelinje, Ordredato eller Produkt, overføres til begge tabeller af faktatypen
• Alle relationer er én til mange-relationer, og hver relation er en almindelig relation. Problemer med dataintegritet maskeres ikke. Du kan finde flere oplysninger i Modelrelationer i Power BI Desktop (Evaluering af relationer).

Relater fakta på højere detaljeringsniveau

Dette mange til mange-scenarie er meget anderledes end de to, der allerede er beskrevet i denne artikel.

Lad os kigge på et eksempel med fire tabeller: Dato, Salg, Produkt og Mål. Dato og Produkt er tabeller af dimensionstypen, og ved hjælp af én til mange-relationer relateres hver til tabellen Salg, som er af faktatypen. Indtil videre repræsenterer det et godt design for et stjerneskema. Tabellen Mål er dog endnu ikke relateret til de andre tabeller.

Tabellen Mål indeholder tre kolonner: Kategori, Målantal og Målår. Tabelrækkerne viser en granularitet på år og produktkategori. Det vil sige, at mål, der bruges til at måle salgspræstation, angives hvert år for hver produktkategori.

Da data gemmes på et højere niveau i tabellen Mål end tabeller af dimensionstypen, kan der ikke oprettes en én til mange-relation. Det gælder i hvert fald for én af relationerne. Lad os se på, hvordan tabellen Mål kan relateres til tabellerne af dimensionstypen.

Relater tidsperioder på højere detaljeringsniveau

En relation mellem tabellerne Dato og Mål skal være en én til mange-relation. Det skyldes, at kolonneværdierne for Målår er datoer. I dette eksempel er hver kolonneværdi af typen Målår den første dato i målåret.

Du skal dog være omhyggelig med at sikre, at filtre på måneds- eller datoniveau giver et meningsfyldt resultat. Uden nogen speciel beregningslogik kan den første dag i hvert år bogstaveligt talt blive rapporteret som måldatoer i rapportvisualiseringer. Alle andre dage – og alle måneder undtagen januar – vil opsummere målantallet som TOMT.

I følgende matrixvisualisering kan du se, hvad der sker, når rapportbrugeren zoomer ind fra et år til dets måneder. Kolonnen Målantal opsummeres i visualiseringen. (Indstillingen Vis elementer uden data er aktiveret for matrixrækker.)

For at undgå denne funktionsmåde anbefaler vi, at du styrer opsummeringen af faktadataene ved hjælp af målinger. En måde at styre opsummeringen på er ved at returnere TOM, når der forespørges om tidsperioder på lavere niveauer. En anden måde, som defineres ved hjælp af nogle avancerede DAX-metoder, er at fordele værdier på tværs af tidsperioder på lavere niveauer.

Overvej følgende målingsdefinition, hvor DAX-funktionen ISFILTERED bruges. Der returneres kun en værdi, når kolonnerne Dato eller Måned ikke er filtreret.

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

Målingen Målantal bruges nu i følgende matrixvisualisering. Alle månedlige målantal vises som TOMME.

Relater højere detaljeringsniveau (ikke dato)

Der kræves en anden designmetode, når en kolonne, der ikke er en dato, skal relateres fra en tabel af dimensionstypen til en tabel af faktatypen (og den er på et højere detaljeringsniveau end tabellen af dimensionstypen).

Kolonnerne Kategori (både fra tabellen Produkt og Mål) indeholder dubletværdier. Det betyder, at der ikke er nogen "én" til en én til mange-relation. I dette tilfælde skal du oprette en mange til mange-relation. Relationen skal overføre filtre i en enkelt retning – fra tabellen af dimensionstypen til tabellen af faktatypen.

Lad os nu se på tabelrækkerne.

I tabellen Mål er der fire rækker: to rækker for hvert målår (2019 og 2020) og to kategorier (Beklædning og Tilbehør). I tabellen Produkt er der tre produkter. To tilhører kategorien Beklædning, og én tilhører kategorien Tilbehør. En af farverne for Beklædning er grøn, og de resterende to er blå.

En gruppering af tabelvisualiseringen efter kolonnen Kategori fra tabellen Produkt giver følgende resultat.

Denne visualisering indeholder det rigtige resultat. Lad os se på, hvad der sker, når kolonnen Farve fra tabellen Produkt bruges til at gruppere målantallet.

Der sker en forkert fremstilling af dataene i visualiseringen. Hvorfor sker det?

Et filter på kolonnen Farve fra tabellen Produkt resulterer i to rækker. En af rækkerne er for kategorien Beklædning, og den anden er for kategorien Tilbehør. Disse to kategoriværdier overføres som filtre til tabellen Mål. Med andre ord, da farven blå bruges af produkter fra to kategorier, bruges disse kategorier til at filtrere målene.

For at undgå denne funktionsmåde anbefaler vi, at du styrer opsummeringen af faktadataene ved hjælp af målinger, som beskrevet tidligere.

Se følgende målingsdefinition. Bemærk, at alle kolonner i tabellen Produkt, der er under kategoriniveauet, er testet for filtre.

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

Målingen Målantal bruges nu i følgende tabelvisualisering. Alle farver for målantal vises som TOMME.

Det endelige modeldesign ser ud som følger.

Vejledning til relatering af fakta på højere detaljeringsniveau

Når du har brug for at relatere en tabel af dimensionstypen til en tabel af faktatypen, og tabellen af faktatypen gemmer rækker på et højere detaljeringsniveau end rækkerne i tabellen af dimensionstypen, giver vi følgende vejledning:

• For datoer med fakta på højere detaljeringsniveau:
  • I tabellen af faktatypen skal du gemme den første dato i tidsperioden
  • Opret en én til mange-relation mellem tabellen Dato og tabellen af faktatypen
• For fakta på højere detaljeringsniveau:
  • Opret en mange til mange-relation mellem tabellen af dimensionstypen og tabellen af faktatypen
• For begge typer:
  • Styr opsummering ved hjælp af målingslogik. Returner TOM, når kolonnerne af dimensionstypen på et lavere niveau bruges til at filtrere eller gruppere
  • Skjul opsummerede kolonner i tabeller af faktatypen. På denne måde er det kun målinger, der kan bruges til at opsummere tabeller af faktatypen

Næste trin

Du kan finde flere oplysninger, der er relateret til denne artikel, i følgende ressourcer:

• Modelrelationer i Power BI Desktop
• Forstå, hvad et stjerneskema er, og hvorfor det er vigtigt for Power BI
• Vejledning til fejlfinding af relationer
• Har du spørgsmål? Prøv at spørge Power BI-community'et
• Forslag? Få ideer til at forbedre Power BI