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_FACTEingabe 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.
