Einstieg in die Verwendung temporaler Tabellen

Seit der Microsoft SQL Server Version 2016 können temporale Tabellen verwendet werden. Sie unterscheiden sich von herkömmlichen Tabellen durch eine automatische Archivierung von Zeilen. Dieser Beitrag zeigt, wie damit eine Stammdaten-Eingabe in DeltaMaster gestaltet werden kann.

Temporale Tabellen sind Tabellen mit Systemversionsverwaltung – ein besonderer Tabellentyp, welcher ab der Version 2016 (13.x) des Microsoft SQL Server zur Verfügung steht. Dabei schlägt Microsoft die Verwendung temporaler Tabellen für folgende Szenarios vor:

1. Datenüberwachung: Nachverfolgung von Änderungen an Datensätzen, z. B. in der Datenforensik
2. Zeitpunktanalyse: Analyse des Verlaufs von Datensätzen, z. B. von Beständen
3. Anomalie-Erkennung: Identifikation von Ausreißern
4. Langsam veränderliche Dimensionen: Abbildung ähnlich des Typs SCD 4, d. h. einer Tabelle für den aktuellen Datensatz und einer Tabelle mit historischen Datensätzen
5. Reparieren von Datenbeschädigungen auf Zeilenebene: Hilfe beim Wiederherstellen alter Datensätze

Im ersten Szenario, der Datenüberwachung, fungieren temporale Tabellen als Alternative zu sonstigen Möglichkeiten des SQL-Server-Audits. Im Datawarehousing ist vor allem das vierte Szenario interessant: die Behandlung langsam veränderlicher Dimensionen, auch Slowly Changing Dimensions (SCD) genannt.

DeltaMaster erlaubt die Eingabe von Daten, üblicherweise in Form von Planzahlen mit Top-Down- und Bottom-Up-Verteilung oder der Pflege einzelner Datensätze, oftmals in Form von Stammdaten. In diesem Blogbeitrag wird nun untersucht, wie eine Stammdateneingabe mit DeltaMaster und temporalen Tabellen aussehen kann. Dabei werden die SQL-Server-Objekte in Microsoft SQL Server Management Studio (SSMS) bearbeitet. Die Dateneingabe in DeltaMaster setzt eine entsprechende Planungs- oder Analyse-Lizenz (PLN oder ANL) unserer Software voraus.

Grundsätzliche Implementierung

Im Folgenden zeigen wir, wie in SSMS eine temporale Tabelle einer MS-SQL-Server-Datenbank angelegt und mit Beispieldaten befüllt wird. Anschließend verbinden wir diese mit einer DeltaMaster-Anwendung und ermöglichen die Dateneingabe per SQL-Durchgriff. Im Beispiel-Szenario sollen Gehälter von Mitarbeitern verändert werden können. Dieses Szenario ist auch mit Microsoft Azure SQL Database abbildbar.

Temporale Tabelle präparieren

Die vorgestellte Lösung basiert auf dem Ansatz, in SSMS automatisch erzeugte Skripts anzupassen und Beispielzeilen mit Copy-and-Paste einzufügen. Die temporale Tabelle soll den Benutzer festhalten, der eine Zeile bearbeitet hat, darüber hinaus aber minimale Komplexität besitzen.

Temporale Tabelle erstellen

Um eine neue temporale Tabelle zu erstellen, kann in einer Datenbank auf einem Datenbankserver ab der Version 2016 ein passendes Skript aufgerufen werden.

Abb. 1: Erstellung der Temporalen Tabelle in SSMS

In der Verwendung mit DeltaMaster bietet es sich an, die Verlaufstabelle in einem anderen Schema anzulegen (vgl. Abschnitt „DeltaMaster-Anwendung erstellen“). Für dieses Beispiel soll sie im Schema [History] angelegt werden. Das Schema lässt sich mit dem folgenden Code erstellen:

 CREATE SCHEMA [History] AUTHORIZATION [dbo]

