Wie im Teil I versprochen wollen wir uns nun mit einer weiteren Möglichkeit für Range-Lookups mit den Integration Services auseinandersetzen.

Dazu zur Erinnerung noch einmal unsere Quelltabelle, aus der wir die zugehörigen IDs ermitteln wollen:

Lösung mittels Lookup – Mit Caching

Wie wir festgestellt haben, verliert der Lookup bei ausgeschaltetem Caching enorm an Geschwindigkeit. Alternativ könnte man also versuchen, den Range-Lookup mit aktiviertem Caching zu realisieren. Da der Lookup dann aber nur genaue Übereinstimmungen als Treffer wertet, muss für jeden möglichen Wert eine Zeile in der Lookup Tabelle existieren. Angenommen die Einkommen sind im beschriebenen Fall als volle Eurobeträge gespeichert, ließe sich das mit folgender rekursiven Common Table Expression (CTE) realisieren:

WITH IncomeGroups(IncomeGroupID,Income, IncomeTo)

AS

(

SELECT    [IncomeGroupID],[IncomeFrom] as Income, [IncomeTo]

FROM    dbo._MATCH_IncomeGroup

WHERE    IncomeGroupID < 7

UNION
ALL

SELECT [IncomeGroupID], Income + 1, [IncomeTo]

FROM IncomeGroups

WHERE Income + 1 <= IncomeTo

)

SELECT Income, IncomeGroupID

FROM IncomeGroups

ORDER
BY 1,2

OPTION (MAXRECURSION 0)

Dieses Statement liefert im Management das folgende Ergebnis:

Dies könnte als Quelle für den Lookup Task verwendet werden der dann wie jeder andere gewöhnliche Lookup konfiguriert werden kann.

Anschließend müsste dann die oberste Grenze, die in der Quelle bewusst ausgelassen wurde (denn 999.999.999 Datensätze wollte ich nun wirklich nicht erzeugen) per Abgeleitete Spalte (derived column) gesetzt werden.

Für Situationen in denen sehr viele Datensätze verarbeitet werden müssen, könnte dies eine gute Alternative zu der Variante ohne Caching sein. Es wird aber auch schnell klar, dass auch dieses Verfahren seine Grenzen hat. Für eine Aufteilung in Altersklassen von Kunden, für die es nur sehr wenige Ausprägungen in den Quelldaten gibt, ist dies wahrscheinlich die beste Lösung. Wenn es aber darum geht Firmenumsätze, die in die Milliarden gehen, in Gruppen einzuteilen, wird sowohl die Abfragezeit der Rekursion, als auch der benötigte Speicherplatz den Rahmen sprengen. Spätestens aber wenn die Grenzen nicht mehr mit ganzen Zahlen definierbar sind, ist dann wirklich Schluss.

Um auch für diese Situationen gerüstet zu sein, wird sich der nächste und vermutlich letzte Teil dieses Artikels mit der dritten Alternative, dem Range-Lookup mittels Script Task auseinandersetzen.

Bekanntlich führen bei den Integration Services viele Wege zum Ziel. So kann selbst die Lösung einfachster Aufgaben bei unterschiedlichen Entwicklungsstilen beliebig komplex ausfallen. Heute möchte ich Ihnen meine vier beliebtesten Lösungen für die typische Aufgabe “Wie benenne ich eine Spalte im SSIS-Datenfluss um?” vorstellen. Die Aufgabe: zwei Spalten namens “given_name” und “surname” sollen in “FirstName” bzw. “LastName” umbenannt werden. Die Lösung: Sehen Sie selbst! Und wetten, Sie kennen mindestens einen der Wege noch nicht?

Weg #1: Abgeleitete Spalte

Der “Standardweg” zeichnet sich durch Übersichtlichkeit und Einfachheit aus. Mit dem Toolbox-Element “Abgeleitete Spalte” lassen sich bekanntlich neue Spalten erstellen. So können wir ganz bequem zwei neue Spalten mit den gewünschten Namen erstellen, deren Inhalt einfach aus den alten Spalten abgeleitet wird:

Der große Nachteil hierbei ist die Tatsache, dass unser Datenfluss von nun an vier Spalten enthält, wobei wir nur zwei benötigen. Ich halte meine SSIS-Pakete gerne übersichtlich, und spätestens wenn es an das Wegschreiben der Daten geht hilft es, wenn die Spalten in Bezeichnung und Anzahl übereinstimmen. Abhilfe schafft hier das “Union All” Element, welches das Löschen überflüssiger Spalten ermöglicht. Hinter dem Union-Element erscheinen dann nur noch die gewünschten Spalten:

Weg #2: Der Erweiterte Editor

Bei vielen Toolbox-Elementen lassen sich Ausgangsspalten auch direkt umbenennen, so z.B. bei dem Element “Suche”. Für alle gewünschten Suchspalten lässt sich ein Alias definieren:

Manchmal hilft auch der Blick in den “erweiterten Editor”, der für viele Toolbox-Elemente dieselbe Funktionalität bietet, nur eben etwas versteckter.

