Reporting environment: Best practices

[Dejan Vu]

Hi,
Ich habe heute ein neues Projekt übernommen: Eine Report-Sammlung für die SQL-Server Reporting Services von Microsoft.

Ich musste erst einmal meinen Schädel sehr kräftig gegen die Wand hämmern, denn der eigentlich geniale SQL-Programmierer hat alle Queries als ein einziges SELECT Statement, implementiert. Die Reports sind ziemlich komplex und damit auch die Queries. Eine Query sieht ungefähr so aus:

Code:

SELECT ....
  FROM (SELECT ... FROM 
    (SELECT ... FROM (
        SELECT * FROM Table JOIN foo...
         ) xy
      ) y
    )
   ) X JOIN (<der gleiche dreck wie oben>)

Also SELECTS in SELECTS in SELECTS. Feldlisten mit ca. 20-30 Einträgen, einige Felder wieder mit SELECT und CASE und WHEN und -alter Schwede-

So, eigentlich muss ich erstmal aufräumen, nur wie?

Ich habe das eigentlich immer so gemacht, das ich pro Report eine SP geschrieben habe (oder eine UDF, ist egal)
alle Parameter des Reports sind Parameter der SP/UDF.

Subselects werden vorher in temporäre Tabellen geschrieben oder als View abgebildet, wenn sie wiederverwenden werden können etc. Also DRY-Prinzip anwenden.

Ich hab mich nie drum gekümmert, wie echte Profis das machen, daher meine Frage: Wie macht ihr das? Oder: Gibt es überhaupt jemanden, der Erfahrung mit einem reinen SQL-Reporting Toolset hat (Views? UDF? etc.)

1. Schreibt ihr die Queries als Script im Reportgenerator oder -so wie ich- komplett als UDF/SP?
2. Verwendet ihr Aggregatfunktionen des Reporting-Tools (SSRS, Fast-Report etc.) oder bildet ihr alles in Queries ab?
3. Wo platziert ihr eventuelle Übersetzungen?
4. Wie würdet ihr das DRY-Prinzip im RDBMS umsetzen?

[DSP]

Ist eigentlich schon ein wenig off topic, aber hab gerade gelesen, dass ihr alle Statements nicht zu dokumentieren braucht, da sie alle selbstsprechend sind und im Team diskutiert wurden ...

... gibt's da keine Dokumentation, wo du nachschauen kannst, was er denn wollte?

[Dejan Vu]

Selbst zu Dir (obwohl es vermutlich ein sehr weiter und beschwerlicher Weg ist) sollte es sich herumgesprochen haben, das eine API in jedem Fall zu dokumentieren ist. Eine Reporting-Schnittstelle sowie die zugrundeliegenden Tabellen ist eine API, weswegen sich dein OT-Geschwätzes schon erübrigt.

Wenn Du aber eine Möglichkeit siehst, die gängigen Coding-Prinzipien (die ein Kommentieren von Code überflüssig machen) auf SQL zu übertragen, bist Du herzlich eingeladen, hier einen Beitrag zu posten.

Und wenn Du etwas Erhellendes zum Thema 'Code kommentieren?' beizutragen hast, dann kram den Thread heraus und poste dort etwas Nützliches. Ich bezweifle, das Dir das gelingt, aber es geschehen immer wieder Zeichen und Wunder

Es geht auch nicht darum, das ich nicht verstehe, was der Programmierer bezweckt (lesen können wir hier und fragen auch, denn er sitzt mir gegenüber), sondern wie man eine Reporting-API sinnvollerweise strukturiert.

Ich habe einen Plan, den ich auch beschrieben habe, aber hier wenig Erfahrung, wie andere es machen.

[sx2008]

Zitat von Dejan Vu:

... denn der eigentlich geniale SQL-Programmierer hat alle Queries als ein einziges SELECT Statement, implementiert.

Alle Reportingtools die ich kenne arbeiten mit mehreren Queries um die Daten einzusammeln und aufzubereiten.
Das Flachklopfen aller relationalen Beziehungen zu einer großen und sehr breiten Abfrage ist sicher nicht der richtige Weg.
Du kannst auch neue Views einführen um zumindest in kleinerem Rahmen die SQL-Abfrage zu vereinfachen.
Wie du ja schon mit <der gleiche Dreck wie oben> bemerkt hast enthalten große SQL-Abfragen sehr häufig Teile die an anderen Stellen schon benützt wurden.

[Dejan Vu]

Ich vergaß zu erwähnen, das es sich um eine Reporting-DB handelt. Täglich werden die Daten aus der Produktiv-DB in die Reporting-DB übernommen. Die Produktiv-DB besteht nur aus XML-Records und im ersten Schritt wurde eigentlich nur die XML-Struktur in der Reporting-DB abgebildet. Jeden Abend werden alle Änderungen in die Reporting-DB übernommen (quasi ETL)

Die redundanten Subselects werde ich vermutlich (zunächst) in indexed Views auslagern. Dann habe ich eine direkte Kontrolle über den Inhalt und kann diesen -speziell während der Designzeit- verändern und erweitern. Nachteil an den Indexed Views ist aber, das die verwendeten Tabellen alle im gleichen Tablespace liegen müssen und verwendete Views ebenfalls mit Schemabinding (also als indexed Views) kompiliert sein müssen. Indexed Views sind also viral.

Ich glaube, wir machen das über Indexed Views, und ersetzen diese später durch Tabellen gleichen Namens. Aber sicher bin ich mir noch nicht.

