Sie können das Video nicht sehen, weil Sie der Anzeige von Youtube nicht zugestimmt haben.
Die Einstellungen kann in der Datenschutzerklärung geändert werdeb.
Die Ausgabe von Youtube-Videos kann auch direkt hier erlaubt werden.
Über CUBE-Funktionen können Werte aus einem Datenwürfel abgerufen werden. Aber was ist denn eigentlich ein Datenwürfel? Diese Frage und vor allen Dingen, wie man die Daten aus einem Würfel mithilfe von Excel-Formeln ins Arbeitsblatt bringen kann, möchte ich in diesem Blogbeitrag beantworten.
Was ist ein Datenwürfel (Cube) und wie erzeugt man ihn mit Excel?
Fangen wir an mit folgender Frage: Was ist ein Datenwürfel? Ein Datenwürfel ist erst mal nur ein gedankliches Konstrukt. Es handelt sich nicht um einen echten Würfel, sondern um miteinander verbundene Tabellen.
Schauen Sie sich das folgende Beispiel an:
Die Tabelle Bestellungen stellt die Faktentabelle dar. Sie enthält die auszuwerten Spalte, nämlich die Betragsspalte. Die Faktentabelle ist in der Regel die Tabelle mit den meisten Datensätzen.
Die Tabellen Kunden, Personal und Kalender stellen die Dimensionstabellen dar. Diese Tabellen enthalten in der Regel wesentlich weniger Datensätze als die Faktentabelle. Die Dimensionstabellen sind über Schlüssel-Fremdschlüssel-Beziehungen mit der Faktentabelle verbunden.
Es existiert darüber hinaus ein berechneter Gesamtumsatz, der ermittelt wird, indem die Beträge aller Bestellung aufaddiert werden. Diese Kennzahl ist ein sogenanntes Measure.
Den Datenwürfel, den Sie in diesem Beispiel sehen, habe ich erzeugt, indem ich die Daten in das Power Pivot-Datenmodell von Microsoft Excel 2016 geladen habe. Wie Sie Daten ins Datenmodell laden, möchte ich in diesem Beitrag nicht behandeln. Das würde den Rahmen sprengen.
Den Gesamtumsatz habe ich mit folgender Formel im Datenmodell berechnet:
=SUM(Bestellungen[Betrag])
Bei dieser Formel handelt es sich um ein sogenanntes DAX-Measure (DAX = Data Analysis Expressions, Measure = Maßzahl).
Warum heißt das ganze jetzt Würfel? Denken Sie mal an die Zauberwürfel aus den siebziger und achtziger Jahren. Je nachdem wie Sie die Teile des Würfels drehen, sieht die Oberfläche anders aus. Der Würfel zeigt ein anderes Bild. Und ähnlich ist das auch bei dem Datenwürfel.
Es gibt neben Excel natürlich auch andere Programme, welche in der Lage sind Datenwürfel zu erzeugen, so zum Beispiel der Microsoft SQL Server.
Den Würfel „drehen"
Mit den Cube-Funktionen, die wir uns gleich anschauen, kann der Würfel so „gedreht" werden, dass nur bestimmte Datensätze der Faktentabelle betrachtet werden. Auch hier ist „drehen" natürlich nicht wörtlich gemeint. Vielmehr werden aus der Faktentabelle nur die Datensätze herausgefiltert, für die der Umsatz ausgegeben werden soll.
In unserem Beispiel sind das alle Bestellungen des Kunden Berglunds snabbköp, die der Mitarbeiter Andrew Fuller bearbeitet hat.
Die Funktion CUBEELEMENT()
Wir holen uns aus dem Datenmodell die Elemente, für die wir den Umsatz der Bestellung ermitteln wollen. Das ist zum einen der Mitarbeiter Andrew Fuller und zum anderen der Kunde Berglunds snabbköp.
Den Mitarbeiter möchte ich in Zelle C8 ausgeben. Daher schreibe ich dort folgende Formel:
=CUBEELEMENT("ThisWorkbookDataModel";"[Personal].[Nachname].[All].[Fuller]")
Das 1. Argument ist die Verbindung ins Datenmodell. Sie lautet in Excel 2013 und Excel 2016 ThisWorkbookDataModel (in Excel 2010 PowerPivot Data). Beachten Sie, dass der Wert in Anführungszeichen gesetzt werden muss. Sobald sie das öffnende Anführungszeichen eingeben, schlägt Ihnen Excel den Namen der Verbindung vor.
Das 2. Argument ist der Zugriff auf den Mitarbeiter. Wir greifen auf die Dimension Personal zu, dann auf die Spalte Nachname, verschaffen uns über den Operator ALL Zugriff auf alle Werte in dieser Spalte, und wählen dann gezielt den Wert Fuller aus. Die Eingabe dieses Arguments ist recht simpel, tippen Sie einfach die Anführungszeichen, anschließend schlägt ihnen Excel die Tabellen vor. Wählen Sie eine Tabelle mithilfe der Tabulatortaste aus und geben Sie dann den Punkt ein. Excel schlägt ihnen nun die Spalten der Tabelle vor. So „hangeln" Sie sich bis dann zum Zielwert durch. Wichtig ist, dass Sie am Schluss wieder die Anführungszeichen setzen.
In Zelle C9 gebe ich jetzt den Kunden aus. Die Formel lautet:
=CUBEELEMENT("ThisWorkbookDataModel";"[Kunden].[Firma].[All].[Berglunds snabbköp]")
Die Funktion CUBEWERT()
Mit der Funktion CUBEWERT() holen wir uns den Umsatz. Dieser liegt im wie oben beschrieben im Datenmodell als Measure vor.
=CUBEWERT("ThisWorkbookDataModel";"[Measures].[Umsatz]"; C8; C9)
Das 1. Argument ThisWorkbookDataModel stellt wieder die Verbindung ins Datenmodell dar. Achten Sie auch hier darauf, den Wert in Anführungszeichen zu übergeben.
Das 2. Argument ist der Umsatz. Die Auflistung Measures beinhaltet alle berechneten Werte. In unserem Datenmodell existiert aktuell nur ein Measure.
Im 3. und im 4. Argument beziehen wir uns auf die Zellen C8 und C9. Diese beinhalten ja die Elemente aus dem Würfel, für welche wir den Umsatz ausgeben wollen. Der Umsatz wird auf die Bestellungen reduziert, die hier betrachtet werden sollen.
Möchten Sie mehr über das Arbeiten mit dem Datenmodell von Microsoft Excel erfahren? Dann empfehle ich Ihnen meinen Kurs MS Excel 2010 / 2013 / 2016 - Power Pivot.