SQL Server 2008 R2 SSRS wartet mit einer interessanten Neuerung auf, nämlich der Suche in Datasets. Damit gibt es Möglichkeiten zwei Datasets in einem Datenbereich zu verknüpfen.

Grundsätzlich gilt: Ein Datenbereich in einem Report kann nur an ein Dataset gebunden sein. Es ist also sinnvoll, alle benötigten Felder bereits vom Datenbankserver in ein Dataset vereinigen zu lassen. Es sind aber auch Situationen denkbar, in denen es nicht möglich ist, die benötigten Felder in ein Dataset zu integrieren (z.B. bei der Nutzung von bereits bestehenden Shared Datasets oder bei verschiedenen Quellen). Die Möglichkeiten in einem Datenbereich auf Inhalte eines zweiten Datasets zuzugreifen waren bisher wenig komfortabel. Es gab dazu nur die Funktionen Last, First und die Aggregatfunktionen, die auf das gesamte Ziel-Dataset anzuwenden sind. Ein wahlfreier Zugriff auf eine beliebige Zeile in dem Ziel-Dataset, anhand eines Suchwertes, wurde nicht von SSRS unterstützt. Um das zu erreichen, war es nötig, das komplette Dataset in einem Hashtable zu cachen und dann mittels einer Custom-Code-Funktion den gesuchten Wert zu ermitteln.

Mit der Veröffentlichung von SQL Server 2008 R2 SSRS gibt es dafür jetzt die Lookup-Funktionen. Diese gibt es gleich in drei Versionen: Lookup, LookupSet und MultiLookup.

Die Lookup-Funktion wertet einen Wert (Ausdruck) pro Zeile aus und sucht durch Vergleich mit diesem Wert eine passende Zeile im Ziel-Dataset. LookupSet wertet ebenfalls einen Wert pro Zeile im aktuellen Dataset aus, liefert aber alle passenden Zeilen aus dem Ziel-Dataset. MultiLookup wertet mehrere Werte pro Zeile des aktuellen Datasets aus und liefert alle passenden Zeile aus dem Ziel-Dataset. Konkret wird für jede passende Zeile ein Wert zurückgegeben, der das Ergebnis der Auswertung eines Ausdrucks über die Felder der Zeile ist. Im einfachsten Fall wird der Wert eines der Felder zurückgegeben.

Diese drei Lookup-Funktionen sollen hier demonstriert werden.

Lookup

				

Ein Beispiel für den Einsatz von Lookup:

Der Umsatz pro Mitarbeiter aus Dataset2 soll in der Tabelle  angezeigt werden, die mit Dataset1 verknüpft ist.

Dazu wird ein neues Feld mit einem Ausdruck erstellt. Der hier verwendete Ausdruck lautet: =Lookup(Fields!MitarbeiterID.Value, Fields!PersonalID.Value, Fields!Umsatz.Value, “DataSet2″)

• Der erste Parameter, Fields!MitarbeiterID.Value, ist ein Feld der Tabelle (Datenbereichs), die an Dataset1 gebunden ist. Es ist aber auch möglich einen Ausdruck zu verwenden, um z.B. mehrere Felder zu konkatenieren, wenn der Vergleich mit den Zeilen im Ziel-Dataset mehrere Spalten berücksichtigen soll.
• Der zweite Parameter , Fields!PersonalID.Value , ist ein Feld aus dem Dataset2, in dem nach einer passenden Zeile gesucht werden soll. Es kann auch ein Ausdruck verwendet werden. Wie bei einem Join wird verglichen ob dieser Wert mit dem Suchwert (der erste Parameter) übereinstimmt.
• Der dritte Parameter, Fields!Umsatz.Value , gibt den Rückgabewert der Funktion an. Auch hier kann anstelle eines Feldes ein Ausdruck angegeben werden. Bei Lookup wird der Rückgabewert aus der ersten Zeile ermittelt, bei der die Suche erfolgreich war. Gibt es keine Zeile, die die Bedingung erfüllt, liefert der Lookup-Aufruf Nothing zurück.
• Der vierte Parameter benennt das Ziel-Dataset (hier Dataset2), in dem ein passender Wert gesucht wird.

 
Die mit Dataset1 verbundene Tabelle enthält jetzt die gewünschten Werte aus DataSet2.

