Erweitern Sie die Leistung Ihrer Datenanalyse, 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 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 einzigen Tabelle mit einer Struktur wie der folgenden nachverfolgen:
| CustomerID | Name | DiscountRate | OrderID | Bestelldatum | Produkt | 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 | |
|---|---|---|
| 1 | Ashton | chris.ashton@contoso.com |
| 2 | Jaworski | michal.jaworski@contoso.com |
CustomerDiscounts
| [CustomerID] | DiscountRate |
|---|---|
| 1 | .05 |
| 2 | .10 |
Orders
| [CustomerID] | OrderID | Bestelldatum | Produkt | 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 |
Beziehungen existieren innerhalb eines Datenmodells, d. h. eines Modells, das Sie explizit erstellen, oder eines Modells, das Excel automatisch in Ihrem Namen 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 verwenden, um Tabellen aus derselben Datenbank zu importieren, kann Power Pivot die Beziehungen zwischen den Tabellen anhand der Spalten in [eckigen Klammern] erkennen und diese Beziehungen in einem Datenmodell reproduzieren, das es hinter den Kulissen erstellt. 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. Sie können z. B. die Tabelle " Kunden " mit der Tabelle "Bestellungen " verknüpfen, wenn beide eine Spalte enthalten, in der eine Kunden-ID gespeichert 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 mehrere Arten von Schlüsseln. Ein Schlüssel ist in der Regel eine 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 für unseren Zweck hier die wichtigsten:
- Primärschlüssel: Identifiziert eine Zeile in einer Tabelle eindeutig, z. B. "Kunden-Nr " in der Tabelle "Kunden ".
- Alternativschlüssel (oder Kandidatenschlüssel): eine Spalte, die nicht der Primärschlüssel ist und 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, z. B. "Kunden-Nr" in der Tabelle "Bestellungen ", die in der Tabelle "Kunden " auf "Kunden-Nr " 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 Kunden-ID in der Tabelle Bestellungen (der Spalte) und Kunden-ID in der Tabelle Kunden (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 die 1:1-Beziehung. In unserem Beispiel hier hat die Tabelle "Kundenrabatte ", die einen einzelnen Rabattsatz für jeden Kunden definiert, eine 1:1-Beziehung zur Tabelle "Kunden".
In dieser Tabelle sind die Beziehungen zwischen den drei Tabellen ("Customers", "CustomerDiscounts" und "Orders") dargestellt:
| Beziehung | F | Nachschlagespalte | 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 durchzuführen, benötigt Excel einen einzigen 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 in Formeln und Abfragen eine inaktive Beziehung angeben.
In der Diagrammansicht ist die aktive Beziehung eine einfarbige Linie, und die inaktiven sind gestrichelte Linien. In AdventureWorksDW2012 enthält die Tabelle DimDate beispielsweise eine Spalte, DateKey, die mit drei verschiedenen Spalten in der Tabelle FactInternetSales verknüpft ist: OrderDate, DueDate und ShipDate. 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 Nachschlagespalten | 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 in der Nachschlagespalte nicht mehrere Nullen vorhanden sein dürfen. |
| Kompatible Datentypen | Die Datentypen in der Quellspalte und Suchspalte müssen kompatibel sein. Weitere Informationen zu Datentypen finden Sie unter In Datenmodellen unterstützte Datentypen. |
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 mehreren Spalten. Datenmodelle können keine zusammengesetzten Schlüssel verwenden: Eine Tabelle muss immer genau eine Spalte enthalten, die jede Zeile in der Tabelle eindeutig identifiziert. Wenn Sie Tabellen importieren, die über eine bestehende Beziehung verfügen, die auf einem zusammengesetzten Schlüssel basiert, 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 von und automatischer Rückschluss auf Beziehungen in PowerPivot
Einer der Vorteile des Importierens von Daten mithilfe des Power Pivot-Add-Ins 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 auch 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ützte Datentypen.
- Damit die Beziehung erfolgreich erkannt werden kann, 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 aufgeführt werden (die Nachschlagetabelle) und eine Umsatztabelle, in der die Umsätze für jedes Produkt (die n-Seite der Beziehung) aufgeführt sind. Wenn Ihre Verkaufsdatensätze die ID eines Produkts enthalten, für das es in der Tabelle "Artikel" keine entsprechende ID gibt, 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 Nachschlagetabelle "Produkt" 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 einem Unternehmen gibt es beispielsweise häufig Variationen der Namen von Spalten, die im Wesentlichen dieselben Daten enthalten: Emp-ID, Mitarbeiter-ID, Mitarbeiter-ID, EMP_ID usw. 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.