„Pivot und Unpivot – ist das nicht ein alter Schuh?“
Korrekt würde ich sagen wenn man sich die einschlägigen englischsprachigen Blogs zum Thema SSIS durchliest. Dennoch stehe ich gelegentlich selbst vor dem Problem, dass ich nicht aus dem Stehgreif weiß wie die beiden Transformationen in SSIS zu konfigurieren sind und muss mich auf die Suche begeben. Außerdem finden sich nur wenige deutschsprachige Seiten zu diesem Thema. Um dem entgegen zu wirken folgt mit diesem Blogbeitrag eine Anleitung zur Anwendung der Pivot- und Unpivot-Funktion mit SSIS.
Beginnen möchte ich mit der einfacheren von Beiden: UNPIVOT
Folgendes Bild soll zunächst exemplarisch verdeutlichen wie unsere Ursprungsdaten aussehen und wie der gewünschte Zustand nach der Transformation auszusehen hat.
Vorab könnt Ihr mit folgendem Statement die Ursprungsdaten herstellen damit Ihr das Ganze parallel testen könnt:
(
[id] [INT] IDENTITY(1, 1) NOT NULL,[land] [NVARCHAR](50) NOT NULL,[2009]
[MONEY] NULL,[2010] [MONEY] NULL,[2011] [MONEY] NULL
)
ON [PRIMARY]
go
SET IDENTITY_INSERT [dbo].[Beispiel_Unpivot] ON
INSERT [dbo].[beispiel_unpivot]
([id],[land],[2009],[2010],[2011])
VALUES (1,N'Deutschland',200000.0000,150000.0000,250000.0000)
INSERT [dbo].[beispiel_unpivot]
([id],[land],[2009],[2010],[2011])
VALUES (2,N'Schweiz',100000.0000,80000.0000,170000.0000)
INSERT [dbo].[beispiel_unpivot]
([id],[land],[2009],[2010],[2011])
VALUES (3,N'Frankreich',75000.0000,90000.0000,110000.0000)
INSERT [dbo].[beispiel_unpivot]
([id],[land],[2009],[2010],[2011])
VALUES (4,N'Spanien',50000.0000,65000.0000,95000.0000)
SET IDENTITY_INSERT [dbo].[Beispiel_Unpivot] OFF
Da die Unpivot-Funktion ein Element im Datenfluss ist benötigen wir zunächst einen Datenflusstask. In diesem wird als Datenquelle oben erstellte Tabelle abgefragt. Im nächsten Schritt verbinden wir die Datenquelle mit der Unpivot-Komponente.
Die Konfiguration dieser sieht wie folgt aus:
Eingabespalten:
Unter den Eingabespalten werden diejenigen ausgewählt, die zukünftig als Zeilen ausgegeben werden sollen.
Pass-Through:
Hier werden alle Spalten markiert, die nicht transformiert aber dennoch in der Zieltabelle enthalten sein sollen.
Zielspalte:
Hierbei handelt es sich um das neue Feld in dem die Umsätze für die einzelnen Jahre gespeichert werden.
Pivotschlüsselwert:
Diese Spalte enthält den neuen Spaltennamen für die in diesem Beispiel transformierten Spalten mit den Jahreszahlen.
Nachdem wir ein Ziel festgelegt haben sieht der Testdurchlauf wie folgt aus:
Als Ergebnis erhalten wir Daten die sich in einem normalisierten Zustand befinden.
Um die Ursprungstabelle wiederherzustellen können wir die Pivot-Komponente benutzen. Diese ist die weitaus kompliziertere was die notwendigen Einstellungen betrifft.
In einem neuen Datenflusstask wählen wir als Quelltabelle die im vorherigen Schritt gefüllte Tabelle aus (Dest_Unpivot). Im Pivot-Task werden alle Spalten als Eingabespalten ausgewählt.
Die nächsten Einstellungen werden unter dem Reiter Eingabe- und Ausgabeeigenschaften vorgenommen. Bei der dort zu setzenden PivotUsage kann zwischen vier verschiedenen Möglichkeiten gewählt werden.
| PivotUsage | Beschreibung |
| 0 | Die Spalte wird unverändert an die Ausgabe übergeben. |
| 1 | Die Spalte dient als Schlüssel oder Teil des Schlüssels, mit dem ein Recordset definiert wird. |
| 2 | Die Spalte definiert den Pivotvorgang. Die Werte in dieser Spalte sind Spalten im pivotierten Dataset zugeordnet. |
| 3 | Die Spalte enthält Werte, die den Spalten hinzugefügt werden, die beim Pivotieren erstellt werden. |
Für unser Beispiel ist folgende PivotUsage auf die Spalten anzuwenden:
Jahr: 2 / Umsatz: 3 / Land: 1
Hinweis: Es müssen mindestens die PivotUsage 2 und 3 sowie 0 oder 1 verwendet werden.
Umsatz (PivotUsage 3) ist die Spalte, deren Werte wir auf die neuen Spalten 2009, 2010 und 2011 verteilen wollen. Dafür werden unter den Ausgabespalten drei neue Spalten für die Jahreszahlen angelegt. Als Wert für SourceColumn muss die LineageID (nicht zu verwechseln mit der ID) aus der Umsatzspalte eingetragen werden (für alle 3 Jahre). Der Datentyp sollte sich automatisch auf Währung ändern. Für PivotKeyValue müssen die Jahreszahlen (2011 für Spalte 2011, 2010 für Spalte 2010 und 2009 für Spalte 2009) eingetragen werden.
Neben den Umsatzzahlen die auf die Jahresspalten verteilt werden benötigen wir noch eine Spalte für das Land. Auch dieser muss die LineageID der Eingabespalte Land als SourceColumn zugewiesen werden. Der Eintrag PivotKeyValue bleibt leer.
Ich habe im Vorfeld bewusst einen Fehler eingebaut, um auf folgenden Umstand aufmerksam zu machen. Ein Durchlauf unseres Pakets liefert folgendes Ergebnis:
Die Umsätze werden zwar auf die Jahre umgebrochen, leider noch mit einigen Schönheitsfehlern (zu viele Zeilen und NULL in den Feldern). Behoben wird dieses Problem durch eine Sortierung der Quelldaten welche als PivotUsage die 1 haben. Wir müssen unser Statement dahingehend anpassen, dass das Ergebnis sortiert nach den Ländern ausgegeben wird. Danach sollte der Datendurchlauf wie folgt aussehen:
An dieser Stelle einen weiterer Hinweis. Für alle Werte die sich in der Spalte mit der PivotUsage 2 (in diesem Beispiel das Jahr) befinden, müssen entsprechende Spalten in der Ausgabe angelegt werden. Entferne ich bei den Ausgabespalten beispielsweise 2009, erhalte ich bei einem Durchlauf den Fehler „[Pivot Umsatz [25]] Fehler: Der Pivotschlüsselwert ‚2009‘ ist ungültig.“.
Das gleiche Ergebnis liefert auch folgendes TSQL Statement:
p.[2009],
p.[2010],
p.[2011]
FROM [dbo].[dest_unpivot]
PIVOT ( Sum(umsatz)
FOR jahr IN ([2009],
[2010],
[2011]) ) AS p
Ich möchte dem Blogbeitrag folgende Ergänzung hinzufügen:
Besteht die Möglichkeit das Pivotieren direkt auf den Quelldaten vorzunehmen, sollte man diesen Weg gehen. Die umständliche Konfiguration der Pivotfunktion in SSIS benötigt einige Zeit. Außerdem ist diese auch in ihren Funktionen eingeschränkt. Füge ich zum Beispiel eine weitere Zeile für Deutschland mit dem Jahr 2009 hinzu, erhalte ich während des Durchlaufs den Fehler [Pivot Umsatz [25]] Fehler: Doppelter Pivotschlüsselwert ‚2009‘. Da die Aggregation nicht automatisch erfolgt, muss man zuvor die zu pivotisierende Spalte aggregieren.
Abschließend natürlich auch wieder der Hinweis, dass jegliche Kritik, Fragen oder auch Anregungen gern in den Kommentaren hinterlassen werden können.
Ist euch dieser Blogbeitrag nicht ausführlich genug, findet ihr weitere Blogbeiträge zum Thema unter folgenden Links:
BI Monkey – The Pivot Transformation
Protean – SSIS Pivot Component Example