Die temporale Tabelle soll nun aus einer Spalte für den Mitarbeiter ([Employee]), seinem Gehalt ([Salary]), dem Ersteller bzw. Aktualisierer der Zeile ([RowModifiedUser]) und den zeitlichen Gültigkeitsschranken bestehen. Das Präfix „Row“ wird hier verwendet, um technische von inhaltlichen Daten zu unterscheiden:

CREATE TABLE [dbo].[Employee_Salary] (
	[Employee] varchar(1) NOT NULL,
	[Salary] money NULL,
[RowModifiedUser] varchar(50) NOT NULL DEFAULT (SUSER_SNAME()),
	[RowValidFrom] datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
	[RowValidTo] datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
	PERIOD FOR SYSTEM_TIME([RowValidFrom], [RowValidTo]),
	CONSTRAINT [PK_Employee_Salary] PRIMARY KEY ([Employee])
) WITH (SYSTEM_VERSIONING = ON (
	HISTORY_TABLE = [History].[Employee_Salary]))

War dies erfolgreich, so liegt nun die temporale Tabelle mit der Verlaufstabelle vor.

Abb. 2: Temporale Tabelle mit Verlaufstabelle

Temporale Tabelle befüllen

Für das vorliegende Beispiel legen wir drei Zeilen an:

INSERT	[dbo].[Employee_Salary] (Employee, Salary)
VALUES	('A', 20000), ('B', 40000), ('C', 60000)

Bearbeitet man den Tabelleninhalt der temporalen Tabelle in SSMS, so sieht das Fenster mit bereits ausgefüllten Zeilen wie folgt aus:

Abb. 3: Ausgefüllte temporale Tabelle in SSMS

In dieser Ansicht sind die Spalten [RowValidFrom] und [RowValidTo] ausgeblendet und damit nicht editierbar. Ruft man den Tabelleninhalt im Lesemodus auf, so ist der Gültigkeitszeitraum zu sehen:

SELECT * FROM [dbo].[Employee_Salary]

Abb. 4: Gültigkeitszeitraum in der temporalen Tabelle

Ändert man das Gehalt von Mitarbeiter A auf 25.000, so zeigt dieselbe Abfrage den neuen Gültigkeitsbeginn an.

Abb. 5: Neuer Gültigkeitszeitraum nach Änderung in der temporalen Tabelle

Die Verlaufstabelle kann abgefragt werden und zeigt den archivierten Datensatz:
SELECT * FROM [History].[Employee_Salary]

Abb. 6: Archivierter Datensatz in der Verlaufstabelle

Sämtliche Abfragen der Versionen einer Zeile können über die Versionstabelle abgerufen werden. Der initiale Stand lässt sich z. B. durch Angabe des Zeitpunkts in der Versionstabelle abfragen.

SELECT * FROM [dbo].[Employee_Salary] 
FOR SYSTEM_TIME AS OF '2023-01-18 08:38'
ORDER BY [Employee]

Abb. 7: Ergebnis der Abfrage des initialen Stands

Weitere Möglichkeiten zur Abfrage von historischen Zeilen bestehen, werden jedoch hier nicht weiter erläutert.

DeltaMaster-Anwendung erstellen

Beim Erstellen einer neuen DeltaMaster-Anwendung kann als Quelle sowohl Microsoft SQL Server wie auch Microsoft Azure SQL Database herangezogen werden. In diesem Beispiel wird Microsoft SQL Server verwendet. Bei der Auswahl der Tabellen wird nach dem jeweiligen Schema unterschieden: Sind viele temporale Tabellen vorhanden und die Verlaufstabellen in einem anderen Schema (hier: [History]) abgelegt, so fällt die Liste der relevanten Tabellen unterhalb des [dbo]-Schemas kleiner aus.

Abb. 8: Auswahl der Tabellen zur Erstellung der DeltaMaster-Anwendung

