Einführung in das Datenmodell und Power Pivot 10

Vom Datenmodell kann man auch in ganz "normalen" Pivot-Tabellen profitieren
 

Pivot-Tabellen können das Leben eines Excel-Anwenders ziemlich vereinfachen: Anstatt mit unzähligen fehleranfälligen Formeln eine Auswertungstabelle zusammenzuschustern, wertet der clevere Anwender sein Daten blitzschnell mit einer Pivot-Tabelle aus.

Es geht aber noch besser: Power Pivot und das zugrunde liegenden Datenmodell lassen „normale“ Pivot-Tabellen noch vor Neid erblassen. Der heutige Beitrag ist ein Auftakt für eine lose Artikelserie rund um Power Pivot. Was es mit dem ominösen Datenmodell auf sich hat, das erfährst du in der heutigen Einführung.

Wenn du jetzt sagst, dass du weder Power Pivot noch ein Datenmodell brauchst und dir normale Pivot-Tabellen ausreichen: Lies trotzdem weiter. Du wirst sehen, dass du trotzdem davon profitieren kannst, selbst wenn du das Datenmodell nie zu Gesicht bekommst!

Und los geht’s.

Ein Einstieg in das Datenmodell

Das Datenmodell wurde mit der Excel-Version 2013 eingeführt. Damit wurde es möglich, Beziehungen zwischen Tabellen herzustellen, ähnlich wie man es aus relationalen Datenbanken kennt. Dieses Konzept dient einer effizienten Daten- und Speicherverwaltung, denn so müssen nicht immer sämtliche Informationen in einer einzigen großen Tabelle gespeichert werden.

In einer Tabelle, welche beispielsweise alle gestellten Rechnungen enthält, müssen nicht zu jeder einzelnen Rechnung auch die vollständigen Adressen der Kunden oder alle detaillierten Artikelinformationen gespeichert werden. Es reicht, wenn jede Zeile die Kundennummer und die Artikelnummer enthält. Über diese lassen sich dann die nötigen Informationen aus den separaten Kunden- und Artikeltabellen holen.

Man spricht häufig von Bewegungsdaten (Rechnungen) auf der einen Seite und Stammdaten (Kunden, Artikel) auf der anderen. Oder in der Datenbanksprache von Faktentabellen (Rechnungen) und Dimensionstabellen (Kunden, Artikel), die über sogenannte Schlüsselfelder miteinander verbunden sind (Kundennummer, Artikelnummer).

Beziehungen zwischen Tabellen

Beziehungen zwischen Tabellen

Die Artikelnummer ist der Schlüssel, über den die beiden Tabellen miteinander verbunden werden. Dabei entsteht eine sogenannte 1:N-Beziehung, da eine Artikelnummer in der Artikelstammtabelle jeweils nur ein einziges Mal vorkommt, in der Rechnungstabelle naturgemäß jedoch sehr häufig (N-mal). Ähnlich verhält es sich mit den Kundennummern.

Genug der Theorie, kommen wir zurück zum Datenmodell. Dieses liegt in einem speziellen Speicherbereich im Hintergrund und ist für den Excel-Anwender erst einmal gar nicht sichtbar. Genauer gesagt, wird es erst angelegt, wenn man bewusst Daten dort hineinlädt.

  • Wo findet man jetzt das Datenmodell?
  • Wie bekommt man seine Daten dorthinein?
  • Und warum sollte man das überhaupt wollen, wenn man vielleicht nur mit einer einzigen Tabelle arbeiten muss?

Power Pivot aktivieren

Zunächst müssen wir sicherstellen, dass wir Power-Pivot überhaupt nutzen können. Es handelt sich dabei um ein Add-In, das seit Excel 2013 in vielen Excel-Versionen enthalten ist, aber möglicherweise noch aktiviert werden muss. Anwender von Excel 2010 müssen das Add-In erst noch bei Microsoft herunterladen und installieren.

Wo ist Power Pivot enthalten?
Leider ist Microsoft etwas eigenwillig, was die Verfügbarkeit von Power Pivot angeht. Das Add-In ist zwar in vielen Excel-Versionen seit 2013 enthalten, aber eben doch nicht in allen. Eine genaue Übersicht findest du auf dieser Microsoft-Seite (vielen Dank an den Leser Joachim Kromm für diesen Hinweis!)

Zum Aktivieren öffnest du die Excel-Optionen. In der Kategorie „Add-Ins“ wählst du in dem Dropdown-Feld den Eintrag „COM-Add-Ins“ aus und klickst dann auf „Los…“

Excel-Optionen und COM-Add-Ins

Excel-Optionen und COM-Add-Ins


Im nächsten Fenster werden alle verfügbaren Add-Ins aufgelistet. Setze hier den Haken vor „Microsoft Power Pivot for Excel“ und bestätige die Auswahl mit OK.
Power Pivot aktivieren

Power Pivot aktivieren


Danach solltest du im Menüband einen neuen Eintrag vorfinden:
Ein neuer Eintrag im Menüband

Ein neuer Eintrag im Menüband

Das war’s dann auch schon.

3 Wege, um das Datenmodell zu befüllen

Möglichkeit 1
Wenn du schon mit Power Query gearbeitet hast (was ich dir sehr ans Herz legen möchte), dann ist dir beim Laden der Abfrageergebnisse zurück nach Excel vielleicht schon einmal das kleine Häkchen aufgefallen:

Abfrage ins Datenmodell laden

Abfrage ins Datenmodell laden

Setzt man hier das Häkchen, dann werden die Daten unabhängig von den anderen Optionen auf jeden Fall im Hintergrund auch in das Datenmodell geladen. Üblicherweise wählt man in diesem Zusammenhang auch die Option „Nur Verbindung erstellen“, da man die Ergebnisse ja nicht noch zusätzlich in ein Tabellenblatt laden möchte.

Möglichkeit 2
Wenn man in Excel eine formatierte Tabelle liegen hat, dann kann man diese direkt aus dem Arbeitsblatt in das Datenmodell einladen, ohne den Umweg über Power Query gehen zu müssen. Einfach die aktive Zelle irgendwo innerhalb der Tabelle platzieren und dann das Menü „Power Pivot | Zu Datenmodell hinzufügen“ wählen:

Formatierte Tabelle direkt zum Datenmodell hinzufügen

Formatierte Tabelle direkt zum Datenmodell hinzufügen

Möglichkeit 3
Die letzte Variante führt über die Power-Pivot-Oberfläche. Dort lassen sich – ähnlich wie in Power Query – externe Daten direkt und ohne Umwege ins Datenmodell einladen. Dorthin gelangt man über das Menü „Power Pivot | Verwalten“:

Das Datenmodell verwalten

Das Datenmodell verwalten

Wenn man bisher noch keine Daten im Datenmodell geladen hat, sieht man danach nur ein leeres Editor-Fenster, in dem man die Möglichkeit hat, externe Daten abzurufen:

Externe Daten direkt im Datenmodell abrufen

Externe Daten direkt im Datenmodell abrufen

Dieser Weg ist aber nur dann zu empfehlen, wenn die Daten schon optimal strukturiert und bereinigt in den externen Quellen vorliegen, zum Beispiel in einer Datenbank. In den allermeisten Fällen ist das nicht der Fall, häufig möchte man die Daten zuvor noch filtern, bereinigen oder umstrukturieren. Daher empfiehlt sich ganz oft der Umweg über Power Query (siehe oben, Möglichkeit 1).

Ok, und was habe ich nun davon?

Jetzt wo klar ist, wie man Daten ins Datenmodell bekommt, wollen wir uns gleich einmal den praktischen Nutzen davon ansehen. Dazu habe ich eine formatierte Tabelle mit Rechnungsinformationen vorbereitet, die ich über eine Pivot-Tabelle auswerten möchte (die Beispieldatei kannst du dir hier herunterladen).

Beispieltabelle mit Rechnungen

Beispieltabelle mit Rechnungen

Folgendes möchte ich wissen:
Wie viele verschiedene Kunden haben in jedem Monat etwas gekauft

Eine scheinbar einfache Frage, die sich jedoch mit einer normalen Pivot-Tabelle nicht beantworten lässt. Denn das Schlüsselwort lautet hier „verschiedene“ Kunden. Mit einer regulären Pivot-Tabelle kann man zwar die Anzahl der Kunden ermitteln. Aber das bedeutet tatsächlich, dass sämtliche Rechnungszeilen gezählt werden, ganz egal, wie häufig ein und derselbe Kunde auftaucht:

Pivot-Tabelle liefert nur die Gesamtanzahl

Pivot-Tabelle liefert nur die Gesamtanzahl

Jetzt schlägt die Stunde von Power Pivot!

Ich lade also die formatierte Tabelle direkt ins Datenmodell („Power Pivot | Zu Datenmodell hinzufügen“), die Tabelle erscheint anschließend im Power Pivot-Fenster:

Quelltabelle wird ins Datenmodell geladen

Quelltabelle wird ins Datenmodell geladen

Für unseren Zweck gibt es hier nichts weiter zu tun, daher können wir dieses Fenster direkt wieder schließen. Die Daten bleiben trotzdem im Hintergrund im Datenmodell erhalten. Und nun erstellen wir eine neue Pivot-Tabelle, dieses Mal jedoch aus dem Datenmodell:

Pivot-Tabelle aus dem Datenmodell erstellen

Pivot-Tabelle aus dem Datenmodell erstellen

Zum Vergleich lasse ich auch hier die normale Anzahl an Kunden pro Monat berechnen. Dann ziehe ich die Kundennummer ein zweites Mal in den Wertebereich und ändere die Zusammenfassung in die nun vorhandene Funktion „Diskrete Anzahl“:

Diskrete Anzahl

Diskrete Anzahl

Herzlichen Glückwunsch, damit hast du deine erste Power-Pivot-Tabelle erstellt!

Die Funktion „Diskrete Anzahl“ am Ende der Auswahlliste wird nur bei Pivot-Tabellen aus dem Datenmodell angeboten und liefert genau das gewünschte Ergebnis zu unserer Fragestellung.

Das war natürlich nur ein klitzekleiner Ausschnitt dessen, was Power Pivot und das Datenmodell zu bieten haben. Aber wie du gesehen hast, kann man selbst dann Nutzen daraus ziehen, wenn man sich mit dem Datenmodell gar nicht näher beschäftigt hat und nur eine einfache Pivot-Tabelle erstellen möchte.

Im nächsten Artikel werde ich ein paar weitere Beispiele vorstellen, wie man über das Datenmodell mehr aus Pivot-Tabellen herausholen kann.

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.

10 Gedanken zu “Einführung in das Datenmodell und Power Pivot

  • Tim

    Das sieht sehr interessant aus und könnte für mich Access möglicherweise obsolet machen. Aber ich habe ein wenig Sorgen, dass ich Daten „verlieren“ könnte, wenn die Quelldateien nicht immer im gleichen Verzeichnis liegen, weil z.B. verschieden Personen damit arbeiten müssen und sich Dateien hin- und herschicken. Kann man die Pfade relativ restalten, oder sind die immer absolut? Oder anders gesagt:
    Wenn ich z.B. ein Verzeichnis „Projekt xy“ habe mit zwei Unterordnern „Daten“ und „Auswertung“, könnten dann Dateien aus dem Auswertungsordner via „..\Daten“ auf die Datendateien zugreifen, oder muss der komplette Pfad angegeben sein?

    • Martin Weiß Autor des Beitrags

      Hallo Tim,

      ich nehme an, die spielst auf die Verweise zu den Quelldateien in den Power Query-Abfragen an. Hier ist es meines Wissens tatsächlich so, dass innerhalb der Abfragen nur absolute Pfade möglich sind. Das lässt sich aber bis zu einem gewissen Grad umgehen, indem man die Pfadangaben innerhalb einer Excel-Zelle im Arbeitsblatt hinterlegt und diese Zelle sozusagen als Parameter für die Quelle in den anderen Abfragen in Power Query einliest.

      So lässt sich beispielsweise über =ZELLE(„dateiname“) der vollständige Pfad und Dateiname der aktuellen Arbeitsmappe auslesen. Mit etwas Phantasie und Geschick kann man das benutzen, um relative Pfade zu anderen Datenquellen aufzubauen. Zumindest dann, wenn sich diese anderen Dateien in einem definierten und vorhersehbaren Verzeichnis befinden. Aber zugegeben, das ist auch keine wasserdichte Angelegenheit.

      Schöne Grüße,
      Martin

    • Carola Lück

      Ich denke nicht, dass Access damit überflüssig wird.
      Excel kann bislang nur „einfache“ Beziehungen erstellen, ohne referentielle Integrität.
      Sowas wie 1:n ist meines Wissens bisher nur im Datenbankprogramm möglich.
      Excellente Grüße
      Carola

  • Andreas Esper

    Hallo Herr Weiß,
    danke für die gute Beschreibung. Ich wollte zum Ausprobieren das Plugin Power-Pivot aktivieren, aber es wurde mir in meinem Excel 2016 bei den COM-Add-Ins nicht angezeigt. Kann es sein, dass ich das bei der Installation des Office-Paketes vergessen habe und es erst noch nach-installieren muss?

  • Carola Lück

    Ich bin mit dem Datenmodell und PowerPivot in Excel noch etwas am Anfang, Pivottabellen unterrichte ich selbst seit Jahren.
    Bisher geht meine Erfahrung dahin, dass es nicht nur Vorteile beim Aktivieren des Datenmodells gibt.
    Einige Aktionen sind dann in den Pivot-Tabellen nicht mehr möglich (ich glaube Gruppierungen)
    Dafür bekommen wir allerdings die diskrete Anzahl.
    Ich verwende bei Problemen dann unabhängige Pivottabellen – mit dem alten Pivotassistenten erstellt.
    Leider blicke ich beim Datenmodell noch nicht soweit durch, um festzustellen, ob sich dadurch jetzt der alte Pivotassistent erledigt.
    Bei einigen Dingen sagt mir auch die Microsoft-Hilfe bisher: alter Assi
    Viele Grüße und Danke für immer wieder neuen Input
    Carola

    • Martin Weiß Autor des Beitrags

      Hallo Carola,

      es stimmt in der Tat, dass zum Beispiel Gruppierungen nicht mehr möglich sind, wenn die Pivot aus dem Datenmodell erstellt wurde. Die wahren Stärken spielt das Datenmodell/Power Pivot aus, wenn es an die Berechnungen (Stichwort Measures) geht. Eigene Berechnungen sind in normalen Pivots ja mit berechneten Feldern/Elementen nur sehr eingeschränkt möglich und es werden dann unter Umständen auch noch falsche/irreführende Ergebnisse geliefert. Mit den Möglichkeiten, die Power Pivot mit hier mit der Sprache DAX bietet, tun sich ganz neue Welten auf.

      Schöne Grüße,
      Martin