DAX: Uhrzeit-Measures

In meinem letzten Projekt hatten wir interessante Kennzahlen:

Es ging um Startzeiten von bestimmten Prozessen:

Gegeben war ein datetime-Feld “Beginn”.

Jetzt waren folgende Kennzahlen gewünscht:

  • Was ist der früheste Beginn?
    Zum Beispiel: In der KW9 um welche Uhrzeit haben folgende Maschinen jeweils begonnen?
    Maschine A: 8:00 Uhr
    Maschine B: 8:30 Uhr
    Maschine C: 7:30 Uhr
  • Wann war der durchschnittliche Beginn in einer Woche?
    Zum Beispiel:
    Mo 8:00 Uhr
    Di 9:00 Uhr
    Mi 8:30 Uhr
    Do 7:00 Uhr
    Fr 10:00 Uhr
    ergibt einen Durchschnitt von 8:30 Uhr

Wir haben es wie folgt implementiert:

Zunächst haben wir zwei berechnete Spalten definiert:

BeginnDatum als date(year([Beginn]); month([Beginn]); DAY([Beginn]))
BeginnUhrzeit als [Beginn]-[BeginnDatum]

Damit erhalten wir die Uhrzeit ohne Datum.

Damit ist die erste Kennzahl ganz einfach:

Erster Beginn:=MIN([BeginnUhrzeit])

Und der Durchschnitt ist auch nicht schwer:

Ø Erster Beginn:=Averagex(Values(‚Fakten_Operationen'[BeginnDatum]); [Erster Beginn])

Dabei ist der erste Parameter der Averagex-Funktion die Menge der Werte, nach denen die Kennzahl berechnet werden muss und worüber dann der Durchschnitt gebildet wird.

Deswegen haben wir hier die Datumswerte mit Values(‚Fakten_Operationen'[BeginnDatum]) verwendet.

Dies lässt sich natürlich einfach verallgemeinern.

XtractIS: Variablen verwenden

Die Theobald-Komponente XtractIS habe ich schon in vielen Projekten eingesetzt. Sie funktioniert wunderbar zum Zugriff auf SAP—Daten.

Bei XtractIS Table hat man einen WHERE-Bereich, in dem man eine Bedingung formulieren kann.

Hier zeige ich, wie man dafür Variablen verwendet, also zum Beispiel die Abfrage auf einen bestimmten (aber dynamisch festzulegenden) Buchungskreis oder ein anderes Kriterium einschränken kann.

Anders als sonst im SSIS funktioniert das nicht über Expressions, sondern sogar noch einfacher.

Man kann die SSIS-Variable einfach in die WHERE-Bedingung schreiben, wobei man die Syntax [@Namespace::Variablenname] verwendet.

Beispiel:

VariableInXtractISTable

Quarantäne-Teil 3: Anlegen der zusätzlichen Komponenten

Nachdem wir im letzten Blog-Kapitel alle Komponenten eines Data Flows durchlaufen konnten und dazu erkennen konnten, ob eine Fehlerbehandlung möglich und noch nicht vorhanden ist, müssen wir nun die Fehlerbehandlung in diesen Fällen einbauen.

Die Fehlerbehandlung soll wie folgt funktionieren:

  • Fehlerbehandlung auf Redirect Row einstellen
  • Den Error Output in eine neu zu erstellende Derived Column leiten.
  • In der Derived Column sollen einige zusätzliche Attribute wie Fehlermeldung, aber auch Primärschlüssel hinzugefügt werden
  • Das Ergebnis läuft dann in einen UNION ALL, der bereits im Paket existiert.

Damit ist die Quarantäne noch nicht ganz fertig. Das UNION ALL führt dann zu einem SQL-Server-Ziel. Dort werden die Spalten aus der Union All in die Datenbank geschrieben. Diesen Teil habe ich, da er nur einmal (je Data Flow) zu erstellen ist, aber manuell gelöst. Hier ist der Aufwand geringer als eine automatisierte Lösung zu implementieren.

Fehlerbehandlung auf Redirect Row umstellen

Interessanter Weise gibt es unterschiedliche Arten von Fehlerbehandlungen in SSIS. Bei manchen Komponenten wird die Fehlerbehandlung einzeln für jede Spalte (z.B. derived column) definiert, in anderen nur global für die gesamte Komponente, in manchen auch für beides.

Der Lookup hat beide Fehlerbehandlungen:

image

Hier ein Beispiel für die Fehlerbehandlung einer Derived Column:

image

Im Advanced Editor sieht das dann so aus:

image

Und die globale Komponenten-Fehlerbehandlung wird nicht verwendet:

image

Wichtig ist zu bemerken, dass diese Einstellungen natürlich nicht in dem Error Output, sondern in dem normalen (bzw. allen normalen) Output(s) und Input(s) gemacht werden müssen.

Deswegen durchläuft der Code alle Inputs und alle Spalten aller Inputs und das gleiche für die Outputs:

For Each outp As IDTSOutput100 In comp.OutputCollection
‚ globale Fehlerhandlung
If outp.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent Then
outp.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow
End If
If outp.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent Then
outp.TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow
End If
‚Fehlerbehandlung je Spalte
For Each col As IDTSOutputColumn100 In outp.OutputColumnCollection
If col.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent Then
col.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow
End If
If col.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent Then
col.TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow
End If
Next col
Next outp

Und das gleiche für die Inputs:

For Each inp As IDTSInput100 In comp.InputCollection . . .

Am besten merkt man sich noch in einer boolschen Variablen, ob ein Redirect Row eingestellt wurde. Nur dann darf man nämlich den nächsten Schritt machen.

Dies ist wichtig, da die Fehlermeldungen bei der SSIS-API-Entwicklung meist kryptisch sind, da .NET-Wrapper von der API verwendet werden.  

Eine Derived Column-Komponente erstellen

Wir erstellen sie so:

‚neue Derived Column
Dim compDerivedCol As IDTSComponentMetaData100 = pipe.ComponentMetaDataCollection.New()
compDerivedCol.ComponentClassID = „DTSTransform.DerivedColumn“
Dim DesignDerivedTransformColumns As CManagedComponentWrapper = compDerivedCol.Instantiate()
DesignDerivedTransformColumns.ProvideComponentProperties()
compDerivedCol.Name = „Fehler_“ & comp.Name
compDerivedCol.InputCollection(0).ExternalMetadataColumnCollection.IsUsed = False
compDerivedCol.InputCollection(0).HasSideEffects = False

Man beachte das Initiate(). Dadurch wird die derived Column wie im Visual Studio auch instantiiert. Das heißt, man kann auf alle Voreinstellungen zugreifen.

Den Fehler-Output und die Derived Column verbinden

Hierzu legt man einen neuen Pfad an, der den Error Output (den wir im letzten Blog gefunden und gemerkt hatten) mit der Derived Column verbindet:

Dim path As IDTSPath100 = pipe.PathCollection.New()
path.AttachPathAndPropagateNotifications(comp.OutputCollection(nrOfErrorOutput), compDerivedCol.InputCollection(0))

Zusätzliche Spalten in der Derived Column anlegen

Da man das immer wieder braucht, habe ich dazu eine Methode erstellt.

Diese erhält als Parameter:

  • die Komponente (compDerivedCol)
  • den Namen der Spalte
  • den Datentyp, also zum Beispiel Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4
    Hier kann man wunderbar den vorhandenen enum verwenden.
  • die Länge des Datentyps – nur bei Strings (o.ä.) nötig. Bei Integer kann man 0 angeben. Das System macht es automatisch richtig.
  • Die Formel, also z.B. „(DT_WSTR,50)@[System::PackageName]“ für den Paketnamen – oder zum Auswerten eigener Variablen. Die Formel darf keine Anführungszeichen (“) enthalten.
  • Optional die LineageID – dazu komme ich im Anschluss

Hier der Code:

Private Sub addNewColumn2DerivedComponent(derivedColumnComponent As IDTSComponentMetaData100, name As String, dataType As Wrapper.DataType, dataTypeLaenge As Integer, expression As String, Optional lineageIDKeyColumn As Integer = 0)
Dim neueSpalte As IDTSOutputColumn100 = derivedColumnComponent.OutputCollection(0).OutputColumnCollection.New()
neueSpalte.Name = name
neueSpalte.SetDataTypeProperties(dataType, dataTypeLaenge, 0, 0, 0)
neueSpalte.ExternalMetadataColumnID = 0
neueSpalte.ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure
neueSpalte.TruncationRowDisposition = DTSRowDisposition.RD_IgnoreFailure

Dim neueSpalteProp As IDTSCustomProperty100 = neueSpalte.CustomPropertyCollection.New()
neueSpalteProp.Name = „Expression“
If lineageIDKeyColumn = 0 Then
neueSpalteProp.Value = expression
Else
neueSpalteProp.Value = „(DT_WSTR,“ & dataTypeLaenge & „)#“ + lineageIDKeyColumn.ToString()
End If
neueSpalteProp = neueSpalte.CustomPropertyCollection.New()
neueSpalteProp.Name = „FriendlyExpression“
neueSpalteProp.Value = expression

End Sub

Hier einige Erklärungen dazu:

  • Am Anfang wird die Spalte angelegt mit dem entsprechenden Namen und Datentyp
  • Die derived column hat als Fehlertyp “Ignore Failure”, weil wir ja sonst die Idee der Quarantäne ad absurdum führen würden.
  • Dann werden zwei Properties angelegt, die notwendig sind – die Expression und die “friendly expression”. Mit der “Expression” rechnet SSIS, die “friendly expression” wird angezeigt.

Und jetzt die Geschichte mit der lineage-ID, wie versprochen:

Man kann auch in den Formeln (expression) auf bestehende Spalten zugreifen. Das funktioniert über die lineage-Id der Spalte, also beispielsweise so “(DT_WSTR,50)#17”. Die “friendly expression” muss nicht unbedingt angegeben werden.

Die lineage-ID kann man sich ermitteln, indem man alle Spalten des Inputs durchläuft und den Namen der Spalte kennt. Allerdings kommt dabei der Wrapper für managed code zum Einsatz, den ich vorher bereits angedeutet habe. Wir benötigen nämlich hier Designer-Funktionalität, wie sie im Visual Studio auch vorhanden ist. Deswegen sieht der Code etwas hässlich aus:

Private Function findeSpalte(comp As IDTSComponentMetaData100, nameSpalte As String, ByRef nameSpalteGefunden As String) As Integer
Dim inp As IDTSInput100 = comp.InputCollection(0)
Dim virtualInp As IDTSVirtualInput100 = inp.GetVirtualInput()
Dim virtualInpCols As IDTSVirtualInputColumnCollection100 = virtualInp.VirtualInputColumnCollection
Dim designer As CManagedComponentWrapper = comp.Instantiate()

nameSpalteGefunden = „“

For Each virtualCol As IDTSVirtualInputColumn100 In virtualInpCols
If virtualCol.Name = nameSpalte Then
designer.SetUsageType(inp.ID, virtualInp, virtualCol.LineageID, DTSUsageType.UT_READONLY)
nameSpalteGefunden = nameSpalte
Return virtualCol.LineageID
End If
Next

Return 0
End Function

Diese Funktion stellt auch den UsageType der Spalte auf Readonly. Dies erscheint zunächst unnötig, ohne dies funktioniert es  aber nicht. Dies spiegelt wider, was der Advanced Editor für die Spalten anzeigt, die in den Formeln verwendet werden:

image

In diesem Beispiel wurde die EinrichtungID-Spalte in einer Formel verwendet.

So habe ich die Primärschlüssel, die ich in der Quarantäne-Tabelle mit protokollieren wollte, gefunden.

Die Derived Column mit der UNION ALL verbinden

Das funktioniert genauso wie der Pfad zur derived column, muss also nicht im Detail beschrieben werden.

Damit sind wir nun fast fertig. Es sind lediglich ein paar Besonderheiten zu beachten – s. nächster Blog-Eintrag.

Quarantäne-Teil 2: Schleife über alle Komponenten eines Data Flows und Untersuchung bestehender Fehlerbehandlung

In dem letzten Blog-Eintrag hatten wir eine Schleife um alle Data Flows erstellt und eine Data Flow identifizieren können.

Heute wollen wir eine Schleife über alle Komponenten bauen und die Komponenten etwas genauer analysieren, insbesondere auf vorhandene Fehlerbehandlung.

Im letzten Kapitel hatte wir eine Methode processAllExecutables geschrieben. Diese enthielt in einer Schleife ein Executable mit Variablennamen e.

Über

If TypeOf e Is TaskHost Then

hatten wir ermittelt, dass es sich um einen Data Flow handelt.

Schleife über alle Komponenten

Nun erstellen wir uns zunächst ein paar Variablen:

Dim th As TaskHost
th = CType(e, TaskHost)
Dim name As String
name = CType(e, TaskHost).Name

If TypeOf th.InnerObject Is
Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe Then
Dim pipe As MainPipe = CType(th.InnerObject, MainPipe)

End If

Diese bedeuten:

  • den Namen des Data Flows in name
  • In th steht der TaskHost, also Data Flow
  • Der th hat ein inneres Objekt, die sogenannte Main Pipe. Diese beinhaltet weiterhin alle Data Flow-Komponente. An ihr sind wir also im besonderen interessiert. Wir haben sie in der Variablen pipe gespeichert.

Nun ist es ganz einfach alle Komponenten eines Data Flows zu durchlaufen, da sie in der Collection ComponentMetaDataCollection des MainPipe-Objekts enthalten sind:

For Each comp As IDTSComponentMetaData100 In pipe.ComponentMetaDataCollection

Next comp

Identifikation des bestehenden Error Handlings

Als erste wollen wir wissen, ob diese Komponente überhaupt grundsätzlich über ein Error Handling verfügen kann. Überraschender Weise gibt es nämlich Komponenten, an die man kein Error Handling anschließen kann, z.B. MultiCast, Union all, aber auch eine Skript-Komponente, die ja definitiv einen Fehler erzeugen kann.

Wie erkennen wir nun, ob eine Komponente ein Error Handling zulässt?

Jede Komponente hat eine Input- und eine OutputCollection. Diese sieht man auch in SSIS im erweiterten Editor, so z.B. bei einer Derived Column:

image

Auf der linken Seite sehen wir 3 Ports dieser Komponente. Davon finden sich im Objekt-Modell der erste in der InputCollection und die anderen beiden in der OutputCollection. Der Fehler-Output unterscheidet sich nun von anderen Outputs dadurch, dass die Property IsErrorOut auf true gesetzt ist.

Wenn wir nun die OutputCollection durchlaufen, können wir den Error Output ermitteln bzw. erkennen, dass diese Komponente gar keinen Error Output ermöglicht.

Wichtig! Wie in der SSIS-Oberfläche auch, sind diese Outputs sichtbar unabhängig davon, ob sie nachher mit Pfaden zu einer anderen Komponente verknüpft sind. Wenn also ein Error Output nicht im Objektmodell vorhanden ist, heißt das, dass keine Fehlerbehandlung für dieses Komponente möglich ist, nicht etwa, dass nur noch keine definiert worden ist.

Was uns jetzt noch interessiert ist, ob für diese Komponente, wenn sie einen Error Output hat, dieser bereits verwendet wird. Das ist dann der Fall, wenn es einen Pfad gibt, der den Error Output als Beginn des Pfades hat. Programmatisch bedeutet das, dass wir die PathCollection durchlaufen müssen und alle Anfangspunkte des Pfades mit dem IdentificationString des Error Outputs vergleichen müssen.

Ich habe das so implementiert:

‚ermittle Error Output
Dim hatErrorOutput As Boolean = False
Dim nrOfErrorOutput As Byte = 0
Dim ErrorOutputSchonVerwendet As Boolean = False

For outpNr As Integer = 0 To comp.OutputCollection.Count – 1
‚Ist das ein Error Output?
If comp.OutputCollection(outpNr).IsErrorOut Then
‚ja!
hatErrorOutput = True
nrOfErrorOutput = outpNr
‚wird dieser Error Output schon in einem Pfad benutzt?
‚schaue nach in pipe.PathCollection
Dim id As String
id = comp.OutputCollection.Item(outpNr).IdentificationString
For p As Integer = 0 To pipe.PathCollection.Count – 1
If pipe.PathCollection(p).StartPoint.IdentificationString = id Then
ErrorOutputSchonVerwendet = True
End If
Next p
End If
Next outpNr

Somit wissen wir

  • ob die Komponente ein Error Handling erlaubt (hatErrorOutput)
  • ob für diese Komponente ein Error Handling bereits definiert ist (ErrorOutputSchonVerwendet). Diesen Fall soll unser Quarantäne-Algorithmus nämlich ignorieren (Da hat sich der Entwickler des Pakets ja was dabei gedacht)

SSRS: Top 10 und Rest zum Auf- und Zuklappen

Die Aufgabe in diesem Blog-Eintrag ist es, bei einem gegebenen Dataset nur die ersten 10 Einträge anzuzeigen und den Rest über +/- auf- und zuklappen zu können.

Dazu gibt es einen guten Artikel: http://www.bidn.com/blogs/mikedavis/ssis/172/top-n-bottom-n-grouping-in-ssrs-2008

Allerdings verwendet dieser die Filter-Funktion “Top n” der Gruppe. (Und für den Rest Bottom (Anzahl-n)) Diese hat folgende Nachteile:

  • Wenn der 10. und der 11. Eintrag gleich sind, so zeigen die Top 10 in Wirklichkeit 11 Einträge. Das wäre noch nicht so schlimm, aber:
  • Damit sind natürlich auch der 10.-letzte und der 11.-letzte Eintrag gleich, somit kommen beide in dem Rest noch einmal, so dass beide Einträge doppelt erscheinen.

Das sieht dann so aus:

image

Der Autor hat zwar noch einen weiteren Artikel veröffentlich, in dem er das Problem versucht zu beheben – aber dabei verwendet er SQL-Funktionen. Ich möchte das Problem nur mit SSRS-Features lösen, um eine allgemein gültige Lösung zu haben, zumal in meinem Kundenprojekt MDX zum Einsatz kam.

Mit folgender Anleitung kann man dieses Problem beheben:

Stellen wir uns vor, wir haben ein Dataset wie:

image

Dieses Dataset zeigen wir in einer Tabelle an – ohne Gruppierung. Das sieht so aus – mit der “Details”-Gruppe:

image

Für die Details-Gruppe setzen wir folgende Eigenschaften:

  • Sortierung: Wert absteigend, danach Person aufsteigend. Letzteres ist notwendig, um die Reihenfolge deterministisch zu machen (falls der Wert gleich)
  • Sichtbarkeit: über Expression: =RowNumber(Nothing)>5
    Damit werden nur die ersten 5 Zeilen angezeigt.

Diese Details sollen die Top 5-Werte anzeigen.

Für die Gruppe, die die restlichen Werte anzeigen soll, legen wir angrenzend daran eine neue Detailgruppe an:

image

Diese Gruppe stellen wir so ein:

  • Details: image
  • Name: Details_Rest
  • Sortierung: Wert absteigend, danach Person aufsteigend.
  • Sichtbarkeit: über Expression: =RowNumber(Nothing)<=5
    Damit werden die ersten 5 Zeilen nicht angezeigt.
  • Die Spalten werden wieder mit Person und Wert besetzt.

Nun legen wir jeweils eine übergeordnete Gruppe an:

image

Die erste Gruppe stellen wir so ein:

  • Name: Person_Top
  • Gruppieren nach Person, das entspricht also unserer Detailzeile (Allgemein verwenden wir hier den Primärschlüssel)
  • Sortierung: wie die Detailgruppe
  • Sichtbarkeit: Anzeigen
  • Die neu angelegte Spalte Person löschen wir (Wichtig! Nur die Spalte löschen nicht die Gruppe löschen)
    image

Das gleiche machen wir für die zweite Detailzeile und richten auch hier eine übergeordnete Gruppe ein:

  • Name: Person_Rest
  • Gruppieren nach Person, das entspricht also unserer Detailzeile (Allgemein verwenden wir hier den Primärschlüssel)
  • Sortierung: wie die Detailgruppe
  • Sichtbarkeit: Anzeigen

Somit sieht unser Bericht so aus:

image

Ganz links fügen wir eine neue Spalte (außerhalb der Gruppe) hinzu:

image

Dann löschen wir wieder die unnötige Person-Spalte (ohne die Gruppe zu löschen):

image

Die Zellen ganz links setzen wir:

  • Name = txt_Top
  • Inhalt = Top 5

bzw.

  • Name = txt_Rest
  • Inhalt = Rest

Unser Bericht sieht dann so aus:

image

Jetzt wollen wir noch das Auf- und Zuklappen implementieren:

  • Gruppe Person_Top
    • Sichtbarkeit umschalten nach Textbox “txt_Top”
  • Gruppe Person_Rest
    • Sichtbarkeit: Ausblenden
    • Sichtbarkeit umschalten nach Textbox “txt_Rest”
      image

Jetzt muss noch etwas Kosmetik gemacht werden:

  • Die txt_Top-Textbox sollte ein – zu Beginn haben und kein +. Also müssen wir bei dieser Textbox den InitialToggleState auf true setzen
  • Wenn die Gruppe zusammengeklappt ist, sieht man die erste Person. Deswegen schreiben wir in die jeweiligen Spalten
    =iif(count(Fields!Person.Value)>1, Nothing, Fields!Person.Value) bzw.
    =iif(count(Fields!Person.Value)>1, Nothing, Fields!Wert.Value)

Geschafft!

image

und

image

Der Beispielbericht ist hier als Download verfügbar.

Zum Abschluss noch ein paar Worte zur Motivation, warum wir jeweils zwei Gruppen verwenden:

  • Die äußere Gruppe wird verwendet, um den “Toggle” zu ermöglichen (also das Ein- und Ausblenden), die inenre Gruppe, um zu definieren, welche Zeilen in der Gruppe sichtbar sind. Da beides über “Sichtbarkeit” geregelt wird, benötigen wir 2 Gruppen.
  • Die Alternative wäre, mit Filtern in der Gruppe zu arbeiten. Dann bräuchte man nur eine Gruppe. Leider darf aber RowNumber(…) nicht in Filtern verwendet werden, so dass dieser Weg ausscheidet.

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.

NULL-Werte bei Lookups

Lookups kommen bei SSIS-Data Flows ziemlich häufig vor. Zum Beispiel müssen bei der Verarbeitung von Fakten-Sätzen enthaltene Dimensions-Referenzen umgesetzt werden. Zum Beispiel könnte es sein, dass Deutschland im Quellsystem ‘D’ ist, im DWH aber mit 49 abgebildet wird.

Das ist mit Lookups natürlich ganz einfach.

Nun kommt es aber vor, dass nicht alle Fakten eine Referenz auf ein Land haben müssen, also NULL sind. Diese sollen dann auch NULL bleiben.

Wie kann eine solche Aufgabe gelöst werden?

Die Standard-Lösung wird so aussehen:

Vor dem Lookup wird über einen Conditional Split überprüft, ob das Land leer ist:

  • Wenn ja, dann wird der Zielwert auch auf NULL gesetzt
    (Dafür muss nichts gemacht werden, da dies beim folgenden UNION ALL automatisch erfolgt)
  • Wenn nein, dann wird der Lookup durchgeführt

Danach werden beide Pfade über einen UNION wieder zusammengeführt:

image

In meinem Beispiel sieht die Lookup-Quelle so aus:

SELECT N’D‘ as Country, 49 as nummer
UNION ALL
SELECT N’A‘ as Country, 43 as nummer
UNION ALL
SELECT N’CH‘ as Country, 41 as nummer

Diese Vorgehensweise hat mehrere Nachteile:

  • Über das UNION ALL wird immer ein neuer Buffer angelegt, was insbesondere bei großen Data Flows unnötig Speicher belegt (UNION ALL ist semi-blocking)
  • Die Lösung ist einigermaßen komplex
  • Falls eine Sortierung vorliegt, geht die Sortierung durch das UNION ALL verloren. Dies sollte nicht durch den Einsatz eines MERGE verhindert werden, da dies die Performance sehr negativ beeinflussen kann (vielleicht schreibe ich dazu mal einen eigenen Blog-Eintrag)

Deswegen hatten wir nach einer besseren Lösung gesucht:

Der erste Ansatz war folgender: Wir können beim Lookup Nicht-Treffer ignorieren und danach überprüfen, ob wir ein Mapping übersehen haben und dann selbst einen Fehler schmeißen:

imageDer Conditional Split enthält folgende Bedingung:

!(ISNULL(Country)) && ISNULL(nummer)

In der Skriptkompopnente wird ein Fehler mit folgendem Code erzeugt:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    bool p=false;
    ComponentMetaData.FireError(10, „Testkomponente“, „Country was not null, but could not be mapped to a country number“, „“, 0, out p);
}

Dieser Ansatz hat folgenden Vorteil:

  • Alle Komponenten sind synchron, benötigen also keine zusätzlichen Buffer.

– aber auch folgende Nachteile:

  • Die Lösung ist noch komplexer
  • Die Lösung ist unübersichtlich und schlechter wartbar, da ohne die Skriptkomponente der Lookup falsch programmiert wäre. Das muss aber der wartende Mitarbeiter wissen. Außerdem ist ein Error Handling “Fehler ignorieren” irreführend.
  • Skript-Komponenten haben selbst keinen Fehler-Output. Damit kann eine Skript-Komponente leider nicht mit der vorhin vorgestellten Quarantäne-Idee automatisch korrigiert werden.

Die beste Lösung ist somit eine ganz einfache:

Wir  stellen den Lookup wieder auf Fehler bei einem Nicht-Treffer. Wir ergänzen die Quelle des Lookups um NULL-Werte, also in meinem Beispiel

SELECT N’D‘ as Country, 49 as nummer
UNION ALL
SELECT N’A‘ as Country, 43 as nummer
UNION ALL
SELECT N’CH‘ as Country, 41 as nummer
UNION ALL
SELECT NULL, NULL

bzw. allgemein

SELECT * FROM <lookup-tabelle>
UNION ALL
SELECT NULL, NULL

Dann sieht der Data Flow ganz einfach aus:

image

Dies hat natürlich etliche Vorteile:

  • Die Lösung ist super-einfach.
  • Die Lösung ist natürlich synchron und somit sehr schnell.

Allerdings funktioniert diese Lösung nur, wenn Full Cache eingestellt ist:

image

Dies ist aber eh die meistens verwendete (da performanteste) Variante.

Die Ursache, warum die anderen Cache-Einstellungen nicht funktionieren, liegt darin, dass im SQL NULLs anders verwendet werden (sie können nicht mit = abgeprüft werden). In den anderen Cache-Einstellungen würden SQLs wie SELECT * FROM <lookuptabelle> where Country = NULL ausgeführt.

Error Handling im Data Flow eines SSIS-Pakets

In den meisten Komponenten innerhalb eines Data Flows in einem SSIS-Paket können als Error Handling folgende Einstellungen getroffen werden:

Englisch Deutsch Bemerkung
Fail Component Fehler bei Komponente Dies ist die Standard-Einstellung. Sie führt dazu, dass bei einem – wie auch immer gearteten Fehler – der gesamte Data Flow fehlschlägt. Noch nicht verarbeitete Daten werden nicht weiter verarbeitet.
Ignore Failure Fehler ignorieren Diese Einstellung würde bedeuten, dass trotz des Fehlers normal weitergearbeitet wird. Dies ist in der Regel nicht sinnvoll.
Redirect Row Zeile umleiten Die fehlerhafte Zeile wird umgeleitet und der aufgetretene Fehler kann durch einen speziellen Ablauf behandelt werden.

In der Regel ist es sinnvoll, während der Entwicklung “Fail Component” einzusetzen. Während der Entwicklung und der Tests möchte man ja schließlich wissen, ob und wenn ja, welche Fehler auftreten, damit man sie programmatisch beheben kann.

Auch für den produktiven Einsatz kann diese Einstellung sinnvoll sein, da sie dafür sorgt, dass keine fehlerhaften Daten ins DWH übernommen werden.

Auf der anderen Seite ist die Einstellung problematisch, da dann ein fehlerhafter Satz die ETL-Strecke zum Erliegen bringt. In einem meiner Projekte laden wir alle 10 Minuten aus einem Quellsystem, das sehr großzügig mit Eingaben vorgeht.  Für dieses Projekt haben wir eine Quarantäne definiert:

Alle Komponenten sollen auf “Redirect Row” gestellt werden und diese fehlerhaften Sätze werden in eine Quarantäne-Tabelle geschrieben (unter Angabe des Primärschlüssels und des aufgetretenen Fehlers).

Danach müssen noch “halbe” Datensätze entfernt werden – es kann ja sein, dass in einem Vater-Satz ein Fehler auftritt und dieser in die Quarantäne läuft, aber alle Kinder-Sätze (der 1:n-Beziehung) richtig durchlaufen würden. Diese sollen dann aber natürlich nicht im DWH landen.

Die Umstellung der Komponenten auf “Redirect Row” wollten wir dabei nicht manuell durchführen, da wir Data Flows mit ca. 150 Komponenten haben. Deshalb haben wir ein (VB).NET-Programm erstellt, das via SSIS API diese Komponenten umstellt. Dieses Programm werde ich in den nächsten Blog-Einträgen vorstellen.

Sichere Logging-Tabellen trotz @@identity

In meinem letzten Blog-Eintrag hatte ich geschrieben, man solle möglichst scope_identity() statt @@identity verwenden.

Ich hatte bei einem Kunden genau den Fall, dass wir nachträglich in einer bestehenden Datenbank-Applikation über Trigger Protokollierungen erstellen wollten, um genauere Informationen über ein Fehlverhalten zu bekommen.

Deshalb legten wir eine Logging-Tabelle an, die die entsprechenden Informationen aufnehmen sollte und einen Trigger auf bestimmte Tabellen, um diese Informationen zu sammeln und wegzuschreiben.

Leider hatte der damalige Entwickler @@identity und nicht scope_identity() verwendet, so dass eine Identity-Column in unserer Logging-Tabelle zu Fehlern führte: Beim INSERT erhält das aufrufende Programm eine falsche ID. Hoffentlich ist das Programm sonst sauber programmiert, dann wird man dies wahrscheinlich über krachende Foreign Key-Beziehungen erfahren.

Wir kann man so ein Problem aber lösen, wenn man nichts von dem zugrunde liegenden Programm weiß?

Man  könnte die Identity-Column in der Logging-Tabelle entfernen und stattdessen Unique Identifiers verwenden. Ich hatte ja schon einiges über die Nachteile von Unique Identifiers geschrieben.

In diesem Fall scheint es mir aber gerechtfertigt. Natürlich muss man die Spalte mit newSequentialId() initialisieren, also etwa so:

CREATE TABLE [dbo].[Logging](
    [LogID] [uniqueidentifier]  NOT NULL default(newSequentialId()),
    [Timestamp] [datetime] NOT NULL default(getdate()),
    [Logtext] [nvarchar](max) NULL,
CONSTRAINT [PK_Logging] PRIMARY KEY CLUSTERED
(
    [LogID] ASC
)
)

Dann ist die Änderung unschädlich für den uns unbekannten Code, der möglicherweise @@identity verwendet.

Meine Erfahrungen in der Business Intelligence Welt