Im ersten Schritt bietet es sich an, den Mitarbeiter als Dimension und das Gehalt als Kennzahl zu modellieren. Dabei ließen sich diverse Schönheitskorrekturen durchführen, z. B. die Umbenennung des All-Elements – auf eine Dokumentation dessen wird an dieser Stelle verzichtet.

Abb. 9: Modellierung in DeltaMaster

In einer Grafischen Tabelle lassen sich damit die aktuellen Werte anzeigen.

Abb. 10: Bericht in DeltaMaster

Für die Verwendung der Dateneingabe in den folgenden Beispielen muss diese aktiviert werden. Die Einstellung ist im Modus „Editieren“ in den Optionen auf dem Reiter „Dateneingabe“ zu finden.

Abb. 11: Aktivierung der Dateneingabe

Dateneingabe im SQL-Durchgriff

Eine Verwaltung von Stammdaten ist bei der Verwendung von SQL-Durchgriffen vollständig abbildbar. In den folgenden Abschnitten zeigen wir, welche SQL-Objekte dafür erstellt werden müssen und wie ein Bericht konfiguriert wird, sodass Eingaben in der temporalen Tabelle korrekt verarbeitet werden.

SQL-Prozeduren erstellen

Für die Bearbeitung von Tabelleninhalten im SQL-Durchgriff bedarf es drei Prozeduren im Namensschema [dbo].[P__] bzw. [dbo].[P__].
In diesem Beispiel soll die bereits erstellte Tabelle als Quelle weiterverwendet werden. Somit sind die benötigten Prozeduren:

• [dbo].[P_Delete_Employee_Salary]
• [dbo].[P_Insert_Employee_Salary]
• [dbo].[P_Update_Employee_Salary]

Im Zielbericht soll es lediglich möglich sein, die Spalten [Employee] und [Salary] zu modifizieren und für neue Zeilen auch nur eine Eingabe dieser Spalten zu erlauben.

Prozedur zum Löschen von Zeilen:

CREATE PROCEDURE [dbo].[P_Delete_Employee_Salary]( 
@Employee nvarchar(1) = NULL,
	@Salary nvarchar(255) = NULL
) AS
DELETE	[dbo].[Employee_Salary] 
WHERE		[Employee] = @Employee

Prozedur zum Einfügen von Zeilen:

CREATE PROCEDURE [dbo].[P_Insert_Employee_Salary]( 
	@Employee nvarchar(1) = NULL,
	@Salary nvarchar(255) = NULL
) AS 
IF NOT ISNUMERIC(ISNULL(@Salary,0)) = 1 
	BEGIN RAISERROR('[Salary] invalid!', 16, 1) RETURN END
INSERT	[dbo].[Employee_Salary] ([Employee], [Salary])
SELECT	@EmployeeID, CAST(@Salary AS money)

Prozedur zum Aktualisieren von Zeilen:

CREATE PROCEDURE [dbo].[P_Update_Employee_Salary]( 
	@Employee nvarchar(1) = '#-OLDVALUE-#',
	@Salary nvarchar(255) = '#-OLDVALUE-#'
) AS 
IF @Salary <> '#-OLDVALUE-#' 
	BEGIN IF NOT ISNUMERIC(ISNULL(@Salary,0)) = 1 
		BEGIN RAISERROR('[Salary] invalid!', 16, 1) RETURN END
	END
UPDATE	[dbo].[Employee_Salary] 
SET		[Employee]	= IIF(@Employee = '#-OLDVALUE-#', 
					[Employee], @Employee),
		[Salary]	= IIF(@Salary = '#-OLDVALUE-#',
					[Salary], CAST(@Salary AS money))
WHERE 	[Employee] = @Employee

DeltaMaster-Bericht erstellen

Für die Bearbeitung der Datensätze bietet sich die Option „Daten-Durchgriff auf Basis einer Auswahl an Tabellenfeldern“ an. In einem neuen Bericht vom Typ SQL-Durchgriff sind die Einstellungen dabei wie folgt zu setzen:

Abb. 12: Einstellungen für den Bericht mit SQL-Durchgriff

Wichtig ist, dass die Checkboxen in der Spalte „Parameter“ nur für die Zeilen „Employee“ und „Salary“ gesetzt sind. In diesem einfachen Beispiel ist eine freie Eingabe für diese Zeilen möglich; häufig wird die Auswahl der Eingabe durch eine SQL-Prozedur eingeschränkt. Für die drei technischen Felder ist keine Eingabe erlaubt.

Abb. 13: Checkboxen in der Spalte „Parameter“ zur Freigabe für die Dateneingabe

Sind diese Einstellungen gesetzt, hat der Bericht das folgende Layout.

Abb. 14: Berichtslayout nach Setup

Damit an den vorgesehen Stellen Daten eingegeben werden können, muss im Editiermodus unter „Berichtseigenschaften“ auf dem Reiter „Dateneingabe“ die Checkbox bei „Dateneingabe zulassen“ angehakt werden.

Abb. 15: „Dateneingabe zulassen“ in den Berichtseigenschaften

Ist dies getan, ist die Dateneingabe im Präsentationsmodus möglich und berechtigte Berichtsempfänger können Änderungen vornehmen.

Beispiele für Update, Insert, Delete

Die drei Funktionen Update, Insert und Delete werden nun exemplarisch dargestellt.

Ändert man das Gehalt bei Mitarbeiter A auf 28.000 (Update) und bestätigt dies, so zeigt der automatisch neu geladene Bericht auch den neuen Gültigkeitsbeginn an.

Abb. 16: Bericht nach Update

Ein Blick in die Verlaufstabelle bestätigt die erfolgreiche Änderung.

SELECT * FROM [History].[Employee_Salary]

Abb. 17: Verlaufstabelle nach Update

Wie gewohnt, können neue Zeilen hinzugefügt werden. Hier soll nun Mitarbeiter D mit einem Gehalt von 80.000 angelegt werden (Insert).

Abb. 18: Hinzufügen einer neuen Zeile im Bericht (Insert)

Bestätigt man diese Eingabe, kennt von nun an auch die Mitarbeiter-Dimension das neue Element.

Abb. 19: Bericht nach Insert

Schließlich soll noch eine Zeile, hier Mitarbeiter C, gelöscht werden (Delete). Nach der Löschung der Zeile ist diese nur noch in der Verlaufstabelle mit dem entsprechenden Gültigkeitszeitraum oder unter Verwendung der exklusiven Befehle auf die temporale Tabelle abzufragen, zum Beispiel so:

FOR SYSTEM_TIME ALL.
SELECT	*
FROM		[dbo].[Employee_Salary]
		FOR SYSTEM_TIME ALL
WHERE		[Employee] = 'C'

Abb. 20: Verlaufstabelle nach Delete

Hinweis: Verwendung einer Sicht als Quelle

In den vorherigen Unterkapiteln wurde eine Herangehensweise bei der Verwendung der temporalen Tabelle als Quelle in DeltaMaster gezeigt.

Für „gewöhnliche“ Tabellen lassen sich die Update-, Insert- und Delete-Prozeduren bei Verwendung von DeltaMaster ETL durch einen Aufruf der Prozedur [dbo].[P_BC_Generate_DeltaMasterTableProc] unter Angabe der Tabelle und optional der Zieldatenbank automatisch erzeugen. Da in den temporalen Tabellen kein Rückschreiben auf die Spalten [RowValidFrom] und [RowValidTo] möglich ist, würde der Aufruf dieser Prozedur mit einer temporalen Tabelle Fehler erzeugen.

