Delen via

Richtlijnen voor veel-op-veel-relaties

  • Artikel

Dit artikel is bedoeld voor u als datamodelleur die werkt met Power BI Desktop. Hierin worden drie verschillende veel-op-veel-modelleringsscenario's beschreven. Het biedt u ook richtlijnen over hoe u succesvol voor hen kunt ontwerpen in uw modellen.

Notitie

In dit artikel wordt geen inleiding tot modelrelaties behandeld. Als u niet volledig bekend bent met relaties, eigenschappen of hoe u deze configureert, raden we u aan eerst de modelrelaties te lezen in Power BI Desktop artikel.

Het is ook belangrijk dat u inzicht heeft in het ontwerp van sterrenschema's. Voor meer informatie, zie Begrijp het sterschema en het belang ervan voor Power BI.

Er zijn drie verschillende veel-op-veel-scenario's. Deze kunnen optreden wanneer u het volgende moet doen:

Veel-op-veel-dimensies relateren

Het klassieke veel-op-veel-scenario heeft betrekking op twee entiteiten, bijvoorbeeld bankklanten en bankrekeningen. Houd er rekening mee dat klanten meerdere accounts kunnen hebben en dat accounts meerdere klanten kunnen hebben. Wanneer een account meerdere klanten heeft, worden ze meestal gemeenschappelijke rekeninghoudersgenoemd.

Het modelleren van deze entiteiten is eenvoudig. Een dimensietabel accounts opslaat en een andere dimensietabel slaat klanten op. Zoals het kenmerk is van dimensietabellen, is er een unieke id-kolom (ID) in elke tabel. Als u de relatie tussen de twee tabellen wilt modelleren, is een derde tabel vereist. Deze tabel wordt meestal een overbruggingstabel genoemd. In dit voorbeeld is het doel om één rij op te slaan voor elke klantaccountkoppeling. Interessant: als deze tabel alleen id-kolommen bevat, wordt deze een feitloze feitentabelgenoemd.

Hier volgt een simplistisch diagram van de drie modeltabellen.

diagram met drie modeltabellen. Het ontwerp wordt beschreven in de volgende alinea.

De eerste tabel heet Accounten bevat twee kolommen: AccountID en Account. De tweede tabel heet AccountCustomeren bevat twee kolommen: AccountID en CustomerID. De derde tabel heeft de naam Customeren bevat twee kolommen: CustomerID en Customer. Er bestaan geen relaties tussen een van de tabellen.

Er worden twee een-op-veel-relaties toegevoegd om de tabellen te relateren. Hier volgt een bijgewerkt modeldiagram van de gerelateerde tabellen. Er is een feitentabel met de naam Transaction toegevoegd. Het registreert rekeningtransacties. De overbruggingstabel en alle identifier-kolommen zijn verborgen.

diagram dat laat zien dat een modeldiagram bestaat uit vier tabellen. Er zijn een-op-veel-relaties toegevoegd om alle tabellen te relateren.

Om te beschrijven hoe de doorgifte van relatiefilters werkt, is het modeldiagram gewijzigd om de tabelrijen weer te geven.

diagram met de modeltabellen en de bijbehorende rijen. De rijdetails voor de vier tabellen worden beschreven in de volgende alinea.

De rijdetails voor de vier tabellen worden weergegeven in de volgende lijst met opsommingstekens:

  • De tabel Account heeft twee rijen:
    • AccountID 1 is voor Account-01
    • AccountID 2 is voor Account-02
  • De tabel Customer heeft twee rijen:
    • CustomerID 91 is voor Klant-91
    • CustomerID 92 is bedoeld voor Customer-92
  • De tabel AccountCustomer heeft drie rijen:
    • AccountID 1 is gekoppeld aan CustomerID91
    • AccountID 1 is gekoppeld aan CustomerID92
    • AccountID 2 is gekoppeld aan CustomerID92
  • De tabel Transaction heeft drie rijen:
    • Date 1 januari 2019, AccountID1, Amount100
    • Date 2 februari 2019, AccountID2, Amount200
    • Date 3 maart 2019, AccountID1, Amount-25

