Der PerformancePoint Server ist tot – lang leben die PerformancePoint Services! Anfang dieses Jahres kündigte Microsoft die Zusammenführung vom PerformancePoint Server mit dem Microsoft SharePoint Server Enterprise an (zur Microsoft Pressemitteilung).

Mit SharePoint 2010 am Horizont und den Vorbereitungen zum Office 2010 Release, hat sich nun auch für PerformancePoint einiges getan. Bevor wir uns aber damit beschäftigen, was neu und aufregend an den PerformancePoint Services für SharePoint 2010 ist, werfen wir einen Blick darauf, was PerformancePoint ist und warum es für Sie wichtig ist.

Die Definition

Die PerformancePoint Services ermöglichen das Erstellen von aussagekräftigen Dashboards mit aggregierten Daten und Inhalten, die Ihnen eine vollständige Sicht auf Ihr Unternehmen und dessen Entwicklung in allen Ebenen geben.

Die Blitzvorstellung der PerformancePoint Services wäre daher, dass sie der einfachste Weg sind, um in SharePoint 2010 Business Intelligence Dashboards zu erstellen und bereitzustellen.

Die Idee des Dashboards (dt.: Armaturenbrett) kommt tatsächlich daher, wo Sie es vermuten würden. Es ist die Anzeige des Piloten Cockpits, das Armaturenbrett im Auto… im Wesentlichen also wie eine jede Aktivität kontrolliert und bewertet werden kann – sei es das Fahren eines Formel 1 Wagens oder das Führen eines erfolgreichen mittelständischen Unternehmens.

In einem jeden Unternehmen bedeutet das Kontrollieren des unternehmerischen Erfolgs der Zugang zu Daten. SharePoint 2010 bietet viele Technologien zur Datenauswertung, aber PerformancePoint lebt und atmet Daten. Wir ersetzen die Tankanzeige und den Tacho durch KPIs, Scorecards und Datenvisualisierung, und ermöglichen Ihnen auf diese Art den Einstieg in die Daten und die Beantwortung Ihrer Fragen.

Die Praxis

Nach diesem Vertriebs-Kauderwelsch soll es nun darum gehen, woraus die PerformancePoint Services gemacht sind und wie Sie diese für sich nutzen können. Die PerformancePoint Services sind ein Teil von SharePoint 2010 geworden und tauchen dort als Webpartseite auf, wo es versierte SharePoint Benutzer erwarten würden. Das Ganze kommt in zwei Teilen.

Die PerformancePoint Services beginnen als Autorenerlebnis. Der Dashboard Designer ist ein Werkzeug zum “bottom-up”-Modellieren: Key Performance Indicators (KPIs), Scorecards, grafische Analysen und Diagramme, Reports, Filter und Dashboards. Jedes dieser Elemente ist einzigartig für PerformancePoint Services und bringt Funktionalität mit, die zusammen mit der Serverkomponente die schwierigen Teile erledigt – wie Datenverbindung und Sicherheit.

Der Dashboard Designer funktioniert nach dem WYSIWYG-Prinzip. Die einzelnen Elemente werden genau so im Browser dargestellt, wie sie erstellt wurden. Das bringt uns zum zweiten Teil, dem Anwendererlebnis. Die PerformancePoint Services wurden unter dem Gedanken des gemeinsamen Benutzens und Austauschens entwickelt. Die erstellten Elemente werden in einem Dashboard zusammengepackt und auf einer SharePoint Seite angezeigt, die regelt, wer was sehen darf. Das bedeutet, Sie entwerfen und publizieren, was die Anwender benutzen… ohne IT, ohne komplizierte Workflows.

Link zum englischen Original Blogeintrag

Die November CTP des SLQ Server 2008 R2 steht seit einiger Zeit zum Download bereit und bietet zahlreiche neue Features. In diesem Artikel sollen die zahlreichen Neuerungen, die die Reporting Services erhalten haben, beschrieben werden. Wir beschränken uns auf die Features, die unmittelbar mit der Entwicklung der Berichte zu tun haben und lassen die Änderungen, die bezüglich der erweiterten Sharepoint Integration vorhanden sind, außen vor. Der Report Manager hat ein neues Äußeres bekommen und der Report Builder steht jetzt in Version 3.0 zur Verfügung, die auch benötigt wird, um mit den neuen Features arbeiten zu können:

