Oracle 12c - Das umfassende Handbuch

Kapitel 25

OLAP und Reporting

594

Client-Programmen funktionieren. So funktioniert das Query Rewrite auch, wenn

Sie eine SQL-Anweisung mit SQL*Plus abschicken.

Das vorliegende Kapitel beschäftigt sich ausschließlich mit den Features für Ana-

lyse und Reporting, die Bestandteil der Oracle-Datenbank sind.

25.1 SQL für Data Warehouse-Abfragen

Oracle 12c stellt ein umfangreiches Paket von SQL-Funktionen für Analyse und

Reporting für eine Data Warehouse-Datenbank zur Verfügung. Dabei handelt es

sich um sogenannte Built-In Functions, die Bestandteil des SQL-Prozessors sind.

Diese Funktionen lassen sich in folgende Gruppen unterteilen:

쐽 Ranking-Funktionen

쐽 Lag/Lead-Analyse

쐽 First/Last-Analyse

쐽 Moving Window-Funktionen

쐽 Linear Regression-Funktionen

In Tabelle 25.1 finden Sie eine Kurzbeschreibung der Funktionsgruppen.

Tipp

Achten Sie darauf, dass der Anteil von Ad-hoc Queries gegenüber Reports nicht

zu groß wird. Ad-hoc Queries laufen in der Regel länger und verbrauchen wert-

volle Ressourcen. Sollte von vornherein klar sein, dass der Anteil von Ad-hoc

Queries sehr hoch ist, dann sollte die Architektur überdacht werden. So können

Sie zusätzliche Data Marts erstellen oder OLAP und Data Mining einsetzen.

OLAP steht für Online Analytic Processing. Dabei handelt es sich um eine spezi-

elle Speicherform in der Datenbank. Die Daten werden nicht in relationaler, son-

dern in einer mehrdimensionalen Form gespeichert. Damit kann einerseits eine

bessere Performance für Abfragen erreicht werden, andererseits wird das Auf-

spüren von Informationen erleichtert. Im Zusammengang mit OLAP gibt es spe-

zielle Data Mining Tools und -Methoden, die das Vorgehen bei Ad-hoc-Abfragen

und -Analysen besser unterstützen.

Funktionstyp Beschreibung

Ranking Berechnung von Rängen und Prozentsätzen

Windowing Berechnung von kumulativen und laufenden Summen

Reporting Berechnung von Anteilen, z.B. Marktanteilen

Tabelle 25.1: Gruppen analytischer Funktionen

25.1

SQL für Data Warehouse-Abfragen

595

Analytische Funktionen werden in mehreren Schritten verarbeitet:

1. Ausführung der SQL-Anweisung unter Berücksichtigung der Klauseln für

JOINS, WHERE, GROUP BY und HAVING.

2. Erstellen von Partitionen für das Resultset. Die analytischen Funktionen werden

auf jeden Datensatz in jeder Partition angewandt. Dieser Schritt wird paralleli-

siert.

3. Zum Schluss wird, falls erforderlich, die ORDER BY-Klausel berücksichtigt,

und die Ergebnisse werden sortiert.

Im zweiten Schritt werden Partitionen für das Zwischenergebnis gebildet. Diese

Partitionen haben keine Beziehung zu den Partitionen einer Tabelle. Ein Vorzug

dieses Vorgehens ist, dass die anschließenden Sortiervorgänge zur Berücksichti-

gung der GROUP BY-Klausel parallelisiert werden können.

Außerdem kann für jeden Satz in den Partitionen ein gleitendes Fenster definiert

werden. Dieses Fenster umfasst einen bestimmten Bereich von Sätzen, auf die

analytische Funktionen angewandt werden können. Jedes Fenster besteht aus

einer Anfangs- und einer Endezeile. Anfang und Ende können sich innerhalb der

Partition bewegen. Im Extremfall kann ein Fenster alle Datensätze einschließen.

Jede Berechnung, die mithilfe einer analytischen Funktion ausgeführt wird,

basiert auf der aktuellen Zeile. Sie dient als Referenzpunkt, alle Positionen werden

relativ zur aktuellen Zeile berechnet. In Abbildung 25.1 ist Zeile 103 die aktuelle

Zeile. Das Fenster reicht (einschließlich) von Zeile 83 (aktuelle Zeile minus 20) bis

Zeile 124 (aktuelle Zeile plus 21). Mit dem Verschieben der aktuellen Zeile ver-

schiebt sich das Fenster.

LAG/LEAD Finden eines Satzes, der eine bestimmte Anzahl von Sätzen entfernt ist

FIRST/LAST Finden des ersten oder letzten Satzes in einer Gruppe

Linear Regression Berechnung von linearer Rückentwicklung und anderen Statistiken

Inverse Percentile Finden von Werten, die einem gewissen Prozentsatz entsprechen

Hypothetical Rank

and Distribution

Bestimmen des Rangs oder Prozentsatzes, den ein Satz hätte, wenn er in

eine Datenmenge eingefügt würde

Tipp

Analytische Funktionen können parallelisiert werden. Dadurch entsteht eine sig-

nifikante Verbesserung der Performance.

Funktionstyp Beschreibung

Tabelle 25.1: Gruppen analytischer Funktionen (Forts.)

Kapitel 25

OLAP und Reporting

596

Abb. 25.1: Aktuelle Zeile und Fenster einer Partition

Eine Ranking-Funktion berechnet den Rang eines Satzes im Vergleich zu den

anderen Sätzen abhängig von bestimmten Regeln. Im folgenden Beispiel werden

Quartalssummen über Kontostände und Transaktionen gebildet.

SQL> SELECT a.fiscal_quarter fq,SUM(b.balance)

2 balance,

3 sum(b.transactions) transactions,

4 RANK() OVER (ORDER BY SUM(b.balance)) AS

5 default_rank,

6 RANK() OVER (ORDER BY SUM(b.transactions)) AS

7 second_rank

8 FROM time_dim a, account_facts b

9 WHERE b.time_key = a.time_key

10 GROUP BY a.fiscal_quarter;

FQ BALANCE TRANSACTIONS DEFAULT_RANK SECOND_RANK

--- ---------- ------------ ------------ -----------

1 2223375000 5058900000 1 1

2 2231625000 5105100000 3 2

3 2239875000 5082000000 2 3

4 2248125000 5128200000 4 4

Listing 25.1: SQL-Abfrage mit RANK-Funktion