Kalendersteuerelement bei Reports auf Cubes

Reporting Services hat ein Kalendersteuerelement, in dem man Datumswerte einfach eingeben kann, wenn ein Prompt vom Typ Date / Time ist. Heute möchte ich beschreiben, wie man das verwenden kann, wenn man einen Bericht erstellt, der auf einem Analysis Services-Cube basiert. Beim Cube ist (in der Regel) das Problem, dass es zwar eine Zeit-Dimension gibt, diese aber natürlich wie alle Dimensionen einen Member Unique Name (also Key) wie z.B. „[Intervall].[Datum].&[20081012]“ oder „[Intervall].[Datum].&[3207]“ haben. Dies ist offensichtlich ein String und kein DateTime, weswegen beim Standard-Vorgehen der Reporting Services eine Kombobox mit allen Datumswerten erstellt.

Aber hier im Detail das Vorgehen:

Im Wizard zum Erstellen der Abfrage gehen wir wie folgt vor:

Wizard zum Erstellen der Abfrage an den Cube

(Zum Vergrößern auf das Bild klicken)

Ich habe einfach eine Kennzahl und das Datum in die Abfrage gezogen und einen Filter auf das Datum gesetzt, wobei ich (wichtig!) die beiden Checkboxen bei den Parametern gesetzt habe.

Bei der Berichtsausführung werden die beiden Parameter über Komboboxen realisiert:

Parameter From als Kombobox

Dazu hat nämlich der Wizard zwei versteckte Data Sets angelegt, die die entsprechenden Werte liefern:

Hidden Datasets

Als nächstes müssen wir das MDX anpassen. (Im Query Designer auf Design Mode klicken)

 

Der Wizard hat folgendes MDX erstellt:

SELECT NON EMPTY { [Measures].[Annahmen] } ON COLUMNS, NON EMPTY { ([Intervall].[Datum].[Datum].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@FromIntervallDatum, CONSTRAINED) : STRTOMEMBER(@ToIntervallDatum, CONSTRAINED) ) ON COLUMNS FROM [<CubeName>]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Die beiden mit @-benannten Ausdrücke sind die Prompts.

Wir müssen nun den Teil „STRTOMEMBER(@FromIntervallDatum, CONSTRAINED) “ so abändern, dass dort eine Variable vom Typ DateTime eine Rolle spielt. Der Schlüssel eines Datumselements in diesem Fall sieht so aus „[Intervall].[Datum].&[3190]“, wobei die ID die Anzahl der Tage zwischen dem 1.1.2000 und dem betreffenden Tag ist.

Gehen wir nun davon aus, dass wir einen Prompt @von vom Typ DateTime haben, müsste der STRTOMEMBER so aussehen:

STRTOMEMBER(„[Intervall].[Datum].&[“ +cstr( datediff(„d“, DateSerial(2000, 1, 1), @von )) + „]“)

Die DateSerial-Funktion verwende ich hier aus hygienischen Gründen, um keinen Cast von String auf DateTime (der abhängig von der Locale wäre) zu verwenden)

Auf das CONSTRAINED verwende ich, da sonst der Query Designer meckert.

Wenn man selbst natürlich einen anderen Key benutzt, muss dies angepasst werden. Bei einem Key im Format YYYYMMDD, also [Intervall].[Datum].&[20091205], müsste die Formel so sein:

STRTOMEMBER(„[Intervall].[Datum].&[“ +cstr( year(@von) ) + right(„0“ + cstr(month(@von), 2) + right(„0“ + cstr(day(@von), 2) + „]“)

Nun müssen wir noch den Parameter der Abfrage definieren (über Button Abfrage Parameter):

Parameter der Abfrage

Damit existiert noch nicht der Prompt.

Unter den Eigenschaften des Datasets erscheint der Parameter @von bereits:

Dataset Properties: Parameter

Dort tragen wir in „Parameter Value“ einfach „[@von]“ ein.

Damit wird automatisch ein Report Parameter von angelegt

Report Parameter von, den wir wie folgt bearbeiten: Wir setzen den Datentyp auf Datetime:

Bild

(Außerdem löschen wir den nicht mehr benötigten Prompt „FromIntervallDatum“)

Damit steht jetzt auch hier das Kalendersteuerelement zur Verfügung:

fertiges Kalendersteuerelement

Unerwartetes Nicht-Logging SSIS

Dieser Artikel widmet sich nicht dem Logging, das man selbst in einem Paket aktivieren kann und über Log-Provider detailliert steuern kann.

Gemeint ist mehr das Logging, das SSIS automatisch ohne Zutun des Entwicklers oder Administrators durchführt, s. dazu auch „Understanding Events Logged by an Integration Services Package„.

Standardmäßig loggt jedes SSIS-Paket den Start, das erfolgreiche Ende oder das Fehlschlagen oder Abbrechen eines SSIS-Pakets:

EventLog Start und Failure eines SSIS Pakets

(Zum Vergößern auf das Bild klicken)

Details des Eventlogs beim Scheitern eines Pakets

(Randbemerkung: Das Logging von Start und erfolgreichem Ende lässt sich abschalten – in der Registry HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server100SSIS, Wert von „LogPackageExecutionToEventLog“ auf 1 setzen – die anderen Einträge lassen sich nicht verhindern)

In dem Beispiel hatte ich ein SSIS-Paket erstellt, das nur aus einer Script-Task bestand, die immer einen Fehler schmeißt:

Script-Task zum Fehler schmeißen

Dort ist folgender Code enthalten:

public void Main()
{
Dts.Events.FireError(999, „SubKomponente A“, „Fehler – Test fehler“, „“, 0);
Dts.TaskResult = (int)ScriptResults.Success;
}

Das Ergebnis ist oben zu bewundern.

Nun hatten wir die Idee, dieses Eintrag im Event Log zu überwachen (mit NetIQ oder einem anderen Werkzeug zur Überwachung von Servern, Diensten, etc.) und so zu erfahren, wenn ein Paket gescheitert ist.

Das funktioniert aber leider nicht.

Der Grund ist, dass ein solcher Eintrag im Eventlog nur erzeugt wird, wenn das Paket bereits angelaufen ist. Wenn aber schon bei der Validierung ein Fehler auftritt, wird kein Eintrag im Eventlog erzeugt.

Dazu habe ich das Paket erweitert um einen Data Flow Task, bei dem ich in der Quelle „SELECT 1 as Zahl“ eingetragen habe. SSIS Paket mit Fehler brerits beim Validieren

Dieses Paket habe ich dann im SQL Server Agent als Job eingerichtet. Dabei habe ich eingestellt, dass im Fehlerfall der SQL Server Agent das Ergebnis im Eventlog festhalten soll:

Properties des SQL Server Agent Jobs zum Protokollieren im Event Log

(Zum Vergößern auf das Bild klicken)

Solange die Quelle des SSIS-Pakets bekannt ist, ist alles wie erwartet. Im Eventlog werden folgende 3 Events festgehalten (da ja mein Script-Task dazu führt, dass das Paket auf einen Fehler läuft):

Die Events, die durch ein Paket mit vorhandener Connection erzeugt werden

Die Meldungen sind:

  1. Starten des SSIS Pakets (Information)
  2. Fehler des SSIS Pakets (Fehler)
  3. Fehler des SQL Server Agent Jobs (Warnung)

Letzte Meldung sieht im Detail so aus:

Ereignis-Details der SQL Server Agent-Meldung, dass der Job nicht erfolgreich war

Wenn ich jetzt aber die Connection des Statements „SELECT 1“ ändere, so dass der dort angegebene Computer nicht existiert, passiert folgendes, wenn das Paket im SQL Server Agent gestartet wird:

Ereignisanzeige, wenn die Connection des SSIS Pakets nicht validiert werden kann

Man sieht, dass die SSIS-Eventlog-Einträge nicht vorhanden sind. Dies liegt daran, dass der Fehler beim Validieren auftritt und deshalb das Paket noch gar nicht angelaufen ist.

Deswegen macht es keinen Sinn, die SSIS-Eventlog-Einträge für eine Überwachung der Jobs zu verwenden.

Stattdessen können die SQL Server Agent-Einträge überwacht werden. Dabei ist aber zu beachten, dass dies bei jedem SQL Server Agent Job – wie oben gezeigt – eingestellt werden muss.