Freigegebene Data Sets

Genau wie freigegebene Datenquellen, die in jedem größerem Projekt verwendet werden, um Änderungen an der Datenquelle an zentraler Stelle zu realisieren, können jetzt auch Data Sets freigegeben werden und in einem geeigneten Ordner auf dem Server zu speichern. Freigegebene Data Sets können von allen Berichten verwendet werden, so können Sie z.B. für einen Parameter, der in mehreren Berichten vorkommt, ein Data Set anpassen, dieses freigeben und es dann auch in den anderen Berichten verwenden.
Mit dem Report Builder können Sie alle freigegebenen Data Sets auf dem Server verwenden, mit dem BIDS hingegen können Sie nur die freigegebenen Data Sets ihres Projekts verwenden.

Report Part Gallery

Seit der November CTP ist es möglich Teile eines Berichts zu veröffentlichen. Diese Report Parts können dann in anderen Berichten verwendet werden. Im Gegensatz zu den freigegebenen Data Sets, können Report Parts in einem neuen Bericht weiter angepasst werden, ohne dass die Vorlage davon beeinflusst wird. Ein Report Part wird sozusagen ein normales Berichtselement im neuen Report. Falls das Original aktualisiert wurde, wird Ihnen dies mitgeteilt, damit Sie Ihre verwendeten Elemente aktualisieren können.
Beim Hinzufügen eines Report Parts werden automatisch die zugehörigen Data Sets und Datenquellen mit angelegt.
Report Parts können sowohl mit dem BIDS, als auch mit dem Report Builder erstellt werden. Sie können aber zurzeit nur mit dem Report Builder verwendet werden, indem Sie einfach per Drag&Drop aus der Report Part Gallery in den Bericht gezogen werden.

Folgende Elemente stehen zur Verfügung:

• Charts
• Tabellen, Matrizen und Listen
• Messgeräte
• Maps
• Bilder
• Rectangles

Neue Berichtselemente zur Datenvisualisierung

Die neuen Berichtselemente sind speziell konzipiert worden, um in Tabellen und Matrizen eingesetzt zu werden.

Sparklines und Data Bars

Sparklines und Data Bars sind einfache Charts, die nur die wesentlichen Elemente beinhalten und gänzlich auf Legenden, Achsen und Beschriftungen verzichten. Sie werden in erster Linie in Tabellen und Matrizen verwendet, um viel Information auf möglichst geringem Raum zu repräsentieren.
Typischerweise zeigen Data Bars nur einen Wert pro Zeile an, um den Vergleich der Werte untereinander zu vereinfachen:

Im Gegensatz zu den Sparklines, die normalerweise den zeitlichen Verlauf wiederspiegeln:

Bei den Sparklines wäre noch anzumerken, dass sie nur in Gruppen- und nicht in Detailzeilen verwendet werden können.

Indikatoren

Indikatoren werden verwendet, um den Status eines Wertes auf den ersten Blick erkennen zu können. Sie werden häufig in Tabellen und Matrizen eingesetzt. Ein Indikator ist ein vereinfachtes Messgerät und kann auch in eben dieses umgewandelt werden. Sie können Trends anhand von Pfeilen, Abstimmungen durch Sterne und Status durch Bilder wie z.B. der Ampel darstellen:

Erweiterungen der RDL Ausdruckssprache

Aggregationen von Aggregationen

In der aktuellen Version ist es endlich möglich Ausdrücke zu verwenden, die Aggregationen von Aggregationen bilden. Dadurch können Werte, wie z.B. der Durchschnitt der monatlichen Einnahmen gebildet werden.
=Avg(Sum(Fields!Reseller_Sales_Amount.Value, “Month”),”Year”)

OverallPageNumber und OverallTotalPages

