Beziehungen zwischen Tabellen in einem Datenmodell

Ihr Browser unterstützt kein Video. Installieren Sie Microsoft Silverlight, Adobe Flash Player oder Internet Explorer 9.
Video: Beziehungen in Power View und PowerPivot

Optimieren Sie die Datenanalyse, indem Sie Beziehungen zwischen Daten in unterschiedlichen Tabellen herstellen. Eine Beziehung ist eine Verbindung zwischen zwei Datentabellen, die auf einer Spalte in jeder der Tabellen basiert. 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önnten alle Daten in einer einzelnen Tabelle verfolgen, die über eine Struktur wie die folgende verfügt:

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

Beziehungen kommen innerhalb eines Datenmodells vor, das Sie explizit erstellen bzw. das von Excel für Sie erstellt wird, 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.

Seitenanfang

Inhalt dieses Artikels

Spalten und Schlüssel

Typen von Beziehungen

Beziehungen und Leistung

Mehrere Beziehungen zwischen Tabellen

Anforderungen für eine Tabellenbeziehung

In einer Tabellenbeziehung nicht unterstützt

Zusammengesetzte Schlüssel und Suchspalten

m:n-Beziehungen

Selbstjoins und Schleifen

Automatische Erkennung von und automatischer Rückschluss auf Beziehungen in PowerPivot

Automatische Erkennung für benannte Mengen

Inferenz von Beziehungen

Spalten und Schlüssel

Beziehungen basieren auf den Spalten in jeder Tabelle, die die gleichen Daten enthalten. Die Tabellen "Customers" und "Orders" können z. B. miteinander verknüpft werden, da beide eine Spalte enthalten, in der eine Kundennummer (CustomerID) 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 Typen von Schlüsseln, die in der Regel nur Spalten mit besonderen Eigenschaften sind. 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.

Die folgenden Schlüssel sind für unsere Zwecke am interessantesten:

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

  • Alternativschlüssel (oder Kandidatenschlüssel): Eine eindeutige Spalte, die nicht mit dem Primärschlüssel identisch 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. auf die Spalte CustomerID in der Tabelle Orders, die auf CustomerID in der Tabelle Customers 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 zwischen CustomerID in der Tabelle Orders (der Spalte) und CustomerID in der Tabelle Customers (der Suchspalte) eine Beziehung 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 Customers und Orders ist eine 1:n-Beziehung. Jeder Kunde kann mehrere Bestellungen haben, aber eine Bestellung kann nicht mehrere Kunden haben. Die anderen Typen von Beziehungen sind 1:1 und m:n. Zwischen der CustomerDiscounts-Tabelle, in der ein einzelner Diskontsatz für jeden Kunden definiert ist, und der Customers-Tabelle besteht eine 1:1-Beziehung.

In der folgenden Tabelle werden die Beziehungen zwischen den drei Tabellen angezeigt:

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

Nach dem Erstellen einer Beziehung muss Excel normalerweise alle Formeln neu berechnen, die Spalten aus den Tabellen in der neu erstellten Beziehung verwenden. Die Verarbeitung nimmt, je nach Datenmenge und Komplexität der Beziehungen, einige Zeit in Anspruch. Ausführliche 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 Tabelle. Daher kann es zwischen jedem Tabellenpaar jeweils nur eine aktive Beziehung geben. Die übrigen sind inaktiv, können jedoch trotzdem in Formeln und Abfragen angegeben werden. In der Diagrammsicht wird die aktive Beziehung als durchgezogene Linie und inaktive Beziehungen als gestrichelte Linien angezeigt. Beispiel: In AdventureWorksDW2012 enthält die Tabelle "DimDate" die 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.

Seitenanfang

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 NULL-Werte und leere Zeichenfolgen einem Leerzeichen, das einem eindeutigen Datenwert entspricht. 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 über Datentypen finden Sie unter In Datenmodellen unterstützteDatentypen.

In einer Tabellenbeziehung nicht unterstützt

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. In Datenmodellen können keine zusammengesetzten Schlüssel verwendet werden. Es muss immer genau eine Spalte in einer Tabelle vorhanden sein, 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 im Modell nicht 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 vor dem Import der Daten erledigen, oder indem Sie mithilfe des Power Pivot-Add-Ins eine berechnete Spalte im Datenmodell 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.

Seitenanfang

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 eindeutiger Schlüssel in der Suchspalte größer sein als die Werte in der Tabelle auf der n-Seite. Das heißt, die Schlüsselspalte auf der n-Seite der Beziehung darf keine Werte enthalten, die nicht in der Schlüsselspalte der Nachschlagetabelle enthalten sind. Beispiel: Es gibt eine Tabelle, in der Produkte mit ihren IDs (die Nachschlagetabelle) aufgeführt sind, und eine Verkaufstabelle, in der der Umsatz für jedes Produkt (die n:Seite der Beziehung) aufgeführt ist. Wenn die Umsatzdatensätze die ID eines Produkts enthalten, das keine entsprechende ID in der Produkttabelle hat, kann die Beziehung nicht automatisch erstellt werden. Sie können sie jedoch 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 Unternehmen kommen beispielsweise häufig Variationen der Namen von Spalten vor, die im Grunde genommen dieselben Daten enthalten: Mitarb.-ID, MitarbeiterID, Mitarbeiter-ID, MITARB_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.

Seitenanfang

Teilen Facebook Facebook Twitter Twitter E-Mail E-Mail

War diese Information hilfreich?

Sehr gut. Noch anderes Feedback?

Was können wir verbessern?

Vielen Dank für Ihr Feedback!

×