SQL Abfragen auf OLAP Cubes, Teil 1
von Frank Schneede, ORACLE Deutschland GmbH

Die Erstellung von OLAP Cubes mit dem Analytic Workspace Manager (AWM) ist in einem früheren Tipp ausführlich behandelt worden. Wie dort ausgeführt, enthalten die Zellen eines OLAP Cubes nahezu beliebig komplexe geschäftliche Kennzahlen, die nach unterschiedlichen Kriterien (Hierarchien) verdichtet vorliegen. Mit den unterschiedlichsten Methoden und Werzeugen von MS Excel bis hin zu Oracle BI Enterprise Edition können die Inhalte von OLAP Cubes ausgewertet und visualisiert werden. Die Abfragesprache, mit der auf den OLAP Cube zugegriffen wird, ist hierbei SQL. Das SQL wird jedoch innerhalb der verwendeten Software generiert und bleibt daher dem Benutzer meistens verborgen.

Durch die Verwendung der seit Oracle 11g bekannten Cube-organized Materialized Views können eine Vielzahl von relationalen Materialized Views ersetzt werden, die Abfragen im Datawarehouse mittels Query Rewrite beschleunigen. Cube-organized Materialized Views werden hierbei über den OLAP Cube gelegt, ohne ein neues Objekt mit redundanten Daten zu erzeugen. Dieses Verhalten ist bereits aus der relationalen Welt mit der Verwendung der Materialized Views on prebuilt table bekannt.

Dieser Tipp, sowie der in 14 Tagen erscheinende 2. Teil, vervollständigen das Thema OLAP in der Datenbank. Beide sind erneut ausdrücklich zum Mitmachen gedacht. Es wird gezeigt, wie das Abfragen von OLAP Cubes über den Zugiff auf die zum OLAP Cube gehörenden relationalen Views mit Hilfe von einfachem SQL erfolgt. Darüberhinaus wird die Steigerung der Abfrage Performance durch Query Rewrite auf eine Cube-organized Materialized View dargestellt.

Im ersten Schritt müssen die Voraussetzungen geschaffen werden, um die in diesem Tipp verwendeten SQL Statements ausführen zu können. Das geschieht, in dem der Tipp Erstellung von OLAP Cubes leicht gemacht mit dem Analytic Workspace Manager 11g (AWM) oder alternativ die Schritte

  1. Installation der Oracle Datenbank 11g mit OLAP Option
  2. Installation des Analytic Workspace Manager 11g
  3. Installation des Beispiel-Schemas
ausgeführt werden.

Abfragen der zum OLAP Cube gehörenden Views

Zusammen mit dem OLAP Cube werden im AWM eine Reihe von relationalen Views angelegt. Diese Views bilden den multidimensionalen OLAP Cube als Star Schema ab. Hierbei entsprechen
  • die Cube View der Fakten-Tabelle und
  • die Dimensions- oder Hierarchie-Views den Dimensions-Tabellen

Es gibt im Gegensatz zum klassischen Star Schema jedoch zwei entscheidende Unterschiede
  • Fakten-Tabellen im Star Schema beinhalten lediglich Detaildaten, während die Cube-View verdichtete Daten verschiedener Ebenen enthält
  • Berechnungen in einem OLAP Cube werden jeweils als Spalten dargestellt und in der OLAP Engine der Datenbank ausgeführt

Diese Unterschiede sind wesentlich dafür, wie Abfragen auf dem OLAP Cube formuliert werden müssen. Im klassischen Star Schema werden Aggregationen mittels einer Kombination von Aggregatfunktionen (z. B. SUM) und GROUP BY Klausel erzeugt. In einer Cube-View braucht hingegen nur die Spalte selektiert zu werden, die das entsprechende Aggregat enthält. Es wird keine Aggregatfunktion im SELECT-Statement benötigt, da die Daten ja bereits im OLAP Cube aggregiert vorliegen!

In der großen Mehrheit sind bei Abfragen auf OLAP Cubes folgende Schritte zu beachten
  1. Auswahl der Kennzahlen und Attribute der Dimensionen
  2. Verknüpfung der Cube-View mit den Dimensions-Views
  3. Einschränkung der Kennzahlen und Attribute der Dimensionen
  4. Verwendung der "ALL"-Filter zur korrekten Berechnung von Aggregationen für ausgeschlossene Spalten der Dimension

Zur Erläuterung dieser Funktionsweise sollen nun einige SELECT-Statements vorgestellt werden, die im Schema OLAPTRAIN ausgeführt werden. Die ausgeführten Statements in Skriptform finden Sie hier.

Beispiel 1: Ausführung einer einfachen OLAP Cube Abfrage

