Vägledning för många-till-många-relationer

Den här artikeln riktar sig till dig som datamodellerare som arbetar med Power BI Desktop. Den beskriver tre olika scenarier för många-till-många-modellering. Det ger dig också vägledning om hur du framgångsrikt utformar dem i dina modeller.

Anteckning

En introduktion till modellrelationer beskrivs inte i den här artikeln. Om du inte är helt bekant med relationer, deras egenskaper eller hur du konfigurerar dem rekommenderar vi att du först läser artikeln Model-relationer i Power BI Desktop.

Det är också viktigt att du har en förståelse för stjärnschemadesign. Mer information finns i Förstå stjärnschema och dess betydelse för Power BI.

Det finns tre olika många-till-många-scenarier. De kan inträffa när du måste:

• Relatera två dimensionstabeller
• Relatera två faktatabeller
• Relatera faktatabeller med högre kornighet, när den faktatabellen lagrar rader med ett högre korn än dimensionstabellraderna

Relatera många-till-många-dimensioner

Det klassiska många-till-många-scenariot relaterar två entiteter, till exempel bankkunder och bankkonton. Tänk på att kunder kan ha flera konton och att konton kan ha flera kunder. När ett konto har flera kunder kallas de ofta gemensamma kontoinnehavare.

Modellering av dessa entiteter är enkelt. En dimensionstabell lagrar konton och en annan dimensionstabell lagrar kunder. Som är kännetecknande för dimensionstabeller finns det en unik ID-kolumn (ID) i varje tabell. För att modellera relationen mellan de två tabellerna krävs en tredje tabell. Den här tabellen kallas ofta för en bryggtabell. I det här exemplet är syftet att lagra en rad för varje kundkontoassociation. Intressant nog, när den här tabellen bara innehåller identifierarkolumner, kallas den för en faktalös faktatabell.

Här är ett förenklat diagram över de tre modelltabellerna.

Den första tabellen heter Accountoch innehåller två kolumner: AccountID och Account. Den andra tabellen heter AccountCustomeroch innehåller två kolumner: AccountID och CustomerID. Den tredje tabellen heter Customeroch innehåller två kolumner: CustomerID och Customer. Relationer finns inte mellan någon av tabellerna.

Två en-till-många-relationer läggs till för att relatera tabellerna. Här är ett uppdaterat modelldiagram över de relaterade tabellerna. En faktatabell med namnet Transaction har lagts till. Den registrerar kontotransaktioner. Bryggtabellen och alla identifierarkolumner har dolts.

För att beskriva hur relationsfilterspridningen fungerar har modelldiagrammet ändrats för att visa tabellraderna.

Radinformationen för de fyra tabellerna visas i följande punktlista:

• Tabellen Account har två rader:
  • AccountID 1 gäller Account-01
  • AccountID 2 gäller Konto-02
• Tabellen Customer har två rader:
  • CustomerID 91 gäller Kund-91
  • CustomerID 92 gäller Kund-92
• Tabellen AccountCustomer har tre rader:
  • AccountID 1 är associerad med CustomerID91
  • AccountID 1 är associerad med CustomerID92
  • AccountID 2 är associerad med CustomerID92
• Tabellen Transaction har tre rader:
  • Date 1 januari 2019, AccountID1, Amount100
  • Date 2 februari 2019, AccountID2, Amount200
  • Date 3 mars 2019, AccountID1, Amount-25

Nu ska vi se vad som händer när modellen efterfrågas.

I följande bild finns det två visuella tabellobjekt som sammanfattar kolumnen Amount i den Transaction tabellen. De första visuella objekten grupperas efter konto, så summan av de Amount kolumnerna representerar kontosaldot. Den andra visualiseringen grupperas efter kund, så summan av kolumnerna för Amount representerar kundsaldo.

Det första visuella tabellobjektet (Kontosaldo) har två kolumner: Account och Amount. Det visar följande resultat:

• Konto-01 saldobelopp är 75.
• Konto-02 saldo är 200.
• Summan är 275.

Det andra visuella tabellobjektet (Kundbalans) har två kolumner: Customer och Amount. Det visar följande resultat:

• belopp för kund-91 275.
• belopp för kund-92 är 275.
• Summan är 275.

En snabb överblick över tabellraderna och det visuella objektet Kontosaldo visar att resultatet är korrekt för varje konto och det totala beloppet. Det beror på att varje kontogruppering resulterar i en filterspridning till Transaction-tabellen för det kontot.

