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

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.

SELECTS beim Lookup dynamisch zusammenstellen

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. http://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!

temporäre Tabellen in SSIS verwenden

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.

Snowflake-Schema-Dimensionen mit NULL-Values konfigurieren

Eine Möglichkeit, eine Dimension relational abzubilden, ist ein Snowflake-Schema. In diesem Schema werden Hierarchien so abgebildet, dass jede Hierarchiestufe eine eigene Tabelle besitzt, die durch Foreign Keys verbunden sind.

Probleme können auftreten, wenn nicht bei allen Dimensionselementen alle Ebenen vorhanden sind und deshalb manche Elemente in der Datenbank NULL sind. Diese können über das Setzen der Eigenschaft „NULL Processing“ gelöst werden. Fangen wir aber vorne an.

Ein Beispiel für eine Dimension im Snowflake-Schema seien die Hierarchien Produkt > Warengruppe > Strategisches Geschäftsfeld (SGF) und Produkt > Produktionskennzeichen (hier ein Screenshot des Data Source View):

Data Source View Produkt Dimension

Daraus lässt sich einfach eine Dimension bauen:

Dimension Produkt mit Hierarchien
(Zum Vergrößern Bild anklicken)

Die einzelnen Attribute sind so definiert: Die Key-Column ist jeweils die ID-Spalte (also zum Beispiel id aus der Tabelle dim_SGF), die Name-Column jeweils die zugehörige Bezeichnung (z.B. die Tabelle SGFBezeichnung der Tabelle dim_SGF).

In meinem Beispiel kann die Produktionskennzeichen_ID der dim_Produkt NULL sein.

Das hätte jetzt zur Folge, dass – Stand jetzt – die Dimension nicht aufbereitet werden kann:

Fehler beim aufbereiten der Dimension
(Zum Vergrößern Bild anklicken)

Die Fehlermeldung im Detail:

Fehler im OLAP-Speichermodul: Der Attributschlüssel wurde bei der Verarbeitung nicht gefunden: Tabelle: dbo_dim_Produktionskennzeichen, Spalte: id, Wert: 0. Das Attribut ist ‚Produktionskennzeichen‘. Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten des Produkt-Attributs der Produkt-Dimension aus der SimpleCube-Datenbank.

Offensichtlich hat der SSAS den NULL-Wert als 0 interpretiert, was er natürlich nicht finden kann.

Die Lösung ist:

  • Definieren des Unknown-Members der Dimension als sichtbar mit der Bezeichnung „unbekannt“ (oder wie auch immer)
  • Definieren der Nullprocessing-Eigenschaft der Key Column von Produktionskennzeichen auf „ConvertToUnknown“:
    Null Processing bei der Key Coulmn eines Attributs

Dann funktioniert die Aufbereitung und die Dimensionen sehen in meinem Beispiel so aus (die Zahlen sind irgendwelche Artikelnummern 🙂 ):

Hierarchie SGF:
Hierarchie SGF

Hierarchie Produktionskennzeichen:
Hierarchie Produktionskennzeichen

SSRS: Darstellung von multi-valued Parametern im Report

In Berichten ist es eine gute Angewohnheit, die durch den Benutzer ausgewählten Parameter im Bericht anzuzeigen.

Bei Parametern, die dem Benutzer nur einen Wert einzugeben, ist das einfach, da dieser Ausdruck im Ausdruck-Editor einfach zusammengeklickt werden kann, zu z.B.

=“Eingegebene Zahl: “ & Parameters!EinfacherParam.Value

Parameter mit einem Wert

(Zum Vergrößern aufs Bild klicken)

Wenn es sich aber um einen Multi-Value Parameter (wie in meinem Beispiel der Parameter „Wochentag“) handelt, dann erzeugt der Ausdruck-Editor … Parameters!Wochentag.Value(0), was den Value (das ist der Schlüssel) ersten ausgewählten Wochentag ausgibt.

Da mir aber etwas wie „Sie haben folgende Wochentage ausgewähtl: Montag, Dienstag, …“ vorschwebt, müssen wir zunächst den Label anzeigen lassen und nicht den Schlüssel. Das geht über Parameters!Wochentag.Label(0).

Nun wollen wir aber alle ausgewählten Parameterwerte sehen und nicht nur den ersten. Da es sich bei Parameters!Wochentag.Label offensichtlich um ein Array handelt (Array startet mit Position 0), kann man über die Join-Funktion die einzelnen Werte konkatenieren.

Dazu kann man folgenden Code im Ausdruck-Fenster eingeben:

=“Alle (via Ausdruck): “ &
Join(Parameters!Wochentag.Label, „, „)

Alle Params via Ausdruck

Damit endet die Möglichkeit im Ausdruck-Fenster.

Will man weitere Funktionalität einbauen, wie z.B. die Liste 1-Montag, 2-Dienstag, …, so kann man das dadurch erreichen, dass man im Code-Fenster auf die Parameter und ihre Werte zugreifen kann:

Code eingeben

(Zum Vergößern Bild anklicken)

Das Code-Fenster erreicht man über Bericht > Berichsteigenschaften > Code.

Dort habe ich folgenden Code eingefügt:

function ParameterwerteVerkettet(ParamName as string)
dim i as integer
dim erg as string

for i = 1 to Report.Parameters(ParamName).Count
erg = erg & Report.Parameters(ParamName).Value(i-1) & „: „
erg = erg & Report.Parameters(ParamName).Label(i-1)
if i<> Report.Parameters(ParamName).Count Then erg = erg & „; „
next i

