Alle Beiträge von Martin Cremer

MS Integration Services, MS Reporting Services, Projekte

Performanceprobleme bei SSAS-Dimensionsabfragen aus SSRS

Schreibe einen Kommentar

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.

MS Reporting Services

Säulendiagramme mit Datum auf der x-Achse

Schreibe einen Kommentar

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.

MS Analysis Services

Analysis Services Formatierungen (inkl. Excel-Bug bei $)

2 Kommentare

In Analysis Services können „echte“ Measures und berechnete Measures formatiert werden (sprich mit Tausender-Trenner, Nachkommastellen und sonstigen Bezeichnern wie cm oder € verschönert werden). Das erhöht die Lesbarkeit und verhindert Verwechslungen („Ist die Dauer in Stunden oder Minuten?“)

Als Formatstrings können die gängen Bezeichnungen verwendet werden:

  • , bedeutet einen Tausendertrenner
  • . bedeutet den Dezimalpunkt
  • 0 bedeutet, dass diese Stelle immer belegt ist
  • # bedeutet, dass diese Stelle angezeigt wird, falls nötig
  • In Anführungszeichen („) können beliebige Texte eingegeben werden

So ist #,##0.00 „€“ meine Standardformatierung in €, also zum Beispiel 17,33 € oder 1.522,12 € (auf deutschen Clients).

Interessanter Weise schlägt das BIDS auf deutschen Clients #.##0,00 vor. Das ist aber falsch und wird leider nicht zum Ziel führen.

Measure

Bei berechneten Measures gilt an sich das gleiche. Allerdings müssen die Strings mit “ umschlossen werden. Falls in dem Formatstring selbst ein Anführungszeichen enthalten ist, muss es verdoppelt werden (wie in der alten VB-Syntax).

Obiger Formatstring ist dann „#,##0.00 „“€“““

berechnetesMeasure

berechnetesMeasure2

Verwendet man Excel als Client, ist ein Bug in Excel zu beachten:

Ich gehe davon aus, dass das Excel ein deutsches Excel ist. Obiger Formatstring funktioniert dann wunderbar. Auch der Formatstring „#,##0.00 „“£“““ funktioniert wunderbar. Allerdings funktioniert „#,##0.00 „“$“““ nicht, statt dessen zeigt Excel € an. Ich erkläre mir das so: Diese Formatierung entspricht der Formatierung „Währung“, die auf deutschen Rechnern eben als € umgesetzt wird. Hier hilft vor oder hinter das Währungssymbol $ ein Leerzeichen einzufügen, also z.B. „#,##0.00″“ $“““

MS Integration Services, MS SQL Server

begin try in SQL-Batch-Statements

Ein Kommentar

Es kommt öfter vor, dass man im Batch mehrere SQL-Statements ausführen möchte, sei es in einer Stored Procedure oder im Execute SQL-Task von SSIS.

Meistens hat man folgende Anforderung:

Läuft ein Statement auf einen Fehler, soll ein Rollback der Statements gemacht werden. Außerdem soll natürlich dem aufrufenden System der Fehler gemeldet werden.

Lässt man einen Batch einfach so laufen, wird dieses Ziel nicht erreicht, da im Fehlerfall auch die Statements nach dem Statement, das den Fehler verursacht, ausgeführt werden.

Beispiel:

set nocount on
select 1
select 1/0
select 2

liefert:

———–
1

———–
Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.

———–
2

In Versionen vor SQL Server 2005 musste man die Error-Variable auslesen, etwa so:

set nocount on
declare @fehler as int
set @fehler = 0
select 1
set @fehler = @fehler + @@error
select 1/0
set @fehler = @fehler + @@error
select 2
set @fehler = @fehler + @@error
if @fehler>0 begin
print ‚Ein Fehler ist aufgetreten‘
end

was folgendes Ergebnis liefert:

———–
1

———–
Msg 8134, Level 16, State 1, Line 6
Divide by zero error encountered.

———–
2

Ein Fehler ist aufgetreten

Das Problem ist, man muss die Zeile „set @fehler = @fehler + @@error“ nach jedem Statement schreiben, da sie nach jedem (!) Statement zurückgesetzt wird.