Något verkar dock inte korrekt med det visuella objektet Customer Balance. Varje kund i det här visuella objektet har samma saldo som det totala saldot. Det här resultatet kan bara vara korrekt om varje kund var en gemensam kontoinnehavare för varje konto. Så är inte fallet i det här exemplet. Det finns ett problem och det är relaterat till filterspridning. Filter kommer inte hela vägen till tabell Transaction.

Om du följer relationsfilterriktningarna från tabellen Customer till tabellen Transaction kan du fastställa att relationen mellan tabellerna Account och AccountCustomer sprids i fel riktning. Filterriktningen för den här relationen måste vara inställd på Both.

Som förväntat har det inte skett någon ändring i det visuella objektet Kontosaldo.

Det visuella objektet Customer Balance visar dock nu följande resultat:

• belopp för kund-91 75.
• Kund-92 saldo är 275.
• Summan är 275.

Det visuella objektet Customer Balance visar nu ett korrekt resultat. Följ filterriktningarna själv och se hur kundsaldonen beräknades. Förstå också att den visuella summan innebär alla kunder.

Någon som inte känner till modellrelationerna kan dra slutsatsen att resultatet är felaktigt. De kanske frågar: Varför är inte det totala saldot för Customer-91 och Customer-92 lika med 350 (75 + 275)?

Svaret på deras fråga ligger i att förstå många-till-många-relationen. Varje kundsaldo kan representera tillägg av flera kontosaldon, så kundsaldonen är icke-additiva.

Relatera vägledning för många-till-många-dimensioner

När du har en många-till-många-relation mellan dimensionstabeller följer du den här vägledningen:

• Lägg till varje många-till-många-relaterad entitet som en modelltabell, så att den har en ID-kolumn.
• Lägg till en bryggtabell för att lagra associerade entiteter.
• Skapa en-till-många-relationer mellan de tre tabellerna.
• Ange en dubbelriktad relation så att filterspridning kan fortsätta till faktatabellen.
• När det inte är lämpligt att ha saknade ID-värden inaktiverar du egenskapen Is Nullable – datauppdateringen misslyckas när saknade värden hämtas.
• Dölj bryggtabellen (om den inte innehåller andra kolumner eller mått som behövs för rapportering).
• Dölj eventuella ID-kolumner som inte är lämpliga för rapportering (till exempel när kolumnerna lagrar surrogatnyckelvärden).
• Om det är klokt att lämna en ID-kolumn synlig, kontrollerar du att den befinner sig på 'en'-sidan av relationen, och dölj alltid kolumnen på 'många'-sidan. Det beror på att filter som tillämpas på den ena bilden ger bättre filterprestanda.
• För att undvika förvirring eller feltolkning kan du förmedla förklaringar till rapportanvändarna– du kan lägga till beskrivningar med textrutor eller knappbeskrivningar för visuella sidhuvuden.

Vi rekommenderar inte att du relaterar många-till-många-dimensionstabeller direkt. Den här designmetoden kräver att du konfigurerar en relation med kardinaliteten många-till-många. Konceptuellt kan det uppnås, men det innebär att de relaterade kolumnerna kan innehålla duplicerade värden. Det är dock en väl accepterad designpraxis att dimensionstabeller har en ID-kolumn. Dimensionstabeller bör alltid använda ID-kolumnen som "en" sida av en relation.

Relatera många-till-många-fakta

En annan scenariotyp för många-till-många innebär att relatera två faktatabeller. Två faktatabeller kan relateras direkt. Den här designtekniken kan vara användbar för snabb och enkel datautforskning. Men för att vara tydlig rekommenderar vi vanligtvis inte den här designmetoden. Vi förklarar varför senare i det här avsnittet.

Låt oss ta ett exempel som omfattar två faktatabeller: Order och Fulfillment. Tabellen Order innehåller en rad per orderrad och den Fulfillment tabellen kan innehålla noll eller fler rader per orderrad. Rader i tabellen Order representerar försäljningsorder. Rader i tabellen Fulfillment representerar orderobjekt som har levererats. En mång-till-mång-relation relaterar kolumnerna OrderID i varje tabell, med filterspridning endast från tabellen Order (vilket innebär att tabellen Order filtrerar tabellen Fulfillment).

Relationens kardinalitet ställs in på Many-to-many för att möjliggöra lagring av dubbletter av OrderID kolumnvärde i båda tabellerna. I tabellen Order kan duplicerade ID-värden finnas eftersom en ordning kan ha flera rader. I tabellen Fulfillment kan duplicerade ID-värden finnas eftersom beställningar kan ha flera rader och orderrader kan uppfyllas av många försändelser.

