MDX-Tutorial #3: Die erste einfache MDX-Abfrage

Besprochene Themen: Syntax einer MDX-Abfrage, der SELECT-Befehl in MDX, Columns (auf deutsch: Spalten), Rows (auf deutsch: Zeilen), Vergleich von SQL- und MDX-Abfragen.

Die erste einfache MDX-Abfrage

Willkommen zum dritten Teil des MDX-Tutorials. Nachdem wir uns zuvor mit den MDX-Grundbegriffen und dem Adventure Works Cube vertraut gemacht haben, wollen wir uns jetzt an eine erste einfache MDX-Abfrage wagen. Fahrrad.9000 läuft. Im Internet-Shop gehen die Aufträge ein und auch unsere Vertriebspartner verkaufen unsere Produkte vor Ort. Jetzt interessiert uns als erstes der Umsatz: Wieviel Euro haben wir tatsächlich eingenommen? Die Antwort gibt uns der Cube auf unsere erste MDX-Abfrage:

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS
FROM [Adventure Works];

Die Abfrage liefert nur einen Wert, nämlich den Gesamtumsatz von Fahrrad.9000:

Offenbar hat unser Unternehmen bislang 80,5 Millionen Euro über die Läden der Vertriebspartner umgesetzt. Das ganze funktioniert so. Unsere erste MDX-Abfrage wählt die Kennzahl „Reseller Sales Amount“ (zu Deutsch der Vertriebspartner-Umsatz) als Spaltenüberschrift und gibt den Wert in der Zeile darunter aus. Mit „SELECT [Measures].[Kennzahl] ON COLUMNS“ befehlen wir also dem Cube: Gib die angegebene Kennzahl aus, und zwar in die erste Spalte.

Immer dann, wenn Kennzahlen berechnet und ausgegeben werden sollen, ist der SELECT-Befehl mit von der Partie. Da es vorkommen kann, dass mit mehreren Cubes gleichzeitig gearbeitet wird, endet jeder SELECT mit einem „FROM [Cubename]“. So weiß MDX, an welchen Cube die Abfrage gerichtet ist.

Alle Namen und Bezeichner in MDX-Abfragen werden grundsätzlich in eckigen Klammern angegeben. Es heißt also „[Reseller Sales Amount]“ und nicht „Reseller Sales Amount“. Das erlaubt die Verwendung von Leerzeichen, was bei einer guten Namensgebung die Lesbarkeit des Quelltexts erleichtert. Falls kein Leerzeichen auftaucht, können die Klammern aber auch ausgelassen werden. Die Variante „Measures.[Reseller Sales Amount]“ sieht zwar komisch aus, funktioniert aber auch.

Ein kleiner Tipp für das MS SQL Server Management Studio

Du hast ja bestimmt schon gemerkt, dass das Server Management Studio alle Dimensionen und Kennzahlen des Adventure Works Cubes in einem eigenen Bereich auflistet:

Dabei werden die Elemente nicht unbedingt so angezeigt, wie sie im MDX-Quelltext verwendet werden. Die Produktkategorie für Fahrräder schreibt sich in MDX beispielsweise [Product].[Category].&[1], taucht aber in der Liste des Management Studios mit dem Bezeichner „Bikes“ (auf deutsch: Fahrräder) auf. Das ist anfangs etwas verwirrend. Und später auch. Der einfachste Weg um ein unbekanntes Element aus der Liste in eine MDX-Abfrage zu übernehmen ist, es per Drag & Drop in den Editor zu ziehen. Dabei wird automatisch der richtige MDX-Quelltext eingefügt.

Mit WHERE den Umsatz für ein bestimmtes Jahr bestimmen