Leichter geht das in SQL 2005 mit begin try … end try – angelehnt an Konstrukte aus Programmiersprachen wie C#:

set nocount on
begin try

select 1
select 1/0
select 2

end try
begin catch
print ‚Ein Fehler aufgetreten‘
end catch

Am Ergebnis

———–
1

———–

Ein Fehler aufgetreten

sieht man, dass nach dem fehlerhaften Statement die Bearbeitung beendet wird.

Nun fehlen nur noch 2 Anforderungen:

Dass keine Datenmanipulation statt findet, erreicht man über eine Transaktion, die im catch-Block zurückgerollt (rollback) wird.

Dass der Aufruf dennoch den Fehler mitbekommt, erreicht man über einen raiserror.

Das fertige Skript sieht dann so aus:

set nocount on

begin tran
begin try

/* hier die eigentlichen SQL-Statements schreiben */
select 1
select 1/0
select 2

end try
begin catch
if @@trancount > 0 begin
rollback tran
end
declare @fehler_text nvarchar(4000)
set @fehler_text = ERROR_MESSAGE()
declare @fehler_severity int
set @fehler_severity = ERROR_SEVERITY()
declare @fehler_state int
set @fehler_state = ERROR_STATE()
RAISERROR (@fehler_text, — Message text.
@fehler_severity, — Severity.
@fehler_state — State.
)

end catch
if @@trancount > 0 begin
commit tran
end

C#, MS Analysis Services

Measure-Meta-Informationen des Cube auslesen: AMO oder ADOMD

Schreibe einen Kommentar

Um an die Meta-Informationen des Cubes heranzukommen, gibt es mehrere Zugriffsmöglichkeiten, einmal mit AMO (Analysis Services Management Objects) oder ADOMD (das hauptsächlich für die Ausführung von MDX-Abfragen verwendet wird).

Beigefügt habe ich eine C#-Sollution, die über ADOMD eine CSV-Datei mit allen Measures erzeugt.

Wenn Zeit ist, werde ich diesen Artikel später noch erklären. Vorerst nur soviel:

Der Versuch mit ADOMD auf die Daten zuzugreifen, ist gescheitert, weil dort die berechneten Measures, die ich ebenfalls dokumentieren wollte, nicht einzeln abfragbar sind. Ich habe in einem Blogeintrag von letztem Oktober bereits beschrieben, wie man mit AMO an die MDX-Skripte herankommt. In diesen Skripten gibt es auch CREATE MEMBER-Skripte, die dann durch den Cube in berechnete Measures umgesetzt werden. Wenn man also AMO verwenden will, müsste man diese Skripte parsen (hierzu ein interessanter Blog-Eintrag auf geekswithblogs.net). Wenn man nur an dem Inhalt interessiert ist und die berechneten Measures nicht ändern will, kann man aber darauf getrost verzichten und – wie ich auch in der beigefügten Sollution gemacht habe – stattdessen ADOMD verwenden.

MS Analysis Services, MS Reporting Services

Berechnete Elemente in MDX-Abfragen mit Anwendung in Reporting Services 2005

Schreibe einen Kommentar

Lange habe ich gesucht, um ein sinnvolles Beispiel für ein berechnetes Dimensions-Element zu finden (berechnete Measures sind ja ständig zu finden).

Ein Beispiel ist eine Matrix in Reporting Services 2005 (in 2008 ist mit der Tablix ja alles (bzw. vieles) besser).

Ich habe über das Problem bereits zwei Artikel auf sqlservercentral.com veröffentlicht (Reporting Services: Adding extra columns / rows to a matrix und Reporting Services: Read Data from SSAS and SQL Server in One Dataset). Heute möchte ich das Problem auf eine andere Weise lösen.

Nehmen wir an, wir wollen in den Spalten alle Monate sehen und die Summe und einen Planwert und die Differenz zwischen Summe und Planwert. Wenn wir es jetzt schaffen, das ganze in einem MDX-Statement zu laden, so kann auch die Matrix von SQL Server Reporting Services 2005 das anzeigen.

