Bei einem international agierenden Kunden haben wir kürzlich eine rollierende Planung implementiert. Einmal pro Monat gibt es bei dem Kunden eine Planungsrunde, in der die letzten Erkenntnisse verarbeitet werden und aus der eine neue Planung entsteht. Es wird sehr langfristig geplant, für die nächsten 3 Jahre auf Monatsbasis und für die nächsten 12 Jahre auf Jahresebene mit linearer Verteilung auf die Monate. Es wird jedoch nicht immer die gesamte Planung angepasst, sondern nur die Regionen und die Monate oder Jahre, in denen neue Erkenntnisse vorliegen.
Der Kunde hatte verschiedene Wünsche für eine möglichst einfache Bedienung in DeltaMaster. Unter anderem sollte es ein Element geben, das für den gesamten Planungszeitraum die jeweils aktuellste Planversion enthält. Die Planversionen selbst sollten aber weiterhin miteinander verglichen werden können, um Änderungen und Planungsqualität beurteilen zu können.
Eine einfache Lösungsmöglichkeit wäre es gewesen, die Daten der einzelnen Planversionen schon in der relationalen Datenbank entsprechend aufzubereiten. Jedoch würden die Daten unnötig verdoppelt werden, was wiederum zu mehr Speicherplatzverbrauch und zu längeren nächtlichen Verarbeitungszeiten führen würde.
Ich hatte die Idee einer rekursiven Umsetzung im Cube-Script. In diesem Fall müssen die Daten nicht verdoppelt werden. Spannend ist die Frage, ob die Rekursion auch mit berechneten Elementen und abgeleiteten Kennzahlen funktioniert.
Rekursion
Eine Rekursion ist eine durch sich selbst definierte Funktion, d.h. eine Funktion ruft sich selbst auf. Auch wenn sich zwei Funktionen gegenseitig aufrufen, wird von einer Rekursion gesprochen. In der Informatik ist eine Rekursion meistens eine elegante Lösung für bestimmte Fragestellungen, z.B. für baumartige Datenstrukturen oder mathematische Fragestellungen. Diese können zwar auch durch iterative Implementierungen umgesetzt werden, aber eine Rekursion ist im Regelfall leichter verständlich. Oft werden Rekursionen schlechte Performance und hoher Speicherverbrauch nachgesagt, aber diese Argumente können in vielen Programmiersprachen leicht entkräftet werden, da Compiler Rekursionen automatisch in effiziente Iterationen übersetzen.
Rekursionen sind sehr einfach aufgebaut: es ist immer mindestens ein rekursiver Aufruf, also ein Aufruf auf sich selbst, mit veränderten Parametern enthalten und eine Bedingung, die die Rekursion beendet.
In einem einfachen Beispiel soll der größte gemeinsame Teiler von 2 Zahlen ermittelt werden:
- ggT mit Rekursion
ggT(x, y) { if (x == y) // Abbruchbedingung return x; if (x > y) return ggt (x - y, y); // 1. Rekursiver Aufruf return ggt (y, x); // 2. Rekursiver Aufruf }
Zum Vergleich die äquivalente Iteration (ebenfalls in einem Funktionsaufruf)
ggT(x, y) {
while (x != y) {
if (x > y)
x = x - y;
else {
t = x;
x = y;
y = t;
}
}
return x;
}
Rekursionen in MDX
In MDX wird eine Rekursion für ein berechnetes Element über die MDX-Ausdrücke PrevMember (vorheriges Element gleicher Ebene) und NextMember gesteuert. Beispielsweise kann über eine Rekursion recht einfach der letzte verfügbare Wert einer Kennzahl ermittelt werden (LastNonEmpty):
CREATE MEMBER [Measures].[Last Value] AS iif ( NOT IsEmpty([Measures].[Stock]) // Abbruchbedingung ,[Measures].[Stock] ,([Measures].[Last Value], [Time].PrevMember) // rekursiver Aufruf ) ;
Leider ist der Aufruf nicht ganz vollständig, da im SQL Server Analysis Services nicht automatisch getestet wird, ob das letzte Element auf einer Ebene in einer Dimension erreicht wurde. Ohne eine zusätzliche Abbruchbedingung könnte die Rekursion ins Leere laufen und damit zu einer Endlosrekursion werden.
CREATE MEMBER [Measures].[Last Value] AS iif ( NOT IsEmpty([Measures].[Stock]) // Abbruchbedingung ,[Measures].[Stock] ,iif( [Time].PrevMember IS NULL // Ende erreicht? ,NULL ,([Measures].[Last Value], [Time].PrevMember) // rekursiver Aufruf ) ) ;
Lösung der Fragestellung
Für die in der Einleitung geschilderte Anforderung an ein Element, das immer den aktuellsten Planungsstand über alle Länder und den gesamten Planungszeitraum – auch für die bereits vergangenen Monate – enthält, sind wir wie folgt vorgegangen.
Festlegungen:
- die Wertart „3“ enthält Plandaten
- die aktuellste Planungsrunde ist auf dem Element „ActualForecast“ in der Dimension „PlanningRound“ festgelegt
- die einzelnen Planungsrunden sind unter dem Knoten „SalesPlan“ in der Dimension „PlanningRound“ abgelegt
- die Measure ist „Value1“
Schritt 1 – Scope:
- ein Scope ist genau mit den vorher genannten Elementen definiert
- Innerhalb des Scopes wird als erstes mit IsEmpty geprüft, ob nicht schon in der aktuellsten Planungsrunde Werte zur Verfügung stehen. Ist dies der Fall, dann wird der Wert übernommen und es sind keine weiteren Schritte notwendig.
- Hinweis: Da es vorkommen kann, dass bestimmte Elemente nicht mehr geplant werden, z.B. Produkte, die nicht mehr produziert werden, findet eine Prüfung auf einer aggregierten Ebene statt, die auf jeden Fall einen Wert enthält. Andernfalls würde die Rekursion so lange die Planversionen durchsuchen, bis ein Wert gefunden wird oder das Ende der Rekursion erreicht ist. Das wiederum hätte zur Folge, dass Werte auf Knoten nicht mehr die Summe der untergeordneten Elemente ergeben.
- Wurde kein Wert gefunden, wird im letzten Schritt die Rekursion gestartet. Dazu wird eine Hilfsmeasure SYSTEM_ActualForecast mit der vorherigen Planungsrunde Lag(1) aufgerufen.
// PlanningRound "Actual" soll immer den letzten Forcast enthalten // Rekursion über [Measures].[SYSTEM_ActualForecast] SCOPE( [Measures].[Value1] ,[PlanningRound].[PlanningRound].&[ActualForecast] ,[ValueType].[ValueType].[ValueType].&[3] ); THIS = iif( // Prüfen, ob die aktuellste Planungsrunde gefüllt ist NOT isEmpty( ( [Measures].[Value1] ,[PlanningRound].[PlanningRound].&[SalesPlan].LastChild ,[Produkt].[All Products] // Prüfung auf dem All-Member ) ) // Wenn die Planungsrunde gefüllt ist, dann ist hier schon Ende , ( [Measures].[Value1] ,[PlanningRound].[PlanningRound].&[SalesPlan].LastChild ) // Sonst wird die Rekursion gestartet , ( [Measures].[SYSTEM_ActualForecast] ,[PlanningRound].[PlanningRound].&[SalesPlan].LastChild.Lag(1) ) ) ; END SCOPE;
Schritt 2 – Rekursion:
- als erstes wird wieder geprüft, ob auf der aktuellen Planungsrunde bereits Daten vorhanden sind
- genauso wie im Scope wird der Wert zurückgegeben, wenn dieser vorhanden ist
- andernfalls wird geprüft, ob die letzte Planungsrunde bereits erreicht ist
- ist dies nicht der Fall, ruft sich die Hilfsmeasure mit dem vorherigen Element selbst auf
// REKURSION CREATE MEMBER CURRENTCUBE.[Measures].[SYSTEM_ActualForecast] AS // Prüfung, ob diese Planungsrunde im aktuellen Datenraum gefüllt ist iif( NOT isEmpty ( [Measures].[Value1] ,[PlanningRound].CURRENTMEMBER ,[Produkt].[All Products] // Prüfung auf dem All-Member ) , ( [Measures].[Value1] ,[PlanningRound].CURRENTMEMBER ) // Bei leerem Wert REKURSION (PREVMEMBER) starten ,iif( [PlanningRound].CURRENTMEMBER.PREVMEMBER IS NULL ,NULL // Abbruch nachdem die letzte Planungsperiode erreicht wurde , ( [Measures].[SYSTEM_ActualForecast] ,[PlanningRound].CURRENTMEMBER.PREVMEMBER // Rekursion ) ) ) ,VISIBLE = 0 // Unsichtbar, da nicht direkt aufgerufen werden soll ,ASSOCIATED_MEASURE_GROUP = 'Sales'; // MsrGrp zuordnen (übersichtlicher)
Probleme
Folgende Schwierigkeiten sind uns begegnet.
Implizite Rekursion
Bei unvorsichtiger Programmierung kann es passieren, dass eine Rekursion unbeabsichtigt aufgerufen wird. Die nachfolgende Anweisung ist identisch mit der Anweisung aus dem Beispiel von Abschnitt 3. Der Unterschied besteht darin, dass die Measure nicht explizit genannt wurde. Implizit wird in dem Tupel ([Time].PrevMember) die aktuelle Measure verwendet:
WITH MEMBER [Measures].[Last Value] AS iif ( NOT IsEmpty([Measures].[Stock]) // Abbruchbedingung ,[Measures].[Stock] ,iif( [Time].PrevMember IS NULL ,NULL ,([Time].PrevMember) // impliziter rekursiver Aufruf ) )
Darüber hinaus sind implizite Rekursionen sehr schwer zu lesen oder gar nicht erst zu erkennen. Darum empfehlen wir Rekursionen immer explizit anzugeben und entsprechend zu kommentieren.
Block Mode vs. Cell-by-Cell Mode
SSAS versucht im Regelfall MDX-Statements im schnellen Block Mode zu verarbeiten. Leider kann es bei ungünstiger Programmierung oder großen Abfragemengen dazu kommen, dass der Block Mode nicht verwendet werden kann. Leider ist es nicht möglich, anhand des Statements zu sagen, welcher Modus verwendet wird. Aufschluss über den verwendeten Modus geben MDX-Ausführungspläne. Außerdem sollten Aufrufe von CLR Assemblys, z.B. VBA-Funktionsaufrufe, vermieden werden, da dann immer der Cell-by-Cell Mode verwendet wird (gilt generell für MDX).
Weitere Informationen: http://mdxdax.blogspot.de/2011/04/performance-considerations-for.html
Fazit
Rekursionen sind eine elegante Möglichkeit bestimmte Fragestellungen in MDX zu lösen. Wir hoffen, dass wir einen Einblick in MDX-Rekursionen geben konnten.
Quellen
http://de.wikipedia.org/wiki/Rekursion
http://technet.microsoft.com/de-de/library/ms145977.aspx
http://technet.microsoft.com/de-de/library/ms144719.aspx
http://mdxdax.blogspot.de/2011/04/performance-considerations-for.html
http://technet.microsoft.com/en-us/library/aa937471%28v=sql.80%29.aspx
MDX Solutions, Second Edition: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase, 2006
