Relaties tussen tabellen in een gegevensmodel

Uw browser biedt geen ondersteuning voor video. Installeer Microsoft Silverlight, Adobe Flash Player of Internet Explorer 9.
Video: relaties in Power View en PowerPivot

Maak uw gegevensanalyse krachtiger door relaties tussen gegevens in verschillende tabellen aan te brengen. Een relatie is een verbinding die u tussen twee gegevenstabellen maakt, gebaseerd op één kolom in beide tabellen. Waarom zijn relaties nuttig? Stelt u zich voor dat u gegevens traceert voor klantenorders in uw bedrijf. U zou alle gegevens in één tabel met de volgende structuur kunnen bijhouden:

KlantId

Naam

E-mail

Kortingspercentage

OrderId

Orderdatum

Product

Aantal

1

Ashton

chris.ashton@contoso.com

.05

256

2010-01-07

Compact Digital

11

1

Ashton

chris.ashton@contoso.com

.05

255

01-03-2010

SLR-Camera

15

2

Takema

willem.takema@contoso.com

.10

254

2010-01-03

Budget Movie-Maker

27

Deze benadering kan volstaan, maar er moeten veel redundante gegevens voor worden opgeslagen, zoals het e-mailadres van de klant voor elke order. De opslag is goedkoop, maar als het e-mailadres wordt gewijzigd, moet u ervoor zorgen dat u elke rij voor de betreffende klant bijwerkt. Een oplossing voor dit probleem is het splitsen van de gegevens in meerdere tabellen en het definiëren van relaties tussen die tabellen. Deze benadering wordt gebruikt in relationele databases zoals SQL Server. Zo kunnen in een database die u importeert de ordergegevens worden weergeven in drie gerelateerde tabellen:

Klanten

[CustomerID]

Naam

E-mail

1

Ashton

chris.ashton@contoso.com

2

Takema

wander.kuijken@contoso.com

Klantkortingen

[CustomerID]

Kortingspercentage

1

0,05

2

0,10

Orders

[CustomerID]

OrderId

Orderdatum

Product

Aantal

1

256

2010-01-07

Compact Digital

11

1

255

01-03-2010

SLR-Camera

15

2

254

2010-01-03

Budget Movie-Maker

27

Relaties bestaan in een gegevensmodel dat u zelf maakt of dat door Excel wordt gemaakt wanneer u meerdere tabellen tegelijk importeert. U kunt het model ook maken of beheren via de Power Pivot-invoegtoepassing. Zie Een gegevensmodel maken in Excel voor meer informatie.

Als u via de Power Pivot-invoegtoepassing tabellen uit dezelfde database importeert, kunnen in Power Pivot de relaties tussen de tabellen worden gedetecteerd op basis van de kolommen die tussen [vierkante haken] staan en kunnen deze relaties worden gereproduceerd in een gegevensmodel dat op de achtergrond wordt gemaakt. Zie Automatische detectie en afleiding van relaties in dit artikel. Als u tabellen uit verschillende bronnen importeert, kunt u handmatig relaties aanbrengen, zoals wordt beschreven in Een relatie tussen twee tabellen maken.

Naar boven

In dit artikel

Kolommen en sleutels

Typen relaties

Relaties en prestaties

Meerdere relaties tussen tabellen

Vereisten voor tabelrelaties

Niet ondersteund voor tabelrelaties

Samengestelde sleutels en opzoekkolommen

Veel-op-veelrelaties

Self-joins en lussen

Automatische detectie en implicatie van relaties in PowerPivot

Automatische detectie voor benoemde sets

Implicatie van relaties

Kolommen en sleutels

Relaties zijn gebaseerd op kolommen in tabellen die dezelfde gegevens bevatten. De tabellen Customers en Orders kunnen met elkaar verwant zijn omdat ze beide een kolom bevatten waarin een klant-id is opgeslagen. In het voorbeeld zijn de kolomnamen hetzelfde, maar dit is geen vereiste. De ene kolom kan CustomerID en de andere kolom kan CustomerNumber heten, zoals alle rijen in de tabel Orders een id bevatten die tevens is opgeslagen in de tabel Customers.

In een relationele database zijn er verschillende soorten sleutels, die normaliter slechts kolommen met speciale eigenschappen zijn. Als u weet waar elke sleutel voor dient, kunt u een gegevensmodel met meerdere tabellen beter beheren waarmee gegevens aan een draaitabel, draaigrafiek of Power View-rapport worden verstrekt.

De volgende sleutels zijn voor ons doel het meest interessant:

  • Primaire sleutel: deze identificeert op unieke wijze een rij in een tabel, zoals CustomerID in de tabel Customers.

  • Alternatieve sleutel (of kandidaatsleutel): een andere kolom dan de primaire sleutel die uniek is. In de tabel Employees zijn bijvoorbeeld mogelijk een medewerker-id en een burgerservicenummer opgeslagen, die beide uniek zijn.

  • Refererende sleutel: een kolom die verwijst naar een unieke kolom in een andere tabel, zoals CustomerID in de tabel Orders, die verwijst naar CustomerID in de tabel Customers.

In een gegevensmodel wordt de primaire of alternatieve sleutel de gerelateerde kolom genoemd. Als een tabel zowel een primaire als een alternatieve sleutel heeft, kunt u beide sleutels gebruiken als basis van een tabelrelatie. Naar de refererende sleutel wordt verwezen als de bronkolom of simpelweg de kolom. In ons voorbeeld wordt een relatie gedefinieerd tussen CustomerID in de tabel Orders (de kolom) en CustomerID in de tabel Customers (de opzoekkolom). Als u gegevens uit een relationele database importeert, kiest Excel standaard de refererende sleutel uit een tabel en de overeenkomstige primaire sleutel uit de andere tabel. U kunt voor de opzoekkolom echter elke kolom met unieke waarden gebruiken.

Typen relaties

De relatie tussen Customers en Orders is een een-op-veelrelatie. Elke klant kan meerdere orders hebben, maar een order kan niet meerdere klanten hebben. De andere typen relaties zijn één-op-één en veel-op-veel. De tabel CustomerDiscounts, waarin voor elke klant één kortingstarief is gedefinieerd, heeft een een-op-eenrelatie met de tabel Customers.

In de onderstaande tabel worden de relaties tussen de drie tabellen getoond:

Relatie

Type

Opzoekkolom

Kolom

Klanten-Klantkortingen

een-op-een

Klanten.KlantID

KlantKortingen.KlantID

Klanten-Orders

een-op-veel

Klanten. KlantID

Orders.KlantID

Opmerking :  Veel-op-eenrelaties worden niet ondersteund in gegevensmodellen. Een voorbeeld van een veel-op-veelrelatie is een rechtstreekse relatie tussen Products en Customers, waarin een klant vele producten kan kopen en hetzelfde product door vele klanten kan worden gekocht.

Relaties en prestaties

Nadat er een relatie is gemaakt, moet de Excel-database normaliter de formules herberekenen die kolommen uit tabellen in de nieuwe relatie gebruiken. De verwerking kan enige tijd duren, afhankelijk van de hoeveelheid gegevens en de complexiteit van de relaties. Zie Formules herberekenen voor meer informatie.

Meerdere relaties tussen tabellen

Gegevensmodellen kunnen meerdere relaties tussen twee tabellen bevatten. Voor het maken van nauwkeurige berekeningen heeft Excel één pad van de ene tabel naar de volgende tabel nodig. Daarom kan een tabellenpaar slechts één actieve relatie tegelijk hebben. De anderen zijn inactief, maar u kunt inactieve relaties in formules en query's opgeven. In de diagramweergave is de actieve relatie een vaste lijn en worden de inactieve relaties weergegeven als onderbroken lijnen. In AdventureWorksDW2012 bevat de tabel DimDate bijvoorbeeld een kolom DateKey, die gerelateerd is aan drie verschillende kolommen in de tabel FactInternetverkopen: OrderDatum, VervalDatum en VerzendDatum. Als de relatie tussen DateKey en OrderDatum actief is, is deze relatie de standaardrelatie in formules, tenzij u iets anders opgeeft.

Naar boven

Vereisten voor tabelrelaties

Een relatie kan worden gemaakt wanneer aan de volgende vereisten wordt voldaan:

Criteria

Beschrijving

Unieke id voor elke tabel

Elke tabel moet één kolom hebben die elke rij in die tabel op unieke wijze identificeert. Naar deze kolom wordt vaak verwezen als de primaire sleutel.

Unieke opzoekkolommen

De gegevenswaarden in opzoekkolom moeten uniek zijn. Met andere woorden, de kolom kan geen dubbele waarden bevatten. In een gegevensmodel zijn null-waarden en lege tekenreeksen gelijk aan een lege cel, die een unieke gegevenswaarde vertegenwoordigt. Dit betekent dat de opzoekkolom niet meerdere null-waarden mag bevatten.

Compatibele gegevenstypen

De gegevenstypen in de bronkolom en de opzoekkolom moeten compatibel zijn. Zie Gegevenstypen die ingegevensmodellen worden ondersteund voor meer informatie over gegevenstypen.

Niet ondersteund voor tabelrelaties

In een gegevensmodel kan een tabelrelatie niet worden gemaakt als de sleutel een samengestelde sleutel is. Bovendien kunt u ook alleen een-op-een- en een-op-veelrelaties maken. Andere typen relaties worden niet ondersteund.

Samengestelde sleutels en opzoekkolommen

Een samengestelde sleutel is een sleutel die uit meer dan één kolom bestaat. Gegevensmodellen kunnen geen samengestelde sleutels bevatten, elke tabel moet altijd exact één kolom hebben waarmee elke rij in de tabel wordt geïdentificeerd. Als u een tabel met een bestaande relatie op basis van een samengestelde sleutel importeert, negeert de wizard Tabel importeren in Power Pivot de relatie, omdat deze niet in het model kan worden gemaakt.

Als u handmatig een relatie wilt maken tussen twee tabellen met meerdere kolommen waardoor de primaire en refererende sleutels worden gedefinieerd, moet u eerst de waarden combineren om één sleutelkolom te maken, voordat u de relatie maakt. U kunt dit doen voordat u de gegevens importeert of door met de invoegtoepassing Power Pivot een berekende kolom in het gegevensmodel te maken.

Veel-op-veelrelaties

Een gegevensmodel kan geen veel-op-veelrelaties hebben. U kunt niet simpelweg verbindingstabellen aan het model toevoegen. U kunt echter wel DAX-functies gebruiken om veel-op-veelrelaties te modelleren.

Self-joins en lussen

Self-joins zijn niet toegestaan in een gegevensmodel. Een self-join is a recursieve relatie tussen een tabel en zichzelf. Self-joins worden vaak gebruikt om bovenliggende-onderliggende hiërarchieën te definiëren. U zou bijvoorbeeld een tabel Employees aan zichzelf kunnen koppelen om een hiërarchie te creëren waarin de managementketen van een bedrijf wordt weergegeven.

In Excel zijn lussen tussen relaties in een werkmap niet toegestaan. Met andere woorden: de onderstaande relaties zijn verboden.

  • Tabel 1, kolom a   tot   tabel 2, kolom f

  • Tabel 2, kolom f   tot   tabel 3, kolom n

  • Tabel 3, kolom n   tot   tabel 1, kolom a

Als u een relatie probeert te maken die in een lus resulteert, wordt een fout gegenereerd.

Naar boven

Automatische detectie en afleiding van relaties in Power Pivot

Een van de voordelen van het importeren van gegevens via de Power Pivot-invoegtoepassing is dat via Power Pivot soms relaties kunnen worden gedetecteerd of gemaakt in het gegevensmodel dat in Excel wordt gemaakt.

Wanneer u meerdere tabellen importeert, worden door Power Pivot automatisch bestaande relaties tussen de tabellen gedetecteerd. Wanneer u een draaitabel maakt, worden de gegevens bovendien in de tabellen door Power Pivot geanalyseerd. Het programma detecteert mogelijke relaties die niet zijn gedefinieerd en suggereert de juiste kolommen voor deze relaties.

De detectiealgoritme gebruikt statistische gegevens over de waarden en metagegevens van kolommen om de kans op relaties af te leiden.

  • De gegevenstypen in alle gerelateerde kolommen moeten compatibel zijn. Voor automatische detectie worden alleen gegevenstypen voor gehele getallen of tekst ondersteund. Zie Gegevenstypen die in gegevensmodellen worden ondersteund voor meer informatie over PowerPivot-gegevenstypen.

  • De relatie kan alleen worden gedetecteerd als het aantal unieke sleutels in de opzoekkolom groter is dan het aantal waarden in de tabel aan de veel-zijde. Met andere woorden: de sleutelkolom aan de veel-zijde van de relatie mag geen waarden bevatten die niet in de sleutelkolom van de opzoektabel voorkomen. Bijvoorbeeld: u hebt een tabel met product-id's (de opzoektabel) en een tabel met de verkopen voor elk product (de veel-zijde van de relatie). Als de verkooprecords een product-id bevatten die niet in de tabel Products voorkomt, kan de relatie niet automatisch worden gemaakt. Mogelijk kunt u de relatie wel handmatig maken. Om ervoor te zorgen dat Excel de relatie detecteert, moet u de opzoektabel Products eerst bijwerken met de id's van de ontbrekende producten.

  • Controleer of de naam van de sleutelkolom aan de veel-zijde vergelijkbaar is met de naam van de sleutelkolom in de opzoektabel. De namen hoeven niet identiek te zijn. In een bedrijfsomgeving ziet u vaak variaties op de namen van kolommen die hoofdzakelijk dezelfde gegevens bevatten: Medewerkernummer, medewerker-nr., medewerker_nummer, enzovoort. De algoritme detecteert namen die op elkaar lijken en wijst een hogere kans toe aan kolommen met soortgelijke of exact overeenkomende namen. Als u de waarschijnlijkheid van een automatische relatie wilt verhogen, kunt u daarom proberen in de gegevens die u importeert de namen van de kolommen te wijzigen in namen die lijken op de kolommen in de bestaande tabellen. Als Excel meerdere geschikte relaties aantreft, wordt er geen relatie gemaakt.

Deze gegevens kunnen u helpen begrijpen waarom niet alle relaties worden gedetecteerd, of hoe wijzigingen in metagegevens (zoals veldnaam en gegevenstypen) de resultaten van automatische relatiedetectie kunnen verbeteren. Zie Problemen met relaties oplossen voor meer informatie.

Automatische detectie voor benoemde sets

Relaties tussen benoemde sets en gerelateerde velden in een draaitabel worden niet automatisch gedetecteerd. U kunt deze relaties handmatig maken. Als u automatische detectie van relaties wilt gebruiken, verwijdert u de benoemde sets en voegt u de aparte velden van de benoemde sets rechtstreeks toe aan de draaitabel.

Implicatie van relaties

In sommige gevallen worden relaties tussen tabellen automatisch geketend. Als u bijvoorbeeld een relatie tussen de eerste twee sets tabellen hieronder maakt, wordt een relatie afgeleid voor de andere twee tabellen en wordt automatisch een relatie gemaakt.

  • Products en Categorie -- handmatig gemaakt

  • Categorie en SubCategorie -- handmatig gemaakt

  • Producten en SubCategorie -- relatie is geïmpliceerd

De relaties moeten in één richting lopen, zoals hierboven wordt getoond, om relaties automatisch te ketenen. Als de aanvankelijke relaties bijvoorbeeld tussen Verkopen en Producten waren, en tussen Verkopen en Klanten, wordt geen relatie geïmpliceerd. Dit komt omdat de relatie tussen Producten en Klanten een veel-op-veelrelatie is.

Naar boven

Uw vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Office Insiders

Was deze informatie nuttig?

Bedankt voor uw feedback.

Hartelijk dank voor uw feedback! Het lijkt ons een goed idee om u in contact te brengen met een van onze Office-ondersteuningsagents.

×