SSAS Tabular DAX: DATEDIFF-Ersatz

In (tabular) Cubes möchte man möglicherweise Berechnungen durchführen, bei denen die Anzahl der Tage zwischen zwei Datumswerten ermittelt werden sollen.

Überraschender Weise gibt es keine DateDiff-Funktion in DAX.

Zwar kursieren im Web einige Beispiele für einen Ersatz, allerdings gefallen sie mir nicht.

Hintergrundwissen

Deswegen schauen wir uns zunächst näher an, wie in (tabular) Cubes Datumswerte gespeichert werden:

Datumswerte werden intern als Zahlen gespeichert, wobei der ganzzahlige Anteil die Anzahl der Tage seit 30.12.1899 angibt und der Nachkommaanteil die Uhrzeit.

Wir können das ganz leicht im Cube sehen, indem wir ein Datum mit 1.0 (deutsch 1,0) multiplizieren (im DAX reicht *1. bzw. *1, – je nachdem welche Lokalisierung eingestellt ist ) – oder über value(…) in eine Zahl konvertieren.

Ich habe einen kleinen Cube erstellt – mit folgender Datenquelle:

select convert(datetime, ‚17.8.2011 17:53:12‘, 104) as DatumUhrzeit, convert(date, ‚17.8.2011‘, 104) as Datum
UNION ALL
select convert(datetime, ‚1.3.2000 06:13:27‘, 104), convert(date, ‚1.3.2000‘, 104)
UNION ALL
select convert(datetime, ‚5.3.2000 01:13:27‘, 104), convert(date, ‚5.3.2000‘, 104)
UNION ALL
select convert(datetime, ‚1.1.1900 5:30:00‘, 104), convert(date, ‚1.1.1900‘, 104)
UNION ALL
select convert(datetime, ‚31.12.1899 17:30:00‘, 104), convert(date, ‚31.12.1899‘, 104)
UNION ALL
select convert(datetime, ‚27.12.2014 20:22:55‘, 104), convert(date, ‚27.12.2014‘, 104)
UNION ALL
select getdate(), convert(date, getdate())

Dann habe ich jeweils eine berechnete Spalte – wie oben beschrieben – hinzugefügt. Das Ergebnis:

image

DateDiff von Dates

Um nun die Differenz zwischen 2 Date-Spalten zu ermitteln, muss man sie nur voneinander abziehen. Das Ergebnis hat allerdings den Datentyp DateTime, weswegen man das Ergebnis noch in ein int verwandeln muss:

([Datum2] –[Datum1]) * 1

oder

int([Datum2]-[Datum1])

Um das zu demonstrieren, habe ich meine Quelle angepasst und das Ergebnis sieht so aus:

image

DateDiff von DateTimes

Wenn wir aber 2 DateTimes haben, dürfen wir sie nicht einfach voneinander abziehen (also int([DatumUhrzeit2]-[DatumUhrzeit1])), da die Uhrzeiten keine rolle spielen sollen. Stattdessen müssen wir die Datumswerte zunächst nach int konvertieren (dabei rundet DAX immer ab) und dann subtrahieren:

int([DatumUhrzeit2])-int([DatumUhrzeit1])

image

Wir sehen, dass die falsche Methode immer dann einen zu niedrigen Wert ausweist, wenn das abgezogene Datum eine größere Uhrzeit als der Minuend aufweist.

Erweiterungen: Alter

Falls mein ein Alter bis heute berechnen will, kann man das mit der gleichen Logik machen. Die DAX-Funktionen sind:

  • now() liefert das heutige Datum inkl. aktueller Uhrzeit
  • today() liefert nur das heutige Datum

Diese Funktionen werden im übrigen beim Process aufgerufen und nicht beim Auswerten in einem Frontend-Programm

Erweiterungen: DateDiff in Sekunden

Ganz analog kann man natürlich vorgehen, um die Differenz in anderen Einheiten auszurechen:

Man multipliziert einfach die DateTime-Spalte mit dem entsprechenden Wert:

  • * 24 für Stunden
  • * 24 * 60 für Minuten
  • * 24 * 60 * 60 für Sekunden

und konvertiert dann die Zahl in ein int.

Allerdings kann so eine Differenz in Monaten bzw. Jahren nicht berechnet werden. Das müsste man zunächst wohl definieren (Was bedeutet 17.1.2015 18:33 – 18.3.2012 17:55 in Monaten?) und dann ggf. über if-Statements selbst berechnen.

Quarantäne–Teil 1: Schleife über alle Data Flows

In einem vorangegangenen Eintrag hatte ich die Idee der Quarantäne vorgestellt, für die wir ein VB-Programm geschrieben haben, das in einem SSIS-Paket das Fehlerhandling umstellt.

DLLs einbinden

Als erstes müssen wir die folgenden DLLs einbinden:

image

also:

  • Microsoft.SqlServer.DTSPipelineWrap
  • Microsoft.SqlServer.DTSRuntimeWrap
  • Microsoft.SqlServer.ManagedDTS

Diese müssen dann mit Imports (VB) bzw. using (c#) eingebunden werden – hier immer die VB-Beispiele:

Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports System.Runtime.InteropServices

Paket laden

Als erstes wollen wir das SSIS-Paket laden und wieder speichern:

Dim ssisApp As New Microsoft.SqlServer.Dts.Runtime.Application
Dim pName As String = “C:\meineProjekte\Paket.dtsx”
Dim package As New Package
package = ssisApp.LoadPackage(pName, Nothing)
ssisApp.SaveToXml(“neuerName.dtsx”, package, Nothing)

Alle Tasks ermitteln

Nun wollen wir in dem SSIS-Paket alle Tasks durchlaufen, um sie verändern zu können. Dazu enthält das SSIS-Paket eine Property “executables”, diese enthält alle Tasks der ersten Ebene.

Mit einer Schleife können wir diese durchlaufen:

For Each e As Executable In package.executables
Next

Allerdings erreichen wir dabei nur die Tasks der ersten Ebene.

In SSIS gibt es aber Tasks, die selber wieder Tasks beinhalten:

  • Sequence
  • For Each Loop
  • For Loop

Diese interne Struktur kann man einfach in der SSIS-Umgebung anhand des Package Explorers sehen. In meinem Beispiel habe ich eine Sequence mit 2 Data Flows  und nach der Sequence noch einen Data Flow (namens “skript”):

image

Um alle Tasks durchlaufen zu können, müssen wir also rekursiv vorgehen:

im Hauptprogramm:


processAllExecutables(package.Executables, 1)

und dann als eigene Methode:

Private Sub processAllExecutables(
ByVal executables As Executables,
ByVal depth As Integer)

For Each e As Executable In executables

Dim d1 As Integer = depth + 1
If TypeOf e Is ForLoop Then
processAllExecutables(CType(e, ForLoop).Executables, d1)
End If
If TypeOf e Is Sequence Then
processAllExecutables(CType(e, Sequence).Executables, d1)
End If
If TypeOf e Is ForEachLoop Then
processAllExecutables(CType(e, ForEachLoop).Executables, d1)
End If
Next
End Sub

Wie man sieht, kann man über TypeOf Is <<Typ>> abfragen, ob die Task e einen bestimmten Typ hat. Bei den oben genannten Typen rufen wir dann die selbe Methode rekursiv auf – ich habe der Übersichtlichkeit halber die Tiefe als zusätzlichen Parameter übergeben, auch wenn das nicht unbedingt notwendig ist.

Somit haben wir das Ziel des heutigen Blogs fast erreicht: Wir durchlaufen alle Tasks. Als nächstes müssen wir Data Flows erkennen und verarbeiten.

DataFlows werden in der SSIS-API als TaskHost bezeichnet. Deswegen können wir das so abprüfen:

If TypeOf e Is TaskHost Then

End If

Damit haben wir eine Schleife über alle Data Flows programmiert.