Beziehungen zwischen Tabellen in einem Datenmodell

Excel für Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Mehr... Weniger

Verleihen Sie Ihrer Datenanalyse mehr Power, indem Sie Beziehungen zwischen verschiedenen 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 sehen, warum Beziehungen nützlich sind, stellen Sie sich vor, Sie verfolgen Daten für Kundenbestellungen in Ihrem Unternehmen. Sie könnten alle Daten in einer einzigen Tabelle mit einer Struktur wie dieser verfolgen:

Kundennummer	Name	Email	Diskontsatz	Auftragsnummer	Bestelldatum	Produkt	Menge
1	Ashton	chris.ashton@contoso.com	.05	256	2010-01-07	Kompakt digital	11
1	Ashton	chris.ashton@contoso.com	.05	255	03.01.2010	Spiegelreflexkamera	fünfzehn
2	Jaworski	michal.jaworski@contoso.com	.10	254	03.01.2010	Budget Movie-Maker	27

Dieser Ansatz kann funktionieren, erfordert jedoch das Speichern vieler redundanter Daten, wie z. B. der Kunden-E-Mail-Adresse für jede Bestellung. Speicherplatz ist günstig, aber wenn sich die E-Mail-Adresse ändert, müssen Sie sicherstellen, dass Sie jede Zeile für diesen Kunden aktualisieren. Eine Lösung für dieses Problem besteht darin, die Daten in mehrere Tabellen aufzuteilen und Beziehungen zwischen diesen Tabellen zu definieren. Dies ist der Ansatz, der in relationalen Datenbanken wie SQL Server verwendet wird. Beispielsweise könnte eine Datenbank, die Sie importieren, Bestelldaten darstellen, indem Sie drei verknüpfte Tabellen verwenden:

Kunden

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

Kundenrabatte

[Kundennummer]	Diskontsatz
1	.05
2	.10

Aufträge

[Kundennummer]	Auftragsnummer	Bestelldatum	Produkt	Menge
1	256	2010-01-07	Kompakt digital	11
1	255	03.01.2010	Spiegelreflexkamera	fünfzehn
2	254	03.01.2010	Budget Movie-Maker	27

Innerhalb eines Datenmodells bestehen Beziehungen – eine, die Sie explizit erstellen, oder eine, die Excel automatisch in Ihrem Namen erstellt, wenn Sie gleichzeitig mehrere Tabellen importieren. Sie können auch das Power Pivot-Add-In verwenden, um das Modell zu erstellen oder zu verwalten. Einzelheiten finden Sie unter Erstellen eines Datenmodells in Excel .

Wenn Sie das Power Pivot-Add-In verwenden, um Tabellen aus derselben Datenbank zu importieren, kann Power Pivot die Beziehungen zwischen den Tabellen basierend auf den Spalten in [Klammern] erkennen und diese Beziehungen in einem Datenmodell reproduzieren, das es dahinter erstellt die Szenen. Weitere Informationen finden Sie in diesem Artikel unter Automatische Erkennung und Ableitung von Beziehungen . Wenn Sie Tabellen aus mehreren Quellen importieren, können Sie Beziehungen manuell erstellen, wie unter Erstellen einer Beziehung zwischen zwei Tabellen beschrieben.

Beziehungen basieren auf Spalten in jeder Tabelle, die dieselben Daten enthalten. Beispielsweise könnten Sie eine Customers -Tabelle mit einer Orders -Tabelle verknüpfen, wenn jede eine Spalte enthält, in der eine Customer ID gespeichert ist. Im Beispiel sind die Spaltennamen gleich, aber das ist keine Voraussetzung. Eine könnte CustomerID und eine andere CustomerNumber sein, solange alle Zeilen in der Orders-Tabelle eine ID enthalten, die auch in der Customers-Tabelle gespeichert ist.

In einer relationalen Datenbank gibt es mehrere Arten von Schlüsseln. Ein Schlüssel ist typischerweise eine Spalte mit speziellen Eigenschaften. Das Verständnis des Zwecks jedes Schlüssels kann Ihnen dabei helfen, ein Datenmodell mit mehreren Tabellen zu verwalten, das Daten für einen PivotTable-, PivotChart- oder Power View-Bericht bereitstellt.

Obwohl es viele Arten von Schlüsseln gibt, sind dies hier die wichtigsten für unseren Zweck:

• Primärschlüssel: Identifiziert eine Zeile in einer Tabelle eindeutig, z. B. CustomerID in der Customers -Tabelle.

• Alternativschlüssel (oder Kandidatenschlüssel): eine andere Spalte als der Primärschlüssel, die eindeutig ist. Beispielsweise könnte eine Employees-Tabelle eine Mitarbeiter-ID und eine Sozialversicherungsnummer speichern, die beide eindeutig sind.

• Fremdschlüssel: Eine Spalte, die auf eine eindeutige Spalte in einer anderen Tabelle verweist, wie z. B. CustomerID in der Orders -Tabelle, die auf CustomerID in der Customers-Tabelle verweist.