Zeigt die Gesamtseitenzahl und die aktuelle Seitenzahl bezogen auf das gesamte Dokument. Im Gegensatz zu den schon bekannten Feldern PageNumber und TotalPages, die die Seitenzahlen abhängig von einer Sequenz angeben(die Seitenzahlen können durch ein PageBreak zurückgesetzt werden). Diese Eigenschaften können nur im Seitenkopf oder –fuß verwendet werden.

RenderFormat

Eine weitere neue, in meinen Augen sehr nützliche Eigenschaft, ist die Globale RenderFormat. Zum Einen kann der Name durch Globals!RenderFormat.Name ermittelt werden und zum Anderen, mit der Expression =Globals!RenderFormat.IsInteractive, ob es sich um ein interaktives Ausgabeformat handelt.

Durch die Möglichkeit das Ausgabeformat zu ermitteln ergeben sich ganz neue Gestaltungsmöglichkeiten. So können für ein Excel-Export z.B. Spalten einer Tabelle ein- oder ausgeblendet werden. Es können Abhängig vom Ausgabeformat komplett unterschiedliche Elemente angezeigt werden.
Bei nicht interaktiven Formaten können, durch den Drilldown versteckte Elemente, sichtbar gemacht werden.

PageName

Endlich können Excelsheets einer Excel-Datei mit Namen versehen werden. Dafür müssen Sie einfach den PageName angeben. Wenn Sie den PageName dynamisch vergeben und den Gruppennamen verwenden für den ein PageBreak existiert, bekommt jedes Excelsheet den Namen der Gruppe.

Mit der Eigenschaft PageBreak/Disabled kann z.B., abhängig vom RenderFormat, der Seitenumbruch deaktiviert werden.

Maps

Was man in früheren Versionen der Reporting Services von Drittanbietern kaufen musste, wurde in der aktuellen Version integriert. Dieses neue Berichtselement dient zur Visualisierung von Daten abhängig von ihrer geographischen Lage. Es stehen eine Vielzahl von Karten in der Kartengalerie zur Verfügung (zurzeit, wohl aus rechtlichen Gründen, nur Karten der USA). Falls Sie eine andere Karte benötigen, können auch ESRI-Shapefiles oder SQL Spatial Datentypen verwendet werden. Sie können hinter Ihre Karte noch eine Bing Karte legen, um die Darstellung noch etwas aufzuwerten. Folgende Kartenvisualisierungen stehen zur Verfügung:

• Basic Map (Basis Karte) – Es werden einfach nur Gebiete angezeigt. So können z.B. Ihre Verkaufsgebiete angezeigt werden.
• Color Analytical Map (Farbanalytische Karte) – Informationen werden durch Farbvariationen hervorgehoben. Z.B. werden die Verkaufszahlen pro Gebiet farblich gekennzeichnet.
• Bubble Map (Blasen Karte) – Informationen werden durch die Größe der Blasen angezeigt. Die Blasen liegen zentriert in den zugehörigen Gebieten.

Report Viewer

Der neue Report Viewer verwendet AJAX für die Seitennavigation und die Interaktivität. So wird beim Öffnen eines Drilldowns die aktuelle Scrollposition beibehalten.
Das Aussehen wurde angepasst und optimiert, um mehr Platz für den Bericht zu haben.

Wir konnten natürlich nur einen kurzen Überblick über die neuen Features der Reporting Services des SQL Server 2008 R2 geben und werden daher in den nächsten Blogeinträgen gezielt und detailliert einzelne der oben genannten Features beschreiben.

“Das beste MDX ist das, das man nicht schreibt” (über den Buschfunk: Mosha Pasumansky)

Aber wer kommt denn auf die Idee, dass dieses Zitat wortwörtlich zu nehmen ist?

Ich sah mich mit einer Kundendimension von 172.000 Kunden konfrontiert, die etwa 200 Vertretern zugeordnet waren. Ergebnis der Abfrage sollten die Kunden sein, die in einem gewissen Monat keine Umsätze, dafür aber im Rest des Jahres Umsätze erzielt hatten. Übersetzt in den AdventureWorks Cube, wobei der Vertreter hier durch die Dimension “Produkt” ersetzt wird, sah die Abfrage zuerst so aus:

WITH

MEMBER
UmsatzDezember03
AS

(

    [Measures].[Internet Sales Amount]

    ,STRTOMEMBER(‘[Date].[Calendar].[Month].&[2003]&[12]‘, CONSTRAINED)

)

SET Kunden AS

EXCEPT(EXISTS

(NONEMPTY([Customer].[Customer].[Customer].MEMBERS

        ,[Measures].[Internet Sales Amount])

        ,[Product].[Product Categories].[Category].&[1])

,NONEMPTY([Customer].[Customer].[Customer].MEMBERS

    ,UmsatzDezember03))

SELECT

{

    [Measures].[Internet Sales Amount]

} ON 0,

{

    Kunden

} ON 1 FROM [Adventure Works]

WHERE [Date].[Calendar].[Calendar Year].&[2003]

Um der besseren Lesbarkeit willen erstellte ich das berechnete MEMBER UmsatzDezember03 um das – zugegeben etwas komplizierte – Kundenset zu filtern.

Die ursprüngliche Abfrage (nicht das Beispiel hier) benötigte 45 Sekunden, was bei einer so kleinen Dimension von 172.000 Membern nicht akzeptabel war. Query-Tuning war also unumgänglich.

Nehmen wir uns des Beispiels oben an: Diese Abfrage – mit “MDX Studio” auf dem AdventureWorks Cube ausgeführt – berechnete 44.240 Zellen und benötigte 2,6 Sekunden für die Ausführung.

Mosha’s Befehl zur Sparsamkeit gehorchend, verzichtete ich auf das Hilfsmember und brachte den Ausdruck für UmsatzDezember03 direkt in das Kundenset.

WITH

SET Customers AS

EXCEPT(

    EXISTS(NONEMPTY([Customer].[Customer].[Customer].MEMBERS

                ,[Measures].[Internet Sales Amount])

        ,[Product].[Product Categories].[Category].&[1])

,NONEMPTY([Customer].[Customer].[Customer].MEMBERS

    ,(

    [Measures].[Internet Sales Amount]

    ,STRTOMEMBER(‘[Date].[Calendar].[Month].&[2003]&[12]‘, CONSTRAINED)

)))

SELECT

{

    [Measures].[Internet Sales Amount]

} ON 0,

{

    Customers

} ON 1 FROM [Adventure Works]

WHERE [Date].[Calendar].[Calendar Year].&[2003]

Dies führte zu einer Berechnung von nur 7.272 Zellen, benötigte aber immer noch 2,6 Sekunden für die Ausführung. Nachdem ich mir auch das CONSTRAINED Flag in der STRTOMEMBER Funktion erspart hatte, lag die Ausführungszeit bei 0,6 Sekunden.

Was war passiert? Offensichtlich führte das Einfügen des berechneten Members UmsatzDezember03 dazu, das die Berechnung für den Term NONEMPTY([Customer].[Customer].[Customer].MEMBERS,UmsatzDezember03) für jedes einzelne Member der Kundendimension ausgeführt wurde – d.h. je mehr Member in der Dimension enthalten sind, desto langsamer wird die Abfrage.

Darüber hinaus ist festzustellen, dass das CONSTRAINED Flag die Query ebenfalls verlangsamt – was uns zu dem Schluss führt, das Mosha’s Zitat tatsächlich wortwörtlich zu nehmen ist. Allein das Einsparen der Worte “MEMBER, AS, STRTOMEMBER und CONSTRAINED” führt in obigem Beispiel zu einer Beschleunigung der Abfrage von 500% und zu einer 86%-igen Reduktion der berechneten Zellen.

In den Analysis Services 2008 tritt dieses Problem nicht mehr auf. “Hilfsmember” können hier ohne Scham verwendet werden. Beide auf einem SQL Server 2008 ausgeführten Abfragen berechneten nur die nötigen 7.272 Zellen.

