Einführung in das Datenmodell und Power Pivot 18

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Tim Antworten abbrechen

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

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

  • Avatar-Foto
    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?

    • Avatar-Foto
      Martin Weiß

      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

    • Avatar-Foto
      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

  • Avatar-Foto
    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?

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Sabina Baxmann

    Hallo Martin,

    danke Dir auch nochmal für den super Artikel! Damit hast Du genau das beschrieben, was ich lange gesucht habe: der Import in das Datenmodell (ohne erst die vollständige Vorgehenswese und Raffinessen von Power Query zu studieren 😉

    Dazu aber nun 2 Fragen:
    1. Ich habe 2016 Excel Prof Plus und auch einen Reiter Power Pivot mit allem. Aber in Einfügen – Power Pivot gibt es nur die eine (alte) Auswahl. Also kein Dreieck und nix mit Power Pivot „aus dem Datenmodell“ (ich habe Dein Beispiel runtergeladen und genau Deine Anleitung nachvollzogen. Irgendeine Idee? 😉
    2. Das Datenmodell wird in der Exceltabelle gespeichert nicht? Wenn ich nun ein Dashboard bauen will aus ca. 20 Tabellen, wird damit die Datei doch sehr groß. Oder zieht sich das Datenmodell doch eigentlich nur Verknüpfungen auf die Originaltabellen…?

    Danke Dir ! und VG
    Sabina

    • Avatar-Foto
      Martin Weiß

      Hallo Sabina,

      in Excel 2016 ist der Weg offensichtlich noch etwas anders. Du wählst hier einfach „Einfügen | PivotTable“. Im dann folgenden Dialogfenster wählst du die Option „Externe Datenquelle verwenden“ und klickst dann auf die Schaltfläche „Verbindung auswählen…“
      Im Fenster mit den vorhandenen Verbindungen klickst du oben auf das Register „Tabellen“. Dort solltest Du an erster Stelle Das Datenmodell finden („Tabellen im Datenmodell der Arbeitsmappe“), das du dann öffnen kannst. Ab dann sollte der Weg wieder so wie im Artikel beschrieben funktionieren.

      Zu deiner zweiten Frage: Ja, das Datenmodell wird in der Exceltabelle gespeichert. Und ja, die Datei wird dadurch erheblich größer. Frag mich nicht, wie die Speicherverwaltung da genau funktioniert, aber die Tabellen werden vermutlich sowohl innerhalb von Excel als auch im Datenmodell gehalten, was den Speicherbedarf deutlich erhöht. Bei wirklich vielen Quelldaten bietet sich daher an, diese über Power Query zu importieren und von dort direkt ins Datenmodell zu laden.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Adrian

    Hallo Martin,

    Ich habe folgendes Problem:
    Ich habe 2 Dateien: A_Mitgliederdaten und B_Kasse

    In Datei A sind alle Stammdaten (Adressen, Mitgliedsbeitrag, Alter, IBAN etc.) zu den Mitgliedern erfasst. In einer zweiten Datei (B) sollen nun nur die für den Kassenwart wichtigen Informationen aus Datei A dargestellt werden (Ist über eine Power-Query-Abfrage gelöst. Soweit kein Problem.).
    In Datei B soll es nun aber möglich sein, dass der Kassenwart zusätzlich Eintragungen vornehmen kann (z.B. Zahlungseingänge der Mitglieder).

    Der 1. Ansatz war nun, an die durch die Abfrage erstellte Tabelle (B) einfach eine Spalte anzufügen. Die dort eingetragenen Daten sind aber nicht mit den restlichen Daten verknüpft. (Kommt ein Mitglied dazu, verrutschen alle Eintragungen um eine Zeile).

    Der 2. Ansatz war, eine Tabelle (Datei B) zu erstellen, in der die Vor- und Nachnamen der Mitglieder eingetragen sind (manuell/ohne Verknüpfung). Hier werden dann auch die Eintragungen (z.B. Zahlungseingänge) erfasst. Eine Abfrage führt dann diese Tabelle mit der Datei A zusammen. Klappt auch soweit.
    Problematisch wird es aber, wenn ein neues Mitglied hinzukommt, oder sich ein Nachname ändert. Dann muss diese Änderung in beiden Dateien erfolgen. Den Aufwand bzw. die Fehlerquelle würde ich gerne vermeiden.

    Gibt es eine Möglichkeit, an eine Abfrage zusätzlich Eintragungen zu binden?

    Ich hoffe ich habe mich halbwegs verständlich ausgedrückt.

    Vielen Dank für eine Rückmeldung,
    Adrian

    • Avatar-Foto
      Martin Weiß

      Hallo Adrian,

      das Problem ist vermutlich schon lösbar mit Power Query. Das Stichwort hier sind selbstreferenzierende Abfragen. Hierfür muss man die Abfragen so anlegen, dass zuerst die schon vorhandenen Daten (mit den zusätzlich ergänzten Eintragungen) nochmal ausgelesen werden und dann die neue Daten dazu geladen werden. Die genaue Vorgehensweise ist aber nicht mit ein, zwei Sätzen erklärt, vielleicht schreibe ich mal einen eigenen Artikel dazu.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Martin

    Hallo Martin,
    ich nutze Microsoft Excel 365 auf meinen MacBook, kann aber Power Pivot nicht aktivieren. Gibt es womöglich Add-Ins die ich mir istallieren kann und wo find ich diese?

  • Avatar-Foto
    Daniel Hildebrand

    Hallo Martin,
    vielen Dank für den hilfreichen Blog-Beitrag.

    Kann es sein, dass durch die Erstellung eines Datenmodells, ein berechnetes Feld von einer Zahl zu einem Textfeld wird?

    Mein Problem ist, dass ich ein interaktives Dashboard mit Pivot Tables erstellt habe. Die Pivot Tables haben dabei zwei Quelldatentabellen und somit funktionieren meine Datenschnitte nicht auf alle Pivot Tables in meinem Dashboard. (Ich brauche mindestens zwei Quelldatentabellen zum aktualisieren meiner Rohdaten. Später möchte ich weitere Quelldatentabellen meinem Datenmodell hinzufügen und das Dashboard stetig erweitern)

    Meine Überlegung war, dass ich ein Datenmodell erstelle und die Pivot Tables aus diesem Datenmodell erstelle. So möchte ich eine Tabelle als Rohdaten Quelle nutzen und in der zweiten Tabelle meine Berechnungsfelder hinzufügen. Am Ende möchte ich dann die Datenschnitte auf das Datenmodell anwenden. Ist es möglich Datenschnitte auf ein Datenmodell anzuwenden?

    Nun kann ich allerdings nicht mehr den Mittelwert von einem berechneten Feld in meiner Pivot Table auswählen. Ich bekomme nur die Rückmeldung „Wir können dieses Feld nicht mit ‚Mittelwert‘ zusammenfassen, da dies keine unterstützte Berechnung für Text-Datentypen ist.“
    Wenn ich allerdings im Datenmodell nachschaue, ist der Datentyp für das Feld eine Zahl.
    Ich hoffe das Problem ist bekannt oder ich habe lediglich einen Anwendungsfehler begangen. In beidem Fällen vorab vielen Dank für die Unterstützung!

    Liebe Grüße,
    Daniel

    • Avatar-Foto
      Martin Weiß

      Hallo Daniel,

      sobald mehrere Quelltabellen ins Spiel kommen, würde ich grundsätzlich das Datenmodell in Betracht ziehen. Auch bei Pivot-Tabellen aus dem Datenmodell lassen sich Datenschnitte nutzen. Was mich etwas irritiert: Du schreibst von einer separaten Tabelle mit Berechnungsfeldern. Ich kenne natürlich nicht deine Datenlandschaft, aber Berechnungen werden üblicherweise direkt im Datenmodell durchgeführt.

      Was die Fehlermeldung bei dem Mittelwert angeht:
      Die kommt mit hoher Wahrscheinlichkeit daher, dass irgendwo in den Daten Textwerte enthalten sind. Sobald auch nur ein einziges Textelement in der Spalte vorhanden ist, funktioniert eine solche Berechnung in einer Pivot-Tabelle nicht mehr, egal, was als Datentyp für die Spalte definiert wurde. Du musst dich also auf die Suche nach dem fehlerhaften Wert in den Daten machen.

      Schöne Grüße,
      Martin