Eine Lösung ist, eine Sicht dazwischenzuschalten (z. B. [dbo].[V_Employee_Salary]) und diese Sicht statt der temporalen Tabelle als Quelle zu verwenden. Hierbei muss die Sicht initial die Spalten der temporalen Tabelle abfragen, außer den Gültigkeitsspalten. Mit der oben genannten Prozedur lassen sich unter der Angabe der Sicht statt der temporalen Tabelle die drei benötigten Prozeduren fehlerfrei erstellen; danach kann die Sicht um die Gültigkeitsspalten mit ALTER VIEW erweitert werden. Abschließend ist diese Sicht anstatt der temporalen Tabelle als Quelle in DeltaMaster auszuwählen.

Weitere Beispiele

In diesem Kapitel geben wir zwei Beispiele für die Verwendung der exklusiven SQL-Befehle von temporalen Tabellen (FOR SYSTEM_TIME ALL, AS OF). Dabei verwenden wir die bisher genutzte Tabelle und zeigen, wie die Dateneingabe in der Grafischen Tabelle ermöglicht wird.

Wie im vorherigen Abschnitt eingeleitet, sind für die ersten beiden Beispiele auch Ansätze unter der Verwendung von Sichten und den erwähnten exklusiven SQL-Befehlen denkbar, wenn diese Sichten als weitere Quellen in DeltaMaster dienen.

Darstellung aller Zeilen im SQL-Durchgriff

Möchte man die gesamte Historie anzeigen, kann in der SQL-Query der Zusatz FOR SYSTEM_TIME ALL verwendet werden. Zur Lösung kann eine SQL-Prozedur anstatt einer Tabelle für den SQL-Durchgriff verwendet werden. In den folgenden beiden Abschnitten werden die SQL-Prozedur und ein dazu passender Bericht gezeigt.

SQL-Prozedur erstellen

In dem Zielbericht ist es möglich, den Mitarbeiter zu filtern oder alle Mitarbeiter anzuzeigen. Die Prozedur zum Abrufen der gesamten Historie wird hier als [dbo].[P_APP_SELECT_ALL_Employee_Salary] betitelt und enthält die Unterscheidung, ob ein einzelner Mitarbeiter oder das All-Element ausgewählt ist, d. h. der Parameter @Employee leer ist. Der Parameter @MaxRows wird stets von DeltaMaster übergeben und dient der Einschränkung der maximalen Zeilenanzahl. Die Beschränkung der Zeilen wird in diesem Beispiel ignoriert, der Parameter wird ungeachtet dessen beim Aufruf übergeben und muss daher Bestandteil der Prozedur sein.

CREATE PROC [dbo].[P_APP_SELECT_ALL_Employee_Salary](
	@MaxRows int = 100,
	@Employee varchar(1) = NULL
) AS
IF @Employee IS NULL BEGIN
	SELECT TOP (@MaxRows) * 
FROM [dbo].[Employee_Salary] FOR SYSTEM_TIME ALL
END;

IF @Employee IS NOT NULL BEGIN
	SELECT TOP (@MaxRows) * 
FROM [dbo].[Employee_Salary] FOR SYSTEM_TIME ALL
	WHERE	[Employee] = @Employee
END;

DeltaMaster-Bericht erstellen

In den Einstellungen eines (neuen) SQL-Durchgriffs wählen wir den „Daten-Durchgriff auf Basis einer gespeicherten Prozedur“.

Abb. 21: Allgemeine Einstellungen im SQL-Durchgriff

Im Reiter „Prozedur“ ist im ersten Freifeld der Name der Prozedur einzutragen, hier also P_APP_SELECT_ALL_Employee_Salary, um anschließend auf „Felder aktualisieren“ zu klicken. Da in diesem Bericht keine Eingabe erlaubt werden soll, wird für die Anzeige nur der Mitarbeiter als Parameter übergeben.

Abb. 22: Prozedur-Einstellungen im SQL-Durchgriff

Nach Bestätigung ist im Bericht die gesamte Historie zu sehen, durch Doppelklick auf die Spalte „Employee“ in Reihenfolge der Mitarbeiter.

