Dieser Beitrag knüpft an den Blog „Ein Ansatz zur Lösung des LNE-Dilemmas“ an. Dort wird eine Lösung mit SQL dargestellt, die verwendet werden kann, wenn die Analysis-Services-Aggregatfunktion „LastNonEmpty“ nicht zur Verfügung steht. Im Folgendem wird eine Lösung mit MDX vorgestellt, die mögliche Bedenken der Performanz-Verringerung ausräumt und als geschickte Lösung in MDX betrachtet werden kann, wenn mit einem Bestandswert weitergerechnet werden muss. 

Allgemein

Der letzte nicht leere Wert wird immer dann benötigt, wenn Werte in unregelmäßigen Zeit­ab­ständen definiert sind, die sich unterjährig ändern können oder für ein gesamtes Jahr gültig sind. Um die Werte in Berechnungen und Auswertungen auf den einzelnen Zeitebenen verwenden zu können, muss der jeweils letzte gültige Wert verwendet werden.

Microsoft bietet hierfür in der Enterprise-Edition des SQL-Server Analysis Services die Aggregatfunktion „LastNonEmpty“ (LNE) an. Wenn diese Serverversion nicht im Einsatz ist, muss die Funktion nachgebaut werden.

Eine Lösung mithilfe einer Rekursion in MDX wird hier beschrieben:
Richard Lees on BI: Getting the last non empty value.

Eine SQL-Lösung wird in folgendem Blog aufgezeigt:
Ein Ansatz zur Lösung des LNE-Dilemmas – Data Warehousing (bissantz.de)

In beiden Beiträgen wird auch auf die Performanz hingewiesen. Eine Rekursion führt meistens zu einer Performanz-Verschlechterung, hier muss im Vorfeld das Datenmodell genau analysiert werden. Bei der SQL-Lösung wird darauf hingewiesen, dass die Anzahl der Zeilen in der Tabelle wächst. In manchen Anwendungsfällen kann dies zu einem großen Datenaufkommen führen.

Folgender Blog zeigt einen Lösungsansatz mit MDX im Cube-Skript, der das Arbeiten mit Bestandskennzahlen möglich macht, ohne, dass die Daten relational vorliegen müssen und eine Rekursion vermieden wird.

Beispiel-Szenario

Ziel ist es, eine Bestandsbetrachtung mit Bestandswerten zum tagesaktuellen Wechselkurs darzustellen. Die Bestandsdaten liegen in diesem Beispiel in einer Zu- und Abgangslogik vor. Hierfür kann mit Hilfe einer Endloskumulation der Zu- und Abgänge in MDX der tagesaktuelle Bestand berechnet werden. Wenn der Bestandswert in verschiedenen Währungen zu aktuellen Kursen betrachtet werden will, muss der Bestandswert täglich mit dem jeweiligen Tageskurs multipliziert werden. Eine reine Lagerbewegungsrechnung ist in diesem Fall nicht mehr möglich, da bei einer relationalen Vorberechnung der Kurs des Zu- oder Abgangs bis zur nächsten Buchung verwendet werden würde. Der Bestandswert ist nun bis zum aktuellen Tag gefüllt, da der Kurs noch nicht für die Zukunft vorliegt. Mit Hilfe der Aggregation des letzten leeren Wertes (LNE) auf den höheren Ebenen, kann der jeweils aktuelle Bestandswert ermittelt werden.

Folgender Lösungsansatz zeigt ein MDX, mit sehr performanten Komponenten, der die LNE-Berechnung im Cube-Skript mit tagesaktuellen Wechselkursen möglich macht. Diese Lösung kann herangezogen werden, wenn Bestandswerte weiter verrechnet werden müssen und eine zweite Kennzahl als Prüfkennzahl zur Verfügung steht.

Lösungsansatz

Berechnung der Endloskumulation

Die Endloskumulation ermöglicht die tagesgenaue Bestandsbetrachtung, die auf höheren Ebenen korrekt summiert wird. Dabei werden die Zu- und Abgänge bis ans Ende der Zeitdimension kumuliert. Hierfür wird im ersten Schritt ein Kumulationselement erstellt, dass jahresübergreifend die Werte aggregiert. Im zweiten Schritt wird die korrekte Bestandskennzahl (Stock_Value) gebildet, indem die Bestandskennzahl und das Kumulationselement in einem Tupel zu­sam­men­ge­fasst werden.

-- Endlosbestand
CREATE MEMBER CURRENTCUBE.Cumulation.Cumulation.Stock AS
	'Aggregate(
PeriodsToDate(
[Periode].[Periode].[(All)]
,[Periode].[Periode].CurrentMember
)
,[Cumulation].[Cumulation].[Cumulation].&[1]
)'
	,Visible=0;

