SQL: Dateiname extrahieren

Hier ein schönes Beispiel, in der man die SQL-Server-String-Funktion reverse zum Umdrehen eines Strings (Hallo –> ollaH) sinnvoll einsetzen kann.

Wir nehmen an, dass in einer Tabelle Dateinamen voll qualifiziert stehen, also z.B. c:tempblogtest.txt.

Nun sei die Aufgabe, den Dateinamen (hier test.txt) zu ermitteln. Dazu muss das letzte Auftreten von gefunden werden und der String rechts davon ermittelt werden.

Dies geschieht so:

case when charindex(“, Dateiname) > 0 then
substring(Dateiname, len(dateiname)-charindex(“, reverse(Dateiname))+2, len(dateiname)) else Dateiname end

Rollierendes Log-File in SSIS-Paketen

Bei Integration Services-Paketen ist es einfach, ein Logfile einzustellen (SSIS>Logging…), das gewünschte Events (Errors, Warnings, Information, …) protokolliert. Im Standard wird ein solches Logfile bei jedem Lauf des Pakets größer, da immer neue Zeilen angefügt werden. Dies hat Nachteile, da es Eingriffe durch einen Administrator erfordert.

In der Regel verwende ich deshalb in meinen Paketen Logfiles, die den Wochentag im Logfilenamen haben (datei_1.log für Montag, datei_2.log für Dienstag, …), so dass die Logfiles nach 7 Tagen überschrieben werden, so dass eine ausreichende Anzahl von Logfiles für die Fehlersuche zur Verfügung stehen, der belegte Plattenplatz aber nicht ständig steigt.

Dazu gehe ich wie folgt vor:

Es werden zwei Variablen definiert, deren Werte über Konfigurationsdateien (*.dtsconfig via SSIS>Package Configurations…) eingestellt werden:

  • setting_LogPath: Der Pfad, in dem die Logfiles liegen sollen, z.B. „c:temp“
  • setting_LogFileName: Name des Logfiles, z.B. testlog

Dann gibt es eine Variable „LogFile“, die über eine Expression berechnet wird:

Variable LogFile - einige Properties

@[User::setting_LogPath] + ((right(@[User::setting_LogPath],1)==“\“) ? „“ : „\“) + @[User::setting_LogFileName] + „_“ + (DT_WSTR, 1) DATEPART( „dw“, GETDATE() ) + „.log“