Bezüglich des Report-Codes (die riesigen SELECTs, die als Skript in der Report-Definition RDL lagern) sind wir uns eigentlich einig, das sie in eine SP gehören, aber bei der Versionierung hapert es noch. Es wäre blöd, wenn der Report für eine Query die Version 1.23 erwartet, aber auf dem Server die SP in Version 1.21 vorliegt. Das muss unterbunden werden. Mal sehen, was uns da einfällt (Versionsnummer in SP und Vergleich o.ä.).

[p80286]

Zitat von Dejan Vu:

..hat alle Queries als ein einziges SELECT Statement, implementiert. Die Reports sind ziemlich komplex und damit auch die Queries

Aus mir unerfindlichen Gründen gibt es da draußen wohl mehrere Leute, die so arbeiten.
U.U. liegt das daran das ein Formular/Statistik/was_auch_immer bestellt wird und auch genau das geliefert wird.
Von Wiederverwertbarkeit hat niemand gesprochen.

Ich persönlich halte nicht viel von SP (wenn ich sie nicht selbst geschrieben habe), hingegen nutze ich Views wann immer es geht, nicht nur um Tabellen zusammen zu fassen, auch um Daten zu trennen z.B. die Personentabelle in Lieferant und Kunden. Das ist zwar nicht die reine Lehre, aber meist einfacher zu handhaben.

Gruß
K-H

[Dejan Vu]

[QUOTE=p80286;1268303..hingegen nutze ich Views wann immer es geht[/QUOTE] Richtig, ich tendiere auch dazu, zumal diese VIEW (z.B. ein Report) auch als Grundlage für weitere Reports dienen kann. Daher vielleicht eher UDF statt SP.

Aber: In einer View kann ich kein Skript unterbringen, d.h. temporäre Tabellen erstellen und übersichtlicher programmieren. Daher bleibt nur die SP/UDF.

[jobo]

Zu Sql Server Spezifika kann ich nichts sagen.

Allgemein:
Jede Tabelle wird per se als View gewraped.Aus dem Report also kein Zugriff direkt auf Tabellen.
Wiederverwendbare Teilmengen werden als eigenständige Views aufgebaut.
„Core“ Informationen werden in „weiten“ Views (Breite & Tiefe) abgelegt (so weit/tief wie nötig).

Reports (+ Varianten) werden nach Möglichkeit auf identische Core Views aufgesetzt, deren Output dann reportspezifisch beschnitten wird.
Core Views gibt es ggF. in mehreren Hierarchiestufen (Aggregationsebenen oder was auch immer, die nicht idealer Weise nicht vermischt werden sollten bzw. erneut rauf/runtergerechnet werden)
Ein „riesen SQL“ finde ich nicht dramatisch, wenn der Report auf nur eine Datenmenge anzeigt. Ist es entsprechend der Punkte oben in sinnvolle Views gegliedert, dann hat man idR eine gute Übersichtlichkeit.
Der weitgehende Einsatz von Views erlaubt natürlich auch, den Report Content relativ bequem zu ändern, ohne den Report selbst anzufassen. So kann man z.B. das zugrundeliegende Datenmodel abändern, ohne die Reports anfassen zu müssen.
Die Notwendigkeit von Temptables habe ich noch nie gesehen / gehabt, alles per View. In dem Zusammenhang: Da es sich ja um eine Reporting DB handelt, böte sich hier im Rahmen des ETL noch die Möglichkeit, von Beginn an abzuspecken und wie der Name sagt zu transformieren.

UDF/SP:
Ich verwende in Views gerne Session Variables (als Parameter), das ist in der Form glaub ich Oracle spezifisch. Ähnliches lässt sich vermutlich per SP / UDF erreichen. Zusätzlich zur normalen View Verwendung, der „von außen“ auf die gewünschten Parameter eingeschränkt wird, hat man die Möglichkeit Parameter „innen“- also in der Viewdefinition-einzusetzen, sofern die entsprechende Entität nicht dargestellt werden muss (oder auch wenn sie dargestellt werden muss, aber einfach eine riesen Selektivität hat).

Indexed Views:
Kenne ich aus Oracle nicht, hier gibt es materialized Views, die man indizieren kann (und die eigentlich keine Views mehr sind). Würde ich bei einer Reporting DB aber auch nicht mit loslegen, erst wenn es echte Performanceprobleme gibt und das ETL Ergebnis nicht weiter verschlankt werden kann.

[Dejan Vu]

So ähnlich habe ich mir das gedacht. Ob bei guten 'Core'-Tabellen (oder Dimensionen) und Fakten noch temporäre Tabellen notwendig sind, wird man sehen. Wenn nicht, sind die SP obsolet. Obgleich ich es charmant finde, jeden Report in einer UDF abzubilden, denn dann bin ich eigentlich auch vom SSRS / Reporting Front-End unabhängig.

[jobo]

noch was vergessen:
Bestehende Reports sind manchmal nicht unbedingt sinnvoll gegeneinander aufgebaut/abgestimmt. Der Kunde wollte es zwar so, weil er "genau das braucht", sie liefern aber nicht unbedingt Ergebnisse, die eine sinnvolle Plausibilisierung untereinander erlauben. Das ist den Kunden manchmal nicht bewusst, und kann bei der Umsetzung des Models schon mal aufs Glatteis führen.

Abgesehen von dem zuvor beschriebenen Vorgehen, finde es hilfreich, so zu bauen, dass man die Ergebnisse der Reports untereinander vergleichbar ausspuckt. Auch wenn der ein oder andere Bereich gar nicht gefragt ist.