Alle Beiträge von Martin Cremer

NULL-Werte bei Lookups

Lookups kommen bei SSIS-Data Flows ziemlich häufig vor. Zum Beispiel müssen bei der Verarbeitung von Fakten-Sätzen enthaltene Dimensions-Referenzen umgesetzt werden. Zum Beispiel könnte es sein, dass Deutschland im Quellsystem ‘D’ ist, im DWH aber mit 49 abgebildet wird.

Das ist mit Lookups natürlich ganz einfach.

Nun kommt es aber vor, dass nicht alle Fakten eine Referenz auf ein Land haben müssen, also NULL sind. Diese sollen dann auch NULL bleiben.

Wie kann eine solche Aufgabe gelöst werden?

Die Standard-Lösung wird so aussehen:

Vor dem Lookup wird über einen Conditional Split überprüft, ob das Land leer ist:

  • Wenn ja, dann wird der Zielwert auch auf NULL gesetzt
    (Dafür muss nichts gemacht werden, da dies beim folgenden UNION ALL automatisch erfolgt)
  • Wenn nein, dann wird der Lookup durchgeführt

Danach werden beide Pfade über einen UNION wieder zusammengeführt:

image

In meinem Beispiel sieht die Lookup-Quelle so aus:

SELECT N’D‘ as Country, 49 as nummer
UNION ALL
SELECT N’A‘ as Country, 43 as nummer
UNION ALL
SELECT N’CH‘ as Country, 41 as nummer

Diese Vorgehensweise hat mehrere Nachteile:

  • Über das UNION ALL wird immer ein neuer Buffer angelegt, was insbesondere bei großen Data Flows unnötig Speicher belegt (UNION ALL ist semi-blocking)
  • Die Lösung ist einigermaßen komplex
  • Falls eine Sortierung vorliegt, geht die Sortierung durch das UNION ALL verloren. Dies sollte nicht durch den Einsatz eines MERGE verhindert werden, da dies die Performance sehr negativ beeinflussen kann (vielleicht schreibe ich dazu mal einen eigenen Blog-Eintrag)

Deswegen hatten wir nach einer besseren Lösung gesucht:

Der erste Ansatz war folgender: Wir können beim Lookup Nicht-Treffer ignorieren und danach überprüfen, ob wir ein Mapping übersehen haben und dann selbst einen Fehler schmeißen:

imageDer Conditional Split enthält folgende Bedingung:

!(ISNULL(Country)) && ISNULL(nummer)

In der Skriptkompopnente wird ein Fehler mit folgendem Code erzeugt:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    bool p=false;
    ComponentMetaData.FireError(10, „Testkomponente“, „Country was not null, but could not be mapped to a country number“, „“, 0, out p);
}

Dieser Ansatz hat folgenden Vorteil:

  • Alle Komponenten sind synchron, benötigen also keine zusätzlichen Buffer.

– aber auch folgende Nachteile:

  • Die Lösung ist noch komplexer
  • Die Lösung ist unübersichtlich und schlechter wartbar, da ohne die Skriptkomponente der Lookup falsch programmiert wäre. Das muss aber der wartende Mitarbeiter wissen. Außerdem ist ein Error Handling “Fehler ignorieren” irreführend.
  • Skript-Komponenten haben selbst keinen Fehler-Output. Damit kann eine Skript-Komponente leider nicht mit der vorhin vorgestellten Quarantäne-Idee automatisch korrigiert werden.

Die beste Lösung ist somit eine ganz einfache:

Wir  stellen den Lookup wieder auf Fehler bei einem Nicht-Treffer. Wir ergänzen die Quelle des Lookups um NULL-Werte, also in meinem Beispiel

SELECT N’D‘ as Country, 49 as nummer
UNION ALL
SELECT N’A‘ as Country, 43 as nummer
UNION ALL
SELECT N’CH‘ as Country, 41 as nummer
UNION ALL
SELECT NULL, NULL

bzw. allgemein

SELECT * FROM <lookup-tabelle>
UNION ALL
SELECT NULL, NULL

Dann sieht der Data Flow ganz einfach aus:

image

Dies hat natürlich etliche Vorteile:

  • Die Lösung ist super-einfach.
  • Die Lösung ist natürlich synchron und somit sehr schnell.

Allerdings funktioniert diese Lösung nur, wenn Full Cache eingestellt ist:

image

Dies ist aber eh die meistens verwendete (da performanteste) Variante.

Die Ursache, warum die anderen Cache-Einstellungen nicht funktionieren, liegt darin, dass im SQL NULLs anders verwendet werden (sie können nicht mit = abgeprüft werden). In den anderen Cache-Einstellungen würden SQLs wie SELECT * FROM <lookuptabelle> where Country = NULL ausgeführt.

Error Handling im Data Flow eines SSIS-Pakets

In den meisten Komponenten innerhalb eines Data Flows in einem SSIS-Paket können als Error Handling folgende Einstellungen getroffen werden:

Englisch Deutsch Bemerkung
Fail Component Fehler bei Komponente Dies ist die Standard-Einstellung. Sie führt dazu, dass bei einem – wie auch immer gearteten Fehler – der gesamte Data Flow fehlschlägt. Noch nicht verarbeitete Daten werden nicht weiter verarbeitet.
Ignore Failure Fehler ignorieren Diese Einstellung würde bedeuten, dass trotz des Fehlers normal weitergearbeitet wird. Dies ist in der Regel nicht sinnvoll.
Redirect Row Zeile umleiten Die fehlerhafte Zeile wird umgeleitet und der aufgetretene Fehler kann durch einen speziellen Ablauf behandelt werden.

In der Regel ist es sinnvoll, während der Entwicklung “Fail Component” einzusetzen. Während der Entwicklung und der Tests möchte man ja schließlich wissen, ob und wenn ja, welche Fehler auftreten, damit man sie programmatisch beheben kann.

Auch für den produktiven Einsatz kann diese Einstellung sinnvoll sein, da sie dafür sorgt, dass keine fehlerhaften Daten ins DWH übernommen werden.

Auf der anderen Seite ist die Einstellung problematisch, da dann ein fehlerhafter Satz die ETL-Strecke zum Erliegen bringt. In einem meiner Projekte laden wir alle 10 Minuten aus einem Quellsystem, das sehr großzügig mit Eingaben vorgeht.  Für dieses Projekt haben wir eine Quarantäne definiert:

Alle Komponenten sollen auf “Redirect Row” gestellt werden und diese fehlerhaften Sätze werden in eine Quarantäne-Tabelle geschrieben (unter Angabe des Primärschlüssels und des aufgetretenen Fehlers).

Danach müssen noch “halbe” Datensätze entfernt werden – es kann ja sein, dass in einem Vater-Satz ein Fehler auftritt und dieser in die Quarantäne läuft, aber alle Kinder-Sätze (der 1:n-Beziehung) richtig durchlaufen würden. Diese sollen dann aber natürlich nicht im DWH landen.

Die Umstellung der Komponenten auf “Redirect Row” wollten wir dabei nicht manuell durchführen, da wir Data Flows mit ca. 150 Komponenten haben. Deshalb haben wir ein (VB).NET-Programm erstellt, das via SSIS API diese Komponenten umstellt. Dieses Programm werde ich in den nächsten Blog-Einträgen vorstellen.

Sichere Logging-Tabellen trotz @@identity

In meinem letzten Blog-Eintrag hatte ich geschrieben, man solle möglichst scope_identity() statt @@identity verwenden.

Ich hatte bei einem Kunden genau den Fall, dass wir nachträglich in einer bestehenden Datenbank-Applikation über Trigger Protokollierungen erstellen wollten, um genauere Informationen über ein Fehlverhalten zu bekommen.

Deshalb legten wir eine Logging-Tabelle an, die die entsprechenden Informationen aufnehmen sollte und einen Trigger auf bestimmte Tabellen, um diese Informationen zu sammeln und wegzuschreiben.