LookupSet   Bei der Lookup-Funktion wird wie bereits beschrieben nur die erste Zeile berücksichtigt, auf die der Suchwert passt. LookupSet wertet immer alle Zeilen des Ziel-Datasets aus und liefert für jede passende Zeile einen Wert zurück. LookupSet gibt diese Werte in Form eines Array zurück. Dieses Array kann nicht direkt als Ausgabe eines Textfeldes verwendet werden. Es muss also eine Bearbeitung z.B. mit der VB-Funktion Join erfolgen wodurch aus dem Array ein anzeigefähiger Wert wird. Das Array kann auch als Argument für eine Custom-Code-Funktion verwendet werden, um einen Anzeigewert zu bestimmen.

Beispiel für die Verwendung von LookupSet

=Join(LookupSet(Fields!ProductCategoryID.Value, Fields!ProductCategoryID.Value, Fields!Name.Value, “DataSet2″),”, “)

Zu Jeder Zeile in DataSet1 werden alle passenden Zeilen aus DataSet2 gesucht. Join wandelt das Ergebnis-Array in einen String um, der anschließend im Feld Subcategory zu sehen ist.

Zu Jeder ProductCategory sind nun alle Subcategories in einem Feld zu sehen.

MultiLookup

MulitLookup nimmt keinen einzelnen Wert sondern ein Array von Suchwerten pro Zeile. Ein Aufruf von MultiLookup wird dann so durchgeführt, dass für jedes Element des Suchwerte-Arrays ein gesonderter Lookup-Aufruf durchgeführt wird. Die Ergebnisse der einzelnen Lookup's werden dann dem Ergebnis-Array zugefügt.

Ein Mehrwertiger Parameter kann direkt als Suchwert verwendet werden. Ein String, der eine Liste von Werten enthält, muss erst in ein Array umgewandelt werden. Dazu eignet sich die VB-Funktion Split.

Beispiel: Ein Mehrwertiger Parameter enthält mehrere Monate, die als Auflistung von Zahlwerten angegeben sind. Mithilfe eines Aufrufes der Funktion MultiLookup werden die Zahlwerte in den
jeweiligen Monatsnamen umgewandelt. Dabei wird das Dataset Monatsnamen als Ziel-Dataset für die Suche verwendet.

 
Anzeige der Parameterwerte in einem Textfeld mit dem Ausdruck =Join(Parameters!Monate.Value,”, “)




Anzeige der Werte nach Änderung des Ausdrucks auf =Join(MultiLookup(Parameters!Monate.Value, Fields!MonatNr.Value,Fields!MonatName.Value,”Monatsnamen”),”, “)

Ich hatte vor kurzem eine interessante Begegnung mit einem SSAS Cube (2008), über die ich an dieser Stelle gerne berichten möchte.

Dabei ging es um einen Cube, bei dem für ein bestimmtes Measure die Leerzeilenunterdrückung nicht so richtig funktionierte. Wenn man dieses Measure, das eigentlich eher selten gefüllt war, abfragte, dann sah das in etwa so aus:

Wie in diesem aus der Adventure Works stammenden Beispiel (wir sehen hier den Reseller Sales Discount Amount nach Reseller Telefonnummer) wurden die leeren Zellen mit einer 0 angezeigt, was ja bekanntlich nicht leer ist und somit auch nicht unterdrückt werden kann. Bei kurzem Blick in die Quelle fand ich in etwa folgendes Bild:

War also alles korrekt, was die Analysis Services so angezeigt haben. Der verwunderliche Teil der Geschichte beginnt aber auch erst jetzt.

Da es für Measures, bei denen mich die 0en gar nicht interessieren, nur bedingt Sinn macht, diese in der Datenbank abzulegen, habe ich also die Definition der Tabelle so geändert, dass die betroffene Spalte ab sofort NULL-Werte erlaubt. Danach wurden dann noch mit einem kleinen Statement wie unten die 0en eliminiert.

WHERE DiscountAmount = 0

Wie fast zu erwarten ist, reicht das aber noch nicht aus, denn bis die Zahlen dann wirklich im Cube landen, haben wir ja noch ein paar Abstraktionsebenen vor uns. In diesem Fall war die nächste zu überwindende Hürde die View, die der Cube für den Datenzugriff benutzt:

Dort war die Spalte nämlich weiterhin als NOT NULL definiert (im Bild ganz unten). Also erst mal rasch die Metadaten der View aktualisiert:

Prompt stimmt die View mit der Realität überein. Aber selbstverständlich reicht auch das noch nicht, damit die 0en aus meinem Cube verschwinden. Es gibt da ja auch noch die Data Source View in den Analysis Services, die Metadaten speichert und die ich deswegen vorsorglich aktualisierte. Das Visual Studio meldete mir aber leider nach dem Klick auf den Refresh-Knopf, dass keine Änderungen gefunden wurden. Und wenn man sich dann die Eigenschaften der Spalte in der DSV anschaut:

Dann gibt es eine Eigenschaft AllowNull, die leider immer noch auf False stand. Und

natürlich sind die 0en nach erneuter Aufbereitung auch immer noch im Cube.

Da ich bei meiner View als Datenquelle bleiben wollte habe ich also kurzfristig „Tabelle ersetzen durch neue benannte Abfrage“ gewählt und danach dann wieder die ursprüngliche View über „Tabelle ersetzen durch andere Tabelle“ übernommen. Und endlich: AllowNull steht auf true. Das Ganze also schnell auf dem Server bereitgestellt, aufbereitet und … NICHTS! Immer noch grinsen mich die 0en förmlich an.

Hm, ist vielleicht beim Measure selbst noch was falsch eingestellt?

Eigentlich sieht alles gut aus. NullProcessing steht auf Automatic, was der Default ist. Ich persönlich musste das zwar bisher auch noch nie ändern, aber irgendwann ist ja immer das erste Mal:

Nach dem Ändern des NullProcessing auf Preserve verwunderte dann beim Bereitstellen, dass die automatische Aufbereitung keinen Anlass sah, die betroffene Measure Group neu mit Daten zu befüllen. Wen wundert’s dann noch, dass die 0en auch immer noch da waren.

Die nun folgende manuelle Aufbereitung war wohl die leichteste Übung, und endlich:

Ich bin die 0en los!

Wer hätte gedacht dass man heutzutage noch über die Vermächtnisse einer 20 Jahre alten Software stolpern kann – und das in den Integration Services!

Aber beginnen wir von vorne: immer wenn die Bearbeitung mehrere gleichartiger Dateien mittels SSIS gefragt ist, kommt früher oder später der ForEach-Loop-Container zum Einsatz. Damit lässt sich z.B. recht komfortabel über Dateien mit einer bestimmten Namensstruktur innerhalb eines Ordners iterieren. So könnte ein mögliches Importszenario vorsehen, dass mehrere Exceldateien in einem Ordner ausgelesen werden sollen – den ForEach-Loop-Container könnte man dann wie folgt konfigurieren:

Soweit so gut, der Rest des Paketes könnte dann wie folgt aussehen. Innerhalb des Loops wird eine Variable mit dem aktuellen Dateipfad ausgelesen um alle gefundenen Dateien nach Beendigung des Containers in einer MessageBox anzuzeigen:

Ich habe bisher den Inhalt des betroffenen Ordners unterschlagen, hier ist er:

Und siehe da: unser Paket findet zwei Exceldateien – wunderbar! Oder doch nicht? Störend an dieser Stelle könnte die Excel-Datei sein, die im neuen 2007er Format gespeichert wurde. Diese hat die Endung .xlsx! Eigentlich ganz praktisch, denn die beiden Exceltypen unterscheiden sich grundlegend und erfordern ggf. eine differenzierte Behandlung innerhalb der SSIS. Weitere prüfende Blicke in die Konfiguration des ForEach-Loops lassen letztlich nur einen Schluss zu: egal wie man es dreht und wendet, der Ausdruck im “Files”-Feld des Editors lässt sich nicht dahingehend ändern dass er nur .xls Dateien erwischt (was man eigentlich erwarten würde) – der Ausdruck verhält sich also wie *.xls*!

