Immer wieder werden wir bei Kunden mit dem Wunsch konfrontiert, dass in Ihrer Planungsanwendung zunächst die Daten auf Jahresebene erfasst und später auf die Monate heruntergebrochen werden sollen. Die Verteilungslogik soll natürlich online rechnen und die Ergebnisse unmittelbar sichtbar werden. Das große Ziel ist selbstverständlich, dass die Summe der Jahresplanung am Ende der Summe der Monatsplanung entspricht. Um dies zu erreichen muss für jeden Jahreswert ein korrekter Verteilungsschlüssel existieren.
Kein Problem, wäre da nicht ein unberechenbarer Faktor, der das System ins Wanken bringt: der Planer…
Wie wir es schaffen, mit fehlenden Verteilungsschlüsseln umzugehen, und dies ohne Batch-Verarbeitung schauen wir uns in nachfolgendem Artikel an.
Wie man eine Verteilungslogik ins Wanken bringt
Die Monatsverteilung ist fast schon eine Standardanforderung an Planungssysteme. Ebenso das Arbeiten mit Vorschlagswerten. Beide Anforderungen dienen primär der Arbeitserleichterung für den geschundenen Planer, der ohnehin viele Daten zu erfassen hat.
Die übliche Architektur für solche Anforderungen ist eine Online-Verteilung der Jahreswerte auf Monate per MDX-Script im Würfel, sowie die initiale Befüllung der Würfel mit Vorschlagswerten. Die Vorschlagswerte werden in der Regel relational vorberechnet und einmalig zu Beginn der Planung in die OLAP-Datenbank geladen. Nehmen wir für unseren Beispielfall an, dass wir als Vorschlagswert die Istwerte des letzten Jahres verwenden und diese um 10% als globale Vorgabe erhöhen. Damit würde sich zu Beginn des Planungsstarts folgendes Bild ergeben:
Neben den absoluten Werten des Jahresumsatzes wurden auch basierend auf der Ist-Umsatzverteilung die entsprechenden Monatsanteile generiert. Durch Ergänzung eines MDX-Scripts im Würfel kann durch Multiplikation der beiden Basisgrößen einfach der Monatsumsatz errechnet werden:
-- Monatsverteilung einfach SCOPE( [Kunde].[Kunde].[Kunde].Members ,[Periode].[Periode].[Monat].Members ,[Produkt].[Produkt].[Produkt].Members ); [Measures].[Umsatz_Monat] = [Measures].[Umsatz_Jahr] * [Measures].[AnteilMonat]; END SCOPE;
Damit ergibt sich ein stimmiges Bild und der Jahresumsatz entspricht dem Monatsumsatz:
Ändert der Anwender jetzt einen Jahresumsatz wird dieser automatisch über die Anteile korrekt auf den Monat heruntergebrochen.
Das große Problem trifft uns jetzt aber, wenn der Planer auf die abstruse Idee kommt, eine neue Kunden-Produkt-Kombination zu planen, die bisher im Ist keine Werte hatte. Nehmen wir in unserem Beispiel an, der Vertrieb möchte eine Marketingoffensive starten und mehr Minis verkaufen. Die Planung wird wie folgt geändert:
Damit ergibt sich jetzt folgendes Bild im Vergleich des Jahresumsatzes mit dem Monatsumsatz:
Da die Monatsanteile fehlen, wird kein Monatsumsatz berechnet. Zwei Aufgaben sind zu lösen:
- Der unwahrscheinliche Fall, dass der Planer komplett vergisst, die Monatsanteile zu erfassen, muss abgefangen werden.
- Will der Planer die Monatsanteile doch erfassen, soll ihm eine Standardverteilung von 1/12 vorgeschlagen werden, die er dann einfach anpassen kann. Die Eingabe soll unmittelbar nach der Eingabe der Jahresumsätze erfolgen können.
Den Fall 1 könnte man durch einen Batch-Prozess lösen, der nachts alle Umsatz-Kombinationen ohne Monatsanteil prüft und dort eine Standardverteilung generiert. Sogar der erste Teil von Fall 2 wäre damit gelöst. Nicht jedoch die Anforderung, dass die neuen Vorschlagswerte unmittelbar nach Erfassung der Jahresumsätze zur Verfügung stehen.
Folglich müssen wir etwas kreativ werden…
Wie es nicht funktioniert
Die erste Idee ist schnell gefunden. Da die Berechnung der Monatsumsätze ja online im Würfelskript durchgeführt wird und in dem beschriebenen Fall lediglich die Anteile fehlen, muss man ja nur dafür sorgen, dass diese entsprechend generiert werden. Und welcher Platz würde sich dafür besser anbieten als auch das Würfelskript. Den Fall zu identifizieren ist recht leicht. Immer wenn ein Jahresumsatz vorhanden ist, aber auf dem gesamten Jahr kein Monatsanteil vorhanden ist, soll der automatische 1/12-Anteil greifen. Der 1/12-Wert steht dabei auf einer weiteren Kennzahl „AnteilMonat_GlobaleVorgabe“ im Datenmodell. Das Script erweitern wir folgendermaßen:
-- Berechnung Monatsanteile wenn Jahresumsatz ohne Verteilungsschlüssel vorhanden SCOPE( [Kunde].[Kunde].[Kunde].Members ,[Periode].[Periode].[Monat].Members ,[Produkt].[Produkt].[Produkt].Members ); [Measures].[AnteilMonat] = iif( --Jahresumsatz <> 0 (Ancestor([Periode].[Periode].CurrentMember, [Periode].[Periode].[Jahr]) ,[Measures].[Umsatz_Jahr]) <> 0 AND --Monatsverteilung auf Jahresebene = 0 (Ancestor([Periode].[Periode].CurrentMember, [Periode].[Periode].[Jahr]) ,[Measures].[AnteilMonat]) = 0 --Dann globaler Vorschlagswert ,[Measures].[AnteilMonat_GlobaleVorgabe] --Ansonsten Normalen Anteil zeigen ,[Measures].[AnteilMonat] ); END SCOPE;
Die Prüfung des Ergebnisses auf dem Übersichtsbericht ist vielversprechend. Sowohl der neue Monatsanteil ist in unseren betrachteten Fällen gefüllt, als auch der Monatsumsatz mit dem korrekten Jahreswert. Die Abweichungen betragen 0:
Die Überschrift dieses Absatzes wäre aber nicht die Überschrift dieses Absatzes, wenn wir schon am Ziel wären. Wo könnte also noch ein Problem liegen? Genau, beim Verändern der Werte durch den Anwender – ja, da ist er wieder…
Was passiert, wenn wir eine Zahl verändern? Wir würden erwarten, dass unsere Eingabe einfach übernommen wird, wie dies der SQL-Server bei einer Planungsapplikation für gewöhnlich tut. Nehmen wir einen Bericht mit den Monatsanteilen her und verändern die Standardverteilung im April von 8,3% auf 10%. Würde man jetzt Wetten bzgl. des Ergebnisses in Empfang nehmen, müssten wir den Artikel vermutlich nicht mehr fertig verfassen – was wäre Ihr Tipp?
1,7%… Wie um alles in der Welt kommt der Server auf die Idee, aus unserer 10 eine 1,7 zu machen? Ganz einfach! Weil er denselben Algorithmus immer anwendet. In seiner Zelle stand zuvor eine 8,3% (diese haben wir per Scripting dort hingebracht), dann wurde eine 10% eingegeben und genau diese wurde an den Server übergeben (prüfen Sie das mdx.log, wenn Sie das nicht glauben). Da die 8,3% aber auf eben jener serverbasierten Kennzahl generiert wurden, die wir gerade ändern, wendet der Server seine übliche Deltalogik an und schreibt exakt die Erhöhung um 1,7% weg. Folglich kann die Lösung nicht in der Manipulation der serverbasierten Kennzahl liegen, die wir für die Berechnung verwenden.
Wie es besser funktioniert
Wie gut, dass exzellente BI-Frontends auch das Anlegen von clientbasierten Kennzahlen erlauben. Legen wir uns einen neuen Monatsanteil an, der genau die Logik nachbildet, die wir gerade im Server aufgebaut haben. Die Kennzahl sollte folgendermaßen aussehen:
Weiterhin muss auf der Kennzahl eine Wertweitergabe auf „AnzahlMonat“ eingerichtet werden, da auf dem berechneten Wert natürlich keine Daten auf dem Server gespeichert werden können. Als „MDX-Ausdruck für den Zielwert“ der Wertweitergabe verwenden Sie bitte „#new“ und Unterdrücken die Veränderung des berechneten Werts selbst.
Prüfen wir nun unseren Übersichtsbericht, sehen wir allerdings zunächst nur einen Teil des gewünschten Ergebnisses. Der Monatsanteil berechnet sich zwar in unseren Sonderfällen, der Monatsumsatz allerdings noch nicht:
Das kann er auch nicht, weil die serverbasierte Kennzahl „AnzahlMonat“ ja nun nicht mehr gefüllt ist. Wir haben den Anteil ja nur auf dem Client generiert. Da wir den Monatsumsatz immer nur ausrechnen und nie eingeben wollen, können wir diesen bedenkenlos auf Serverseite berechnen. Dafür wenden wir quasi das gleiche MDX-Script auf den Monatsumsatz an und multiplizieren im „Normalfall“ mit der Kennzahl „AnteilMonat“ und wenn diese nicht gefüllt ist, greifen wir auf die globale Vorgabe zurück:
--Monatsverteilung mit Vorschlagswertberücksichtigung SCOPE( [Kunde].[Kunde].[Kunde].Members ,[Periode].[Periode].[Monat].Members ,[Produkt].[Produkt].[Produkt].Members ); [Measures].[Umsatz_Monat] = iif( ( Ancestor([Periode].[Periode].CurrentMember, [Periode].[Periode].[Jahr]) ,[Measures].[Umsatz_Jahr] ) <> 0 AND isempty( ( Ancestor([Periode].[Periode].CurrentMember, [Periode].[Periode].[Jahr]) ,[Measures].[AnteilMonat] ) ) , [Measures].[Umsatz_Jahr] * [Measures].[AnteilMonat_GlobaleVorgabe] , [Measures].[Umsatz_Jahr] * [Measures].[AnteilMonat] ); END SCOPE;
Prüft man das Ergebnis erneut, erhellt sich die Miene des geschundenen BI-Beraters. Doch Vorsicht, so weit waren wir schon einmal:
Wie verhält sich nun die Eingabe, wenn wir wieder versuchen, den April auf 10% zu verändern? Wird sich a) gar nichts ändern oder b) das Delta weggeschrieben oder c) das richtige Ergebnis präsentieren?
Richtig ist Antwort… na welche eigentlich? Eigentlich gar keine. Das richtige Ergebnis sieht auch anders aus. Die 10% wurden zwar diesmal ordnungsgemäß zurückgeschrieben, da der Server erkannt hat, dass seine Daten vorher noch leer waren, aber alle anderen elf Monate verschwinden wie von Geisterhand. Ein verwirrter Anwender dürfte in jedem Fall die Folge sein.
Wie es richtig gut funktioniert
Hat schon jemand eine Idee? Die ausgefeilten Planungsfunktionen von DeltaMaster tragen entscheidend zur optimalen Lösung bei. In dem Fall die Fixierung. Wir aktivieren in den Tabelleneigenschaften die automatische Fixierung von 100%-Werten, sowie die Fixierung entlang der Dimension „Periode“. Anschließend lassen wir auch für unsere neue, berechnete Kennzahl die Fixierung zu. Geben wir nun auf unserem bisher unveränderten „Kunde 3“ wieder den Wert 10% auf dem Monat April ein, erleben wir ein Wunder der BI-Technik, das selbst gestandene BI-Berater staunen lässt:
Die 10% wurden erfasst und die restlichen Monate wurden anteilig so weit verringert, dass die 100% auf Jahressumme erhalten bleiben. Das für sich ist aber für den DeltaMaster-Anwender fast schon langweilig. Warum also spreche ich von einem Wunder? Das fällt einem erst auf den zweiten Blick auf. Wir erinnern uns, dass wir keinen Wert auf unserem berechneten Anteil hatten, da die Kombinatorik komplett neu erfasst wurde. Weiterhin hat der Anwender genau einen Wert, die 10%, erfasst. Werfen Sie nun mal einen Blick in das mdx.log…
Ist Ihnen etwas aufgefallen? Genau, die Kombination aus Fixierung und Wertweitergabe hat dafür gesorgt, dass unsere komplette Monatsreihe materialisiert wurde! Der Anwender hat also auf einem virtuellen Element eingegeben und DeltaMaster hat im Hintergrund zwölf Werte weggeschrieben und so die komplette Monatsverteilung für unsere neue Kombinatorik generiert. Und das ohne Batch-Prozess oder relationale Nachbearbeitung – quasi on Demand. Wenn das kein kleines Wunder ist…
In dem Sinne: Scotty – Materialisieren!