Weg #3: Das Union-Element

“In der Kürze liegt die Würze” – so oder so ähnlich könnte das Tooltip für das Union-Element lauten, denn es erlaubt die Umbenennung der Ausgangsspalten nach Belieben:

Aber Vorsicht! Diese Lösung erscheint solange praktikabel bis sich die Metadaten der betroffenen Spalten irgendwo überhalb des Union-Elements ändern. Dann erwartet Sie (je nach Art der Metadatenänderung) eine regelrechte “Klickorgie”, da die betroffenen Spalten aus dem Union-Element entfernt, neu eingefügt und umbenannt werden müssen. Das alles natürlich erst nachdem SSIS Sie freundlich mit einem Pop-Up auf die Metadaten-Änderung hingewiesen hat. Im schlimmsten Fall haben Sie beim Troubleshooting vergessen, wie die eigentliche Umbenennung lautete…

Weg #4: Abgeleitete Spalte für Fortgeschrittene

Einen sehr eigenwilligen Weg habe ich erst kürzlich entdeckt. Bei der Einarbeitung in ein bestehendes SSIS-Paket verfolgte ich den Verlauf einer Spalte, wobei mir auffiel, dass die Spaltenbezeichner vor und nach einem Element “Abgeleitete Spalte” komplett unterschiedlich waren und weder ersichtlich wurde an welcher Stelle eine Umbenennung stattgefunden hatte, noch welche Ausgangsspalte den Inhalt welcher Eingangsspalte trug. Nach langem Probieren war ich in der Lage folgenden “hack” zu rekonstruieren:

Zunächst nutzt man das Element “Abgeleitete Spalte” um den Inhalt der beiden Spalten mit deren Inhalt zu ersetzen – intuitiv, nicht wahr?

Anschließend folgt die Umbenennung, indem man in der linken Spalte den gewünschten Bezeichner einträgt und [Enter] drückt. Der screenshot zeigt, dass die erste Spalte (given_name) bereits umbenannt wurde und die zweite Spalte kurz vor der Umbenennung steht.

Von nun an sind die Spalten in diesem Element nur noch unter dem neuen Namen zu finden – keine Spur mehr von den alten Bezeichnern! Dies bezeugen auch die die Metadaten des folgenden Pfades:

Diese “gespenstische” Lösung realisiert die Spaltenumbenennung im Verborgenen und ist alles andere als transparent für den Nutzer. Viel schlimmer ist allerdings noch, dass die Zuordnung der Eingangs- zu den Ausgangsspalten überhaupt nicht mehr ersichtlich wird und man damit auf die Semantik der Bezeichner angewiesen ist. Semantik – igitt.

Im “daily business” bevorzuge ich übrigens wann immer es geht Weg #2 und dort wo mir nichts anderes übrig bleibt Weg #1. Ein wichtiges Kriterium beim Entwickeln von ETL-Paketen ist aus meiner Sicht die Lesbarkeit und Verständlichkeit, denn eher früher als später wird sich ein anderer Entwickler oder gar der Kunde mit Ihrem Paket auseinandersetzen müssen/wollen – erst dann zeigt sich wie lesbar Sie entwickelt haben!

Und? Alles kalter Kaffee für Sie? Oder war doch etwas Neues dabei? Haben Sie noch weitere Lösungen? Wie handhaben Sie die Spaltenumbenennung in Ihrem “daily business”? Ich freue mich auf Ihr feedback!

Um innerhalb einer Zeitdimension zurückliegende Member zu erreichen braucht es kein Voodoo, MDX stellt eine bequeme Funktion namens “PARALLELPERIOD” zur Verfügung, welche zu einem angegebenen Element (Bsp. August 2003) über eine Ebene der Zeitdimension (Monat, Quartal, Jahr …) eine bestimmte Anzahl von Schritten (12 Monate, 2 Jahre o. ä.) zurück oder vorwärts geht.

Zum Beispiel folgende Abfrage:

WITH
MEMBER LevelMonth_OneYearAgo AS
PARALLELPERIOD([Date].[Calendar].[Month],12,
[Date].[Calendar].CURRENTMEMBER).MEMBER_CAPTION
MEMBER LevelYear_OneYearAgo AS
PARALLELPERIOD([Date].[Calendar].[Calendar Year],1,
[Date].[Calendar].CURRENTMEMBER).MEMBER_CAPTION
SELECT {
LevelMonth_OneYearAgo
,LevelYear_OneYearAgo
} ON 0 ,
{
[Date].[Calendar].[Month].&[2003]&[8]
}ON 1 FROM [Adventure Works]

liefert zurück:

Obwohl das Hierarchielevel “[Date].[Calendar].[Calendar Year]” auf keiner Achse angegeben ist, kann es durch seine bestehende Attributrelation zur Ebene “[Date].[Calendar].[Month]” angesprochen werden .

Sind also Attributbeziehungen innerhalb der verschiedenen Zeitebenen definiert, können auch andere als in der Abfrage verwendete Ebenen der Zeitdimension mit PARALLELPERIOD verwendet werden.

