Das Thema Performanceoptimierung in Cognos Analytics beschäftigt uns fast täglich. Es ist ein sehr komplexes Thema, denn die Laufzeit eines Berichtes oder Dashboards wird durch sehr viele Einzelfaktoren bestimmt (Hardware, Netzwerk, Konfiguration…). In diesem Beitrag sammeln wir ein paar Themen was aus Anwendersicht für eine gute Laufzeit getan werden kann. Das Dokument hat keinen Anspruch auf Vollständigkeit und über einige Punkte lässt sich bestimmt diskutieren. Aber es kann als Leitfaden/Checkliste genutzt werden um Abfragen, Analysen und Berichte ggf. noch schneller zum Anwender zu bringen.
Reporting
Bei der Erstellung von Reports haben viele Faktoren Einfluss auf die Gesamtlaufzeit. Bei Berichtserstellung sollten diese Aspekte schon von Anfang an im Blick bleiben.
Übersicht der einzelnen Punkte
- Wiederverwenden von Abfragen
- Ausschalten der automatischen Aggregation bei Detailberichten / -abfragen auf Einzeldatensatzebene
- COUNT vs COUNT DISTINCT vs TOTAL
- Vermeiden von unnötigen Verschachtelung bei Datencontainern (Master-Detail-Verbindungen)
- Paralleles oder sequentielles Ausführen von Abfragen
- Aktualisieren von Daten für statische Abfragen verhindern
- Verringern der Anzahl ausgegebener Zeilen
- Vermeiden von UNION Verknüpfungen bei Abfragen / bei UNION Duplikate nicht entfernen
- Ausschalten des interaktiven Berichtsmodus
- Kontrolle: Leistungsdetails einbeziehen
- nur Datenbank vs. begrenzt lokale Verarbeitung
Wiederverwendung von Abfragen
Cognos (aber auch die genutzen Datenbanken) verfügen über etliche Caching-Mechanismen. Um diese optimal zu nutzen ist eine Grundregel möglichst Abfragen wieder zu verwenden.
Ein klassisches Beispiel ist ein Bericht in dem ein Diagramm und eine Kreuztabelle (zwei Datencontainer) die gleichen Informationen auf einer Seite darstellen soll. Wird in einem solchen Fall die selbe Abfrage verwendet, werden die Daten nur ein mal von der Datenbank abgerufen.
Dieser positive Effekt kann sogar dann merkbar sein, wenn unterschiedliche Teile der Abfrage in den Datencontainern genutzt werden.
Ausschalten der automatischen Aggregation
Um die Reporterstellung für den Anwender effektiver zu gestalten aggregiert Cognos Analytics alle Abfragen automatisch. Dies ist bei den meisten Berichten auch gewünscht.
Beispiel: Bei einer Liste mit Produktreihen und der Absatzmenge wird die Menge automatisch auf die jeweiligen Produktreigen summiert.
Sollen Berichte erstellt werden, die Daten aber auf der Einzeldatensatzebene abrufen, sind diese aggregationen aber nicht notwendig und erhöhen unnötig die Laufzeit.
In solchen Fällen kann das automatische gruppieren und auswerten in den Eigenschaften der Abfrage ausgeschaltet werden:
Technischer Hintergrund: Bei der Verwendung von Kennzahlen wird bei der automatischen Auswertung in dem generierten SQL Statement eine summe gebildet und alle Attribute über “Group by” gruppiert.
Werden nur Attribute in einer Liste angezeigt wird ein “Select distinct” durchgeführt. “Distinct” und “group by” sind datenbankseitig sehr kostenintensiv.
COUNT vs COUNT DISTINCT vs TOTAL
Wenn Kennzahlen aggregiert werden sollen, sind hier auch durchaus Laufzeitunterschiede den verschiedenen Aggregaten zu erkennen. Für Datenbanken ist das “DISTICT” generell schlecht für die Laufzeit.
Beispiel: Soll die Anzahl an Verträgen gezählt werden, kann dies direkt am Datenelement “Vertragsnummer” eingestellt werden. Hierzu ist die Detailaggregation auf “Anzahl eindeutiger Elemente” einzustellen.
“Anzahl eindeutiger Elemente” wird im SQL durch ein COUNT DISTINCT umgesetzt. Dies stellt sicher, dass jede Vertragsnummer eindeutig gezählt wird. Dafür muss die Datenbank aber erst aufwendig Dubletten elementieren.
Ist also sichergestellt, dass jede Vertragsnummer nur einmal in der Tabelle vorkommt, ist die einfache Anzahl besser (in einem Test bei 150 Millionen Zeilen 1 Sekunde zu 40 Sekunden):
Wenn es generell nur um einen Zähler von Datensätzen geht, ist in vielen Fällen die schnellste Variante eine Spalte in der Datenbank mit einer 1 zu füllen und diese dann zu summieren.
Beispiel: Eine Tabelle hat drei Spalten: Produktgruppe, Produktnummer und Produktfarbe. Es soll nun gezählt werden wie viele Produkte je Produktgruppe existieren.
Um die Anzahl zu ermitteln müssten die Spalten Produktnummer und Produktfarbe kombiniert gezählt werden. Das ist durch eine Zählerspalte (hier “Anzahl”) einfacher und schneller:
Vermeiden von unnötigen Verschachtelung bei Datencontainern (Master-Detail-Verbindungen)
Cognos Analytics hat eine Technik Datencontainer in einen “Master-Detail”-Kontext zu bringen. Dies kann z.B. ein Diagramm sein, dass in einer Liste als Spalte eingefügt wird. Cognos erzeugt dann für jede Zeile der Liste ein eigenes Diagramm.
Die Master-Detail-Definition bestimmt, wie Daten von dem Master-Container an den Detail-Container übergeben werden. Jedes einzelne Detail-Objekt ist also wie eine neue Abfrage.
Um diese verschachtelten Abfragen zu minimieren, sollte versucht werden mit möglichst wenigen Datencontainern auszukommen.
Beispiel “Liste mit Kopfzeile”:
Paralleles oder sequentielles Ausführen von Abfragen
Hat ein Bericht mehrere Abfragen, können diese parallel oder sequentiell verarbeitet werden. In der Regel ist das parallele Abarbeiten der Abfragen schneller. Ist die Datenquelle aber stark ausgelastet, kann es sein, dass die Summe der Einzellaufzeiten bei einer sequentiellen Abarbeitung kleiner ist, als die Laufzeit einer gleichzeitigen Verarbeitung. Hier hilft es nur, diese Einstellung in beiden Varianten zu testen:
Aktualisieren von Daten für statische Abfragen verhindern
In vielen Berichten wird mit Eingabeaufforderungen gearbeitet. Gibt es Abfragen in dem Bericht, die nicht abhängig von einer Eingabeaufforderung sind, müssen die bei einem Wechsel der Auswahl nicht erneut ausgeführt werden.
Beispiel: Eine Eingabeaufforderung mit drei Parametern (Jahr, Region und Land). Land ist abhängig von der zuvor ausgewählten Region. Die Seite muss also nach der Auswahl einer Region erneut ausgeführt werden.
Die Auswahl der Jahre ändert sich hierdurch aber nicht. Diese Abfrage kann dann in den Eigenschaften so eingestellt werden, dass diese nicht aktualisiert wird (“Bei Aufforderung aktualisieren” ? nein)
Verringern der Anzahl ausgegebener Zeilen
Die einfachste Performance-Optimierung ist es die Menge der zu verarbeitenden Daten zu verringern. Oft wünschen sich die Berichtsanforderer “alle Daten”. Es resultieren Berichte, die neben Zusammenfassungen auch lange Listen von Detaildaten abrufen. In der Praxis zeigt sich, dass diese Detaildaten nur in den wenigsten Fällen sofort benötigt werden. Hier kann durch die Verwendung der Drill-Through-Technik die Komplexität eines einzelnen Reports verringert werden.
So kann z.B. ein Bericht mit vielen Detaildaten auf drei Berichte aufgeteilt werden:
1. Summen je Region ? 2.Summen je Land einer ausgewählten Region ? 3. Detaildaten je Stadt eines gewählten Landes
Vermeiden von UNION Verknüpfungen bei Abfragen / bei UNION Duplikate nicht entfernen
Unabhängig von Cognos sind Verknüpfungen von SQL-Statements über den UNION-Befehl in der Regel Datenbankseitig sehr kostenintensiv.
Ist der UNION nicht vermeidbar, sollten folgende Punkte überprüft werden:
1) Duplikate entfernen
Das Entfernen von Duplikaten sollte – wenn möglich – ausgeschaltet werden. Hintergrund: Um Duplikate in der Gesamtmenge zu finden, muss die Datenbank das Gesamtergebnis kostenintensiv komplett sortieren.
TIPP: Diese Einstellung ist nur dann wirklich bemerkbar, wenn auch das automatische Auswerten und Gruppieren deaktiviert ist (Siehe 1.2)
2) UNION über Seitenlayout nachbilden
Der schnellste Weg die Ergebnisse zweier Abfragen in eine zu vereinigen ist der, dieses direkt über die Seitenansicht zu machen.
Hierzu werden zwei Listenobjekte untereinander auf einer Seite platziert die jeweils auf eine der beiden Abfragen verweisen. Dann wird in der zweiten Liste in den Eigenschaften die Spaltentitel ausgeblendet.
Ausschalten des interaktiven Berichtsmodus
Der interaktive Berichtsmodus erlaubt es Anwendern in der HTML-Ansicht einen angezeigten Bericht nach eigenen Wünschen weiter anzupassen (Filtern, Sortieren, Gruppieren, Summen erzeugen…).
Damit diese Funktionalität zur Verfügung steht, wird aber in das HTML Dokument eine Reihe an Java-Skript-Funktionen implementiert. Sollen also an einem Bericht keine Anpassungen mehr vorgenommen werden können, können auch diese Funktionen abgeschaltet werden.
HINWEIS: In den letzten Versionen wurde bei der Entwicklung von IBM Cognos Analytics verstäkt auf Performanceoptimierung geachtet. Ab der aktuellen Version 11.1.7 hat dieser Punkt weniger Auswirkung auf die Laufzeit.
Beispiel: Eine verschachtelte Kreuztabelle mit knapp 26.000 Zellen wurde bei einem Test im interaktiven Modus in 3318 Millisekunden ausgeführt und ohne in 3290 Millisekunden.
Gerade bei komplexen Dashboard-Darstellungen kann dies aber weiterhin noch ein Faktor sein und sollte überprüft werden.
Nur Datenbank vs. begrenzt lokale Verarbeitung
Cognos verfügt über Berechnungsfunktionen, die zum Teil nicht von einer Datenbank durchgeführt werden kann. Diese Schritte werden dann auf dem Cognos Server durchgeführt.
Generell sollte aber der Großteil der Datenverarbeitung von der Datenbank übernommen werden. Berichtsersteller können hier in den Eigenschaften der Abfragen die Option “Verarbeiten” auf “Nur Datenbank” stellen. Somit wird sichergestellt, dass alle Berechnungen schon von der Datenbank durchgeführt werden.
Bei der Berichtserstellung wird es aber immer wieder Stellen geben, wo eine lokale Verarbeitung notwendig ist. Cognos zeigt dieses bei der Berichtsausführung durch eine entsprechende Fehlermeldung an. Der Berichtsersteller muss in diesem Fall überprüfen welche Funktion genau dafür verantwortlich ist und ggf. die Berechnung über alternative Funktionen so umwandeln, dass die Abfrage wieder auf der Datenbank läuft. Gibt es keine Alternative, dann ist der letzte Weg die Abfrage auf “Begrenzt Lokal” stehen zu lassen.
Beispiel: Ein Bericht wird über ein Filter auf eine bestimmten Bereich gefiltert. Der Bereich ist aber nur über eine Funktion aus einer Spalte abzuleiten. Ist diese Funktion nicht durch die Datenbank auszuführen, werden erst alle Daten komplett auf den Cognos Server geholt und dann lokal der Filter durchgeführt. So kann es passieren, dass z.B. Millionen von Datensätzen gelesen werden, obwohl nur 100 angezeigt werden sollen.
Kontrolle: Leistungsdetails einbeziehen
Zur Performanceoptimierung bietet Cognos für Berichtsersteller eine gute Hilfestellung: Die anzeige der Leistungedetails.
Mit dieser Einstellung zeigt Cognos bei einem ausgeführten Bericht im HTML-Format zu jedem Datencontainer die Laufzeit der Query sowie die Zeit die benötigt wurde die Ausgabe aufzubereiten.
Einzellaufzeit eines Datencontainers:
Gesamtlaufzeit einer Seite:
Dashboards und Storys (Datasets)
Gerade bei der Erstellung von Dashboards (identisch bei Storys) ist eine sehr schnelle Antwortzeit erforderlich. Da es bei den Dashboards keine Möglichkeiten zur Abfrageoptimierung gibt, erfolgt hier die Optimierung auf Datenebene.
In Cognos Analytics stehen hier Datasets zur Verfügung. Datasets sind Datenauszüge die zu einem definierten Zeitpunkt (manuell oder per Zeitplan) über eine Abfrage abgerufen und gespeichert werden.
Das Speichern der Datasets erfolgt auf dem Cognos Server als Datei. Diese Dateien sind verschlüsselt und für performante Abfrage in Analysen optimiert (Parquet-Format).
Metadatenmodelle
Bei den Datenmodellen (Datenmodule und Framework Manager Packages) gelten die selben Regeln wie bei dem Reporting.
Generelle Checkliste
- wenn möglich auf Outer-Joins verzichten (ggf. Inner-Joins nutzen und auf Tabellenebene “fehlende Treffer” durch Dummydaten auffüllen)
- UNION vermeiden
- Komplexe Berechnungen in den ETL Bereich verlagern
Framework Manager
Eine wichtige Besonderheit bei dem Cognos Framework Manager ist der Umgang mit Determinanten. Determinanten sind ein leistungsstarkes Mittel um Fakten auf unterschiedliche Aggregationsebenen miteinander zu verbinden (z.B. Tagesumsätze mit Monatsplanzahlen). Die Determinanten sind aber auch für genau diesen Fall wichtig und erforderlich. Der Framework Manager generiert in der Regel schon bei dem Import der Metadaten neuer Tabellen über die Primärschlüssel auch Determinanten. Es sollte hier immer überprüft werden ob diese korrekt sind, bzw. ob diese benötigt werden.
Liegt z.B. nur eine Faktentabelle vor, können (sollten) alle Determinanten aus den Abfrageobjekten gelöscht werden.
Datenmodule
Bei den Datenmodulen kann für jede einzelne Tabelle das Caching-Verhalten eingestellt werden. Cognos arbeitet wenn möglich mit Daten aus dem Cache.
Gerade bei Dashboards ist diese Einstellung wichtig, da in einem Dashboard in der Regel sehr viele kleine Abfragen generiert werden. Hier soll nicht bei jedem Dashboard-Widget erneut ein SQL an die Datenbank geschickt werden.
Die Standardeinstellung des Cache ist hier eine Vorhaltedauer von 60 Minuten:
