Office: Beziehungen zwischen Tabellen in einem Datenmodell

Beziehungen zwischen Tabellen in einem Datenmodell
Excel für Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Mehr... Weniger
Fügen Sie Ihrer Datenanalyse mehr Energie hinzu, indem Sie Beziehungen amogn unterschiedliche Tabellen erstellen. Eine Beziehung ist eine Verbindung zwischen zwei Tabellen, die Daten enthalten: eine Spalte in jeder Tabelle ist die Grundlage für die Beziehung. Um zu verstehen, warum Beziehungen nützlich sind, stellen Sie sich vor, dass Sie in Ihrem Unternehmen die Daten für Kundenbestellungen verfolgen möchten. Sie können alle Daten in einer einzelnen Tabelle mit einer Struktur wie der folgenden überwachen:

CustomerID	Name	EMail	DiscountRate	OrderID	OrderDate	Product	Quantity
1	Ashton	chris.ashton@contoso.com	.05	256	2010-01-07	Compact Digital	11
1	Ashton	chris.ashton@contoso.com	.05	255	2010-01-03	SLR Camera	15
2	Jaworski	michal.jaworski@contoso.com	.10	254	2010-01-03	Budget Movie-Maker	27

Dieser Ansatz kann umgesetzt werden, bedeutet aber, dass viele redundante Daten gespeichert werden müssen, z. B. die Kunden-E-Mail-Adresse für jede Bestellung. Speicher ist zwar billig, aber wenn sich die E-Mail-Adresse eines Kunden ändert, müssen Sie sicherstellen, dass jede Zeile für diesen Kunden aktualisiert wird. Eine Lösung für dieses Problem ist, die Daten in mehrere Tabellen aufzuteilen und zwischen diesen Tabellen Beziehungen zu definieren. Dieser Ansatz wird in relationalen Datenbanken wie SQL Server verwendet. Eine Datenbank, die Sie importieren, könnte die Bestelldaten z. B. mithilfe von drei verknüpften Tabellen darstellen:

Customers

[CustomerID]	Name	Email
1	Ashton	chris.ashton@contoso.com
2	Jaworski	michal.jaworski@contoso.com

CustomerDiscounts

[CustomerID]	DiscountRate
1	.05
2	.10

Orders

[CustomerID]	OrderID	OrderDate	Product	Quantity
1	256	2010-01-07	Compact Digital	11
1	255	2010-01-03	SLR Camera	15
2	254	2010-01-03	Budget Movie-Maker	27

In einem Datenmodell sind Beziehungen vorhanden – eine, die Sie explizit erstellen, oder eine, die Excel automatisch in Ihrem Auftrag erstellt, wenn Sie mehrere Tabellen gleichzeitig importieren. Sie können auch das Power Pivot-Add-in verwenden, um das Modell zu erstellen oder zu verwalten. Ausführliche Informationen finden Sie unter Erstellen eines Datenmodells in Excel.

Wenn Sie das Power Pivot-Add-In zum Importieren von Tabellen aus derselben Datenbank verwenden, kann Power Pivot die Beziehungen zwischen den Tabellen anhand der Spalten ermitteln, die in eckigen Klammern stehen, und diese Beziehungen in einem Datenmodell reproduzieren, das im Hintergrund erstellt wird. Weitere Informationen finden Sie unter Automatische Erkennung und Inferenz von Beziehungen in diesem Artikel. Wenn Sie Tabellen aus mehreren Quellen importieren, können Sie Beziehungen manuell erstellen, wie es in Erstellen einer Beziehung zwischen zwei Tabellen beschrieben ist.

Spalten und Schlüssel
Beziehungen basieren auf den Spalten in jeder Tabelle, die die gleichen Daten enthalten. So können Sie beispielsweise eine Tabelle " Kunden " mit einer Tabelle " Bestellungen " verknüpfen, wenn jede eine Spalte enthält, in der eine Kunden-IDgespeichert ist. Im Beispiel sind die Spaltennamen identisch, dies ist jedoch keine Voraussetzung. Eine Spalte kann beispielsweise den Namen "CustomerID" und die andere "CustomerNumber" besitzen, sofern alle Zeilen in der Tabelle "Orders" eine ID enthalten, die auch in der Tabelle "Customers" gespeichert ist.

In einer relationalen Datenbank gibt es verschiedene Typen von Schlüsseln. Ein Schlüssel ist in der Regel Spalte mit speziellen Eigenschaften. Ein Datenmodell mit mehreren Tabellen, das Daten für eine PivotTable, ein PivotChart oder einen Power View-Bericht bereitstellt, ist einfacher zu verwalten, wenn Sie den Verwendungszweck der einzelnen Schlüssel kennen.

Obwohl es viele Arten von Schlüsseln gibt, sind diese hier die wichtigste für unsere Zwecke:

• Primärschlüssel: kennzeichnet eine Zeile in einer Tabelle, beispielsweise CustomerID , in der Tabelle " Customers ".
  
  
• Alternativ Taste (oder Kandidatenschlüssel): eine andere Spalte als der Primärschlüssel, der eindeutig ist. In einer Tabelle für Mitarbeiter kann beispielsweise eine Mitarbeiter-ID und eine Sozialunterstützungsnummer gespeichert werden, die beide eindeutig sind.
  
  
• Fremdschlüssel: eine Spalte, die auf eine eindeutige Spalte in einer anderen Tabelle verweist, beispielsweise CustomerID in der Tabelle " Orders ", die in der Tabelle "Customers" auf CustomerID verweist.
  
  

In einem Datenmodell wird der Primärschlüssel oder Alternativschlüssel als verknüpfte Spalte bezeichnet. Wenn eine Tabelle sowohl über einen Primärschlüssel als auch einen Alternativschlüssel verfügt, kann jeder als Basis für eine Tabellenbeziehung verwendet werden. Der Fremdschlüssel wird als Quellspalte oder einfach als Spalte bezeichnet. In unserem Beispiel wird eine Beziehung zwischen CustomerID in der Tabelle " Bestellungen " (der Spalte) und CustomerID in der Tabelle " Customers " (der Nachschlagespalte) definiert. Wenn Sie Daten aus einer relationalen Datenbank importieren, wählt Excel standardmäßig den Fremdschlüssel aus einer Tabelle und den entsprechenden Primärschlüssel aus der anderen Tabelle aus. Sie können jedoch jede Spalte verwenden, die über eindeutige Werte für die Suchspalte verfügt.

Typen von Beziehungen
Die Beziehung zwischen einem Kunden und einem Auftrag ist eine 1: n-Beziehung. Jeder Kunde kann mehrere Bestellungen haben, aber eine Bestellung kann nicht mehrere Kunden haben. Eine weitere wichtige Tabellenbeziehung ist eins zu eins. In unserem Beispiel enthält die CustomerDiscounts -Tabelle, die einen einzelnen Diskontsatz für jeden Kunden definiert, eine 1:1-Beziehung mit der Tabelle "Kunden".

Diese Tabelle zeigt die Beziehungen zwischen den drei Tabellen (Kunden, CustomerDiscountsund Bestellungen):

Beziehung	Typ	Nachschlagefeld	Spalte
Customers-CustomerDiscounts	1:1	Customers.CustomerID	CustomerDiscounts.CustomerID
Customers-Orders	1:n	Customers.CustomerID	Orders.CustomerID

Hinweis: M:n-Beziehungen werden in einem Datenmodell nicht unterstützt. Ein Beispiel für eine m:n-Beziehung ist eine direkte Beziehung zwischen der Tabelle "Products" und der Tabelle "Customers", bei der ein Kunde viele Produkte kaufen kann und ein Produkt von vielen Kunden gekauft werden kann.

Beziehungen und Leistung
Nachdem eine Beziehung erstellt wurde, muss Excel in der Regel alle Formeln neu berechnen, die Spalten aus Tabellen in der neu erstellten Beziehung verwenden. Die Verarbeitung nimmt, je nach Datenmenge und Komplexität der Beziehungen, einige Zeit in Anspruch. Weitere Informationen finden Sie unter Neuberechnen von Formeln.

Mehrere Beziehungen zwischen Tabellen
Ein Datenmodell kann mehrere Beziehungen zwischen zwei Tabellen aufweisen. Um genaue Berechnungen zu erstellen, benötigt Excel einen einzelnen Pfad von einer Tabelle zur nächsten. Daher kann es zwischen jedem Tabellenpaar jeweils nur eine aktive Beziehung geben. Obwohl die anderen inaktiv sind, können Sie eine inaktive Beziehung in Formeln und Abfragen angeben.

In der Diagrammansicht ist die aktive Beziehung eine durchgezogene Linie, und die inaktiven sind gestrichelte Linien. In AdventureWorksDW2012 enthält die Tabelle DimDate beispielsweise eine Spalte, DateKey, die sich auf drei verschiedene Spalten in der Tabelle FactInternetSales: OrderDate, DueDateund Liefer. Wenn die aktive Beziehung zwischen "DateKey" und "OrderDate" besteht, handelt es sich, sofern nicht anders angegeben, um die Standardbeziehung in Formeln.

