SQL - Materialized Query Tables

[Fuerchau]

Da hast du sicherlich recht, es ging aber nicht darum, auch noch die UDF's mit skalaren SQL-Funktionen zu ersetzen.
Und meine "Analyse" bezog sich nun mal ausschließlich auf die Aussagen von KM.
Eine Analyse der internen UDF's ist für diese Aufgabenstellung nicht erforderlich da dies die Komplexität der Aufgabenstellung sprengt.

Wenn schon mal die Frage nach Performance gestellt wird verlangt ja auch keiner ein Redesign des Datenbankmodells.

[KM]

Danke schon mal für die zahlreichen Anworten. Hier sind meine Anmerkungen dazu:

Ein Live-Update der Daten ist für unsere Zwecke nicht nötig und ist auch nicht unbedingt der Sinn eines BI-Systems. Ein abgeschlossener Zeitraum (z.B. bis einschließlich Vortag) ist völlig ausreichend. Und so wie ich die MQTs auf der AS/400 verstanden habe, gibt es auch gar keinen "REFRESH IMMEDIATE" bzw. "MAINTAINED BY SYSTEM". Ich würde hierfür einfach Planungsjobs einbauen, die dann komfortabel den "Refresh Table" auf die MQTs machen.

Ich habe gestern dann noch herausgefunden, dass diese ganzen Einschränkungen bei den Abfragen in der MQT nur zutreffen, wenn man "ENABLE QUERY OPTIMIZATION" verwendet. Ich habe dann mal auf "DISABLE" umgestellt und konnte meine MQT problemlos erstellen. Da wir die MQTs auch nicht in weiteren komplexen Abfragen verwenden, sondern komplett so wie sie sind ins BI-System laden, haben wir auch keine Performance-Einbußen.

Ich hab das jetzt mal getestet. Die ursprüngliche SQL-Abfrage (mit den UDFs) dauert in meinem Beispiel ca. 70 Sekunden. Gerade die UDFs sind es, die für die lange Laufzeit verantwortlich sind. Allerdings können wir die schlecht umstrukturieren, denn sie sind recht komplex. Deshalb haben wir sie ja in UDF ausgelagert. Nachdem ich die Daten in eine MQT geladen habe, dauert die Abfrage derselben Daten aus der MQT nur noch 0,3 Sekunden. Ich glaube ehrlich gesagt nicht, dass ich durch diverse Umstrukturierungen auf diese Antwortzeiten komme. Da finde ich es schon sinnvoller diese Daten in MQTs auszugliedern.

@Fuerchau: Was genau meinst Du mit "echtem BI-System"? Etwa die großen (Cognos, Hyperion, SAS, Board, etc.), die preislich im hohen 5-stelligen oder 6-stelligen Bereich liegen? Das kommt für uns sicher nicht in Frage. Ich verwende ja ein "echtes" BI-System und das kostet gar nix bzw. nur Kleinbeträge. Und was ich so in der ersten Testphase herausgefunden habe, ist es sehr mächtig und für unsere Zwecke völlig ausreichend.

Viele Grüße,
KM

[andreaspr@aon.at]

Wenn du noch weiter Tunen willst, kannst du über die MQTs dann auch noch Indice anlegen.

[Fuerchau]

Rufe mich gerne dazu an, wir bewegen uns da im 4-stelligen bis max. unteren 5-stelligen Bereich.
Meine Erfahrung ist einfach, dass die kostenlosen BI's nicht wirklich was können.
Die Anforderungen sind z.B. Integration von Daten verschiedener Quellen in einem Bericht mit gleichzeitigem Anzeigen der Bezeichnungen zu den vorhanden Schlüsseln.
Das schaffe ich auch nicht in in Qlikview und schon gar nicht in BIRT.

Ach ja, und MQT werden bei uns auch nicht benötigt.

[KM]

Meine Erfahrung ist einfach, dass die kostenlosen BI's nicht wirklich was können.

OK, dann schau Dir doch das neue Power BI von Microsoft an und vergleiche mal was Dein Tool kann und Power BI nicht. Vergleiche aber auch was Power BI kann und Dein Tool nicht ;-)

Ach ja, und MQT werden bei uns auch nicht benötigt.

Und wie kommst Du dann performant an die Daten? Und zwar genauso performant wie mit MQTs?

[Fuerchau]

Nun, das Thema ist hier eigentlich falsch, aber was solls:
Power BI kostenlos:
Max. 1GB / User, max. 10.000 Datensätze / Stunde
Power BI 10$ je User und Monat:
Max. 10GB / User, max. 1.000.000 Datensätze / Stunde
Generell:
Importieren von Daten und Berichten aus Excel-, CSV- und Power BI-Desktop-Dateien

Das verfügbare Volumen je User ist schon etwas seltsam und die Datenbegrenzung ebenso.
Nehme ich also einen Umsatzbericht der aus der AS/400 500.000 Zeilen benötigt, kann ich den ggf. in der kostenlosen Version gar nicht ansehen?
Oder muss ich da 5 Stunden auf die Daten warten?

