Power Pivot. Oder: Gute Beziehungen sind alles 4

Nachdem ich im letzten Artikel ein paar grundlegende Gedanken und Überlegungen über das Datenmodells und Power Pivot vorgestellt habe, wollen wir uns heute etwas weiter in die Praxis vorwagen. Eine Schwäche normaler Pivot-Tabellen ist es, wenn die auszuwertenden Daten auf mehrere Quelltabellen verteilt sind. Denn von wenigen eher exotischen Ausnahmen abgesehen kann eine normale Pivot-Tabelle eben nicht mit mehr als einer Datenquelle arbeiten.

Daher ist es üblich, die Daten vorher zusammenzuführen: Entweder auf die klassische und mühsame Tour per SVERWEIS und Co. Oder deutlich komfortabler und sicherer mit Power Query.

Das Datenmodell und Power Pivot machen solche Vorarbeiten aber überflüssig. Stattdessen bleiben alle Daten in ihren unterschiedlichen Quelltabellen und werden lediglich über sogenannte Beziehungen miteinander verbunden.

Wie das genau funktioniert und was man damit anstellen kann, davon handelt der heute Artikel.

Und los geht’s.

Die Ausgangslage

Als Beispiel dient wieder die Rechnungstabelle, die ich bereits im letzten Beitrag verwendet habe. Die Tabelle enthält die grundsätzlichen Transaktionsdaten, wie Rechnungsnummer und -datum, Kundennummer, Artikelnummer und Verkaufsmengen. Allerdings werden wir diesmal auch auf andere Daten zurückgreifen. Denn Details zu den Kunden und Artikeln sind in separaten Tabellen abgespeichert. Die Beispieldatei zum heutigen Artikel kannst du dir hier herunterladen.

Ein solche Trennung zwischen sogenannten Bewegungsdaten (Rechnungen) und Stammdaten (Kunden, Artikel) ist in Warenwirtschaftssystem und anderen Datenbanken üblich und dient vor allem einer effizienteren Datenhaltung.

Rechnungen (Bewegungsdatn)

Artikel und Kunden (Stammdaten)

Wie eingangs erwähnt, müssen Verbindungen zwischen diesen Tabellen hergestellt werden, um sie gemeinsam auswertbar zu machen. Diese Verbindungen oder Beziehungen erfordern gemeinsame Schlüsselfelder, die in den Tabellen enthalten sind. Das wären beispielsweise die Artikelnummer oder die Kundennummer. In der Rechnungstabelle kommt eine bestimmte Artikelnummer erfahrungsgemäß mehrfach vor – die Produkte werden ja hoffentlich häufig verkauft. In der Artikeltabelle hingegen darf jede Artikelnummer nur ein einziges Mal enthalten sein. Genauso verhält es sich mit der Kundennummer. Damit können sogenannte 1:n-Beziehungen zwischen den Tabellen hergestellt werden.

Und genau das werden wir jetzt machen. Dazu gibt es zwei Möglichkeiten.

Gute Beziehungen: Variante 1

Bei sämtlichen Tabellen (Rechnungen, Kunden, Artikel) handelt es sich um formatierte Tabellen. Dies ist eine Grundvoraussetzung für alle folgenden Schritte.

Rufen wir also das Menü „Daten | Beziehungen“ auf:

Beziehungen erstellen

Wichtig:
Diese Schaltfläche ist nur dann aktiv, wenn die aktuelle Arbeitsmappe mindestens zwei formatierte Tabellen enthält. Ansonsten wären Beziehungen ja auch nicht möglich. Die Tabellen dürfen dabei auch in unterschiedlichen Arbeitsblättern liegen.

Es erscheint ein leeres Dialogfenster, in dem wir über die Schaltfläche „Neu“ die erste Beziehung einrichten werden:

Beziehungen verwalten

Eine neue Beziehung erstellen

Im ersten Auswahlfeld wählen wir die Rechnungstabelle („Arbeitsblatttabelle: tblRechnungen“) aus, im Feld daneben werden danach alle Felder aus dieser Tabelle aufgelistet. Da die erste Beziehung zur Artikeltabelle erfolgen soll, markieren wir daher das Feld „Artikel“.

Als verwandte Tabelle tragen wir jetzt die Artikeltabelle („Arbeitsblatttabelle: tblArtikel“) aus und geben im Feld „Verwandte Spalte“ ebenfalls „Artikel“ ein:

Beziehung zwischen Rechnungen und Artikel

Mit einem Klick auf „OK“ schließen wir die Erstellung ab und sehen damit die neue Beziehung im ersten Fenster. Nun kommt die nächste Beziehung dran: Zwischen Rechnungs- und Kundentabelle. Hier ist die Kundennummer die gemeinsame Spalte:

Beziehung zwischen Rechnungen und Kunden

An dieser Stelle möchte ich auf zwei Dinge hinweisen:
Erstens hat sich die Bezeichnung der Rechnungstabelle geändert. Im Auswahlfeld steht jetzt nicht mehr „Arbeitsblatttabelle“, sondern „Datenmodelltabelle“. Denn durch das Erstellen der Beziehungen wurden die beiden Tabellen automatisch im Hintergrund in das Datenmodell geladen.
Außerdem sieht man, dass die Spaltennamen nicht zwingend identisch sein müssen: Kundennr vs. Kdnr. Der Name spielt also keine Rolle, wichtig ist nur, dass die Feldinhalte zueinander passen.