In dieser Abfrage werden die nach Departments aggregierten Verkäufe einfach als Spalte abgefragt. Der Filter in der Produkthierachie wird genutzt, um die gewünschten Produktebenen zu bekommen. Obwohl nur die Produkthierarchie im SELECT ausgewählt worden ist, müssen in der WHERE-Klausel alle anderen Dimensionen auf höchster Hierarchieebene ("ALL%") angegeben werden, um die im OLAP Cube vorausberechneten Werte zu erhalten. 
SQL> COLUMN dept  FORMAT a30
SQL> COLUMN sales FORMAT 999,999,990.000
SQL> SELECT p.department_long_descript AS dept
  2  ,      s.sales                    AS sales
  3  FROM product_standard_view p
  4  ,    sales_cube_view       s
  5  WHERE(p.dim_key    = s.product
  6  AND   p.level_name = 'DEPARTMENT'
  7  AND   s.channel    = 'ALL_CHANNELS'
  8  AND   s.geography  = 'ALL_REGIONS'
  9  AND   s.time       = 'ALL_YEARS');

DEPT                                      SALES
------------------------------ ----------------
Portable Music and Video         49,286,079.480
Cameras and Camcorders           31,820,248.400
Computers                       336,408,689.390

Abgelaufen: 00:00:00.23
SQL>
Die Aggregate werden durch die Verwendung der "ALL"-Filter in der OLAP Engine der Datenbank berechnet, das Ergebnis erscheint nahezu ohne Zeitverzug. Die Spaltennamen werden übrigens durch OLAP auf 24 Zeichen begrenzt (das muss bei Abfragen entsprechend berücksichtigt werden).

Beispiel 2: Verwendung von Level- und Member-Conditions in einer Abfrage

Im ersten Beispiel ist lediglich die Produkthierarchie als Bedingung für die ausgewählte Ebene (Level-Condition) verwendet worden. Das zweite Beispiel soll zeigen, dass an dieser Stelle Ebenen mehrerer Hierarchien ausgewählt werden können.

Jede Hierarchie- bzw. Dimensions-View enthält eine Spalte LEVEL_NAME. Der Wert dieser Spalte ist der Name der OLAP Hierarchieebene, die bei der Modellierung der Dimension im AWM angelegt wurde. Über die Einschränkung dieser Spalte in der WHERE-Bedingung läßt sich die Abfrage so einschränken, dass nur die Verdichtungen auf der gewünschten Hierarchieebene berücksichtigt werden.

Im folgenden Beispiel werden die Verdichtungen auf den Hierarchieebenen Vertriebskanal (CLASS), Abteilung (DEPARTMENT) und Quartal (CALENDAR_QUARTER) festgelegt. Es sollen die Aggregationen für das Kalenderjahr 2007 errechnet werden. 
SQL> COLUMN class FORMAT a10
SQL> COLUMN qtr   FORMAT a10
SQL> SELECT c.class_long_description   AS class
  2  ,      p.department_long_descript AS dept
  3  ,      t.calendar_quarter_long_de AS qtr
  4  ,      round(s.sales)             AS sales
  5  FROM channel_sales_channel_view c
  6  ,    product_standard_view      p
  7  ,    geography_regional_view    g
  8  ,    time_calendar_view         t
  9  ,    sales_cube_view            s
 10  WHERE(c.dim_key                  = s.channel
 11  AND   g.dim_key                  = s.geography
 12  AND   p.dim_key                  = s.product
 13  AND   t.dim_key                  = s.time
 14  AND   g.level_name               = 'ALL_REGIONS'
 15  AND   c.level_name               = 'CLASS'
 16  AND   p.level_name               = 'DEPARTMENT'
 17  AND   t.level_name               = 'CALENDAR_QUARTER'
 18  AND   t.calendar_year_long_descr = 'CY2007')
 19  ORDER BY c.class_long_description
 20  ,        p.department_long_descript
 21  ,        t.end_date;

CLASS      DEPT                           QTR                   SALES
---------- ------------------------------ ---------- ----------------
Direct     Cameras and Camcorders         Q1-CY2007     1,242,385.000
Direct     Cameras and Camcorders         Q2-CY2007     1,125,521.000
Direct     Cameras and Camcorders         Q3-CY2007     1,354,490.000
Direct     Cameras and Camcorders         Q4-CY2007     1,443,028.000
Direct     Computers                      Q1-CY2007    13,917,490.000
Direct     Computers                      Q2-CY2007    11,756,607.000
Direct     Computers                      Q3-CY2007    12,865,030.000
Direct     Computers                      Q4-CY2007    14,308,176.000
Direct     Portable Music and Video       Q1-CY2007     1,945,639.000
Direct     Portable Music and Video       Q2-CY2007     1,666,430.000
Direct     Portable Music and Video       Q3-CY2007     1,812,649.000
Direct     Portable Music and Video       Q4-CY2007     2,045,273.000
Indirect   Cameras and Camcorders         Q1-CY2007     1,719,385.000
Indirect   Cameras and Camcorders         Q2-CY2007     1,573,766.000
Indirect   Cameras and Camcorders         Q3-CY2007     1,837,557.000
Indirect   Cameras and Camcorders         Q4-CY2007     2,097,116.000
Indirect   Computers                      Q1-CY2007    19,859,709.000
Indirect   Computers                      Q2-CY2007    16,824,419.000
Indirect   Computers                      Q3-CY2007    18,117,883.000
Indirect   Computers                      Q4-CY2007    20,257,301.000
Indirect   Portable Music and Video       Q1-CY2007     2,747,134.000
Indirect   Portable Music and Video       Q2-CY2007     2,323,586.000
Indirect   Portable Music and Video       Q3-CY2007     2,500,406.000
Indirect   Portable Music and Video       Q4-CY2007     2,878,119.000

