Web-Veröffentlichungen zu Reporting Services

Ich habe zwei Artikel unter www.SqlServerCentral.com veröffentlicht, in denen ich aufzeige, wie man die in meinen Augen größten Mankos des Reporting Services umgehen kann:

  • Reporting Services ist nicht in der Lage, zwei DataSets zu joinen. In meinen Artikeln zeige ich,
    • wie man in einer Kreuztabelle sowohl die Istwerte auf Monatsebene (erstes SQL-Statement) als auch die Planwerte in Summe (zweites SQL-Statement) kombinieren kann.
    • wie man sogar SQL-Abfragen mit Abfragen auf andere Datenbanktypen (in meinem Fall eine Analysis Services-Datenbank) kombinieren kann
  • Die Kreuztabelle (Matrix) von Reporting Services hat wenig Funktionalitäten, so dass selbst das Hinzufügen einer weiteren Spalte nicht möglich ist (lediglich Summen-Spalten werden unterstützt)

Diese Artikel sind unter http://www.sqlservercentral.com/articles/Reporting+Services/63415/ und http://www.sqlservercentral.com/articles/Linked+Server/63867/ nachzulesen.

Dazu benötigt man ein kostenloses Account bei SQLServerCentral.com

SSIS: Achtung bei Groß- und Kleinschreibung – Warum

Gestern und vorgestern habe ich anhand der Beispiele Aggregate und Lookup beschrieben, welche Unterschiede zwischen SSIS und SQL Server in Bezug auf Groß- und Kleinschreibung zu beachten sind.

Hier ein Wort zur Motivation:

Man könnte sich ja fragen, warum ist da der SSIS so pingelig und erschwert mir als altem SQL-Entwickler die Arbeit?

Ein Grundprinzip beim SSIS ist Performance (worüber wir alle ja froh sind).

Offensichtlich ist es schneller, wenn ein Unterschied zwischen Groß- und Kleinschreibung gemacht wird, denn – binär gesehen – besteht natürlich ein Unterschied zwischen „a“ und „A“. Also seien wir froh 🙂 , dass der SSIS sich genau so verhält.

SSIS: Achtung bei Groß- und Kleinschreibung – Lookup

Gestern habe ich beschrieben, welche Unterschiede zwischen SQL Server und SSIS bei Groß- und Kleinschreibung bei der Aggregation zu beachten sind.

Natürlich ist das Verhalten der beiden Produkte konsistent:

  • SQL Server unterscheidet grundsätzlich (im Standard) nicht zwischen Groß- und Kleinschreibung
  • SSIS unterscheidet grundsätzlich (im Standard) zwischen Groß- und Kleinschreibung

Damit ist auch klar, dass andere Transformationen von Unterschieden betroffen sind. Heute betrachte ich die SSIS-Transformation Lookup (Suche):

Als Beispiel verwende ich wieder die Tabelle Customers

Tabelle Customers

und die neue Tabelle Countries

tabelleCountries

Eine Anmerkung zu diesem Beispiel: Die Verwendung von Strings für Schlüssel liefert mir ein einfaches Besipiel, soll aber natürlich nicht als Standard für die Datenmodellierung angesehen werden 🙂

Der SSIS-Data Flow soll nun alle Kunden mit zugehöriger Hauptstadt ermitteln.

Die Lookup-Transformation funktioniert auf zwei unterschiedliche Arten:

  • komplettes Laden der Lookup-Tabelle (hier Countries) vor dem Start der Ausführung des eigentlichen Data Flows – dies ist der Standard
  • Einzelnes Laden der nachzuschlagenden Datensätze – wenn man unter dem 3. Reriter (Advanced) den Haken bei „Enable memory restriction“ aktiviert.

Ich beginne mit dem zweiten:

Einzelnes Laden der nachzuschlagenden Datensätze:

Die Einstellungen für die einzelnen Reiter der Lookup-Transformation sind wie folgt:

  • Reference Table: verwende Tabelle Countries
  • Columns: Der Join geht über Country — Country. Als zusätzliche Spalte wird Capital ausgegeben
  • Advanced: Wir setzen den Halen bei „Enable memory restriction“. Der Rest bleibt im Standard (keine weiteren Haken)

Als Ergebnis erhalten wir:

ErgebnisLookup2

Das liefert also das gewünschte Ergebnis.

Wie geht hier SSIS intern vor?

Für jede Zeile wird ein SQL-Statement ausgeführt, das zu der Country die Capital dazuliest (Also z.B. SELECT * FROM Countries WHERE Country = ‚de‘) [Das genaue SQL-Statement sieht man im Bereich „Caching SQL-Statement“ auf dem 3. Reiter (Advanced)].

