Wie sich in der Zwischenzeit sicher rumgesprochen hat, ist es mit Excel 2010 möglich, Daten in einem Analysis Services Cube zu verändern. Damit besteht also theoretisch die Möglichkeit, das Gespann aus Excel und SQL Server für einfache Planungsprozesse zu verwenden. Ein wichtiges Feature vieler Excel-Addins – alternativer OLAP Server – ist das Verteilen der eingegebenen Daten auf tiefere Ebenen mit unterschiedlichen Gewichtungsmöglichkeiten – das sogenannte Splashing. Meinen Versuch ein Splashing mit Excel Boardmitteln durchzuführen, möchte ich heute teilen.
Getestet habe ich die Fähigkeiten von Excel wie immer auf der Adventure Works, in diesem Fall auf einer mit Writeback versehenen Measure Group der Sales Targets.
Idee ist es, die Zahlen für Q4 zu planen und das möglichst realistisch mit möglichst wenig Arbeit.
Unter “Was-wäre-wenn-Analyse” / Einstellungen in der PivotTable-Tools/Optionen Multifunktionsleiste (Ribbon) findet sich der Dialog zum Einstellen der Zuweisungslogik beim Eingeben von Daten.
Standardmäßig ist hier “Gleiche Zuweisung” gewählt, was bedeutet, dass jedes tiefste Element in den betroffenen Dimensionen den gleichen Wert erhält. Wählt man “Gewichtete Zuweisung”, so wird die Textbox “Gewichtungsausdruck” freigeschaltet, in die man dann einen MDX Ausdruck eingeben kann. Was genau Excel hier erwartet, liegt vielleicht nicht unbedingt auf der Hand.
Nachdem in Excel eine Zelle geändert wurde, wird an die Analysis Services eine UPDATE CUBE Anweisung gesendet, die den Gewichtungsausdruck enthält. Die Analysis Services ermitteln dann alle Blattelemente für die zu ändernde Zelle und führen den Gewichtungsausdruck für jedes der Blätter aus. Der vom Gewichtungsausdruck ermittelte Wert wird dann mit dem zu verteilenden Wert multipliziert, stellt also einen Faktor dar, der zwischen 0 und 1 liegen muss.
Lässt man den Gewichtungsausdruck weg, wird nach der Wertverteilung vor der Änderung gewichtet. Ein Plan-Szenario wie “Erhöhe die bestehende Planung um x€ bei gleichbleibender Verteilung” ist also bereits ohne MDX möglich.
Das Q4 hat nun aber noch keine Werte und wenn ich den neuen Wert z.B. anhand der Werte aus dem vorigen Quartal verteilen möchte, muss ich zu MDX greifen. Eine Variante wäre z.B. das folgende Beispiel:
PARALLELPERIOD([Date].[Calendar].[Calendar Quarter],1,[Date].[Calendar].CURRENTMEMBER)
/
(
PARALLELPERIOD([Date].[Calendar].[Calendar Quarter],1,[Date].[Calendar].CURRENTMEMBER)
, Root([Employee]), Root([Sales Territory])
)
Hier wird mit PARALELPERIOD auf das Vorquartal gegangen und für die aktuellen Zellen der Wert ermittelt. Dieser wird dann durch den Wert aller Mitarbeiter und Verkaufsgebiete im Vorquartal geteilt. Nach Eingabe dieses Ausdrucks kann ich dann auf dem höchsten Level im Q4 einen Wert eingeben und er wird analog dem Q3 verteilt:
Problem an der Sache ist, dass das Ganze jetzt nur auf der höchsten Ebene funktioniert, weil durch die Root-Funktionen im unteren Teil des MDX immer der Faktor in Bezug auf “Alle Mitarbeiter” errechnet wird. Das bedeutet, selbst wenn ich die 5.000 eine Zeile tiefer eingegeben hätte, würde die 67,57 darunter bleiben. Das letzte Element erhält dann die Differenz der bisher verteilten Beträge zu den 5000:
Was mir an dieser Stelle fehlt, ist der Bezug zu der Zelle, in der die Daten eingegeben wurden. Könnte ich diese ermitteln, ließen sich einige Splashing-Verfahren umsetzen. Ohne diese Information ist das ganze leider nur sehr bedingt einsetzbar. Da man den Planern aber auch schlecht zumuten kann, je nach gewünschtem Splashing-Verfahren einen neuen MDX-Ausdruck zu schreiben, wird man wohl weiterhin auf Drittanbietertools setzen müssen.