24 Zeilen ausgewählt.

Abgelaufen: 00:00:00.17
SQL>
An dieser Stelle noch einige Bemerkungen zu dem obigen Statement
  • Es wurden drei der vier zur Verfügung stehenden Dimensionen ausgewählt: CHANNEL, PRODUCT, TIME
  • Für jede der ausgewählten Dimensionen wurde die Hierachie-View der Dimension und die darin enthaltene Bezeichnung LONG_DESCRIPTION verwendet.
  • Die Dimension GEOGRAPHY ist nicht in der Select-Liste des Statement enthalten, sondern wird nur im JOIN verwendet. Analog zu Beispiel 1 dient die Einschränkung auf die Hierarchieebene ALL_REGIONS dann dazu, die Aggregate über die GEOGRAPHY korrekt zu berechnen.
  • Die Einschränkung auf die gewünschten Ebenen (Level-Conditions) der anderen Dimensionen erfolgt über die Auswahl von
    • c.level_name = 'CLASS'
    • p.level_name = 'DEPARTMENT'
    • t.level_name = 'CALENDAR_QUARTER'
  • Auf der Dimension TIME wird der Filter t.calendar_year_long_descr = 'CY2007' (Member-Condition) gesetzt, um den gewünschten Zeitraum festzulegen. Hier werden also bestimmte Sätze ausgewählt und nicht alle Sätze einer festgelegten Ebene.
  • Durch die Kombination von Level- und Member-Condition auf der Dimension TIME wird erreicht, dass nur die Quartale des Kalenderjahres 2007 als Ergebnis zurückgeliefert werden.


Beispiel 3: Hinzufügen von berechneten Kennzahlen

In der Cube-View werden Berechnungen in Form von Spalten zur Verfügung gestellt, wodurch die Durchführung analytischer SQL-Abfragen erheblich erleichtert wird. Spalten für berechnete Kennzahlen erscheinen im Ergebnis vollständig ausgerechnet, die Berechnung erfolgt jedoch durch die OLAP Engine und wird über die Cube-View angezeigt.

In dem Analytischen Workspace SALESTRACK sind nur die Kennzahlen SALES und QUANTITY direkte Kennzahlen. Alle anderen Werte sind berechnete Kennzahlen. Die OLAP Berechnungen arbeiten problemlos über alle Aggregationsebenen, selbst wenn die zugrundeliegenden Berechnungen komplex sind. In der folgenden Abfrage werden drei berechnete Kennzahlen (aufgelaufene Verkäufe, prozentuale Veränderung zum Vorjahr, Tendenz der Verkäufe) zu der in Beispiel 2 verwendeten Abfrage hinzugefügt
  • round(s.sales_ytd) AS ytd
  • round(s.sales_ytd_py_pct_chg, 2) AS ytd_py_pct_chg
  • how_is_sales_ytd

Diese Kennzahlen werden in der OLAP Engine berechnet und über die Cube-View bereitgestellt. 
SQL> COLUMN ytd              FORMAT 999,999,990.000
SQL> COLUMN ytd_py_pct_chg   FORMAT 90.00
SQL> COLUMN how_is_sales_ytd FORMAT a20
SQL> SELECT c.class_long_description         AS class
  2  ,      p.department_long_descript       AS dept
  3  ,      t.calendar_quarter_long_de       AS qtr
  4  ,      round(s.sales)                   AS sales
  5  ,      round(s.sales_ytd)               AS ytd
  6  ,      round(s.sales_ytd_py_pct_chg, 2) AS ytd_py_pct_chg
  7  ,      how_is_sales_ytd
  8  FROM channel_sales_channel_view c
  9  ,    product_standard_view      p
 10  ,    geography_regional_view    g
 11  ,    time_calendar_view         t
 12  ,    sales_cube_view            s
 13  WHERE(c.dim_key                  = s.channel
 14  AND   g.dim_key                  = s.geography
 15  AND   p.dim_key                  = s.product
 16  AND   t.dim_key                  = s.time
 17  AND   g.level_name               = 'ALL_REGIONS'
 18  AND   c.level_name               = 'CLASS'
 19  AND   p.level_name               = 'DEPARTMENT'
 20  AND   t.level_name               = 'CALENDAR_QUARTER'
 21  AND   t.calendar_year_long_descr = 'CY2007')
 22  ORDER BY c.class_long_description
 23  ,        p.department_long_descript
 24  ,        t.end_date;