Abb. 23: Gesamte Historie im SQL-Durchgriff in DeltaMaster

Auch die Filterung auf einen Mitarbeiter funktioniert.

Abb. 24: Filterung auf einen Mitarbeiter im SQL-Durchgriff

Darstellung eines Zeitpunkts im SQL-Durchgriff

Für die Abfrage eines spezifischen Zeitpunkts ist die Verwendung von AS OF notwendig. Im Folgenden wird eine Lösung gezeigt, bei der eine Zeitdimension angehängt wird, aufgeteilt in Periode und Uhrzeit. Über diese Dimension kann der gewünschte Zeitpunkt ausgewählt werden.

Datenbankobjekte erstellen

Die Grundlage der Perioden-Dimension wird die Tabelle [dbo].[Period] sein, während die Uhrzeit-Dimension auf [dbo].[Time] zeigt. Anschließend erstellen wir die Prozedur [dbo].[P_APP_SELECT_AsOf_Employee_Salary].

Für die [dbo].[Period] werden berechnete Spalten verwendet, um eine Sicht für weitere Ebenen einzusparen. Die Spalte [DateText] wird als Ebene für den Tag verwendet, da die Spalte [Date] vom Datentyp date ist und DeltaMaster dies automatisch zu datetime konvertiert und darstellt.

CREATE TABLE [dbo].[Period](
	[Date] date NOT NULL,
	[Month] AS (DATEPART(YEAR, [Date])*(100) + DATEPART(MONTH, [Date])),
	[Year] AS (DATEPART(YEAR, [Date])),
[DateText] AS (CONVERT(varchar, [Date], 104)),
CONSTRAINT [PK_Period] PRIMARY KEY CLUSTERED ([Date] ASC)
) ON [PRIMARY]

Mit einem Skript zum Einfügen von Tagen zwischen Start- und Enddatum kann die Tabelle befüllt werden.

DECLARE	@StartDate date = '20230101', @EndDate date = '20231231'
INSERT	[dbo].[Period]
SELECT	DATEADD(DAY, nbr - 1, @StartDate)
FROM (
SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
FROM      sys.columns c
) nbrs
WHERE   nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)

Auch bei der [dbo].[Time] wird eine berechnete Spalte für eine weitere Ebene verwendet.

CREATE TABLE [dbo].[Time](
	[Time] varchar(5) NULL,
	[Hour] AS (LEFT([Time], 2))
) ON [PRIMARY]

Die Uhrzeiten können hier als ein kartesisches Produkt aus den Stunden und Minuten erstellt werden.

DECLARE	@StartHour int = 0, @EndHour int = 23,
@StartMin int = 0, @EndMin int = 59

INSERT	[dbo].[Time] ([Time])
SELECT	[Time] = CONCAT(IIF([Hour]<10,'0',''), [Hour], ':', 
IIF([Minute]<10,'0',''), [Minute])
FROM (
	SELECT	[Hour] = @StartHour + nbr - 1
	FROM (
		SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
		FROM      sys.columns c
		) nbrs
	WHERE   nbr - 1 <= @EndHour - @StartHour
	) AS [Hour], 
	(
	SELECT	[Minute] = @StartMin + nbr - 1
	FROM (
		SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
		FROM      sys.columns c
		) nbrs
	WHERE   nbr - 1 <= @EndMin - @StartMin
	) AS [Minute]

Schließlich braucht es noch die Prozedur zur Darstellung der Tabelle.

CREATE PROC [dbo].[P_APP_SELECT_AsOf_Employee_Salary](
	@MaxRows int = 100,
	@Employee char(1) = NULL,
	@Time varchar(5) = NULL,
	@Hour varchar(2) = NULL,
	@DateText varchar(10) = NULL,
	@Month varchar(6) = NULL,
	@Year varchar(4) = NULL
) AS

