Relationale Pivoteingabe – Das Splashing-Upgrade

DeltaMaster bietet mit seinen Planungsfunktionen umfangreiche Möglichkeiten zur Dateneingabe in multidimensionalen Modellen. Nicht jeder Anwendungsfall rechtfertigt allerdings die Entwicklung eines solchen Modells. Mit Hilfe von relationalen DeltaMaster-Anwendungen auf SQL Server und der damit verfügbaren Pivoteingabe lassen sich einfache Anwendungsfälle auch ganz ohne Würfel realisieren. Auch wenn die Eingabe standardmäßig  kein Splashing zulässt, lässt sich diese Funktion mit einigen Anpassungen ergänzen.

Überblick

DeltaMaster und DeltaMaster ETL bieten umfangreiche Funktionen, um Plananwendungen für nahezu jeden Anwendungsfall zu realisieren. Diesen Anwendungen liegt meist ein multidimensionales Datenmodell zu Grunde, welches durch definierte Strukturen und schnelle Aggregationen prädestiniert dafür ist. Bei der Entscheidung für diese Lösung muss jedoch bedacht werden, dass der zugrundeliegende Cube nach jeder Änderung an Dimensionsdaten aktualisiert werden muss. Wird diese Aktualisierung im Planungsprozess zum Regelschritt, da neben Kennzahlen auch Strukturen, z.B. durch SQL-Durchgriffsberichte, erzeugt werden, kann dies die Bedienung der Anwendung unkomfortabel machen. Außerdem muss für die Bedienung des SQL-Durchgriffs in eine andere Anwendung gewechselt werden. DeltaMaster lässt uns mit diesem Problem jedoch nicht allein und ermöglicht auch in rein relationalen Anwendungen auf SQL Server die Eingabe in Pivottabellen.

Mittels einer unscheinbaren Einstellung in DeltaMaster ETL im Bericht „Measure Groups“ lassen sich alle dafür benötigten Objekte für Faktentabellen generieren:

Anschließend werden die folgenden Objekte erzeugt:

• P_UPDATE_PIV_* – Prozedur für die Durchführung der Eingabe
• T_D_* – Tabelle, in welcher die eingegebenen Daten abgespeichert werden
• V_D_* – View auf der T_D_*-Tabelle

Rein relationalen Anwendungen liegt jedoch häufig kein DeltaMaster-ETL-Modell zugrunde. In diesen Fällen kann die notwendige P_UPDATE_PIV_* – Prozedur für eine Basistabelle, in welche Daten geschrieben werden sollen, mit Hilfe der Prozedur P_BC_Generate_DeltaMasterTableProc_PIV generiert werden. Diese wird standardmäßig mit dem DeltaMaster-ETL-Metamodell ausgeliefert. Eine Erläuterung der einzelnen Parameter kann der DeltaMaster ETL-Administratorenreferenz entnommen werden.

Einen Nachteil hat die Sache jedoch: Im Standard können relationale Eingaben in Pivottabellen nur auf der untersten Ebene vorgenommen werden. Ein Splashing, wie man es aus DeltaMaster-Plananwendungen kennt, ist nicht möglich. Das folgende Beispiel soll zeigen, wie die generierten Prozeduren um diese Funktionalität erweitert werden können.

Das Beispiel

Für diesen Blogbeitrag wird ein einfaches Datenmodell herangezogen, welches auf Anfrage heruntergeladen werden kann. In diesem sollen Geldbeträge für Kostenstellen, welche einzelnen Organisationseinheiten zugeordnet sind, pro Sachkonto und pro Jahr erfasst werden. Die Eingabe soll sowohl auf der Ebene „Kostenstelle“, als auch auf der übergeordneten Ebene „Organisationseinheit“ möglich sein. Auf den Dimensionen Sachkonto und Jahr soll nur auf der untersten Ebene eingegeben werden.

Implementierung der Eingabe

