Den letzten nicht leeren Wert (LastNonEmpty) in einer Datentabelle zu ermitteln, ist eine häufige Aufgabe in OLAP-Modellen, z. B. wenn es um Währungskurse geht, welche sporadisch erfasst werden, aber auf Tagesbasis zur Berechnung herangezogen werden müssen. Wenn die MDX-Funktion LastNonEmpty nicht zur Verfügung steht oder nicht genutzt werden kann, muss eine alternative Lösung gefunden werden. Wie dies relational mit der Funktion LEAD() (verfügbar ab SQL-Server 2012) aber auch ohne diese Funktion z. B. in SQL-Server 2008R2 gelöst werden kann, zeigt dieser Blogbeitrag.

Last Non Empty mit SQL

Wozu braucht man den letzten nicht leeren Wert? Ein häufiger Anwendungsfall ist die Nutzung von Währungsumrechnungen. Meist werden Währungskurse monatlich oder pro Quartal erfasst, aber die umzurechnenden Daten liegen auf Tagesbasis vor. Wird der Währungskurs auf den Erfassungstag des entsprechenden Monats geschrieben, enthalten alle anderen Tage des Monats keine Währungskursdaten, sodass man hier den letzten nicht leeren Wert verwenden muss.

Ein anderes Beispiel sind Kosten, die pro Jahr oder in unregelmäßigen Zeitabständen definiert werden. Sie können sich unterjährig ändern oder sie bleiben das gesamte Jahr gültig. Um diese Kosten in Berechnungen auf Quartals-, Monats-, Kalenderwochen- oder Tagesbasis nutzen zu können, muss der letzte gültige Wert verwendet werden.

In den meisten OLAP-Modellen wird hierfür die MDX-Aggregatfunktion LastNonEmpty eingesetzt, welche in der Enterprise-Edition von SQL-Server enthalten ist. Wer nicht über diese Edition verfügt und beispielsweise die Standard-Edition einsetzt und trotzdem den letzten nicht leeren Wert einer Datentabelle abrufen möchte, muss Alternativen finden.

Eine Lösung mithilfe von MDX findet man unter http://www.ssas-info.com/analysis-services-articles/50-mdx/2333-getting-the-last-non-empty-value
Eine Lösung auf Basis von SQL auf der Ebene der relationalen Datenbank wird im Folgenden vorgestellt.

Letzter nicht leerer Wert mithilfe der Funktion LEAD()

Ab der SQL-Server Version 2012 stehen die Funktionen LAG(), welche den Vorgänger und LEAD(), welche den Nachfolger eines Elements in einer Datentabelle ermittelt, zur Verfügung.

In diesem Blogbeitrag wird die Funktion Lead() verwendet, die an dieser Stelle kurz vorgestellt werden soll:

LEAD (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )
Scalar expression – die numerische Spalte einer Datenbanktabelle, deren Nachfolger ermittelt werden soll.
Offset – der Abstand des Nachfolgerwertes zum aktuellen Wert, immer ein ganzzahliger nicht negativer Wert.
Default – Der Wert, der zurückgeben werden soll, wenn es keinen Nachfolger gibt oder dieser NULL ist.
OVER
partition_by_clause – Angabe der Tabellenspalten, nach denen unterteilt werden soll.
order_by_clause – Angabe der Tabellenspalten nach denen sortiert werden soll.

Anhand eines Beispiels soll die Ermittlung des letzten nicht leeren Werts demonstriert werden.

Dafür wird auf der bekannten Demodatenbank Chair oder einer anderen Datenbank, die eine Tabelle mit Perioden wie die hier verwendete Tabelle T_Import_Periode_manuell enthält, mit dem folgenden SQL-Statement die Tabelle T_S_AVG_Costs erstellt und mit Werten gefüllt:

CREATE TABLE [dbo].[T_S_AVG_Costs](
	[Period] [int] NULL,
	[ProductID] [int] NULL,
	[Costs] [decimal](18, 2) NULL
) ON [PRIMARY]
GO

INSERT INTO T_S_AVG_Costs 
VALUES 
(201701,123, 50000),
(201710, 123, 55000),
(201801, 123, 56000),
(201803, 123, 57000),
(201701, 456, 30000),
(201707, 456, 33000),
(201801, 456, 35000),
(201805, 456, 35500),
(201701, 678, 25000),
(201801, 678, 25000)
GO