-- Lagerbestand
CREATE MEMBER CURRENTCUBE.[Measures].[Stock_Value] AS
	'([Measures].[Stock_Value], [Cumulation].[Cumulation].[Stock])'
, Visible = 1
, ASSOCIATED_MEASURE_GROUP = 'StockData';

Berechnung des tagesaktuellen Bestandswertes

Die Multiplikation mit dem Wechselkurs ermöglicht die Betrachtung des Bestandes zum tagesaktuellen Wert, ohne dass der Bestandswert täglich in der Datenbank vorliegen muss.

-- Aktueller Kurs (Bestandswert)
SCOPE ([CurrencyView].[CurrencyView].[CurrencyView].&[3]
	, [Measures].[Stock_Value]
);
THIS = [Measures].[Stock_Value] 
* 
([Measures].[Exchange_Rate], [Currency].[Currency].[Currency].&[EUR]);
END SCOPE;

Nach dieser Berechnung liegen die Bestandswerte nur noch bis zum aktuellen Tag des Wechsel­kurses vor. An diesem Punkt muss der letzte gefüllte Tageswert mittels LNE an die aggre­gierten Ebenen weitergegeben werden. Dieser Schritt wird im nächsten Abschnitt aufgezeigt.

Berechnung LNE

Allgemein ermittelt die LNE-Funktion in einer definierten Menge, in diesem Fall in einem Zeitraum, den letzten nicht leeren Wert. Die Ermittlung sieht wie folgt aus:

/* 
Ermittlung des letzten gefüllten Wertes für die aggregierten Ebenen der      
Zeitdimension
*/
SCOPE ([Measures].[Stock_Value]
,[CurrencyView].[CurrencyView].[CurrencyView].&[3]
);
THIS = ([Measures].[Stock_Value]
,TAIL(
FILTER(
DESCENDANTS([Periode].[Periode].CurrentMember
     ,[Periode].[Periode].[Day]
)
,NOT ISEMPTY([Measures].[Exchange_Rate])	
)
,1
).item(0)
);
END SCOPE;

Wichtig hierbei ist, dass nicht der Bestandswert zur Prüfung verwendet wird, sondern eine Kennzahl, die eine geringere Dimensionalität aufweist und das letzte Element leicht ermittelt werden kann. Voraussetzung hierbei ist, dass die LNE-Ermittlung den identischen Wert zurückliefert. Würde in der Funktion „isempty“ der Bestandswert abgefragt werden, würde dies genau den Fall der Rekursion widerspiegeln: Einen Verweis auf sich selbst.

Einen weiteren Performanz-Vorteil liefert nachstehender Lösungsansatz. Das Ergebnis ist äquivalent, jedoch ist die Abfrage ca. 25 % schneller. Dabei wird die Zeitdimension in einzelne Datenscheiben unterteilt, damit die Abfrage die korrekte und reduzierte Datenmenge anspricht. Hierbei wird die bereits erarbeitete Scope-Anweisung in die Ebenen der Zeitdimension aufgeteilt. In diesem Beispiel in Jahr, Quartal und Monat. Wenn außerdem eine Hierarchie nach Kalenderwochen existiert, muss diese auch nach diesem Schema aufgeteilt werden.

-- Jahr
SCOPE ([Measures].[Stock_Value]
	,[CurrencyView].[CurrencyView].[CurrencyView].&[3]
,[Periode].[Periode].[Year].Members
);
THIS = ([Measures].[Stock_Value]
,TAIL(
FILTER(
DESCENDANTS([Periode].[Periode].CurrentMember
     ,[Periode].[Periode].[Day]
)
,NOT ISEMPTY([Measures].[Exchange_Rate])	
) 
,1
).item(0)
);
END SCOPE;

-- Quartal
SCOPE ([Measures].[Stock_Value]
,[CurrencyView].[CurrencyView].[CurrencyView].&[3]
,[Periode].[Periode].[Quarter].Members
);
THIS = ([Measures].[Stock_Value]
,TAIL(
FILTER(
DESCENDANTS([Periode].[Periode].CurrentMember
     ,[Periode].[Periode].[Day]
)
,NOT ISEMPTY([Measures].[Exchange_Rate])	
)
,1
).item(0)
);
END SCOPE;

Fazit

Sind Daten nicht für alle Periodenelemente vorhanden und die MDX-Aggregatfunktion LastNonEmpty nicht verfügbar, ist diese LNE-Berechnung ein komfortabler Weg, wenn mit Bestandswerten weitere Berechnungen durchgeführt werden müssen. Ein weiterer Vorteil ist, dass die Anzahl der Zeilen in der SQL-Datenbank nicht um ein Vielfaches erhöht werden muss und mit Hilfe einer zweiten Kennzahl der LNE-Wert performant ermittelt werden kann.