Cube Funktionen in Excel 2007 & 2010
** Hinweis: Bitte auch die späteren Webcasts zu diesem Thema beachten! **
In der letzten Woche kam während eines SSAS Workshops die Frage nach dem Sinn der Cube Funktion in Excel 2007 & 2010 auf. Zwar war den meisten Teilnehmern die Existenz der Funktionen bekannt, da sie diese über den Menüpunkt “In Formeln konvertieren” in Excel (s. Screenshot) kannten, aber bisher hatte kein Teilnehmer Erfolg dabei einen Bericht von Grund auf mit den Funktionen zu erstellen.
Die offizielle Online Hilfe von Microsoft kennt zwar die Funktionen, aber die Beschreibung der Funktionsweise als auch die Beispiele dort sind eher knapp gehalten bzw. leider auch ein wenig substanzlos.
Welche Funktionen für die Arbeit mit Analysis Services Cubes kennt ein Excel überhaupt? Das lässt sich leicht herausfinden. Einfach mal “=cube” in eine Zelle eingeben. Die AutoVervollständigen Funktion von Excel zeigt uns alle Excel Funktionen an.
Das sind ganze 7 Excel Funktionen. Fangen wir mit einem einfachen Beispiel für CUBEWERT an. Dazu geben wir in eine Zelle “=cubewert(” ein.
Als ersten Parameter benötigen wir eine Verbindung. Nach der Eingabe eines Anführungszeichen bekommen wir eine Auswahl aller gespeicherten Verbindungen zu Analysis Services Würfeln.
Wie kommt aber Euer Excel nun an eine solche Verbindung? Wenn Ihr von einer leeren Arbeitsmappe aus startet, dann kennt diese noch keine Verbindungen. Verbindungen werden über den Tab “Daten” und den Punkt “Verbindungen” hinzugefügt.
Und die vorhandenen Verbindungen entstehen, wenn Ihr Euch schon mal mit Würfeln verbunden habt. Dazu ist dieser Dialog im Datenverbindungs-Assistent. Der kommt Euch sicher bekannt vor.
Aber zurück zu unserem CUBEWERT Beispiel. Wir wählen nun eine Verbindung aus indem wir die “Tab”-Taste drücken und mit Anführungszeichen die Bezeichnung der Verbindung abschließen.
Nun noch ein Semikolon und Excel erwartet den nächsten Parameter. Geben wir erst mal wieder Anführungszeichen ein.
Was ist das? Das sind ja alle Dimensionen unseres Cubes! Wie hilfreich! Wählen wir erst mal “[Date]” aus. Einfach mit den Cursortasten markieren und wieder die “Tab”-Taste betätigen. Und nun? Einfach mal einen Punkt eingeben.
Und schon sind wir auf dem nächsten Level der Hierarchie der Date Dimension. Dort wählen wir nun “[Date].[Calendar]” aus. Zum Abschluss geben wir noch “.&[2003]”)” ein, da uns das Jahr 2003 interessiert. Eine andere Art der Auswahl gibst gleich.
Mit der Enter-Taste bestätigen wir die Eingabe der Formel. Bei mir erscheint nun folgendes:
Cool! Nur was sagt uns dieses Ergebnis? Wir haben doch bisher nur ein Jahr angegeben und nichts weiter. Woher weiß denn Excel nun was wir wollen? Eigentlich weiß Excel rein gar nichts über unsere Absichten. Das Ergebnis kommt direkt von den Analysis Services und zeigt den Wert des Default Measures “Reseller Sales Amount” an. Starten wir kurz den SQL Profiler und schauen uns mal die MDX Query an, welche Excel absendet für diese Abfrage. Dieses mal interessiert uns das Jahr 2004.
Hier die MDX Query im Detail:
SELECT {([Date].[Calendar Year].&[2004])} ON 0 FROM [Adventure Works] CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
Auch hier steht nichts vom dem Measure, welches uns tatsächlich interessiert. In unserem Beispiel soll es um den “Internet Sales Amount” gehen. MDX ist bekanntlicherweise nicht ganz einfach und denkt sich schon mal seinen Teil, wenn etwas weggelassen wird. Warum stresse ich diesen Punkt hier? Wenn ich mit den Cube Funktionen arbeiten möchte, dann sind zumindest Grundkenntnisse in MDX hilfreich!
Nun steuern wir die Dimension [Measures] an. Moment mal, warum denn die Dimension Measures? Teilt sich ein Cube nicht in Dimensionen und Measures auf? Nun ja, noch ein Beweis dafür, dass wir auf der dunklen Seite der Macht unterwegs sind. Für MDX sind Measures nichts weiter als eine Art weitere Dimension! Wählen wir den “Internet Sales Amount” aus.
Hmm, langsam wir die Formel recht lang.
Aber wir bekommen nun unseren Wert.
Kurz in der klassischen Pivot Darstellung überprüft:
Stimmt! Und was dem geneigten Leser vielleicht aufgefallen ist, ist die Tatsache, dass auch bei den Cube Funktionen die Formatierung als Währung mit übergeben wurde vom Cube. Wie praktisch.
Nun wollen wir den Wert noch weiter filtern. Also Formel wieder bearbeiten und durch ein weiteres Semikolon einen weiteren Parameter beginnen. Die Eingabe der Anführungszeichen bringt uns wieder in die Auswahlbox. Hier wählen wir “[Customer]” und geben wieder einen Punkt an für den nächsten Level. Das machen wir bis wir “[Customer].[Customer Geography].[All Customers].[Germany]” zusammen haben.
Wie Ihr seht, kommen wir auch direkt an die Länder innerhalb der Dimension. Wie komfortabel! Darüber hätten wir weiter oben übrigens auch das Jahr auswählen können. Ihr erinnert Euch?
Wir können also direkt die Formel bearbeiten oder quasi alles das AutoVervollständigen erledigen lassen. Wie ist eigentlich nun unser Wert?
Schick! Und, stimmt er? Schnell wieder ein klassisches Pivot genutzt.
Strike! Was für ein Spaß, oder? Wenn wir uns nun vorstellen, dass wir noch weitere Dimension schneiden wollen, dann stellt sich schnell die Frage nach dem Nutzen der Cube Funktionen. Das ist doch recht umständlich!?!
Wenn wir so weitermachen würden wie bisher, dann ist das tatsächlich recht mühsam und auch ohne echten Nutzen in Excel. Aber wir haben uns bisher ja auch nur eine von 7 Funktionen angeschaut. Und wie so häufig liegt der Nutzen in der Kombination!
Kommen wir zur Excel Funktion CUBEMENGE. Laut Doku bekommen wir damit einen berechneten Satz von Elementen oder Tupeln. Na super, probieren wir das mal aus. Die Basics kennen wir ja schon.
Jetzt brauchen wir einen Mengen Ausdruck? Ok, schauen wir mal.
So, und das ergänzen wir jetzt noch um ein “.children”.
Damit sollten wir, sofern mich meine MDX Kenntnisse nicht komplett verlassen haben, ein Set (Menge) bekommen. Und los! Und was kommt dabei raus?
Nee, schon klar. Was ist das jetzt? Ein Set! Wie soll auch Excel eine unbekannte Anzahl an Daten in nur einer Zelle darstellen? Nur was soll das dann? Und wie bekommen wir raus, was sich dort befindet?
Als erstes nutzen wir mal den Parameter, um eine Beschriftung zu hinterlegen.
Damit haben wir nun zumindest dort was stehen.
Aber was steckt da nun hinter? Nehmen wir gleich mal die nächste Cube Funktion und zwar CUBEMENGENANZAHL. Diese braucht nur einen Parameter und zwar eine Menge. Das trifft sich gut, da wir ja gerade eine über. Also los.
Und was kommt raus?
In unserer “unsichtbaren” Menge stecken also 6 Elemente. Schauen wir uns diese mal an. Und dazu kommt gleich die nächste Cube Funktion ins Spiel und zwar CUBERANGELEMENT.
Auch brauchen wir wieder eine Menge (F8) und einen Rang, sprich die Ansage das wievielte Element unserer Menge wir haben wollen. Und was ist das erste Element?
Ok, das ist doch schon mal was. Holen wir uns die restlichen fünf Elemente dazu. Und damit wir den Rang nun nicht manuell durchzählen, legen wir erst mal eine Zahlenreihe daneben. Dann können wir die Formel gleich schön runterziehen. Statt mit der Zahlenreihe zu arbeiten wäre auch die Verwendung von ZEILE(A1) möglich. Das ist ein alter Excel Trick, um aus dem nichts eine Zahlenreihe innerhalb von Formeln zu erzeugen. Aber zurück zu unserem Beispiel.
Cool, nun haben wir schon unsere Vertriebsgebiete.
Ein paar Zahlen wären nun ganz nett, oder? Da kommt unser alter Freund CUBEWERT wieder ins Spiel. Aber vorher sollten wir nur noch kurz festlegen welche Measures wir sehen wollen. Das können wir ebenfalls hinterlegen. Wir nutzen wir dafür die Cube Funktion CUBEELEMENT.
Nehmen wir wieder unseren “Internet Sales Amount”.
Und die “Internet Freight Cost” haben wir gleich daneben gelegt. Was haben wir denn nun davon außer einer Überschrift? Das sehen wir, wenn wir nun endlich die CUBEWERT Funktion nutzen.
Nun bauen wir uns nämlich die Parameter einfach ein. Zuerst kommt das Gebiet.
Und das erste Measure. Und damit haben wir für den Moment bereits alles zusammen. Das ging doch nun viel schneller, oder?
Damit haben eine Zahl! Nun die Formel auf die restlichen Zellen kopieren und schon haben wir den Rest!
Das ging doch nun richtig schnell, oder? Glückwunsch, damit sind wir schon recht weit gekommen. Alles was uns für den Moment fehlt ist ein wenig Interaktivität, oder? Dazu nutzen wir Filter vom PivotTable ohne die starre Struktur eines solchen zu verwenden. Wie das? Dafür fügen wir an einer freien Stelle einen PivotTable basierend auf der bisher auch verwendeten Verbindung ein. Warum eine freie Stelle? Sonst wird unsere bisherige Arbeit überschrieben!
Da ist nun unser PivotTable. Was machen wir nun damit?
Wir fügen in den Berichtsfilter die Hierarchien “Date Calendar” und “Produkt Categories” ein. Das war es schon! Dann markieren wir die beiden Filterzeilen und verschieben diese über unsere Tabelle.
Es liegt Business Intelligence in der Luft, oder? Nun noch schnell die CUBEWERT Formeln erweitern um die beiden Filter (G5 & G6). Und schön auf alle Zellen kopieren.
Und nun können wir die Filter nutzen.
Und die Auswahl wirkt sich jeweils sofort auf unsere Zahlen aus!
Damit haben wir nun in unserem Arbeitsblatt die Möglichkeit geschaffen direkt auf Cube Daten zuzugreifen ohne die starre Struktur einer PivotTable. Des Weiteren haben wir gleich noch zwei interaktive Filter eingebaut. Das ist schon mal gutes Handwerkszeug, um anspruchsvollere Controllingaufgaben zu meistern. Nun können wir eine weitere Dimensionen hinzufügen und noch ein kleines Chart. Die notwendige Basis dafür haben wir schon geschaffen.
Das mag von der Komplexität und der nötigen Zeit her aufwändig erscheinen, aber mit ein klein wenig Übung geht das schnell und zügig von Hand und der Excel Profi wird diese Möglichkeit einfach und schnell mit immer aktuellen Zahlen zu arbeiten nicht mehr missen wollen. Wann immer sich die Daten im Analysis Services Cube ändern bzw. weitere Daten dazu kommen, werden auch die Informationen im Arbeitsblatt aktualisiert.
Ich hoffe, dass ich dem einen oder anderen mit dieser Schritt-für-Schritt Anleitung einen Einstieg in die Arbeit mit den Cube Funktionen von Excel ermöglicht habe. Viel Freude damit!
In der letzten Woche kam während eines SSAS Workshops die Frage nach dem Sinn der Cube Funktion in Excel 2007 & 2010 auf. Zwar war den meisten Teilnehmern die Existenz der Funktionen bekannt, da sie diese über den Menüpunkt “In Formeln konvertieren” in Excel (s. Screenshot) kannten, aber bisher hatte kein Teilnehmer Erfolg dabei einen Bericht von Grund auf mit den Funktionen zu erstellen.
Die offizielle Online Hilfe von Microsoft kennt zwar die Funktionen, aber die Beschreibung der Funktionsweise als auch die Beispiele dort sind eher knapp gehalten bzw. leider auch ein wenig substanzlos.
Welche Funktionen für die Arbeit mit Analysis Services Cubes kennt ein Excel überhaupt? Das lässt sich leicht herausfinden. Einfach mal “=cube” in eine Zelle eingeben. Die AutoVervollständigen Funktion von Excel zeigt uns alle Excel Funktionen an.
Das sind ganze 7 Excel Funktionen. Fangen wir mit einem einfachen Beispiel für CUBEWERT an. Dazu geben wir in eine Zelle “=cubewert(” ein.
Als ersten Parameter benötigen wir eine Verbindung. Nach der Eingabe eines Anführungszeichen bekommen wir eine Auswahl aller gespeicherten Verbindungen zu Analysis Services Würfeln.
Wie kommt aber Euer Excel nun an eine solche Verbindung? Wenn Ihr von einer leeren Arbeitsmappe aus startet, dann kennt diese noch keine Verbindungen. Verbindungen werden über den Tab “Daten” und den Punkt “Verbindungen” hinzugefügt.
Und die vorhandenen Verbindungen entstehen, wenn Ihr Euch schon mal mit Würfeln verbunden habt. Dazu ist dieser Dialog im Datenverbindungs-Assistent. Der kommt Euch sicher bekannt vor.
Aber zurück zu unserem CUBEWERT Beispiel. Wir wählen nun eine Verbindung aus indem wir die “Tab”-Taste drücken und mit Anführungszeichen die Bezeichnung der Verbindung abschließen.
Nun noch ein Semikolon und Excel erwartet den nächsten Parameter. Geben wir erst mal wieder Anführungszeichen ein.
Was ist das? Das sind ja alle Dimensionen unseres Cubes! Wie hilfreich! Wählen wir erst mal “[Date]” aus. Einfach mit den Cursortasten markieren und wieder die “Tab”-Taste betätigen. Und nun? Einfach mal einen Punkt eingeben.
Und schon sind wir auf dem nächsten Level der Hierarchie der Date Dimension. Dort wählen wir nun “[Date].[Calendar]” aus. Zum Abschluss geben wir noch “.&[2003]”)” ein, da uns das Jahr 2003 interessiert. Eine andere Art der Auswahl gibst gleich.
Mit der Enter-Taste bestätigen wir die Eingabe der Formel. Bei mir erscheint nun folgendes:
Cool! Nur was sagt uns dieses Ergebnis? Wir haben doch bisher nur ein Jahr angegeben und nichts weiter. Woher weiß denn Excel nun was wir wollen? Eigentlich weiß Excel rein gar nichts über unsere Absichten. Das Ergebnis kommt direkt von den Analysis Services und zeigt den Wert des Default Measures “Reseller Sales Amount” an. Starten wir kurz den SQL Profiler und schauen uns mal die MDX Query an, welche Excel absendet für diese Abfrage. Dieses mal interessiert uns das Jahr 2004.
Hier die MDX Query im Detail:
SELECT {([Date].[Calendar Year].&[2004])} ON 0 FROM [Adventure Works] CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
Auch hier steht nichts vom dem Measure, welches uns tatsächlich interessiert. In unserem Beispiel soll es um den “Internet Sales Amount” gehen. MDX ist bekanntlicherweise nicht ganz einfach und denkt sich schon mal seinen Teil, wenn etwas weggelassen wird. Warum stresse ich diesen Punkt hier? Wenn ich mit den Cube Funktionen arbeiten möchte, dann sind zumindest Grundkenntnisse in MDX hilfreich!
Und MDX ist u.a. die Kunst des Weglassens.
Aber wieder zurück in die Zelle mit unserer Formel. Bearbeiten wir unsere Formel und ergänzen diese mit einem Semikolon um den Wunsch nach einem weiteren Parameter. Nun steuern wir die Dimension [Measures] an. Moment mal, warum denn die Dimension Measures? Teilt sich ein Cube nicht in Dimensionen und Measures auf? Nun ja, noch ein Beweis dafür, dass wir auf der dunklen Seite der Macht unterwegs sind. Für MDX sind Measures nichts weiter als eine Art weitere Dimension! Wählen wir den “Internet Sales Amount” aus.
Hmm, langsam wir die Formel recht lang.
Aber wir bekommen nun unseren Wert.
Kurz in der klassischen Pivot Darstellung überprüft:
Stimmt! Und was dem geneigten Leser vielleicht aufgefallen ist, ist die Tatsache, dass auch bei den Cube Funktionen die Formatierung als Währung mit übergeben wurde vom Cube. Wie praktisch.
Nun wollen wir den Wert noch weiter filtern. Also Formel wieder bearbeiten und durch ein weiteres Semikolon einen weiteren Parameter beginnen. Die Eingabe der Anführungszeichen bringt uns wieder in die Auswahlbox. Hier wählen wir “[Customer]” und geben wieder einen Punkt an für den nächsten Level. Das machen wir bis wir “[Customer].[Customer Geography].[All Customers].[Germany]” zusammen haben.
Wie Ihr seht, kommen wir auch direkt an die Länder innerhalb der Dimension. Wie komfortabel! Darüber hätten wir weiter oben übrigens auch das Jahr auswählen können. Ihr erinnert Euch?
Wir können also direkt die Formel bearbeiten oder quasi alles das AutoVervollständigen erledigen lassen. Wie ist eigentlich nun unser Wert?
Schick! Und, stimmt er? Schnell wieder ein klassisches Pivot genutzt.
Strike! Was für ein Spaß, oder? Wenn wir uns nun vorstellen, dass wir noch weitere Dimension schneiden wollen, dann stellt sich schnell die Frage nach dem Nutzen der Cube Funktionen. Das ist doch recht umständlich!?!
Wenn wir so weitermachen würden wie bisher, dann ist das tatsächlich recht mühsam und auch ohne echten Nutzen in Excel. Aber wir haben uns bisher ja auch nur eine von 7 Funktionen angeschaut. Und wie so häufig liegt der Nutzen in der Kombination!
Kommen wir zur Excel Funktion CUBEMENGE. Laut Doku bekommen wir damit einen berechneten Satz von Elementen oder Tupeln. Na super, probieren wir das mal aus. Die Basics kennen wir ja schon.
Jetzt brauchen wir einen Mengen Ausdruck? Ok, schauen wir mal.
So, und das ergänzen wir jetzt noch um ein “.children”.
Damit sollten wir, sofern mich meine MDX Kenntnisse nicht komplett verlassen haben, ein Set (Menge) bekommen. Und los! Und was kommt dabei raus?
Nee, schon klar. Was ist das jetzt? Ein Set! Wie soll auch Excel eine unbekannte Anzahl an Daten in nur einer Zelle darstellen? Nur was soll das dann? Und wie bekommen wir raus, was sich dort befindet?
Als erstes nutzen wir mal den Parameter, um eine Beschriftung zu hinterlegen.
Damit haben wir nun zumindest dort was stehen.
Aber was steckt da nun hinter? Nehmen wir gleich mal die nächste Cube Funktion und zwar CUBEMENGENANZAHL. Diese braucht nur einen Parameter und zwar eine Menge. Das trifft sich gut, da wir ja gerade eine über. Also los.
Und was kommt raus?
In unserer “unsichtbaren” Menge stecken also 6 Elemente. Schauen wir uns diese mal an. Und dazu kommt gleich die nächste Cube Funktion ins Spiel und zwar CUBERANGELEMENT.
Auch brauchen wir wieder eine Menge (F8) und einen Rang, sprich die Ansage das wievielte Element unserer Menge wir haben wollen. Und was ist das erste Element?
Ok, das ist doch schon mal was. Holen wir uns die restlichen fünf Elemente dazu. Und damit wir den Rang nun nicht manuell durchzählen, legen wir erst mal eine Zahlenreihe daneben. Dann können wir die Formel gleich schön runterziehen. Statt mit der Zahlenreihe zu arbeiten wäre auch die Verwendung von ZEILE(A1) möglich. Das ist ein alter Excel Trick, um aus dem nichts eine Zahlenreihe innerhalb von Formeln zu erzeugen. Aber zurück zu unserem Beispiel.
Cool, nun haben wir schon unsere Vertriebsgebiete.
Ein paar Zahlen wären nun ganz nett, oder? Da kommt unser alter Freund CUBEWERT wieder ins Spiel. Aber vorher sollten wir nur noch kurz festlegen welche Measures wir sehen wollen. Das können wir ebenfalls hinterlegen. Wir nutzen wir dafür die Cube Funktion CUBEELEMENT.
Nehmen wir wieder unseren “Internet Sales Amount”.
Und die “Internet Freight Cost” haben wir gleich daneben gelegt. Was haben wir denn nun davon außer einer Überschrift? Das sehen wir, wenn wir nun endlich die CUBEWERT Funktion nutzen.
Nun bauen wir uns nämlich die Parameter einfach ein. Zuerst kommt das Gebiet.
Und das erste Measure. Und damit haben wir für den Moment bereits alles zusammen. Das ging doch nun viel schneller, oder?
Damit haben eine Zahl! Nun die Formel auf die restlichen Zellen kopieren und schon haben wir den Rest!
Das ging doch nun richtig schnell, oder? Glückwunsch, damit sind wir schon recht weit gekommen. Alles was uns für den Moment fehlt ist ein wenig Interaktivität, oder? Dazu nutzen wir Filter vom PivotTable ohne die starre Struktur eines solchen zu verwenden. Wie das? Dafür fügen wir an einer freien Stelle einen PivotTable basierend auf der bisher auch verwendeten Verbindung ein. Warum eine freie Stelle? Sonst wird unsere bisherige Arbeit überschrieben!
Da ist nun unser PivotTable. Was machen wir nun damit?
Wir fügen in den Berichtsfilter die Hierarchien “Date Calendar” und “Produkt Categories” ein. Das war es schon! Dann markieren wir die beiden Filterzeilen und verschieben diese über unsere Tabelle.
Es liegt Business Intelligence in der Luft, oder? Nun noch schnell die CUBEWERT Formeln erweitern um die beiden Filter (G5 & G6). Und schön auf alle Zellen kopieren.
Und nun können wir die Filter nutzen.
Und die Auswahl wirkt sich jeweils sofort auf unsere Zahlen aus!
Damit haben wir nun in unserem Arbeitsblatt die Möglichkeit geschaffen direkt auf Cube Daten zuzugreifen ohne die starre Struktur einer PivotTable. Des Weiteren haben wir gleich noch zwei interaktive Filter eingebaut. Das ist schon mal gutes Handwerkszeug, um anspruchsvollere Controllingaufgaben zu meistern. Nun können wir eine weitere Dimensionen hinzufügen und noch ein kleines Chart. Die notwendige Basis dafür haben wir schon geschaffen.
Das mag von der Komplexität und der nötigen Zeit her aufwändig erscheinen, aber mit ein klein wenig Übung geht das schnell und zügig von Hand und der Excel Profi wird diese Möglichkeit einfach und schnell mit immer aktuellen Zahlen zu arbeiten nicht mehr missen wollen. Wann immer sich die Daten im Analysis Services Cube ändern bzw. weitere Daten dazu kommen, werden auch die Informationen im Arbeitsblatt aktualisiert.
Ich hoffe, dass ich dem einen oder anderen mit dieser Schritt-für-Schritt Anleitung einen Einstieg in die Arbeit mit den Cube Funktionen von Excel ermöglicht habe. Viel Freude damit!