Laten we eens kijken wat er gebeurt wanneer het model wordt opgevraagd.

In de volgende afbeelding zijn er twee tabelvisuals die de kolom Amount van de Transaction tabel samenvatten. De eerste visual groepeert per account, en dus de som van de Amount kolommen vertegenwoordigt het rekeningsaldo. De tweede visual groepeert op klantniveau. Daarom weerspiegelt de som van de Amount kolommen het klantsaldo.

diagram met twee tabelvisuals naast elkaar. De visuals worden beschreven in de volgende alinea.

De eerste tabelvisual (Rekeningsaldo) heeft twee kolommen: Account en Amount. Het volgende resultaat wordt weergegeven:

  • rekening-01 saldobedrag is 75.
  • rekening-02 saldo is 200.
  • Het totaal is 275.

De tweede tabelweergave (Customer Balance) heeft twee kolommen: Customer en Amount. Het volgende resultaat wordt weergegeven:

  • klant-91 saldobedrag is 275.
  • klant-92 saldobedrag is 275.
  • Het totaal is 275.

In een kort overzicht van de tabelrijen en de visual Account Balance ziet u dat het resultaat juist is, voor elke rekening en het totale bedrag. Dat komt doordat elke accountgroepering resulteert in een filteroverdracht naar de Transaction-tabel voor dat account.

Er lijkt echter iets niet te kloppen met de visual Customer Balance. Elke klant in deze visual heeft hetzelfde saldo als het totale saldo. Dit resultaat kon alleen correct zijn als elke klant een gezamenlijke rekeninghouder van elke rekening was. Dat is niet het geval in dit voorbeeld. Er is een probleem en het is gerelateerd aan filterpropagatie. Filters stromen niet helemaal naar de Transaction tabel.

Als u de filterrichtingen van de relatie vanuit de Customer tabel naar de Transaction tabel volgt, kunt u bepalen dat de relatie tussen de Account en AccountCustomer tabellen in de verkeerde richting wordt doorgegeven. De filterrichting voor deze relatie moet worden ingesteld op Both.

diagram waarin wordt aangegeven dat het model is bijgewerkt. Het filtert nu in beide richtingen.

diagram met dezelfde twee rapportvisuals naast elkaar. De eerste visual is niet gewijzigd, terwijl de tweede visual wel is.

Zoals verwacht, is er geen wijziging aangebracht in de visual Account Balance.

In de visual Customer Balance wordt nu echter het volgende resultaat weergegeven:

  • bedrag van klant-91 is 75.
  • klant-92 saldobedrag is 275.
  • Het totaal is 275.

In de visual Customer Balance wordt nu een correct resultaat weergegeven. Volg zelf de filterinstructies en bekijk hoe de klantsaldi zijn berekend. Begrijp ook dat het visuele totaal van betrekking heeft op alle klanten.

Iemand die niet bekend is met de modelrelaties kan concluderen dat het resultaat onjuist is. Ze kunnen vragen: Waarom is het totale saldo voor Customer-91 en Customer-92 niet gelijk aan 350 (75 + 275)?

Het antwoord op hun vraag ligt in het begrijpen van de veel-op-veel-relatie. Elk klantsaldo kan de toevoeging van meerdere rekeningsaldi vertegenwoordigen, en daardoor zijn de klantsaldi niet te optellen.

Richtlijnen voor veel-op-veel-dimensies koppelen