An dieser Stelle wirken die Books Online einmal mehr erhellend und decken auf, dass es sich quasi um erwünschtes Verhalten handelt – der Ausdruck verhält sich nämlich wie beim dir-Befehl in Windows (siehe: http://msdn.microsoft.com/en-us/library/ms187670.aspx). Dieser wiederum ist aus Kompatibilitätsgründen so gestrickt, dass er nur drei Zeichen der Dateinamenerweiterung betrachtet – die gute alte 8.3-Dateinamenbeschränkung aus MS-DOS Zeiten schlägt hier also wieder einmal zu!

Eine Differenzierung nach Dateierweiterung muss also später geschehen, die könnte dann z.B. so aussehen: innerhalb des ForEach-Loop-Containers fügen wir eine Rangfolgeeinschränkung (zu deutsch: einen Pfeil) ein und binden daran eine SSIS-Expression. Diese prüft, ob die letzten drei Buchstaben des ausgelesenen Dateinamens auch tatsächlich x, l und s sind:

So modifiziert, verrichtet das Paket ordnungsgemäß seinen Dienst und findet tatsächlich nur eine Excel-Datei:

Da kann man sich natürlich schon fragen ob es sich um ein Bug oder ein Feature handelt, aber genau wegen diesen Ecken und Kanten lieben wir die Integration Services so sehr – man entdeckt eben immer wieder etwas Neues!

Divide et impera – das von Machiavelli vor gut 500 Jahren geprägte Prinzip des Teilen und Herrschens ist heutzutage eine allseits beliebte Technik zur Problemlösung. Auch in den Integration Services finden sich eine Menge Möglichkeiten, um große Aufgaben in kleinere, übersichtliche Schritte zu unterteilen. Beispiele sind unterschiedliche Datenflüsse für unterschiedliche Aufgaben, die Strukturierung mittels Sequenzcontainern und nicht zuletzt die Möglichkeit, aus einem SSIS Paket andere SSIS Pakete aufzurufen.

Doch mit steigender Komplexität der Pakete (Verbindungsmanager, Paketvariablen) und ausgiebiger Nutzung der Paketkonfiguration gelangt man mit den SSIS-Bordmitteln einmal mehr an die Grenzen des Wartbaren. Die Integration Services bieten von Haus aus die Möglichkeit, andere SSIS Pakete aufzurufen, doch die Übergabe von Variablen und Verbindungsmanagern an das “Kindpaket” gestaltet sich sehr aufwändig (viel Expression-Hacking). Die Möglichkeit, Rückgabewerte vom Kindpaket zu empfangen fehlt gar gänzlich.

In diese Nische stürzt sich ein Codeplex-Projekt, welches ich im Folgenden kurz vorstellen möchte: der “Enhanced SSIS Execute Package Task”. Unter http://ssisexec.codeplex.com/ ist das noch recht unbekannte Projekt zu finden (derzeit für SSIS 2005 und SSIS 2008 R2 – siehe “Downloads”). Hilfreich bei der Installation (es wird eine dll und ein schmales readme geliefert) ist folgender Eintrag in den Books Online: http://msdn.microsoft.com/en-us/library/ms403356.aspx Hat man die Komponente erfolgreich installiert lässt sie sich über einen Rechtsklick in die Toolbox -> Choose Items in die Liste der Kontrollfluss Tasks aufnehmen.

Die Komponente bietet eine grafische Oberfläche zum Mappen der Variablen bzw. Verbindungsmanagern zwischen den Paketen. Diese können entweder im Dateisystem oder in der MSDB serverseitig gespeichert sein. Somit lassen sich recht schnell und komfortabel Konfigurationen zwischen Paketen übergeben. Wie das aussehen könnte sieht man in folgendem screenshot: Hier wird von einem Steuerpaket, welches den Pfad zu einer Exceldatei via Paketkonfiguration erhält, ein Kindpaket aufgerufen wobei der Verbindungsmanager zwischen den Paketen übergeben wird.

Hinweis: Das Projekt hat derzeit noch den Alpha-Status – vom Einsatz in einer Produktivumgebung rate ich also dringend ab! Laut Aussage des Authors scheint es aber stabil zu laufen und dieser freut sich mit Sicherheit über ausgiebiges feedback in Form von Kommentaren oder Anmerkungen. In diesem Sinne: happy testing!

Wer kennt sie nicht: die Problematik der führenden Nullen in Excel. Um beispielsweise Postleitzahlen mit führenden Nullen korrekt darzustellen, bedarf es in aller Regel Einiges an Formatierungsaufwand, da Excel Zahlen gerne als numerischen Wert interpretiert. Befüllt man nun mit den SSIS ein Excel-Ziel, gehen führende Nullen beim ersten Öffnen der Excel-Mappe verloren, ganz gleich ob man z.B. eine Spalte PLZ als Text oder Zahl durchleitet.

Abhilfe schafft hier ein kleiner Trick, der in den screenshots unten zu sehen ist. Mittels des Tasks “Abgeleitete Spalte” erstellt man eine neue Textspalte, deren Inhalt die Spalte mit den führenden Nullen eingebettet in Hochkommata und ein vorangestelltes Gleichheitszeichen ist (Escape-Sequenz beachten!).

Als Resultat interpretiert Excel fortan den Wert in der Spalte als Formel und die führende Null wird korrekt dargestellt.

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.