In den vorangegangenen beiden Teilen dieses Artikels (Teil I, Teil II) haben wir uns mit den Möglichkeiten des Range-Lookups beschäftigt, die ohne weitere Programmierung oder zusätzliche Komponenten mit reinen SSIS-Boardmitteln realisiert werden können. Nun wollen wir uns mit einer dritten Variante beschäftigen, um in unserem Beispielszenario die Kunden nach Ihrem Einkommen in die bereits bekannten Einkommensgruppen einzuteilen:

Lösung mittels Script Task – Wunderwaffe “Binary Search”

Wenn einen die mitgelieferten Standardkomponenten der Integration Services mal wieder nicht so richtig ans Ziel bringen, dann gibt es ja zum Glück immer noch den Script Task, den wir auch dazu nutzen können, um einen eigenen Lookup zu realisieren. Und genau das werden wir jetzt tun. Script Transformation in unseren Datenfluss gezogen und schon kann es mit der Definition der Eingabespalten beginnen. In unserem Fall reicht das YearlyIncome aus:

Danach müssen wir dann noch die zugehörige Ausgabespalte definieren, die dann später unsere Gruppen-ID aufnehmen soll:

Da ich die möglichen Gruppen direkt im Script-Task aus der Datenbank lesen möchte (es gibt auch andere, durchaus sauberere Wege), benötige ich auch noch einen Verbindungsmanager (der Einfachheit halber mit dem .NET Provider für SQL Server):

