Vorhandenes Kartenmaterial in einem Report nutzen

Dieser Blogbeitrag soll als Schritt für Schritt Anleitung zum Nutzen von zur Verfügung stehenden Karten in Verbindung mit Bing Maps in SSRS dienen. Dabei werden die frei zur Verfügung stehenden Programme Shape2SQL und QGIS genutzt. Es soll einerseits die Möglichkeit dargestellt werden, wie die Karte direkt als Shapefile genutzt werden kann und andererseits wie man selbst ein Shapefile erzeugt, in dem man die Karte oder Teile davon nachzeichnet. Die Shapefiles werden dann in den SQL Server geladen und von dort als Quelle in SSRS genutzt.

Schritt 1 Vorbereitungen

Zuerst sollten die beiden Programme Shape2SQL (Innerhalb der SQLSpatialTools: http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools) und QGIS (http://www.qgis.org/de/site/) heruntergeladen und installiert werden (nur QGIS).

Um folgendem Fehler bei Shape2SQL vorzubeugen,

kann es von Vorteil sein, in den Ordner von Shape2SQL ebenfalls eine oder mehrere DLLs zu laden. Es handelt sich hierbei um:

  • SqlServerSpatial.dll
  • SqlServerSpatial110.dll (ab SQL Server Version 2012)
  • SqlServerSpatial120.dll (ab SQL Server Version 2014)

Diese können von C:\Windows\system32 kopiert werden. Falls nur eine der unteren Beiden zur Verfügung steht, sollte diese in „SqlServerSpatial.dll“ umbenannt werden, damit Shape2SQL diese nutzen kann. Das Programm sollte danach neugestartet werden.

Des Weiteren sollte im SQL Server eine Datenbank zur Verfügung stehen, wo die Shapefiles hineingeladen werden können.

Schritt 2 Kartenmaterial in QGIS bearbeiten

Nachdem alle Vorbereitungen getroffen sind, muss zunächst die Karte in QGIS geladen werden. Dies kann über den Button „Rasterlayer hinzufügen“ (1) oder über den intergierten Browser (2) geschehen. Bei der Koordinatenbezugssystem-Auswahl sollte das WGS 84 (häufig genutztes Koordinatensystem u.a. bei Bing) ausgewählt werden, was zur Verbindung zu anderen Karten im gleichen Bezugssystem dient. Ist die Karte geladen erscheint sie als neue Layer (3) und man sieht sie im Bearbeitungsfeld.    

Damit man die Karte zusätzlich zu einer Bing Map verwenden kann, muss diese georeferenziert werden. Dazu wählt man im Menü Raster -> Georeferenzierung -> Georeferenzierung…
Hier kann die zur Verfügung stehende Karte auch als Raster mit dem Koordinatensystem WGS 84 genutzt werden (1). Zunächst müssen nun Koordinatenpunkte hinzugefügt werden(2). Eignen tun sich dafür vier Punkte in der Karte in dieser Reihenfolge:

  • Links unten
  • Rechts oben
  • Links oben
  • Rechts unten

Diese Punkte dienen dazu, um die Karte in dem ausgewählten Koordinatensystem zu verankern. Damit man diese jedoch eingeben kann muss man zu den ausgewählten Punkten die entsprechenden Koordinaten haben. Eine Möglichkeit hierfür ist z.B. Google Maps, das für einen Punkt genau die Werte anzeigt die wir brauchen, dies jedoch umgedreht. Also steh bei Google Maps „50,718724/6,040220“ müssen diese Zahlen beim Eingeben (3) unbedingt vertauscht werden, sodass X=6,040220 und Y=50,718724 ist. Die eingegebenen Passpunkte erscheinen unterhalb der Karte (4). Nachdem mindestens vier Punkte eingetragen wurden, kann die Georeferenzierung gestartet werden (5).

Hierbei wird eine Meldung uns zu den Transformationseinstellungen führen. Als Transformationstyp sollte Linear ausgewählt sein und das Ausgaberaster ist als Ausgabedatei zu bestimmen. Dabei sollte der gesamte Pfad angegeben werden, da ansonsten die Datei nur temporär gespeichert wird. Einfacher halber sollte auch das Häkchen bei „Wenn fertig in QGIS laden“ gesetzt werden, sodass die neue, georeferenzierte Karte als Layer hinzugefügt wird. Nach erfolgreicher Ausführung kann die Georeferenzierung geschlossen werden.

Die ursprüngliche Karte kann als Layer nun gelöscht werden. Um die neue Karte zu sehen, ruft man das Kontextmenü des Layer auf und nutzt die Option „Auf den Layer zoomen“. Unter Umständen kann die Karte nun ein wenig verzerrt dargestellt werden, was jedoch kein Problem darstellen sollte. Nun trennen sich die beiden Möglichkeiten, ob die vorhandene Karte direkt als Shapefile erstellt wird oder ob man selbst die Karte bzw. Teile der Karte selbst nachzeichnet.

Möglichkeit 1 – Karte in ein Shapefile konvertieren

Um die vorhandene, georeferenzierte Karte in ein Shapefile zu konvertieren, nutzt man das Menü Raster -> Konvertierung -> Vekotrisieren (Raster nach Vektor)…
Im folgenden Menü muss die Eingabedatei (die georeferenzierte Karte) und die Ausgabedatei festgelegt werden. Bei der Ausgabedatei wählt man als Dateityp ESRI-Shapedateien. Der Feldname sollte aktiviert werden, da es ansonsten passieren kann, dass keine Objekte(Shapes) von Shape2SQL erkannt werden können. Danach kann die Konvertierung abgeschlossen werden und man erhält eine neue Layer aus dem Shapefile. Das Projekt kann nun gespeichert und QGIS geschlossen werden. Unter Schritt 3 wird der weitere Weg erläutert.    

Möglichkeit 2 – Karte nachzeichnen

Um die Karte oder nur Teile davon eigenständig mit Vektorgrafiken nachzuzeichnen, benötigt man zunächst eine neue Layer. Hierzu wählt man das Menü Layer -> Layer erstellen -> Shapedatei-Layer erstellen … (alternativ auch Strg+Umschalt+N) aus. Um ein Gebiet einer Karte nachzuzeichnen benötigt man ein Polygon (1). Das Koordinatensystem sollte automatisch dem zuvor gewählten WGS 84 entsprechen (2). Nun kann man für die zu zeichnenden Vektorgrafiken Attribute definieren (3). Der Ortsname oder ein Gebäudename würde sich hier z.B. anbieten. Wenn dieser mit eingetragen ist, kann man später im Report diese Attribute auch wieder aufrufen. Die erstellten Attribute können dann nochmal bearbeitet oder gelöscht werden (4). Das automatisch erstellte Attribut „id“ kann gelöscht werden, da bei der Übertragung in den SQL Server ebenfalls eine ID angelegt wird und somit die von QGIS angelegte ID zu Problemen führen kann.

Wenn nun die neu hinzugefügte Layer ausgewählt ist, kann diese in den Bearbeitungsmodus umgeschaltet werden (1). Nun können neue Objekte hinzugefügt werden (2), in dem versucht wird, z.B. ein Gebiet möglichst genau nachzuzeichnen (3). Dazu klickt man zunächst auf einen Punkt am Rand des Gebietes und fährt nun diesen Rand Stück für Stück ab und setzt bei jeder Biegung und Ecke einen neuen Punkt. Wenn die Vektorgrafik fertig ist, genügt ein Rechtsklick und nun kann man die Attributwerte dieses Polygons festlegen. Anschließend können weitere Polygone gezeichnet werden, die somit alle in der gleichen Layer liegen. Zum Schluss kann die Layer und somit das Shapefile abgespeichert werden (4). Damit ist die Zeichnung fertig und das Projekt kann gespeichert und geschlossen werden. Weiter geht es mit Schritt 3.

Schritt 3 Shapefiles in den SQL Server übertragen (Shape2SQL)

Wenn nun ein Shapefile zur Verfügung steht, kann dieses mit Hilfe von Shape2SQL in den SQL Server übertragen werden. Dazu wird dieses als Eingabe ausgewählt (1). Daraufhin kann man den Server und die Datenbank auswählen, wo die Daten hineingeschrieben werden sollen (2). Die Option „Create Spatial Index“ sollte unter Umständen deaktiviert werden, da dies ansonsten zu einem unerwarteten Abbruch des Uploads ohne Fehlermeldung führen kann (3). Es kann der Tabellenname angegeben werden, sowie die Attribute ausgewählt werden (4), die mit in die Tabelle übernommen werden sollten. Ist hier eine ID vorhanden sollte diese abgewählt werden, da es ansonsten zu einem Fehler kommt, da der SQL Server ebenfalls eine ID anlegt. Sind alle Einstellungen getroffen können die Daten in die Datenbank geladen werden.

Es kann nun im SQL Server Management Studio überprüft werden, ob die Daten korrekt übertragen wurden. Dazu gehört bei einer Abfrage auf die neue Tabelle (1), neben den normalen Ergebnissen auch der Tab „Spatial results“ (2). Dort ist eine Vorschau der Vektorgrafiken zu sehen (3). Zu beachten ist dabei, dass im Management Studio nur maximal 5000 Grafiken auf einmal angezeigt werden können und somit bei größeren Shapefiles mit vielen Vektorgrafiken nur ein Ausschnitt angezeigt wird. In der Grafik ebenfalls zu erkennen sind die Koordinaten, die mit den Eingaben in QGIS übereinstimmen sollten.

Schritt 4 Report erstellen

Zum Ende kann nun in Visual Studio ein Reporting Services Projekt mit einem Report erstellt werden. Als Datenquelle dient die Datenbank, mit den Geodaten aus dem Shapefile. Im Report Wizard kann direkt ein Dataset über eine Query angelegt werden. Hier wird unsere gerade erstellte Quelle genutzt um die Tabelle mit unseren Geodaten in ein Dataset zu verpacken. Wenn der Report, die Datenquelle und das Dataset angelegt sind, kann aus der Toolbox eine Map in den Report gezogen werden.
Als Quelle kann die Option „SQL Server spatial query“ und im darauffolgenden Fenster das eben erstellte Dataset ausgewählt werden. Im Folgenden, unten dargestellten Fenster sollten die Angaben für Spatial fiel (geom) und Layer type (Polygon) bereits automatisch korrekt gesetzt sein. Es sollte nun eine Vorschau der Karte zu sehen sein, welche ebenso wie die Darstellung im SQL Server Management Studio nur eingeschränkt alle Vektorgrafiken darstellt (1). Da sich die Geodaten im Normalfall nicht ändern sollten, können die Daten in den Report integriert werden (2). Dies sorgt für ein schnelleres Rendering der Grafiken, erhöht jedoch natürlich auch den Speicherplatzbedarf des RDL. Die letzte Option „Add a Bing Maps layer“ (3) zeigt nun, ob unsere Karte wirklich mit den richtigen Koordinaten verankert ist. Ist diese Option ausgewählt, sollte um die eigene Karte herum eine Bing Map erscheinen.

Da bei nachgezeichneten Karten die kanten nicht passgenau aufeinander liegen, kann man das Dataset ein wenig abwandeln. Anstatt alles wo wie es in der Tabelle steht zu selektieren, verändern wir die Abfrage mit der STBuffer() Funktion:

Select ID, ort, geom.STBuffer(0.002)
as geom from [Aachen Shapes test]

Die STBuffer() Funktion rundet die Polygone ein wenig ab. Dazu wird an den Kanten ein wenig Fläche hinzuaddiert. Bei der genauen Verwendung ist also Vorsicht geboten. Ist die Zahl zu groß gewählt, fangen die Shapes an sich zu überlappen. Bei einer zu niedrigen Zahl, sind die Lücken immernoch erkennbar.

Nun zu den letzten Einstellungen. Falls das Shapefile aus Möglichkeit 1, also direkt aus der Karte erstellt wurde, kann es schnell vorkommen, dass mehr als 1000 Vektorgrafiken darin enthalten sind. Falls dem so ist, können die Einstellung des Polygon Layers in dem Map Objekt (Erster Klick Map-Objekt auswählen, Zweiter Klick Map Layers anzeigen) geändert werden. Dabei ist dies nur die Option „DesignerMaxRowCount“ (1). Standardmäßig ist hier eine 1000 eingetragen. Diese sollte auf das entsprechende Niveau des Shapefiles angehoben werden. Damit wird auch im Design-Fenster die komplette Vorschau aller Vektorgrafiken angezeigt. Eventuell muss auch die Transparenz des Polygon Layers geändert werden. Für komplett sichtbar sollte eine 0 eingetragen werden.

Falls mit nachgezeichneten Vektorgrafiken gearbeitet wurde, können die in QGIS eingetragenen Attribute nun z.B. als Label oder Tooltip verwendet werden. Dazu ist in den Map Layers das Kontextmenü des PolygonLayers zu öffnen und die Option Polygon Properties auszuwählen. Im Tab „General“ (1) können nun die Attribute wie z.B der Ortsname als Label (2) ausgewählt werden.

Nun ist alles eingestellt und man kann sich die berechnete Vorschau angucken oder den Report auf einen Report Server hochladen und dort betrachten. Zu beachten ist, dass die Berechnung einer nachgezeichneten Karte wesentlich schneller verlaufen kann, da diese normalerweise nur einen Bruchteil an Vektorgrafiken hat. Das Ergebnis sollte in etwa so aussehen:

Eine Besonderheit ist noch zu erwähnen. Wenn man einen Report hochlädt, der die Geodaten integriert, kann es passieren, dass man folgende Fehlermeldung erhält: „System.Web.HttpException: Maximum request length exceeded“. Dies ist dann darauf zurückzuführen, dass die maximale Größe eines Reports standardmäßig bei 4096kb liegt. Es gibt die Möglichkeit dies zu ändern, in dem man die web.config des Reportservers verändert. Normalerweise ist diese im Verzeichnis: C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer.

Dort kann man die Linie:

<httpRuntime executionTimeout=“9000?/>

erweitern, sodass folgendes steht:

<httpRuntime executionTimeout=“9000? maxRequestLength = „16384“ />

Die maxRequestLength wird dabei in Kilobytes festgehalten.

Fazit

Das Erstellen einer eigenen georeferenzierten Karte birgt verschiedene Schwierigkeiten. Zum einen ist die Georeferenzierung nicht 100% passgenau. Zum anderen bieten die beiden Möglichkeiten (Karte vektorisieren vs. Karte nachzeichnen) unterschiedliche Nachteile. Wenn die Karte automatisch vektorisiert wird, entstehen zu viele Vektoren, die SSRS nicht effizient verarbeiten kann. Das Anzeigen eines Reports braucht daher zu lange. Zeichnet man die Karte selber nach, entstehen unschöne Lücken zwischen den Vektoren, es sei denn, man bringt sehr viel Geduld beim Zeichnen der Vektoren auf. Vor allem bei vielen zu zeichnenden Vektoren bietet sich diese Methode nicht an.

Performancetests von Cubes

Der folgende Beitrag befasst sich mit dem Testen der Performance von Cubes auf einem SQL Server. Es soll aufgezeigt werden, wie man die Geschwindigkeit von Abfragen auf Cubes aufzeichnen kann und welche Unterschiede zwischen einem relationalen OLAP Cube und einem Tabular-Cube sowie zwischen Cubes ohne und mit Clustered-ColumnStore-Indizes in Bezug auf die Abfragegeschwindigkeit bestehen.

Vorbereitungen

Die hierbei zugrundeliegenden Daten sollen die Passagierzahlen an Berliner Bahnhöfen widerspiegeln. Grundlegend sind für die späteren Untersuchungen drei Tabellen wichtig. Eine Date-Dimension, eine Dimension zu den Stationen, sowie eine Log-Faktentabelle, die die Fahrgastzahlen(wartende, einsteigende & austeigende Passagiere) an den Bahnhöfen erfasst.

 

 

Anhand dieser Struktur sollen verschiedene beispielhafte Abfragen nach ihrer Dauer untersucht und verglichen werden. In der Faktentabelle sind dabei 492.591 Zeilen enthalten. Exemplarisch werden hier drei Abfragen untersucht:

  1. Durchschnitt aller hinzukommenden, wartenden und verlassenden Passagiere aufgeteilt nach Stationen
  2. Durchschnitt aller hinzukommenden, wartenden und verlassenden Passagiere aufgeteilt nach Stationen am 16.12.2014
  3. Neueste Einträge der hinzukommenden, wartenden und verlassenden Passagiere aufgeteilt nach Stationen (Diese Werte müssen vorher als Calculated Measures formuliert werden)

Da die Abfragen einmal für einen multidimensionalen und einmal für einen Tabular-Cube ausgeführt werden müssen, müssen diese zum einen in MDX (multidimensional) und zum anderen in DAX (Tabular) geschrieben bzw. ausgeführt werden. Die Abfragen wurden nicht im SQL-Server geschrieben und ausgeführt, sondern mittels Excel über eine Pivot-Tabelle (Rolap) und ein Power View Sheet (Tabular) realisiert.

Die Abfragegeschwindigkeit kann dabei mit dem SQL Server 2014 Profiler aufgezeichnet werden. Dazu benötigt man eine neue Trace (File -> New Trace). Daraufhin wird eine Verbindung zum Server hergestellt, wo der Cube liegt und die Abfragen geschrieben werden. In den darauffolgenden Einstellungen (Tab „Events Selection“) ist wichtig, dass die Häkchen für „Queries Events“ gesetzt sind:

 

 

In der folgenden Übersicht kann man für jede Query auf dem Server ein Start- und ein End-Event sehen, welche die Eigenschaften der Query beinhalten. Interessant ist hierbei vor allem die Dauer, welche in Millisekunden angegeben wird. Zum besseren Verständnis sind jedoch die später folgenden Werte der Abfragedauer in Sekunden angegeben.

Zuerst sollt jedoch bedacht werden, dass beim Testen der Abfragen die Zwischenspeicher der Datenbank und der SSAS gefüllt werden, sodass ein Wiederholen der Abfrage die Ergebnisse beinträchtigen könnte. Um diesen zu entgehen, sollte vor dem Messen der einzelnen Abfragen zum einen der Cache der Datenbank per SQL-Befehl geleert werden:

 

USE [Datenbankname];

GO

CHECKPOINT;

GO

DBCC DROPCLEANBUFFERS;

GO

 

Der zweite Schritt ist die Leerung des Cache von SSAS. Beachten sollte man hier, dass dies einmal für den Rolap Cube und einmal für den Tabular Cube passieren muss. Dazu lässt sich eine XMLA Query nutzen:

 

<ClearCache
xmlns=http://schemas.microsoft.com/analysisservices/2003/engine>

    <Object>

        <DatabaseID>Datenbankname</DatabaseID>

    </Object>

</ClearCache>

 

Um die Abfragen mittels Clustered-ColumnStore-Indizes auszuführen muss in der Ausgangsdatenbank im SQL-Server für jede Tabelle ein Clustered-ColumnStore-Index angelegt werden:

 

 

Ergebnisse ohne Clustered-ColumnStore-Indizes

 

Abfrage 1

Abfrage 2

Abfrage 3

Rolap

5,222

1,751

9,859

Tabular

0,940

0,951

116,596

 

Man erkennt deutliche Unterschiede zwischen den zwei Cubes. Die Abfrage 1 zeigt einen erheblichen Geschwindigkeitsvorteil des Tabular Cube mit Direct Query Mode gegenüber dem Rolap Cube. Abfrage 2 ist eine Weiterentwicklung von Abfrage 1 mit einem zusätzlichen Filter. Dieser beschleunigt die Abfragegeschwindigkeit beim Rolap Cube, jedoch nicht beim Tabular Cube. Dennoch ist der Tabular Cube schneller als der Rolap Cube. Die dritte Abfrage beinhaltet Calculated Measures und dort wird ersichtlich, dass der Tabular Cube einen enormen Nachteil hinsichtlich der Abfragedauer hat, da er ca. 11mal länger benötigt als sein Gegenpart.

Ergebnisse mit Clustered-ColumnStore-Indizes

 

Abfrage 1

Abfrage 2

Abfrage 3

Rolap

5,162

1,245

8,154

Tabular

0,792

0,615

108,599

 

Mit den Clustered-ColumnStore-Indizes ändert sich an den Unterschieden zwischen dem Rolap Cube und dem Tabular Cube wenig. Die Dauer von Abfrage 1 hat sich bei beiden Cubes durch die Indizes nur unwesentlich verkürzt und somit bleibt der Geschwindigkeitsvorteil hier beim Tabular Cube, sowie auch bei Abfrage 2. Jedoch macht sich hier auch durch die Indizes bereits ein Unterschied bei beiden Cubes bemerkbar, wodurch die Abfragedauer um ca. 1/3 verkürzt wurde. Die dritte Abfrage wiederum bietet auf die gesamte Abfragedauer gesehen nur eine kleine Verkürzung im Bereich von 5% (Tabular) – 20% (Rolap) durch die Indizes.

Fazit

Aus den Ergebnissen kann man schließen, dass der Tabular Cube im Direct Query Mode für einfachere Abfragen sich besser eignet als der Rolap Cube. Dieser hat jedoch enorme Vorteile wenn man z.B. berechnete Measures einsetzt. Die Clustered-ColumnStore-Indizes vermögen bei diesem Beispiel keinen großen Ausschlag auf die Abfragegeschwindigkeit geben. Es ist zu vermuten, dass sich dies jedoch bei größeren Datenmengen ändert und so größere Vorteile durch den Einsatz der Indizes zu beobachten wären. Man sollte auch beachten, dass sich die Unterschiede zwischen dem Tabular Cube und dem Rolap Cube bei größeren Datenmengen durchaus verschieben können.