Alle Monate in MDX anzuzeigen, ist einfach. Das Beispiel zeigt je Produkt und Monat den Verkaufswert an (ein konstruiertes, vereinfachtes Beispiel – ich habe sogar auf die Monatsnamen verzichtet und zeige die Monate im Format JJJJMM an):

select non empty [Datum].[Monat].[Monat].members on columns,
non empty [Produkt].[Produkt].[Produkt].members on rows
from [Verkaeufe]
where ([Measures].[Verkaufswert], [Datum].[Jahr].&[2010])

liefert:

Monate mal Produkte

Die Spalte Summe geht auch einfach. Dazu müssen wir den Alle-Member der Attribut-Hierarchie Monat anzeigen:

select non empty {[Datum].[Monat].[Monat].members, [Datum].[Monat].[All]} on columns,
non empty [Produkt].[Produkt].[Produkt].members on rows
from [Verkaeufe]
where ([Measures].[Verkaufswert], [Datum].[Jahr].&[2010])

liefert:

Monate inkl. Alle Mal Produkt

Für den Planwert konstruieren wir ein einfache Element der Attribut-Hierarchie Monat, das konstant 10 ist:

with member [Datum].[Monat].[Plan] as 10
select non empty {[Datum].[Monat].[Monat].members, [Datum].[Monat].[All], [Datum].[Monat].[Plan]} on columns,
non empty [Produkt].[Produkt].[Produkt].members on rows
from [Verkaeufe]
where ([Measures].[Verkaufswert], [Datum].[Jahr].&[2010])

liefert:

Monate inkl. Alle und Plan mal Produkte

(Die unschöne unbekannt-Zeile kann man natürlich leicht loswerden, aber das ist hier nicht unser Thema)

Das war noch eine leichte Übung. Aber in diesen Berechnungen können wir auch rechnen, wie in jedem MDX. Somit können wir einfach die Differenz anzeigen:

with member [Datum].[Monat].[Plan] as 10
member [Datum].[Monat].[Differenz] as [Datum].[Monat].[All] – [Datum].[Monat].[Plan]
select non empty {[Datum].[Monat].[Monat].members, [Datum].[Monat].[All], [Datum].[Monat].[Plan], [Datum].[Monat].[Differenz]} on columns,
non empty [Produkt].[Produkt].[Produkt].members on rows
from [Verkaeufe]
where ([Measures].[Verkaufswert], [Datum].[Jahr].&[2010])

liefert:

Monate inkl. Alle, Plan, Differenz Mal Produkte

Das finde ich ein sehr schönes Beispiel, wie man berechnete Dimensions-Elemente einsetzt.

Um das ganze in Reporting Services zu verwenden, muss man alles natürlich auf die Zeilen bringen und nur das Measure in den Spalten haben – aber das ist trivial.

Außerdem muss man in der Matrix noch die Sortierung lösen, aber über iif-Berechnungen in dem Sortierungsfeld ist das auch leicht zu lösen.

MS Integration Services

SELECTS beim Lookup dynamisch zusammenstellen

Ein Kommentar

Manchmal reicht es nicht aus, feste SQL-Statements als Quelle oder bei Lookups zu hinterlegen.

Bei einer OLE-DB-Quelle kann man ganz einfach das SQL-Statement in einer Variable ablegen.

Wie man das auch bei Lookups machen kann, zeigt dieser Eintrag.

Zunächst zur Motivation:

Man könnte sich vorstellen, dass innerhalb eines Datenflusstasks immer nur Daten eines Tages in eine Tabelle geschrieben werden. Der Tag steht dabei in einer Variablen. Nun soll bei jedem Lookup überprüft werden, ob der Primary Key der Daten an diesem Tag schon in einer Tabelle steht. Da der Lookup alle Daten cacht, macht es Sinn nur die Daten des Datums aus der Variablen zu lesen.

In unserem Beispiel heißt die Variable „Tag_deutsch“ und speichert das Datum als String mit deutschem Datumsformat.

Das Lookup-SQL müsste also so aussehen:

select * from ZahlenProTag
Where Tag = CONVERT(date, '7.5.2010', 104)

oder allgemein

select * from ZahlenProTag
Where Tag = CONVERT(date, '<Inhalt der Variablen Tag_deutsch>', 104)