Als u een veel-op-veel-relatie tussen dimensietabellen hebt, volgt u deze richtlijnen:

  • Voeg elke veel-op-veel-gerelateerde entiteit toe als een modeltabel, zodat deze een id-kolom heeft.
  • Voeg een overbruggingstabel toe om gekoppelde entiteiten op te slaan.
  • Maak een-op-veel-relaties tussen de drie tabellen.
  • Stel één bidirectionele relatie in om filteroverdracht voort te zetten naar de feiten-tabel.
  • Als het niet geschikt is om ontbrekende id-waarden te hebben, schakelt u de eigenschap Is Nullable uit. Het vernieuwen van gegevens mislukt wanneer ontbrekende waarden worden opgehaald.
  • Verberg de overbruggingstabel (tenzij deze andere kolommen of metingen bevat die vereist zijn voor rapportage).
  • Verberg alle id-kolommen die niet geschikt zijn voor rapportage (bijvoorbeeld wanneer in de kolommen surrogaatsleutelwaarden worden opgeslagen).
  • Als het zinvol is om een ID-kolom zichtbaar te laten, moet u ervoor zorgen dat deze op de "een"-kant van de relatie staat—verberg altijd de kolom aan de "veel"-kant. Dat komt doordat filters die zijn toegepast op de eerste dia resulteren in betere filterprestaties.
  • Om verwarring of onjuiste interpretatie te voorkomen, communiceert u uitleg aan uw rapportgebruikers. U kunt beschrijvingen toevoegen met tekstvakken of knopinfo voor visuele kopteksten.

Het is niet raadzaam om veel-op-veel dimensietabellen rechtstreeks te koppelen. Deze ontwerpbenadering vereist het instellen van een relatie met een veel-op-veel-kardinaliteit. Conceptueel gezien kan dit worden bereikt, maar het impliceert dat de gerelateerde kolommen dubbele waarden kunnen bevatten. Het is een goed geaccepteerde ontwerppraktijk dat dimensietabellen een id-kolom hebben. Dimensietabellen moeten altijd de id-kolom gebruiken als de 'een'-zijde van een relatie.

Veel-op-veel-feiten relateren

Een ander veel-op-veel-scenariotype omvat het koppelen van twee feitentabellen. Twee feitentabellen kunnen rechtstreeks worden gerelateerd. Deze ontwerptechniek kan handig zijn voor snelle en eenvoudige gegevensverkenning. Maar om duidelijk te zijn, raden we deze ontwerpbenadering over het algemeen niet aan. Verderop in deze sectie wordt uitgelegd waarom.

Laten we eens kijken naar een voorbeeld met twee feitentabellen: Order en Fulfillment. De Order tabel bevat één rij per orderregel en de Fulfillment tabel kan nul of meer rijen per orderregel bevatten. Rijen in de tabel Order vertegenwoordigen verkooporders. Rijen in de tabel Fulfillment vertegenwoordigen orderitems die zijn verzonden. Een veel-op-veel-relatie betreft de OrderID kolommen in elke tabel, waarbij de filters alleen worden doorgegeven vanuit de Order tabel (wat betekent dat de Order tabel de Fulfillment tabel filtert).

diagram met een model met twee tabellen: Order en Fulfillment.

De relatiekardinaliteit is ingesteld op Many-to-many ter ondersteuning van het opslaan van dubbele OrderID kolomwaarden in beide tabellen. In de Order tabel kunnen dubbele id-waarden bestaan omdat een order meerdere regels kan bevatten. In de Fulfillment tabel kunnen dubbele id-waarden bestaan omdat orders meerdere regels kunnen hebben en orderregels kunnen worden uitgevoerd door veel zendingen.

Laten we nu eens kijken naar de rijen van de tabel. In de tabel Fulfillment ziet u dat orderregels kunnen worden vervuld door meerdere zendingen. (Het ontbreken van een orderregel betekent dat de bestelling nog moet worden uitgevoerd.)

diagram met de tabelrijen van het model. De rijdetails voor de twee tabellen worden beschreven in de volgende alinea.