DECLARE @AsOfDate datetime = DATETIMEFROMPARTS(
	RIGHT(@DateText,4), SUBSTRING(@DateText, 4,2), LEFT(@DateText, 2), 
	LEFT(@Time, 2), RIGHT(@time, 2), '00', '00')

IF @Employee IS NULL BEGIN
	SELECT	* 
	FROM	[dbo].[Employee_Salary]
		FOR SYSTEM_TIME AS OF @AsOfDate
END;

IF @Employee IS NOT NULL BEGIN
	SELECT	* 
	FROM	[dbo].[Employee_Salary]
		FOR SYSTEM_TIME AS OF @AsOfDate
	WHERE	[Employee] = @Employee
END;

Relationales Datenmodell erweitern

Über die Schaltfläche „Modellieren“ und das sich darin befindliche Kleiner-als-Zeichen (im Modellier-Modus) kann in die Auswahl der Tabellen zurückgekehrt werden.

Hier können nun auch die Tabellen „Period“ und „Time“ ausgewählt werden, um sie per Klick auf „Modell“ zum Datenmodell hinzuzufügen.

Abb. 25: Ergänzung der Tabellen „Period“ und „Time“ im Datenmodell

Beim Modellieren können diese neuen Quell-Tabellen ausgewählt werden.

Abb. 26: Auswahl der Tabellen „Period“ und „Time“ zum Modellieren

Sowohl für „Period“ wie auch „Time“ werden separate Dimensionen erstellt. Wie zuvor beschrieben, sollte die Spalte „DateText“ als Ebene für den Tag verwendet werden; in diesem Beispiel ist die Ebene dann in „Date“ umbenannt. Bei der Zeit sollten keine aufwändigeren Anpassungen notwendig sein.

Abb. 27: Dimensionserstellung für „Period“ und „Time“

DeltaMaster-Bericht erstellen

Auch bei der Erstellung des Berichts ist in den Einstellungen eines (neuen) SQL-Durchgriffs der „Daten-Durchgriff auf Basis einer gespeicherten Prozedur“ auszuwählen. Im Reiter „Prozedur“ sind die Einstellungen z. B. wie folgt vorzunehmen:

Abb. 28: Prozedur-Einstellungen für den Bericht in DeltaMaster

Nach der Bestätigung kann in den neuen Dimensionen ein Zeitpunkt ausgewählt werden, um so jeden beliebigen Stand der Tabelle abzurufen.

Abb. 29: Abruf des Stands der Tabelle zu einem bestimmten Zeitpunkt

Dateneingabe in der Grafischen Tabelle

In Grafischen Tabellen ist es nur möglich, Kennzahlen zu ändern. Jedoch lassen sich hier Visualisierungen wie Sparklines und Bissantz‘Numbers wie auch berechnete Elemente verwenden, um ein optimales Berichtslayout zu erzeugen. In den folgenden Abschnitten zeigen wir, wie die dafür notwendige Prozedur erstellt und welche Einstellungen in einer Grafischen Tabelle vorgenommen werden müssen.

SQL-Prozedur erstellen

Im Gegensatz zum SQL-Durchgriff wird für die Dateneingabe in der Grafischen Tabelle lediglich eine Prozedur benötigt, die mit DeltaMaster ETL generiert werden kann. Die Prozedur dafür ist [dbo].[P_BC_Generate_DeltaMasterTableProc_PIV]. Das Namensschema der dann verwendeten Prozedur ist ähnlich zum SQL-Durchgriff: [dbo].[P_Update_PIV_], hier also [dbo].[P_Update_PIV_Employee_Salary].

Es folgt das aus DeltaMaster ETL erzeugte Skript, welches Update, Insert und Delete in der Prozedur enthält:

CREATE PROCEDURE [dbo].[P_Update_PIV_Employee_Salary]
(
	@Employee varchar(255),
	@TransactionID varchar(255),
	@StepNumber varchar(255) = NULL,
	@MeasureName varchar(255),
	@NewValue varchar(255),
	@Operator varchar(10) = N'=',
	@OldValue decimal = NULL,
	@CustomOperator varchar(100) = NULL,
	@ErrorDesc varchar(255) = NULL OUTPUT
)
AS

--CREATED BY P_BC_Generate_DeltaMasterTableProc_PIV 


DECLARE @SQL varchar(max)
DECLARE @ChangeUserID varchar(50)


--------------------------------------------------------------------------------
-- Writeback entry
--------------------------------------------------------------------------------

--Clean UserID (only required if transactions are turned off)
SET @ChangeUserID = isnull(@ChangeUserID, '''' + system_user + '''')

--Check for existing record
-- OldValue is in DeltaMaster Versions beyond 6.4.4 always NULL

declare @oldvalueexists int = 1;

IF (@OldValue IS NULL)
BEGIN
	IF NOT EXISTS(
		SELECT TOP(1) 1
		FROM dbo.Employee_Salary
		WHERE
			Employee = @Employee
	)
	BEGIN
		set @oldvalueexists = 0;
	END
END

	
IF (@oldvalueexists = 1)
--	Record exists
	BEGIN	
		SET @SQL =
'		UPDATE dbo.Employee_Salary
		SET
			' + @MeasureName + ' = ' + isnull(@NewValue, 'NULL') + '
			
		WHERE
			Employee = ''' + @Employee + ''''

		EXEC(@SQL)
		IF @@error <> 0 PRINT @SQL
		
		--Check for full deletion of record
		IF @NewValue IS NULL
		BEGIN

			SET @SQL = 
'		DELETE FROM dbo.Employee_Salary
		WHERE 
			Employee = ''' + @Employee + ''' and
			Salary IS NULL'
			
			EXEC(@SQL)
			IF @@error <> 0 PRINT @SQL
		END
	END
ELSE
	--Record does not exist
	BEGIN
		SET @SQL =
'		INSERT INTO dbo.Employee_Salary
		(
			Employee,
			' + @MeasureName + '
		)
		VALUES
		(
			''' + @Employee + ''',
			' + @NewValue + '
		)'
		
		EXEC(@SQL)
		IF @@error <> 0 PRINT @SQL
	END

Bericht

In einer neuen Grafischen Tabelle können im Menü rechts unter „Einstellungen“ die Zeilen- und Spaltenachse befüllt werden.

Abb. 30: Befüllung der Zeilen- und Spaltenachse der Grafischen Tabelle

Anschließend rufen wir die „Eigenschaften“ über das Menü rechts neben dem Bericht auf und passen im Reiter „Dateneingabe“ die Bedingung für die Dateneingabe an – in diesem Fall kann „TRUE“ eingegeben werden.

Abb. 31: Anpassung der Bedingung für die Dateneingabe

Nun können die Werte auch in der Grafischen Tabelle verändert werden.

Abb. 32: Dateneingabefelder im Bericht in DeltaMaster

Fazit

Mit temporalen Tabellen lässt sich auf die notwendige Modellierungs- bzw. ETL-Logik zur Archivierung von historischen Zuständen von Datensätzen verzichten. Stattdessen können wir hier auf standardisierte Lösungen von Microsoft SQL Server zurückzugreifen. Dadurch reduziert sich der Aufwand bei gleichbleibender Objekt- bzw. Code-Qualität beispielsweise bei der Umsetzung von Slowly Changing Dimensions Typ 4. DeltaMaster von Bissantz kann mit diesen temporalen Tabellen problemlos umgehen.

In diesem Blogbeitrag wurde ein Einstieg in die Verwendung von temporalen Tabellen in DeltaMaster gegeben. Bei Interesse raten wir, sich mit weiteren Ressourcen zum Thema auseinanderzusetzen, z. B. deren Einschränkungen, verschiedenen Ansätzen zum User-Logging und dem Ändern (DDL) von temporalen Tabellen.