Nun kann ich mir noch die Programmiersprache auswählen (ich nehm mal C#) und endlich mit der eigentlichen Arbeit beginnen.

Als erstes benötige ich einen gefüllten Cache, der die möglichen Ausprägungen meiner Gehaltsgruppen aufnehmen kann. Diesen definiere ich als globale generische Liste einer eigens dafür gedachten Klasse Namens “NumericRangeItem”:

Im PreExecute übernehme ich dann den Freigegebenen Verbindungsmanager und fülle einmalig den besagten Cache:

Im FillCache() werden dann die oben gezeigten Zeilen aus der Datenbank gelesen und für jede Zeile ein neues NumericRangeItem in den Cache übernommen. Das NumericRangeItem ist dabei im wesentlichen ein Key-Value-Pair, wobei IncomeFrom als Key und die zugehörige IncomeGroupID als Value übernommen wird :

Besonderheit ist, dass es die Schnittstelle IComparable implementiert. Dazu muss die Klasse also eine Methode namens CompareTo enthalten, die aber auch ziemlich simpel ist.

Damit wird lediglich festgelegt, dass beim Vergleich zweier NumericRangeItems die Schlüssel (also die Einkommensgrenze) miteinander verglichen werden.

Ergebnis ist, dass ich nach dem PreExecute alle Einkommensgrenzen als generische Liste vergleichbarer Elemente im Speicher habe. Belohnt werde ich für diesen Aufwand dann damit, dass eine solche Liste eine Methode namens BinarySearch() zur Verfügung stellt, die wir dann für den eigentlichen Lookup nutzen können:

Für jede Zeile die ein Einkommen enthält wird die Funktion GetID aufgerufen, die einen numerischen Wert übergeben bekommt und das dazu passende NumericRangeItem zurückgibt. Der zu suchende Wert wird dabei selbst in einem NumericRangeItem verpackt, damit der Vergleich funktioniert. Und dann kann BinarySearch() die eigentliche Arbeit übernehmen. Dabei wird immer in die Mitte der Wertmenge geschaut und überprüft ob der dort vorhandene Wert größer oder kleiner als der Vergleichswert ist und dann mit der jeweils passenden Hälfte weitergearbeitet. Dadurch kann man mit BinarySerach auch in großen Datenmengen sehr schnell suchen (im schlimmsten Fall werden log₂(N) + 1 Iterationen benötigt => zum Durchsuchen von 1 Mio Datensätzen werden höchstens 20 Versuche gebraucht). Was das Ganze für unser Szenario aber erst nutzbar macht, ist, dass auch für Werte, die nicht gefunden werden können ein Ergebnis geliefert wird.

Mathematisch ist dieser negative Rückgabewert das Komplement des nächstgrößeren Index. Da wir über die Untergrenzen suchen (IncomeFrom) erhalten wir also mit if (iScore < 0) iScore = (short)(~iScore – 1); genau das Ergebnis, dass wir für unseren RangeLookup brauchen.

Das Ergebnis sieht dann wie zu hoffen war folgendermaßen aus:

Dieses Bild haben wir so ähnlich bereits im Teil I gesehen, nur dass das Ergebnis diesmal in nicht mal einem Sechstel der Zeit berechnet war. Bei größeren Datenmengen verstärkt sich dieser Effekt sogar noch weiter. In unserem kleinen Beispiel benötigt der Script Task noch fast die Hälfte für das PreExecute, in dem ja auch der Cache gefüllt werden muss. Wenn wir aber nicht mehr 18k sondern ein paar Millionen Datensätze durch die Pipeline schicken, fällt dieser Overhead nicht mehr ins Gewicht.

Mit der Wunderwaffe BinarySearch brauchen wir also in Zukunft keine Angst mehr vor Range-Lookups bei großen Datenmengen zu haben.

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.

In den von uns gebauten Integration Services Paketen ist der Lookup der wohl am meisten genutzte Task und im Grunde macht er seine Arbeit auch meistens gut und schnell. Spätestens wenn man während des ETLs jedoch fortlaufende Werte wie z.B. das Einkommen eines Kunden in Gruppen übersetzen will, stößt man mit dem Lookup an seine Grenzen. Diese Artikel-Reihe beschreibt zwei Workarounds, mit denen sich dieses Problem mit dem Lookup trotzdem bewältigen lässt und schlägt zum Schluss eine wesentlich performantere Lösung mittels Script Task vor.

Zur besseren Nachvollziehbarkeit bezieht sich das folgende Szenario auf die Microsoft Demo-Datenbank AdventureWorksDW2008, die unter http://msftdbprodsamples.codeplex.com/ heruntergeladen werden kann. Alle Screenshots basieren auf dem SQL Server 2008, die beschriebenen Vorgehensweisen lassen sich aber auch mit dem SQL Server 2005 realisieren.

Ausgangssituation

In den Quelldaten für die Kunden gibt es eine Spalte, die das jährliche Einkommen der jeweiligen Kunden enthält (wie ein Fahrradhändler an solche Informationen kommt ist nicht nur Ihnen ein Rätsel). Um nach dieser Information später im Cube bzw. den Berichten besser auswerten zu können, sollen mehrere Einkommensgruppen entstehen, in die die einzelnen Kunden einsortiert werden müssen. Die dafür nötige Zuordnungstabelle hat folgenden Aufbau:

Um den “normalen” ETL-Aufwand für dieses Beispiel möglichst gering zu halten, nutzen wir als Quelldaten für die Kunden bereits die Kundendimension des DWHs, in der alle relevanten Kundeninformationen bereits zusammen vorliegen. Diese sollen nun um die IncomeGroupID aus unserer Zuordnungstabelle ergänzt werden.

Lösung mittels Lookup – Ohne Caching

Die hohe Geschwindigkeit des Lookups resultiert vor allem daraus, dass alle dem Lookup zu Grunde liegenden Daten bereits vor der eigentlichen Ausführung in den Hauptspeicher geladen werden und das Nachschlagen der Einzelwerte dann direkt im RAM erfolgen kann. In diesem Modus kann man aber leider nur exakt gleiche Werte wiederfinden. Eine Zuordnung zu einem Bereich, wie er in diesem Fall nötig ist, ist mit eingeschaltetem Caching nicht möglich. Da aber der Weg zum Ziel auch bei ausgeschaltetem Caching alles andere als intuitiv ist, wollen wir uns das doch mal genauer anschauen.

Die Quelldaten werden 1:1 aus der Tabelle DimCustomer gelesen, mit dem Lookup um die zusätzliche Spalte ergänzt und ins Ziel (hier ins Nirvana) geschrieben. Der Lookup ist dabei folgendermaßen konfiguriert:

Der Cache muss also wie gesagt deaktiviert werden.

Als Quelle werden die untere und obere Grenze und die daraus resultierende GruppenID benötigt.

Die Verknüpfung wird erst einmal so eingerichtet, als ob ein Standard-Lookup über eine Spalte eingerichtet wird, damit wir die Ergebnisspalte auswählen können.

Erst unter dem Punkt “Erweitert” kommt dann die eigentliche Besonderheit ins Spiel. Durch das deaktivieren des Caches, können wir eine benutzerdefinierte Abfrage angeben, die für JEDE! Zeile des Datenflusses ausgeführt wird. Diese kann dann wie gezeigt parametrisiert werden. Resultat ist also, dass nicht mehr nach einem speziellen Wert gesucht wird, sondern der Ausdruck “YearlyIncome BETWEEN IncFrom AND IncTo” ausgewertet und die gewünschte GruppenID zurückgegeben wird.

Um das Ergebnis begutachten zu können, hängen wir hinter den Lookup einen Datenviewer mit den wichtigsten Spalten und lassen das ganze laufen.

Wie man sieht erhalten wir das gewünschte Ergebnis. Allerdings deutet sich bereits bei dieser sehr kleinen Datenmenge an, dass die Geschwindigkeit nicht gerade Rekordverdächtig ist. Für kleine bis mittlere Datenmengen ist diese Lösung aber durchaus brauchbar.

Demnächst werden wir uns dann noch mit zwei weiteren Alternativen beschäftigen, die je nach Szenario eine bessere Performance erreichen können.

Nein, Windows Azure ist nicht das neue Desktop-Betriebssystem von Microsoft – und möglicherweise wird Windows Azure auch nicht alle Ihre Probleme lösen, aber ein Blick auf Windows Azure kann durchaus lohnenswert und interessant sein.
Dazu aber eine kleine Einführung in die Thematik Cloud Computing.

Cloud Computing

Cloud Computing ist momentan das Schlagwort der IT-Branche. Nutzen Sie etwa noch kein Cloud Computing?
(Der Autor kann sich an dieser Stelle ein Lächeln nicht verkneifen)
Aber jetzt im Ernst:
Cloud Computing bedeutet : weg vom zentralen Server, der seiner Umwelt Dienste bereitstellt, hin zu einer ganzen Ansammlung von Servern (Wolke), die die Aufgaben des einzelnen Servers übernehmen. An dieser Stelle dürfte dem geneigten Leser  bei der zu erwartenden Kostenexplosion – schon das erste Mal der Hut hoch gehen.
Aber nein, ganz so schlimm ist es nicht!
Sie können die Bestellung über 50 neue Server also wieder stornieren. Ganz im Gegenteil, Cloud Computing unterstützt Sie aktiv bei der Reduzierung Ihrer IT-Kosten. Anstatt eigene IT-Infrastruktur aufzubauen, mieten Sie doch einfach Rechenkapazität bei einem Cloud-Anbieter. Die Vorteile liegen auf der Hand. Unmengen an Rechenkapazität steht Ihnen zur Verfügung – und zwar dann, wann Sie sie benötigen. Und sollten Sie mal keine Rechenkapazität benötigen, entstehen auch keine Kosten.

Microsoft Windows Azure

Windows Azure ist so ein Cloud Computing System von Microsoft. Es befindet sich momentan in der Entwicklung, soll aber voraussichtlich noch 2009 fertiggestellt werden. Es richtet sich vorrangig an Softwareentwickler, die während der Entwicklung „on-demand“ Speicherplatz, Rechenkapazität, Datenbanken, etc. benötigen. Unterstützt werden diverse Protokolle wie zum Beispiel SOAP, REST und XML sowie Programmiersprachen von Microsoft (.NET), aber auch non-Microsoft Programmiersprachen wie PHP.
Preise für die Cloud Services hat Microsoft auch schon bekannt gegeben: Demnach soll eine Stunde Rechenzeit in der Cloud 12 US-Cent kosten, Speicherplatz in der Cloud schlägt mit 15 US-Cent pro GByte pro Monat zubuche.

Weiterführende Informationen zu Windows Azure finden Sie unter www.microsoft.com/azure.