20 | 01 | 2019

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 317ms1263ms
Erweitertes YTD (s.o.) 1) 1219ms 8345ms 1464ms22ms
YTD MDX Funktion - zweiter Durchlauf 30ms 28ms 26ms23ms
Erweitertes YTD (s.o.) - zweiter Durchlauf 30ms 25ms 24ms22ms
YTD MDX Funktion - nach Clear Cache 417ms 338ms 326ms1222ms
Erweitertes YTD (s.o.) 1) 1301ms 8788ms 1405ms22ms
YTD MDX Funktion - zweiter Durchlauf 38ms 81ms 44ms37ms
Erweitertes YTD (s.o.) - zweiter Durchlauf 26ms 25ms 59ms29ms

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.