Die ersten Produkte von Fahrrad.9000 liefen schon 2001 vom Band. Wenn wir wie oben schlicht den Umsatz abfragen, berechnet der Cube den Gesamtumsatz, der sich seit der Unternehmensgründung angehäuft hat. Das ist natürlich nicht genau genug. Schließlich sind wir besessen von Zahlen! Uns interessiert der Umsatz für ein bestimmtes Jahr. Darum kommen jetzt die Dimensionen und Attribute ins Spiel:

Im vorherigen Teil der Einführung in MDX haben wir schon gesehen, dass es im Adventure Works Cube die Dimension „Datum“ gibt. Diese enthält unter anderem das Attribut „Kalenderjahr“. Um nun ein bestimmtes Jahr auszuwählen, können wir die konkreten Elemente dieses Attributs verwenden. In diesem Cube sind das die Jahre 2001 bis 2004 , also 4 Jahre seit Gründung. Wir ergänzen die Abfrage um das Attribut „Kalenderjahr“ und schränken so die Berechnung auf das Jahr 2004 ein:

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2004];

Die Abfrage unterscheidet sich von der vorherigen nur durch die zusätzliche dritte Zeile. Wieder wird die Kennzahl „Reseller Sales Amount“ für die erste Spalte ausgewählt, dieses Mal aber mit der Einschränkung auf ein bestimmtes Element des Attributs „Kalenderjahr“. Das Ergebnis verringert sich dadurch auf:

Die zusätzliche WHERE-Klausel bietet also die Möglichkeit, die Berechnung auf bestimmte Attributelemente einzuschränken. Weitere Beispiele für mögliche Attributelemene sind etwa die Produktfarbe „rot“ oder das Geschlecht „weiblich“. Solche WHERE-Bedingungen werden in der Literatur gerne „Slicing“ genannt. Slicing bedeutet auf Deutsch etwa „sich eine Scheibe abschneiden“. Das ist eine schicke Bildsprache. Stellt man sich den Cube als rechteckigen Würfel vor, in dem die Daten sortiert nach Jahr liegen, also etwa so wie in dem folgenden Bild, dann schränkt die WHERE-Abfrage auf eine bestimmten Scheibe des Cubes ein:

Der WHERE-Befehl schneidet sozusagen ein Stück aus dem Cube heraus – und die Kennzahl verwendet eben nur diese abgetrennte Scheibe zur Berechnung. Dabei unterteilt jede Dimension die Daten auf eine andere Weise. Die Scheiben sehen also für jede Dimension anders aus:

Warum steht da ein &-Symbol vor dem Element &[2004]?

Die letzte Abfrage hat mit dem Element [Date].[Calendar Year].&[2004] auf das Jahr 2004 eingeschränkt. Interessanterweise hätte auch [Date].[Calendar Year].[CY 2004] zu dem gleichen Ergebnis geführt. Beachte hier, dass aus dem &[2004] ein [CY 2004] wird. Warum ist da so?

Alle Elemente, Attribute, Kennzahlen und Hierarchien des Cubes besitzen zwei Namen: Zum einen existiert eine für den Benutzer verständliche Bezeichnung, die klar lesbar ist. Im Attribut Produktkategorie steht zum Beispiel „Bikes“ für die Kategorie der Fahrräder. Hinter dem Klarnamen steckt aber immer auch noch eine zweite maschinenfreundliche Bezeichnung, die der Cube leichter verarbeiten kann. Das wäre für die Kategorie der Fahrräder einfach die „1“.

Damit der Cube bei der Interpretation des MDX-Befehls weiß, ob wir den internen Schlüsel oder den benutzerfreundlichen Bezeichner meinen, wird entweder das &-Symbol verwendet oder nicht. Die Regel lautet:

Um das ganze noch etwas weiter zu verwirren, ist es möglich, dass der Bezeichner und der Schlüssel übereinstimmen. In dem Fall macht es dann keinen Unterschied, ob das &-Zeichen verwendet wird, oder nicht.

Den Umsatz pro Jahr bestimmen

