Datenmodell in Excel mit Power Query und Power Pivot erstellen

Wenn Sie als Anwender Daten aus einer einzigen Datenquelle mit Power BI Desktop oder mit Power Pivot in Excel umfassend auswerten und analysieren möchten, sollten Sie das zugrunde liegende Datenmodell sorgfältig planen und erstellen. In diesem Tipp erfahren Sie, wie Sie ein solches Datenmodell, das auf einer einzigen Tabelle in einem Excel-Tabellenblatt basiert, mit Hilfe vom Power Query und Power Pivot aufbauen.

Ausgangsbeispiel

Die Erstellung des Datenmodells soll anhand einer Excel-Tabelle mit Artikelumsätzen gezeigt werden. Die Tabelle hat folgenden Aufbau:

Tabelle mit Artikelumsätzen

Die Daten können Sie zwar ohne Datenmodell mit einer Hilfe einer PivotTable auswerten. Wenn Sie die zusätzlichen Auswertungsmöglichkeiten von Power Pivot nutzen möchten, sollten Sie auch bei nur einer Tabelle als Datenbasis ein Datenmodell nach dem sogenannten Starschema aufbauen. Das Starschema, auch Sternenschema genannt, ist ein bevorzugtes Datenmodell für die Analyse von Daten in Power Pivot.

Datenmodell nach dem Starschema

Das Starschema hat in der Regel eine Faktentabelle und ein oder mehrere Dimensionstabellen. In der Faktentabelle befinden sich die auszuwertenden Zahlen sowie Schlüsselfelder, nach denen Sie diese Zahlen filtern und gruppieren möchten. In einer Dimensionstabelle befinden sich die Bezeichnungen für die Filter- und Gruppierungskriterien. Die Dimensionstabelle muss mindestens ein Feld aufweisen, in der alle Werte eindeutig sind. Dieses Feld dient als Verbindungsfeld zum passenden Schlüsselfeld in der Faktentabelle.

In unserem Beispiel soll das folgende Datenmodell erzeugt werden:

Geplantes Datenmodell mit Starschema

Die Tabelle Artikelumsatz ist in dem Datenmodell die Faktentabelle. Die Tabellen Artikel und Kalender sind die Dimensionstabellen, die mit der Faktentabelle in Verbindung stehen. Mit diesem Datenmodell können Sie die Artikelumsätze- und mengen nach Artikel und zeitraumbezogen nach Jahr, Monat, Wochentag und Datum analysieren.

Im Folgenden erfahren Sie, wie Sie ein solches Datenmodell in Excel erstellen.

Basistabelle mit Verkaufsdaten laden

Zunächst sollen die Daten aus der Excel-Arbeitsmappe mit Power Query geladen und bearbeitet werden.

Um das Datenmodell zu erzeugen, gehen Sie bitte folgendermaßen vor:

  1. Legen Sie in Excel eine neue Arbeitsmappe an.
  2. Klicken Sie im Menüband auf die Registerkarte Daten und dann auf die Schaltfläche Daten abrufen.
  3. Wählen Sie im aufklappenden Menü den Eintrag Aus Datei und dann den Eintrag Aus Arbeitsmappe.
  4. Suchen Sie im Explorer nach der Excel-Arbeitsmappe mit den auszuwertenden Daten.
  5. Klicken Sie auf die Schaltfläche Importieren.
  6. Nach kurzer Zeit erscheint ein Navigator, der Ihnen alle Tabellenblätter und dynamischen Tabellen in der gewählten Arbeitsmappe anzeigt.
  7. Markieren Sie im Navigator das Element mit den auszuwertenden Daten. Rechts wird Ihnen eine Vorschau auf die Daten angezeigt:
    Navigator in Power Query
  1. Klicken Sie auf die Schaltfläche Daten transformieren. Die Daten werden in Power Query geladen.
  2. Sollten sich in der Tabelle Spalten befinden, die Sie für Ihre Auswertungen nicht brauchen, sollten Sie sie zunächst entfernen. In unserem Beispiel ist dies nicht der Fall.
  3. Als nächsten Schritt soll diese Abfrage umbenannt werden, da Sie als Basis für zwei weitere, noch anzulegenden Abfragen dienen soll. Klappen Sie den Navigationsbereich Abfragen auf, in dem Sie auf die Schaltfläche mit dem rechten Pfeil klicken.
  4. Benennen Sie die Abfrage Artikelumsatz um, indem Sie die Abfrage markieren und mit der rechten Maustaste das Kontextmenü aufrufen, Klicken Sie auf Umbenennen. In unserem Beispiel soll die Abfrage Artikelumsatz_Basis heißen.

Artikeltabelle generieren

Im nächsten Schritt sollen aus der Tabelle mit den Artikelumsatzdaten die eindeutigen Artikel mit Artikelnummer und -bezeichnung in eine separate Tabelle transferiert werden. Gehen Sie folgendermaßen vor:

  1. Markieren Sie in Power Query die Abfrage Artikelumsatz_Basis und rufen Sie im Kontextmenü den Eintrag Verweis auf. Im Navigator erscheint eine zweite Abfrage namens Artikelumsatz_Basis (2). Nennen Sie diese Abfrage Artikel. Diese Abfrage enthält zunächst die gleichen Daten wie die die Abfrage Artikelumsatz.
  2. Um die eindeutigen Artikel aus dieser Tabelle herausziehen, wird die Tabelle nach den Spalten Artikel-Nr. und Bezeichnung gruppiert. Klicken Sie im Menüband von Power Query auf der Registerkarte Transformieren auf die Schaltfläche Gruppieren nach. Folgendes Dialogbild erscheint:
    Dialogbild Gruppieren nach in Power Query
  1. Aktivieren Sie in dem Dialogbild die Option Weitere.
  2. Wählen Sie als erstes Gruppierungskriterium Artikel-Nr. Klicken Sie danach auf Gruppierung hinzufügen und wählen Sie als zweites Kriterium Bezeichnung.
  3. Wählen Sie im Kombinationsfeld Vorgang den Eintrag Alle Zeilen. Klicken Sie auf OK.

Die Abfrage Artikel hat folgendes Aussehen:

Generierte Abfrage aus Gruppierung

Sie enthält alle in der Abfrage Artikelumsatz vorkommenden Artikelnummern mit deren Bezeichnungen. Die Spalte Anzahl wird nicht gebraucht. Markieren Sie diese Spalte und entfernen Sie sie aus der Abfrage.

Faktentabelle erzeugen

In der noch zu erzeugenden Faktentabelle soll mit Ausnahme der Spalte Bezeichnung alle Spalten enthalten sein, die sich auf in der Abfrage Artikelumsatz_Basis befinden. Die Spalte Bezeichnung sollten Sie nicht aus der Tabelle Artikelumsatz_Basis löschen, da sonst die Abfrage Artikel sich nicht mehr erzeugen und aktualisieren lässt. Gehen Sie stattdessen folgendermaßen vor:

  1. Markieren Sie die Abfrage Artikelumsatz_Basis und rufen Sie im Kontextmenü erneut den Eintrag Verweis auf. Im Navigator erscheint eine neue Abfrage namens Artikelumsatz_Basis (2). Nennen Sie diese neue Abfrage Artikelumsatz.
  2. Löschen Sie aus dieser Tabelle die Spalte Bezeichnung.

Abfragen ins Datenmodel laden

Sie haben alle in Power Query vorzunehmenden Transformationen abgeschlossen. Die Daten können Sie ins Datenmodell von Power Pivot übertragen werden.

  1. Klicken Sie in Power Query auf die die Registerkarte Datei und dann auf den Menüpunkt Schließen und laden in. Das folgende Dialogbild erscheint:
    Daten ins Datenmodel von Power Pivot importieren
  1. Aktivieren Sie die Option Nur Verbindung erstellen und das Kontrollkästchen Dem Datenmodell diese Daten hinzufügen. Klicken Sie auf OK.

Nach dem Import der Daten wird in Excel der Aufgabenbereich Abfragen und Verbindungen angezeigt. Er sieht in unserem Beispiel so aus:

Aufgabenbereich Abfragen und Verbindungen

Die Abfrage Artikelumsatz_Basis wird im Datenmodell nicht gebraucht. Daher soll diese Abfrage nur als Verbindung bestehen bleiben.

  1. Markieren Sie die Abfrage Artikelumsatz_Basis.
  2. Klicken Sie im Kontextmenü der Abfrage auf den Menüpunkt Laden in. Das Dialogbild Daten importieren erscheint wieder:
  3. Deaktivieren Sie das Kontrollkästchen Dem Datenmodell diese Daten hinzufügen. Klicken Sie auf OK.

Nach dieser Aktion befinden sich nur noch die Tabellen Artikel und Artikelumsatz im Datenmodell.

Datumstabelle hinzufügen

Für zeitraumbezogene Datenanalysen benötigen Sie im Datenmodell noch eine Datumstabelle. Diese Datumstabelle können Sie in Power Pivot automatisch erzeugen lassen. Die Datumstabelle soll für jedes Jahr, in dem Artikel verkauft wurden, alle Tage als Datum enthalten. Zu jedem Datum soll noch das Jahr, der Monat und der Wochentag ermittelt werden. Diese Datumstabelle können Sie folgendermaßen erzeugen:

  1. Klicken Sie in der Excel-Arbeitsmappe auf der Registerkarte Power Pivot in der Gruppe Datenmodell auf die Schaltfläche Verwalten. Das Fenster für Power Pivot wird angezeigt.
  2. Klicken Sie auf der Registerkarte Entwurf in der Gruppe Kalender auf die Schaltfläche Datumstabelle. IM aufklappenden Menü wählen Sie den Menüpunkt Neu.

Im Datenmodell wird eine neue Tabelle Kalender erzeugt. Sie hat folgenden Aufbau:

Datumstabelle in Power Pivot

Bei Bedarf können Sie die Spalte Date noch in Datum umbenennen und das Format dieser Spalte so ändern, dass die Uhrzeit nicht mehr angezeigt wird. Das Format ändern Sie, indem Sie die Spalte mit dem Datum markieren und dann auf der Registerkarte Start in der Gruppe Formatierung das Format 14.03.2001 wählen.

Beziehungen

Alle für die Datenanalyse benötigten Tabellen sind jetzt im Datenmodell vorhanden. Es fehlt noch die Herstellung und Prüfung der Beziehungen zwischen den Tabellen.

  1. Klicken Sie Im Fenster von Power Pivot auf der Registerkarte Start in der Gruppe Ansicht auf Diagrammansicht. Die Diagrammansicht dürfte so ähnlich aussehen:
    Datenmodel in Power Pivot Ohne Beziehungen
  1. Die Beziehungen zwischen den Tabellen sind noch nicht erstellt worden. Es gibt keine Verbindungslinien im Diagramm zu sehen. Markieren Sie mit der Maus in der Tabelle Artikel die Spalte Artikel-Nr. und ziehen Sie es auf das Feld Artikel-Nr. in der Tabelle Artikelumsatz.
  2. Danach ziehen Sie das Feld Datum in der Tabelle Kalender auf das gleichnamige Feld in der Tabelle Artikelumsatz.
  3. Ordnen Sie die Tabellen übersichtlich an.

Die Diagrammansicht hat danach folgendes Aussehen:

Geplantes Datenmodell mit Starschema

Das erzeugte Datenmodell entspricht dem anzustrebenden Starschema.

Wenn Sie jetzt noch die benötigte berechneten Felder bzw. Measures im Datenmodell anlegen, haben Sie gute Voraussetzungen für eine ausführliche Datenanalyse geschaffen.

Hinweis

In Power BI Desktop lässt sich das Datenmodell auf ähnliche Weise erzeugen. Die Vorgänge und Transformationen in Power Query sind identisch. Sie können in Power BI Desktop die Abfrage Artikelumsatz_Basis vom Laden ausschließen, indem Sie die im Kontextmenü der markierten Abfrage den Menüpunkt Laden aktvieren deaktivieren. Das automatische Anlegen einer Datumstabelle funktioniert anders. Sie benötigen dafür eine Formel zur Erzeugung einer Tabelle mit der DAX-Funktion CALENDARAUTO oder CALENDAR.

Michael Richter

Michael Richter

Autor

Ich unterstütze unsere Kunden in erster Linie durch Programmierung von Datenbankanwendungen und Zusatzmodulen von Microsoft Office. In Seminaren und Tipps auf unserer Blogseite gebe ich mein Wissen weiter.

2 Kommentare

  1. Hallo Michael,

    gibt es auch eine Übungsdatei?

    Antworten
    • Hallo Manfred,

      eine Übungsdatei können wir Ihnen nicht zur Verfügung stellen.

      Antworten

Einen Kommentar abschicken

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Pin It on Pinterest

Share This