Bestandsgrößen in Flussgrößen umwandeln

Typische Bestandskennzahlen wie Lagermengen werden in Datenmodellen meist in Form von Bestandsgrößen realisiert. Dabei fallen oft große Datenmengen an. Außerdem hat der Datentyp „Bestandsgröße“ weitere Einschränkungen. Daher kann es sinnvoll sein, die Daten als Flussgrößen vorzubereiten und entsprechend zu modellieren. Dieser Beitrag zeigt ein abstrahiertes Beispiel, wie man diese Lösung umsetzt.

Vor über zehn Jahren haben wir im Rahmen dieses Blogs bereits mehrere Artikel zu den Eigenheiten und den Umgang mit Bestandsdaten verfasst (siehe allen voran den Auftakt der Bestandsbetrachtungen). Dort wird erläutert, dass Kennzahlen unter anderem anhand ihres Typs unterschieden werden und demnach entweder Fluss- oder Bestandsgrößen sind. Dabei zeigt sich, dass eine unmittelbare vollständige Abbildung von Bestandsgrößen ein sehr hohes Datenvolumen fordert, wobei die tatsächlich stattfindenden Bestandsveränderungen oft um Größenordnungen kleiner sind. So nennt der Artikel ein Beispiel, in dem das Datenvolumen der Veränderungen nur 1,5 Prozent der ausführlichen Bestandsdaten darstellt. In Kombination mit der LastNonEmpty-Aggregation, die bei der Verwendung von Bestandsgrößen üblich ist und mehrere Unzulänglichkeiten birgt, können in der Praxis Schwierigkeiten bei der Verwendung von Bestandskennzahlen auftreten.

In einem unserer Kundenprojekte entstand der Wunsch, das hohe Datenvolumen von Bestandskennzahlen zu reduzieren. Schauplatz ist eine Microsoft SQL & Analysis Services On-Premises-Umgebung. Bei den Bestandskennzahlen ging es um 100 Millionen Zeilen pro Jahr – Tendenz steigend – deren Datenvolumen täglich lange Zeit zur Verarbeitung braucht und Speicherplatz im Gigabyte-Bereich belegt. Dabei existieren, abgesehen von dem Zeitstempel, exakt gleiche Zeilen mehrfach, wie in Tabelle 1 vereinfacht illustriert.

Tabelle 1: Beispiel Daten mit Bestandsgrößen

Eine Alternative ist, nur die Bestandsveränderungen zu protokollieren und so als Flussgröße darzustellen (vgl. Tabelle 2).

Tabelle 2: Beispiel Daten mit Flussgrößen

Summiert man die Spalten „Menge“ und „Wert“ in Tabelle 2, so erhält man den Bestand, gültig bis zum gewählten Zeitstempel. Für das Datenmodell bedeutet dies, dass die Kennzahlen ab dem zeitlich ersten Datenpunkt aggregiert werden müssen.

In diesem Beitrag untersuchen wir, wie eine solche Umwandlung von Bestands- zu Flussgrößen aussehen kann.

Grundsätzliche Implementierung

Im Folgenden wird gezeigt, wie Daten mit Microsoft SQL transformiert und in Analysis Services mit der sogenannten Ewigkeitskumulation summiert werden.

SQL-Komponente

Die vorgestellte Lösung basiert auf dem Ansatz, zuerst nachfolgende Zeilen zu ermitteln und anschließend nur diejenigen zu behalten, die Bestandsveränderungen enthalten. Aus logisch-technischen Gründen werden noch zwei weitere Unterscheidungen benötigt:

1. Existiert der Bestand nur zu einem Zeitpunkt, d. h. die Kombination der Elemente der Dimensionen respektive zusammengesetzte Primärschlüssel sind einmalig, so wird die Zeile dupliziert. Das Duplikat wird um eine Zeiteinheit (z. B. einen Tag) in die Zukunft versetzt und mit -1 multipliziert, um diesen mit der folgenden Zeiteinheit auszubuchen. Dies jedoch nur, wenn es sich nicht um einen „neuen“ Bestand handelt, also die zuvor erwähnte Kombination mit der aktuellen Datenlieferung (hier: CURRENT_TIMESTAMP) auftaucht.
2. Verändert sich der Bestand zum zweiten Zeitstempel, so wird aufgrund des Aufbaus der Query auch hier ein Duplikat der ersten Zeile erstellt, welches auf die nächste Zeiteinheit datiert ist und die Bestandsveränderung enthält.