Einige Bemerkungen zu dieser Berechnung:

  • Falls der Pfad nicht auf endet, wird ein an den Pfad angefügt. Achtung muss wie in C# zu \ „escapet“ werden.
  • Der Wochentag wird über Datepart ermittelt. Statt GetDate() könnte auch das Startdatum des Pakets (System-Variable @[System::StartTime]) verwendet werden
  • Im deutschen SSIS heißen die Variablen @[Benutzer:: statt @[User::

Im Connection Manager wird dann eine File-Connection angelegt:

SSISLogFile im Connection Manager

Über die Expressions dieser Connection wird der Dateiname auf die Variable „Logfile“ gesetzt:

Eigenschaften der SSISLogFile Connection

Somit wird jeden Wochentag ein anderes Logfile verwendet. Da dabei immer angefügt wird, muss noch das morgige Logfile gelöscht werden.

Dazu wird noch eine Variable „LogFile2Delete“ angelegt, die über folgende Expression – analog zu LogFile – berechnet wird:

@[User::setting_LogPath] + ((right(@[User::setting_LogPath],1)==“\“) ? „“ : „\“) + @[User::setting_LogFileName] + „_“ + (DT_WSTR, 1) DATEPART( „dw“, dateadd(„day“, 1, GETDATE()) ) + „.log“

Über ein FileSystemTask wird diese Datei dann zum Ende des Pakets gelöscht. Falls die Datei nicht existiert, macht das nichts – die FileSystemTask schmeißt keinen Fehler.

Lösche morgiges Logfile über eine FileSystemTask

Einstellungen der FilesystemTask

SSIS – binäre Collation bei der Suche

In einem früheren Blog-Eintrag habe ich beschrieben, wie die Suche im SQL Server Integration Services (im Standard) abhängig von der Groß- und Kleinschreibung ist. Das liegt daran, dass der Cache im Integration Services binär angelegt werden, so dass „Martin“, „martin“ und „MARTIN“ 3 unterschiedliche ELemente sind, wohingegen bei einem SELECT DISTINCT Vorname FROM Personen dies (in der Standard-Collation) nur als einen Wert zurückliefern würde.

Dieses Phänomen tritt aber natürlich nicht nur bei Groß- und Kleinschreibung auf, sondern bei allen Strings, die laut Collation gleich sind.

Damit führt das Standard-Szenario nicht zum gewünschten Ergebnis, wenn ich zum Beispiel Vornamen in eine Dimensionstabelle umsetzen will:

Paket-Beispiel Vornamen auf IDs umsetzen

Laden der Dimensionen (Vorname)Beispiel für Faktenimport

 

In meinem Beispiel habe ich die Personen-Tabelle wie folgt gefüllt:

Inhalt der Tabelle Personen

Auf der Nachnamen-Spalte habe ich die Standard-Collation (SQL_Latin1_General_CP1_CI_AS) verwendet, die Vornamen-Spalte habe ich so eingestellt, dass auch Akzente ignoriert werden (Collation Latin1_General_CI_AI) (OK, das ist ein bisschen gestellt, aber in der Praxis hatte ich einen ähnlichen Fall).

Somit liefert select distinct vorname from person:

SELECT DISTINCT Vorname FROM Person

und select distinct nachname from person:

SLECT DiSTINCT Nachname FROM Person

Man beachte, dass bei der Standard-Collation ß wie ss behandelt wird.

Nun ist klar, dass beim Lookup nach Vorname im Faktenimport nicht alle Vornamen einen Treffer liefern und somit das ETL einen Fehler schmeißt.

Außerdem ist es vermutlich nicht sinnvoll, dass Michel und Michél gleiche Vornamen sind. Deswegen beitet es sich in diesem Fall an, dass alle unterschiedlichen Schreibweisen der Vornamen auch unterschiedliche Dimensionselemente werden. Dazu kann man im Dimensions-SELECT statt „SELECT DISTINCT Vorname FROM Personen“ besser „SELECT DISTINCT vorname COLLATE Latin1_General_BIN as Vorname from person“ verwendet. Damit instruiert man den SQL Server die binäre Collation zu verwenden und somit sind beim SQL Server selbst die einzelnen Vornamen nicht mehr gleich – und schon geht’s.

Eine andere Alternative wäre gewesen, über eine Funktion Strings, die gleich sein sollen, in einen eindeutigen String zu überführen (wie in meinem letzten Blog-Eintrag mit UPPER()). Bei Akzenten ist das aber nicht so einfach.

Eine weitere Alternative ist, den Lookup nicht mit vollem Cache zu machen, was aber negative Auswirkungen auf die Performance hat. Also könnte man auch zuerst einen Lookup mit vollem Cache und im Fehlerfall einen nachgelagerten Lookup ohne Cache ausführen. Im zweiten Lookup kann man dann über die Collation genau definieren, welche Strings gleich sein sollen und welche nicht.

In einem solchen Fall muss man also auf jeden definieren, welche Strings im DWH als gleich angesehen werden sollen. Über die COLLATION kann man das – wie gezeigt – sehr fein einstellen.

Natürlich sind solche Szenarien selten, da in der Regel nicht beliebige Freitextwerte in einer Dimension vorkommen – aber, wie in meinen Projekten geschehen, ab und zu gibt es dann doch solche Fälle.

Performance und GUIDs

In einem Kunden-Projekt sollte ich die Performance der SQL-Zugriffe einer .NET-Applikation verbessern.

Hier beschreibe ich die Ergebnisse, da sie sich auch auf andere Szenarien verallgemeinern lassen.

Die erste Veränderung war, keine GUIDs als clustered primary keys zu verwenden. Stattdessen setzen wir nun ints (mit Identity) ein. Dies führt zu einer immensen Beschleunigung bei den INSERTs in die Datenbank. Dies ist sehr gut nachvollziehbar, da nun die INSERTs immer am Ende der Tabelle statttfinden, so dass kein zeitaufwändiges Umorganisieren der Seiten innerhalb der Tabelle notwendig wird.

Leider ging es aber in meiner Aufgabe um die SELECT-Performance und nicht um die INSERT-Performance. Aber auch diese verbesserte sich durch die Verwendung der integer-Werte deutlich (Abfragezeit ungefähr halbiert). Als Test verwendete ich die Abfrage von 500 Datensätzen, die ich zu Beginn zufällig ausgewählt hatte. Vor jeder Abfrage wurde natürlich der Cache geleert 🙂 Die Steigerung lässt sich dadurch erklären, dass alle Indizes nun vom Platzbedarf viel kleiner wurden (1 int = 4 byte, 1 guid = 16 byte –> ca. 4x so viele Daten gehen auf eine Index-Page [natürlich abhängig von den weiteren Feldern des Index]). Außerdem ist ein Zähler besser verteilt als eine Guid.

Als nächste Verbesserung verwendete ich in m:n-Tabellen als clustered primary key nicht einen Zähler, sondern einen zusammengesetzten Schlüssel aus den beiden referenzierten Tabellen (+ ein weiteres Feld, um die Eindeutigkeit sicherzustellen). Dabei verwendete ich als erstes Feld das Feld der beiden, das in den meisten Abfragen bekannt ist. (Auf der umgekehrten Reihenfolge lag natürlich auch ein Index). Dadurch muss beim Standard-Zugriff nicht mehr über einen non-clustered Index zugegriffen werden, wodurch ein Zugriff eingespart wird. Dies brachte eine weitere Halbierung der Zugriffszeit.

ALs letztes gab es spezielle Szenarien, in denen nach Texten gesucht werden musste – ein Beispiel: Man möchte alle Aufträge ermitteln, die in einer Position einen bestimmten Positionsfreitext enthalten. Dann wird folgendes SQL-Statement abgesetzt:

SELECT * FROM Auftrag a INNER JOIN Auftragsposition pos on pos.AuftragID = a.AuftragID WHERE pos.Positionsfreitext like ‚Test%‘

Natürlich war auf der Auftragspositions-Tabelle ein Index auf Positionsfreitext. Ich erweiterte diesen Index um die AuftragID. Dadurch kann der Join direkt über den Index abgewickelt werden und ein Zugriff auf die Tabelle wird eingespart, was für diesen Spezialfall ebenfalls eine deutliche Performance-Steigerung einbrachte.

SQL Server: zufällige Auswahl von n Zeilen einer Tabelle

Bei einer Aufgabe zur Performance-Steigerung einer SQL Server 2008-Applikation (über die ich später berichten werde), war die erste Aufgabe, ein Test-Szenario aufzubauen, anhand dessen die Abfragezeiten verglichen werden konnten.

Der erste Schritt war dazu die Auswahl 100 beliebiger Sätze aus einer Tabelle. Dabei zeigte mir ein Kollege folgende super einfache Möglichkeit:

select top 100 * from sysobjects order by newid()

[Natürlich muss sysobjects durch die entsprechende Applikations-Tabelle ersetzt werden 🙂 ]

Analysis Services: Zahlen-Formatierung mit Texten (z.B. „min“)

Im Analysis Services kann man natürlich die Formatierung der Measures und berechneten Measures vielfältig gestalten. Die Möglichkeiten gehen aber über die Anzahl der Nachkommastellen hinaus.

Im Beispiel will ich ein Measure, das Zeiten beinhaltet, so formatieren, dass die Zahlen als 17,5 min dargestellt werden. Dadurch ist dem Benutzer klar, dass es sich um Dauer in Minuten handelt.

Bei Measures gibt man als Formatstring an: #,##0.00 min oder #,##0.00″ min“

Bei berechneten Measures gibt man als Formatstring an: „#,##0.00 min“ oder „#,##0.00″“ min“““

[Wegen der Anführungszeichen muss man bei den berechneten Measures aufpassen, da bei einfachen Anführungszeichen ein Syntax Fehler erscheint. Aber wie die Beispiele zeigen, kann man die Anführungszeichen um „min“ herum auch weglassen]

Das Ergebnis in Excel 2007:

Formatierung mit Minuten in Excel 2007

Wie man sieht, stimmt die Formatierung. Und es ist wirklich nur eine Formatierung und kein String, so dass in Excel mit der Zahl auch weiter gerechnet werden könnte.

Kalenderwoche im Analysis Services

Ich hatte ja gestern in meinem Blog geschrieben, wie man die deutsche KW im SQL Server berechnen kann. Wenn man darauf basierend eine Datumsdimension aufbaut, bieten sich natürlich zwei kanonische Hierarchien an:

  • Jahr > Quartal > Monat > Tag
  • Jahr > KW > Tag

Allerdings muss man dabei beachten, dass nur die erste eine echte Hierarchie ist. Die KW-Hierarchie ist nicht echt, da zwei Tage aus der selben KW in unterschiedlichen Jahren liegen können (z.B. 31.12.2008 und 1.1.2009 liegen beide in der KW 2009/01).

Deswegen darf man nicht einstellen, dass eine direkte Beziehung zwischen KW und Jahr besteht:

Also statt

Dimension Datum falsche Attributbeziehung

muss es so aussehen (man beachte die Attributbeziehungen der KW):

Dimension Datum KW richtige Attributbeziehungen

Deswegen kann man natürlich trotzdem beide Hierarchien angeben:

Gierarchien der Datumsdimension

(Das gelbe Dreieck gibt an, dass die Hierarchie nicht echt ist und deshalb nicht für Aggregationen verwendet werden kann)

Eine andere Alternative wäre, dass man unterschiedliche IDs und Namen für die KWs spendiert, die in einem anderen Jahr liegen, also beispielsweise:

  • 31.12.2008 liegt in der KW 2009 / 01 (in 2008)
  • 1.1.2009 liegt in der KW 2009 / 01

Damit wäre die KW-Hierarchie auch eine echte Hierarchie. Allerdings kann man dann die KW 2009 / 01 nicht mehr (so einfach) über Jahresgrenzen hinweg auswerten.

Berechnung der deutschen Kalenderwoche in SQL

Die datepart-Funktion des SQL-Servers liefert eine Woche zurück, wenn man wk als Parameter wählt. Dies ist allerdings nicht die Kalenderwoche. Die deutsche Kalenderwoche funktioniert nämlich so, dass die erste Woche des Jahres mit mindestens 4 Tagen in diesem Jahr die KW 1 dieses Jahres ist. Somit kann der 1.1. eines Jahres noch in der letzten KW des letzten Jahres liegen (nicht so im neuen Jahr 2009 – aber bis 2010 wollte ich nicht mit diesem Blog-Eintrag warten 🙂 )

Offensichtlich hängt die Berechnung davon ab, mit welchem Wochentag die Woche beginnt – bei uns in Deutschland mit dem Montag. Der SQL-Server verfügt über eine Einstellung @@DateFirst, die genau das angibt. Mit SET DATEFIRST 1 kann angegeben werden, dass der Monatg der 1. Tag der Woche ist. Diese Anweisung ist gültig für die Session.

In meinen hier beigefügten Skripten habe ich mich unabhängig von den Einstellungen mit DATEFIRST gemacht, so dass sie unabhängig von allen Einstellungen funktionieren und immer die deutsche Kalenderwoche zurückgeben:

select convert(nvarchar(10), getdate(), 104) Tag, dbo.getKW_Woche(getdate()) KW_Woche, dbo.getKW_Jahr(getdate()) KW_Jahr

liefert als Ergebnis

Tag KW_Woche KW_Jahr
01.01.2009 1 2009

Das bedeutet, dass der heutige Tag in der KW 1/2009 liegt, wie übrigens der 31.12.2008 auch.

In diesem Sinne, ein gutes, gesundes und erfolgreiches Neues Jahr

Nettes Projekt: Im InfoView von BO XI R2 die Prompts anpassen

MultiBase hat einen Kunden, der schon seit langem mit Business Objects arbeitet. Da es ein weltweit tätiges Unternehmen ist, wollte der Kunde die Namen der Berichte, Kategorien und Prompts in mehreren Sprachen anzeigen. Außerdem sollten die Prompts schöner + benutzerfreundlicher gestaltet werden unter Verwendung von Listen, Komboboxen, Checkboxen, abhängigen Prompts (ausgewähltes Land bestimmt den Inhalt der Bundesland-Kombobox) und allem, was das Herz begehrt.

In einem früheren Projekt hatten wir bereits diese Anforderung in BO 6.5 umgesetzt. Dazu hatten wir die ASP-WebI-Version verwendet und die ASP-Seiten modifiziert.

Jetzt beim Umstieg auf XI R2 sollten dieselben Anforderungen auch dort umgesetzt werden. Deswegen verwendeten wir dort das .NET InfoView. Da dazu alle Sourcen mitgeliefert werden, war es möglich, sich an einer Stelle in den bestehenden Code zu hängen und über eine Art Weiche (wenn in unserer Meta-Datenbank zu diesem Bericht Einstellungen gesetzt waren) auf eigenen aspx-Code umzuleiten. Dort erstellen wir in HTML die Prompts wie in einer Meta-Datenbank definiert. Letzlich definiert „unsere“ Seite dann die gleichen hidden fields, die auch die Originalseite definiert und somit können wir dann wieder in den Standard-Code zurückkehren.

Dieses Projekt ist nicht typisch für meine Projekte (es hat ja auch gar nichts mit Microsoft zu tun), aber es war trotzdem recht nett, weil es gelang, (trotz sehr schlechter Dokumentation) eine sehr schöne Erweiterung für InfoView zu erstellen.

Dabei musste man eine ausgewogene Lösung finden, die folgenden Anforderungen genügt:

  • Umsetzung der fachlichen Anforderung (Mehrsprachigkeit, Prompts)
  • Geringe Kosten: Kein eigenes Frontend bauen, sondern nur an wenigen Stellen das vorhandene verbessern
  • Nur minimale Veränderungen an dem bestehenden BO-Code, da jede Veränderung Probleme nach sich zieht (Beim Einspielen von Service Packs etc.).

Jetzt sind wir richtig stolz, was wir aus BO XI gezaubert haben.

Ich war in diesem Projekt für die technische Konzeption und für die Erarbeitung der grundsätzlichen Logik, wie wir uns an welchen Stellen in InfoView einhängen können.

SQL Server: Versteckte Einstellung für Copy & Paste mit Spaltenüberschriften

Es kommt ziemlich oft vor, dass man das Ergebnis einer SQL-Abfrage (aus dem SQL Server Management Studio) in Excel weiterbearbeiten will (z.B. Filtern, Sortieren etc.). Dabei ist extrem störend, dass die Spaltenüberschriften nicht mit kopiert werden. Glücklicherweise kann man das unter Tools > Options einstellen:

Einstellungen, um die Spaltenüberschriften bei Copy&Paste mitzunehmen

PS: Danke, Stefan, für den Tip.

Meine Erfahrungen in der Business Intelligence Welt