Deswegen legen wir eine berechnete (EvaluateAsExpression:=true) Variable „Lookup-SQL“ an, mit der Expression:

"select * from ZahlenProTag
Where Tag = CONVERT(date, '" + @[User::Tag_deutsch] + "', 104)"

Jetzt bauen wir erst mal unseren Data Flow Task, noch mit dem kompletten Lookup auf ZahlenProTag:

BeispielDataFlow

Zu den einzelnen Komponenten:

  • alle Zahlen von 1 bis 10 habe ich bereits in meinem letzten Blogeintrag verwendet (s. https://www.csopro.de/biblog/2010/04/temporaere-tabellen-in-ssis-verwenden/). Es liefert alle Zahlen von 1 bis 10
  • Eine abgeleitete Spalte mit dem Namen Datum wird hinzugefügt, mit folgender Formel:
    (DT_DBDATE)@[User::Tag_deutsch]
    ACHTUNG. Dabei muss als LocaleID deutsch eingestellt sein, da der String ja in deutschem Datumsformat steht
  • Der Lookup sieht auf dem ersten Tab so aus:
    LookupAllgemein
    und so auf dem zweiten:
    LookupVerbindung
    und so auf dem dritten:
    LookupSpalten
    Man beachte, dass zum Lookup nur die Zahl und nicht das Datum verwendet wird.
    Letzteres könnte man natürlich machen. Dann würde aber die gesamte Tabelle in den Cache geladen werden, obwohl wir wissen, dass nur ein kleiner Bruchteil benötigt wird.
  • Das OLE-DB-Ziel ist wieder naheliegend:
    Tab 1:
    ZielVerbindung
    Tab 2:
    ZielZuordnungen

Soweit so gut.

Nun wollen wir das Lookup-SQL auf die oben angelegte Variable umstellen. Dazu gehen wir in die Ablaufsteuerung / Workflow und selektieren den Datenfluss-Task, so dass wir im Eigenschaftsfenster dessen Eigenschaften sehen.

Dort fällt auf, dass es unter Sonstiges eine Eigenschaft mit dem Titel [Zahl an diesem Tag schon da?].[SqlCommand] gibt:

SqlCommand

[Zahl an diesem Tag schon da?] ist ja der Name der Lookup-Komponente.

SqlCommand gibt das SQL an, das zum Befüllen des Caches verwendet wird (wir hatten ja den Standard Vollcache belassen)

SqlCommandParam würde man nur bei den anderen Cache-Typen benötigen.

Nun können wir über Expressions des Data Flow Tasks diese Eigenschaft überschreiben:

ExpressionsÜberschreiben

So jetzt geht’s!

Im Status bzw. Protokoll kann man kontrollieren, wieviele Zeilen der Lookup gecacht hat:

[Zahl an diesem Tag schon da? [13]] Informationen: ‚Komponente ‚Zahl an diesem Tag schon da?‘ (13)‘ hat insgesamt 0 Zeilen zwischengespeichert.

Diese Zeile kommt, wenn man die Variable auf ein neues Datum stellt.

Wenn die Variable auf einem bereits verwendeten Datum steht, kommt:

[Zahl an diesem Tag schon da? [13]] Informationen: ‚Komponente ‚Zahl an diesem Tag schon da?‘ (13)‘ hat insgesamt 10 Zeilen zwischengespeichert.

Also ist alles OK!

MS Integration Services

temporäre Tabellen in SSIS verwenden

2 Kommentare

Für manche Aufgaben erscheint es sinnvoll, Daten zunächst in temporäre Tabellen zu übertragen, um sie dann zum Beispiel per Insert oder Update in die Zieltabelle zu überführen (damit kann man ein „Bulk Update“ durchführen).

Dazu kann man natürlich im SQL-Server beliebige Tabellen anlegen, die dann nur „logisch“ temporär sind, da sie ja ständig in der Datenbank sind.

Der SQL Server bietet aber auch temporäre Tabellen an, die sessionweit (# – sobald die Connection geschlossen wird, verschwindet die Tabelle) oder global (## – sobald die letzte Connection geschlossen wird, die diese Tabelle verwendet, verschwindet sie) gelten.

Diese in SSIS zu verwenden, mag z.B. Sinn machen, wenn man keine Tabellen-Erstellungs-Rechte auf dem Zielserver hat.

Sie zu verwenden, ist nicht ganz einfach:

Nehmen wir an, wir wollen eine temporäre Tabelle mit einer einzigen numerischen Spalte anlegen, so geht das so:

CREATE TABLE [dbo].[#ZielTabelle]
(
[zahl] [int] NOT NULL
)

Randbemerkung:
Man kann auch Primary Keys vergeben, z.B. mit folgendem Statement:

CREATE TABLE [dbo].[#ZielTabelle]
(
[zahl] [int] NOT NULL,
CONSTRAINT ZielTabelle_PK PRIMARY KEY CLUSTERED
(
[zahl] ASC
)
)

Das wäre aber nicht so gut, da dann ein Vorteil verloren geht: Eine solche temporäre Tabelle (#) kann mehrfach existieren (je Connection einmal). Verwendet man aber einen festen Primary-Key-Namen, so geht das nicht mehr. Man könnte in den Namen des Primary Key eine GUID einbauen und hätte wieder Eindeutigkeit.

Dieses Create-Table-Statement baut man in eine Execute-SQL-Task und kann dann folgenden Workflow abbilden:

Workflow

Den DROP TABLE braucht man nicht unbedingt, da nach Schließen der Connection die Tabelle eh verschwindet. Aber aus Gründen der Hygiene empfehle ich es.

Unser Datenflusstask soll im Beispiel einfach alle Zahlen von 1 bis 10 in die temporäre Tabelle schreiben:

DataFlow

Die Skript-Komponente hat folgenden Code:

public override void CreateNewOutputRows()
{
for (int i = 1; i <= 10; i++)
{
AusgabeBuffer.AddRow();
AusgabeBuffer.Zahl = i;
}
}

Nun haben wir aber ein Problem: Die temp-Tabelle taucht nicht in der Liste der Tabellen auf. Deswegen empfiehlt es sich, auf dem Entwicklungsrechner eine Tabelle mit der selben Struktur anzulegen, und diese dann auszuwählen (hier „ZielTab_gibtsNetProduktiv“):

Ziel

Nun kann man auf dem nächsten Reiter die Zuordnungen – wie gewohnt – einstellen (was in diesem Beispiel sehr einfach ist 🙂 ):

Zuordnungen

Noch haben wir aber nichts gewonnen, da die temporäre Tabelle nicht verwendet wird.

Es müssen deshalb noch ein paar Einstellungen vorgenommen werden:

  • Unter Eigenschaften des OLEDB-Ziels muss man in OpenRowset die temporäre Tabelle eintragen:
    Vorher:
    nachher
    Nachher:
    nachher
  • Die weitere Eigenschaft „ValidateExternalMetaData“ des OLEDB-Ziels muss auf „false“ gestellt, da zum Zeitpunkt der Entwicklung bzw. des Starten des Pakets die temporäre Tabelle noch nicht existiert.

Jetzt funktioniert das Paket immernoch nicht. Es tritt folgender Fehler auf:

[Speichern in temp Tabelle [16]] Fehler: Fehler beim Öffnen eines FastLoad-Rowsets für ‚#ZielTabelle‘. Überprüfen Sie, ob das Objekt in der Datenbank vorhanden ist.

Offensichtlich kennt der DataFlow Task die Tabelle nicht. Dies liegt daran, dass der CREATE TABLE-Befehl und der Datenfluss-Task nicht in der selben Connection ausgeführt werden. Deswegen müssen wir noch die Eigenschaft der Connection „RetainSameConnection“ auf true setzen:

RetainSameConnection

Jetzt funktioniert es.

Eine Anmerkung:
Man kann die Zuordnungen des OLEDB-Ziels nicht mehr mit dem normalen Editor bearbeiten, da zum Design-Zeitpunkt die temporäre Tabelle nicht existiert. Man kann das durch das oben gezeigte Umstellen auf eine nur auf dem Entwicklungsserver existierende Tabelle umgehen.