Dass es zu einem gesuchten Ziel in einer Analysis-Services-Datenbank meist mehrere verschiedene Lösungsansätze gibt, wird wohl jedem, der sich etwas intensiver mit Datenmodellierung auseinandersetzt, bereits bekannt sein. Dass aber diese unterschiedlichen Lösungsansätze einen extremen Laufzeitunterschied haben können (in diesem Beispielfall für den heutigen Blogbeitrag lag diese Zeit zwischen 15 Minuten und 800 Millisekunden), wurde uns vor einigen Wochen klar, als wir uns daran machten, ein Laufzeitproblem bei einem Kunden näher unter die Lupe zu nehmen.
Ausgangspunkt ist eine komplexe Berechnung zur Ermittlung der Planungsgenauigkeit, bei der es darum geht, die vertriebsseitigen Eingaben der monatlichen Produktionsplanung den real ausgelieferten Stückzahlen vier Monate später gegenüber zu stellen und damit die Planungsqualität zu bewerten. Hierfür ist es notwendig, die korrekte Planversion und die zugehörige Snapshotweek1 für die jeweils vier Monate zurückliegende Planungsrunde zu ermitteln. Um diese Kombination herausfinden zu können, wurde eine Hilfs-Measuregroup aufgebaut, die für jede gültige Kombination aus Monat, Snapshotweek und Planversion eine 1 eingetragen bekommt. Hinzu kommt, dass nicht alle Produkte in die Planung mit einbezogen werden und deshalb auf der vierten Ebene der Produktstruktur (Ebene Key-Product mit 571 Elementen) ein Flag „RelevantForPlanning“, das als Attribut hinterlegt ist, in der Berechnung mit berücksichtigt werden muss.
Da das Flag „RelevantForPlanning“ nur auf der Ebene KeyProduct existiert, wurde das ursprüngliche SCOPE-Statement so aufgebaut, dass immer nur auf der Ebene KeyProduct gerechnet wurde:
Für die Berechnung wurde dann über die Hilfs-Measuregroup die für den Liefermonat relevante Planversion und die dazugehörige Snapshotweek ermittelt:
Unter Verwendung einer weiteren Hilfs-Measuregroup, die nur aus der Produkt-Dimension besteht und in der die Measure „RelevantForPlanning_Flag“ auf 1 gesetzt wird, wenn das KeyProduct relevant für die Planung ist, kann durch Multiplikation der Planwert übernommen bzw. ausgeblendet werden:
Bedingt durch diese Kombination, dass im SCOPE-Header auf die Elemente der KeyProduct-Ebene eingeschränkt wird und innerhalb der SCOPE-Berechnung die Abfrage des Hilfscubes erfolgt, wurde die Filter-Anweisung für jedes einzelne Element der KeyProduct-Ebene ausgeführt – im schlechtesten Falle also 571 mal! Dies hatte ewig lange Laufzeiten – bis zu 15 Minuten – zur Folge.
Optimierung
Ein erster Optimierungsversuch war die Ersetzung der Filter()-Funktion, die sich alleine schon durch den vorausgegangenen Umzug von SQL-Server 2005 auf SQL-Server 2008R2 aufdrängte. Die Filter()-Funktion wurde durch NonEmpty() ersetzt:
Damit war die Performance aber noch nicht viel besser und lag immer noch bei bis zu 12 Minuten.
Der Durchbruch gelang mit einem Umbau des Scope-Statements. Dabei wurde die Einschränkung auf die Ebene KeyProduct aus der Subcube-Expression herausgenommen. Innerhalb des Scope wurde über eine Kombination von SUM()- und NonEmpty()-Funktionen das Ergebnis ermittelt:
Damit liegt die Berechnungszeit nur noch bei knapp 800 Millisekunden!
Fazit
Es ist wichtig zu wissen, dass es bei SCOPE-Berechnungen solch riesige Laufzeitunterschiede geben kann, die eine kleine Umstellung erheblich verbessert. Schwierig ist nur, darauf zu kommen, welche (kleine) Umstellung dafür notwendig ist.
Wir hoffen mit diesem Beitrag ein wenig Sensibilität für das Thema geweckt zu haben. In Zukunft sollte kein Scope-Statement, mit einer Einschränkung auf eine bestimmte Dimensionsebene in der Subcube-Expression, einfach so hingenommen werden. Mindestens ist zu prüfen, dass sich dies nicht negativ auf die Performance auswirkt.