Zur Vereinfachung wird nur die Dimension „Produkt“ und die Kennzahl „Menge“ betrachtet. Alle Zeilen stammen aus der Tabelle „dbo.Bestand“.

Unterabfrage: Daten der nachfolgenden Zeile

Für jede Zeile wird mit Hilfe der LEAD()-Funktion der nächste Zeitstempel ([Next_TagID]), die nächste Menge ([Next_Menge]) und allgemein die Zeilenzahl mit ROW_NUMBER() unter einer aufsteigenden Sortierung nach [TagID] ermittelt.

SELECT	[TagID], [ProduktID], [Menge],
[Next_TagID] = LEAD([TagID]) OVER (PARTITION BY [ProduktID] ORDER BY [TagID]),
[Next_Menge] = LEAD([Menge]) OVER (PARTITION BY [ProduktID] ORDER BY [TagID]),
[RowNumber] = ROW_NUMBER() OVER (PARTITION BY [ProduktID] ORDER BY [TagID])
FROM		[dbo].[Bestand]

Anstatt der Window-Function (OVER …) ist auch ein JOIN auf dieselbe Tabelle „dbo.Bestand“ denkbar. Entscheidend ist, dass die nachfolgenden Daten hinzugezogen werden.

Für die spätere Hauptabfrage wird angenommen, dass diese Abfrage in einer Sicht „dbo.V_Bestand“ gespeichert ist.

Unterabfrage: Logisch-technische Unterscheidung

Für beide Fälle (Bestand mit nur einer Zeile; Bestandsveränderung bereits in der zweiten Zeile) wird zur Hilfe eine Sicht herangezogen, die aus zwei Zeilen mit den Werten 1 und 2 besteht.

SELECT [No] = 1 UNION ALL 
SELECT [No] = 2 

Auch hier wird angenommen, dass diese Abfrage die Sicht „dbo.V_RowNumbers“ zur weiteren Verwendung definiert.

Bestandteile der Hauptabfrage

Die Unterabfrage „dbo.V_Bestand“ ist die Grundlage und wird zwei Mal um die „dbo.V_RowNumbers“ erweitert. Vereinfacht gilt:

SELECT	[…]
FROM		[dbo].[V_Bestand]
/* Positionen mit nur einer Zeile sollen dupliziert werden, wobei das Duplikat mit -1 multipliziert wird. Ausbuchung jedoch nicht, wenn aktueller Datensatz (hier: heutiges Datum) */
LEFT JOIN	[dbo].[V_RowNumbers] AS [OneRow]
	ON	[V_Bestand].[RowNumber] = 1
	-- Ohne Nachfolger
	AND	[V_Bestand].[Next_TagID] IS NULL
	-- Nicht aktueller Auftrag
	AND	[V_Bestand].[TagID] <> CONVERT(date, CURRENT_TIMESTAMP)
/* Positionen, bei denen bereits in der zweiten Zeile eine Bestandsveränderung besteht, werden ebenso dupliziert, sodass das Original den Startbestand und das Duplikat die Bestandsveränderung enthält */
LEFT JOIN	[dbo].[V_RowNumbers] AS [Change]
	ON	[V_Bestand].[RowNumber] = 1
	AND	[V_Bestand].[Menge] <> [V_Bestand].[Next_Menge]

Dabei werden nur die relevanten Zeilen behalten. Diese sind:

1. der Startbestand,
2. der (derzeitige) Endbestand – falls vorhanden
3. die Bestandsveränderungen

WHERE	-- Start-Bestände
[V_Bestand].[RowNumber] = 1 OR
	-- End-Bestände
	[V_Bestand].[Next_TagID] IS NULL OR
	-- Bestandsveränderung
	[V_Bestand].[Menge] <> [V_Bestand].[Next_Menge]

Im SELECT-Abschnitt finden sich vor allem die technisch-logischen Transformationen wieder. So wird die [TagID] wie folgt ermittelt:

CONVERT(date, CASE
	-- Bestand mit nur einer Zeile
	WHEN	[OneRow].[No] = 1 THEN [V_Bestand].[TagID]
WHEN	[OneRow].[No] = 2 THEN DATEADD(day, +1, [V_Bestand].[TagID])
-- Bestand mit unmittelbarer Bestandsveränderung
WHEN	[Change].[No] = 1 THEN [V_Bestand].[TagID]
WHEN	[Change].[No] = 2 THEN DATEADD(day, +1, [V_Bestand].[TagID])
	-- Startbestand
	WHEN	[V_Bestand].[RowNumber] = 1 THEN [V_Bestand].[TagID]
	-- Zeitpunkt der Bestandsveränderung
ELSE	DATEADD(day, +1, [V_Bestand].[TagID])
END)

Die vorletzte Zeile, die den Zeitpunkt der Bestandsveränderung enthält, adressiert die durch das „WHERE“ zugelassene Zeile, bei welcher der nachfolgende Bestand vom aktuellen Wert abweicht. In Tabelle 1 entspricht dies beispielsweise der Zeile 5. Da die Bestandsveränderung zu der nachfolgenden Zeiteinheit stattfindet, muss diese zeitliche Differenz (hier: ein Tag) addiert werden.

Neben der Zeit-Dimension werden auch die Kennzahlen, hier also [Menge], angepasst und gegebenenfalls mit dem Nachfolger verrechnet oder mit -1 multipliziert.

CASE
	-- Bestand mit nur einer Zeile
	WHEN	[OneRow].[No] = 1 THEN [V_Bestand].[Menge]
WHEN	[OneRow].[No] = 2 THEN [V_Bestand].[Menge] * -1.0
-- Bestand mit unmittelbarer Bestandsveränderung
WHEN	[Change].[No] = 1 THEN [V_Bestand].[Menge]
WHEN	[Change].[No] = 2 THEN ISNULL([V_Bestand].[Next_Menge],0) 
– ISNULL([V_Bestand].[Menge],0)
	-- Startbestand
	WHEN	[V_Bestand].[RowNumber] = 1 THEN [V_Bestand].[Menge]
	-- Zeitpunkt der Bestandsveränderung
ELSE ISNULL([V_Bestand].[Next_Menge],0) 
– ISNULL([V_Bestand].[Menge],0)
END

Hauptabfrage

Zur Vervollständigung des Lösungsweges folgt nun die gesamte Abfrage, reduziert um Kommentare und um eine explizite Angabe der V_Bestand in den Spalten, wenn eindeutig.

SELECT [ProduktID], 
[TagID] = CONVERT(date, CASE
WHEN	[OneRow].[No] = 1 THEN [TagID]
WHEN	[OneRow].[No] = 2 THEN DATEADD(day, +1, [TagID])
WHEN	[Change].[No] = 1 THEN [TagID]
WHEN	[Change].[No] = 2 THEN DATEADD(day, +1, [TagID])
WHEN	[V_Bestand].[RowNumber] = 1 THEN [TagID]
ELSE	DATEADD(day, +1, [TagID])
END), 
[Menge] = CASE
WHEN	[OneRow].[No] = 1 THEN [Menge]
WHEN	[OneRow].[No] = 2 THEN [Menge] * -1.0
WHEN	[Change].[No] = 1 THEN [Menge]
WHEN	[Change].[No] = 2 
THEN ISNULL([Next_Menge],0) – ISNULL([Menge],0)
WHEN	[V_Bestand].[RowNumber] = 1 THEN [Menge]
ELSE ISNULL([Next_Menge],0) – ISNULL([Menge],0)
END
FROM		[dbo].[V_Bestand]
LEFT JOIN	[dbo].[V_RowNumbers] AS [OneRow]
	ON	[V_Bestand].[RowNumber] = 1
	AND	[V_Bestand].[Next_TagID] IS NULL
	AND	[V_Bestand].[TagID] <> CONVERT(date, CURRENT_TIMESTAMP)
LEFT JOIN	[dbo].[V_RowNumbers] AS [Change]
	ON	[V_Bestand].[RowNumber] = 1
	AND	[V_Bestand].[Menge] <> [V_Bestand].[Next_Menge]
