Power Pivot für die DatenanalyseSo nutzen Sie Power Pivot für die Datenanalyse

Nachdem Sie die Grundlagen von Power Pivot kennengelernt und das Add-In aktiviert haben, steigen Sie nun in die Praxis der Datenanalyse ein. Sie bauen ein vollständiges, einfaches Power-Pivot-Datenmodell auf – mit verknüpften Tabellen, einer berechneten Spalte und einer Pivot-Auswertung.

Ziel dieser Anleitung

Sie lernen, wie Sie:

• zwei Tabellen ins Power-Pivot-Modell laden
• eine Beziehung zwischen diesen Tabellen definieren
• eine Kennzahl mit einer benutzerdefinierten Spalte erstellen
• eine PivotTable auf Basis des Modells aufbauen

Das Beispiel orientiert sich an einem klassischen Verkaufsszenario, bestehend aus einer:

• Faktentabelle mit Bestellungen: tbl_Bestellungen
• Dimensionstabelle mit Produktinformationen: tbl_Produkte

Hinweis: In einer Faktentabelle befinden sich Daten zu unterschiedlichen Ereignissen. Die Daten sind also dynamisch und ändern sich immer wieder; im Beispiel durch jede neue Bestellung. Eine Dimensionstabelle beschreibt ein Objekt anhand von Merkmalen, die eher statisch sind, sich also nur gelegentlich ändern; im Beispiel ist das Objekt ein Produkt.

1. Überblick über die Datentabellen

Die Tabelle tbl_Bestellungen enthält die verkauften Mengen, Preise und Produktzuordnungen.

Die Tabelle tbl_Produkte ergänzt zusätzliche Informationen zu jedem Produkt.

2. Tabellen in Excel als intelligente Tabellen formatieren

Sie übernehmen diese Daten zunächst in das Datenmodell.

Obwohl Power Pivot eine eigene Möglichkeit besitzt, um Daten in das Datenmodell zu integrieren, sollten Sie unbedingt den Weg über Power Query gehen. Power Query bietet Ihnen viel mehr Möglichkeiten als das Feature in Power Pivot.

Damit Sie die Daten in das Datenmodell über Power Query laden können, müssen Sie diese in eine intelligente Tabelle umwandeln.

Markieren Sie jeweils die Inhalte in den Tabellenblättern tbl_Bestellungen und tbl_Produkte.

Drücken Sie Strg + T, um eine intelligente Tabelle zu erstellen.

Es öffnet sich das Dialogfeld Tabelle erstellen. Vergewissern Sie sich, dass das Kontrollkästchen Tabelle hat Überschriften aktiviert ist, da in der ersten Zeile der Tabelle Spaltenbeschriftungen stehen. Bestätigen Sie Ihre Einstellungen, indem Sie das Dialogfeld durch Klick auf OK schließen.

Der Bereich wird daraufhin in eine intelligente Tabelle umgewandelt. Klicken Sie auf eine Zelle in der intelligenten Tabelle, so dass diese aktiviert ist. Vergeben Sie in der Registerkarte Tabellenentwurf oben links einen Tabellennamen; zum Beispiel tbl_Bestellungen.

Wiederholen Sie diese Schritte nun für die zweite Tabelle mit den Produktdaten. Die Tabellen sind dann bereit für den Import ins Datenmodell.

3. Daten in Power Query importieren

Klicken Sie mit der linken Maustaste in die Tabelle  tbl_Bestellungen und aktivieren Sie im Menüband die Befehlsfolge Registerkarte Daten > Befehlsgruppe Daten abrufen und transformieren > Befehl Aus Tabelle/Bereich.

Power Query öffnet sich und die Tabelle wird in den Editor geladen.

Überprüfen Sie die Datentypen. Sie können diese am linken Rand in der jeweiligen Spaltenüberschrift erkennen. 123 bedeutet Ganze Zahl, 1.2 ist das Symbol für Dezimalzahl und ABC steht für den Datentyp Text.

Sie können den Datentyp verändern, indem Sie mit der linken Maustaste auf das Symbol in der jeweiligen Spalte klicken und dann aus der Liste den benötigten Datentyp auswählen.

4. Power-Query-Daten ins Datenmodell laden

Wenn keine Änderungen mehr nötig sind, dann klicken Sie im Menüband auf die Befehlsfolge Start > Schließen & laden > Schließen & laden in.

Es öffnet sich das Dialogfeld Daten importieren. Wählen Sie hier die folgende Option und das entsprechende Kontrollkästchen aus:

• Nur Verbindung erstellen
• Daten dem Datenmodell hinzufügen

Bestätigen Sie Ihre Einstellungen, indem Sie das Dialogfeld durch Klick auf OK schließen.

Wiederholen Sie diese Schritte für die Tabelle mit den Produktdaten. Beide Tabellen befinden sich nun im Excel-Datenmodell, also in Power Pivot.

5. Beziehungen im Power-Pivot-Datenmodell definieren

Da sich beide Tabellen nun im Datenmodell (Power Pivot) befinden, können Sie mit Power Pivot starten. Klicken Sie im Menüband die Befehlsfolge Registerkarte Power Pivot > Befehlsgruppe Datenmodell > Befehl Verwalten.

Der Power Pivot-Editor wird geöffnet. Sie sehen die Daten der Tabellen dargestellt. Unten links sehen Sie – analog zu den Tabellenblättern in Excel – ein Register mit den unterschiedlichen Tabellen, die sich im Datenmodell befinden.

In diesem Beispiel sind dies die beiden Tabellen tbl_Bestellungen und tbl_Produkte.

Aktivieren Sie nun die Diagrammansicht im Power-Pivot-Add-in, indem Sie im Menüband die Befehlsfolge Start > Diagrammansicht aktivieren.

In der Diagrammansicht bekommen Sie die im Datenmodell enthaltenen Tabellen visuell dargestellt. Sie können hier auf einfache Art und Weise Beziehungen zwischen diesen Tabellen herstellen und visuell darstellen.

Ziehen Sie bei gedrückt gehaltener linker Maustaste das Feld ProduktID von tbl_Produkte auf tbl_Bestellungen.

Es entsteht eine 1:n-Beziehung. Denn: Ein Produkt kommt in der tbl_Produkte genau einmal (1) vor, kann aber in mehreren Bestellungen (n) vorkommen. Die Richtung und der Verbindungstyp (1:* oder n:m) werden grafisch in der Diagrammansicht dargestellt.

Die beiden Tabellen sind jetzt miteinander verbunden. Analog zum SVERWEIS() kann jetzt auf die fehlenden Produktdaten in der Tabelle tbl_Bestellungen automatisch durch die Verknüpfung zugegriffen werden.

6. Berechnete Spalte im Datenmodell erstellen

Da der Umsatz aus einer Bestellung in den Daten nicht ersichtlich ist, können Sie für diesen Wert eine berechnete Spalte im Datenmodell integrieren. Eine berechnete Spalte ist zwar kein DAX-Measure, aber eine einfache Möglichkeit, um fehlende Werte zu ergänzen.

Berechnete Spalten in Power Pivot sind zusätzliche Spalten, die Sie mithilfe der Formelsprache DAX direkt in einer Tabelle erstellen. Sie berechnen sich für jede Zeile der Tabelle – ähnlich wie eine Excel-Formel in einer Zelle.

Typischer Einsatz von berechneten Spalten:

• Berechnung von Zwischenergebnissen wie Umsatz = Einzelpreis × Menge
• Erzeugen von Hilfsspalten (zum Beispiel Kategorienzuordnungen, Monatsnamen, Flags)

Um nun eine berechnete Spalte für den Umsatz zu erstellen, aktivieren Sie im Power-Pivot-Editor die Tabelle tbl_Bestellungen und klicken am rechten Rand auf die Spaltenüberschrift Spalte hinzufügen.

Doppelklicken Sie auf die Spaltenüberschrift und erfassen Sie jetzt als Spaltenüberschrift den Text Umsatz. Bestätigen Sie Ihre Eingabe, indem Sie die Enter-Taste drücken.

In der Bearbeitungsleiste wird automatisch ein = Zeichen angezeigt. Wie im klassischen Excel können Sie jetzt hier auf einfache Art und Weise eine Formel erfassen, die auf jede Zeile in der Tabelle angewendet wird.

• Klicken Sie mit der linken Maustaste in der Spalte Menge auf die erste Zeile und erfassen Sie anschließend das *-Zeichen.
• Anschließend klicken Sie nochmal in der Spalte Einzelpreis auf die erste Zeile.
• Die entsprechenden Feldnamen werden in die Formel übernommen.
• Bestätigen Sie Ihre Eingabe, indem Sie die Enter-Taste drücken.

Daraufhin wird in jeder Zeile der Tabelle der Umsatz über eine berechnete Spalte dargestellt.

Bei berechneten Spalten ist Folgendes zu beachten:

• Berechnete Spalten werden dauerhaft im Datenmodell gespeichert.
• Sie erhöhen den Speicherbedarf, vor allem bei großen Datenmengen.
• Sie lassen sich in PivotTables genau wie normale Felder verwenden; zum Beispiel für Gruppierungen oder Filter.
• Ideal, wenn Sie pro Datensatz einen zusätzlichen Wert benötigen, der später analysiert oder weiterverarbeitet wird.

7. PivotTable auf Basis des Datenmodells erstellen

Jetzt können Sie die verknüpften Tabellen im Datenmodell mit einer „klassischen Pivot-Tabelle“ auswerten. Kehren Sie wieder nach Excel zurück, indem Sie in der Symbolleiste oben links auf das Excel-Symbol klicken.

Klicken Sie Menüband jetzt auf die Befehlsfolge Registerkarte Einfügen > Befehlsgruppe PivotTables > Befehl PivotTable > Befehl Aus dem Datenmodell.

Es öffnet sich das Dialogfeld PivotTable aus dem Datenmodell. Aktivieren Sie hier das Optionsfeld Neues Arbeitsblatt und bestätigen Sie Ihre Einstellungen, indem Sie auf die Schaltfläche OK klicken.

Sie bekommen wie gewohnt den Berichtsbereich für die Pivot-Tabelle und die Feldliste angezeigt.

Klicken Sie in der Feldliste auf Aktiv, damit nur die relevanten Datenquellen und nicht alle Tabellen oder sonstige Abfragen als Quelle für die Pivot Tabelle angezeigt werden. Im Beispiel sehen Sie dann die beiden Tabellen tbl_Bestellungen und tbl_Produkte.

Dadurch, dass die Tabellen im Datenmodell verknüpft sind, können Sie jetzt die benötigten Felder aus beiden Tabellen in die Pivot-Tabelle ziehen.

Sie können sich nun beispielsweise die Umsätze pro Produkt anzeigen lassen.

• Den Produktnamen finden Sie in der Tabelle tbl_Produkte.
• Den Umsatz in der Tabelle tbl_Bestellungen.

Ziehen Sie per Drag-and-drop das Feld Produktname in die Zeilen und das Feld Umsatz in Werte.

Fertig: Sie haben Ihre erste Power Pivot auf Grundlage des Datenmodells erstellt.

Das Beispiel zeigt: Mit wenigen Klicks können Sie alle im Datenmodell verknüpften Daten miteinander kombinieren und für Ihre Datenanalyse verwenden. Sie erstellen schnell und einfach Pivot-Tabellen und schaffen damit eine flexible Informationsbasis für Ihre Auswertungen und Entscheidungen.

Fazit

Mit dieser einfachen Anleitung zur Datenanalyse mit Power Pivot haben Sie:

• ein einfaches Datenmodell aufgebaut
• Tabellen sinnvoll verknüpft
• eine Spalte dem Datenmodell hinzugefügt
• eine Pivot-Auswertung aufgebaut