Die neu erstellte Tabelle enthält Kosten für verschiedene Produkte (hier nur durch die entsprechenden ProduktIDs dargestellt) und Monate. Die Kosten ändern sich in unregelmäßigen Abständen und sind nur für die Monate eingetragen, an denen sich der Wert tatsächlich ändert. Um auch in den Monaten, für welche keine Werte vorhanden sind, mit den Kosten rechnen zu können, werden mit folgendem SQL-Statement die Werte für die fehlenden Monate ergänzt:

WITH CTE (Periode, ProductID, Costs, Next_Periode)
AS
(
-- Ermitteln, innerhalb welcher Zeitspanne (Period) ein Wert (Costs) für ein Produkt gültig ist
SELECT
		Period,
		ProductID,
		Costs,
		LEAD(Period,1,0) OVER (PARTITION BY ProductID ORDER BY Period)
FROM T_S_AVG_Costs
),
CT_Periode (MonthID)
AS
(
-- Alle Monate aus einer Periodentabelle mit allen erforderlichen Datumselementen ermitteln
-- Soll eine andere Tabelle als Quelle für die Perioden verwendet werden, so kann die hier verwendete 
-- Tabelle T_Import_Periode_manuell entsprechend ersetzt werden

	SELECT DISTINCT 
		Year(Periode) * 100 + Month(Periode) AS monthID
	FROM T_Import_Periode_manuell
	WHERE Year(Periode) > 2016
),
CT_MaxPeriod (Periode, ProductID)
AS
(
	SELECT
		MAX(Period),
		ProductID
	FROM T_S_AVG_Costs
	GROUP BY ProductID
)

-- Alle Monate mit den entsprechenden Produkten und den jeweils gültigen Kosten bis zum vorletzten Da-tums—
-- eintrag selektieren
SELECT 
	p.monthID,
	c.ProductID,
	c.Costs 

from CTE c
INNER JOIN
CT_Periode p ON p.monthID >= c.Periode AND p.monthID < c.Next_Periode

-- Alle Monate bis zum Ende des Kalenderjahres pro Produkt mit dem letzten gültigen Kostenwert (Costs) hinzufügen
UNION ALL
 
SELECT
		p2.MonthID,
		m.ProductID,
		c2.Costs
FROM CTE c2
INNER JOIN CT_MaxPeriod m ON m.Periode = c2.Periode AND m.ProductID = c2.ProductID
INNER JOIN CT_Periode p2 ON p2.MonthID >= m.Periode AND LEFT(p2.monthid,4) = LEFT(m.Periode,4)
ORDER BY 2,1

Mit der ersten WITH_Klausel „CTE“ wird die Zeitspanne ermittelt, in welcher ein Kostenwert für ein Produkt gültig ist. Hier wird die Funktion LEAD() eingesetzt, um zu jedem Periodenwert pro Produkt den entsprechenden Nachfolger zu ermitteln. Gibt es keinen Nachfolger, gibt die Funktion den Wert „0“ zurück (siehe Abbildung 1, Spalte „NextPeriod“).

Abbildung 1: Ergebnis des Selects mit der Funktion LEAD()

Mit der nächsten WITH-Klausel „CT_Periode“ werden aus der Tabelle T_Import_Periode_manuell alle erforderlichen Perioden selektiert. Die Einschränkung auf Werte ab dem Jahr 2017 erfolgt hier, um die Anzahl der Datensätze im Beispiel der Übersichtlichkeit halber zu minimieren.

Die dritte WITH-Klausel „CT_MaxPeriod“ ermittelt den letzten Wert pro Produkt, für den ein Kostenwert existiert.

Mit dem sich an die WITH-Klauseln anschließenden SELECT-Befehl werden die Werte pro Produkt und Monat bis zum Ende des Jahres, in welchem sich der letzte Kostenwert befindet, zusammengestellt. So erhält man für jeden Monat pro Produkt die korrekten Kostenwerte.

Abbildung 2 Select-Ergebnis für ProductID = 123

Letzter nicht leerer Wert ohne Einsatz der Funktion LEAD()

Nun mag mancher Leser denken, schön und gut, aber ich setze noch SQL-Server 2008R2 ein und kann daher die SQL-Funktion LEAD() nicht verwenden. Für alle, die dies betrifft, gibt es auch eine Lösung ohne LEAD().