De details van de rijen voor de twee tabellen worden beschreven in de volgende opsomming:

  • De tabel Order heeft vijf rijen:
    • 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 maart 2019, OrderID3, OrderLine1, ProductIDProd-C, OrderQuantity5, Sales100
  • De tabel Fulfillment heeft vier rijen:
    • 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

Laten we eens kijken wat er gebeurt wanneer het model wordt opgevraagd. Hier is een tabelvisualisatie die de hoeveelheden van bestellingen en uitvoering vergelijkt met behulp van de Order tabel OrderID kolom.

Diagram met een tabelvisual met drie kolommen: OrderID, OrderQuantity en FulfillmentQuantity.

De visual geeft een nauwkeurig resultaat weer. Het nut van het model is echter beperkt omdat u alleen kunt filteren of groeperen op de Order tabel OrderID kolom.

Richtlijnen voor veel-op-veel-feiten relateren

Over het algemeen raden we u niet aan om twee feitentabellen rechtstreeks met elkaar te verbinden door gebruik te maken van veel-op-veel-kardinaliteit. De belangrijkste reden hiervoor is dat het model geen flexibiliteit biedt op de manieren waarop uw rapportvisuals filteren of groeperen. In het voorbeeld is het alleen mogelijk om visuals te filteren of te groeperen met kolom OrderID van tabel Order. Een andere reden heeft betrekking op de kwaliteit van uw gegevens. Als uw gegevens integriteitsproblemen hebben, kunnen sommige rijen worden weggelaten tijdens het uitvoeren van queries vanwege de aard van de veel-op-veel kardinaliteit en beperkte relaties.

In plaats van feitentabellen rechtstreeks te koppelen, raden we u aan een stervormig schema te implementeren ontwerp. Dat betekent dat u dimensietabellen toevoegt. Deze dimensietabellen hebben vervolgens betrekking op de feitentabellen met behulp van een-op-veel-relaties. Deze ontwerpbenadering is robuust omdat deze efficiënt flexibele rapportageopties biedt. Hiermee kunt u filteren of groeperen met behulp van een van de dimensietabelkolommen en kolommen van een gerelateerde feitentabel samenvatten.

Laten we eens kijken naar een betere oplossing.

Diagram dat een model toont bestaande uit zes tabellen: OrderLine, OrderDate, Order, Fulfillment, Product en FulfillmentDate.

Let op de volgende ontwerpwijzigingen:

  • Het model heeft nu vier extra tabellen: OrderLine, OrderDate, Producten FulfillmentDate.
  • De vier extra tabellen zijn alle dimensietabellen waarbij een-op-veel-relaties deze relateren aan de feitentabellen.
  • De OrderLine tabel bevat de kolom OrderLineID, waarin de OrderID waarde wordt opgeslagen, vermenigvuldigd met 100, plus de OrderLine kolomwaarde, een id voor elke orderregel.
  • De tabellen Order en Fulfillment bevatten nu elk een OrderLineID kolom en bevatten niet langer de kolommen OrderID en OrderLine.
  • De tabel Fulfillment bevat nu OrderDate en ProductID kolommen.
  • De tabel FulfillmentDate heeft alleen een relatie met de Fulfillment tabel.
  • Alle id-kolommen zijn verborgen.

Het nemen van de tijd om een stervormig schemaontwerp te gebruiken biedt de volgende voordelen:

  • Uw rapportvisuals kunnen filteren of groeperen op een zichtbare kolom uit de dimensietabellen.
  • Uw rapportvisuals kunnen elke zichtbare kolom uit de feitentabellen samenvatten.
  • Filters die zijn toegepast op de OrderLine, OrderDateof Product tabellen worden doorgegeven aan beide feitentabellen.
  • Alle relaties zijn een-op-veel en elke relatie is een reguliere relatie. Problemen met gegevensintegriteit worden niet gemaskeerd. Zie Modelrelaties in Power BI Desktopvoor meer informatie over de evaluatie van relaties.

Feiten over graan van hogere kwaliteit relateren