CLASS      DEPT                           QTR                   SALES              YTD YTD_PY_PCT_CHG HOW_IS_SALES_YTD
---------- ------------------------------ ---------- ---------------- ---------------- -------------- --------------------
Direct     Cameras and Camcorders         Q1-CY2007     1,242,385.000    1,242,385.000           1.62 On track
Direct     Cameras and Camcorders         Q2-CY2007     1,125,521.000    2,367,906.000           8.52 On track
Direct     Cameras and Camcorders         Q3-CY2007     1,354,490.000    3,722,396.000          16.21 Outstanding
Direct     Cameras and Camcorders         Q4-CY2007     1,443,028.000    5,165,424.000          18.14 Outstanding
Direct     Computers                      Q1-CY2007    13,917,490.000   13,917,490.000          18.78 Outstanding
Direct     Computers                      Q2-CY2007    11,756,607.000   25,674,097.000          17.92 Outstanding
Direct     Computers                      Q3-CY2007    12,865,030.000   38,539,127.000          15.25 Outstanding
Direct     Computers                      Q4-CY2007    14,308,176.000   52,847,303.000          13.75 On track
Direct     Portable Music and Video       Q1-CY2007     1,945,639.000    1,945,639.000           9.15 On track
Direct     Portable Music and Video       Q2-CY2007     1,666,430.000    3,612,069.000           9.10 On track
Direct     Portable Music and Video       Q3-CY2007     1,812,649.000    5,424,718.000           9.63 On track
Direct     Portable Music and Video       Q4-CY2007     2,045,273.000    7,469,990.000          10.22 On track
Indirect   Cameras and Camcorders         Q1-CY2007     1,719,385.000    1,719,385.000           0.71 On track
Indirect   Cameras and Camcorders         Q2-CY2007     1,573,766.000    3,293,152.000           5.79 On track
Indirect   Cameras and Camcorders         Q3-CY2007     1,837,557.000    5,130,709.000          12.63 On track
Indirect   Cameras and Camcorders         Q4-CY2007     2,097,116.000    7,227,825.000          16.58 Outstanding
Indirect   Computers                      Q1-CY2007    19,859,709.000   19,859,709.000          21.42 Outstanding
Indirect   Computers                      Q2-CY2007    16,824,419.000   36,684,128.000          20.31 Outstanding
Indirect   Computers                      Q3-CY2007    18,117,883.000   54,802,011.000          16.34 Outstanding
Indirect   Computers                      Q4-CY2007    20,257,301.000   75,059,312.000          14.31 On track
Indirect   Portable Music and Video       Q1-CY2007     2,747,134.000    2,747,134.000           9.54 On track
Indirect   Portable Music and Video       Q2-CY2007     2,323,586.000    5,070,720.000           9.99 On track
Indirect   Portable Music and Video       Q3-CY2007     2,500,406.000    7,571,126.000           9.19 On track
Indirect   Portable Music and Video       Q4-CY2007     2,878,119.000   10,449,245.000          10.14 On track

24 Zeilen ausgewählt.

Abgelaufen: 00:00:00.12
SQL>


Beispiel 4: Verwendung unterschiedlicher Gesamtsummen in einer OLAP Cube Abfrage

Im OLAP Cube werden Gesamtsummen auf allen Aggregationsebenen der Dimensionen vorgehalten. Auf diese Weise können in einer Abfrage unterschiedliche Ebenen dargestellt werden. Dadurch ist es in der OLAP Darstellung möglich
  • unabhängig von der Aggregationsebene jede beliebige Dimension auszuwählen
  • komplexe Berechnungsregeln (z. B. Bildung eines Saldo) in der OLAP Engine automatisch aufzulösen

Im folgenden Synstaxbeispiel wird die bereits bekannte Abfrage so erweitert, dass die Werte dreier Ebenen (Monat, Quartal, Kalenderjahr) ausgegeben werden. 
SQL> COLUMN channel FORMAT a10
SQL> COLUMN product FORMAT a30
SQL> COLUMN time    FORMAT a10
SQL> SELECT c.long_description               AS channel
  2  ,      p.long_description               AS product
  3  ,      t.long_description               AS time
  4  ,      round(s.sales)                   AS sales
  5  ,      round(s.sales_ytd)               AS ytd
  6  ,      round(s.sales_ytd_py_pct_chg, 2) AS ytd_py_pct_chg
  7  ,      how_is_sales_ytd
  8  FROM channel_sales_channel_view c
  9  ,    product_standard_view      p
 10  ,    geography_regional_view    g
 11  ,    time_calendar_view         t
 12  ,    sales_cube_view            s
 13  WHERE(c.dim_key          = s.channel
 14  AND   g.dim_key          = s.geography
 15  AND   p.dim_key          = s.product
 16  AND   t.dim_key          = s.TIME
 17  AND   g.level_name       = 'ALL_REGIONS'
 18  AND   c.level_name       = 'CLASS'
 19  AND   p.level_name       = 'DEPARTMENT'
 20  AND   t.long_description in ('CY2007', 'Q3-CY2007', 'Nov-2007'))
 21  ORDER BY channel
 22  ,        product
 23  ,        t.end_date;

