YTD
YTD ist eine immer wieder gebrauchte Funktion. Im Internet gibt es zahlreiche Artikel die diese und andere zeitliche Betrachtungen behandeln. Im Rahmen einer MDX Challenge bei uns im Büro unter den Kollegen (wobei das auch Ausbildungscharakter haben sollte) habe ich eine Aufgabe in den Raum gestellt.
Man entwickle eine YTD Berechnung auf der Basis eines reduzierten Adventure Works 2008 Enterprise OLAP Models (nur eine Measure Group "Internet Sales", nur eine Zeitdimension - in dieser nur Kalender-Zeit-Informationen, nur Dimensionen mit Bezug zu diese Measuregroup) welche für jede Kennzahl immer korrekt möglichst effizient den YTD Wert berechnet. Die Lösung soll eine Analysis Services Lösung sein - ein relationales vervielfachen der Bewegungsdaten ist nicht erlaubt.
Daraufhin habe ich mir mal überlegt was es diesbezüglich für Lösungen gibt (welche ich schon mal gemacht habe) und mir eine Aufstellung gemacht
- Der einfachste Standardweg - für jede Kennzahl einen YTD berechnete Kennzahl anlegen
- So wie die Time Intelligence - eine Hierarchie in der Zeitdimension mit der YTD Berechnung
- ähnlich der Time Intelligence - eine Utility Dimension mit der YTD Berechnung
- wie schon einmal versucht eine Lösung über Many to Many
- die Brachiallösung - für jedes Element seine eigene Rechenregel (habe ich noch nie gemacht und werde ich hier auch nicht evaluieren, aber sicher eine Möglichkeit)
Um diese zu verifizieren habe ich mir die Adventure Works Datenbank entsprechend aufbereitet.
- reduzieren auf das Notwendigste (entfernen von Measure Groups, Dimensionen und nicht benötigten Attributen)
- Erweiterung des Testdatenbestandes
- Erstellung der Utitlity Hierarchie in der Date Dimension
- Erstellung der Utiltity Dimension
- Erstellung von 4 Cubes welche die vier verschiedenen Lösungsmöglichkeiten abbilden
- Anlage der YTD Berechnungen
Was wird wohl die effizienteste Berechnung sein - die integrierte YTD MDX Funktion. Daraufhin habe ich nach den jeweiligen Schemata die entsprechenden Berechnungen angelegt.
- YTD berechnete Kennzahlen
- CreateMember CurrentCube.[Measures].[YTD Internet Sales Amount] AS Aggregate(Ytd(),[Measures].[Internet Sales Amount]);
- YTD in Utility Hierarchie der Zeitdimension
- CreateMember CurrentCube.[Date].[Utility].[YTD] AS Aggregate(Ytd(),([Date].[Utility].[Current]));
- YTD in Utiltiy Dimension
- ([Utility].[Utility].[YTD])=Aggregate(Ytd,([Utility].[Utility].[Current]));
- YTD by Many to Many
- Keine Rechenregel notwendig
Danach habe ich zu testen begonnen - über die normale Kalender Hierarchie waren die Ergebnisse mit YTD auch korrekt, aber über die Wochen waren sie leider falsch. Das Problem ist, dass YTD immer über eine Hierarchie arbeitet - was aber wenn mehrere oder keine da sind - YTD ist auf diese nicht so ohne weiteres anzuwenden. YTD über Many to Many hat verständlicherweise sofort überall korrekte Ergebnisse geliefert.
Daraufhin habe ich mir Gedanken gemacht, wie ich das YTD per Rechenregel überall korrekt machen kann, denn die Many to Many Lösung war die langsamste aller vier Lösungen. Gehen wir mal vom schlimmsten Fall aus - wir wollen ein YTD berechnen, haben aber gar keine Hierarchie. Ein Ansatz der über eine Hierarchie arbeitet ist nicht immer anwendbar - auch liefert das YTD bei Mehrfachauswahl nicht immer korrekte Ergebnisse. Seltsamerweise funktioniert das Many to Many in jedem Fall korrekt.
Nach längerem überlegen bin ich dann auf folgenden neuen Ausdruck für eine YTD Berechnung ohne Hierarchie gekommen der für mich in den meisten Fällen funktioniert (wesentlich häufiger als YTD), aber Lieder auch nicht immer. Die Grundannahme - von der aktuellen Zeitauswahl ermittle den letzten Tag - von diesem letzten Tag das Jahr - von diesem Jahr den ersten Tag - und dann Aggregiere alles zwischen dem ersten Tag in diesem Jahr und dem letzten Tag in der Auswahl. Leider wird durch diesen Ansatz die Berechnung auch nicht wirklich effizienter - funktioniert aber sowohl mit der Monats als auch der Wochenhierarchie, sowie auch nur einzelnen Attributen und Mehrfachauswahl.
Create Member CurrentCube.[Measures].[YTD Extended Internet Sales Amount]
AS
Aggregate
(
Head
(
Exists
(
[Date].[Date].[Date]
,Exists
(
[Date].[Calendar Year].[Calendar Year]
,Tail
(
Existing
(
[Date].[Date].[Date]
)
,1
)
)
)
,1
).Item(0)
:
Tail
(
Existing
(
[Date].[Date].[Date]
)
,1
).Item(0)
,[Measures].[Internet Sales Amount]
);
Diese Berechnung ist aber auch nicht perfekt - Anhand der vielen Funktionen lässt sich schon erahnen, dass die Performance wohl nicht so gut ist, und ich lasse außer acht, was passiert wenn ich über der Jahresebene bin - die Funktion sucht sich auch dort den letzten Tag - das Jahr und macht ein YTD für das letzte Jahr. Das kann schon mal zu unerwarteten Ergebnissen führen - zum Beispiel bei einer Abfrage von Tagen des Monats ohne Jahreseinschränkung, weil von 1 bis 30 2006 das letzte Jahr ist, aber der 31 als letztes Jahr 2004 hat - so sind die Daten von Adventure Works nun mal.
Der Ordnung halber muss ich auch festhalten, das die Many to Many Lösung über halb der Jahresebene ein YTD von Anbeginn der Zeit macht.
Das Ergebnis
Da die YTD Funktion sowieso nicht immer korrekte Ergebnisse liefert sollte sie eigentlich nicht aufgeführt werden, aber da sie in sehr vielen Fällen genügt belasse ich sie dennoch in meinen Vergleichen.
| YTD als Kennzahl | YTD über Utility Hierarchie Zeit | YTD über Utility Dimension | YTD über Many to Many | |
|---|---|---|---|---|
| YTD MDX Funktion - Kaltstart | 314ms | 333ms | 317ms | 1263ms |
| Erweitertes YTD (s.o.) 1) | 1219ms | 8345ms | 1464ms | 22ms |
| YTD MDX Funktion - zweiter Durchlauf | 30ms | 28ms | 26ms | 23ms |
| Erweitertes YTD (s.o.) - zweiter Durchlauf | 30ms | 25ms | 24ms | 22ms |
| YTD MDX Funktion - nach Clear Cache | 417ms | 338ms | 326ms | 1222ms |
| Erweitertes YTD (s.o.) 1) | 1301ms | 8788ms | 1405ms | 22ms |
| YTD MDX Funktion - zweiter Durchlauf | 38ms | 81ms | 44ms | 37ms |
| Erweitertes YTD (s.o.) - zweiter Durchlauf | 26ms | 25ms | 59ms | 29ms |
1)Der Server wurde hier nicht neu gestartet / der Cache nicht gelöscht. Many to Many profitiert vom bereits vorhandenen Cache während die anderen Rechnungen neue Caches aufbauen müssen.
Nochmal der Hinweise, dass dies kein repräsentativer Vergleich war. Jeder sollte das für seine Situation angepasst Testen.
Testumgebung war
- SQL Server 2008 Developer Edition RTM
- Dell Latitude E6400
Um diesen Vergleich selbstständig nachvollziehen zu können sind hier alle Daten zum herunterladen.