Leider hatte der damalige Entwickler @@identity und nicht scope_identity() verwendet, so dass eine Identity-Column in unserer Logging-Tabelle zu Fehlern führte: Beim INSERT erhält das aufrufende Programm eine falsche ID. Hoffentlich ist das Programm sonst sauber programmiert, dann wird man dies wahrscheinlich über krachende Foreign Key-Beziehungen erfahren.

Wir kann man so ein Problem aber lösen, wenn man nichts von dem zugrunde liegenden Programm weiß?

Man  könnte die Identity-Column in der Logging-Tabelle entfernen und stattdessen Unique Identifiers verwenden. Ich hatte ja schon einiges über die Nachteile von Unique Identifiers geschrieben.

In diesem Fall scheint es mir aber gerechtfertigt. Natürlich muss man die Spalte mit newSequentialId() initialisieren, also etwa so:

CREATE TABLE [dbo].[Logging](
    [LogID] [uniqueidentifier]  NOT NULL default(newSequentialId()),
    [Timestamp] [datetime] NOT NULL default(getdate()),
    [Logtext] [nvarchar](max) NULL,
CONSTRAINT [PK_Logging] PRIMARY KEY CLUSTERED
(
    [LogID] ASC
)
)

Dann ist die Änderung unschädlich für den uns unbekannten Code, der möglicherweise @@identity verwendet.

Scope_Identity statt @@Identity

Beides sind Funktionen, um den Wert der Identity-Column nach dem Insert zu erhalten. Als ich SQL Server gelernt hatte (1997), hatte ich nur @@identity gekannt.

@@Identity hat aber einen entscheidenden Nachteil, aber dazu später mehr.

Nehmen wir an, wir haben eine Kunden-Tabelle mit Identity-Column KundeID:

CREATE TABLE [dbo].[Kunden](
    [KundeID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Kunden] PRIMARY KEY CLUSTERED
(
    [KundeID] ASC
)
)

Wenn wir in diese Tabelle einen Kunden eintragen und dann das Ergebnis mit @@Identity oder SCOPE_Identity() abfragen, gibt es keinen Unterschied:

INSERT INTO Kunden SELECT ‚Martin‘
SELECT @@IDENTITY
SELECT SCOPE_Identity()

Beides liefert den Wert 1.

Wenn wir aber nun eine Logging-Tabelle hinzufügen, die ebenfalls eine Identity-Column hat:

CREATE TABLE [dbo].[Logging](
    [LogID] [int] IDENTITY(1,1) NOT NULL,
    [Timestamp] [datetime] NOT NULL default(getdate()),
    [Logtext] [nvarchar](max) NULL,
CONSTRAINT [PK_Logging] PRIMARY KEY CLUSTERED
(
    [LogID] ASC
)
)

Und der Tabelle Kunden einen Insert-Trigger geben, der in diese Tabelle schreibt:

CREATE TRIGGER dbo.tr_i_Kunden
   ON  dbo.Kunden
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
   
    INSERT INTO Logging (LogText) VALUES (‚Kunde angelegt‘)
    INSERT INTO Logging (LogText) VALUES (‚und dann habe ich noch überprüft, ob er schon da ist‘)
    INSERT INTO Logging (LogText) VALUES (‚und noch irgendwas anderes auch gemacht‘)

END

Wenn wir jetzt einen Kunden anlegen, werden automatisch noch 3 Zeilen in die Logging-Tabelle geschrieben.

Da @@identity die letzte Identity-Column der aktuellen Connection zurückliefert, liefert dies den Identity-Wert aus der Logging-Tabelle.

Da SCOPE_Identity() die letzte Identity-Column des aktuellen Scopes (also der betrachteten Tabelle) in der aktuellen Connection zurückliefert, gibt das den Identity-Wert der Kunden-Tabelle.

Wir sehen das hier:

INSERT INTO Kunden SELECT ‚mein Schatz‘
SELECT @@IDENTITY
SELECT SCOPE_Identity()

liefert die Werte 3 für @@identity (da in Logging als letztes die Zeile 3 eingefügt wurde) bzw. 2 für Scope_identity().

Man sollte deshalb grundsätzlich SCOPE_IDENTITY() verwenden, da dies das in der Regel gewünschte Verhalten ist.