Um die notwendigen Objekte zur Transaktionssteuerung zu erzeugen, wird zunächst der folgende Befehl ausgeführt:

EXEC dbo.P_BC_Generate_DeltaMasterTableProc_PIV  
@TabName = 'V_D_Werte',                        
@BaseTabName = 'T_D_Werte',                    
@DimColList = 'SachkontoID, KostenstelleID, JahrID',                         
@MeasColList = 'Betrag',           
@ColSeparator = ',',                
@IncludeTransactions = 2, 
@AutoInsertUser = 1,               
@AddColsToBasetab = 1

Zu beachten ist, dass die Dimensionen und Measures analog zu den Spaltennamen in der zugrundeliegenden Tabelle angegeben werden müssen.

Anschließend muss nur noch die Planungsfunktion in der relationalen DelteMaster-Anwendung aktiviert und als Transaktionssteuerung „Modellspezifische Transaktionen“ unter Optionen = > Dateneingabe ausgewählt werden:

Die Euphorie schwindet jedoch, sobald eine Eingabe auf der Organisationseinheit vorgenommen wird:

Was ist passiert? Um das zu verstehen ist der sql.log aus der DeltaMaster-Diagnose aufschlussreich:

EXECUTE dbo.P_Update_PIV_V_D_Werte 
@JahrID = 2020, 
@Organisationseinheit = N'OE1', 
@SachkontoID = 10000000, 

@TransactionID = 'ed7ac2cb-d22e-4c1f-80e7-43c57447f8d9', 
@StepNumber = 0, 
@MeasureName = N'Betrag', 
@NewValue = 40, 
@ErrorDesc = NULL, 

@ReturnValue = NULL 
 -> 
Procedure or function 'P_Update_PIV_V_D_Werte' expects parameter '@KostenstelleID', which was not supplied

Es ist leicht zu erkennen, wie die Aufrufe in der Anwendung generiert werden. DeltaMaster übergibt als Parameter die der aktuellen Zelle zugrundeliegenden Spalte. Den Parameter „Organisationseinheit“ kennt unsere Prozedur nicht und weiß auch nicht, ob und wie sie den Wert verteilen soll. Ergänzen wir also zunächst den Parameter und erlauben gleichzeitig, dass der Parameter „KostenstelleID“ nicht übergeben werden muss:

ALTER PROCEDURE [dbo].[P_Update_PIV_V_D_Werte] 

( 
@SachkontoID varchar(255),  
@Organisationseinheit VARCHAR(255) = NULL, 
@KostenstelleID varchar(255) = NULL,  
@JahrID varchar(255), 
@TransactionID varchar(255), 
@StepNumber varchar(255) = NULL, 
@MeasureName varchar(255), 
@NewValue varchar(255), 
@ErrorDesc varchar(255) = NULL OUTPUT
)

Mit Hilfe dieser Änderung wird nun kein Fehler mehr beim Aufruf erzeugt. Eine sinnvolle Eingabe findet jedoch noch nicht statt, schließlich müssen wir der Prozedur noch die Verteilung auf darunter liegende Kostenstellen beibringen. Für die hier beschriebene Lösung wird die Verteilung analog zum DeltaMaster-Planungsstandard implementiert: Ist die Zelle leer findet eine Gleichverteilung auf darunterliegende Zellen statt. Ist die Zelle bereits gefüllt, findet die Verteilung proportional zur bestehenden Verteilung auf die darunterliegenden Zellen statt.

Die Prozedur muss dazu an den Stellen, an denen ein Join über KostenstelleID erfolgt um eine alternative Überprüfung der Organisationseinheit ergänzt werden.

[…] 
FROM 
dbo.T_D_Werte w 
LEFT JOIN 
dbo.T_DIM_Kostenstelle k 
ON w.KostenstelleID = k.KostenstelleID 
WHERE 
SachkontoID = convert(int, @SachkontoID)   
AND  
( 
w.KostenstelleID = convert(int, @KostenstelleID) 
OR	 
k.Organisationseinheit = @Organisationseinheit 
) 
AND 
JahrID = convert(int, @JahrID)

