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
Danach öffnet sich das eigentliche Beziehungsfenster, in dem die ersten beiden Tabellen miteinander verbunden werden sollen.
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
Als Ziel bestätigen wir den Vorgabewert „Neues Arbeitsblatt“.
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.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
ich versuche, mit Hilfe deiner Anleitung einen ganz ähnlichen Berg an Daten zu durchdringen. Es geht um sehr viele Teile in sehr vielen Stücklisten…
Das Konzept der miteinander verbundenen Tabellen ist klar und die Beziehungen sind hergestellt.
Aber irgendwie schaffe ich es nicht, selbst bei Verwendung Deiner Beispieldatei, die Daten korrekt zu verdichten: die gemeinsame Verwendung der Felder tblArtikel_Bezeichnung und tblRechnungen_Artikel im Bereich Zeilen führt in der Anzeige zu einer ausgeklappten Liste von jeweils ALLEN Bezeichnungen unter jedem Artikel.
Was mache ich hier falsch?
Schöne Grüße, Anton.
Hallo Anton,
das beschriebene Problem sollte in dem Moment verschwinden, wenn du ein Feld in den Wertebereich ziehst (also z.B. Menge oder Umsatz). Dann werden nur noch die relevanten Artikel mit den entsprechenden Bezeichnungen angezeigt.
Schöne Grüße,
Martin
Ciao Martin
Ich hatte das gleiche Problem und zur Lösung ein Summenaggregat im Berechnungsfeld gebildet, funktioniert perfekt!
LG
Alexis
Hi Alexis,
was genau meinst du?
Schöne Grüße,
Martin
Hallo Martin.
Ich hatte heute eine Idee und dachte mir, dass für eine Lösung mit Excel das Datenmodell hier bestimmt genau das Richtige ist, da meine Informationen aus vielen verschiedenen Tabellen kommen.
Beim erstellen der Beziehungen bin ich jedoch bereits ins Stocken geraten.
Es war mir nicht möglich Beziehungen herzustellen, wenn der Primary-Key der Datenbank aus 2 Feldern besteht, da ich in PowerPivot irgendwie immer nur eine auswählen kann.
Auch der Versuch zunächst eine Access-Datenbank zu erstellen mit den entsprechenden Beziehungen und diese dann mit PowerPivot ins Datenmodell zu importieren führte nur dazu, dass alle Beziehungen, die aus mehr als einem Feld bestanden wieder entfernt wurden und die Neuanlage wie oben erwähnt ja nicht geht.
Jetzt dachte ich mir, leg ich doch einfach eine neue Spalte in den einzelnen Tabellen an, die beide Primarykey-Spalten verkettet.
Das sah auf den ersten Blick auch aus, als ob dies als Lösung taugt, doch nun sehe ich zusätzlich, dass in der Originaldatenbank teilweise der Wert „A“ und der Wert „a“ verwendet wurde. Das scheint für die Erstellung von Beziehungen auch das gleiche zu sein, so dass Excel meckert, dass beide Tabellen mehrmals die gleichen werte enthalten 🙁
Hast du eine Idee, wie man 1. ggf. schöner zwei Felder zusammen für eine Beziehung festlegen kann und 2. eine Idee, wie man bei der Erstellung von Beziehungen zwischen Gros- und Kleinschreibung unterscheiden kann?
Danke+Gruß
Sebastian
Hallo Sebastian,
es ist richtig, in Power Pivot lassen sich Beziehungen zwischen zwei Tabellen nur mit einem einzigen Schlüsselfeld erstellen. Zwei oder mehr Schlüssel gehen nicht, stattdessen müssten dann diese Felder zu einem Feld zusammengeführt werden, so wie du es auch gemacht hast.
Und jetzt kommt das zweite Problem, das du auch beschrieben hast: In Power Query wird zwischen Groß- und Kleinschreibung unterschieden, in Power Pivot jedoch leider nicht. Imke Feldmann hat mal für längerer Zeit auf ihrem Blog eine mögliche Lösung dafür beschrieben, die über eine separate neu einzurichtende Schlüsselspalte führt:
Create a Dimension Table with Power Query: Avoid the case sensitivity bug
Vielleicht hilft das weiter.
Schöne Grüße,
Martin
Hallo Martin.
Danke für den schönen Tipp. Den schaue ich mir auf jeden Fall einmal genauer an.
Heute bin ich noch auf eine andere Idee gekommen, die ich erstmal versuchen werde.
Die Zeichenkette in Groß- und Kleinschreibung in einer neuen Spalte als Unicode ausgeben.
Dann sollten die Werte ebenfalls eindeutig werden.
Mal schauen. Danke+Gruß
Sebastian
Es hat geklappt, zumindest erstmal im Groben…
Ich habe meiner Tabelle eine neue Spalte hinzugefügt und folgende Formel verwendet:
=TEXTVERKETTEN(„-„;WAHR;TEXTKETTE(WENNFEHLER(CODE(TEIL([@[TYP_TYP]];{1.2.3};1));““));CODE([@[TYP_ART]]))
Ich habe nun eine Spalte mit ausschließlich eindeutigen Werten und somit einen verwendbaren Primärschlüssel für die Erstellung von Beziehungen.
Das Feld TYP_TYP in ein VARCHAR2(3 BYTE), hat also nur maximal 3 Zeichen.
Mit dem Teil CODE(TEIL([@[TYP_TYP]];{1.2.3};1)) gebe ich als Array die ersten 3 Zeichen als Unicode aus, der zwischen Groß- und Kleinschreibung unterscheidet (z.B. A=65 und a=97).
Da bei allen Zeilen mit nur einem oder zwei Zeichen Fehler auftreten habe ich dies noch mit einer WENNFEHLER-Funktion umschlossen, damit diese Felder dann einfach leer sind.
Diese Formel umschließe ich wiederum mit einer Textkette, damit das Ergebnis nicht als Array ausgegeben wird.
Abschließend Kommt noch die äußerste Funktion drum herum mit TEXTVERKETTEN, damit ich mit einem Trennzeichen sauber die Felder TYP_TYP und TYP_ART getrennt habe.
Zugegeben, es ist nicht ganz naheliegend und auch noch nicht optimal, aber es funktioniert. Jetzt muss ich mich die Tage mal damit beschäftigen, wie ich das alles bereits in Power Query bzw. Power Pivot mache, da ich die Tabelle eigentlich nicht in die Exceldatei laden möchte.
Danke+Gruß
Sebastian
Hallo Sebastian,
danke für diese Lösung, vielleicht hilft sie dem einen oder anderen Leser, der vor einem ähnlichen Problem steht.
Schöne Grüße,
Martin