Es gibt noch eine Funktion IDENT_CURRENT(‘Tabellenname’). Diese liefert den letzten Identity-Wert für diese Tabelle – egal ob aus meiner Connection oder nicht. Damit sollte man also nicht versuchen, den gerade eingefügten Identity-Wert zu ermitteln. Wenn nämlich parallel eine andere Connection schreibt, hat man Pech gehabt.

SSIS: Tabellen-Variablen als Datenquelle

In SSIS kann man als Datenquelle ein SQL-Statement eintragen:

Eingabefeld für SQL als Datenquelle

Manchmal möchte man in dieser Maske kompliziertere Berechnungen verwenden. Dafür kann man T-SQL (wie bei der Programmierung von Stored Procedures) verwenden. Allerdings funktioniert es nicht ganz wie gedacht:

Als erstes kann man keine temporären Tabellen (beginnend mit #) verwenden. Als Beispiel betrachten wir das SQL-Statement:

select 1 as zahl ,2 as doppelte into #t
select * from #t

Dieses einfache Statement liefert im SQL Server Management Studio – wie erwartet:

(1 Zeile(n) betroffen)
zahl        doppelte
———– ———–
1           2

(1 Zeile(n) betroffen)

Fügt man dieses Statement aber in der SSIS-Datenquelle hinzu, erhält man nach OK folgenden Fehler:

Fehler beim Vorbereiten: #t unbekannt

bzw. als Text:

Fehler bei Datenflusstask [T-SQL Programm [1]]: SSIS-Fehlercode ‚DTS_E_OLEDBERROR‘. OLE DB-Fehler. Fehlercode: 0x80040E14.
Ein OLE DB-Datensatz ist verfügbar. Quelle: ‚Microsoft SQL Server Native Client 10.0‘ HRESULT: 0x80040E14 Beschreibung: ‚Anweisung(en) konnte(n) nicht vorbereitet werden.‘.
Ein OLE DB-Datensatz ist verfügbar. Quelle: ‚Microsoft SQL Server Native Client 10.0‘ HRESULT: 0x80040E14 Beschreibung: ‚Ungültiger Objektname ‚#t‘.‘.

Auch eine verspätete Überprüfung des SQLs bringt keine Besserung.

Allerdings funktioniert es, Tabellen-Variablen zu verwenden. Dort heißt obiger Code entsprechend:

Declare @t as TABLE (zahl integer, doppelte integer)
insert into @t select 1 ,2
select * from @t

Dieser Code lässt sich als Datenquelle eintragen und SSIS erkennt korrekt die beiden Spalten:

Spalten korrekt erkannt

Startet man das SSIS-Paket (damit man etwas sieht, habe ich ein UNION ALL dahinter gehängt), scheint es zu funktionieren:

Datenquelle zu UNION ALL

Alles ist grün – wunderbar.

Aber nein! Es wurde keine Zeile übertragen.

Das ist ein bekanntes Problem, dass in dem INSERT-Statement bereits ein Dataset erzeugt wird – dem entspricht in obiger Ausführung der erste Text “(1 Zeile(n) betroffen)”. Die Daten des zweiten Befehls werden dann gar nicht mehr im SSIS weiterverwendet!

Also muss man das SQL so abwandeln:

set nocount on
Declare @t as TABLE (zahl integer, doppelte integer)
insert into @t select 1 ,2
select * from @t

Durch das “set nocount on” wird das erste Dataset unterdrückt, so dass es jetzt funktioniert:

image

Wir sehen, es wurde tatsächlich eine Zeile übertragen – und die Daten stimmen auch, wie der Data Viewer zeigt.

SSIS: Laufzeitprotokollierung je Task

Wenn man größere SSIS-Jobs schreibt, sollten diese Jobs später auf ihre Laufzeiten kontrolliert werden. Läuft die Laufzeit eines SSIS-Jobs aus dem Ruder, ist es sinnvoll, protokolliert zu haben, wie lange welcher Task des SSIS-Jobs läuft, um schnell den Schuldigen ausfindig zu machen. Alternative Techniken wie Analyse des Log-Files sind sehr mühselig.

In dem Code-Beispiel gehen wir davon aus, dass es eine SQL-Tabelle gibt, in der für jeden SSIS-Job ein Eintrag erzeugt wird, so dass es eine eindeutige Lauf-ID gibt. Diese Lauf-ID wird im ersten Schritt des SSIS-Jobs erzeugt. Dies ist bei uns “Best Practice”

Zur Protokollierung legen wir eine Tabelle wie folgt an:

CREATE TABLE [dbo].[META_ImportLaufTaskDauer](
    [ImportLauf_ID] [int] NOT NULL,
    [TaskName] [nvarchar](200) NOT NULL,
    [Startzeitpunkt] [datetime] NOT NULL,
    [Endezeitpunkt] [datetime] NULL,
CONSTRAINT [PK_META_ImportLaufTaskDauer] PRIMARY KEY CLUSTERED
(
    [ImportLauf_ID] ASC,
    [TaskName] ASC
)
)

Die Spaltennamen sollten selbst erklärend sein.

In diese Tabelle wird nun über Events im SSIS-Paket geschrieben. Dazu werden auf der obersten Ebene des Pakets (und nur dort – also nicht etwa für jede Task) die Events für OnPreExecute und OnPostExecute angelegt:

In dem OnPreExecute wird nun der Startzeitpunkt der Task in die Tabelle geschrieben… :

OnPreExecute

Dazu wird ein Execute SQL-Task angelegt. Dieser beinhaltet folgendes SQL:

declare @Laufid int
set @Laufid = ?

if @LaufId > 0
  INSERT INTO META_ImportLaufTaskDauer (ImportLauf_ID, TaskName, Startzeitpunkt)
VALUES
  (@laufid, left(?, 200), getdate())

Das Mapping der Variablen sieht so aus:

Parameter bei OnPreExecute

Die ID des Importlaufs findet sich hier in der Benutzer-Variablen LaufID. Die andere Variable ist eine System-Variable, die uns den verantwortlichen Task nennt.

… und im OnPostExecute der Endzeitpunkt der Task mit folgendem SQL:

declare @Laufid int
set @Laufid = ?

if @LaufId > 0
UPDATE META_ImportLaufTaskDauer
Set Endezeitpunkt = getdate()
where ImportLauf_ID = @Laufid and TaskName= left(?, 200)

Das ParameterMapping ist wie im OnPreExecute.

Dynamisches Top n in SQL 2000

Ab SQL Server 2005 kann man TOP n über SELECT TOP (@n) … erledigen:

declare @n integer
set @n = 2

select top (@n) a
from
(select 1 as a
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
) as t
order by a desc

Das liefert als Ergebnis

a
———–
6
5

(2 row(s) affected)

In früheren Versionen geht das leider nicht. Da hilft die Verwendung von set rowcount, das die Anzahl der zurückgegebenen Zeilen definiert:

declare @n integer
set @n = 2

set rowcount @n

select distinct  a
from
(select 1 as a
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
) as t
order by a desc

Das liefert das gleiche Ergebnis wie oben.

Allerdings muss man danach wieder

set rowcount 0

absetzen, damit alle nachfolgenden SQL-Statements in dieser Session wieder alle Datensätze zurückliefern.

Performanceprobleme bei SSAS-Dimensionsabfragen aus SSRS

Nachdem wir in einem Projekt unseren Test-Datenbestand (12 Mio Fakten) auf den Produktiv-Bestand (73 Mio Fakten) erweitert hatten, zeigten die Berichte (SSRS 2008 R2) massiv schlechte Antwortszeiten in bestimmten Berichten auf unseren Cube (SSAS 2008 R2) – jeweils nach der Cubeaufbereitung. Somit war klar, dass Ursache war, dass einige der im Bericht verwendeten Abfragen nicht im Cache waren, da dieser ja durch die inkrementelle Dimensionsaufbereitung und Aufbereitung einiger Cubepartitionen gelöscht wird.

Naheliegende Strategien waren:

  • Aufteilung in mehr Partitionen unter Angabe der Slice-Property
  • Verbesserte Aggregationen über Usage based Aggregation Design
  • Cache Warming – Strategien

All das brachte uns aber nicht wirklich weiter.

Letztendlich stellte sich aber heraus, dass die Ursache gar nicht in den (tlw. komplexen) Abfragen zur Ermittlung der Fakten zu suchen war, sondern in „Dimensions-Abfragen“. Damit meine ich folgendes: Unsere Berichte werden (aus einer eigenen Web-Applikation heraus) mit Parametern aufgerufen, die IDs sind (eigentlich die Member Unique Names, also z.B. Datum.Jahr.&[2010]). Dabei haben die Parameter (aus Performancegründen) keine Datasets, die alle verfügbaren Werte enthalten. Nun wollen wir aber in dem Bericht natürlich auch den übergebenen Wert im Klartext anzeigen, also in obigem Beispiel das Jahr 2010. Deswegen mussten also einfache Abfragen her, die aus dem Member Unique Name den Klartext (Member Caption) ermitteln.

In unserem Projekt hatten wir uns darauf geeinigt, wenn möglich den SSRS-Designer für SSAS-Abfragen zu verwenden, um eine gute Wartbarkeit (ohne tiefere MDX-Kenntnisse) zu erreichen.

Damit gibt es zwei einfache Arten, dieses Problem zu lösen:

Die erste Art, hat eine Spalte – das Jahr – und zusätzlich den (MultiSelect) Report Parameter als Parameter für die Abfrage:

Wizard für die langsame Abfrage
(Bild zum Vergößern anklicken!)

Als MDX ergibt sich:

SELECT { } ON COLUMNS, { ([Datum].[Jahr].[Jahr].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DatumJahr, CONSTRAINED) ) ON COLUMNS FROM [Verkaeufe]) CELL PROPERTIES VALUE