Fahrrad.9000 hat in 2004 also 16 Mio Euro umgesetzt. Um zu überprüfen, ob unser Unternehmen auf Wachstumskurs oder gegen den Untergang läuft, benötigen wir auch noch die Umsatzzahlen der übrigen Jahre, also etwa eine Tabelle wie folgt:

Jedes bisherige MDX-Query enthielt den Umsatz als Spaltenüberschrift. Nur kurz zur Erinnerung: Das ging mit

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS
FROM [Adventure Works];

Jetzt sollen zusätzlich die Jahre zeilenweise ausgegeben werden. Genauso wie mit „on columns“ (deutsch: auf die Spalten) ausgewählt wird, was in die Spalten gehört, gibt es ein „on rows“ (deutsch: auf die Zeilen), das festlegt, was in die Zeilen gehört:

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,
[Date].[Calendar Year].[Calendar Year] ON ROWS
FROM [Adventure Works];

Übrigens: Falls du den MDX-Fehler „Die Calendar Year-Hierarchie wird bereits auf der Axis1-Achse angezeigt.“ erhältst, hast du möglicherweise vergessen, die WHERE-Bedingung zu löschen. Vergleiche deine Abfrage noch einmal genau mit der meinigen.

Beachte, dass wir das [Date].[Calendar Year].[Calendar Year] verwenden – und nicht nur [Date].[Calendar Year]. Hier steht [Date] für die Dimension Datum, das erste [Calendar Year] ist das interessierende Attribut dieser Dimension – und das zweite [Calendar Year] ist schwierig zu erklären. Vereinfacht gesagt macht der Cube keinen Unterschied zwischen Attributen und Hierarchien. Für ihn ist jedes Attribut eine Hierarchie mit nur einer Hierarchieebene. Das zweite [Calendar Year] ist gerade diese Pseudo-Hierarchieebene. Kurzum: Um die Elemente eines Attributs auf einer Achse oder Spalte anzuzeigen, darf nicht das Attribut selbst verwendet werden, sondern die Ebene unterhalb dieses Attributs.

Anfangs verwirrt es etwas, dass Attribute sowohl in den Zeilen, als auch im WHERE-Teil auftauchen können. Vielleicht wird der Unterschied klar, wenn wir kurz einen Schritt zurückgehen und nur das Jahr 2004 betrachten. Die alte Abfrage von oben lautet:

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2004];

Mit der gerade neu gelernten Syntax können wir den Wert auch auf die folgende Weise bestimmen:

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,
[Date].[Calendar Year].&[2004] ON ROWS
FROM [Adventure Works];

Mit dieser Variante ergibt sich:

Der Unterschied besteht darin, wie das Ergebnis angezeigt wird. Die erste Variante enthält das Jahr im WHERE-Teil. Dadurch wird das Jahr 2004 herausgeschnitten und die restliche Abfrage verwendet nur dieses Jahr zur Berechnung. Da aber das Jahr weder für die Anzeige in den Spalten noch in den Zeilen ausgewählt wird, erfolgt keine Ausgabe des Jahres. In der zweiten Variante wird dem Cube mitgeteilt, das Jahr in den Zeilen auszugeben, und sogar noch mehr als das – es wird ein konkretes Jahr ausgewählt, das angezeigt werden soll. Kurz: Wenn eine Dimension ausgegeben werden soll, muss diese in den Spalten (on columns) oder Zeilen (on rows) auftauchen. Andernfalls reicht es aus, die Einschränkung im WHERE-Teil vorzunehmen.

Noch ein MDX-Beispiel mit WHERE

Abschließend folgt noch ein Beispiel, um das Thema klar zu machen. Es wird der Umsatz pro Hauptkategorie bestimmt, der im Jahr 2004 erzielt wurde, also die folgende Tabelle:

Die MDX-Query dazu schreibt sich so:

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,
[Product].[Product Categories].[Category] ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2004];