Die wesentliche Einschränkung ist aber der Import auf wenige Formate.
Wie sieht es mit anderen Datenquellen aus (Oracle, Sybase, Access, SQL-Server)?

Auch hier sieht es i.W. so aus, dass die Daten für den Bericht jedes mal wieder neu aus dem Quellsystem geladen werden. Dies macht BIRT, Web/Query und Qlikview ja genauso.
Kann ein Bericht Daten aus unterschiedlichen Quellen verknüpfen?

Beispiel: Ich habe den Umsatz auf der AS/400 und die Umsatzplanung in Excel. Kann ich nun einen Vergleich zwischen diesen Daten in einem Bericht darstellen?

Wie sieht es mit der Drilldown-Funktion aus?
Findet die innerhalb des Berichts statt oder muss ich hier Sub-Berichte erstellen?

Kann ich das Ganze im Batch auf einem Server machen und die Berichtsausgabe per Mail versenden?
Es gibt da schon sehr viele Fragen und Anforderungen an ein "echtes" BI-System.

Und warum keine MQT'?
Ganz einfach:
Ein "vernünftiges" BI lädt die Daten in eine eigene Datenbank.
Begründung:
- lokale DB-Größe bis die Platte platzt
- Anzahl User unbeschränkt
- Konsolidierung, also zusammenführung, von Daten aus verschiedenen Quellen
- Strukturierung und Anpassen der Daten, insbesonders der Datentypen bei verschiedenen Quellen
- Performante Abfrage der Daten unabhängig vom Hostsystem
- u.v.m.
D.h., die MQT's mit den täglichen Updates werden nicht benötigt, da man nur die neuen Daten mit simplem SQL (also incl. der UDF's) importiert.
Da nur aktualisiert, also ergänzt wird, ist die Abfragedauer nicht so entscheidend.
Deine Abfrage von 70 Sekunden ließe sich im Batch als Import also durchaus alle 5 Minuten durchführen um die Daten noch aktueller zu halten.

Beispiel:
Ein Kunde ist zum 1.1.2014 von MAS90 auf SAP umgestiegen.
Nun befinden sich die Umsätze vor 2014 in MAS90 und danach in SAP.
Mit einem "echten" BI kannst du diese Daten nun halt gemeinsam auswerten.

"Ich habe gestern dann noch herausgefunden, dass diese ganzen Einschränkungen bei den Abfragen in der MQT nur zutreffen, wenn man "ENABLE QUERY OPTIMIZATION" verwendet. Ich habe dann mal auf "DISABLE" umgestellt und konnte meine MQT problemlos erstellen. Da wir die MQTs auch nicht in weiteren komplexen Abfragen verwenden, sondern komplett so wie sie sind ins BI-System laden, haben wir auch keine Performance-Einbußen."
Diese Antwort hätte ich bzgl. deines Problems eigentlich von Birgitta erwartet, dann wäre der Thread allerdings nicht so interessant geworden.

[andreaspr@aon.at]

Um auch noch meinen Senf dazu zugeben:
Die Argumentation warum NICHT MQTs sind für mich jetzt keine "Wow"-Argumente. Vor allem da sie teilweise auch nichts mit MQTs zu tun haben.
Ich kann ja MQTs am Server erstellen und im BI mit den zusammengefassten Daten weiter arbeiten.
Ob ich die Logik jetzt am Server oder in die BI verlagere ist für mich da mehr Geschmackssache bzw. wird es auch Situationsabhängig sein.

[Fuerchau]

Ich habe ja nichts gegen die MQT's sondern nur gesagt, dass ich diese nicht brauche.

Bzgl. BI gibt es sicherlich viele Möglichkeiten. Ich kenne da auch genügend Leute, die sich Monat für Monat mit ihren Excel's, Pivot und Power-Pivot rumschlagen.
Ebenso kenne ich auch genügend Leute, die unter der Last ständig irgendwelche Queries bereitzustellen stöhnen.
Des weiteren kenne ich auch Leute, die BI-Tools mangels Bedienbarkeit, Sicherheitsbedenken u.ä. nicht an ihre User weitergeben was wiederum Mehrbelastung für die IT bedeutet.

BI heißt i.W.: 1x installieren, 1x die Daten bereitstellen und die User (ggf. nach Schulung) selber auf die Anwendung los zu lassen.
Der Vorteil dabei ist, dass der Anwender von SQL keine Ahnung haben muss. Die Tools, die ich so sehe, sind doch einem normalen User nicht zumutbar.

Wer kennt sie nicht, die User, die mit Query/400 mal eben das System lahmlegen?
Auch MQT's verhindern dies nicht, wenn ich einen ungünstigen Bericht erstelle zu dem es wieder mal keinen Index gibt.

Aber ich weiß ja, es gibt keine perfekte und schon gar nicht nur eine Lösung sonst käme man ja auch mit einer einzigen Programmiersprache (und überhaupt nur eine Sprache) aus.

[KM]

Du hast hier nur den Cloud-Dienst von Power BI angesprochen. Dafür bestehen natürlich diese Begrenzungen. Ich meinte aber die Desktop-Variante "Power BI Desktop". Damit kann man beliebig viele Daten verarbeiten, da hier keine Prüfung wie beim Cloud-Dienst erfolgt. Die betreffenden Benutzer müssen das Programm dann natürlich installiert haben.

Die wesentliche Einschränkung ist aber der Import auf wenige Formate.

Wie kommst Du darauf? Es gibt hier sehr viele Import-Möglichkeiten. Für uns relevant ist hauptsächlich ODBC, Excel, CSV, SQL-Server. Es gibt aber noch viel mehr. Man kann sogar Daten von URLs einlesen (z.B. Länderliste oder Umrechnungskurse aus dem Web). Kannst Du das auch?

Auch hier sieht es i.W. so aus, dass die Daten für den Bericht jedes mal wieder neu aus dem Quellsystem geladen werden.

Du musst die Daten nur neu laden, wenn Du neue/aktualisierte Daten haben willst, ansonsten nicht.

Kann ein Bericht Daten aus unterschiedlichen Quellen verknüpfen?

Es handelt sich hier um eine ganz normale ETL-Umgebung, wie bei einem "echten BI-System" :-) Man lädt die Daten aus unterschiedlichen Systemen rein, kann diese Daten entsprechend transformieren (z.B. das Jahr aus einem Datumsfeld extrahieren und in eine neue Spalte einfügen), und stellt schließlich über das "Beziehungsfenster" die entsprechenden Verknüpfungen zwischen den geladenen Daten her, egal aus welcher Datenquelle.

Beispiel: Ich habe den Umsatz auf der AS/400 und die Umsatzplanung in Excel. Kann ich nun einen Vergleich zwischen diesen Daten in einem Bericht darstellen?

Kein Problem, solange die Daten via ETL hochgeladen sind.

Wie sieht es mit der Drilldown-Funktion aus?
Findet die innerhalb des Berichts statt oder muss ich hier Sub-Berichte erstellen?

Ich kann mir z.B. bei einem Säulendiagramm zu jeder Säule die dahinterliegenden Datensätze anzeigen lassen, falls Du das meinst.

Kann ich das Ganze im Batch auf einem Server machen und die Berichtsausgabe per Mail versenden?

Wozu per Mail versenden? Die betreffenden Leute müssen nur das Programm installiert haben und können sich dann jederzeit ihre relevanten Daten selbst anschauen und damit "spielen".

Wie sieht es bei Deinem Tool mit Slice + Dice aus? Kann man z.B. in einem Säulendiagramm mehrere Spalten markieren und diese dann gezielt auswerten?

Gibt es Map-Auswertungen (z.B. Umsatzverteilung auf einer Landkarte)?

Viele Grüße,
KM

[andreaspr@aon.at]

Es ist einfach nicht im Sinne eines professionellen Forums wenn Aussagen oder Abwertungen über ein Produkt oder was auch immer gemacht wird ohne dieses überhaupt richtig zu kennen.

Aber ich weiß ja, es gibt keine perfekte und schon gar nicht nur eine Lösung sonst käme man ja auch mit einer einzigen Programmiersprache (und überhaupt nur eine Sprache) aus.

Dem schließe ich mich an.

[Fuerchau]

Wenn ich mir das so ansehe, muss ich mir schon über Kosten Gedanken machen:
https://powerbi.microsoft.com/de-de/pricing/

Mit der Freeversion kann jeder User alleine für sich hinwurschteln.
Wenn man gemeinsame Daten/Berichte in Abteilungen benötigt ist schon die Proversion erforderlich.

Was mich am meisten stört sind die Beschränkungen:
10GB je User oder Gruppe
1.000.000 Zeilen je Stunde => was ist damit eigentlich gemeint?
max. 8 Updates täglich => das ist schon nicht unerheblich, oder was soll ich darunter verstehen?

[KM]

Mit der Freeversion kann jeder User alleine für sich hinwurschteln.
Wenn man gemeinsame Daten/Berichte in Abteilungen benötigt ist schon die Proversion erforderlich.

Nein, erforderlich ist die Pro-Version nicht. Du kannst ja mit "Power BI Desktop" die Auswertung erstellen und im normalen Dateisystem als .pbix speichern und mit entsprechenden Berechtigungen versehen.

Was mich am meisten stört sind die Beschränkungen:
10GB je User oder Gruppe
1.000.000 Zeilen je Stunde => was ist damit eigentlich gemeint?
max. 8 Updates täglich => das ist schon nicht unerheblich, oder was soll ich darunter verstehen?

Das mit dem Cloud-Dienst habe ich mir noch nicht weiter angeschaut. Und auf den ersten Blick habe ich es auch nicht verstanden. Wir würden eh hauptsächlich mit der Desktop-Variante arbeiten.

PS: Wenn ich richtig gesehen habe, ist "Microsoft Power BI" sogar ein Vortrag auf der Common im Juni. Ich bin aber leider nicht dort.