ParameterwerteVerkettet=erg
end function

Wie man sieht, kann man einfach in dem VB-Code auf die Parameter-Arrays Value und Label zugreifen.

Diese Funktion ruft man dann im Ausdruck wie folgt auf:

Ausdruck via Code

Mein fertiger Bericht sieht dann wie folgt aus:

fertiger Report

Das RDL kann man hier runterladen: www.csopro.de/blogdateien/ParamsAnzeigen.rdl

Noch einige weiterführende Hinweise:

Nachinstallieren von Visual Studio 2008 Komponenten schlägt fehl

Bei einem Kunden mussten DLLs in VB.NET und nicht C# erstellt werden, weswegen ich bei meiner Visual Studio Installation VB.NET hinzufügen wollte.

Leider scheiterte das, sowohl über Systemsteuerung > Software > Visual Studio 2008 > Ändern als auch beim Starten von der Visual Studio DVD.

Jeweils scheiterte bereits das Hochfahren der Setup-Komponente.

Am Anfang werden ja etliche Sachen in der Setup-Komponente geladen und getestet und mitten dabei brach das Setup ab, so dass ich nicht einmal zur Auswahl der Komponenten gelangte.

Glücklicherweise fand ich den Link http://blogs.msdn.com/heaths/archive/2008/10/06/adding-features-to-visual-studio-2008-may-fail-to-load-setup-components.aspx, der genau dieses Problem beschreibt.

Man kann entweder alle Patches von Visual Studio 2008 entfernen oder am besten Visual Studio 2008 Service Pack 1 installieren.

Migration SSAS 2005 > 2008: Class not registered – Fehler

Wenn man eine SSAS-Datenbank von SQL 2005 auf SQL Server Analysis Services 2008 migriert, taucht gerne folgender Fehler auf, der aber eine nicht auf Anhieb durchschaubare Fehlermeldung hat:

Die Migration geht an sich gut. Hat man die SSAS-Datenbank dann aber auf den neuen Server mit SSAS 2008 deployt, so bereitet die Datenbank nicht auf, sondern scheitert mit folgendem Fehler:

Class not registered beim Aufbereiten
(Zum Vergrößern auf das Bild klicken)

Der Fehler tritt nur auf, wenn die Daten auf dem SQL Server liegen und nach der Migration auf dem Zielserver keine SQL Server 2005-Installation mehr vorhanden ist.

Ursache ist, dass in der Data Source als OLEDB Treiber der SQL Server Native Client in der Version 2005 angegeben ist, aber auf dem neuen Zielserver nicht mehr installiert ist.

Alte Verbindung mit 2005er Treiber

Man kann einfach den Treiber auf die aktuelle Version 2008 (10.0) ändern. Dann funktioniert wieder alles (egal ob die zugrundeliegende Datenbank noch SQL Server 2005 ist oder auch auf SQL 2008 migriert wurde):

Verwendung des neuen Treibers von SQL 2008

Verhalten von Views bei Strukturänderungen der zugrundeliegenden Tabellen

Ein View, der mittels * alle Spalten einer verwendeten Tabelle zurückgibt, wie z.B.

CREATE VIEW dbo.testView
AS
SELECT u.*, k.name as Kunden_Name, k.AnzahlKinder
FROM umsatz u
INNER JOIN kunde k
ON k.kunde_id = u.kunde_id

verhält sich überaschend, wenn sich die Tabellenstruktur der zugrunde liegenden Tabellen ändert. Die Metainformationen aktualisieren sich nämlich nicht automatisch, was sogar zu Datentypsverletzungen führen kann.

Nehmen wir an, die Tabelle umsatz aus obigen Beispiel habe folgenden Inhalt:

Inhalt der Tabelle Umsatz

Damit liefert die Abfrage des Views

SELECT * FROM testView

folgende Daten:

Inhalt des TestView original

Wird nun in der Tabelle umsatz eine Spalte Rabatt hinzugefügt (und mit Werten gefüllt), liefert das SELECT überaschender Weise:

nach Hinzunahme der Spalte Inhalt des Views

Dabei fällt auf, dass die Spaltentitel die ursprünglichen Inhalte haben, d.h. Rabatt ist nirgends zu sehen, die Werte der Spalten sind aber falsch: In der Spalte Kundenname steht nun der Rabatt und im Feld AnzahlKinder (Datentyp ist eigentlich int!) steht nun der Kundenname.

Das Aktualisieren der Views geht einfach: Nach

exec sp_refreshview 'testview'

sieht wieder alles richtig aus.

Hat man mehrere Views, in einer Datenbank, kann man über

select 'exec sp_refreshview ''' + name + '''' from sysobjects
where xtype = 'V'

sich die notwendigen SQL-Statements automatisch generieren lassen, die man dann einfach ausführt.

Einige Hinweise:

  • Views können natürlich verschachtelt sein, d.h. ein View greift auf einen anderen View zu. Dann müssen die Refresh-View-Statements in der richtigen Reihenfolge oder einfach mehrfach ausgeführt werden.
  • Clustered / Indexed Views können nicht refresht werden – das Statement liefert einen Fehler und muss aus der Liste der auszuführenden Statements gelöscht werden. Das Aktualisieren eines indizierten Views ist aber auch unnötig, da die zugrunde liegenden Tabellen nicht geändert werden können (da der View mit Schema Binding angelegt wurde)

Meine Erfahrungen in der Business Intelligence Welt