Übersicht über Beziehungen
Dieses Thema gibt eine Einführung in die Beziehungen, die Sie in PowerPivot für Excel zwischen Tabellen definieren können. Das Thema enthält die folgenden Abschnitte:
Was ist eine Beziehung?
Anforderungen für Beziehungen
Automatische Erkennung und Inferenz von Beziehungen
Nachdem Sie dieses Thema gelesen haben, sollten Sie wissen, was eine Beziehung ist, welche Anforderungen das Definieren einer Beziehung umfasst und wie PowerPivot für Excel Beziehungen automatisch erkennen kann. Dabei lernen Sie einen Teil der Terminologie kennen, mit der Datenbankfachleute Beziehungen beschreiben.
Was ist eine Beziehung?
Eine Beziehung ist eine Verbindung zwischen zwei Datentabellen, die auf mindestens einer Spalte in jeder Tabelle basiert (in PowerPivot basiert sie auf genau einer Spalte in jeder Tabelle). 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 |
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 in PowerPivot für Excel importieren, könnte die Bestelldaten z. B. mit 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 |
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 |
Wenn Sie diese Tabellen aus der gleichen Datenbank importieren, kann PowerPivot die Beziehungen zwischen den Tabellen auf Grundlage der Spalten erkennen, die in eckigen Klammern stehen, und diese Beziehungen im PowerPivot-Fenster reproduzieren. Weitere Informationen finden Sie unter "Automatische Erkennung und Inferenz von Beziehungen" in diesem Thema. Wenn Sie Tabellen aus mehreren Quellen importieren, können Sie Beziehungen manuell erstellen, wie in Erstellen einer Beziehung zwischen zwei Tabellen beschrieben.
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" haben, 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. Die folgenden vier Typen von Schlüsseln sind für unsere Zwecke am interessantesten:
Primärschlüssel: Identifiziert eine Zeile in einer Tabelle eindeutig, z. B. CustomerID in der Customers-Tabelle.
Alternativschlüssel (oder Kandidatenschlüssel): Eine eindeutige Spalte, die nicht mit dem Primärschlüssel identisch ist. In einer Employees-Tabelle kann beispielsweise eine Mitarbeiter-ID und eine Sozialversicherungsnummer gespeichert werden, die beide eindeutig sind.
Fremdschlüssel: Eine Spalte, die auf eine eindeutige Spalte in einer anderen Tabelle verweist, z. B. CustomerID in der Orders-Tabelle, die auf CustomerID in der Customers-Tabelle verweist.
Zusammengesetzter Schlüssel: Ein Schlüssel, der aus mehr als einer Spalte besteht. Zusammengesetzte Schlüssel werden in PowerPivot für Excel nicht unterstützt. Weitere Informationen finden Sie unter "Zusammengesetzte Schlüssel und Suchspalten" in diesem Thema.
In PowerPivot für Excel wird der Primärschlüssel oder der Alternativschlüssel als verknüpfte Suchspalte oder nur als Suchspalte, bezeichnet. Wenn eine Tabelle sowohl über einen Primärschlüssel als auch einen Alternativschlüssel verfügt, können Sie beide als Suchspalte verwenden. Der Fremdschlüssel wird als Quellspalte oder nur als Spalte bezeichnet. In unserem Beispiel wird zwischen CustomerID in der Orders-Tabelle (die Spalte) und CustomerID (die Suchspalte) in der Customers-Tabelle eine Beziehung definiert. Wenn Sie Daten aus einer relationalen Datenbank importieren, wählt PowerPivot für 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 Rabatt für jeden Kunden definiert ist, und der Customers-Tabelle besteht eine 1:1-Beziehung. Ein Beispiel für eine m:n-Beziehung ist eine direkte Beziehung zwischen der Products-Tabelle und der Customers-Tabelle, bei der ein Kunde viele Produkte kaufen und ein Produkt von vielen Kunden gekauft werden kann. PowerPivot für Excel unterstützt keine m:n-Beziehungen in der Benutzeroberfläche. Weitere Informationen finden Sie unter "m:n-Beziehungen" in diesem Thema.
In der folgenden Tabelle werden die Beziehungen zwischen den drei Tabellen angezeigt:
Beziehung |
Typ |
Suchspalte |
Spalte |
|---|---|---|---|
Customers-CustomerDiscounts |
1:1 |
Customers.CustomerID |
CustomerDiscounts.CustomerID |
Customers-Orders |
1:n |
Customers.CustomerID |
Orders.CustomerID |
Beziehungen und Leistung
Nach dem Erstellen einer Beziehung muss PowerPivot für 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. Weitere Informationen finden Sie unter Neuberechnen von Formeln.
Anforderungen für Beziehungen
Beim Erstellen von Beziehungen müssen in PowerPivot für Excel mehrere Anforderungen beachtet werden:
Nur eine Beziehung zwischen zwei Tabellen
Mehrere Beziehungen können zu mehrdeutigen Abhängigkeiten zwischen Tabellen führen. Um genaue Berechnungen zu erstellen, benötigen Sie einen einzelnen Pfad von einer Tabelle zur nächsten Tabelle. Daher kann es zwischen jedem Tabellenpaar nur eine Beziehung geben. Beispielsweise enthält in AdventureWorks DW 2008 die Tabelle DimDate eine Spalte, DateKey, die eine Beziehung mit drei verschiedenen Spalten in der Tabelle FactInternetSales hat: OrderDate, DueDate und ShipDate. Wenn Sie versuchen, diese Tabellen zu importieren, wird die erste Beziehung erfolgreich erstellt, bei den darauffolgenden Beziehungen, die dieselbe Spalte verwenden, wird jedoch der folgende Fehler ausgelöst:
* Beziehung: table[column 1]-> table[column 2] - Status: Fehler - Ursache: Zwischen Tabelle <1> und <2> kann keine Beziehung erstellt werden. Zwischen zwei Tabellen kann nur eine direkte oder indirekte Beziehung vorhanden sein.
Wenn Sie zwei Tabellen mit mehreren Beziehungen zu einander haben, müssen Sie mehrere Kopien der Tabelle importieren, die die Suchspalte enthält, und dann eine Beziehung zwischen jedem Tabellenpaar erstellen.
Eine Beziehung pro Quellspalte
Eine Quellspalte kann nicht an mehreren Beziehungen beteiligt sein. Wenn Sie eine Spalte bereits als Quellspalte in einer Beziehung verwendet haben, diese Spalte jedoch mit einer anderen zugehörigen Suchspalte in einer anderen Tabelle verknüpfen möchten, erstellen Sie eine Kopie der Spalte, und verwenden Sie diese Spalte für die neue Beziehung.
Mithilfe einer DAX-Formel in einer berechneten Spalte kann problemlos eine Kopie einer Spalte mit exakt gleichen Werten erstellt werden. Weitere Informationen finden Sie unter Erstellen einer berechneten Spalte.
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 PowerPivot für Excel entsprechen Nullen und leere Zeichenfolgen einem Leerzeichen, welches ein eindeutiger Datenwert ist. Das bedeutet, dass die Suchspalte nicht mehrere NULLEN enthalten darf.
Kompatible Datentypen
Die Datentypen in der Quellspalte und Suchspalte müssen kompatibel sein. Weitere Informationen zu Datentypen finden Sie unter In PowerPivot-Arbeitsmappen unterstützte Datentypen.
Zusammengesetzte Schlüssel und Suchspalten
Sie können zusammengesetzte Schlüssel nicht in einer PowerPivot-Arbeitsmappe verwenden. Es muss immer genau eine Spalte vorhanden sein, die jede Zeile in der Tabelle eindeutig identifiziert. Wenn Sie versuchen, Tabellen mit einer vorhandenen Beziehung auf Grundlage eines zusammengesetzten Schlüssels zu importieren, ignoriert der Tabellenimport-Assistent diese Beziehung, da sie in PowerPivot nicht erstellt werden kann.
Wenn Sie in PowerPivot eine Beziehung zwischen zwei Tabellen erstellen möchten und mehrere Spalten die Primär- und Fremdschlüssel definieren, müssen Sie die Werte kombinieren, um vor dem Erstellen der Beziehung eine einzelne Schlüsselspalte zu erstellen. Sie können dies vor dem Importieren von Daten durchführen, oder Sie können den Schritt in PowerPivot ausführen, indem Sie eine berechnete Spalte erstellen.
m:n-Beziehungen
PowerPivot für Excel unterstützt keine m:n-Beziehungen, und Sie können nicht einfach Verknüpfungstabellen in PowerPivot hinzufügen. Sie können jedoch DAX-Funktionen verwenden, um m:n-Beziehungen zu modellieren.
Selbstjoins und Schleifen
Selbstjoins sind in PowerPivot-Tabellen nicht zulässig. Ein Selbstjoin ist eine rekursive Beziehung einer Tabelle mit sich selbst. Selbstjoins werden oft verwendet, um Parent-Child-Hierarchien zu definieren. Sie könnten z. B. eine Employees-Tabelle mit sich selbst verknüpfen, um eine Hierarchie zu erzeugen, die die Managementkette in einem Unternehmen anzeigt.
PowerPivot für 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 zu Tabelle 2, Spalte f
Tabelle 2, Spalte f zu Tabelle 3, Spalte n
Tabelle 3, Spalte n zu 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
Wenn Sie Daten in das PowerPivot-Fenster importieren, erkennt der Tabellenimport-Assistent automatisch vorhandene Beziehungen zwischen Tabellen. Wenn Sie eine PivotTable erstellen, analysiert PowerPivot für Excel 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 folgenden Datentypen unterstützt: ganze Zahlen und Text. Weitere Informationen zu Datentypen finden Sie unter In PowerPivot-Arbeitsmappen unterstützte Datentypen.
Damit die Beziehung erkannt wird, muss die Anzahl der eindeutigen Schlüssel in der Suchspalte größer als die Werte in der Tabelle auf der n-Seite sein. Mit anderen Worten: Die Schlüsselspalte auf der n-Seite der Beziehung darf keine Werte beinhalten, die nicht in der Schlüsselspalte der Suchtabelle vorhanden sind. Beispiel: Angenommen, Sie verfügen über eine Tabelle, in der Produkte mit ihren IDs aufgeführt sind (die Suchtabelle), und über eine Verkaufstabelle, in der die Verkaufszahlen für jedes Produkt aufgeführt sind (die n-Seite der Beziehung). Wenn die Verkaufsdatensätze die ID eines Produkts beinhalten, das keine entsprechende ID in der Products-Tabelle besitzt, kann die Beziehung nicht automatisch erstellt werden. Unter Umständen ist jedoch eine manuelle Erstellung möglich. Damit PowerPivot für Excel die Beziehung erkennt, muss zuerst die Produktsuchtabelle mit den IDs der fehlenden Produkte aktualisiert werden.
Stellen Sie sicher, dass der Name der Schlüsselspalte auf der n-Seite dem Namen der Schlüsselspalte in der Suchtabelle ähnelt. Die Namen müssen nicht genau übereinstimmen. In Unternehmen kommen beispielsweise häufig Variationen der Namen von Spalten vor, die im Grunde genommen dieselben Daten enthalten: Emp ID, EmployeeID, Employee 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 zu erhöhen, dass eine Beziehung erstellt wird, können Sie daher versuchen, die Spalten der zu importierenden Daten umzubenennen, damit diese den Spalten in den vorhandenen Tabellen ähneln. Wenn PowerPivot für 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 Tabellenpaars 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 bestanden, wird keine Beziehung abgeleitet. Das liegt daran, dass die Beziehung zwischen Products und Customers eine m:n-Beziehung ist.