Anforderungen für eine Tabellenbeziehung
Eine Beziehung kann erstellt werden, wenn die folgenden Anforderungen erfüllt sind:

Kriterien	Beschreibung
Eindeutiger Bezeichner für jede Tabelle	Jede Tabelle muss eine einzelne Spalte enthalten, die jede Zeile in dieser Tabelle eindeutig identifiziert. Diese Spalte wird oft als Primärschlüssel bezeichnet.
Eindeutige Suchspalten	Die Datenwerte in der Suchspalte müssen jedoch eindeutig sein. Die Spalte darf also keine Duplikate enthalten. In einem Datenmodell entsprechen Nullen und leere Zeichenfolgen einem leeren Wert, bei dem es sich um einen eindeutigen Datenwert handelt. Das bedeutet, dass die Suchspalte nicht mehrere NULL-Werte enthalten darf.
Kompatible Datentypen	Die Datentypen in der Quellspalte und Suchspalte müssen kompatibel sein. Weitere Informationen zu Datentypen finden Sie unter unterstützte Datentypen in Datenmodellen.

Nicht unterstützte Datenbankfeatures im Excel-Datenmodell
In einem Datenmodell kann keine Tabellenbeziehung erstellt werden, wenn der Schlüssel ein zusammengesetzter Schlüssel ist. Darüber hinaus sind Sie auf die Erstellung von 1:1- und 1:n-Beziehungen beschränkt. Andere Beziehungstypen werden nicht unterstützt.

Zusammengesetzte Schlüssel und Suchspalten

Ein zusammengesetzter Schlüssel besteht aus mehr als einer Spalte. Datenmodelle können keine zusammengesetzten Schlüssel verwenden: eine Tabelle muss immer genau eine Spalte aufweisen, die jede Zeile in der Tabelle eindeutig identifiziert. Wenn Sie Tabellen mit einer vorhandenen Beziehung auf Grundlage eines zusammengesetzten Schlüssels importieren, ignoriert der Tabellenimport-Assistent in Power Pivot diese Beziehung, da Sie nicht im Modell erstellt werden kann.

Wenn Sie eine Beziehung zwischen zwei Tabellen erstellen möchten und mehrere Spalten die Primär- und Fremdschlüssel definieren, müssen Sie die Werte zunächst kombinieren, um vor dem Erstellen der Beziehung eine einzelne Schlüsselspalte zu erstellen. Sie können dies tun, bevor Sie die Daten importieren, oder indem Sie eine berechnete Spalte im Datenmodell mithilfe des Power Pivot-Add-Ins erstellen.

m:n-Beziehungen

Ein Datenmodell kann keine m:n-Beziehungen aufweisen. Dem Modell können nicht einfach Verknüpfungstabellen  hinzugefügt werden. Sie können jedoch DAX-Funktionen verwenden, um m:n-Beziehungen zu modellieren.

Selbstjoins und Schleifen

Selbstjoins sind in einem Datenmodell nicht zulässig. Ein Selbstjoin ist eine rekursive Beziehung einer Tabelle mit sich selbst. Selbstjoins werden oft verwendet, um Über-/Unterordnungshierarchien zu definieren. Sie könnten z. B. eine Tabelle "Employees" mit sich selbst verknüpfen, um eine Hierarchie zu erzeugen, die die Managementkette in einem Unternehmen anzeigt.

Excel lässt die Erstellung von Schleifen zwischen Beziehungen in einer Arbeitsmappe nicht zu. Anders gesagt sind folgende Beziehungen unzulässig.

Tabelle 1, Spalte a   bis   Tabelle 2, Spalte f

Tabelle 2, Spalte f   bis   Tabelle 3, Spalte n

Tabelle 3, Spalte n   bis   Tabelle 1, Spalte a

Wenn Sie versuchen, eine Beziehung zu erstellen, die die Erstellung einer Schleife bedingt, wird ein Fehler ausgelöst.

Automatische Erkennung und Inferenz von Beziehungen in Power Pivot
Einer der Vorteile beim Importieren von Daten mit dem Power Pivot-Add-In besteht darin, dass Power Pivot manchmal in der Lage ist, Beziehungen zu erkennen und neue Beziehungen in dem in Excel erstellten Datenmodell zu erstellen.

Beim Import mehrerer Tabellen erkennt Power Pivot automatisch bestehende Beziehungen unter den Tabellen. Wenn Sie eine PivotTable erstellen, analysiert Power Pivot ebenfalls die Daten in den Tabellen. Hierbei werden mögliche Beziehungen erkannt, die nicht definiert wurden, und entsprechende Spalten vorgeschlagen, die in diese Beziehungen eingeschlossen werden sollten.