Was aber passiert, wenn es im angegebenen zurückliegenden Zeitraum keinen Member gibt? Im AdventureWorks Cube 2008 ist der erste Member der Zeitdimension der erste Juli 2001. Ersetzt man den oben abgefragten Monat auf der Achse mit “[Date].[Calendar].[Month].&[2002]&[6]” liefert die Abfrage folgendes zurück:

Das Ergebnis für PARALLELPERIOD mit Level “Month” ist korrekt, aber was ist mit dem Ergebnis für Level “Year” los? Angezeigt wird der Dezember 2001, der nun definitiv kein Jahr hinter dem Juni 2002 liegt. In diesem Fall wird nun der letzte Monat des Vorjahres angezeigt, also das letzte Member der darunterliegenden Ebene. Auch darauf sollte man sich nun nicht verlassen, beachten Sie das folgende Beispiel (und, wenn möglich, führen Sie das Statement auch mal auf Ihrem eigenen Adventure Works Cube aus)

Ziel der Abfrage ist den allseits beliebten “Sales Amount” der Monate 2001 bis Ende 2002 zu ermitteln. Beim Cubebrowsen stellt sich folgendes heraus:

Zwischen Januar 2001 und Juni 2001 sind keine Werte für “Sales Amount” vorhanden, eine ähnliche MDX-Abfrage wie folgende:

WITH
//LAG:
Gibt das Element zurück, das eine angegebene Anzahl von Positionen (hier 12) vor einem angegebenen Element entlang der Dimension des Elements liegt.
MEMBER [Measures].[Lag_Month] AS
([Measures].[Sales Amount],
[Date].[Calendar].CURRENTMEMBER.LAG(12))

//PARALLELPERIOD: Gibt ein Element aus einer früheren Periode in derselben relativen Position wie ein angegebenes Element zurück
MEMBER [Measures].[PP_LevelMonth] AS
([Measures].[Sales Amount],
PARALLELPERIOD([Date].[Calendar].[Month],12,
[Date].[Calendar].CURRENTMEMBER))

MEMBER [Measures].[PP_LevelYear] AS
([Measures].[Sales Amount],
PARALLELPERIOD([Date].[Calendar].[Calendar Year],1,
[Date].[Calendar].CURRENTMEMBER))

SELECT {
[Measures].[Sales Amount]
,[Measures].[Lag_Month]
,[Measures].[PP_LevelMonth]
,[Measures].[PP_LevelYear]
} ON 0,
{ //Level month on axis 1
[Date].[Calendar].[Month].&[2001]&[7]
    : [Date].[Calendar].[Month].&[2002]&[12]
} ON 1 FROM [Adventure Works]

ergibt:

Die “Sales Amount” Werte für das mit PARALLELPERIOD Level “Year” erstellte Member sind in Unordnung geraten. Schon ab Januar 2002 werden die rückliegenden Werte angezeigt, obwohl erst der Juli 2002 Werte für das aktuelle und das zurückliegende Jahr aufweist.

Die Werte der Member welche mit der MDX Funktion “Lag” oder mit dem in der Achse verwendeten Level, nämlich “Month” erstellt wurden sind korrekt.

Deshalb, “Schuster – bleib bei Deinem Leisten” und bei PARALLELPERIOD im abgefragten Level.

Hallo zusammen,

ich habe in einer Feature-Liste für das neue Excel 2010 etwas gelesen, was ich unbedingt mal ausprobieren musste: Das allseits gefürchtete/geliebte Writeback für OLAP-Cubes kommt zurück.

Meine Ergebnisse möchte ich Euch natürlich nicht vorenthalten. Ich habe mir in einem 2008er Cube (Adventure Works 2008) eine kleine, einfache Partition mit Planzahlen gebaut und habe dort das Writeback im Modus ROLAP aktiviert (die AS können das mit dem Zurückschreiben übrigens durchweg seit mind. Version 2000 und mit dem Excel Add-in for SQL Server Analysis Services konnte man das sogar schon mal benutzen).

Dann habe ich mich mit Excel 2010 auf diesen Cube verbunden und die “What-If-Analyis” aktiviert.

Folgende Settings habe ich genommen.

Dann hab ich mein Measure mit zwei Dimensionen in die Pivot-Tabelle gepackt…

… und den Wert für Europa erhöht.

Nach dem Neuberechnen der Pivot-Tabelle haben sich alle zugehörigen Werte erhöht.

Allerdings ist das ganze bis dahin nur eine Art Simulation. Erst wenn man es explizit sagt, werden die Änderungen in den Cube übernommen.

Dort landen sie dann in der vorher definierten Tabelle.

Nett, oder? Und die aus alten Zeiten bekannten Performanceprobleme beim Writeback auf größere Datenmengen sollen seit SQL Server 2008 auch der Vergangenheit angehören, da nicht mehr nur ROLAP, sondern auch MOLAP unterstützt wird. Das kann man aber auf einer Adventure Works schlecht überprüfen.