Nu ska vi ta en titt på tabellraderna. Observera att orderrader kan uppfyllas av flera försändelser i tabellen Fulfillment. (Avsaknaden av en orderrad innebär att ordern ännu inte har uppfyllts.)

Radinformationen för de två tabellerna beskrivs i följande punktlista:

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

Nu ska vi se vad som händer när modellen efterfrågas. Här är en visualisering av tabellen som jämför order- och försändelsekvantiteter efter Order tabell OrderID kolumn.

Den visuella bilden visar ett korrekt resultat. Modellens användbarhet är dock begränsad eftersom du bara kan filtrera eller gruppera efter Order tabell OrderID kolumn.

Vägledning för hantering av många-till-många-fakta

I allmänhet rekommenderar vi inte att du relaterar två faktatabeller direkt med hjälp av kardinaliteten många-till-många. Den främsta orsaken är att modellen inte ger flexibilitet i hur dina visuella rapportobjekt filtrerar eller grupperar dem. I exemplet är det bara möjligt för visuella objekt att filtrera eller gruppera efter Order tabell OrderID kolumn. En annan orsak är kvaliteten på dina data. Om dina data har integritetsproblem är det möjligt att vissa rader kan utelämnas under frågningen på grund av många-till-många-kardinaliteten och begränsade relationer.

I stället för att relatera faktatabeller direkt rekommenderar vi att du implementerar en stjärnschema-design. Det innebär att du lägger till dimensionstabeller. Dessa dimensionstabeller relaterar sedan till faktatabellerna med hjälp av en-till-många-relationer. Den här designmetoden är robust eftersom den effektivt levererar flexibla rapporteringsalternativ. Du kan filtrera eller gruppera med någon av dimensionstabellkolumnerna och sammanfatta kolumner i en relaterad faktatabell.

Nu ska vi överväga en bättre lösning.

Observera följande designändringar:

• Modellen har nu fyra extra tabeller: OrderLine, OrderDate, Productoch FulfillmentDate.
• De fyra extra tabellerna är alla dimensionstabeller där en-till-många-relationer relaterar dem till faktatabellerna.
• Tabellen OrderLine innehåller kolumnen OrderLineID, som lagrar OrderID-värdet multiplicerat med 100 plus OrderLine kolumnvärdet – ett ID för varje orderrad.
• Tabellerna Order och Fulfillment innehåller nu en OrderLineID kolumn, och de innehåller inte längre kolumnerna OrderID och OrderLine.
• Tabellen Fulfillment innehåller nu OrderDate och ProductID kolumner.
• Tabellen FulfillmentDate har endast en relation till tabellen Fulfillment.
• Alla ID-kolumner är dolda.

Om du tar dig tid att använda en star-schemadesign får du följande fördelar:

• Dina visuella rapportobjekt kan filtrera eller gruppera efter valfri synlig kolumn från dimensionstabellerna.
• Dina rapportvisualiseringar kan sammanfatta valfri synlig kolumn från faktatabellerna.
• Filter som tillämpas på tabellerna OrderLine, OrderDateeller Product sprids till båda faktatabellerna.
• Alla relationer är en till många, och varje relation är en regelbunden relation. Dataintegritetsproblem maskeras inte. Mer information om relationsutvärdering finns i Modellrelationer i Power BI Desktop.

Relatera fakta med högre kornighet

Det här många-till-många-scenariot skiljer sig mycket från de andra två som redan beskrivs i den här artikeln.

Låt oss ta ett exempel med fyra tabeller: Date, Sales, Productoch Target. Tabellerna Date och Product är dimensionstabeller och en-till-många-relationer relaterar var och en till den Sales faktatabellen. Hittills utgör den en bra design av ett stjärnschema. Den Target tabellen är dock ännu inte relaterad till de andra tabellerna.

Tabellen Target innehåller tre kolumner: Category, TargetQuantityoch TargetYear. Tabellraderna visar en detaljnivå för år och produktkategori. Med andra ord anges mål – som används för att mäta försäljningsprestanda – varje år för varje produktkategori.

Eftersom den Target tabellen lagrar data på en högre nivå än dimensionstabellerna kan en en-till-många-relation inte skapas. Tja, det är sant för bara en av relationerna. Nu ska vi utforska hur den Target tabellen kan relateras till dimensionstabellerna.

Relatera tidsperioder med högre kornighet

En relation mellan tabellerna Date och Target bör vara en en-till-många-relation. Det beror på att TargetYear kolumnvärden är datum. I det här exemplet lagrar varje TargetYear kolumn målårets första datum.

Tips