Der Erkennungsalgorithmus verwendet statistische Daten zu den Werten und Metadaten von Spalten, um Rückschlüsse auf die Wahrscheinlichkeit von Beziehungen ziehen zu können.

• Die Datentypen in allen verknüpften Spalten sollten kompatibel sein. Bei der automatischen Erkennung werden nur die Datentypen für ganze Zahlen und Text unterstützt. Weitere Informationen über Datentypen finden Sie unter In Datenmodellen unterstützteDatentypen.
  
  
• Damit die Beziehung erfolgreich erkannt wird, muss die Anzahl der eindeutigen Schlüssel in der Nachschlagespalte größer als die Werte in der Tabelle auf der n-Seite sein. Anders ausgedrückt: die Schlüsselspalte auf der n-Seite der Beziehung darf keine Werte enthalten, die sich nicht in der Schlüsselspalte der Nachschlagetabelle befinden. Angenommen, Sie verfügen über eine Tabelle, in der Produkte mit Ihren IDs (der Nachschlagetabelle) und einer Verkaufstabelle aufgelistet sind, in der die Umsätze für die einzelnen Produkte (die zahlreichen Seiten der Beziehung) aufgelistet sind. Wenn Ihre Verkaufsdatensätze die ID eines Produkts enthalten, das nicht über eine entsprechende ID in der Tabelle "Artikel" verfügt, kann die Beziehung nicht automatisch erstellt werden, Sie können Sie aber möglicherweise manuell erstellen. Damit Excel die Beziehung erkennt, müssen Sie zuerst die produktnachschlagetabelle mit den IDs der fehlenden Produkte aktualisieren.
  
  
• Stellen Sie sicher, dass der Name der Schlüsselspalte auf der n:Seite dem Namen der Schlüsselspalte in der Nachschlagetabelle ähnelt. Die Namen müssen nicht identisch sein. In einer Unternehmenseinstellung haben Sie beispielsweise häufig Variationen hinsichtlich der Namen von Spalten, die im wesentlichen dieselben Daten enthalten: EMP-ID, EmployeeID, Mitarbeiter-ID, EMP_IDusw. Der Algorithmus erkennt ähnliche Namen und weist Spalten, die ähnliche oder genau übereinstimmende Namen aufweisen, eine höhere Wahrscheinlichkeit zu. Um die Wahrscheinlichkeit der Erstellung einer Beziehung zu erhöhen, können Sie daher versuchen, die Spalten der zu importierenden Daten umzubenennen, damit diese den Spalten in den vorhandenen Tabellen ähneln. Wenn Excel mehrere mögliche Beziehungen findet, wird keine Beziehung erstellt.
  
  

Diese Informationen tragen zum Verständnis bei, warum nicht alle Beziehungen erkannt werden oder wie Änderungen von Metadaten, z. B. der Feldname und die Datentypen, die Ergebnisse der automatischen Beziehungserkennung verbessern können. Weitere Informationen finden Sie unter Behandeln von Problemen mit Beziehungen.

Automatische Erkennung für benannte Mengen

Beziehungen zwischen benannten Mengen und verwandten Feldern in einer PivotTable werden nicht automatisch erkannt. Sie können diese Beziehungen manuell erstellen. Wenn Sie automatische Beziehungserkennung verwenden möchten, entfernen Sie die benannten Mengen, und fügen Sie die einzelnen Felder aus den benannten Menge direkt der PivotTable hinzu.

Inferenz von Beziehungen

In einigen Fällen werden Beziehungen zwischen Tabellen automatisch verkettet. Wenn Sie beispielsweise eine Beziehung zwischen den beiden ersten unten aufgeführten Tabellenpaaren erstellen, wird abgeleitet, dass auch zwischen den Tabellen des dritten Paars eine Beziehung besteht, die dann automatisch erstellt wird.

Products und Category -- manuell erstellt

Category und SubCategory -- manuell erstellt

Products und SubCategory -- Beziehung wird abgeleitet

Damit Beziehungen automatisch verkettet werden, müssen die Beziehungen in eine Richtung zeigen, so wie oben gezeigt. Wenn ursprünglich z. B. Beziehungen zwischen Sales und Products und Sales und Customers bestehen, wird keine Beziehung abgeleitet. Das liegt daran, dass die Beziehung zwischen Products und Customers eine m:n-Beziehung ist.

:)