In diesem Beitrag veranschaulichen wir anhand der Wertfortschreibung, wie Custom Operator in DeltaMaster funktioniert. Die Wertfortschreibung ist Teil der Hybridplanung und bietet die Möglichkeit, neben den standardisierten Eingabeoperatoren auch individuelle Operatoren (Custom Operator) zu definieren. Für die Umsetzung der Wertfortschreibung beinhaltet dieser Blog auch eine neue Prozedur, die es ermöglicht, bei der Hybridplanung zu jedem Zeitpunkt (PreProcess/PostProcess) den Planungsdatenraum auszugeben.

Mit dem DeltaMaster-Release 6.3.5 Anfang 2021 ging eine Erweiterung der Funktionalität in der Hybridplanung einher: Anwendungen, die auf der Hybrid-Architektur basieren, können seitdem neben den Standard-Operatoren wie z.B. „+“ oder „%+“ auch individuelle Operatoren nutzen. Sie werden projektseitig auf Kundenwunsch implementiert und festgelegt.

Damit DeltaMaster einen Custom Operator erkennt, wird das Rauten-Symbol (#) als Erkennungszeichen genutzt. Mit dieser Integration können kundenindividuelle Planungsanwendungen umgesetzt werden, in denen der Kunde selbst den Custom Operator frei verwendet. Der Planungsprozess mit DeltaMaster lässt sich dadurch auch bei individuellen Anforderungen von Unternehmen einfach etablieren.

Wir zeigen die Definition und Umsetzung eines neuen Custom Operators und einer Prozedur anhand eines Beispiels bei der Wertfortschreibung. Diese ermöglicht eine komfortable Planung von sich über den Zeitverlauf nicht ändernden Werten.

Custom Operator

Mit der Einführung des Custom Operators wurde DeltaMaster so angepasst, dass das Erkennungszeichen „#“ bei einer Dateneingabe im Frontend verarbeitet werden kann. Darüber hinaus wurden die Rückschreibeprozeduren der Hybridplanung unter der sprichwörtlichen Motorhaube um den Custom Operator ergänzt. Damit kann in den Vor- und Nachverarbeitungsprozeduren (…_PreProcess/…_PostProcess) die Eingabe auf einen Custom Operator hin geprüft und anschließend die nötigen individuellen Prozesse angestoßen werden. Der neue Parameter @CustomOperator enthält dabei die individuell festzulegende Text- bzw. Zeichenfolge nach dem #-Symbol. Somit würde die beispielhafte Eingabe „300#tue_irgendwas“ im @CustomOperator-Parameter der Pre- und PostProcess-Prozeduren als „tue_irgendwas“ ankommen, worauf dann in den Prozeduren geprüft werden kann.

Zum Beispiel per:

IF @CustomOperator = „tue_irgendwas“ 
BEGIN 
--Individueller Code, der ausgeführt werden soll 

END

Abb. 1: PreProcess-Prozedur mit dem Parameter „CustomOperator“

Dataview-Prozedur

Aus dem Umsetzungsgedanken der Wertfortschreibung per Custom Operator hat sich eine potenzielle Erweiterung des Werkzeugkastens der Hybridplanung ergeben. Denn um im PreProcess herauszufinden, welche Datensätze von der Eingabe betroffen sind, musste man sich bisher selbst behelfen – beispielsweise mit dem Aufbau eines dynamischen SELECT-Statements, das eine Filterung auf jeder Ebene der Dimensionen ermöglicht und über die übergebenen Parameter eingeschränkt wird.

Die Dataview-Prozedur schließt diese Lücke und gibt in einer temporären Tabelle die Datensätze aus, die von der Eingabe aktuell betroffen sind. Die Dataview-Prozedur verfügt dabei über die gleichen Parameter, wie die P_Writeback-Prozeduren, wodurch die aktuellen Parameter einfach an die neue Prozedur innerhalb der PreProcess/PostProcess-Prozeduren übergeben werden können. Damit ist es auch möglich, die übergebenen Parameter anzupassen und einen beliebigen Datenraum abzufragen. Anschließend lassen sich die Datensätze über die temporäre Tabelle auslesen, die durch die Prozedur erstellt wird (im Namensschema: ##T_FACT_…_DataView).

Die Anwendungsgebiete der Dataview-Prozedur sind damit so umfangreich, wie die Vor- und Nachverarbeitungsprozesse (Pre- und PostProcess) selbst. Im Fall der Wertfortschreibung besteht der Nutzen darin, herauszufinden, was aktuell in den von der Fortschreibung betroffenen Monaten wertmäßig steht.

Zur Generierung der Dataview-Prozedur gibt es wiederum eine Prozedur, die diese anhand der Hybridplanungsroutine „P_WritebackSQL_FACT_…“ erstellt. Dazu müssen der Prozedur diese Parameter mitgegeben werden. Das Script zur Erstellung der Generierungsprozedur ist das folgende:

CREATE PROC [dbo].[P_WriteBackSQL_FACT_Create_DataView_Proc] (@ProcName varchar(500)) AS

DECLARE
@ProcDefinition varchar(max),
@Hybridversion varchar(500),
@OLD_TempTableName varchar(500),
@NEW_TempTableName varchar(500),
@NEW_WB_ObjectName varchar(500),
@OLD_WB_ObjectName varchar(500)

--Init
SET @ProcDefinition = ( SELECT OBJECT_DEFINITION( Object_ID(@ProcName)) )

SET @Hybridversion = LTRIM(RTRIM(SUBSTRING(@ProcDefinition,CHARINDEX('--CREATED BY DeltaMaster ETL',@ProcDefinition)+33,7)))

IF @Hybridversion = 'Delta-'
BEGIN
SET @OLD_WB_ObjectName	= REPLACE(@ProcName,'P_WriteBackSQL', 'T_WriteBackSQL') + '_DELTA wb'
SET @NEW_WB_ObjectName	= REPLACE(@ProcName,'P_WriteBackSQL', 'V_WriteBackSQL') + '_UNION wb'
END
ELSE
BEGIN
SET @OLD_WB_ObjectName	= REPLACE(@ProcName,'P_WriteBackSQL', 'T_WriteBackSQL') + ' wb'
SET @NEW_WB_ObjectName	= REPLACE(@ProcName,'P_WriteBackSQL', 'T_WriteBackSQL') + ' wb'
END

SET @OLD_TempTableName = '#T'	+ SUBSTRING(@ProcName,15,8)
SET @NEW_TempTableName = '##T'	+ SUBSTRING(@ProcName,15,8) + '_Dataview'


-- Schritt 1: CREATE OR ALTER PROC
SET @ProcDefinition = REPLACE(@ProcDefinition,'CREATE PROC', 'CREATE OR ALTER PROC')

-- Schritt 2:P_WriteBackSQL_FACT_... gegen P_WriteBackSQL_FACT_..._DataView  austauschen
SET @ProcDefinition = REPLACE(@ProcDefinition,@ProcName, @ProcName + '_Dataview')

--Schritt 3: BEGIN TRY herausnehmen
SET @ProcDefinition = REPLACE(@ProcDefinition,'BEGIN TRY', '')

--Schritt 4: Code auf benötigten Teil kürzen
SET @ProcDefinition =  LEFT(@ProcDefinition,CHARINDEX('--Get "RecCount" of',@ProcDefinition)-1 )

--Schritt 5: Herausnehmen von PreProcess-Aufruf und dazugehörigem Log
SET @ProcDefinition =  LEFT(@ProcDefinition,CHARINDEX('-- Write "MERGE DATA BEGIN" log',@ProcDefinition)-1) + ' ' + SUBSTRING(@ProcDefinition,CHARINDEX('-- Build dimension key tables',@ProcDefinition),1000000)

--Schritt 6: #T_FACT_... gegen ##T_FACT_..._Dataview austauschen
SET @ProcDefinition = REPLACE(@ProcDefinition,@OLD_TempTableName, @NEW_TempTableName)

--Schritt 7: Delta-Hybrid spezifische Punkte entfernen
IF @Hybridversion = 'Delta-'
BEGIN

--Herausnehmen von "Build second data area table for delta values"
SET @ProcDefinition =  LEFT(@ProcDefinition,CHARINDEX('--Build second data area table for delta values',@ProcDefinition)-1) + ' ' + SUBSTRING(@ProcDefinition,CHARINDEX('--Fill delta data area table variable',@ProcDefinition),1000000) 
SET @ProcDefinition = REPLACE(@ProcDefinition,@OLD_WB_ObjectName, @NEW_WB_ObjectName)
SET @ProcDefinition = REPLACE(@ProcDefinition,@NEW_TempTableName + '_DELTA',@NEW_TempTableName)
END

EXEC (@ProcDefinition)

Wertfortschreibung

Unter der Wertfortschreibung verstehen wir im Planungskontext die Eingabe eines Wertes, die sich automatisch in die Zukunft fortschreibt. Diese Art der Eingabe wird in der Praxis gerne genutzt, um Werte einzugeben, die sich über den Verlauf nicht, nur punktuell oder stufenweise ändern. Beispiele hierfür sind Mietkosten, die in der Regel monatlich gleichbleibend sind oder ab einem gewissen Zeitpunkt erhöht/gesenkt werden und dann im Anschluss wieder gleichbleiben. Andere Beispiele wären auch Mitarbeiterzahlen oder Kundenrabatte in %. Diese Kennzahlen müssten ohne Wertfortschreibung mühsam per Hand in jeden einzelnen Monat eingegeben werden.

In der Regel wird die Wertfortschreibung auf ein Kalenderjahr begrenzt, sodass der Wert nur bis zum Ende des Jahres fortgeschrieben wird. Aber auch andere Konstellationen sind möglich und können praxisrelevant sein – bei rollierenden Planungen zum Beispiel ist es sinnvoll, den Wert nicht nur bis zum Ende des Kalenderjahres, sondern abhängig vom jeweiligen rollierenden Zeitraum fortzuschreiben. Diese Konstellationen lassen sich jedoch sehr gut über den gestalterischen Freiraum, den die Pre- und PostProcess-Prozeduren bieten, umsetzen.

Umsetzung

Als Beispielanwendung für eine Planung mit Wertfortschreibung ziehen wir eine fiktive Personalplanung heran: Je Monat soll die Anzahl an Mitarbeitern in den unterschiedlichen Mitarbeiterkategorien und -gruppen geplant werden, ausgedrückt als FTE (Full Time Equivalent).

Abb. 2: Eingabebericht zur Personalplanung mit FTE

Wir gehen von einem Szenario aus, in dem sich im oben gezeigten Beispiel (Abbildung 1) die geplante Anzahl der operativen Mitarbeiter – Gruppe 3 durch die Einstellung von 5 Mitarbeitern im April von 120 auf 125 FTE erhöht. Ohne die Funktionalität der Wertfortschreibung müsste nun für jeden Monat im Zeitraum April bis Dezember der Wert einzeln auf 125 angepasst werden. Mit der Fortschreibung genügt dazu ein Eintrag im April.

Bevor wir zur eigentlichen Umsetzung kommen, lassen wir uns erstmal die bereits beschriebene Dataview-Prozedur aus der P_WriteBackSQL_FACT_Create_DataView_Proc-Routine erstellen (der Code hierzu findet sich oben). Dafür führen wir die Prozedur mit der Angabe der Rückschreibeprozedur aus, für die wir die Dataview-Prozedur erstellen lassen wollen. In unserem Beispiel sieht das wie folgt aus:

EXEC [dbo].[P_WriteBackSQL_FACT_Create_DataView_Proc] 'P_WriteBackSQL_FACT_02_Personalkostenplanung'

Das Ergebnis ist die Prozedur „P_WriteBackSQL_FACT_02_Personalkostenplanung_Dataview“, die über die gleichen Parameter wie die Rückschreibeprozedur selbst verfügt und als Ergebnis eine globale temporäre Tabelle mit den betroffenen Datensätzen erstellt (in unserem Beispiel „##T_FACT_02_DataView“)

Abb. 3: Generierte DataView-Prozedur

Nach der Erstellung des nötigen Werkzeugs kann es jetzt an die eigentliche Umsetzung der Wertfortschreibung gehen. Hierzu ist eine Ergänzung der PreProcess-Routine um unseren individuellen Teil für die Fortschreibung notwendig:

--Wertfortschreibungslogik
IF LOWER(@CustomOperator) = 'yearend' AND @Periode_Periode_AttrName = 'MonatID'
BEGIN

DECLARE @curMonat varchar(50)

DECLARE yearend CURSOR FOR
SELECT MonatID
FROM [dbo].[T_DIM_01_03_Monat]
WHERE 1=1  -- Vom eingegebenen Monat bis zum Ende des Jahres
AND MonatID > @Periode_Periode_MemberKey  
AND LEFT(MonatID,4) = LEFT(@Periode_Periode_MemberKey,4)

OPEN yearend

FETCH NEXT FROM yearend INTO @curMonat

WHILE @@FETCH_STATUS = 0

BEGIN  

--Ermittlung des MeasureValueOld via erstellter Dataview-Prozedur

EXECUTE P_WriteBackSQL_FACT_02_Personalkostenplanung_Dataview
@TransactionID = @TransactionID,
@UserName = @UserName,
@MeasureName = @MeasureName,
@MeasureValue = @MeasureValue,
@MeasureValueOld = NULL,
@Operator = N'=',
@MeasureValueOldType = 1,
@Kumulation_Kumulation_AttrName = @Kumulation_Kumulation_AttrName,
@Kumulation_Kumulation_MemberKey = @Kumulation_Kumulation_MemberKey,
@Periode_Periode_AttrName = @Periode_Periode_AttrName,
@Periode_Periode_MemberKey = @curMonat,  -- Angepasster Parameter
@Periode_Periode_KW_AttrName = @Periode_Periode_KW_AttrName,
@Periode_Periode_KW_MemberKey = @Periode_Periode_KW_MemberKey,
@Periodenansicht_Periodenansicht_AttrName = @Periodenansicht_Periodenansicht_AttrName,
@Periodenansicht_Periodenansicht_MemberKey = @Periodenansicht_Periodenansicht_MemberKey,
@Wertart_Wertart_AttrName = @Wertart_Wertart_AttrName,
@Wertart_Wertart_MemberKey = @Wertart_Wertart_MemberKey,
@Team_Team_AttrName = @Team_Team_MemberKey,
@Team_Team_MemberKey = @Team_Team_MemberKey,
@Mitarbeiterkategorie_Mitarbeiterkategorie_AttrName = @Mitarbeiterkategorie_Mitarbeiterkategorie_AttrName,
@Mitarbeiterkategorie_Mitarbeiterkategorie_MemberKey = @Mitarbeiterkategorie_Mitarbeiterkategorie_MemberKey,
@Mitarbeitergruppe_Mitarbeitergruppe_AttrName = @Mitarbeitergruppe_Mitarbeitergruppe_AttrName,
@Mitarbeitergruppe_Mitarbeitergruppe_MemberKey = @Mitarbeitergruppe_Mitarbeitergruppe_MemberKey,
@ErrorDesc = NULL

DECLARE @execsql nvarchar(max),
		@WertOld float,
	    @ParamDef nvarchar(100) 

SET @execsql = '(SELECT @WertOldOutfloat = SUM(' + @MeasureName + ')
	FROM ##T_FACT_02_DataView)'

--EXEC(@execsql)
SET @ParamDef = '@WertOldOutfloat float OUTPUT'


EXECUTE sp_executesql @execsql, @ParamDef, @WertOldOutfloat=@WertOld OUTPUT

EXECUTE P_WriteBackSQL_FACT_02_Personalkostenplanung
@TransactionID = @TransactionID,
@UserName = @UserName,
@MeasureName = @MeasureName,
@MeasureValue = @MeasureValue,
@MeasureValueOld = @WertOld, -- Angepasster Parameter
@Operator = N'=',
@MeasureValueOldType = 1,
@Kumulation_Kumulation_AttrName = @Kumulation_Kumulation_AttrName,
@Kumulation_Kumulation_MemberKey = @Kumulation_Kumulation_MemberKey,
@Periode_Periode_AttrName = @Periode_Periode_AttrName,
@Periode_Periode_MemberKey = @curMonat,  -- Angepasster Parameter
@Periode_Periode_KW_AttrName = @Periode_Periode_KW_AttrName,
@Periode_Periode_KW_MemberKey = @Periode_Periode_KW_MemberKey,
@Periodenansicht_Periodenansicht_AttrName = @Periodenansicht_Periodenansicht_AttrName,
@Periodenansicht_Periodenansicht_MemberKey = @Periodenansicht_Periodenansicht_MemberKey,
@Wertart_Wertart_AttrName = @Wertart_Wertart_AttrName,
@Wertart_Wertart_MemberKey = @Wertart_Wertart_MemberKey,
@Team_Team_AttrName = @Team_Team_MemberKey,
@Team_Team_MemberKey = @Team_Team_MemberKey,
@Mitarbeiterkategorie_Mitarbeiterkategorie_AttrName = @Mitarbeiterkategorie_Mitarbeiterkategorie_AttrName,
@Mitarbeiterkategorie_Mitarbeiterkategorie_MemberKey = @Mitarbeiterkategorie_Mitarbeiterkategorie_MemberKey,
@Mitarbeitergruppe_Mitarbeitergruppe_AttrName = @Mitarbeitergruppe_Mitarbeitergruppe_AttrName,
@Mitarbeitergruppe_Mitarbeitergruppe_MemberKey = @Mitarbeitergruppe_Mitarbeitergruppe_MemberKey,
@ErrorDesc = NULL

FETCH NEXT FROM yearend INTO @curMonat

END

CLOSE yearend
DEALLOCATE yearend

END

Ein paar Erklärungen dazu: Zuerst wird über das IF-Statement geprüft, ob die Wertfortschreibung überhaupt zum Einsatz kommen soll. In unserem Beispiel ist das Schlagwort hierzu „yearend“. Mit dem LOWER-Statement sorgen wir dafür, dass die Groß- und Kleinschreibung keinen Einfluss darauf hat, ob die Wertfortschreibung ausgeführt wird oder nicht. Zudem prüfen wir auf die Ebene der Periode, die in diesem Fall der Monat sein muss. Mit Fallunterscheidungen könnte natürlich auch die Eingabe auf anderen Ebenen umgesetzt werden. Anschließend wird per Cursor eine Schleife mit den Monaten bis zum Ende des Kalenderjahres durchlaufen. Nun kommt auch das erste Mal die zuvor erstellte Dataview-Prozedur zum Einsatz. Dieser übergeben wir die Parameter, die wir aus der Rückschreibeprozedur im PreProcess erhalten, mit dem einzigen Unterschied, dass wir beim „@Periode_Periode_MemberKey“ den Monat aus der Schleife übergeben. So erhalten wir bei jedem Schleifendurchlauf den Datenraum des zukünftigen Monats zurückgeliefert. Das ermöglicht uns im Anschluss den „MeasureValueOld“ für den zukünftigen Monat zu ermitteln. Dies geschieht über die dynamische Abfrage der temporären Tabelle, die durch die Dataview-Prozedur befüllt worden ist. Hier bilden wir die Summe über die betroffene Kennzahl und übergeben den Wert an einen Parameter (in diesem Fall @WertOld).

Damit haben wir sämtliche Informationen ermittelt, um im Anschluss die Rückschreibeprozedur per Schleife für die zukünftigen Monate mit den angepassten Parametern @MeasureValueOld und @Periode_Periode_MemberKey auszuführen. Ein wichtiger Hinweis hierbei ist, dass die Ermittlung des @MeasureValueOld nur dann wirklich notwendig ist, wenn die Delta-Hybrid-Variante verwendet wird. Denn hier ist der @MeasureValueOld zwingend notwendig, um den Wert für die Delta-Tabelle bestimmen zu können.

Bei der Ausführung der Rückschreibeprozedur ist darauf zu achten, nicht nochmal den Parameter @CustomOperator zu übergeben. Sonst führt die Dateneingabe in eine Endlosschleife, da bei der Ausführung des PreProcess die Wertfortschreibungslogik jedes Mal erneut ausgeführt werden würde.

Verwendung

Nachdem wir die Logik für die Wertfortschreibung implementiert haben, kann sie getestet werden. Hierzu öffnen wir den Eingabebericht der Personalplanung erneut und probieren nun, die Anforderung, die im ersten Teil des Abschnittes „Umsetzung“ beschrieben worden ist, mit der Wertfortschreibung umzusetzen. Wir wollen also die Einstellung von 5 Mitarbeitern im April abbilden und dadurch ab diesem Monat die Mitarbeiteranzahl von 120 auf 125 FTE erhöhen. Diese Anforderungen können wir nun über die Wertfortschreibungslogik abbilden, indem wir im April die 125 in Kombination mit unserem Erkennungszeichen für den Custom Operator „#“ und unserem individuell festgelegten Schlagwort für die Wertfortschreibung „yearend“ eintragen.

Abb. 4: Eingabe per Custom Operator „#yearend“

Das Ergebnis spiegelt genau das wider, was wir auch abbilden wollten: Die 125 sind nun ab dem April bis zum Kalenderjahresende eingetragen worden – mit nur einer Eingabe.

Abb. 5: Ergebnis aus der Eingabe per Custom Operator „yearend“

Fazit

Die Wertfortschreibungslogik ist nur ein Beispiel von vielen, die mit Hilfe von Custom Operator umsetzbar sind. Wir können damit auf nahezu jeden Kundenwunsch eingehen – vor allem in der Kombination mit der Dataview-Prozedur. Diese ermöglicht es, jeden beliebigen Datenraum im Eingabeprozess zu ermitteln. Daraus lassen sich zum Beispiel auch Vorjahresverteilungen auslesen, die auf die aktuelle Eingabe angewendet werden können. Entsprechend vielfältig sind die möglichen Anwendungsbereiche für diese Lösung. Allen gemein ist ein Komfort- und Zeitgewinn einhergeht, den viele Unternehmen, gerade bei immer kürzer getakteten Planungsphasen, gerne in Anspruch nehmen.