Damit sind wir zunächst einmal fertig mit unserer Beziehungsarbeit, alle Voraussetzungen für eine gemeinsame Pivot-Tabelle sind geschaffen.

Die Power-Pivot-Tabelle

Über das Menü „Einfügen | PivotTable | Aus dem Datenmodell“ wird jetzt eine neue Pivot-Tabelle erstellt:

PivotTable aus dem Datenmodell erstellen

Nun sieht eigentlich alles fast so aus, wie man es von einer normalen Pivot-Tabelle kennt. Ein noch leerer Pivot-Bereich im Arbeitsblatt und am rechten Rand die Liste mit den Pivot-Feldern. Allerdings werden jetzt gleich alle vier Quelltabellen angezeigt (drei davon hatten wir ja gerade miteinander verknüpft, auf die vierte Tabelle tblVerkäufer kommen wir später noch zu sprechen). Über das Pfeilsymbol können die Tabellen „aufgeklappt“ werden, so dass man die Felder sieht:

Übersicht der Tabellen und Felder

Für die erste Auswertung hole ich mir die Felder „Artikel“ und „Menge“ aus der Tabelle tblRechnungen und das Feld „Bezeichnung“ aus tblArtikel. Da die Tabellen über die zuvor eingerichteten Beziehungen miteinander verbunden sind, können wir jetzt auch die Felder aus allen Tabellen in unserer Pivot-Tabelle verwenden – ganz so, als ob sie alle aus einer einzigen Quelle stammen würden!

PivotTable aus zwei Quelltabellen

Und genauso funktioniert es natürlich auch bei einer Auswertung nach Kunden. Denn auch wenn in der Rechnungstabelle nur die Kundennummern vorhanden sind, kann ich über die erstellten Beziehungen doch die passenden Namen aus der Kundentabelle anzeigen lassen. Das Datenmodell macht’s möglich.

PivotTable aus drei Quelltabellen

Alternativ: Beziehungen im Datenmodell einrichten

Ich möchte noch einen zweiten, noch bequemeren Weg aufzeigen, Beziehungen zwischen Tabellen zu erstellen. In der Rechnungstabelle sind keinerlei Informationen über die Verkäufer enthalten. Dafür gibt es in der Kundentabelle eine Verkäufernummer, denn jedem Kunden ist ein fester Vertriebsmitarbeiter zugeordnet. Die dazu passenden Namen und Vertriebsregionen befinden sich – du ahnst es bereits – in einer separaten Tabelle:

Kunden und Verkäufer

Diese Verkäufertabelle laden wir jetzt direkt ins Datenmodell. Dazu stellen wir die aktive Zelle irgendwo innerhalb der Verkäufertabelle und rufen das Menü „Power Pivot | Zu Datenmodell hinzufügen“ auf:

Verkäufertabelle ins Datenmodell laden

(Wie man das Power-Pivot Add-In aktiviert, hatte ich im ersten Artikel beschrieben).

Damit wird das Power Pivot-Fenster geöffnet und du siehst neben der Verkäufertabelle am unteren Bildschirmrand auch alle anderen Tabellen, die durch das Erstellen der Beziehungen automatisch geladen wurden.

Power Pivot-Editor

Jetzt brauchen wir nur noch die Beziehung. Dazu aktivierst du oben im Menüband die Diagrammansicht durch einen Klick auf die entsprechende Schaltfläche. Das Fenster sollte dann ungefähr so aussehen:

Diagrammsicht

Man erkennt die vorhandenen Verbindungen (= Beziehungen) zwischen Kunden, Rechnungen und Artikel. Und etwas abseits liegt die Verkäufertabelle. Du kannst die Anordnung der Tabelle ganz einfach ändern, indem du oben auf den Namen der Tabelle klickst, die Maus gedrückt hältst und dann das Fenster einfach an die gewünschte Position verschiebst:

Tabellen können beliebig angeordnet werden

Um nun die Kundentabelle mit der Verkäufertabelle zu verbinden, klickst du in das Feld „Verkäufer“ in der Kundentabelle und ziehst dann dieses Feld nach unten auf das Feld „VKID“ in der Verkäufertabelle. Sobald beide Felder grün hervorgehoben sind, lässt du die Maus wieder los und die Beziehung wurde erstellt.

Beziehung per Drag&Drop erstellen

Fertige Beziehung zwischen Kunden und Verkäufer

Anschließend kannst du das Power Pivot-Fenster wieder schließen und zu Excel zurückkehren.

Jetzt lässt sich die Pivot-Tabelle auch nach Regionen auswerten, und das, obwohl es innerhalb der Rechnungstabelle eigentlich keinerlei Informationen zu den Verkäufern und Regionen gibt. Aber durch die Beziehungen zwischen allen vier Tabellen sind diese Einschränkungen aufgehoben:

PivotTable nach Regionen

Wie du jetzt sicherlich langsam erkennst, sind das Datenmodell und Power Pivot ziemlich mächtige Werkzeuge. In einem der nächsten Artikel werden wir uns ansehen, welche Auswertungen mit Power Pivot möglich sind, die eine normale Pivot-Tabelle nicht bietet.