Dit veel-op-veel-scenario verschilt van de andere twee die al in dit artikel zijn beschreven.

Laten we eens kijken naar een voorbeeld met vier tabellen: Date, Sales, Producten Target. De Date- en Product tabellen zijn dimensietabellen en een-op-veel-relaties hebben betrekking op de Sales feitentabel. Tot nu toe vertegenwoordigt het een goed stervormig schemaontwerp. De Target tabel is echter nog niet gerelateerd aan de andere tabellen.

diagram met een model met vier tabellen: Datum, Verkoop, Product en Doel.

De tabel Target bevat drie kolommen: Category, TargetQuantityen TargetYear. In de tabelrijen wordt een granulariteit van jaar en productcategorie weergegeven. Met andere woorden, doelen, die worden gebruikt om de verkoopprestaties te meten, worden elk jaar voor elke productcategorie ingesteld.

diagram met de feitentabellen Verkoop en Doel. De feitentabel Target heeft drie kolommen: TargetYear, Category en TargetQuantity.

Omdat de Target tabel gegevens op een hoger niveau opslaat dan de dimensietabellen, kan er geen een-op-veel-relatie worden gemaakt. Nou, het is waar voor slechts één van de relaties. Laten we eens kijken hoe de Target tabel kan worden gerelateerd aan de dimensietabellen.

Hogere tijdsperioden relateren

Een relatie tussen de Date en Target tabellen moet een een-op-veel-relatie zijn. Dat komt doordat de TargetYear kolomwaarden datums zijn. In dit voorbeeld wordt in elke kolom TargetYear de eerste datum van het doeljaar opgeslagen.

Tip

Als u feiten met een hogere tijdgranulariteit dan dag opslaat, stelt u het gegevenstype van de kolom in op Datum (of Geheel getal als u datumsleutels gebruikt). Sla in de kolom een waarde op die de eerste dag van de periode aangeeft. Een jaarperiode wordt bijvoorbeeld geregistreerd als 1 januari van het jaar en een maandperiode wordt geregistreerd als de eerste dag van die maand.

Zorg er echter voor dat filters op maand- of datumniveau een zinvol resultaat opleveren. Zonder speciale berekeningslogica kunnen rapportvisuals rapporteren dat doeldatums letterlijk de eerste dag van elk jaar zijn. Alle andere dagen, en alle maanden behalve januari, geven een overzicht van de doelhoeveelheid als BLANK.

In de volgende matrix-visual ziet u wat er gebeurt wanneer de rapportgebruiker inzoomt van een jaar naar de maanden. De visual bevat een overzicht van de kolom TargetQuantity. (De optie Items zonder gegevens weergeven is ingeschakeld voor de matrixrijen.)

Een diagram toont een matrixvisual waarin de doelhoeveelheid voor het jaar 2020 aangegeven is als 270. Het produceert incorrecte waarden per datum.

Om dit gedrag te voorkomen, raden we u aan de samenvatting van uw feitengegevens te beheren met behulp van metingen. Een manier om de samenvatting te beheren, is door BLANK te retourneren wanneer er query's worden uitgevoerd op perioden op een lager niveau. Een andere manier, gedefinieerd met een aantal geavanceerde DAX, is het toewijzen van waarden voor perioden op een lager niveau.

Houd rekening met de volgende metingsdefinitie die gebruikmaakt van de ISFILTERED DAX-functie. Er wordt alleen een waarde geretourneerd wanneer de Date en Month kolommen niet worden gefilterd.

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

In de volgende matrixvisualisatie wordt de meting Target Quantity gebruikt. Hier ziet u dat alle maandelijkse doelhoeveelheden BLANK zijn.

Diagram met twee matrixvisualisaties. De eerste geeft het doel van de eerste maand van 2020 weer als 270, terwijl de tweede blanco is.

Hogere korrel (niet-datum) relateren

