Seit mehreren Jahren wird immer wieder über die Problematik von berechneten Elementen in den Analysis Services in Verbindung mit Multiselects diskutiert.
So schrieb Mosha bereits 2005 in Writing multiselect friendly MDX calculations über die Probleme, die auftreten wenn eine Berechnung ein CURRENTMEMBER benötigt und Sets in der WHERE Clause verwendet werden.
Das Thema ist aber leider immer noch aktuell und wurde durch Excel 2007 und dessen Vorliebe für Subselects beim Auswählen mehrerer Elemente der gleichen Hierarchie eher noch verschlimmert. Denn dann geben die Analysis Services unter Umständen keinen Fehler mehr zurück, sondern zeigen einfach falsche Werte an.
Da die Hoffnung „Mit Katmai wird alles besser“ für diese Problematik auch nicht zutrifft, wird im Folgenden noch mal auf die Problematik eingegangen und anschließend mit Hilfe von SCOPE eine mögliche Lösung erklärt. Die Beispiele basieren wie immer auf dem Adventure Works Cube, den es bei Codeplex zum Download gibt.
Das Problem
Um das eigentliche Problem zu verstehen, benötigen wir zwei berechnete Elemente. Zum einen werden wir uns „Ratio to Parent Product“ anschauen, das bereits in der Adventure Works enthalten ist und folgende Definition hat:
Create Member CurrentCube.[Measures].[Ratio to Parent Product]
As Case
When [Product].[Product Model Categories].CurrentMember.Level.Ordinal
= 0
Then 1
Else [Measures].[Sales Amount]
/
( [Product].[Product Model Categories].CurrentMember.Parent,
[Measures].[Sales Amount] )
End,
Format_String = "Percent" ;
Zusätzlich definieren wir uns ein einfaches eigenes Measure, dass die Anzahl der Produkte für die jeweilige Auswahl anzeigt:
Create MEMBER CurrentCube.[Measures].[Number of Products]
AS
COUNT(EXISTING([Product].[Product].[Product].MEMBERS));
EXISTING sorgt dabei dafür, dass nicht immer die gleiche Anzahl zurückgegeben wird, sondern das Measure für die jeweils ausgewählten Koordinaten des Cubes neu berechnet wird.
Nun können wir uns das ganze in Excel ansehen. Wenn wir die beiden berechneten Measures und zusätzlich noch den Sales Amount auswählen und die „Product Categories“ auf den Zeilen ausgeben, dann ergibt sich folgendes Bild:
Wie zu erwarten war, werden sowohl Number of Products, als auch die Ratio to Parent richtig für die einzelnen Produktkategorien angezeigt. Dass die Summe der Einzelwerte der „Number of Products“ nicht dem Gesamtergebnis entspricht, liegt übrigens daran, dass 209 Produkte dem, unglücklicherweise unsichtbaren, UnknownMember zugeschlagen wurden.
Wenn wir nun die Produktkategorien von den Zeilen in den Filter verschieben und dort mehrere Elemente auswählen,
dann nutzt Excel im Hintergrund ein SUBSELECT, um diese Einschränkung abzubilden. Das Ergebnis sieht dann folgendermaßen aus:
Für den Sales Amount wird korrekt die Summe der Kategorien „Accessoires“ und „Bikes“ angezeigt, was beweist, dass Excel mit seinem MDX nicht völlig daneben liegen kann. Trotzdem stimmen die Zahlen der anderen beiden Measures nicht. Es sieht aus, als wäre gar keine Einschränkung gemacht worden. Würde man sich ein Measure definieren, das [Product].[Product Model Categories].CurrentMember.Name ausgibt, dann könnte man sehen, dass genau das geschehen ist. Innerhalb der MDX-Scripts, gibt es nur eine gewählte Produktkategorie, nämlich „[All]“. Unsere berechneten Elemente bekommen von der Tatsache, dass sie in einem beschränkten Subcube laufen, nichts mit.
Die Lösung?
Die Grundidee, um dieses Problem zu umgehen, ist echte Measures anzulegen und diese dann per SCOPE so zu überschreiben, dass die eigentlichen Werte dann wieder per MDX ermittelt werden.
Beginnen wir also mit dem Anlegen eines „echten“ Measures für die Anzahl der Produkte. Da es diesen Wert auf Faktenebene nicht gibt, müssen wir uns also erst mal eine Spalte erzeugen, die wir später als Quelle für unser neues Measure nutzen können. Wir erzeugen uns also in der Data Source View für die FactSalesSummary eine leere Dummy-Spalte.
Dazu überarbeiten wir die Abfrage, die hinter der Faktentabelle liegt so, dass in beiden Teilen des UNIONS die neue Spalte „Dummy“ auftaucht.
SELECT
…
, NULL AS [Dummy]
FROM FactResellerSales
UNION
SELECT
…
, NULL AS [Dummy]
FROM FactInternetSales
Nun können wir diese Spalte im Cube verwenden und erzeugen uns auf dessen Basis ein neues Measure Namens „Scoped Number of Products“.
Dabei ist zu beachten, dass die Aggregat-Funktion, die bei Usage ausgewählt wird, später auch wirklich Anwendung findet. Während bei berechneten Elementen auf jeder Ebene im Cube die Berechnung stattfindet, wird beim SCOPING „nur“ für den explizit überschriebenen Subcube das MDX verwendet. Für alle nicht überschriebenen Bereiche wird normal aggregiert, wobei allerdings die „neuen“ Werte herangezogen werden.
Wir wählen also bei Usage die Summe aus und haben unser neues Measure. Nach dem Bereitstellen und Aufbereiten des überarbeiteten Cubes können wir das auch schon verwenden, erhalten aber selbstverständlich nur leere Zellen. Die eigentliche Berechnung müssen wir nun noch unter „Calculations“ definieren:
SCOPE ([Measures].[Scoped Number of Products], [Product].[Product].[Product].MEMBERS );
THIS = 1;
SCOPE ([Product].[Product].[All Products].UnknownMember);
THIS = 0;
END SCOPE;
END SCOPE;
Für alle Elemente des Levels “Product” in der Hierarchie “Product” der Dimension “Product” wird damit der Wert des Measures „Scoped Number of Products“ auf 1 gesetzt. Alle anderen Ebenen und Hierarchien, wie z. B. der All-Knoten, werden dabei ignoriert, da wir dafür ja die bereits definierte Summen-Funktion nutzen wollen.
Um eine Vergleichbarkeit mit dem alten berechneten Element herzustellen, verhindert das zweite SCOPE lediglich, dass das unsichtbare UnknownMember der Produkt-Hierarchie mitgezählt wird.
Um zu überprüfen, ob die Zahlen passen, nehmen wir das neue Measure nun in unsere erste Abfrage von oben mit auf:
Soweit, so gut. Spannender wird es jetzt aber, wenn wir die Kategorien wieder von den Zeilen in den Filter verschieben und nur die ersten beiden Elemente auswählen – Excel also wieder sein beliebtes SUBSELECT ausführt:
Wie erhofft ergibt 35 + 125 nun nicht mehr 606, sondern Excel gibt die korrekte Summe 160 zurück. Dadurch, dass wir das Measure auf der untersten Ebene der Produktdimension überschrieben haben, nutzen die Analysis Services nicht mehr bestehende Aggregate, sondern müssen alle betroffenen Produkte zur Laufzeit berechnen. Die anschließend stattfindende Aggregation der Werte wird dann nur für den betroffenen Subcube, also unter Berücksichtigung des Subselects, durchgeführt.
Um zu zeigen, dass dies nicht nur für simples Zählen funktioniert, erzeugen wir uns ein weiteres Measure auf Basis unserer Dummy-Spalte, nennen es „Scoped Ratio to Parent Product“ und erweitern unser MDX-Script um folgenden Ausdruck:
SCOPE ([Measures].[Scoped Ratio to Parent Product], [Product].[Product Model Categories].MEMBERS);
SCOPE(DESCENDANTS([Product].[Product Model Categories].[All Products], , AFTER));
THIS = [Measures].[Sales Amount]
/ ( [Product].[Product Model Categories].CurrentMember.Parent,
[Measures].[Sales Amount] );
END SCOPE;
Format_String(THIS) = "Percent" ;
END SCOPE;
Dabei sorgt das DESCENDANTS(…) dafür, dass alle Elemente der Hierarchie “Product Model Categories” außer dem All-Knoten überschrieben werden. Das Ergebnis in Excel sieht dann folgendermaßen aus:
Auch hier sorgt das Überschreiben auf unterster Ebene dafür, dass die Berechnung richtig funktioniert. In dem Augenblick, in dem die einzelnen Knoten der ausgewerteten Hierarchie berechnet werden, gibt es eben immer ein CurrentMember, egal ob nun ein oder mehrere Knoten ausgewählt sind.
Einschränkungen
Zugegeben, habe ich mir das ganze hier etwas leicht gemacht. Die beiden Berechnungen einfach zu summieren, macht nämlich eigentlich keinen Sinn. Sowohl die Anzahl der Produkte, als auch die Prozente sind sogenannte semi-additive Measures, die eben nicht einfach summiert werden dürfen. Für beide fehlt also mindestens eine Sonderbehandlung für die Zeitdimension, die durch Nutzung anderer Aggregatfunktionen oder erweiterter SCOPE-Statements erreicht werden könnte, die den Rahmen dieses Beitrags sprengen würden.
Außerdem muss ich davor warnen jetzt ab sofort alle berechneten Elemente durch solche SCOPEs abzulösen, denn das ganze hat natürlich einen Haken. Der Grund, warum das Ganze funktioniert, nämlich das Berechnen auf unteren Ebenen, hat gleichzeitig negative Auswirkungen auf die Performance. Es können eben nicht mehr vorberechnete Werte aus den Aggregationen oder dem Cache übernommen werden. Bei jeder Abfrage, die sonst nur wenige Zellen zur Berechnung hätte nutzen müssen, muss jetzt unter Umständen ein Vielfaches an Daten berücksichtigt werden, um das gewünschte Ergebnis zu erhalten.
Nichts desto trotz zeigt das Ganze, dass es möglich ist, Berechnungen so zu definieren, dass sie Multiselect-fähig sind, obwohl sie Funktionen wie EXISTING oder CurrentMember benötigen. Wer mit relativ wenig Daten arbeitet, oder wem es nicht gelingt seinen Nutzern das Multiselect in Excel auszureden, der kommt so vielleicht zu einer Lösung.
English
Deutsch
ixto-Blog RSS Feed
) nicht per Default unterdrückt, sondern wären erst mal sichtbar. In den meisten echten Anwendungsfällen ist diese Negativ-Liste oft auch deutlich kürzer als die Positiv-Liste. Aber genug über den Sinn und Unsinn von Negativ-Listen, das Statement