CHANNEL    PRODUCT                        TIME                  SALES              YTD YTD_PY_PCT_CHG HOW_IS_SALES_YTD
---------- ------------------------------ ---------- ---------------- ---------------- -------------- --------------------
Direct     Cameras and Camcorders         Q3-CY2007     1,354,490.000    3,722,396.000          16.21 Outstanding
Direct     Cameras and Camcorders         Nov-2007        441,683.000    4,586,706.000          17.46 Outstanding
Direct     Cameras and Camcorders         CY2007        5,165,424.000    5,165,424.000          18.14 Outstanding
Direct     Computers                      Q3-CY2007    12,865,030.000   38,539,127.000          15.25 Outstanding
Direct     Computers                      Nov-2007      4,280,462.000   46,797,775.000          13.72 On track
Direct     Computers                      CY2007       52,847,303.000   52,847,303.000          13.75 On track
Direct     Portable Music and Video       Q3-CY2007     1,812,649.000    5,424,718.000           9.63 On track
Direct     Portable Music and Video       Nov-2007        608,237.000    6,587,010.000           9.08 On track
Direct     Portable Music and Video       CY2007        7,469,990.000    7,469,990.000          10.22 On track
Indirect   Cameras and Camcorders         Q3-CY2007     1,837,557.000    5,130,709.000          12.63 On track
Indirect   Cameras and Camcorders         Nov-2007        662,219.000    6,376,675.000          15.82 Outstanding
Indirect   Cameras and Camcorders         CY2007        7,227,825.000    7,227,825.000          16.58 Outstanding
Indirect   Computers                      Q3-CY2007    18,117,883.000   54,802,011.000          16.34 Outstanding
Indirect   Computers                      Nov-2007      6,105,937.000   66,545,865.000          14.65 On track
Indirect   Computers                      CY2007       75,059,312.000   75,059,312.000          14.31 On track
Indirect   Portable Music and Video       Q3-CY2007     2,500,406.000    7,571,126.000           9.19 On track
Indirect   Portable Music and Video       Nov-2007        918,025.000    9,256,802.000           9.77 On track
Indirect   Portable Music and Video       CY2007       10,449,245.000   10,449,245.000          10.14 On track

18 Zeilen ausgewählt.

Abgelaufen: 00:00:00.09
SQL>
Man sieht hier, dass eine Member-Condition über mehrere Ebenen in der TIME Dimension angewendet wird, also t.long_description in ('CY2007', 'Q3-CY2007', 'Nov-2007').

Beispiel 5: Ausführung einer drill-down Abfrage

Durch die Dimensionshierarchien innerhalb des Datenmodells ist es möglich, ein drill-down durchzuführen, also die Child-Datensätze abzufragen, die zu einem bestimmten Parent-Datensatz gehören. Ein drill-down kann in jedem Typ von Hierarchie ausgeführt werden, unabhängig davon, ob diese Hierarchie z. B. aus aufeinander aufbauenden Ebenen besteht oder die Ebenen unabhängig voneinander sind.

In diesem Beispiel wird die obige Abfrage mit einem drill-down auf den Dimensionen PRODUCT und GEOGRAPHY erweitert
  • drill-down auf ALL_PRODUCT liefert alle Child-Daten, also die Sätze auf der Ebene DEPARTMENT
  • drill-down auf ALL_REGIONS liefert alle Child-Daten, also die Sätze auf der Ebene REGION
  • in der Hierarchie CHANNEL wird weiterhin die oberste Aggregationsebene verwendet und nicht auf die Sätze der Ebene CLASS zugegriffen

SQL> COLUMN geography FORMAT a30
SQL> SELECT g.long_description              AS geography
  2  ,      p.long_description              AS product
  3  ,      t.long_description              AS time
  4  ,      round(s.sales)                  AS sales
  5  ,      round(s.sales_ytd)              AS ytd
  6  ,      round(s.sales_ytd_py_pct_chg,2) AS ytd_py_pct_chg
  7  ,      how_is_sales_ytd
  8  FROM channel_sales_channel_view c
  9  ,    product_standard_view      p
 10  ,    geography_regional_view    g
 11  ,    time_calendar_view         t
 12  ,    sales_cube_view            s
 13  WHERE(c.dim_key          = s.channel
 14  AND   g.dim_key          = s.geography
 15  AND   p.dim_key          = s.product
 16  AND   t.dim_key          = s.TIME
 17  AND   g.parent           = 'ALL_REGIONS'
 18  AND   c.level_name       = 'ALL_CHANNELS'
 19  AND   p.parent           = 'ALL_PRODUCTS'
 20  AND   t.long_description in ('CY2007', 'Q3-CY2007', 'Nov-2007'))
 21  ORDER BY geography
 22  ,        product
 23  ,        t.end_date;

