In diesem Blog geht es um die Abbildung von Menge, Preis und Umsatz in der Vertriebsplanung. Er behandelt insbesondere den Umgang mit (nachträglichen) Eingaben auf verdichteten, berechneten Werten übergeordneter Dimensionsebenen, z. B. Kundengruppen oder Gesamtjahr, und die korrekte Neuberechnung der davon abhängigen Kennzahlen.

In der Vertriebsplanung geht es im Wesentlichen darum, den künftigen Umsatz anhand von geplanten Mengen und Preisen zu berechnen. Normalerweise sind Menge und Preis Basis-Measures. In DeltaMaster wird der Umsatz dann per MDX im Cubescript berechnet.

Häufig geforderte Features sind beispielsweise die pauschale Preiserhöhung, gegebenenfalls auf verdichteter Ebene, oder die generelle Erhöhung der Menge für bestimmte Vertriebsgebiete über alle weiteren Dimensionen. Diese Funktionen sind standardmäßig in DeltaMaster enthalten und mit Bordmitteln lösbar. Im Nachgang kommt es allerdings oft dazu, dass die Bereichsleitung oder die Geschäftsführung den Umsatz auf verdichteter Ebene anpasst. Bei der Rückrechnung auf die geplanten Mengen und Preise stellt sich die Frage, welche der beiden Measures sich ändern soll.

Nehmen wir an, die Preise sollen fix bleiben und die Mengen sollen angepasst werden. Durch die Flexibilität bei den Eingaben auf höheren Ebenen scheidet ein reiner Cubescript-Ansatz aus – innerhalb der Rückschreibeprozesse müssen der Umsatz und gegebenenfalls auch die Menge neu berechnet werden. Einen Überblick über die gesamte Thematik und Lösungen zu dem beschriebenen Problem geben wir im Folgenden.

Ausgangssituation

Für unser Beispiel nutzen wir die Demo-Daten unseres fiktiven Stühle-Herstellers aus Nürnberg, der Chair AG, als Basis für die Vertriebsplanung. Geplant werden die Kennzahlen Menge, Preis und Umsatz auf den Dimensionen Kunde, Artikel und Periode.

Abbildung 1: Measure-Gruppe Vertriebsplanung

Abbildung 2: Measures Vertriebsplanung

Standardmäßig werden die Werte innerhalb der Dimensionen auf verdichteter Ebene (z. B. Jahr, Produktgruppe, Kundengruppe) summiert. Für Preise ist diese Darstellung nicht sinnvoll. Den Preis auf verdichteter Ebene anzuzeigen, bilden wir einen mengengewichteten Durchschnittspreis im Cubescript. Man könnte auch das vorhandene Preis-Measure im Cubesript auf höheren Ebenen per Scope überschreiben, wir legen aber zur Übersichtlichkeit ein neues Measure „Preis_R“ an.

CREATE MEMBER CURRENTCUBE.[Measures].[Preis_R]
AS
    NULL,
VISIBLE = 1;


SCOPE([Measures].[Preis_R]);

     THIS= IIF ([Measures].[Menge] = 0, NULL, [Measures].[Umsatz]/[Measures].[Menge]);
END SCOPE;

Die Daten für die Wertart „Plan“ werden über eine Prozedur vorbefüllt, damit nicht in den leeren Raum gesplasht werden muss und sich sinnvolle Verteilungen gemäß den Ist-Daten ergeben.

Damit sind alle Vorarbeiten erledigt und die aktuelle Planungsrunde mit DeltaMaster kann beginnen.

Theorie: hybride Writeback-Prozeduren

Die Logik der Writeback-Prozeduren pro Faktentabelle ist in die drei Schritte aufgeteilt: PreProcess, Process und PostProcess.

Abbildung 3: Writeback-Prozeduren

Während die Process-Routine bei jedem „Create relational schema“ neu generiert wird, werden die PreProcess- und die PostProcess-Routinen einmalig von DeltaMaster-ETL erzeugt, mit den entsprechenden Übergabeparametern versehen und können danach individuell angepasst werden.

Abbildung 4: Writeback PreProcess

Einstellungen in DeltaMaster

Zunächst richten wir in DeltaMaster eine Wertweiterleitung ein, damit die Daten für den Preis an die korrekte Stelle geschrieben werden. Zur Erinnerung: der im Report angezeigte Preis (Preis_R) ist der gewichtete Durchschnittspreis aus dem Cubescript.

Abbildung 5: Wertweiterleitung Preis Report (Preis_R) auf Measure Preis

Der Preis muss auf die Basiselemente der jeweiligen Dimension (Artikel, Kunde, Periode) geschrieben werden, nur dort ist die Berechnung des Umsatzes aus Menge x Preis korrekt. Auf verdichteter Ebene darf der Umsatz nur summiert und nicht mehr berechnet werden.

Da der Umsatz auf verdichteter Ebene überschreibbar sein soll, ist es notwendig, nach dem Schreiben der Basiswerte in die Writeback-Tabelle auch die abhängige Kennzahl (Menge) bei fixem Preis neu zu berechnen und in die Tabelle zu schreiben.

Umsetzung im Writeback

Wir wollen die notwendigen Berechnungen nun über eine Erweiterung der PostProcess-Routine realisieren: Die eingegebenen Daten müssen im ersten Schritt in der Writeback-Tabelle gespeichert werden, bevor darauf zugegriffen wird. Durch diesen Schritt werden die verdichteten Eingaben in die einzelnen Basis-Elemente der Dimensionen aufgelöst. Dies geschieht in der standardmäßigen Prozedur [P_WriteBackSQL_FACT_01_Vertriebsplanung].

