Ein berechnetes Element im MDX, das falsche Werte erzeugt? Da muss doch ein Fehler beim Erstellen der Berechnungsformel vorliegen – könnte man meinen, wenn man zum ersten Mal von einem Kunden mit diesem Thema konfrontiert wird; so auch bei Bissantz. Dabei handelte es sich um die Berechnung des „Rest obere“/„Rest untere“ in der Pivotnavigation von DeltaMaster. Also kann es kein Fehler bei der Erstellung des MDX sein – schließlich funktioniert es doch in vielen anderen Fällen!

Abbildung 1: Da staunt der Laie und der Fachmann wundert sich stundenlang

Bei der anschließenden Analyse des generierten MDX stellte sich heraus, dass alles korrekt war und auch die Ergebnisse waren nicht immer falsch. Aber unter bestimmten Umständen war die Berechnung dann doch nicht richtig. Der „Rest obere“ war größer als die Gesamtsumme – da konnte etwas nicht stimmen. Nach langem Analysieren und mehrmaligem Umstellen des ursprünglich von DeltaMaster erzeugten MDX-Statements konnte ein erster Erfolg vermeldet werden – die Berechnungen waren immer korrekt. Keine berechneten Teilmengen, die in Summe größer waren, als die Summe über alle Elemente.

Stellte sich nur noch die Frage nach dem Warum! Nun, es ist ganz einfach: Sobald Attribut-Hierarchien mit ins Spiel kommen, kann der oben erwähnte Fall auftreten. In DeltaMaster werden die Elemente für die Darstellung des „Rest obere“/„Rest untere“ als WITH MEMBER angelegt. Innerhalb eines solchen Members wird dann mit FILTER() und TOPCOUNT() bzw. BOTTOMCOUNT() die Restsumme errechnet. Bei der Berechnung wird aber nicht berücksichtigt, wenn in der Attribut-Hierarchie eine Einschränkung vorgenommen wurde. Die Berechnung geht also immer von der Gesamtmenge aus und es wird auch immer der Restwert auf der Basis des Gesamtwerts errechnet – ohne die Einschränkung aus der Attribut-Hierarchie zu berücksichtigen. Ungeachtet dieses Phänomens ist die Darstellung der Top-/Bottom-Elemente vollkommen korrekt. Diese werden immer richtig ausgewertet!

Die gute Nachricht: Alles wird gut

All denen, die jetzt feuchte Hände bekommen und sich fragen bei wie vielen Kunden uns das auf die Füße fallen kann, sei gesagt: keine Angst – mit dem nächsten Release wird alles gut! Die Entwicklung hat sich des Themas angenommen und sofort einen Hotfix bereitgestellt. Im nächsten Release (Delta-Master 6.1.3 bzw. 5.6.5 MP4) wird die Berechnungsformel angepasst sein, so dass auch bei Verwendung von Attribut-Hierarchien die Berechnung von „Rest obere“/„Rest untere“ korrekte Ergebnisse liefern wird.

Die schlechte Nachricht: Nicht alles kann automatisiert werden

DeltaMaster ist ein tolles Produkt – das steht außer Frage. Aber manchmal müssen wir auch selbst Hand anlegen und da kann uns weder DeltaMaster noch unsere hervorragende Entwicklungsabteilung unterstützen. Genau in diesen Fällen müssen wir als Beratermannschaft die seltsamen Effekte kennen, die mit Attribut-Hierarchien auftreten können und natürlich müssen wir auch wissen, welche Waffe wir für diese Fälle in unserem MDX-Fundus haben.

Alles wird gut – mit EXISTING()

Es ist nur diese eine MDX-Funktion, die zwischen scheinbar falschen und absolut richtigen Ergebnissen liegt: EXISTING(). An der richtigen Stelle eingesetzt, werden nicht alle Elemente einer Dimension für die weitere Berechnung verwendet, sondern nur die Elemente, die in Bezug auf die Auswahl in der Attribut-Hierarchie der gleichen Dimension, logisch mit der Auswahl zusammenhängen.
Ein praktisches Beispiel wäre jetzt ganz gut, um zu verdeutlichen, wie die EXISTING()-Funktion eingesetzt wird.

Ein einfaches Beispiel mit der Chair-Datenbank

Für ein einfaches Beispiel benötigen wir ein Datenmodell mit mindestens einer Attribut-Hierarchie – und wir haben eins, das jeder kennt: die Chair!
In der Chair gibt es in der Kunden-Dimension eine Attribut-Hierarchie – die Branche. Das einzige, was wir als Voraussetzung für das Beispiel benötigen, ist die Einstellung, dass parallele Hierarchien immer als separate Dimension behandeln werden sollen (Extras – Optionen – System).

Abbildung 2: Einstellung für die Behandlung von parallelen Hierarchien

Damit bekommen wir gleichzeitig die Möglichkeit, Elemente aus der Kunden-Struktur als auch aus der Branchen-Struktur auszuwählen.

Kommen wir nun zu einer Aufgabenstellung, bei der wir die EXISTING()-Funktion unbedingt benötigen:

In der Kunden-Struktur soll ein berechnetes Element im DeltaMaster hinzugefügt werden, das die Summe aller Kunden die mit „B“ beginnen, ausgibt.