GEOGRAPHY                      PRODUCT                        TIME                  SALES              YTD YTD_PY_PCT_CHG HOW_IS_SALES_YTD
------------------------------ ------------------------------ ---------- ---------------- ---------------- -------------- --------------------
Africa                         Cameras and Camcorders         Q3-CY2007       131,006.000      391,502.000           9.48 On track
Africa                         Cameras and Camcorders         Nov-2007         51,821.000      488,912.000          15.32 Outstanding
Africa                         Cameras and Camcorders         CY2007          542,473.000      542,473.000          14.33 On track
Africa                         Computers                      Q3-CY2007     1,393,278.000    4,088,334.000          11.72 On track
Africa                         Computers                      Nov-2007        454,352.000    4,976,090.000           9.46 On track
Africa                         Computers                      CY2007        5,620,477.000    5,620,477.000           9.26 On track
Africa                         Portable Music and Video       Q3-CY2007       195,573.000      566,383.000           6.44 On track
Africa                         Portable Music and Video       Nov-2007         65,432.000      691,098.000           7.12 On track
Africa                         Portable Music and Video       CY2007          775,816.000      775,816.000           6.47 On track
Asia                           Cameras and Camcorders         Q3-CY2007     1,387,059.000    3,856,158.000          13.40 On track
Asia                           Cameras and Camcorders         Nov-2007        474,724.000    4,777,146.000          15.38 Outstanding
Asia                           Cameras and Camcorders         CY2007        5,407,279.000    5,407,279.000          16.01 Outstanding
Asia                           Computers                      Q3-CY2007    13,781,877.000   41,183,310.000          14.55 On track
Asia                           Computers                      Nov-2007      4,570,693.000   50,032,719.000          12.90 On track
Asia                           Computers                      CY2007       56,543,728.000   56,543,728.000          13.02 On track
Asia                           Portable Music and Video       Q3-CY2007     1,924,032.000    5,744,635.000           8.88 On track
Asia                           Portable Music and Video       Nov-2007        672,656.000    6,998,453.000           9.05 On track
Asia                           Portable Music and Video       CY2007        7,905,262.000    7,905,262.000           9.21 On track
Europe                         Cameras and Camcorders         Q3-CY2007       458,505.000    1,303,749.000          12.79 On track
Europe                         Cameras and Camcorders         Nov-2007        159,295.000    1,612,463.000          14.60 On track
Europe                         Cameras and Camcorders         CY2007        1,840,624.000    1,840,624.000          16.59 Outstanding
Europe                         Computers                      Q3-CY2007     4,587,126.000   13,996,610.000          14.51 On track
Europe                         Computers                      Nov-2007      1,560,431.000   16,982,014.000          13.02 On track
Europe                         Computers                      CY2007       19,210,536.000   19,210,536.000          13.33 On track
Europe                         Portable Music and Video       Q3-CY2007       653,754.000    1,967,518.000           8.82 On track
Europe                         Portable Music and Video       Nov-2007        236,497.000    2,394,375.000           8.68 On track
Europe                         Portable Music and Video       CY2007        2,720,976.000    2,720,976.000          11.04 On track
North America                  Cameras and Camcorders         Q3-CY2007       818,281.000    2,230,612.000          12.53 On track
North America                  Cameras and Camcorders         Nov-2007        299,825.000    2,781,832.000          16.32 Outstanding
North America                  Cameras and Camcorders         CY2007        3,144,918.000    3,144,918.000          17.26 Outstanding
North America                  Computers                      Q3-CY2007     7,794,181.000   23,678,125.000          18.56 Outstanding
North America                  Computers                      Nov-2007      2,685,137.000   28,812,281.000          17.40 Outstanding
North America                  Computers                      CY2007       32,455,561.000   32,455,561.000          16.78 Outstanding
North America                  Portable Music and Video       Q3-CY2007     1,065,079.000    3,276,365.000          10.77 On track
North America                  Portable Music and Video       Nov-2007        380,151.000    4,000,738.000          10.98 On track
North America                  Portable Music and Video       CY2007        4,526,968.000    4,526,968.000          11.63 On track
Oceania                        Cameras and Camcorders         Q3-CY2007         2,852.000        5,611.000          13.45 On track
Oceania                        Cameras and Camcorders         Nov-2007            275.000        6,594.000           3.54 On track
Oceania                        Cameras and Camcorders         CY2007            7,734.000        7,734.000          -0.36 Needs Improvement
Oceania                        Computers                      Q3-CY2007        28,396.000       61,568.000          -0.92 Needs Improvement
Oceania                        Computers                      Nov-2007          2,098.000       67,790.000         -17.36 Needs Improvement
Oceania                        Computers                      CY2007           76,323.000       76,323.000         -15.10 Needs Improvement
Oceania                        Portable Music and Video       Q3-CY2007         2,859.000        8,785.000         -15.98 Needs Improvement
Oceania                        Portable Music and Video       Nov-2007            440.000       10,280.000         -18.62 Needs Improvement
Oceania                        Portable Music and Video       CY2007           12,134.000       12,134.000          -9.70 Needs Improvement
South America                  Cameras and Camcorders         Q3-CY2007       394,343.000    1,065,473.000          24.28 Outstanding
South America                  Cameras and Camcorders         Nov-2007        117,963.000    1,296,432.000          24.47 Outstanding
South America                  Cameras and Camcorders         CY2007        1,450,221.000    1,450,221.000          24.15 Outstanding
South America                  Computers                      Q3-CY2007     3,398,055.000   10,333,191.000          19.10 Outstanding
South America                  Computers                      Nov-2007      1,113,688.000   12,472,746.000          16.74 Outstanding
South America                  Computers                      CY2007       13,999,990.000   13,999,990.000          15.55 Outstanding
South America                  Portable Music and Video       Q3-CY2007       471,759.000    1,432,158.000          10.34 On track
South America                  Portable Music and Video       Nov-2007        171,085.000    1,748,868.000          10.14 On track
South America                  Portable Music and Video       CY2007        1,978,079.000    1,978,079.000          11.27 On track

54 Zeilen ausgewählt.