Nun müssen wir nur noch herausfinden, welche Datensätze in der Writeback-Tabelle geändert wurden, und können diese dann nachträglich anpassen. Dazu gibt es das eindeutige Feld [TransactionID], welches von DeltaMaster an die Schreibprozeduren übergeben wird.

Die Logik und die einzelnen Übergabeparameter können in den Log-Dateien von DeltaMaster, hier dem „sql.log“, nachgesehen werden.

Abbildung 6: Auszug sql.log

Über die mitgelieferte Transaction-ID können alle in dem Schreibvorgang betroffenen Datensätze eindeutig identifiziert werden. Wir erweitern also die Writeback_PostProcess-Prozedur dahingehend, dass alle notwendigen Datensätze in eine temporäre Tabelle geschrieben werden. Über ein Merge wird ein gezieltes Update auf die Writeback-Tabelle und die abhängigen Kennzahlen durchgeführt.

if object_id('tempdb..#T_WB_FACT') IS NOT NULL DROP TABLE #T_WB_FACT

	-- Quell Tabelle erzeugen
	CREATE TABLE #T_WB_FACT (
		[MonatID]			varchar(50)		 NOT NULL,
		[WertartID]		varchar(50)		 NOT NULL,
		[PeriodenansichtID]		varchar(50)		 NOT NULL,
		[KumulationID]		varchar(50)		 NOT NULL,
		[KundeID]			varchar(50)		 NOT NULL,
		[ProduktID]		varchar(50)		 NOT NULL,
		[Menge]			int			 NULL,
		[Preis]			decimal (18,7)	 	 NULL,
		[Umsatz]			decimal (18,7)   		 NULL,
		[SourceID]		int			 NULL,
		[TransactionID]		uniqueidentifier 		 NULL,
		[ChangeCount]		int			 NULL
	);
		
		-- Tabelle befüllen
		INSERT INTO #T_WB_FACT
		(
				[MonatID],
				[WertartID],
				[PeriodenansichtID],
				[KumulationID],
				[KundeID],
				[ProduktID],
				[Menge],
				[Preis],
				[Umsatz],
				[SourceID],
				[TransactionID],
				[ChangeCount]
		)
		SELECT
				wb.[MonatID],
				wb.[WertartID],
				wb.[PeriodenansichtID],
				wb.[KumulationID],
				wb.[KundeID],
				wb.[ProduktID],
				wb.[Menge],
				wb.[Preis],
				wb.[Umsatz],
				wb.[SourceID],
				wb.TransactionID,
				isnull(wb.ChangeCount, 0) ChangeCount
		FROM
			T_WriteBackSQL_FACT_01_Vertriebsplanung wb 
		WHERE
			wb.[TransactionID] = @TransactionID

Die eigentliche Berechnung erfolgt im zweiten Teil der Prozedur.

-- Berechnung abhängiger Kennzahlen
		MERGE INTO T_WriteBackSQL_FACT_01_Vertriebsplanung tgt

		USING #T_WB_FACT AS src
		ON
			tgt.[MonatID]	    = src.[MonatID] AND
			tgt.[WertartID]	    = src.[WertartID] AND
			tgt.[PeriodenansichtID] = src.[PeriodenansichtID] AND
			tgt.[KumulationID]	    = src.[KumulationID] AND
			tgt.[KundeID]	    = src.[KundeID] AND
			tgt.[ProduktID]	    = src.[ProduktID] AND
			tgt.TransactionID	    = src.TransactionID
		
		WHEN MATCHED THEN
			UPDATE
				SET 			
					tgt.[Umsatz] = CASE
							WHEN @MeasureName =  'Umsatz'THEN src.[Umsatz]
							WHEN @MeasureName =  'Preis' THEN src.[Preis] * src.[Menge]
							WHEN @MeasureName =  'Menge' THEN src.[Preis] * src.[Menge]
							ELSE src.[Umsatz]
						     END,
					tgt.[Menge]  = CASE 
							WHEN @MeasureName =  'Umsatz' THEN src.[Umsatz] / src.[Preis]
							ELSE src.[Menge]
						     END,

					tgt.[SourceID]    = isnull(src.[SourceID] ,4),
					tgt.[ChangeCount] = isnull(src.[ChangeCount],0)+1
		;
				
	-- Temp Tabelle löschen
	if object_id('tempdb..#T_WB_FACT') IS NOT NULL DROP TABLE #T_WB_FACT

Eingabe in DeltaMaster

In der Eingabe-Anwendung zur Vertriebsplanung können nun auf beliebigen Ebenen und für alle angebotenen Kennzahlen flexibel Daten eingegeben werden. Sämtliche Eingaben lösen über die modifizierte PostProcess-Prozedur eine Berechnung der abhängigen Kennzahlen aus. Wir nehmen als Beispiel die Eingabe auf der Produktgruppe „Arcade“ an.

Abbildung 7: Ausgangslage in DeltaMaster

Die Anpassung der Menge löst direkt eine Neuberechnung des Umsatzes aus.

Abbildung 8: Anpassung Menge

Alternativ sollte bei einer Preiserhöhung (z. B. + 100 %) der Umsatz bei gleichbleibender Menge verdoppelt werden.

Abbildung 9: Anpassung Preis

Die direkte Anpassung des Umsatzes soll definitionsgemäß bei fixem Preis die Menge anpassen.

Abbildung 10: Anpassung Umsatz

Mit dieser Lösung haben wir eine flexible Erweiterung der Standardmöglichkeiten für Zwischenberechnungen oder spezielle Anforderungen innerhalb der Hybridplanung gefunden.

Planen in DeltaMaster

Sie möchten mehr erfahren über Budgetierung, Planung und Forecasting in DeltaMaster? In diesem Webinar zeigen wir, wie Sie Ihre Planung in kürzester Zeit erledigen, den Prozess zentral steuern und alle Abläufe überwachen – kompakt in 30 Minuten.