Verwendet wird hier ebenfalls die unter Punkt 1 erstellte und befüllte Tabelle T_S_AVG_Costs.

Danach setzten wir folgendes SQL-Statement ein:

WITH CT_Costs (Periode, Next_Periode, ProductID, Costs)
AS
(
-- Ermitteln, innerhalb welcher Zeitspanne (period) ein Wert (Costs) gültig ist
SELECT 
	c2.Period, 
	min(c1.Period) AS NextPeriod,
	c1.ProductID,
	MAX(c2.Costs)			
FROM T_S_AVG_Costs c1
INNER JOIN T_S_AVG_Costs c2 ON c2.period < c1.period 
AND c2.ProductID = c1.ProductID 
GROUP BY c2.Period, c1.ProductID

),
 CT_Periode (MonthID)
AS

(
-- Alle Monate aus einer Periodentabelle mit allen erforderlichen Datumselementen ermitteln		
	SELECT DISTINCT 
		Year(Periode) * 100 + Month(Periode) AS monthID
	FROM T_Import_Periode_manuell
	WHERE Year(Periode) > 2016
)

-- Alle Monate mit den entsprechenden Produkten und den jeweils gültigen Kosten bis zum vorletzten Da-tums-eintrag
SELECT 
	p.MonthID,
	c.ProductID
	, c.Costs
FROM CT_Costs c
INNER JOIN CT_Periode p ON p.monthID >= c.Periode AND p.monthID < c.Next_Periode

-- Alle Monate bis zum Ende des Kalenderjahres pro Produkt mit dem letzten gültigen Kostenwert (Costs) hinzufügen
UNION ALL

SELECT
	p2.MonthID,
	c2.ProductID,
	c2.Costs
FROM T_S_AVG_Costs c2
INNER JOIN 
	(SELECT
		MAX(Period) Periode,
		ProductID
	FROM T_S_AVG_Costs
	GROUP BY ProductID) m ON m.Periode = c2.Period AND m.ProductID = c2.ProductID
INNER JOIN CT_Periode p2 ON p2.MonthID >= m.Periode AND LEFT(p2.monthid,4) = LEFT(m.Periode,4)
ORDER BY 2,1

Mit der ersten WITH-Klausel „CT_Costs“ wird auch in diesem Beispiel die Zeitspanne ermittelt, in welcher ein Kostenwert für ein Produkt gültig ist, wobei dieser Select keinen Datensatz zurückgibt, wenn es keinen weiteren Folgewert gibt. Im Vergleich zu dem Ergebnis in Abbildung 1 fehlen hier die Werte für den letzten eingetragenen Wert mit NextPeriod = 0.

Abbildung 3: Select-Ergebnis ohne Funktion LEAD()

Die WITH-Klausel „CT_Periode“ selektiert wie im Beispiel in Punkt 1 alle Monatswerte ab 2017.

Im anschließenden SELECT-Befehl werden die Werte pro Produkt und Monat bis zum Ende des Jahres, in welchem sich der letzte Kostenwert befindet, zusammengestellt. Die Werte für die letzte gültige Periode müssen aus der Tabelle T_S_AVG_Costs ermittelt werden, da sie in „CT_Costs“ nicht enthalten sind. Diese Daten werden anschließend bis zum Jahresende fortgeschrieben.

Dieser erneute Zugriff auf die Datentabelle T_S_AVG_Costs ist der Nachteil bei der Selektion ohne die Verwendung der Funktion Lead(). Die im Beispiel verwendeten Tabelle enthält nur wenige Datensätze, was in der Praxis meist nicht der Fall ist. So kann der erneute Zugriff auf die Datentabelle Performanceeinbußen nach sich ziehen.

Fazit

Sind Daten nicht für alle Periodenelemente vorhanden und die MDX-Aggregatfunktion LastNonEmpty nicht verfügbar, können die Daten für die fehlenden Perioden per SQL fortgeschrieben werden. Das erhöht zwar die Anzahl der Zeilen in der entsprechenden Selektion, ermöglicht es aber ebenfalls mit dem jeweils korrekten Wert zu rechnen.

Ein weiterer Vorteil der Berechnung in der relationalen Datenbank ist, dass die Daten im Würfel bereits zur Verfügung stehen und nicht per MDX zur Laufzeit ermittelt werden.