Man kann auch die Abfrage etwas erweitern:

Man fügt ein berechnets Measure (in diesem Fall namens „UniqueName“) mit dem MDX

[Datum].[Jahr].CurrentMember.UniqueName

hinzu:

Wizard der schnellen Abfrage
(Bild zum Vergößern anklicken!)

Damit ergibt sich folgendes MDX:

WITH MEMBER [Measures].[UniqueName] AS [Datum].[Jahr].CurrentMember.UniqueName SELECT NON EMPTY { [Measures].[UniqueName] } ON COLUMNS, NON EMPTY { ([Datum].[Jahr].[Jahr].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DatumJahr, CONSTRAINED) ) ON COLUMNS FROM [Verkaeufe]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Anmerkung: Das dabei enstehende versteckte Dataset zum Befüllen der „verfügbaren Werte“ des Parameters löschen wir, da wir ja keine „verfügbaren Werte“ anzeigen wollen (unsere Dimension hatte zu viele Einträge).

Beide Abfragen scheinen dasselbe zu tun und mit der selben Performance. Dies ist aber ein Trugschluss!

Leeren wir zunächst (und vor Ausführung eines neuen Test-Statements) den SSAS-Cache mit

<ClearCache xmlns=“http://schemas.microsoft.com/analysisservices/2003/engine“>
<Object>
<DatabaseID>SimpleCube</DatabaseID>
<CubeID>Verkaeufe</CubeID>
</Object>
</ClearCache>

