| Oracle® OLAPユーザーズ・ガイド 11g リリース2（11.2） B61345-01 |
|
 戻る |
 次へ |
Oracle OLAPでは多数の分析コンテンツが用意されており、問合せに対する応答も高速なため、お使いのSQLアプリケーションを強化できます。SQL問合せのインタフェースにより、OLAPの知識がなくても、すべてのアプリケーションでキューブやディメンションへの問合せを実行できます。
OLAPオプションは、キューブ、ディメンションおよび階層のリレーショナル・ビューを自動的に生成します。SQLアプリケーションでこれらのビューを問い合せることにより、分析担当者や意思決定者はこれらのオブジェクトの情報量に富んだ内容を表示できます。また、実表などのシステム生成ビューを使用して、お使いのアプリケーションが求める構造に従ったカスタム・ビューを作成できます。
この章では、SQLで次元オブジェクトを問い合せるための基本的な方法を説明します。この章では、次の項目について説明します。
|
参照:
|
システム生成ビューはアナリティック・ワークスペースと同じスキーマに作成されます。Oracle OLAPでは、次の3種類のビューを使用できます。
キューブ・ビュー
ディメンション・ビュー
階層ビュー
これらのビューは、スター・スキーマにおけるファクト表とディメンション表と同様の関係にあります。キューブ・ビューはファクト表と同じ機能を持ち、階層ビューとディメンション・ビューはディメンション表と同様に機能します。通常の問合せでは、階層ビューまたはディメンション・ビューのいずれかとキューブ・ビューを結合します。
各キューブには、キューブ内のすべてのメジャーと計算済メジャーのデータを示すキューブ・ビューが1つあります。キューブ・ビューは、スター・スキーマまたはスノーフレーク・スキーマ内のファクト表のように使用できます。ただし、キューブ・ビューには、ディテール・レベルのデータの他にすべてのサマリー・データも含まれます。
キューブ・ビューのデフォルトの名前はcube_VIEWです。スキーマ内のUNITS_CUBEのビューを検索するには、次のような問合せを発行します。
SELECT view_name FROM user_views WHERE view_name LIKE 'UNITS_CUBE%'; VIEW_NAME ------------------------------ UNITS_CUBE_VIEW
次の問合せを発行すると、USER_CUBE_VIEWSからスキーマ内のすべてのキューブ・ビューの名前が戻されます。
SELECT view_name FROM user_cube_views; VIEW_NAME ------------------------------ UNITS_CUBE_VIEW PRICE_CUBE_VIEW
ファクト表と同様、キューブ・ビューには、キューブ内のメジャー、計算済メジャーおよびディメンションそれぞれに対応する列が含まれています。次の例のUNITS_CUBE_VIEWには、SALES、UNITS、COSTの3つのメジャー、SALESの複数の計算済メジャー、およびTIME、CUSTOMER、PRODUCT、CHANNELの各ディメンションを表す列があります。
DESCRIBE units_cube_view Name Null? Type ----------------------------------------- -------- ---------------------------- SALES NUMBER UNITS NUMBER COST NUMBER SALES_PP NUMBER SALES_CHG_PP NUMBER SALES_PCTCHG_PP NUMBER SALES_PROD_SHARE_PARENT NUMBER SALES_PROD_SHARE_TOTAL NUMBER SALES_PROD_RANK_PARENT_PP NUMBER TIME VARCHAR2(100) CUSTOMER VARCHAR2(100) PRODUCT VARCHAR2(100) CHANNEL VARCHAR2(100)
次の問合せのように、USER_CUBE_VIEW_COLUMNSデータ・ディクショナリ・ビューを問い合せるとキューブ・ビューの列が戻されます。
SELECT column_name, column_type FROM user_cube_view_columns
WHERE view_name = 'UNITS_CUBE_VIEW';
COLUMN_NAME COLUMN_TYPE
------------------------------ --------------
SALES MEASURE
UNITS MEASURE
COST MEASURE
SALES_PP MEASURE
SALES_CHG_PP MEASURE
SALES_PCTCHG_PP MEASURE
SALES_PROD_SHARE_PARENT MEASURE
SALES_PROD_SHARE_TOTAL MEASURE
SALES_PROD_RANK_PARENT_PP MEASURE
TIME KEY
CUSTOMER KEY
PRODUCT KEY
CHANNEL KEY
13 rows selected.
次のような問合せを発行すると、キューブ・ビューの内容をすぐに表示できます。キューブには、ディテール・レベルから最上位レベルに至るすべてのレベルのデータが格納されています。
SELECT sales, units, time, customer, product, channel
FROM units_cube_view WHERE ROWNUM < 15;
SALES UNITS TIME CUSTOMER PRODUCT CHANNEL
---------- ---------- ---------- ---------- ---------- --------
1120292752 4000968 TOTAL TOTAL TOTAL TOTAL
134109248 330425 CY1999 TOTAL TOTAL TOTAL
130276514 534069 CY2003 TOTAL TOTAL TOTAL
100870877 253816 CY1998 TOTAL TOTAL TOTAL
136986572 565718 CY2005 TOTAL TOTAL TOTAL
140138317 584929 CY2006 TOTAL TOTAL TOTAL
144290686 587419 CY2004 TOTAL TOTAL TOTAL
124173522 364233 CY2000 TOTAL TOTAL TOTAL
92515295 364965 CY2002 TOTAL TOTAL TOTAL
116931722 415394 CY2001 TOTAL TOTAL TOTAL
31522409.5 88484 CY2000.Q1 TOTAL TOTAL TOTAL
27798426.6 97346 CY2001.Q2 TOTAL TOTAL TOTAL
29691668.2 105704 CY2001.Q3 TOTAL TOTAL TOTAL
32617248.6 138953 CY2005.Q3 TOTAL TOTAL TOTAL
14 rows selected.
各ディメンションには、ディメンション・ビューが1つと、そのディメンションに関連付けられている各階層の階層ビューがあります。たとえば、Timeディメンションは、次の3つのビューを持つ場合があります。
Time（時間）ディメンション・ビュー
Calendar（暦年）階層ビュー
Fiscal（会計年度）階層ビュー
ディメンション・ビューと階層ビューは、スター・スキーマのディメンション表のように使用できます。
USER_CUBE_DIM_VIEWSでは、すべてのディメンションのディメンション・ビューを確認できます。ディメンション・ビューのデフォルトの名前はdimension_VIEWです。
SELECT * FROM user_cube_dim_views; DIMENSION_NAME VIEW_NAME ------------------------------ ------------------------------ PRODUCT PRODUCT_VIEW CUSTOMER CUSTOMER_VIEW CHANNEL CHANNEL_VIEW TIME TIME_VIEW
USER_CUBE_HIER_VIEWSでは、すべてのディメンションの階層ビューを確認できます。階層ビューのデフォルトの名前はdimension_hierarchy_VIEWです。次の問合せを発行すると、ディメンション名、階層名およびビュー名が戻されます。
SELECT * FROM user_cube_hier_views ORDER BY dimension_name; DIMENSION_NAME HIERARCHY_NAME VIEW_NAME --------------- --------------- ------------------------------ CHANNEL PRIMARY CHANNEL_PRIMARY_VIEW CUSTOMER SEGMENT CUSTOMER_SEGMENT_VIEW CUSTOMER SHIPMENTS CUSTOMER_SHIPMENTS_VIEW PRODUCT PRIMARY PRODUCT_PRIMARY_VIEW TIME FISCAL TIME_FISCAL_VIEW TIME CALENDAR TIME_CALENDAR_VIEW
ディメンション表と同様、ディメンション・ビューにはキー列、レベル名、ディメンションと関連付けられた各階層のすべてのレベルのレベル・キー、および属性列が含まれます。次の例のTIME_VIEWには、ディメンション・キー、レベル名、ディメンション属性を表す列があります。
DESCRIBE time_view Name Null? Type ----------------------------------------- -------- ---------------------------- DIM_KEY VARCHAR2(100) LEVEL_NAME VARCHAR2(30) DIM_ORDER NUMBER END_DATE DATE LONG_DESCRIPTION VARCHAR2(100) SHORT_DESCRIPTION VARCHAR2(100) TIME_SPAN NUMBER
次の問合せのように、USER_CUBE_DIM_VIEW_COLUMNSを問い合せると各列の情報が戻されます。
SELECT column_name, column_type FROM user_cube_dim_view_columns
WHERE view_name ='TIME_VIEW';
COLUMN_NAME COLUMN_TYPE
------------------------------ --------------------
DIM_KEY KEY
LEVEL_NAME LEVEL_NAME
DIM_ORDER DIM_ORDER
END_DATE ATTRIBUTE
TIME_SPAN ATTRIBUTE
LONG_DESCRIPTION ATTRIBUTE
SHORT_DESCRIPTION ATTRIBUTE
次の問合せを発行すると、各ディメンション・キーのレベルと属性が表示されます。
SELECT dim_key, level_name, long_description description, time_span, end_date
FROM time_view WHERE dim_key LIKE '%2005%';
DIM_KEY LEVEL_NAME DESCRIPTION TIME_SPAN END_DATE
------------ -------------------- ------------ ---------- ---------
CY2005 CALENDAR_YEAR 2005 365 31-DEC-05
CY2005.Q2 CALENDAR_QUARTER Q2.05 91 30-JUN-05
CY2005.Q4 CALENDAR_QUARTER Q4.05 92 31-DEC-05
CY2005.Q3 CALENDAR_QUARTER Q3.05 92 30-SEP-05
CY2005.Q1 CALENDAR_QUARTER Q1.05 90 31-MAR-05
2005.01 MONTH JAN-05 31 31-JAN-05
2005.05 MONTH MAY-05 31 31-MAY-05
2005.07 MONTH JUL-05 31 31-JUL-05
2005.03 MONTH MAR-05 31 31-MAR-05
2005.04 MONTH APR-05 30 30-APR-05
2005.08 MONTH AUG-05 31 31-AUG-05
2005.09 MONTH SEP-05 30 30-SEP-05
2005.02 MONTH FEB-05 28 28-FEB-05
2005.11 MONTH NOV-05 30 30-NOV-05
2005.06 MONTH JUN-05 30 30-JUN-05
2005.10 MONTH OCT-05 31 31-OCT-05
2005.12 MONTH DEC-05 31 31-DEC-05
FY2005 FISCAL_YEAR FY2005 365 30-JUN-05
FY2005.Q4 FISCAL_QUARTER Q4 FY-05 91 30-JUN-05
FY2005.Q1 FISCAL_QUARTER Q1 FY-05 92 30-SEP-04
FY2005.Q2 FISCAL_QUARTER Q2 FY-05 92 31-DEC-04
FY2005.Q3 FISCAL_QUARTER Q3 FY-05 90 31-MAR-05
22 rows selected.
ディメンション・ビューと同じく、階層ビューにもディメンション・キー、レベル名およびレベル・キーが含まれています。ただし、行と列はすべて、階層に属するディメンション・キーと関連付けられています。
DESCRIBE time_calendar_view Name Null? Type ----------------------------------------- -------- ---------------------------- DIM_KEY VARCHAR2(100) LEVEL_NAME VARCHAR2(30) DIM_ORDER NUMBER HIER_ORDER NUMBER LONG_DESCRIPTION VARCHAR2(100) SHORT_DESCRIPTION VARCHAR2(100) END_DATE DATE TIME_SPAN NUMBER PARENT VARCHAR2(100) TOTAL VARCHAR2(100) CALENDAR_YEAR VARCHAR2(100) CALENDAR_QUARTER VARCHAR2(100) MONTH VARCHAR2(100)
次の問合せを発行すると、ディメンション・キー、親キー、および2005年（暦年）の全親子関係（祖先、子孫を含む）が表示されます。
SELECT dim_key, long_description description, parent, calendar_year year,
calendar_quarter quarter, month FROM time_calendar_view
WHERE calendar_year='CY2005'
ORDER BY level_name, end_date;
DIM_KEY DESCRIPTION PARENT YEAR QUARTER MONTH
------------ ------------ ------------ ------------ ------------ ------------
CY2005.Q1 Q1.05 CY2005 CY2005 CY2005.Q1
CY2005.Q2 Q2.05 CY2005 CY2005 CY2005.Q2
CY2005.Q3 Q3.05 CY2005 CY2005 CY2005.Q3
CY2005.Q4 Q4.05 CY2005 CY2005 CY2005.Q4
CY2005 2005 TOTAL CY2005
2005.01 JAN-05 CY2005.Q1 CY2005 CY2005.Q1 2005.01
2005.02 FEB-05 CY2005.Q1 CY2005 CY2005.Q1 2005.02
2005.03 MAR-05 CY2005.Q1 CY2005 CY2005.Q1 2005.03
2005.04 APR-05 CY2005.Q2 CY2005 CY2005.Q2 2005.04
2005.05 MAY-05 CY2005.Q2 CY2005 CY2005.Q2 2005.05
2005.06 JUN-05 CY2005.Q2 CY2005 CY2005.Q2 2005.06
2005.07 JUL-05 CY2005.Q3 CY2005 CY2005.Q3 2005.07
2005.08 AUG-05 CY2005.Q3 CY2005 CY2005.Q3 2005.08
2005.09 SEP-05 CY2005.Q3 CY2005 CY2005.Q3 2005.09
2005.10 OCT-05 CY2005.Q4 CY2005 CY2005.Q4 2005.10
2005.11 NOV-05 CY2005.Q4 CY2005 CY2005.Q4 2005.11
2005.12 DEC-05 CY2005.Q4 CY2005 CY2005.Q4 2005.12
17 rows selected.
キューブに対する問合せは、スター・スキーマに対する問合せと似ています。スター・スキーマの場合は、ファクト表とディメンション表を結合します。ファクト表にはビジネス・メジャー（数値）、ディメンション表にはデータに意味を与える説明的な属性が格納されています。同様に、キューブ・ビューをディメンション・ビューまたは階層ビューのいずれかと結合することによって、完全に識別可能な意味のあるデータを利用できます。
階層を持たないディメンションの場合は、ディメンション・ビューを問合せで使用します。階層を持つディメンションの場合は階層ビューを使用します。これは、ディメンション・ビューよりも階層ビューの方が多くの情報を含んでいるためです。
キューブを問い合せるときは、次のガイドラインに従ってください。
すべてのディメンションにフィルタを適用する。
キューブにはディテール・レベル・データと集計データの両方が含まれています。問合せでフィルタを適用しないディメンションを使用すると、一般にユーザーが必要とするよりも多くのデータが戻されるため、パフォーマンスが低下します。
キューブによるデータの集計を許可する。
キューブ内では集計値が計算されているため、通常の問合せではGROUP BY句は必要ありません。ディメンション・キーまたは属性に対して適切なフィルタを使用し、必要な集計値を選択してください。
レベル・フィルタを作成する場合、ディメンション・レベルの名前が必要になります。これらは、ディメンション・ビューまたは階層ビューを問い合せることにより簡単に取得できます。
SELECT DISTINCT level_name FROM time_calendar_view; LEVEL_NAME ------------------------------ CALENDAR_YEAR CALENDAR_QUARTER MONTH TOTAL
一部のデータ・ディクショナリ・ビューでもレベルの名前を確認できます。次の例では、USER_CUBE_HIER_LEVELSを問い合せています。
SELECT level_name FROM user_cube_hier_levels
WHERE dimension_name = 'TIME' AND hierarchy_name ='CALENDAR';
LEVEL_NAME
--------------------
TOTAL
CALENDAR_YEAR
CALENDAR_QUARTER
MONTH
すべてのディメンションにフィルタを適用することの重要性を理解するために、Timeディメンションにフィルタを適用していない例4-1の問合せについて考えてみます。
例4-1 Timeのすべてのレベルでの集計の表示
/* Select key descriptions and facts */
SELECT t.long_description time,
ROUND(f.sales) sales
/* From dimension views and cube view */
FROM time_calendar_view t,
product_primary_view p,
customer_shipments_view cu,
channel_primary_view ch,
units_cube_view f
/* No filter on Time */
WHERE p.level_name = 'TOTAL'
AND cu.level_name = 'TOTAL'
AND ch.level_name = 'TOTAL'
/* Join dimension views to cube view */
AND t.dim_key = f.time
AND p.dim_key = f.product
AND cu.dim_key = f.customer
AND ch.dim_key = f.channel
ORDER BY t.end_date;
Timeディメンションにフィルタを適用しないと、Timeのすべてのレベルの値が戻されます。ユーザーが通常必要とするよりも多数のデータが戻されるため、パフォーマンスが低下する可能性があります。
TIME SALES
---------- ----------
JAN-98 8338545
FEB-98 7972132
Q1.98 24538588
MAR-98 8227911
APR-98 8470315
MAY-98 8160573
JUN-98 8362386
Q2.98 24993273
JUL-98 8296226
AUG-98 8377587
SEP-98 8406728
Q3.98 25080541
OCT-98 8316169
NOV-98 8984156
Q4.98 26258474
1998 100870877
.
.
.
次に、Timeを年レベルのデータに制限するフィルタを適用したときの結果を見てみます。
例4-2は、基本的な問合せの例です。ここでは、UNITS_CUBE_VIEWからSalesメジャーを選択し、このキューブ・ビューのキーを階層ビューに結合してキーの説明を取得します。
例4-2 キューブ・ビューの基本的な問合せ
/* Select key descriptions and facts */
SELECT t.long_description time,
ROUND(f.sales) sales
/* From dimension views and cube view */
FROM time_calendar_view t,
product_primary_view p,
customer_shipments_view cu,
channel_primary_view ch,
units_cube_view f
/* Create level filters */
WHERE t.level_name = 'CALENDAR_YEAR'
AND p.level_name = 'TOTAL'
AND cu.level_name = 'TOTAL'
AND ch.level_name = 'TOTAL'
/* Join dimension views to cube view */
AND t.dim_key = f.time
AND p.dim_key = f.product
AND cu.dim_key = f.customer
AND ch.dim_key = f.channel
ORDER BY t.end_date;
例4-2を実行すると、次の行が選択されます。CUSTOMER、PRODUCTおよびCHANNELについては最上位レベルの1つの値のみ戻されます。TIMEでは各歴年の値が戻されます。
TIME SALES -------- ---------- 1998 100870877 1999 134109248 2000 124173522 2001 116931722 2002 92515295 2003 130276514 2004 144290686 2005 136986572 2006 140138317
ディメンション属性を使用して、問合せ用のデータを選択することもできます。例4-3のWHERE句では、すべてのディメンションをフィルタするために、属性値を使用しています。
例4-3 属性フィルタによるデータの選択
/* Select key descriptions and facts */
SELECT t.long_description time,
p.long_description product,
cu.long_description customer,
ch.long_description channel,
ROUND(f.sales) sales
/* From dimension views and cube view */
FROM time_calendar_view t,
product_primary_view p,
customer_shipments_view cu,
channel_primary_view ch,
units_cube_view f
/* Create attribute filters */
WHERE t.long_description in ('2005', '2006')
AND p.package = 'Laptop Value Pack'
AND cu.long_description LIKE '%Boston%'
AND ch.long_description = 'Internet'
/* Join dimension views to cube view */
AND t.dim_key = f.time
AND p.dim_key = f.product
AND cu.dim_key = f.customer
AND ch.dim_key = f.channel
ORDER BY time, customer;
この問合せでは、2つの暦年、Laptop Value Packの製品、ボストンの顧客、インターネット・チャネルが選択されます。
TIME PRODUCT CUSTOMER CHANNEL SALES ------ ------------------------------ --------------------- -------- ---------- 2005 Laptop carrying case KOSH Entrpr Boston Internet 5936 2005 56Kbps V.92 Type II Fax/Modem KOSH Entrpr Boston Internet 45285 2005 Internal 48X CD-ROM KOSH Entrpr Boston Internet 2828 2005 Standard Mouse KOSH Entrpr Boston Internet 638 2005 Envoy Standard Warren Systems Boston Internet 19359 2005 Laptop carrying case Warren Systems Boston Internet 13434 2005 Standard Mouse Warren Systems Boston Internet 130 2006 Standard Mouse KOSH Entrpr Boston Internet 555 2006 Laptop carrying case KOSH Entrpr Boston Internet 6357 2006 56Kbps V.92 Type II Fax/Modem KOSH Entrpr Boston Internet 38042 2006 Internal 48X CD-ROM KOSH Entrpr Boston Internet 3343 2006 Envoy Standard Warren Systems Boston Internet 24198 2006 Laptop carrying case Warren Systems Boston Internet 13153 2006 Standard Mouse Warren Systems Boston Internet 83 14 rows selected.
キューブにはすべての集計データが含まれています。この章で説明しているとおり、キューブに対する問合せでは集計データの選択のみを行います。集計値の計算は行われません。
ファクト表に対する基本的な問合せを次に示します。
/* Querying a fact table */
SELECT t.calendar_year_dsc time,
SUM(f.sales) sales
FROM time_dim t, units_fact f
WHERE t.calendar_year_dsc IN ('2005', '2006')
AND t.month_id = f.month_id
GROUP BY t.calendar_year_dsc;
次の問合せは、フィルタを使用してキューブからまったく同じ結果をフェッチします。
/* Querying a cube */
SELECT t.long_description time, f.sales sales
FROM time_calendar_view t,
product_primary_view p,
customer_shipments_view cu,
channel_primary_view ch,
units_cube_view f
/* Apply filters to every dimension */
WHERE t.long_description IN ('2005', '2006')
AND p.level_name = 'TOTAL'
AND cu.level_name = 'TOTAL'
AND ch.level_name = 'TOTAL'
/* Join dimension views to cube view */
AND t.dim_key = f.TIME
AND p.dim_key = f.product
AND cu.dim_key = f.customer
AND ch.dim_key = f.channel
ORDER BY time;
どちらの問合せでも、次の結果が戻されます。
TIME SALES ----- ---------- 2005 136986572 2006 140138317
キューブに対する問合せでは、SUM演算子とGROUP BY句を使用した集計値の計算は行いません。キューブ内には集計値が存在するので、集計済のデータを再集計することになるためです。かわりに、この問合せではキューブから直接集計値を選択し、各ディメンションに適切なフィルタを適用することにより、必要な集計値を指定しています。
最も効率的な問合せでは、OLAPエンジンでデータをフィルタして、問合せで必要な最小行数がSQLに戻されるようにします。
次の演算子は、WHERE句内で使用されるもので、処理用にOLAPエンジンに送られます。
=
!=
>
!>
<
!<
IN
NOT IN
IS NULL
LIKE
NOT LIKE
OLAPエンジンは、ネストした文字関数（INSTR、LENGTH、NVL、LOWER、UPPER、LTRIM、RTRIM、TRIM、LPAD、RPAD、SUBSTR）も処理します。
WHERE句内のその他の演算子と関数、およびSELECT構文の他の部分で使用される演算子は、SQLで処理されます。
ドリル操作は、ビジネス分析における重要な機能の1つです。ダッシュボードやアプリケーションでは、ユーザーはディメンション・キーをクリックすることによってデータの選択を変更できます。また、意思決定者がよく行うのは、特定のデータ値の要因を調べるためのドリルダウンや、特定のデータ値がデータ全体にどのように影響しているかを確認するためのドリルアップです。たとえば、ボストン地区の販売部長の場合、ボストン全体の売上から開始して、各販売担当者の貢献度を見るためにドリルダウンした後、ボストン地区のニュー・イングランド全体の売上に対する貢献度を調べるためにドリルアップすることがあります。
階層ビューにはPARENT列があり、これによってすべてのディメンション・キーの親が示されます。この列には、ディメンションのすべての階層情報が要約されています。つまり、すべてのキーの親がわかっていれば、その祖先、子、子孫も導き出すことができます。
レベルベース階層の場合は、LEVEL_NAME列がこの情報を補います。階層の同じ深さ（最上位レベルからベース・レベルまで）にあるすべてのキーを識別できるため便利です。値ベース階層の場合は、PARENT列が階層に関するすべての情報を提供します。
階層ビューのPARENT列を使用して、特定の値に対する子のみを選択できます。次のWHERE句は、暦年2005の子を選択します。
/* Select children of calendar year 2005 */ WHERE t.parent = 'CY2005' AND p.dim_key = 'TOTAL' AND cu.dim_key = 'TOTAL' AND ch.dim_key = 'TOTAL'
この問合せは、年から四半期にドリルダウンします。Q1-05からQ4-05の4つの四半期が、Calendar階層の年CY2005の子です。
TIME SALES -------- ---------- Q1.05 31381338 Q2.05 37642741 Q3.05 32617249 Q4.05 35345244
各ディメンション・キーの親は階層ビューのPARENT列によって特定できます。全継承親子は、レベル・キーの各列で示されます。次のWHERE句は、LONG_DESCRIPTION属性に基づいて特定のTimeキーの親を選択します。
/* Select the parent of a Time key*/
WHERE t.dim_key =
(SELECT DISTINCT parent
FROM time_calendar_view
WHERE long_description='JAN-05')
AND p.dim_key= 'TOTAL'
AND cu.dim_key = 'TOTAL'
AND ch.dim_key = 'TOTAL'
この問合せは、月から四半期にドリルアップします。月JAN-05の親は、Calendar階層の四半期Q1-05です。
TIME SALES -------- ---------- Q1.05 31381338
次のWHERE句は、LEVEL_NAMEがMONTH、CALENDAR_YEARがCY2005の行を選択することによって、暦年2005の子孫を選択します。
/* Select Time level and ancestor */
WHERE t.level_name = 'MONTH'
AND t.calendar_year = 'CY2005'
AND p.dim_key = 'TOTAL'
AND cu.dim_key = 'TOTAL'
AND ch.dim_key = 'TOTAL'
この問合せは、2つのレベル（年から四半期、四半期から月）をドリルダウンします。Jan-05からDec-05までの12の月が、Calendar階層の年2005の子孫です。
TIME SALES -------- ---------- JAN-05 12093518 FEB-05 10103162 MAR-05 9184658 APR-05 9185964 MAY-05 11640216 JUN-05 16816561 JUL-05 11110903 AUG-05 9475807 SEP-05 12030538 OCT-05 11135032 NOV-05 11067754 DEC-05 13142459
「階層ビューの内容の表示」で説明したとおり、階層ビューでは各ディメンション・キーの祖先、子孫を含む全親子関係が示されます。次のWHERE句は、CALENDAR_YEARレベル・キー列を使用してMONTHディメンション・キーの祖先を特定します。
/* Select the ancestor of a Time key based on its Long Description attribute */
WHERE t.dim_key =
(SELECT calendar_year
FROM time_calendar_view
WHERE long_description = 'JAN-05')
AND p.dim_key = 'TOTAL'
AND cu.dim_key = 'TOTAL'
AND ch.dim_key = 'TOTAL'
この問合せは、2つのレベル（月から四半期、四半期から年）をドリルアップします。月JAN-05の祖先は、Calendar階層の年2005です。
TIME SALES -------- ---------- 2005 136986572
DBAは、すべてのアプリケーションで利用できる計算済メジャーをAnalytic Workspace Managerで作成できます。これによりアプリケーション開発が簡素化されるのに加えて、同じ計算に対してすべてのアプリケーションで同じ名前を使用できるようになります。
一方、ユーザー独自の計算を含む問合せの作成が必要になることもあります。この場合は、内部問合せを使用してキューブから集計データを選択してから、外部問合せの計算を実行します。データを選択するキューブではどの集計演算子が使用されていてもかまいません。また、問合せ内ではすべての関数または演算子を使用できます。キューブの適切なレベルから計算用のデータを選択すること、およびキューブと問合せにおける演算子の組合せにより必要な計算が作成されるようにすることの2点についてのみ注意してください。
例4-4は、「2005会計年度の第3四半期における政府系顧客に対するSentinel Standardコンピュータの平均売上」を求める問合せです。UNITS_CUBEはすべてのディメンションで合計されており、FY2005.Q3が7月、8月、9月の合計となります。内部問合せでこれらの月のデータを抽出し、外部問合せでMIN、MAX、AVGの3つの演算子とGROUP BY句を使用して平均を求めます。
例4-4 顧客全体の平均売上の計算
SELECT customer, ROUND(MIN(sales)) minimum, ROUND(MAX(sales)) maximum,
ROUND(AVG(sales)) average
FROM
(SELECT cu.long_description customer,
f.sales sales
FROM time_fiscal_view t,
product_primary_view p,
customer_segment_view cu,
channel_primary_view ch,
units_cube_view f
WHERE t.parent = 'FY2005.Q3'
AND p.dim_key = 'SENT STD'
AND cu.parent = 'GOV'
AND ch.level_name = 'TOTAL'
AND t.dim_key = f.time
AND p.dim_key = f.product
AND cu.dim_key = f.customer
AND ch.dim_key = f.channel
)
GROUP BY customer
ORDER BY customer;
内部問合せにより、次のデータがキューブから抽出されます。
CUSTOMER TIME SALES ---------------------------------------- -------- ---------- Dept. of Labor JAN-05 1553.26 Dept. of Labor MAR-05 1555.6 Ministry of Intl Trade JAN-05 1553.26 Ministry of Intl Trade FEB-05 1554.56 Ministry of Intl Trade MAR-05 1555.6 Royal Air Force JAN-05 1553.26 Royal Air Force FEB-05 6218.23 UK Environmental Department JAN-05 4659.78 UK Environmental Department FEB-05 3109.12
外部問合せによって計算された各顧客の売上の最大値、最小値および平均値は次のとおりです。
CUSTOMER MINIMUM MAXIMUM AVERAGE ------------------------------ ---------- ---------- ---------- Dept. of Labor 1553 1556 1554 Ministry of Intl Trade 1553 1556 1554 Royal Air Force 1553 6218 3886 UK Environmental Department 3109 4660 3884
OLAPキューブは、階層ビューによって示される親子関係を使用して、階層内のデータを集計します。OLAPエンジンによるディメンション属性値の集計計算は行われません。
しかし、製品を色またはサイズで集計したり、顧客を年齢、郵便番号、人口密度などで集計したいという場合もあります。このような場合は、キューブを問い合せるときにGROUP BY句を使用できます。この問合せでは、キューブからデータを抽出してから、SQLを使用して属性値別に集計します。
キューブではすべてのディメンションに対して同じ集計演算子を使用する必要があります。また、問合せのSELECT構文内の集計演算子は、キューブの集計演算子と一致する必要があります。次の演算子を使用するキューブの問合せで、GROUP BY句を使用できます。
FIRST（最初のNA以外の値）
LAST（最後のNA以外の値）
MAX（最大）
MIN（最小）
SUM（合計）
例4-5は、Packageという名前の属性に基づき集計する問合せです。次の結果が戻されます。
TIME PACKAGE SALES ------ ------------------ ---------- 2005 All 1809157.64 2005 Multimedia 18083256.3 2005 Executive 19836977 2005 Laptop Value Pack 9547494.81
Unitsキューブはすべてのディメンションに対してSUM演算子を使用しており、この問合せではSUM演算子を使用してSalesに対する集計を行います。Package属性はProductディメンションのItemレベルにのみ適用されるため、問合せではProductのItemレベルを選択しています。また、PackageについてNULLを除外しているので、パッケージの値を持つ製品のみが計算に含まれます。GROUP BY句によって、総売上が時間およびパッケージ別で集計されます。
例4-5 属性による集計
SELECT t.long_description time,
p.package package,
SUM(f.sales) sales
FROM time_calendar_view t,
product_primary_view p,
customer_shipments_view cu,
channel_primary_view ch,
units_cube_view f
/* Select Product by level and attribute */
WHERE p.level_name = 'ITEM'
AND p.package IS NOT NULL
AND t.long_description = '2005'
AND cu.level_name = 'TOTAL'
AND ch.level_name = 'TOTAL'
/* Join dimensions and cube */
AND t.dim_key = f.time
AND p.dim_key = f.product
AND cu.dim_key = f.customer
AND ch.dim_key = f.channel
GROUP BY t.long_description, p.package;
「属性によるメジャーの集計」で説明した方法を使用する前に、その計算自体に意味があるかどうか確認してください。たとえば、一般的な計算である変化率は、計算済メジャーとしてキューブ内に定義されている場合があります。変化率を合計すると予想外の結果になることがありますが、これは、変化率の計算（(a-b)/b）が加算的ではないためです。
次の行を持つデータについて考えてみます。全体に対する正しい変化率は.33ですが、1行目と2行目の変化率を合計すると.75になります。
| 行 | 売上 | 前期間の売上 | 変化率 |
|---|---|---|---|
| 1 | 15 |
10 |
.50 |
| 2 | 25 |
20 |
.25 |
| 合計 | 40 |
30 |
.33 |
例4-6は、Package属性に基づいて集計し、前期間からの変化率を計算する問合せです。内部問合せで総売上と前期間の売上を属性別に集計し、外部問合せではその結果を使用して変化率を計算します。問合せの結果は次のようになります。PCT_CHG_PPに正しい結果が表示されます。
TIME PACKAGE SALES PRIOR_PERIOD PCT_CHG_PP ------ ------------------ ---------- ------------ ---------- 2005 All 1809157.64 1853928.06 -.02414895 2006 All 1720399.03 1809157.64 -.04906074 2005 Executive 19836977 20603879.8 -.03722128 2006 Executive 19580638.4 19836977 -.01292226 2005 Laptop Value Pack 9547494.81 10047298.6 -.04974509 2006 Laptop Value Pack 9091450.58 9547494.81 -.04776585 2005 Multimedia 18083256.3 19607675.5 -.07774604 2006 Multimedia 18328678.7 18083256.3 .013571806 8 rows selected.
例4-6 属性による問合せ（計算済メジャーを使用）
/* Calculate Percent Change */
SELECT TIME, package, sales, prior_period,
((sales - prior_period) / prior_period) pct_chg_pp
FROM
/* Fetch data from the cube and aggregate over Package */
(SELECT t.long_description time,
p.package package,
SUM(f.sales) sales,
SUM(f.sales_pp) prior_period
FROM time_calendar_view t,
product_primary_view p,
customer_shipments_view cu,
channel_primary_view ch,
units_cube_view f
/* Create filters */
WHERE p.level_name = 'ITEM'
AND p.package IS NOT NULL
AND t.long_description IN ('2005', '2006')
AND cu.level_name = 'TOTAL'
AND ch.level_name = 'TOTAL'
/* Join dimension views to cube view */
AND t.dim_key = f.time
AND p.dim_key = f.product
AND cu.dim_key = f.customer
AND ch.dim_key = f.channel
GROUP BY t.long_description, p.package
ORDER BY p.package);
キューブやディメンションに対する問合せの実行計画は、リレーショナル表に対する問合せの実行計画と同様に作成および表示できます。
SQLコマンドのEXPLAIN PLANは、実行計画の内容を表示する表を作成します。この表のデフォルト名はPLAN_TABLEです。
次のコマンドを実行すると、キューブに対する基本的な問合せの実行計画が作成されます。
EXPLAIN PLAN FOR
SELECT t.long_description time,
p.long_description product,
cu.long_description customer,
ch.long_description channel,
f.sales sales
FROM time_calendar_view t,
product_primary_view p,
customer_shipments_view cu,
channel_primary_view ch,
units_cube_view f
WHERE t.level_name = 'CALENDAR_YEAR'
AND p.level_name = 'TOTAL'
AND cu.level_name = 'TOTAL'
AND ch.level_name = 'TOTAL'
AND t.dim_key = f.TIME
AND p.dim_key = f.product
AND cu.dim_key = f.customer
AND ch.dim_key = f.channel
ORDER BY t.end_date;
例4-7は、この実行計画から選択された列です。CUBE SCAN操作が実行されます。この計画のオプションはPARTIAL OUTERです（「実行計画の種類」を参照）。
例4-7 PLAN_TABLEから選択された列
SQL> SELECT operation, options, object_name FROM plan_table; OPERATION OPTIONS OBJECT_NAME -------------------- -------------------- --------------- SELECT STATEMENT SORT ORDER BY JOINED CUBE SCAN PARTIAL OUTER CUBE ACCESS UNITS_CUBE CUBE ACCESS CHANNEL CUBE ACCESS CUSTOMER CUBE ACCESS PRODUCT CUBE ACCESS TIME 8 rows selected.
PL/SQLパッケージDBMS_XPLANのDISPLAYテーブル・ファンクションを使用すると、実行計画の情報を例4-8のように書式を整えて表示できます。
例4-8 DBMS_XPLANを使用して書式を整えた実行計画
SQL> SELECT plan_table_output FROM TABLE(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1667678335
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 104 (3)| 00:00:02 |
| 1 | SORT ORDER BY | | 1 | 100 | 104 (3)| 00:00:02 |
| 2 | JOINED CUBE SCAN PARTIAL OUTER| | | | | |
| 3 | CUBE ACCESS | UNITS_CUBE | | | | |
| 4 | CUBE ACCESS | CHANNEL | | | | |
| 5 | CUBE ACCESS | CUSTOMER | | | | |
| 6 | CUBE ACCESS | PRODUCT | | | | |
|* 7 | CUBE ACCESS | TIME | 1 | 100 | 103 (2)| 00:00:02 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter(SYS_OP_ATG(VALUE(KOKBF$),12,13,2)='CALENDAR_YEAR' AND
SYS_OP_ATG(VALUE(KOKBF$),43,44,2)='TOTAL' AND
SYS_OP_ATG(VALUE(KOKBF$),33,34,2)='TOTAL' AND
SYS_OP_ATG(VALUE(KOKBF$),23,24,2)='TOTAL')
22 rows selected.
表4-1に、キューブの実行計画の種類を示します。
汎用アプリケーション（次元オブジェクトの名前が不明なアプリケーション）を開発している場合、データ・ディクショナリからこの情報を取得してアプリケーションで利用できます。
データベースのデータ・ディクショナリの静的ビューには、次元オブジェクトに関する情報を提供するものがあります。OLAPメタデータはすべてデータ・ディクショナリに格納されています。一部のデータ・ディクショナリ・ビューについては、この章の前半で紹介しています。
表4-2に、ALLビューの簡単な説明を示します。対応するDBAおよびUSERビューが存在します。
表4-2 OLAPの静的データ・ディクショナリ・ビュー
| ビュー | 説明 |
|---|---|
|
|
キューブのディメンションの属性が表示可能かどうかを示す。 |
|
|
キューブのディメンションの属性を示す。 |
|
|
キューブの作成プロセスとメンテナンス・スクリプトを示す。 |
|
|
キューブのディメンションの計算済メンバー（キー）を示す。 |
|
|
キューブのディメンション・レベルを示す。 |
|
|
OLAPのディメンションのモデルを示す。 |
|
|
キューブのディメンションのシステム生成リレーショナル・ビューの列を示す。 |
|
|
OLAPディメンションのシステム生成リレーショナル・ビューを示す。 |
|
|
OLAPキューブのディメンションの順序を示す。 |
|
|
キューブのディメンションを示す。 |
|
|
キューブのディメンションの階層レベルを示す。 |
|
|
キューブのディメンションのリレーショナル階層ビューの列を示す。 |
|
|
キューブのディメンションの階層を示す。 |
|
|
OLAPディメンション階層を示す。 |
|
|
OLAPキューブのメジャーを示す。 |
|
|
OLAPキューブのリレーショナル・ビューの列を示す。 |
|
|
OLAPキューブのシステム生成リレーショナル・ビューを示す。 |
|
|
OLAPキューブを示す。 |
|
|
OLAPメジャー・フォルダの内容を示す。 |
|
|
OLAPのメジャー・フォルダを示す。 |
|
参照: データ・ディクショナリ・ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。 |
