Aus kumulierten Werten Monatswerte erzeugen

Es ist gar nicht so untypisch, dass kumulierte Werte geliefert werden, die in die Monatswerte zurückgerechnet werden müssen. Kumulierte Werte sind Werte, bei denen zum Beispiel der Wert vom Februar sich aus der Summe vom Januar und Februar berechnet. Selbstverständlich kann diese Aufgabe auch mit SQL gelöst werden. Häufig wird dafür ein Cursor eingesetzt, der aber die Verarbeitung langsam macht.

In diesem Demopaket finden Sie eine von vielen möglichen Lösungen, die ohne Cursor und SQL auskommt die ganz einfach und auch wirklich schnell diese Berechnung durchführt.

Das Paket geht davon aus, dass Sie die AventureWorks Datenbank auf dem lokalen Rechner installiert haben. Falls dies nicht der Fall sein sollte, passen Sie die Verbindungs-Manager an Ihre Umgebung an.

Rahmenbedingungen
In der Ablaufsteuerung finden Sie zwei Sequenzcontainer. Der linke Container ist aktiviert und der rechte deaktiviert. Wenn Sie beide Container ausführen, werden in der Tempdb zwei neue Tabellen dbo.OrderMonthly und dbo.OrderMonthlyNeu angelegt. In diese beiden Tabellen werden jeweils die Monatswerte geschrieben, die aus der Tabelle Sales.SalesOrderHeader und Sales.SalesOrderDetail erzeugt werden. Im Demopaket finden Sie auch ein SQL Script, mit dem überprüft werden kann, ob die Monatswerte auch wirklich richtig berechnet werden.

Die eigentliche Verarbeitung findet, wie könnte es auch anders sein, im Datenfluss statt. Die Datenquelle berechnet aus den Tageswerten in den beiden Tabellen kumulierte Monatswerte. Im normalen Leben werden diese Werte selbstverständlich schon berechnet geliefert.

Das Ergebnis wird über die ProductID und das OrderDate aufsteigend sortiert.

Eine der Transformationen, die im Datenfluss sehr viele Ressourcen benötigt ist die Sortierung. Wenn die Datenquelle bereits die Daten sortiert liefern kann, ist es in aller Regel günstiger diese Sortierung im Datenfluss weiter zu verwenden.

Damit SSIS weiß, dass es sich um eine bereits sortierte Datenquelle handelt, rufen Sie mit der rechten Maustaste bei der Datenquelle den Erweiterten Editor auf.

Beim Reiter Eingabe- und Ausgabeeigenschaften OLE DB Source Output wird die Eigenschaft ISSorted auf True gesetzt.

Unter dem Punkt Externe Spalten finden Sie die Spalten aus der Eingabe. Unter den Ausgabespalten tauchen die Spalten auf, die in die Datenfluss Pipeline weitergegeben werden.

Danach wird bei den Ausgabespalten die jeweilige SortKeyPosition gesetzt. Das erste Sortierkriterium wird auf 1 gesetzt, die anderen entsprechend. Es spielt keine Rolle, ob auf- oder absteigend sortiert wird. Entscheidend ist, das eine definierte Sortierreihenfolge vorhanden ist.

Beim überprüfen der Metadaten nach der Datenquelle finden Sie die Sortierung wieder.

Um die kumulierten Werte auf Monatswerte umzurechnen, wird die  Scriptkomponente für die Transformation verwendet. Die Scripttranformation kann als Datenquelle, Datenziel oder als Zeilentranformation eingesetzt werden.
Bei der Zeilentranformation wird der Code für jede einzelne Zeile ausgeführt. Da dieser Code aber kompiliert wird ist die Verarbeitungsgeschwindigkeit ausgezeichnet.

Beim Einsatz der Scriptransformation sind immer folgende Schritte in dieser Reihenfolge notwendig:

• Festlegen der Eingabespalten (nur die Spalten, die ausgewählt wurden, stehen innerhalb der Zeilentransformation zur Verfügung. Für jede Spalte muss festgelegt werden, ob sie nur gelesen (ReadOnly) oder auch verändert (ReadWrite) werden.
• Falls eine neue Spalte in den Ausgabestrom (Pipeline) eingefügt werden soll, dann muss diese Spalte dem Output 0 unter Ausgabespalten hinzugefügt werden. Dabei ist darauf zuachten, dass auch der richtige Datentyp festgelegt wird.
• Als nächstes kann der eigentliche Scriptcode erstellt werden.

Die Logik für die Verarbeitung ist nicht anspruchsvoll, da die Daten sortiert zeilenweise an die Funktion Input0_ProcessInputRow übergeben werden.
Die Variablen mJahr, mProductID und mWert sind ausserhalb der Funktion aber innerhalb der Klasse ScriptMain definiert, damit der Wert über die unterschiedlichen Zeilen erhalten bleibt.

Durch den Code der in den Events PreExecute und PostExecute hinzugefügt wurde, wird in Millisekunden berechnet, wie lange der gesamte Berechnungsvorgang wirklich dauert. Das Ergebnis wird über die Funktion Me.ComponentmetaData.FireInformation in den Status Reiter ausgegeben.
Diese Funktionalität hat aber mit der Aufgabenstellung nichts zu tun und soll nur zeigen, wie schnell diese Berechnung ist.

Nach dem Ausführen des Datenflusses wird die Anzahl der verarbeiteten Zeilen ausgegeben.

Wie beim testen eines Programms muss auch das Ergebnis einer SSIS Verarbeitung geprüft werden. Deshalb finden Sie im rechten Teil der Ablaufsteuerung den deaktivierten Sequence Container, der Vergleichsdaten für die Prüfung erzeugt. 

Wird der rechte Container ausgewählt und dann über die rechte Maustaste das Kontextmenü aufgerufen, so können alle Tasks innerhalb des Containers auf einmal aktiviert oder deaktiviert werden. Wenn beide Verarbeitungsschritte ausgeführt wurden, können Sie mit Hilfe des SQL Scriptes CheckResult.sql prüfen, ob die Berechnung richtig ausgeführt wurde. Die beiden Container können auch selbstverständlich beliebig oft und auch parallel ausgeführt werden.

Zusammenfassung

Die Rückrechnung von kumulierten Werten auf Monatswerten ist mit Hilfe der Möglichkeiten von SSIS kein Problem. Die Geschwindigkeit wird in den allermeisten Fällen einer SQL Lösung überlegen sein.
Wenn Sie spielen wollen, dann können Sie auch schon die kumulierten Werte mit Hilfe einer Scriptransformation berechnen. Die Logik ist sicher kein Problem.
Spielen Sie einfach mit dem Paket und schauen Sie sich den Code an.

Komponentenindex:

• Datenflusstask
• OLE DB-Quelle
• Script Transformation
• OLE DB-Ziel
• Sequenzcontainer
• Task SQL ausführen

erstellt am 28.3.2006