In einem Datenmodell wird der Primärschlüssel oder Alternativschlüssel als zugehörige Spalte bezeichnet. Wenn eine Tabelle sowohl einen Primär- als auch einen Alternativschlüssel hat, können Sie beide als Grundlage einer Tabellenbeziehung verwenden. Der Fremdschlüssel wird als Quellspalte oder einfach als Spalte bezeichnet. In unserem Beispiel würde eine Beziehung zwischen CustomerID in der Orders -Tabelle (Spalte) und CustomerID in der Customers -Tabelle (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. Sie können jedoch jede Spalte mit eindeutigen Werten für die Nachschlagespalte verwenden.

Die Beziehung zwischen einem Kunden und einer Bestellung ist eine Eins-zu-viele-Beziehung. Jeder Kunde kann mehrere Bestellungen haben, aber eine Bestellung kann nicht mehrere Kunden haben. Eine weitere wichtige Tabellenbeziehung ist die Eins-zu-Eins-Beziehung. In unserem Beispiel hier hat die CustomerDiscounts -Tabelle, die einen einzigen Rabattsatz für jeden Kunden definiert, eine Eins-zu-eins-Beziehung mit der Customers-Tabelle.

Diese Tabelle zeigt die Beziehungen zwischen den drei Tabellen ( Customers, CustomerDiscounts und Orders ):

Beziehung	Typ	Suchspalte	Spalte
Kunden-Kundenrabatte	eins zu eins	Kunden.Kunden-ID	Kundenrabatte.Kunden-ID
Kunden-Aufträge	eins zu vielen	Kunden.Kunden-ID	Bestellungen.Kunden-ID

Hinweis: Viele-zu-viele-Beziehungen werden in einem Datenmodell nicht unterstützt. Ein Beispiel für eine Viele-zu-Viele-Beziehung ist eine direkte Beziehung zwischen Produkten und Kunden, bei der ein Kunde viele Produkte kaufen kann und dasselbe Produkt von vielen Kunden gekauft werden kann.

Nachdem eine Beziehung erstellt wurde, muss Excel normalerweise alle Formeln neu berechnen, die Spalten aus Tabellen in der neu erstellten Beziehung verwenden. Die Verarbeitung kann je nach Datenmenge und Komplexität der Zusammenhänge einige Zeit in Anspruch nehmen. Weitere Einzelheiten finden Sie unter Formeln neu berechnen .

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

In der Diagrammansicht ist die aktive Beziehung eine durchgehende Linie und die inaktiven Beziehungen sind gestrichelte Linien. Beispielsweise enthält die Tabelle DimDate in AdventureWorksDW2012 eine Spalte, DateKey , die sich auf drei verschiedene Spalten in der Tabelle FactInternetSales bezieht : OrderDate, DueDate und ShipDate . Wenn die aktive Beziehung zwischen DateKey und OrderDate besteht, ist dies die Standardbeziehung in Formeln, sofern Sie nichts anderes angeben.

Eine Beziehung kann erstellt werden, wenn die folgenden Anforderungen erfüllt sind:

Kriterien	Beschreibung
Eindeutige Kennung für jede Tabelle	Jede Tabelle muss eine einzelne Spalte haben, die jede Zeile in dieser Tabelle eindeutig identifiziert. Diese Spalte wird oft als Primärschlüssel bezeichnet.
Eindeutige Nachschlagespalten	Die Datenwerte in der Nachschlagespalte müssen eindeutig sein. Mit anderen Worten, die Spalte darf keine Duplikate enthalten. In einem Datenmodell entsprechen Nullen und leere Zeichenfolgen einem Leerzeichen, das ein eindeutiger Datenwert ist. Das bedeutet, dass Sie nicht mehrere Nullen in der Nachschlagespalte haben können.
Kompatible Datentypen	Die Datentypen in der Quellspalte und der Nachschlagespalte müssen kompatibel sein. Weitere Informationen zu Datentypen finden Sie unter In Datenmodellen unterstützte Datentypen .

In einem Datenmodell können Sie keine Tabellenbeziehung erstellen, wenn der Schlüssel ein zusammengesetzter Schlüssel ist. Sie sind auch darauf beschränkt, Eins-zu-eins- und Eins-zu-viele-Beziehungen zu erstellen. Andere Beziehungstypen werden nicht unterstützt.

Zusammengesetzte Schlüssel und Nachschlagespalten

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 haben, die jede Zeile in der Tabelle eindeutig identifiziert. Wenn Sie Tabellen importieren, die eine vorhandene Beziehung basierend auf einem zusammengesetzten Schlüssel haben, ignoriert der Tabellenimport-Assistent in Power Pivot diese Beziehung, da sie nicht im Modell erstellt werden kann.

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

Many-to-Many-Beziehungen

Ein Datenmodell kann keine Viele-zu-Viele-Beziehungen haben. Sie können dem Modell nicht einfach Verbindungstabellen hinzufügen. Sie können jedoch DAX-Funktionen verwenden, um viele-zu-viele-Beziehungen zu modellieren.

Self-Joins und Loops

Self-Joins sind in einem Datenmodell nicht zulässig. Ein Self-Join ist eine rekursive Beziehung zwischen einer Tabelle und sich selbst. Self-Joins werden häufig verwendet, um Eltern-Kind-Hierarchien zu definieren. Beispielsweise könnten Sie eine Employees-Tabelle mit sich selbst verknüpfen, um eine Hierarchie zu erstellen, die die Managementkette eines Unternehmens darstellt.

Excel lässt das Erstellen von Schleifen zwischen Beziehungen in einer Arbeitsmappe nicht zu. Mit anderen Worten, der folgende Satz von Beziehungen ist verboten.

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 zur Erstellung einer Schleife führen würde, wird ein Fehler generiert.

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

Wenn Sie mehrere Tabellen importieren, erkennt Power Pivot automatisch alle bestehenden Beziehungen zwischen den Tabellen. Wenn Sie eine PivotTable erstellen, analysiert Power Pivot außerdem die Daten in den Tabellen. Es erkennt mögliche Beziehungen, die nicht definiert wurden, und schlägt geeignete Spalten vor, die in diese Beziehungen aufgenommen werden sollen.

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

• Datentypen in allen zugehörigen Spalten sollten kompatibel sein. Für die automatische Erkennung werden nur Ganzzahl- und Textdatentypen unterstützt. Weitere Informationen zu Datentypen finden Sie unter In Datenmodellen unterstützte Datentypen .

• Damit die Beziehung erfolgreich erkannt wird, muss die Anzahl der eindeutigen Schlüssel in der Nachschlagespalte größer sein als die Werte in der Tabelle auf der Viele-Seite. Mit anderen Worten, die Schlüsselspalte auf der Viele-Seite der Beziehung darf keine Werte enthalten, die nicht in der Schlüsselspalte der Nachschlagetabelle enthalten sind. Angenommen, Sie haben eine Tabelle, die Produkte mit ihren IDs auflistet (die Nachschlagetabelle), und eine Verkaufstabelle, die die Verkäufe für jedes Produkt auflistet (die viele Seite der Beziehung). Wenn Ihre Verkaufsdatensätze die ID eines Produkts enthalten, für das es keine entsprechende ID in der Tabelle „Produkte" gibt, kann die Beziehung nicht automatisch erstellt werden, aber Sie können sie möglicherweise manuell erstellen. Damit Excel die Beziehung erkennt, müssen Sie zuerst die Produktsuchtabelle mit den IDs der fehlenden Produkte aktualisieren.

• Stellen Sie sicher, dass der Name der Schlüsselspalte auf der Viele-Seite dem Namen der Schlüsselspalte in der Nachschlagetabelle ähnelt. Die Namen müssen nicht genau gleich sein. Beispielsweise haben Sie in einer Geschäftsumgebung häufig Variationen der Namen von Spalten, die im Wesentlichen dieselben Daten enthalten: Emp ID, EmployeeID, Employee ID, EMP_ID und so weiter. Der Algorithmus erkennt ähnliche Namen und weist Spalten mit ähnlichen oder genau übereinstimmenden Namen eine höhere Wahrscheinlichkeit zu. Um die Wahrscheinlichkeit des Erstellens einer Beziehung zu erhöhen, können Sie daher versuchen, die Spalten in den Daten, die Sie importieren, in etwas umzubenennen, das den Spalten in Ihren vorhandenen Tabellen ähnelt. Wenn Excel mehrere mögliche Beziehungen findet, wird keine Beziehung erstellt.

Diese Informationen können Ihnen helfen zu verstehen, warum nicht alle Beziehungen erkannt werden oder wie Änderungen in Metadaten – wie Feldnamen und Datentypen – die Ergebnisse der automatischen Beziehungserkennung verbessern können. Weitere Informationen finden Sie unter Fehlerbehebung bei 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 die automatische Beziehungserkennung verwenden möchten, entfernen Sie jeden benannten Satz, und fügen Sie die einzelnen Felder aus dem benannten Satz direkt zur PivotTable hinzu.

Rückschluss auf Beziehungen

In einigen Fällen werden Beziehungen zwischen Tabellen automatisch verkettet. Wenn Sie beispielsweise eine Beziehung zwischen den ersten beiden Tabellensätzen unten erstellen, wird davon ausgegangen, dass eine Beziehung zwischen den anderen beiden Tabellen besteht, und es wird automatisch eine Beziehung hergestellt.

Produkte und Kategorie – manuell erstellt

Kategorie und Unterkategorie – manuell erstellt

Produkte und Unterkategorie – Beziehung wird abgeleitet

Damit Beziehungen automatisch verkettet werden können, müssen die Beziehungen wie oben gezeigt in eine Richtung gehen. Wenn die anfänglichen Beziehungen beispielsweise zwischen Verkäufen und Produkten und Verkäufen und Kunden bestanden, wird keine Beziehung abgeleitet. Dies liegt daran, dass die Beziehung zwischen Produkten und Kunden eine Viele-zu-Viele-Beziehung ist.