Nur zur Kontrolle: Die Summe ergibt 16 Millionen. Das stimmt mit dem Betrag der vorherigen Abfrage überein. Das Jahr steht hier im WHERE-Teil, da sich die Abfrage auf ein einzelnes Jahr beschränkt. Dieses mit auszugeben wäre hier unnötig. Natürlich könnten wir zusätzlich noch eine weitere Einschränkung auf ganz bestimmte Kategorien vornehmen. Wie das funktioniert, sehen wir im nächsten Teil. Dort wird beschrieben, wie mit Mengen ausgewählte Elemente zeilenweise ausgegeben werden können. Das wird uns dabei helfen, die Entwicklung des Umsatzes pro Kategorie über die Jahre zu ermitteln.

Wen es interessiert: Ähnlichkeiten zwischen SQL- und MDX-Abfragen

Dieser Abschnitt richtet sich speziell an alle, die sich mit SQL auskennen. Sollte dir das nichts sagen, kannst du ohne Probleme schon mit dem nächsten Teil des MDX-Tutorial fortfahren. Falls du eine Frage hast, kannst du mir natürlich eine E-Mail schreiben oder einen Kommentar am Ende der Seite hinterlassen. Um etwas nachzuschlagen bieten sich zudem auch die Microsoft-Seiten über die Grundlagen zu MDX-Abfragen an.

Wer schon mit SQL gearbeitet hat, wird sicher einige vertraute Schlüsselwörter in den MDX-Abfragen wiedererkennen. Leider sind die Ähnlichkeiten nur sehr oberflächlich – bis auf die Namen und Reihenfolge der Bezeichner haben SQL- und MDX-Abfragen nicht sehr viel gemeinsam. Eine große Schwierigkeit für mich zu Beginn war es, die SQL-WHERE-Bedingungen nach MDX zu übersetzen. Das funktioniert in MDX nämlich deutlich anders. Betrachten wir das vorherige Beispiel, in dem wir den Umsatz pro Hauptkategorie im Jahr 2004 bestimmen. Die entsprechende SQL-Abfrage könnte etwa so lauten:

SELECT ProductCategory, ResellerSalesAmount
FROM sales
WHERE year=2004;

Der große Stolperstein ist die Art und Weise, mit der die WHERE-Bedingung formuliert ist. In SQL bestehen WHERE-Klauseln aus logischen Ausdrücken. Eine Zeile wird genau dann zurückgeliefert, falls für sie die Bedingung „year=2004“ zutrifft. In MDX wird anstatt des logischen Ausdrucks ein Attributselement verwendet, das im Prinzip das Ergebnis der logischen Bedingung darstellt: Für welche Objekte des Cubes gilt „year=2004“? Na eben für alle, deren Attribut „Jahr“ die Ausprägung „2004“ annimmt. Anstatt also den ganzen Cube zu betrachten und für jedes Element zu überprüfen, ob es „year=2004“ erfüllt, schneiden wir uns einfach das gesamte Jahr 2004 heraus und arbeiten damit weiter.

Die geschweiften Klammern {…} und der Doppelpunkt „:“ wählen mehrere Elemente eines Attributs aus. Wie solche sogenannten MDX-Sets funktionieren, wird im nächsten Teil des MDX-Tutorial besprochen.

Ein netter Vorteil an der Sache ist übrigens, dass Ausschlüsse bereits in der SELECT-Klausel angewendet werden können. Falls uns zum Beispiel nur die Hauptkategorie der Fahrräder interessiert, könnten wir die Einschränkung ebenfalls vornehmen mit:

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,
[Product].[Category].&[1] ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2004];

Zur Volständigkeit hier auch das Ergebnis dieser MDX-Abfrage:

Im WHERE-Teil klappt die Kategorieneinschränkung natürlich auch – der Name der Kategorie wird dann aber nicht mit ausgegeben.

Zur MDX-Tutorial-Überrsicht