Oracle 12c - Das umfassende Handbuch

Kapitel 25

OLAP und Reporting

600

Die Abfrage in Listing 25.9 verwendet ein wanderndes Fenster. In der Spalte AVG

wird der Durchschnittswert der Transaktion für die letzen vier Monate gebildet.

Mit dem Wandern der aktuellen Zeile bewegt sich auch das Fenster, so wie in

Abbildung 25.1 dargestellt.

25.2 SQL-Modeling

Die MODEL-Klausel wurde in Oracle 10g eingeführt. Sie können aus dem

Resultset einer SQL-Abfrage ein mehrdimensionales Array bilden und darauf

sogenannte Rules anwenden. Die Rules führen Berechnungen auf dem Array

durch und erstellen das gewünschte Format. Die Möglichkeiten sind durchaus mit

8 WHERE year IN (2002,2003)

9 GROUP BY a.year, a.fiscal_quarter

10 ORDER BY a.year, a.fiscal_quarter;

YEAR FISCAL_QUARTER TRANS CUM_TRANS

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

2002 1 34543 34543

2002 2 5678 40221

2002 3 149758 189979

2002 4 32844 222823

2003 1 885583 885583

2003 2 4567 890150

2003 3 543543 1433693

2003 4 23432 1457125

Listing 25.8: Gruppenfunktion durch Bildung von Partitionen

SQL> SELECT a.year, a.fiscal_quarter,

2 TO_CHAR(SUM(b.transactions)) TRANS,

3 TO_CHAR(AVG(SUM(b.transactions))

4 OVER (ORDER BY a.year, a.fiscal_quarter

5 ROWS 3 PRECEDING),'9999999999999') AVG

6 FROM time_dim a, account_facts b

7 WHERE year IN (2002,2003)

8 GROUP BY a.year, a.fiscal_quarter

9 ORDER BY a.year, a.fiscal_quarter;

YEAR FISCAL_QUARTER TRANS AVG

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

2002 1 34543 34543

2002 2 5678 20111

2002 3 149758 63326

2002 4 32844 55706

2003 1 885583 268466

2003 2 4567 268188

2003 3 543543 366634

2003 4 23432 364281

Listing 25.9: Ein wanderndes Fenster verwenden

25.2

SQL-Modeling

601

denen zu vergleichen, die Sie bei der Aufbereitung von Resultsets in einer Tabel-

lenkalkulation haben. Der Vorteil beim SQL-Modeling besteht darin, dass er auto-

matisiert werden kann. Die komplette Aufbereitung wird damit vom Datenbank-

Server vorgenommen, was bei großen Arrays schneller ist als in einem Kalkulati-

onssystem.

Abb. 25.2: Beispiel für SQL-Modeling

Im Beispiel in Listing 25.10 sind Rules definiert. Danach werden z.B. die Werte für

2002 als Summe der Werte für 2000 und 2001 gebildet. Das SQL-Modeling inte-

griert diese Werte in das Abfrageergebnis, als würden diese Sätze existieren.

SQL> SELECT country_name, prod_name, calendar_year, sales

2 FROM sh.sales_view

3 WHERE country_name IN ('Italy', 'Japan')

4 AND prod_name = 'Bounce'

5 MODEL

6 PARTITION BY (country_name) DIMENSION BY (prod_name, calendar_year)

7 MEASURES (sales sales)

Kapitel 25

OLAP und Reporting

602

Mit SQL-Modeling können Sie u.a. folgende Funktionen durchführen:

쐽 Symbolische Zellenadressierung

Die Zeilenwerte von Measure-Spalten werden wie Zellen behandelt. Diese kön-

nen adressiert und verändert werden. Die symbolische Adressierung wurde in

Listing 25.10 verwendet.

쐽 Symbolische Array-Berechnung

Für die symbolischen Zellen können Formeln verwendet werden. Die Berech-

nung erfolgt dann für das gesamte Array, so wie in Listing 25.10:

쐽 Sie können eine UPSERT- und eine UPDATE-Option verwenden. Mit UPSERT

wird die Zelle erstellt, falls sie nicht existiert, und geändert, falls sie vorhanden

ist. Dagegen führt die UPDATE-Option nur eine Änderung durch und fügt kei-

nen neuen Zellen ein. Eine UPSERT-Option könnte folgendermaßen ausse-

hen:

쐽 Für Dimensionen können Wildcards verwendet werden. Das Schlüsselwort für

eine Wildcard ist ANY.

8 RULES

9 (sales['Bounce', 2002] = sales['Bounce', 2001] + sales['Bounce', 2000],

10 sales['Y Box', 2002] = sales['Y Box', 2001],

11 sales['All_Products', 2002] = sales['Bounce', 2002] + sales['Y Box',

2002])

12 ORDER BY country_name, prod_name, calendar_year;

COUNTRY_NAME PROD_NAME CALENDAR_YEAR SALES

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

Italy Bounce 1999 2474,78

Italy Bounce 2000 4333,69

Italy Bounce 2001 4846,30

Italy Bounce 2002 9179,99

Japan Bounce 1999 2961,30

Japan Bounce 2000 5133,53

Japan Bounce 2001 6303,60

Japan Bounce 2002 11437,13

. . .

Listing 25.10: Beispiel für SQL-Modeling

(sales['Bounce', 2002] = sales['Bounce', 2001] + sales['Bounce', 2000]

UPSERT transactions [2004, 2] = transactions [2003, 2] - 1000

UPSERT transactions [2004, ANY] = transactions [2003, 1] - 1000

25.2

SQL-Modeling

603

쐽 Verwendung der CV-Funktion

Sie können die CV-Funktion verwenden, um die Werte einer Spalte einer

Dimension Table einzufügen. Das erspart die mehrfache Definition einer Rule.

Damit können die Regeln

durch folgende Regel ersetzt werden:

Weitere Funktionen stehen zur Verfügung. Diese Auswahl lässt bereits erahnen,

dass es sich um ein wirklich universell einsetzbares Feature handelt.

sales[country='Spain', year=2003] = 1.5 * sales['Spain', 2002],

sales[country='Japan', year=2003] = 1.5 * sales['Japan', 2002],

sales[country='Italy', year=2003] = 1.5 * sales['Italy', 2002]

salse[country IN ('Spain','Japan','Italy'), year=2003] = 1.5 *

sales[CV(country), 2002]