Er is een andere ontwerpbenadering vereist bij het koppelen van een kolom zonder datum van een dimensietabel aan een feitentabel (en deze heeft een grotere detaillering dan de dimensietabel).

De Category kolommen (uit zowel de Product- als Target tabellen) bevatten dubbele waarden. Er is dus geen ene kant voor een een-op-veel-relatie. In dit geval moet u een veel-op-veel-relatie maken. De relatie moet filters in één richting doorgeven, van de dimensietabel naar de feitentabel.

diagram met een model van de tabellen Doel en Product. Een veel-op-veel-relatie heeft betrekking op de twee tabellen.

Laten we nu eens kijken naar de tabelrijen.

diagram met een model met twee tabellen: Doel en Product. Een veel-op-veel-relatie heeft betrekking op de twee categoriekolommen.

In de tabel Target zijn er vier rijen: twee rijen voor elk doeljaar (2019 en 2020) en twee categorieën (Kleding en Accessoires). In de tabel Product zijn er drie producten. Twee behoren tot de kledingcategorie en één behoort tot de categorie accessoires. Een van de kledingkleuren is groen en de resterende twee zijn blauw.

Een tabelvisualisatie die de Category-kolom groepeert uit de Product-tabel produceert het volgende resultaat. Deze visual produceert echter het juiste resultaat. Laten we nu eens kijken wat er gebeurt wanneer de kolom Color uit de Product tabel wordt gebruikt om de doelhoeveelheid te groeperen.

diagram met twee tabelvisuals. De eerste groepen op categorie en de tweede groeperen op kleur. De tweede visual produceert een onjuist resultaat.

De visual produceert een onjuiste voorstelling van de gegevens. Wat gebeurt er hier?

Een filter op de kolom Color uit de Product tabel resulteert in twee rijen. Een van de rijen is voor de categorie Kleding en de andere is voor de categorie Accessoires. Deze twee categoriewaarden worden doorgegeven als filters aan de Target tabel. Met andere woorden, omdat de kleurblauw wordt gebruikt door producten uit twee categorieën, deze categorieën worden gebruikt om de doelen te filteren.

Om dit gedrag te voorkomen, zoals eerder beschreven, raden we u aan de samenvatting van uw feitengegevens te beheren met behulp van metingen.

Houd rekening met de volgende metingsdefinitie. U ziet dat alle Product tabelkolommen onder het categorieniveau worden getest op filters.

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

In de volgende tabelvisual wordt de maatstaf Target Quantity gebruikt. U ziet dat alle kleurdoelhoeveelheden BLANK zijn.

diagram met twee tabelvisuals. De eerste groepeert op categorie en de tweede groepeert op kleur. De tweede visual geeft correct een leeg resultaat.

Het uiteindelijke modelontwerp ziet er als volgt uit.

diagram met een model met datum- en doeltabellen die zijn gerelateerd aan een een-op-veel-relatie.

Richtlijnen voor het relateren van meer gedetailleerde feiten

Als u een dimensietabel wilt koppelen aan een feitentabel en de feitentabel rijen opslaat met een hogere korrel dan de dimensietabelrijen, volgt u deze richtlijnen:

  • Voor datums van hogere feiten
    • Sla in de feitentabel de eerste datum van de periode op.
    • Maak een een-op-veel-relatie tussen de datumtabel en de feitentabel.
  • Voor andere feiten met een hogere graan
    • Maak een veel-op-veel-relatie tussen de dimensietabel en de feitentabel.
  • Voor beide typen
    • Beheer samenvatting met meetlogica—retourneer BLANK wanneer lagere-dimensiekolommen worden gebruikt om te filteren of te groeperen.
    • Verberg samenvattende feitentabelkolommen, zodat alleen metingen kunnen worden gebruikt om de feitentabellen samen te vatten.

Verwante inhoud

Raadpleeg de volgende bronnen voor meer informatie over dit artikel: