Daten im Excel-Datenmodell können wir mit Diagrammen oder Pivot-Tabellen analysieren. Auf Werte oder Aggregationen im Datenmodell kann man aber auch mit Excel-Funktonen zugreifen – ganz ohne Pivot oder Diagramm – mit den sogenannten CUBE-Funktionen in Excel.  

Das Ziel

In unserem Datenmodell haben wir Umsatzdaten von 2011-2014 verfügbar – wir nehmen die Daten aus der AdventureWorks-Datenbank. Die Daten verknüpfen wir mit einer Kalender-Tabelle: Datenmodell für das Beispiel Als Ergebnis möchten wir weder ein Diagramm noch eine Pivot-Tabelle, sondern tatsächlich nur nackte Ergebnisse (allerdings wird die Beschriftung vorne dynamisch angepasst): Das Ergebnis

Measures

Für die Umsatzzahlen sowie den Wert des Slicers legen wir ein paar Measures an:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos
  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos
  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos
  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Die Magie der CUBE-Funktionen

Excel bietet Funktionen an, um direkt auf das Datenmodell zuzugreifen. Wir betrachten die Funktion CUBEWERT: CUBE-Funktionen in der Autovervollständigung Die Parameter sind:

  • Daten-Verbindung: Die Datenverbindung zum Datenmodell heißt ThisWorkbookDataModel.
  • Eigenschaft1: Als Eigenschaft geben wir beliebig viele Koordinaten ein – Measures fangen dabei immer mit “[Measures]” an.
  • Eigenschaft2
  • …

Dank den Entwicklern erhalten wir aber jeweils eine Autovervollständigung für die Datenverbindung oder die Eigenschaften. Die Formel für den Umsatz des aktuellen Jahres lautet: =CUBEWERT("ThisWorkbookDataModel";"[Measures].[Summe_Jahr]")

Filtern

Das Ergebnis der Formel ist leer – das liegt daran, dass die Eigenschaft Summe\Jahr_ keiner Einschränkung unterliegt und damit laut obiger Definition Blank() zurückgibt. Möchten wir den Wert für 2012 haben, geben wir eben diese entsprechende Koordinate an: =CUBEWERT("ThisWorkbookDataModel";"[Measures].[Summe_Jahr]";"[Kalender].[Year].[2012]") Damit haben wir die Zahl auf das Jahr 2012 eingeschränkt. Das bekommen wir aber noch dynamischer hin.

Filtern mit Slicern

Slicer können völlig unabhängig von Pivot-Tabellen angelegt werden. Im Excel-Menü unter Einfügen finden wir den Datenschnitt: Datenschnitt anlegen ohne Pivot Im PopUp wählen wir die Datenverbindung – das eigene Datenmodell befindet sich auf einem zweiten, separaten Reiter: Verbindungsauswahl für den Slicer Dort wählen wir das Jahr des Kalenders aus und erhalten wie bei Pivot-Tabellen gewohnt einen Slicer. Jetzt müssen wir diesen Slicer nur noch mit unserem CUBEWERT verbinden. Namen von Datenschnitten fangen im deutschen Excel immer mit Datenschnitt an. Also bemühen wir doch einfach mal die Autovervollständigung: Slicer in der CUBE-Funktion Und dort finden wir auch unseren neu eingefügten Datenschnitt. Wählen Sie jetzt ein Jahr aus erhalten Sie für die CUBEWERT-Formel auch ein Ergebnis.

Zum Abschluss ein Trick

Wir legen nochmal eine Pivot-Tabelle an – Umsatz zu Jahr: Einfaches Pivot-Diagramm Im Power Pivot Menü finden wir einen Eintrag Unter OLAP-Tools mit dem Namen In Formeln konvertieren: In Formeln konvertieren Dadurch wird unsere Pivot-Tabelle aufgelöst – und mit CUBE-Formeln dargestellt: Pivot-Tabelle als Formeln Hier sieht man auch die Verwendung der Funktion CUBEELEMENT – diese Funktion gibt nur das entsprechende Element zurück, das in der Funktion CUBEWERT als Einschränkung verwendet wird. Viel Spaß beim Nachbauen!