När du lagrar fakta med en högre tidskornighet än dagen anger du kolumndatatypen till Datum (eller Heltal om du använder datumnycklar). I kolumnen lagrar du ett värde som representerar den första dagen i tidsperioden. Till exempel registreras en årsperiod som 1 januari på året och en månadsperiod registreras som den första dagen i den månaden.

Var dock noga med att se till att filter på månad eller datumnivå ger ett meningsfullt resultat. Utan någon särskild beräkningslogik kan visuella rapportobjekt rapportera att måldatum bokstavligen är den första dagen i varje år. Alla andra dagar – och alla månader utom januari – kommer att sammanfatta målkvantiteten som BLANK.

Följande visuella matrisobjekt visar vad som händer när rapportanvändaren ökar detaljnivån från ett år till dess månader. Det visuella objektet sammanfattar kolumnen TargetQuantity. (Alternativet Visa objekt utan data har aktiverats för matrisraderna.)

För att undvika det här beteendet rekommenderar vi att du kontrollerar sammanfattningen av dina faktadata med hjälp av mått. Ett sätt att styra sammanfattningen är att returnera BLANK när tidsperioder på lägre nivå efterfrågas. Ett annat sätt – som definierats med vissa avancerade DAX – är att fördela värden över tidsperioder på lägre nivå.

Överväg följande måttdefinition som använder funktionen ISFILTERED DAX. Det returnerar bara ett värde när kolumnerna Date och Month inte filtreras.

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

Följande visuella matris använder måttet Target Quantity. Den visar att alla månatliga målkvantiteter är BLANK.

Relatera högre kornighet (ej datum)

En annan designmetod krävs när du relaterar en icke-datumkolumn från en dimensionstabell till en faktatabell (och den har ett högre korn än dimensionstabellen).

De Category kolumnerna (från både tabellerna Product och Target) innehåller dubblettvärden. Det finns alltså ingen "enda" sida för en en-till-många-relation. I det här fallet måste du skapa en många-till-många-relation. Relationen bör sprida filter i en enda riktning, från dimensionstabellen till faktatabellen.

Nu ska vi ta en titt på tabellraderna.

I tabellen Target finns det fyra rader: två rader för varje målår (2019 och 2020) och två kategorier (Kläder och tillbehör). I tabellen Product finns det tre produkter. Två tillhör klädkategorin och en tillhör kategorin tillbehör. En av klädfärgerna är grön och de återstående två är blå.

En gruppering av tabellen efter kolumnen Category från tabellen Product ger följande resultat. Den här visualiseringen ger dock rätt resultat. Nu ska vi fundera på vad som händer när kolumnen Color från tabellen Product används för att gruppera målkvantitet.

Visualiseringen ger en felaktig bild av data. Vad händer här?

Ett filter på kolumnen Color från tabellen Product resulterar i två rader. En av raderna är för kategorin Kläder och den andra för kategorin Tillbehör. Dessa två kategorivärden sprids som filter till tabellen Target. Med andra ord, eftersom färgen blå används av produkter från två kategorier, används dessa kategorier för att filtrera målen.

För att undvika det här beteendet rekommenderar vi att du kontrollerar sammanfattningen av dina faktadata med hjälp av mått.

Överväg följande måttdefinition. Observera att alla Product tabellkolumner som ligger under kategorinivån testas för filter.

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

Följande visuella tabellobjekt använder måttet Target Quantity. Det visar att alla färgmålkvantiteter är TOMMA.

Den slutliga modelldesignen ser ut så här.

Relatera vägledning om fakta med högre kornighet

När du behöver relatera en dimensionstabell till en faktatabell och faktatabellen lagrar rader med högre kornighet än dimensionstabellraderna följer du den här vägledningen:

• För faktadatum med högre kornighet
  • I faktatabellen lagrar du det första datumet i tidsperioden.
  • Skapa en en-till-många-relation mellan datumtabellen och faktatabellen.
• För andra fakta med högre kornighet
  • Skapa en många-till-många-relation mellan dimensionstabellen och faktatabellen.
• För båda typerna
  • Kontrollsammanfattning med måttlogik – returnera BLANK när dimensionskolumner på lägre nivå används för att filtrera eller gruppera.
  • Dölj sammanfattningsbara faktatabellkolumner – som säkerställer att endast mått kan användas för att sammanfatta faktatabellen.

Relaterat innehåll

Mer information om den här artikeln finns i följande resurser:

• modellrelationer i Power BI Desktop
• Förstå stjärnschema och vikten för Power BI
• felsökningsvägledning för relation
• Frågor? Försök fråga Fabric-communityn
• Förslag? Bidra med idéer för att förbättra Fabric