Somit wird bei Eingabe auf eine Organisationseinheit überprüft, ob schon Einträge für die darunter liegenden Kostenstellen existieren. Bei Eingabe auf Kostenstellen bleibt die bestehende Funktionalität erhalten. In diesem Blogbeitrag werden nur einzelne Codestellen beschrieben, die komplette Prozedur befindet sich auf dem Laufwerk bzw. ist gegen Anfrage erhältlich.

Analog werden auch die Update-/Delete-/ und Insert-Statements in der Prozedur angepasst. Für das proportionale Splashing muss zusätzlich noch eine Ermittlung des Faktors, der den abzuziehenden Anteil angibt, ergänzt werden:

SELECT @OldValue = SUM(w.Betrag) 
--select * 
FROM 
dbo.T_D_Werte w 
LEFT JOIN dbo.T_DIM_Kostenstelle k 
ON w.KostenstelleID = k.KostenstelleID 
 
WHERE 
w.SachkontoID = convert(int, @SachkontoID) 
AND  
( 
w.KostenstelleID = convert(int, @KostenstelleID) 
OR	 
k.Organisationseinheit = @Organisationseinheit 
) 
AND w.JahrID = convert(int, @JahrID)

Dieser Faktor wird dann in der Update-Prozedur verwendet:

SET @SQL = 
'	UPDATE w 
SET 
' + @MeasureName + ' = CONVERT(FLOAT, '+@MeasureName +') * CONVERT(FLOAT,' + 
CONVERT(VARCHAR,@Faktor) + '), 
TransactionState = CASE WHEN TransactionState IS NULL THEN 2 ELSE TransactionState END,  
TransactionID = ''' + @TransactionID + ''', 
InputTypeID = 0, 
ChangeDate = getdate(), 
ChangeUserID = ' + @ChangeUserID + ', 
ChangeHost = host_name() 
FROM  
T_D_Werte w 
LEFT JOIN dbo.T_DIM_Kostenstelle k 
ON w.KostenstelleID = k.KostenstelleID	 



WHERE 
SachkontoID = convert(int, ' + @SachkontoID + ')  
AND 
(k.KostenstelleID = ''' +  ISNULL(@KostenstelleID,'') + ''') 
OR 
(k.Organisationseinheit = ''' +  ISNULL(@Organisationseinheit,'') + ''')  
AND 
JahrID = convert(int, ' + @JahrID + ')' 
EXEC(@SQL)

Beim generierten Insert-Statement, also der Eingabe in eine leere Zelle, muss statt eines Faktors die Anzahl an darunterliegenden Elementen ermittelt werden:

 
SELECT @Splashcount = COUNT(DISTINCT k.KostenstelleID) 
FROM 
 dbo.T_DIM_Kostenstelle k 

WHERE 
( 
(@KostenstelleID IS NOT NULL AND k.KostenstelleID = @KostenstelleID) 
OR 
(@Organisationseinheit IS NOT NULL  
AND k.Organisationseinheit = @Organisationseinheit) 
) 

--Neuer Wert ist der eingegebene Wert / Anzahl an Datensätzen 
SET @locNewValue = CONVERT(FLOAT,@NewValue)/CONVERT(FLOAT,@Splashcount);

Damit bei der Eingabe auf übergeordnete Elemente mehrere Datensätze geschrieben werden, wird das Insert-Statement erweitert:

SET @SQL = 
'		INSERT INTO dbo.T_D_Werte 
( 
SachkontoID,  
KostenstelleID,  
JahrID, 
' + @MeasureName + ', 
TransactionState, 
TransactionID, 
InputTypeID, 
ChangeDate, 
ChangeUserID, 
ChangeHost 
) 
SELECT 

convert(int, ' + @SachkontoID + '),  
convert(int, k.KostenstelleID ),'+  
'convert(int, ' + @JahrID + '), 
' + CONVERT(VARCHAR,@locNewValue) + ', 
1, 
''' + @TransactionID + ''', 
0, 
getdate(), 
' + @ChangeUserID + ', 
host_name() 

FROM 
T_DIM_Kostenstelle k 


WHERE 
(k.KostenstelleID = ''' +  ISNULL(@KostenstelleID,'') + ''') 
OR 
(k.Organisationseinheit = ''' +  ISNULL(@Organisationseinheit,'') + ''')'  
EXEC(@SQL)

Eine Eingabe auf der Ebene Organisationseinheit zeigt nun das gewünschte Ergebnis:

Rollback

Die nun gewonnene gute Stimmung wird schnell getrübt, sollte ein Anwender innerhalb einer Eingabetransaktion auf die Idee kommen, den Knopf  zu benutzen. Das Ergebnis lässt sich einfach zusammenfassen: Bei einer Eingabe auf leere Zellen funktioniert alles wie erwartet. Bei der Eingabe auf bereits gefüllte Zellen geschieht bei Abbruch nichts. Grund dafür ist, dass die P_Update_PIV_*-Prozeduren über eine einfache Rollback-Funktionalität verfügen. Diese muss aber nach den Anpassungen für die Splashing-Logiken ebenfalls modifiziert werden, um alle geänderten Datensätze zu sichern:

 
INSERT INTO dbo.T_D_Werte_Rollback 
(  
SachkontoID,  
KostenstelleID,  
JahrID,  
Betrag,  
TransactionID,  
TransactionState,  
InputTypeID,  
ChangeDate,  
ChangeUserID,  
ChangeHost 
) 
SELECT  
SachkontoID,  
k.KostenstelleID,  
JahrID,  
Betrag,  
@TransactionID,  
TransactionState,  
InputTypeID,  
ChangeDate,  
ChangeUserID,  
ChangeHost 
FROM 
dbo.T_D_Werte w 
LEFT JOIN 
dbo.T_DIM_Kostenstelle k 
ON w.KostenstelleID = k.KostenstelleID 
WHERE 
SachkontoID = convert(int, @SachkontoID)   
AND  
( 
w.KostenstelleID = convert(int, @KostenstelleID) 
OR	 
k.Organisationseinheit = @Organisationseinheit 
) 
AND 
JahrID = convert(int, @JahrID)  
AND 
TransactionState IS NULL

Anschließend funktioniert auch das Rollback problemfrei.

Sperren von Datensätzen

Auch das Sperren von Datensätzen muss in mittlerweile gewohnter Manier angepasst werden:

SELECT  
@TransactionID_InUse  = TransactionID  
FROM 
dbo.T_D_Werte w 
LEFT JOIN dbo.T_DIM_Kostenstelle k 
ON w.KostenstelleID = k.KostenstelleID 
WHERE 
SachkontoID = convert(int, @SachkontoID)  
AND  
( 
w.KostenstelleID = convert(int, @KostenstelleID) 
OR	 
k.Organisationseinheit = @Organisationseinheit 
) 
AND 
JahrID = convert(int, @JahrID)

Fazit

Die hier beschriebenen Anpassungen lassen sich problemlos auf unterschiedliche Anwendungsfälle übertragen. Zu beachten ist jedoch, dass die Logik für jede Dimension, auf der eine aggregierte Eingabe möglich sein soll, einzeln implementiert werden muss. Ansonsten bietet die modifizierte, relationale Pivoteingabe alle gewohnten Funktionen in guter Laufzeit. Besonders charmant wird die hier beschriebene Lösung, wenn sich die zugrundeliegenden Stammdaten zur Laufzeit erweitern, z.B. durch eine neue Kostenstelle. Diese ist dann in der Anwendung ohne Aufbereitungsschritte sofort beplanbar.