WHERE	[V_Bestand].[RowNumber] = 1 OR
	[V_Bestand].[Next_TagID] IS NULL OR
	[V_Bestand].[Menge] <> [V_Bestand].[Next_Menge]

Cube-Script

Nach der notwendigen OLAP-Modellierung der Kennzahl mit dem Aggregationstyp „Sum“ wird noch Code in den Analysis-Services-Cube-Berechnungen benötigt. Sofern eine Kumulations-Dimension besteht (hier: [Cumulation]), kann dieser ein weiteres Element (hier: [Stock]) hinzugefügt werden.

CREATE MEMBER CURRENTCUBE.[Cumulation].[Cumulation].[Stock] AS  
NULL, VISIBLE = 1; 

Damit die Ewigkeitskumulation verwendet werden kann, wird für jedes Element außer dem All-Element pro Hierarchie der Zeit-Dimension (hier: [Period]) ein Tupel mit dem „nicht kumuliert“-Element (&[1]) gebildet und alle Periodenelemente bis zum gewählten Element zusammengerechnet.

SCOPE ([Cumulation].[Cumulation].[Stock], 
DESCENDANTS([Period].[Period],, AFTER));
THIS = AGGREGATE(
PERIODSTODATE([Period].[Period].[(All)], 
[Period].[Period].CURRENTMEMBER),
[Cumulation].[Cumulation].[Cumulation].&[1]);
END SCOPE;

Hinweis: Die Ewigkeitskumulation kann auch in anderen Szenarien verwendet werden, beispielsweise beim Modellieren von Wechselkonten in der Bilanz.

Weiterhin wird eine berechnete Kennzahl [Menge_Stock] erstellt, die aus dem Tupel der Kennzahl und dem erstellten Kumulations-Element besteht.

CREATE MEMBER CURRENTCUBE.[Measures].[Menge_Stock] AS 
([Measures].[Menge], [Cumulation].[Cumulation].[Stock]), VISIBLE = 1;

Abschließend kann im Frontend wie üblich mit der neuen berechneten Kennzahl gearbeitet werden.

Fazit

Wie an den Beispielen eingangs dargestellt, kann die Umwandlung von Bestandsgrößen in Flussgrößen eine Reduktion der Zeilenanzahl zur Folge haben und damit oftmals die Laufzeit verbessern. Natürlich hängt diese Verbesserung von der Komplexität der notwendigen Transformationen ab.

Einschränkungen und Vorteile

Die Möglichkeit, die Anzahl der Datensätze zu reduzieren, ist abhängig von der Konstellation der Daten. Beispielsweise werden Bestände, die nur zu einem einzigen Zeitpunkt bestehen, in der Zeilenanzahl verdoppelt, da sie in der darauffolgenden Zeiteinheit ausgebucht werden. Gibt es viele solche „kurzlebigen“ Datensätze, kann sich die Zeilenanzahl durch die Umwandlung in Flussgrößen sogar erhöhen.

Genereller Vorteil einer Umwandlung ist, dass Bestandskennzahlen summiert werden können, was üblicherweise mehr Möglichkeiten als die Verwendung des OLAP-Aggregationstyps LastNonEmpty erlaubt.

Besonderheiten im Kundenprojekt

Im Kundenprojekt, das den Anstoß für den Inhalt dieses Beitrags gab, war die Implementierung weiterer Logiken notwendig. Hier mussten fehlende und inkorrekte Daten berücksichtigt und Gruppierungen vorgenommen werden. Dadurch wurde das Verfahren komplexer und es war notwendig, Unterabfragen zwischenzuspeichern.

Der Aufwand zahlte sich allerdings aus: Die Anzahl der Zeilen im Datenmodell wurde gegenüber der bisherigen Bestandsgröße um den Faktor 10 verringert – eine Einsparung von 90 Millionen Zeilen, ausgehend von den eingangs beschriebenen 100 Millionen. Positiver Nebeneffekt: Fehlende Daten in den Bestandsgrößen stehen in den Berichten nicht länger als leere Werte, sondern werden für jede Zeiteinheit mit den zuletzt bekannten Daten dargestellt.