Abgelaufen: 00:00:00.29
SQL>
Einige Anmerkungen zu der Abfrage in diesem Beispiel
  • Die Hierarchie GEOGRAPHY wird in der SELECT-Liste verwendet, die Hierarchie CHANNEL hingegen nicht. Aus diesem Grund muss die CHANNEL Dimension mit der "ALL_CHANNELS" Bedingung versehen werden.
  • Der drill-down auf der Dimension GEOGRAPHY wird ausgeführt, indem der Wert der Spalte PARENT in der Hierarchie-View abgefragt wird: g.parent = 'ALL_REGIONS'
  • Der drill-down auf der Dimension PRODUCT wird ausgeführt, indem der Wert der Spalte PARENT in der Hierarchie-View abgefragt wird: p.parent = 'ALL_PRODUCTS'


Beispiel 6: Verwendung von Parametern zum drill-down

Eine Parametrisierung des drill-down kann über einen Substitutions-Parameter sehr einfach umgesetzt werden. In der folgenden Abfrage wird der Substitutions-Parameter genutzt, um einen gültigen Wert für die Dimension TIME eingeben zu können. 
SQL> VARIABLE time_parent VARCHAR2(10);
SQL> BEGIN
  2   :time_parent:=NULL;
  3  END;
  4  /

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.01
SQL> SELECT c.long_description               AS channel
  2  ,      p.long_description               AS product
  3  ,      t.long_description               AS time
  4  ,      round(s.sales)                   AS sales
  5  ,      round(s.sales_ytd)               AS ytd
  6  ,      round(s.sales_ytd_py_pct_chg, 2) AS ytd_PY_pct_chg
  7  ,      how_is_sales_ytd
  8  FROM channel_sales_channel_view c
  9  ,    product_standard_view      p
 10  ,    geography_regional_view    g
 11  ,    time_calendar_view         t
 12  ,    sales_cube_view            s
 13  WHERE(c.dim_key    = s.channel
 14  AND   g.dim_key    = s.geography
 15  AND   p.dim_key    = s.product
 16  AND   t.dim_key    = s.time
 17  AND   c.parent     = 'ALL_CHANNELS'
 18  AND   g.level_name = 'ALL_REGIONS'
 19  AND   p.parent     = 'ALL_PRODUCTS'
 20  AND   t.parent     = nvl(:time_parent, 'ALL_YEARS'))
 21  ORDER BY channel
 22  ,        product
 23  ,        t.end_date;

CHANNEL    PRODUCT                        TIME                  SALES              YTD YTD_PY_PCT_CHG HOW_IS_SALES_YTD
---------- ------------------------------ ---------- ---------------- ---------------- -------------- --------------------
Direct     Cameras and Camcorders         CY2005        3,709,301.000    3,709,301.000
Direct     Cameras and Camcorders         CY2006        4,372,207.000    4,372,207.000          17.87 Outstanding
Direct     Cameras and Camcorders         CY2007        5,165,424.000    5,165,424.000          18.14 Outstanding
Direct     Computers                      CY2005       39,562,142.000   39,562,142.000
Direct     Computers                      CY2006       46,459,972.000   46,459,972.000          17.44 Outstanding
Direct     Computers                      CY2007       52,847,303.000   52,847,303.000          13.75 On track
Direct     Portable Music and Video       CY2005        6,209,045.000    6,209,045.000
Direct     Portable Music and Video       CY2006        6,777,315.000    6,777,315.000           9.15 On track
Direct     Portable Music and Video       CY2007        7,469,990.000    7,469,990.000          10.22 On track
Indirect   Cameras and Camcorders         CY2005        5,145,600.000    5,145,600.000
Indirect   Cameras and Camcorders         CY2006        6,199,891.000    6,199,891.000          20.49 Outstanding
Indirect   Cameras and Camcorders         CY2007        7,227,825.000    7,227,825.000          16.58 Outstanding
Indirect   Computers                      CY2005       56,816,124.000   56,816,124.000
Indirect   Computers                      CY2006       65,663,836.000   65,663,836.000          15.57 Outstanding
Indirect   Computers                      CY2007       75,059,312.000   75,059,312.000          14.31 On track
Indirect   Portable Music and Video       CY2005        8,893,545.000    8,893,545.000
Indirect   Portable Music and Video       CY2006        9,486,938.000    9,486,938.000           6.67 On track
Indirect   Portable Music and Video       CY2007       10,449,245.000   10,449,245.000          10.14 On track

18 Zeilen ausgewählt.

Abgelaufen: 00:00:00.14
SQL>
Einige Anmerkungen zu der Abfrage in diesem Beispiel
  • Die Hierarchie GEOGRAPHY wurde entfernt, dafür wurde die Dimension CHANNEL eingefügt. Daher muss eine "ALL_REGIONS" Bedingung für die Dimension eingesetzt werden.
  • Der drill-down auf den Dimensionen CHANNEL und PRODUCT erfolgt wie bereist oben beschrieben.
  • Zusätzlich wird ein parametrisierter drill-down auf der TIME Dimension durchgeführt. Um Fehler abzufangen, wird die Funktion nvl benutzt, die sicherstellt, dass mindestens der Wert "ALL_YEARS" als Parent verwended wird.