Da der SQL Server nicht zwischen Groß- und Kleinschreibung unterscheidet, unterscheidet also auch diese Version der Lookup-Transformation nicht.

Natürlich ist aber dieses Vorgehen bei großen Datenmengen imperformant, da für jede Zeile ein SQL-Select ausgeführt wird. Daran ändern auch die weiteren Optionen unter Advanced nichts (grundlegendes): Enable Caching würde nur verhindern, dass nicht zweimal dasselbe SQL-Statement ausgeführt wird (Wenn also „de“ in zwei Zeilen auftauchen würde). Der Cache selbst wäre im übrigen wieder case-sensitive (D.h. unterscheidet zwischen Groß- und Kleinschreibung) – es würden also für „de“ und „DE“ zwei SELECTs ausgeführt.

Deswegen jetzt die Betrachtung der Standard-Methode des Lookups:

komplettes Laden der Lookup-Tabelle

Wir entfernen den Haken „Enable memory Restriction“ im Tab „Advanced“ und starten das Paket nochmals.

Sofort erhalten wir einen Fehler, dass der Lookup keinen Treffer findet. Durch Erweitern des Data Flows sehen wir die nicht gefundenen Datensätze:

Bild

Wir sehen also, dass der Lookup im Standard Groß- und Kleinschreibung unterscheidet.

Wie kann man dieses Problem umgehen?

Man kann natürlich die verwendeten Spalten vor dem Zugriff auf Großbuchstaben konvertieren:

  • Im SSIS durch das Einfügen einer derived Column, die mittels UPPER( [Country]) entweder eine neue Spalte erzeugt oder die bestehende überschreibt.
  • Im SQL-Statement für den Referenz-SELECT durch UPPER(), z.B. SELECT Upper(Country) as CountryUpper, * from countries

Natürlich gibt es noch andere Möglichkeiten zum Umgehen des Problems wie Fehlerhandling. Das erscheint mir hier aber konstruiert.

SSIS: Achtung bei Groß- und Kleinschreibung – AGGREGATE

Der SQL-Server unterscheidet (im Standard) bei den Daten nicht zwischen Groß- und Kleinschreibung.

Gerade für versierte SQL-Entwickler führt dies bei der Verwendung der Integration Services zu Schwierigkeiten, da Integration Services sehr wohl zwischen Groß- und Kleinschreibung unterscheidet. Somit verhalten sich SSIS-Transformationen leicht anders als ihre SQL-Pendants:

Ich möchte auf die SSIS-Transformationen Aggregation (Aggregate) und die Suche (Lookup) [im nächsten Blog-Eintrag] eingehen:

Zunächst vergleiche ich die SSIS-Transformation Aggregate mit dem SQL-Befehl GROUP BY:

Als Ausgangsdaten verwende ich die Tabelle CustomersTabelle Customers

Damit liefert das SQL-Statement

SELECT Country, count(*) as Anzahl
FROM Customers
Group by Country

folgendes Ergebnis (de=DE im SQL Server):

Bild

Die naheliegende SSIS-Transformation liefert aber (de <> DE im SSIS):

Bild

Hierbei habe ich das Standard-Verhalten der Tools dargestellt. Man kann aber in beiden Produkten ein gleiches Verhalten erzeugen:

  • Im SQL Server könnte man die Collation Spalte ändern, so dass die Werte auch Groß- und Kleinschreibung unterscheidet, also zum Beispiel Latin1_General_CS_AS
    Bild
    Diese Änderung betrifft natürlich die komplette Tabelle und somit alle Abfragen auf diese Tabelle!
  • Wenn man nur diese eine Abfrage im SQL auf „Beachte Groß- und Kleinschreibung“ setzen will, so kann man folgendes Statement verwenden:
    SELECT Country , count(*) as Anzahl
    FROM (select country COLLATE Latin1_General_CS_AS as Country, CustomerName FROM Customers) as tab
    Group by Country
    Ohne die Verwendung von Derived Tables (dem obigen „SubSelect“) bzw. Common Table Expressions geht es nicht. Ein Statement wie
    SELECT Country COLLATE Latin1_General_CS_AS, count(*) as Anzahl
    FROM Customers
    Group by Country
    liefert also immer noch 5 Zeilen.
  • In der SSIS-Transformation Aggregate kann man einstellen, dass die Groß- und Kleinschreibung ignoriert werden soll:
    Bild