Richtlijnen voor veel-op-veel-relaties

Dit artikel is bedoeld voor u als gegevensmodeller die werkt met Power BI Desktop. Hierin worden drie verschillende veel-op-veel-modelleringsscenario's beschreven. Het biedt u ook richtlijnen voor het ontwerpen ervan in uw modellen.

Notitie

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

Het is ook belangrijk dat u inzicht hebt in het ontwerp van stervormige schema's. Zie Meer informatie over stervormige schema's en het belang van Power BI.

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

• Twee dimensietabellen relateren
• Twee feitentabellen relateren
• Relateer tabellen met een hoger feitentype, wanneer de feitentabel rijen opslaat met een hogere korrel dan de tabelrijen van het dimensietype

Notitie

Power BI biedt nu systeemeigen ondersteuning voor veel-op-veel-relaties. Zie Veel-op-veel-relaties toepassen in Power BI Desktop voor meer informatie.

Veel-op-veel-dimensies relateren

Laten we eens kijken naar het eerste veel-op-veel-scenariotype met een voorbeeld. Het klassieke scenario heeft betrekking op twee entiteiten: 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 gezamenlijke rekeninghouders genoemd.

Het modelleren van deze entiteiten is eenvoudig. In de ene dimensietabel worden accounts opgeslagen en in een andere dimensietabel worden klanten opgeslagen. Zoals het kenmerk is van dimensietabellen, is er een id-kolom 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 is dat wanneer deze tabel alleen id-kolommen bevat, deze een feitloze feitentabel wordt genoemd.

Hier volgt een simplistisch modeldiagram van de drie tabellen.

De eerste tabel heet Account en bevat twee kolommen: AccountID en Account. De tweede tabel heet AccountCustomer en bevat twee kolommen: AccountID en CustomerID. De derde tabel heet Customer en 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 id-kolommen zijn verborgen.

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

Notitie

Het is niet mogelijk om tabelrijen weer te geven in het Power BI Desktop-modeldiagram. Het wordt in dit artikel gedaan om de discussie met duidelijke voorbeelden te ondersteunen.

De rijdetails voor de vier tabellen worden beschreven 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 Klant heeft twee rijen:
  • CustomerID 91 is voor Customer-91
  • CustomerID 92 is voor Customer-92
• De tabel AccountCustomer heeft drie rijen:
  • AccountID 1 is gekoppeld aan CustomerID 91
  • AccountID 1 is gekoppeld aan CustomerID 92
  • AccountID 2 is gekoppeld aan CustomerID 92
• De transactietabel heeft drie rijen:
  • Datum 1 januari 2019, AccountID 1, Bedrag 100
  • Datum 2 februari 2019, AccountID 2, Bedrag 200
  • Datum 3 maart 2019, AccountID 1, Bedrag -25

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

Hieronder ziet u twee visuals die de kolom Amount uit de tabel Transaction samenvatten. De eerste visual groepeert op account, en dus de som van de kolommen Amount vertegenwoordigt het saldo van het account. De tweede visual groepeert op klant en dus de som van de kolommen Amount vertegenwoordigt het saldo van de klant.

De eerste visual heeft de titel Accountsaldo en heeft twee kolommen: Account en Amount. Het volgende resultaat wordt weergegeven:

• Rekening-01 saldo is 75
• Rekening-02 saldo is 200
• Het totaal is 275

De tweede visual heet Klantsaldo en heeft twee kolommen: Klant en Bedrag. Het volgende resultaat wordt weergegeven:

• Klant-91 saldo is 275
• Het saldo van klant-92 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. Dit komt doordat elke accountgroepering resulteert in een filterdoorgifte naar de transactietabel voor dat account.

Er wordt echter niets correct weergegeven met de visual Customer Balance . Elke klant in de visual Customer Balance heeft hetzelfde saldo als het totale saldo. Dit resultaat kon alleen worden gecorrigeerd als elke klant een gemeenschappelijke rekeninghouder van elke rekening was. Dat is niet het geval in dit voorbeeld. Het probleem heeft betrekking op het doorgeven van filters. Het stroomt niet helemaal naar de transactietabel .

Volg de richtingen van het relatiefilter uit de tabel Klant naar de tabel Transactie . Het moet duidelijk zijn dat de relatie tussen de tabel Account en AccountCustomer in de verkeerde richting wordt doorgegeven. De filterrichting voor deze relatie moet worden ingesteld op Beide.

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

In de visuals customer balance wordt nu echter het volgende resultaat weergegeven:

• Het saldo van klant-91 is 75
• Het saldo van klant-92 is 275
• Het totaal is 275

In de visual Customer Balance wordt nu een correct resultaat weergegeven. Volg de filterrichtingen voor uzelf en bekijk hoe de klantsaldi zijn berekend. Begrijp ook dat het visuele totaal alle klanten betekent.

Iemand die niet bekend is met de modelrelaties kan concluderen dat het resultaat onjuist is. Ze kunnen vragen: Waarom is het totale saldo voor klant-91 en klant-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 accountsaldo's vertegenwoordigen, zodat de klantsaldi niet-additief zijn.