Hier eine Ansicht der Ausgangsmenge – der geneigte Leser merkt sofort, dass unsere Zielmenge der Kunden unterschiedlichen Vertriebsbereichen der Branche-Hierarchie zugeordnet ist. Was für ein Glücksfall:

Abbildung 3: Die Grundlage für unser Beispiel

Nun aber zurück zur Aufgabe: Das berechnete Element, mit dem die Summe über alle Kunden, die mit „B“ beginnen, ausgegeben wird. Dazu legen wir ein berechnetes Element mit folgendem MDX an:

Aggregate(
  Filter(
    [Kunde].[Kunde].[Kunde].Members,
    Left([Kunde].[Kunde].CurrentMember.Name, 1) = "B"
  )
)

Damit bekommen wir auf den ersten Blick genau das, was geliefert werden soll: Die Summe über alle Kunden, die mit „B“ beginnen:

Abbildung 4: Der erste Test – Ergebnis stimmt!

Jetzt wäre das Ganze aber ein schlechtes Beispiel, wenn es nicht noch einen Haken gäbe: Das Ergebnis stimmt nämlich nur so lange, wie die Branchen-Auswahl auf „Alle Kunden“ steht. Sobald etwas anderes für die Branche ausgewählt wird, stimmt die berechnete Summe nicht mehr mit der Liste der Kunden überein. Wird zum Beispiel der Vertriebsbereich „Handel“ in der Sicht eingestellt, darf die Bundesagentur für Arbeit weder in der Pivottabelle noch im berechneten Element einbezogen werden. Bei der Darstellung in der Pivottabelle funktioniert das auch, aber im berechneten Element offensichtlich nicht:

Abbildung 5: Fehlerhafte Berechnung bei Einschränkung auf Vertriebsbereich “Handel”

Die errechnete Summe passt nicht zu den angezeigten Kunden. Warum ist das so? Nun, bei der Berechnung wird als Ausgangsmenge für den Filter die Menge aller Kunden verwendet ([Kunde]. [Kunde]. [Kunde].Members). Die gesamte Berechnung wird im WITH-Block des MDX-Statements untergebracht, während die Einschränkung für die Branche im WHERE-Teil des SELECT-Statements steht. Die Einschränkungen aus dem WHERE-Teil werden aber bei der Ermittlung der Mengen im WITH-Block nicht berücksichtigt – und das ist die Stelle, an der wir die EXISTING()-Funktion brauchen.

       WITH

WITH
MEMBER [Kunde].[Kunde].[temp] AS '
Aggregate(
  Filter(
    [Kunde].[Kunde].[Kunde].Members,
    Left([Kunde].[Kunde].CurrentMember.Name, 1) = "B"
  )
)', SOLVE_ORDER=125, SCOPE_ISOLATION=CUBE, VISIBLE=1

SELECT 
  {[Measures].[Absatz]} ON AXIS(0),
  {
    Filter(
	  Descendants(
	    [Kunde].[Kunde].[Alle Kunden], 
	    [Kunde].[Kunde].[Kunde]
        ),
	  Left([Kunde].[Kunde].CurrentMember.Name, 1)="B"
	)
	+
	[Kunde].[Kunde].[temp]
  } ON AXIS(1)
FROM [Chair]
WHERE ([Kunde].[Branche].[Vertriebskanal].&[1])

Wenn die EXISTING()-Funktion auf die Menge der Kunden angewendet wird, die über [Kunde]. [Kunde]. [Kunde].Members ermittelt wird, dann wird die Einschränkung auf den Vertriebskanal aus dem WHERE-Teil auf diese Menge angewendet und es kommen nur noch die Kunden in die Ausgangsmenge für die Filterfunktion, die über die Attribut-Hierarchie dem ausgewählten Vertriebskanal zugeordnet sind.
Hier das MDX:

WITH
MEMBER [Kunde].[Kunde].[temp] AS '
Aggregate(
  Filter(
    EXISTING([Kunde].[Kunde].[Kunde].Members),
    Left([Kunde].[Kunde].CurrentMember.Name, 1) = "B"
  )
)', SOLVE_ORDER=125, SCOPE_ISOLATION=CUBE, VISIBLE=1

SELECT 
  {[Measures].[Absatz]} ON AXIS(0),
  {
    Filter(
	  Descendants(
	    [Kunde].[Kunde].[Alle Kunden], 
	    [Kunde].[Kunde].[Kunde]
        ),
	  Left([Kunde].[Kunde].CurrentMember.Name, 1)="B"
	)
	+
	[Kunde].[Kunde].[temp]
  } ON AXIS(1)
FROM [Chair]
WHERE ([Kunde].[Branche].[Vertriebskanal].&[1])

Und hier das Ergebnis aus DeltaMaster:

Abbildung 6: Nur durch Verwendung der Existing()-Funktion stimmt jetzt das Ergebnis

Fazit

Dieses Problem war in seinem vollen Umfang bis vor kurzem nicht bekannt. Wir hoffen, dass dieser Blog-Artikel dabei hilft, in Projekten, in denen mit Attribut-Hierarchien gearbeitet wird, die Sensibilität für die Tatsache zu erhöhen, dass trotz einwandfreien MDX-Statements in berechneten Elementen fehlerhafte Ergebnisse allein durch das Fehlen der EXISTING()-Funktion auftreten können.