MDX-Tutorial #4: Abfragen mit MDX-Sets
Besprochene Themen: MDX-Sets – oder auf deutsch: MDX-Mengen; der Doppelpunkt-Operator „:“
Was sind MDX-Sets?
Hallo und willkommen zurück zum MDX-Tutorial. Im vierten Teil geht es um Sets – oder auf Deutsch: um Mengen. Wozu wir Sets brauchen, sehen wir am besten anhand eines Beispiels. Beim letzten Mal haben wir festgestellt, dass die Verkäufe durch Vertriebspartner pro Jahr etwa 20 bis 30 Millionen Euro Umsatz generieren. Aber wie steht es um den Webshop? Einer der Marketing-Experten von Fahrrad.9000 behauptet, dass der Webshop komplett überflüssig ist. Kunden wollen seiner Meinung nach ein Fahrrad erst probefahren, bevor sie zugreifen. Und das ganze Internet ist sowieso nur eine vorübergehende Erscheinung.
Nur gut, dass wir den Cube haben. Bestimmen wir zunächst den Internetumsatz pro Jahr. Das ist nichts Neues:
SELECT [Measures].[Internet Sales Amount] ON COLUMNS, [Date].[Calendar Year].[Calendar Year] ON ROWS FROM [Adventure Works];
Also alles wie gehabt. Das ganze liefert uns:
Im Gründungsjahr haben wir mit Fahrrad.9000 mehr als 3 Millionen Euro über den Webshop eingenommen. In 2003 waren es bereits fast 10 Millionen Euro. Aber reicht das aus um die Behauptung des Marketing-Experten zu widerlegen? Am besten ist es wohl, wir vergleichen Online- und Offline-Umsatz miteinander. Aber wie war nochmal der Umsatz durch die Vertriebspartner? Der Mensch ist vergesslich, nur der Cube ist es nicht. Besser, wir lassen uns beide Kennzahlen gleichzeitig anzeigen.
Mit Sets mehrere Kennzahlen gleichzeitig ausgeben
Der Vergleich zwischen Online- und Offline-Umsatz fällt deutlich leichter, wenn die beiden Kennzahlen gemeinsam in einer Tabelle dargestellt werden. Das könnte dann etwa so aussehen:
Die vorherige Tabelle soll also um eine zusätzliche Spalte erweitert werden, nämlich um den Offline-Umsatz, den wir durch die Verkäufe über unsere Vertriebspartner vor Ort erzielen. Die Abfrage dieser zusätzlichen Spalte geschieht in MDX genau dort, wo festgelegt wird, was in den Spalten angezeigt werden soll, also ON COLUMNS:
SELECT {[Measures].[Reseller Sales Amount], [Measures].[Internet Sales Amount]} ON COLUMNS, [Date].[Calendar Year].[Calendar Year] ON ROWS FROM [Adventure Works];
Sowohl die Kennzahl für den Online-Umsatz als auch für den Offline-Umsatz werden ON COLUMNS – also zu Deutsch „auf die Spalten“ – ausgewählt. Da nun mehrere Kennzahlen abgefragt werden, müssen diese durch Mengenklammern {…} zu einem MDX-Set zusammengefasst werden.
Offenstichtlich stammt ungefähr ein Drittel des Umsatzes von Fahrrad.9000 aus dem Internet. Das ist eine ganze Menge und sollte dem Marketing-Experten vorerst zu denken geben.
MDX-Sets gruppieren gleichartige Elemente zu einer Menge
Die Kennzahlen in der vorherigen Abfrage sind in Mengenklammern {…} aufgeführt und bilden so ein MDX-Set. Sets werden in MDX immer dann verwendet, wenn auf mehrere Objekte zugegriffen werden soll, und diese Objekte vom gleichen Typ sind. Und genau das ist ja im Beispiel der Fall: Alle Elemente der Menge { [Measures].[Reseller Sales Amount], [Measures].[Internet Sales Amount] } sind Kennzahlen. Genauso könnten wir mehrere Jahre des Attributs „Kalenderjahr“ oder mehrere Kategorien des Attributs „Kategorie“ zu einer Menge zusammenfassen, etwa so:
Wichtig ist, dass ein Set immer nur aus gleichartigen Elementen besteht. Elemente aus verschiedenen Attributen dürfen nicht vermischt werden! In den ersten MDX Queries tappt man sehr gerne in die Falle und kombiniert Elemente aus unterschiedlichen Attributen zu einer Menge. Das führt zum Fehler „Die Elemente gehören zu unterschiedlichen Hierarchien in der -Funktion“. Falls diese Meldung auftritt, überprüfe die Sets: Stelle sicher, dass zwischen geschweiften Klammern nur Elemente des gleichen Attributs auftauchen. Das heißt, die Einträge der Menge dürfen sich nur hinter dem letzten Punkt unterscheiden.
Wenn Elemente verschiedener Dimensionen oder Attribute kombiniert werden sollen, entstehen keine Sets, sondern Tupel – darüber sprechen wir aber erst später.
Abfrage-Beispiel mit mehreren MDX-Sets
Mengen können auch in den Zeilen und im WHERE-Teil einer SELECT-Abfrage verwendet werden. Schauen wir uns dazu ein Beispiel an. Um dem Marketingexperten von Fahrrad.9000 das fürchten zu lehren, betrachten wir den Umsatz in drei bestimten Unterkategorien, nämlich für Fahrradhelme, Reifen & Schläuche sowie für Schutzbleche. Das geht so:
SELECT {[Measures].[Reseller Sales Amount], [Measures].[Internet Sales Amount]} ON COLUMNS, {[Product].[Product Categories].[Subcategory].&[30], [Product].[Product Categories].[Subcategory].&[31], [Product].[Product Categories].[Subcategory].&[37]} ON ROWS FROM [Adventure Works] WHERE {[Date].[Calendar Year].&[2003], [Date].[Calendar Year].&[2004]};
Wie vorher wählen wir die zwei Kennzahlen zum Offline- und Online-Umsatz für die Spalten aus – oder jetzt vielleicht etwas präziser: Die Menge der zwei Kennzahlen wird auf die Spalten gesetzt. Die drei interessierenden Kategorien werden ebenfalls als Menge zusammengestellt und für die Zeilen ausgewählt. Das bewirkt zwei Dinge: Erstens wird der Umsatz für diese Kategorien getrennt ausgegeben. Jede Zeile enthält ausschließlich den Umsatz, der in einer einzelnen Kategorie angefallen ist. Zweitens schränkt die Menge der drei Kategorien die gesamte Abfrage auf nur diese Kategorien ein. Der Umsatz wird ausschließlich für Produkte aus den Unterkategorien 30 (Fahrradhelme), 31 (Reifen & Schläuche) und 37 (Schutzbleche) berechnet – den Rest lassen wir unter den Tisch fallen. Allem voran schränkt außerdem die WHERE-Bedingung den Umsatz auf die Jahre 2003 und 2004 ein.
Das Ergebnis der Abfrage sieht so aus:
Offenbar läuft der Vertrieb von Fahrradzubehör im Internet ausgesprochen gut, sogar deutlich besser als in den Fahrradläden vor Ort. Der Marketingexperte hat nicht daran gedacht, dass niemand Schutzbleche probefährt.
Große Mengen schnell mit dem Doppelpunkt ‚ : ‘ definieren
Einige unserer Vertriebspartner veranstalten alljährlich eine große Sommeraktion: Von Juni bis Oktober gibt es ein Gewinnspiel, das Neukunden gewinnen und den Umsatz steigern soll. Der Cube soll helfen, den Erfolg dieser Aktion zu messen. Dazu fragen wir den Umsatz für den Aktionszeitraum ab. Die einzelnen Monate können mit dem Attribut [Date].[Calendar].[Month] aus der Datums-Dimension abgefragt werden. Wenn wir wie oben vorgehen, müssen in der WHERE-Bedingung alle fünf Monate angegeben werden, wobei die Monate als Menge gruppiert werden:
SELECT [Measures].[Reseller Sales Amount] ON COLUMNS, {[Date].[Calendar].[Month].&[2003]&[6], [Date].[Calendar].[Month].&[2003]&[7], [Date].[Calendar].[Month].&[2003]&[8], [Date].[Calendar].[Month].&[2003]&[9], [Date].[Calendar].[Month].&[2003]&[10]} ON ROWS FROM [Adventure Works]
Hier ist es recht mühselig und unübersichtlich, alle Monate in der Menge aufzulisten. Glücklicherweise gibt es dafür eine recht nützliche Kurzschreibweise. Da die Monate in der Hierarchieebene „Monat“ aufeinander folgen, können wir den Doppelpunkt-Operator verwenden:
SELECT [Measures].[Reseller Sales Amount] ON COLUMNS, [Date].[Calendar].[Month].&[2003]&[6]: [Date].[Calendar].[Month].&[2003]&[10] ON ROWS FROM [Adventure Works]
Hier dürfen keine Mengenklammern verwendet werden, denn der Doppelpunkt erzeugt bereits eine Menge. Der Ausdruck { A : B } wäre somit eine Menge, die eine Menge enthält – also etwas ziemlich kompliziertes, das wir gar nicht brauchen.
Bei der Verwendung des Doppelpunkts ist allerdings Vorsicht geboten. Der Cube entscheidet für A : B , welche Elemente zwischen A und B liegen, indem er nachsieht, in welcher Reihenfolge die Elemente im Attribut stehen. Was das heißt, sieht man recht gut am Attribut „Farbe“ aus der Dimension „Produkt“.
Die Menge [Blue] : [Red] entspricht zum Beispiel den Elementen [Blue], [Grey], [Multi], [NA] und [Red]. Der „:“ vergleicht hier keine Werte der Größe nach und sortiert auch nicht alphabetisch – in die Menge gelangen schlicht diejenigen Elemente, die von den beiden angegebenen Elementen eingeschlossen werden. Und das gilt auch für numerische Attribute! Das bedeutet insbesondere, dass bei einer falschen Sortierung innerhalb der Datums-Dimension die Menge [2002] : [2004] nicht zwingend die Jahre 2002 bis 2004 enthalten muss.
Weitere Möglichkeiten Mengen zu erzeugen
Es gibt noch eine ganze Reihe weiterer MDX-Befehle, mit denen Mengen erzeugt werden können, etwa die Differenz und die Vereinigung von Mengen. Dazu aber später mehr.
Das war es für diese Woche. Bei Unklarheiten oder Vorschlägen gilt wie immer: gerne eine E-Mail schreiben oder einfach einen Kommentar weiter unten hinterlassen.
28. Januar 2014 @ 12:41
Hallo und guten Tag,
ein wirklich sehr nützliches Tutorial für absolute MDX-Anfänger wie mich. Ich kenne mich zwar sehr gut mit SQL aus, aber MDX war erstmal ein Schlag ins Gesicht. Vielleicht auch erstmal vor dem Hintergrund, dass man natürlich schnell etwas erstellen soll. Den man ‚macht ja auch sonst mit Datenbanken herum, da kann das doch nicht so schwierig sein‘.
Schade, dass es (noch?) nicht weiter ging. Aber nichts destotrotz: vielen Dank. Die Seite hat mir sehr geholfen einen ersten Einstieg zu erhalten.
Gruss, Adam
18. Februar 2014 @ 10:10
Guten Tag,
wunderbar die Seite – das hat mir sehr geholfen.
Danke!
PS: ich bin schon auf die nächsten Kapitel gespannt!
21. Mai 2014 @ 10:05
Das Tutorial ist ein super Einstieg in MDX. Hat mir sehr geholfen
22. August 2014 @ 13:55
Super! Vielen Dank!
Hat mir einen sehr verständlichen Einstieg in die Denke von MDX verschafft.
Wenn es doch nur mehr solcher eleganten Tutorien geben würde bzw. ein Ebook zu einem vernünftigen Preis, dann wäre ich sofort dabei
Freue mich auf die nächsten Teile… weiter so!
15. September 2014 @ 15:54
Super Tutorial! Ich bin schon gespannt auf Teil 5!
16. September 2014 @ 10:33
Hey – wunderbestes Tutorial. Hast dir sehr viel Mühe gegeben
Freue mich schon sehr auf Teil 5!
LG
Chris
18. September 2014 @ 11:16
Endlich mal ein verständliches MDX-Tutorial. Wann geht es mit Teil 5 weiter?
16. Januar 2015 @ 18:47
Kompliment für das super Tutorial. Würde mich ebenfalls über weitere Teile sehr freuen!
17. Januar 2015 @ 22:17
Auch ich kann mich den Komplimenten nur anschließen und kann nur sagen weiter so.
4. August 2015 @ 10:21
Wird es noch eine Fortsetzung des Tutorials geben?
13. September 2015 @ 3:10
super Beitrag!
20. Juli 2016 @ 14:47
Super Ding, dein Tutorial. Schade dass du nicht weiter machst.
27. Juli 2016 @ 10:10
Hi,
wie schränke ich denn in der where Bedingung ein, wenn ich zum Bespiel ab einem bestimmten Jahr alle Daten haben möchte? Das wäre die Übersetzung der Select-Anweisung im where mit >= 2013.
28. August 2016 @ 4:52
Fashion shows are also playing an important part in spreading fashion.
26. Oktober 2018 @ 21:18
Überlegen Sie sich! http://maxisize.eu Es ist einfach, sich einer Operation zu unterziehen, probieren Sie erst jedoch MAXISIZE – eine risikolose und unschädliche Methode, die kein Pendant auf dem Markt hat!
26. Oktober 2018 @ 21:19
Fito Spray hilft Ihnen, Ihr Leben zu verändern!
http://fitosprayde.com/
Dieser Abnehmspray ist ein revolutionäres Mittel, das auf der Basis von natürlichen pflanzlichen Inhaltsstoffen hergestellt ist. Sein Hauptziel ist, das Hungergefühl zu reduzieren, welches mollige und korpulente Menschen oft begleitet.
26. Oktober 2018 @ 21:20
Fito Spray Teilnehmerin des Eurovision hat auf 40 Kilo abgenommen! http://fitospray.eu/
Sogar bei der schlechten Erblichkeit kann man auf 40 Kilo abnehmen. Ich bekam es auf eigener Haut zu spüren!