Richtlijnen voor veel-op-veel-dimensies koppelen

Wanneer u een veel-op-veel-relatie hebt tussen dimensietabellen, bieden we de volgende richtlijnen:

• Voeg elke veel-op-veel-gerelateerde entiteit toe als een modeltabel, zodat deze een unieke id-kolom (ID) heeft
• Een overbruggingstabel toevoegen om gekoppelde entiteiten op te slaan
• Een-op-veel-relaties tussen de drie tabellen maken
• Een bidirectionele relatie configureren zodat filterdoorgifte kan worden voortgezet naar de feitentabellen
• Als het niet geschikt is om ontbrekende id-waarden te hebben, stelt u de eigenschap Is Nullable van id-kolommen in op ONWAAR. Het vernieuwen van gegevens mislukt vervolgens als ontbrekende waarden worden gebrond
• De overbruggingstabel verbergen (tenzij deze extra kolommen of metingen bevat die vereist zijn voor rapportage)
• Id-kolommen verbergen die niet geschikt zijn voor rapportage (bijvoorbeeld wanneer id's surrogaatsleutels zijn)
• Als het zinvol is om een id-kolom zichtbaar te laten, moet u ervoor zorgen dat deze zich op de 'een'-dia van de relatie bevindt. Verberg altijd de kolom 'veel'. Dit resulteert in de beste 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

We raden u niet aan veel-op-veel dimensietabellen rechtstreeks te koppelen. Deze ontwerpbenadering vereist het configureren van een relatie met een veel-op-veel-kardinaliteit. Conceptueel gezien kan het worden bereikt, maar het impliceert dat de gerelateerde kolommen dubbele waarden bevatten. Het is echter een goed geaccepteerde ontwerppraktijk, maar 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

Het tweede 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 tabel Order bevat één rij per orderregel en de tabel Fulfillment 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 heeft betrekking op de twee order-id-kolommen , met alleen filterdoorgifte uit de tabel Order (OrderfiltersFulfillment).

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

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

De rijdetails voor de twee tabellen worden beschreven in de volgende lijst met opsommingstekens:

• De tabel Order heeft vijf rijen:
  • OrderDatum 1 januari 2019, OrderID 1, OrderLine 1, ProductID Prod-A, OrderQuantity 5, Verkoop 50
  • OrderDate 1 januari 2019, OrderID 1, OrderLine 2, ProductID Prod-B, OrderQuantity 10, Verkoop 80
  • OrderDatum 2 februari 2019, OrderID 2, OrderLine 1, ProductID Prod-B, OrderQuantity 5, Verkoop 40
  • OrderDatum 2 februari 2019, OrderID 2, OrderLine 2, ProductID Prod-C, OrderQuantity 1, Verkoop 20
  • OrderDatum 3 maart 2019, OrderID 3, OrderLine 1, ProductID Prod-C, OrderQuantity 5, Verkoop 100
• De tabel Fulfillment heeft vier rijen:
  • FulfillmentDate 1 januari 2019, FulfillmentID 50, OrderID 1, OrderLine 1, FulfillmentQuantity 2
  • FulfillmentDate 2 februari 2019, FulfillmentID 51, OrderID 2, OrderLine 1, FulfillmentQuantity 5
  • FulfillmentDate 2 februari 2019, FulfillmentID 52, OrderID 1, OrderLine 1, FulfillmentQuantity 3
  • FulfillmentDate 1 januari 2019, FulfillmentID 53, OrderID 1, OrderLine 2, FulfillmentQuantity 10

Laten we eens kijken wat er gebeurt wanneer het model wordt opgevraagd. Hier volgt een tabelvisual voor het vergelijken van order- en uitvoeringshoeveelheden door de kolom OrdertabelOrderID .

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

Richtlijnen voor veel-op-veel-feiten relateren

Over het algemeen raden we niet aan om twee feitentabellen rechtstreeks te koppelen met behulp van veel-op-veel-kardinaliteit. De belangrijkste reden hiervoor is dat het model geen flexibiliteit biedt op de manieren waarop u visuals filtert of groepeert. In het voorbeeld is het alleen mogelijk om visuals te filteren of te groeperen op de kolom OrdertabelOrderID . Een extra reden heeft betrekking op de kwaliteit van uw gegevens. Als uw gegevens integriteitsproblemen hebben, kunnen sommige rijen worden weggelaten tijdens het uitvoeren van query's vanwege de aard van de beperkte relatie. Zie Modelrelaties in Power BI Desktop (evaluatie van relaties) voor meer informatie.

In plaats van feitentabellen rechtstreeks te koppelen, raden we u aan stervormige ontwerpprincipes te gebruiken. U doet dit door dimensietabellen toe te voegen. De dimensietabellen hebben vervolgens betrekking op de feitentabellen met behulp van een-op-veel-relaties. Deze ontwerpbenadering is robuust omdat deze flexibele rapportageopties biedt. Hiermee kunt u filteren of groeperen met behulp van een van de dimensiekolommen en eventuele gerelateerde feitentabel samenvatten.

Laten we eens kijken naar een betere oplossing.

Let op de volgende ontwerpwijzigingen:

• Het model heeft nu vier extra tabellen: OrderLine, OrderDate, Product en FulfillmentDate
• De vier extra tabellen zijn allemaal dimensietabellen en een-op-veel-relaties relateren deze tabellen aan de feitentabellen
• De tabel OrderLine bevat een kolom OrderLineID , die de OrderID-waarde vertegenwoordigt, vermenigvuldigd met 100, plus de waarde Orderlijn , een unieke id voor elke orderregel
• De tabellen Order en Fulfillment bevatten nu een kolom OrderLineID en bevatten niet langer de kolommen OrderID en OrderLine
• De tabel Fulfillment bevat nu de kolommen OrderDate en ProductID
• De tabel FulfillmentDate heeft alleen betrekking op de tabel Fulfillment
• Alle kolommen met unieke id's zijn verborgen

Het toepassen van ontwerpprincipes voor stervormige schema's 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 tabellen OrderLine, OrderDate of Product 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 Desktop (evaluatie van relaties) voor meer informatie.

Hogere feiten 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, Product en Target. De datum - en producttabellen zijn dimensietabellen en een-op-veel-relaties hebben betrekking op de feitentabel Sales . Tot nu toe vertegenwoordigt het een goed stervormig schemaontwerp. De doeltabel is echter nog gerelateerd aan de andere tabellen.

De tabel Target bevat drie kolommen: Category, TargetQuantity en 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.

Omdat de doeltabel 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 doeltabel kan worden gerelateerd aan de dimensietabellen.

Hogere tijdsperioden relateren

Een relatie tussen de tabellen Datum en Doel moet een een-op-veel-relatie zijn. Dit komt doordat de kolomwaarden TargetYear datums zijn. In dit voorbeeld is elke kolomwaarde TargetYear de eerste datum van het doeljaar.

Tip

Bij het opslaan van feiten met een hogere tijdgranulariteit dan dag, stelt u het gegevenstype van de kolom in op Datum (of Wie le 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 matrixvisual ziet u wat er gebeurt wanneer de rapportgebruiker van een jaar in de maanden inzoomt. De visual bevat een samenvatting van de kolom TargetQuantity . (De Items zonder gegevensoptie weergeven is ingeschakeld voor de matrixrijen.)

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 DAX-functie ISFILTERED . Er wordt alleen een waarde geretourneerd wanneer de kolommen Datum of Maand niet worden gefilterd.

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

In de volgende matrixvisual wordt nu de meting Doelhoeveelheid gebruikt. Hier ziet u dat alle maandelijkse doelhoeveelheden BLANK zijn.

Hogere korrel (niet-datum) relateren

Er is een andere ontwerpbenadering vereist bij het koppelen van een niet-datumkolom van een dimensietabel aan een feitentabel (en deze is hoger dan de dimensietabel).

De categoriekolommen (uit de tabellen Product en Doel ) bevatten dubbele waarden. Er is dus geen 'één' 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 tot de feitentabel.

Laten we nu eens kijken naar de tabelrijen.

In de tabel Doel 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 tabelvisual die wordt gegroepeerd op de kolom Categorie uit de tabel Product produceert het volgende resultaat.

Deze visual produceert het juiste resultaat. Laten we nu eens kijken wat er gebeurt wanneer de kolom Kleur uit de tabel Product wordt gebruikt om de doelhoeveelheid te groeperen.

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

Een filter op de kolom Kleur uit de tabel Product 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 doeltabel. Met andere woorden, omdat de kleurblauw wordt gebruikt door producten uit twee categorieën, worden deze categorieën 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 kolommen in de tabel Product 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 nu de meting Doelhoeveelheid gebruikt. U ziet dat alle kleurdoelhoeveelheden BLANK zijn.

Het uiteindelijke modelontwerp ziet er als volgt uit.

Richtlijnen voor nauwkeurigere feiten relateren

Wanneer u een dimensietabel wilt koppelen aan een feitentabel en de feitentabel rijen opslaat met een hogere korrel dan de dimensietabelrijen, bieden we de volgende richtlijnen:

• Voor nauwkeurigere feitendatums:
  • Sla in de feitentabel de eerste datum van de periode op
  • Een een-op-veel-relatie tussen de datumtabel en de feitentabel maken
• Voor andere nauwkeurigere feiten:
  • Een veel-op-veel-relatie maken tussen de dimensietabel en de feitentabel
• Voor beide typen:
  • Samenvatting van besturingselementen beheren met metingslogica: retourneer BLANK wanneer dimensiekolommen op een lager niveau worden gebruikt om te filteren of groeperen
  • Samenvattende feitentabelkolommen verbergen, op deze manier kunnen alleen metingen worden gebruikt om de feitentabel samen te vatten

Gerelateerde inhoud

Raadpleeg de volgende bronnen voor meer informatie over dit artikel:

• Modelrelaties in Power BI Desktop
• Stervormige schema's en het belang van Power BI begrijpen
• Richtlijnen voor het oplossen van problemen met relaties
• Vragen? Vraag het Power BI-community
• Suggesties? Ideeën bijdragen om Power BI te verbeteren