(siehe dazu auch: http://www.ssas-info.com/analysis-services-faq/27-mdx/133-mdx-how-do-i-clear-analysis-services-ssas-database-cache)

Dann betrachten wir die Darstellung im SQL Server Profiler (mit den Standard-Einstellungen für den SSAS):

Die erste Abfrage ergibt folgendes Bild:

SQL Profiler Ergebnis der langsamen Abfrage
(Bild zum Vergößern anklicken!)

Schauen wir uns die einzelnen Zeilen an:

  • Session Initialize – spricht für sich
  • Query Begin: Hier fängt die Abfrage an – im unteren Bereich sieht man das MDX und die Parameterwerte für die @Parameter im MDX
  • Progress Report Begin: Daten aus einer Partition werden gelesen (!), d.h. von der Festplatte in den Speicher übernommen.
  • Progress Report End: das dazugehörige Ende
  • Query SubCube: die gelesenen Daten werden verwendet, um die Abfrage zu beantworten (in diesem Fall Non-Cache)
  • Query End: Das Ende der Abfrage

Die zweite Abfrage ergibt ein anderes Bild:

SQL Profiler Ergebnis der schnellen Abfrage
(Bild zum Vergößern anklicken!)

Hier sehen wir, dass kein Zugriff auf eine Partition erfolgt!

Der Zugriff auf die Partition ist natürlich schädlich, da er bei großen Datenmengen lang dauern kann, zumal er gar nicht benötigt wird, da wir ja nur Dimensions-Element-Bezeichnungen abfragen wollen.
Es ist nicht erklärbar, warum SSAS hier dennoch auf die Fakten zugreift.
Deswegen ist die zweite Abfrage auf jeden Fall vorzuziehen!

Noch ein interessante Anmerkung zum Schluss: Wenn man die erste Abfrage im SQL Server Management Studio ausführt, ist sie auch nicht langsam. Da im Management Studio keine parametrisierten Abfragen möglich sind, muss man dazu die Parameter durch die entsprechenden Strings ersetzen, und erhält somit folgendes MDX:

SELECT { } ON COLUMNS, { ([Datum].[Jahr].[Jahr].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(„{ [Datum].[Jahr].&[2001],[Datum].[Jahr].&[2008] }“, CONSTRAINED) ) ON COLUMNS FROM [Verkaeufe]) CELL PROPERTIES VALUE

Führt man dieses MDX aus (nachdem man den Cache geleert hat), so erhält man folgendes Bild:

Ergebnis des Profilers der langsamen Abfrage ausgeführt im Management Studio
(Bild zum Vergößern anklicken!)

Man sieht hier also auch nur ein Query Begin und ein Query End, also auch keinen Zugriff auf die Cube-(bzw. Partitions-)Daten.

Dies hatte uns die Fehlersuche erschwert, da wir natürlich zunächst auf der Suche nach dem verantwortlichen Statement alle Statements im SQL Server Profiler mitgeschnitten und dann einzeln im Management Studio ausgeführt hatten, was – wie eben gesehen – das problematische Statement leider nicht offenbart.

Säulendiagramme mit Datum auf der x-Achse

Bekanntermaßen kann man in Reporting Services alle Standard-Charts darstellen.

Bei den Säulen-Diagrammen hat man sogar – was ich heute zeigen möchte – die Möglichkeit, auf der x-Achse Datumswerte anzuzeigen. Dies ist z.B. in (Pseudo-) Gantt-Charts interessant.

Für unser Beispiel wollen wir die Laufzeit von zwei Phasen eines Projekts darstellen:

Phase 1 läuft vom 1.3.2011 bis zum 20.3.2011, wobei die Zeit ab dem 17.3.2011 rot dargestellt werden soll.
Phase 2 läuft vom 15.3.2011 bis zum 7.4.2011, wobei die Zeit ab dem 1.4.2011 rot dargestellt werden soll.

Das Ziel sieht somit so aus:

Ziel der 2 Phasen mit Datum auf x-Achse
(Zum Vergrößern anklicken)

Um dies zu erreichen, geben wir folgendes im Chart an:

  • als Kategorie: die Phase
  • als Reihe: die Farbe
  • als Wert: das Datum

Allerdings ist das Datum nicht einfach einzugeben. Wir müssen vielmehr folgendes einstellen (examplarisch an der Phase 1 gezeigt):

  • ein transparenter Balken bis zum 1.3.2011
  • ein grüner Balken bis zum 17.3.2011, also 16 Tage breit
  • ein roter Balken bis zum 20.3.2011, also 3 Tage breit

Für den ersten Balken können wir als Wert einfach den 1.3.2011 angeben. Für die darauffolgenden Balken müssen wir aber das Datum als Wert angeben, das die Anzahl der Tage auf den 30.12.1899 addiert, also

  • für einen Balken mit 1 Tag Länge: 31.12.1899
  • für einen Balken mit 2 Tagen Länge: 1.1.1900
  • für einen Balken mit 2,5 Tagen Länge: 1.1.1900 12:00
  • für einen Balken mit 3 Tagen Länge: 2.1.1900
  • für einen Balken mit 16 Tagen Länge: 15.1.1900
  • usw.

Natürlich müssen wir darauf achten, dass die Reihenfolge der Balken stimmt. Deswegen muss die Reihe entsprechend sortiert sein.

Außerdem werden wir die x-Achse so formatieren, dass sie die Datumswerte schön anzeigt.

Nun haben wir alles beisammen, um das Projekt zu schaffen:

Als SQL wählen wir:

SELECT ‚Phase 1‘ as Phase, ‚transparent‘ as Farbe, 1 as sort, convert(Datetime, ‚1.3.2011‘, 104) as wert
UNION ALL
SELECT ‚Phase 1‘ as Phase, ‚green‘ as Farbe, 2 as sort, convert(Datetime, ‚15.1.1900‘, 104) as wert
UNION ALL
SELECT ‚Phase 1‘ as Phase, ‚red‘ as Farbe, 3 as sort, convert(Datetime, ‚2.1.1900‘, 104) as wert
UNION ALL
SELECT ‚Phase 2‘ as Phase, ‚transparent‘ as Farbe, 1 as sort, convert(Datetime, ‚15.3.2011‘, 104) as wert
UNION ALL
SELECT ‚Phase 2‘ as Phase, ‚green‘ as Farbe, 2 as sort, convert(Datetime, ‚16.1.1900‘, 104) as wert
UNION ALL
SELECT ‚Phase 2‘ as Phase, ‚red‘ as Farbe, 3 as sort, convert(Datetime, ‚5.1.1900‘, 104) as wert

Und im Chart muss folgendes eingestellt werden:

Es ist ein Stacked Bar Chart:

Stacked Bar Chart

Die Grundeinstellungen sieht man hier:

Einstellungen des Charts

Die genauen Eigenschaften der Kategorie (Phase) sind:

General Eigenschaften der Category
(Zum Vergrößern anklicken)

und

Sorting Category
(Zum Vergrößern anklicken)

Die genauen Eigenschaften der Reihe (Farbe) sind:

Generelle Eigenschaften der Reihe
(Zum Vergrößern anklicken)

und

Sortier-Eigenschaften der Reihe
(Zum Vergrößern anklicken)

Die genauen Eigenschaften der Werte sind:

Einstellungen der Werte 1
(Hier darauf achten, dass nicht count(Wert) dort steht (wie es SSRS beim Anklicken erstellt), sondern nur wert!

und

Fülleigenschaften der Werte
(Hier darauf achten, dass unter Farbe der Wert „=Fields!Farbe.Value“ steht, zu erreichen über das fx-Symbol)

Dann muss nur noch die x-Achse korrekt eingestellt werden:

Optionen der Achse
(Zum Vergrößern anklicken)

Dabei sind in diesem Fall als Minimum „=DateSerial(2011, 2, 27)“ und als Maximum „=DateSerial(2011,4,10)“ eingestellt. Natürlich sollte man das „in Wirklichkeit“ aus den Daten über min / max und ggf. dateadd von Tagen berechnen.

Als Datumsformat ist das deutsche Format eingestellt:

Datumsformat der x-Achse

Um die kleinen Zacken pro Tag (ohne Datumsanzeige) zu haben (wie man sie oben in dem Ziel-Chart sehen kann), habe ich noch die minor tick marks auf 1 gesetzt:

Minor Tick Marks der x-Achse
(Zum Vergrößern anklicken)

Die vollständige rdl-Datei kann hier heruntergeladen werden.

Dieses Projekt lässt sich zu einem Pseudo-Gantt-Chart ausbauen, in dem dann (nur) die senkrechten Linie und Pfeile fehlen, was für eine erste Orientierung allerdings ausreichend sein dürfte.

Natürlich lassen sich auch andere Visualisierungen damit realisieren, z.B. Darstellung von Up-/Down-Time von Maschinen etc.