Die Abfrage zeigt die Aggregation für alle Kalenderjahre im OLAP Cube, da der Parameter den Wert NULL hat und somit "ALL_YEARS" als Einschränkung der TIME Dimension angenommen wird. Für das Jahr 2005, das das erste Jahr im OLAP Cube ist, gibt es keine Vorjahreswerte. Aus diesem Grunde sind die auf YTD basierenden Berechnungen leer.

Im letzten Beispiel wird als Wert für den Substitutionsparameter CY2007 angenommen, so dass das Ergebnis dann alle Child-Datensätze enthält, hier also die einzelnen Quartale des Kalenderjahres 2007. 
SQL> BEGIN
  2   :time_parent:='CY2007';
  3  END;
  4  /

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.00
SQL> SELECT c.long_description               AS channel
  2  ,      p.long_description               AS product
  3  ,      t.long_description               AS time
  4  ,      round(s.sales)                   AS sales
  5  ,      round(s.sales_ytd)               AS ytd
  6  ,      round(s.sales_ytd_py_pct_chg, 2) AS ytd_py_pct_chg
  7  ,      how_is_sales_ytd
  8  FROM channel_sales_channel_view c
  9  ,    product_standard_view      p
 10  ,    geography_regional_view    g
 11  ,    time_calendar_view         t
 12  ,    sales_cube_view            s
 13  WHERE(c.dim_key    = s.channel
 14  AND   g.dim_key    = s.geography
 15  AND   p.dim_key    = s.product
 16  AND   t.dim_key    = s.time
 17  AND   c.parent     = 'ALL_CHANNELS'
 18  AND   g.level_name = 'ALL_REGIONS'
 19  AND   p.parent     = 'ALL_PRODUCTS'
 20  AND   t.parent     = nvl(:time_parent, 'ALL_YEARS'))
 21  ORDER BY channel
 22  ,        product
 23  ,        t.end_date;

CHANNEL    PRODUCT                        TIME                  SALES              YTD YTD_PY_PCT_CHG HOW_IS_SALES_YTD
---------- ------------------------------ ---------- ---------------- ---------------- -------------- --------------------
Direct     Cameras and Camcorders         Q1-CY2007     1,242,385.000    1,242,385.000           1.62 On track
Direct     Cameras and Camcorders         Q2-CY2007     1,125,521.000    2,367,906.000           8.52 On track
Direct     Cameras and Camcorders         Q3-CY2007     1,354,490.000    3,722,396.000          16.21 Outstanding
Direct     Cameras and Camcorders         Q4-CY2007     1,443,028.000    5,165,424.000          18.14 Outstanding
Direct     Computers                      Q1-CY2007    13,917,490.000   13,917,490.000          18.78 Outstanding
Direct     Computers                      Q2-CY2007    11,756,607.000   25,674,097.000          17.92 Outstanding
Direct     Computers                      Q3-CY2007    12,865,030.000   38,539,127.000          15.25 Outstanding
Direct     Computers                      Q4-CY2007    14,308,176.000   52,847,303.000          13.75 On track
Direct     Portable Music and Video       Q1-CY2007     1,945,639.000    1,945,639.000           9.15 On track
Direct     Portable Music and Video       Q2-CY2007     1,666,430.000    3,612,069.000           9.10 On track
Direct     Portable Music and Video       Q3-CY2007     1,812,649.000    5,424,718.000           9.63 On track
Direct     Portable Music and Video       Q4-CY2007     2,045,273.000    7,469,990.000          10.22 On track
Indirect   Cameras and Camcorders         Q1-CY2007     1,719,385.000    1,719,385.000           0.71 On track
Indirect   Cameras and Camcorders         Q2-CY2007     1,573,766.000    3,293,152.000           5.79 On track
Indirect   Cameras and Camcorders         Q3-CY2007     1,837,557.000    5,130,709.000          12.63 On track
Indirect   Cameras and Camcorders         Q4-CY2007     2,097,116.000    7,227,825.000          16.58 Outstanding
Indirect   Computers                      Q1-CY2007    19,859,709.000   19,859,709.000          21.42 Outstanding
Indirect   Computers                      Q2-CY2007    16,824,419.000   36,684,128.000          20.31 Outstanding
Indirect   Computers                      Q3-CY2007    18,117,883.000   54,802,011.000          16.34 Outstanding
Indirect   Computers                      Q4-CY2007    20,257,301.000   75,059,312.000          14.31 On track
Indirect   Portable Music and Video       Q1-CY2007     2,747,134.000    2,747,134.000           9.54 On track
Indirect   Portable Music and Video       Q2-CY2007     2,323,586.000    5,070,720.000           9.99 On track
Indirect   Portable Music and Video       Q3-CY2007     2,500,406.000    7,571,126.000           9.19 On track
Indirect   Portable Music and Video       Q4-CY2007     2,878,119.000   10,449,245.000          10.14 On track

24 Zeilen ausgewählt.

Abgelaufen: 00:00:00.21
SQL>


Im zweiten Teil dieses Community-Tipps, der schon in 14 Tagen erscheinen wird, erfahren Sie, wie Cube-oriented Materialized Views eingerichtet werden. Weiterhin wird anhand von Beispielen gezeigt, wie durch deren Einsatz die Abfrageperformance gesteigert werden kann.

Zurück zur Community-Seite