4 ディメンション・オブジェクトの問合せ
Oracle OLAPでは多数の分析コンテンツが用意されており、問合せに対する応答も高速なため、お使いのSQLアプリケーションを強化できます。SQL問合せのインタフェースにより、OLAPの知識がなくても、すべてのアプリケーションでキューブやディメンションへの問合せを実行できます。
OLAPオプションは、キューブ、ディメンションおよび階層のリレーショナル・ビューを自動的に生成します。SQLアプリケーションでこれらのビューを問い合せることにより、分析担当者や意思決定者はこれらのオブジェクトの情報量に富んだ内容を表示できます。また、実表などのシステム生成ビューを使用して、ご使用のアプリケーションに必要な構造に従ったカスタム・ビューを作成できます。
この章では、SQLで次元オブジェクトを問い合せるための基本的な方法を説明します。次の項目が含まれます。
関連項目:
-
Oracle Application Expressを使用して作成したサンプル・ダッシュボードについては、「SQLツールとアプリケーション・ビルダーを使用したレポートとダッシュボードの開発」を参照してください
-
キューブ、ディメンションおよび階層については、「ディメンション・データ・モデルの概要」を参照してください
OLAPの各ビューについて
システム生成ビューはアナリティック・ワークスペースと同じスキーマに作成されます。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
の各メジャー、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 MARKET CUSTOMER_MARKET_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
例4-1 Timeのすべてのレベルでの集計の表示
すべてのディメンションにフィルタを適用することの重要性を理解するために、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 . . .
例4-2 キューブ・ビューの基本的な問合せ
次に、Timeを年レベルのデータに制限するフィルタを適用したときの結果を見てみます。これは、基本的な問合せの例です。ここでは、UNITS_CUBE_VIEW
からSalesメジャーを選択し、このキューブ・ビューのキーを階層ビューに結合してキーの説明を取得します。
/* 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;
この例では、次の行が選択されます。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
句では、すべてのディメンションをフィルタするために、属性値を使用しています。
/* 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
句を使用した集計値の計算は行いません。キューブ内には集計値が存在するので、集計済のデータを再集計することになるためです。かわりに、この問合せではキューブから直接集計値を選択し、各ディメンションに適切なフィルタを適用することにより、必要な集計値を指定しています。
階層問合せの作成
ドリル操作は、ビジネス分析における重要な機能の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
句を使用して平均を求めます。
SELECT customer, ROUND(MIN(sales)) minimum, ROUND(MAX(sales)) maximum, ROUND(AVG(sales)) average FROM (SELECT cu.long_description customer, t.month_long_description time f.sales sales FROM time_fiscal_view t, product_primary_view p, customer_market_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以外の値)
-
最大
-
最小
-
合計
属性によるメジャーの集計
例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 |
|
|
|
2 |
|
|
|
合計 |
|
|
|
例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);
表およびビューへのキューブの結合
キューブは、他のキューブおよび次のようなリレーショナル・オブジェクトと結合できます。
-
表
-
外部表やPL/SQLテーブル・ファンクションを含むビュー
-
他の行ソース・タイプ(他の結合など)
通常、キューブを表またはビューに結合する場合、キューブが完全に集計されている必要はありません。また、CUBE JOIN
演算によって、フェッチされる値の数が制限されるため、パフォーマンスが自動的に向上します。キューブは方程式の右側に記述する必要があります。CUBE JOIN
をサポートしていない問合せの場合は、より負荷の高いHASH JOIN
、MERGE JOIN
またはNESTED LOOPS
が一般的に使用されます。
CUBE JOIN
が効果的に使用されるように、問合せでヒントを使用できます。
-
USE_CUBE
を指定すると、CUBE JOIN
が強制的に使用されます(可能な場合)。 -
NO_USE_CUBE
を指定すると、CUBE JOIN
は使用されません。
CUBE JOIN
の詳細は、「実行計画の表示」を参照してください。
例4-7では、表をキューブに結合しています。表にはCustomerディメンションのフランス語の説明が含まれており、キューブでは英語のみをサポートしています。この問合せは次の結果を戻します
CUSTOMER SALES ---------------------------------------- ------------ La Marine des USA Washington 600.34 Monolith Motor Co. Chattanooga 17946.51 Piedmont, Inc. San Jose 24874.41 Ministere du Commerce Int. Nagano 27595.97 Depart. des commun. - Stuttgart 30706.10 Min. Env. Brit. Londres 38125.77 Departement de travail Nouvelle-Orleans 42507.50 Ministere des Finances Sorbonne 43607.58 Monolith Motor Co. Knoxville 50874.53 Serv. des USA de recherche Wyo 54497.19 Depart. des commun. - Bonn 58944.97 . . .
例4-7 キューブと表の結合
SELECT cu.ship_to_dsc_french customer, f.sales sales FROM time_calendar_view t, product_primary_view p, customer_dim cu, channel_primary_view ch, units_cube_view f WHERE t.dim_key = 'CY2006' AND p.level_name = 'TOTAL' AND ch.level_name = 'TOTAL' AND t.dim_key = f.TIME AND p.dim_key = f.product AND cu.ship_to_id = f.customer AND ch.dim_key = f.channel ORDER BY f.sales;
実行計画の表示
キューブやディメンションに対する問合せの実行計画は、リレーショナル表に対する問合せの実行計画と同様に作成および表示できます。
SQLコマンドのEXPLAIN PLAN
は、実行計画の内容を表示する表を作成します。この表のデフォルト名はPLAN_TABLE
です。
関連項目:
実行計画の詳細は、『Oracle Database SQLチューニング・ガイド』を参照してください。
実行計画の生成
次のコマンドを実行すると、キューブに対する基本的な問合せの実行計画が作成されます。
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-8 キューブ問合せの実行計画
PL/SQLパッケージDBMS_XPLAN
のDISPLAY
表関数を使用すると、実行計画の情報をこの例のように書式を整えて表示できます。
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-9 キューブ結合の実行計画
これは、キューブと表を結合する問合せの実行計画の例です。問合せについては、「表およびビューへのキューブの結合」を参照してください。
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------- Plan hash value: 3634608218 ------------------------------------------------------------------------------------------------- | Id | Operation |Name |Rows | Bytes |TempSpc|Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | |1464 | 128K| | 1524 (94)| 00:00:19| | 1 | SORT ORDER BY | |1464 | 128K| 152K| 1524 (94)| 00:00:19| |* 2 | CUBE JOIN | |1464 | 128K| | 1422 (100)| 00:00:18| | 3 | TABLE ACCESS FULL |CUSTOMER_DIM| 61 | 2379 | | 4 (0)| 00:00:01| | 4 | JOINED CUBE SCAN PARTIAL OUTER| | | | | | | | 5 | CUBE ACCESS |UNITS_CUBE | | | | | | | 6 | CUBE ACCESS |CHANNEL | | | | | | | 7 | CUBE ACCESS |PRODUCT | | | | | | |* 8 | CUBE ACCESS |TIME |2520 | 125K| | 1417 (100)| 00:00:18| -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CU"."SHIP_TO_ID"=SYS_OP_ATG(VALUE(KOKBF$),76,77,2)) 8 - filter(SYS_OP_ATG(VALUE(KOKBF$),32,33,2)='CY2006' AND SYS_OP_ATG(VALUE(KOKBF$),85,86,2)='TOTAL' AND SYS_OP_ATG(VALUE(KOKBF$),65,66,2)='TOTAL') 22 rows selected.
実行計画の種類
表4-1に、キューブの実行計画の種類を示します。
表4-1 キューブおよびディメンションの実行計画の説明
操作 | オプション | 説明 |
---|---|---|
|
-- |
左側の表またはビューおよび右側のキューブを結合します。 |
|
|
左側の表またはビューおよび右側のキューブに対してアンチ結合を使用します。 |
|
|
左側の表またはビューと右側のキューブに対してアンチ結合(片側NULL認識)を使用する。右側の結合列(キューブ側)は SELECT cols FROM table WHERE table.c1 NOT IN (SELECT col FROM cube WHERE cube.col IS NOT NULL) |
|
|
左側の表またはビューおよび右側のキューブに対して外部結合を使用します。 |
|
|
左側の表またはビューおよび右側のキューブに対して右側セミ結合を使用します。 |
|
-- |
|
|
|
少なくとも1つのディメンションに対して外部結合を使用し、その他のディメンションについては内部結合を使用する。 |
|
|
すべてのキューブ・アクセスで外部結合を使用します。 |
関連項目:
結合のタイプの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
データ・ディクショナリの問合せ
汎用アプリケーション(次元オブジェクトの名前が不明なアプリケーション)を開発している場合、データ・ディクショナリからこの情報を取得してアプリケーションで利用できます。
データベースのデータ・ディクショナリの静的ビューには、次元オブジェクトに関する情報を提供するものがあります。OLAPメタデータはすべてデータ・ディクショナリに格納されています。一部のデータ・ディクショナリ・ビューについては、この章の前半で紹介しています。
表4-2に、ALL
ビューの簡単な説明を示します。対応するDBA
およびUSER
ビューが存在します。
表4-2 OLAPの静的データ・ディクショナリ・ビュー
ビュー | 説明 |
---|---|
キューブのディメンションの属性が表示可能かどうかを示す。 |
|
キューブのディメンションの属性を示す。 |
|
キューブの作成プロセスとメンテナンス・スクリプトを示す。 |
|
キューブのディメンションの計算済メンバー(キー)を示す。 |
|
キューブのディメンション・レベルを示す。 |
|
キューブのディメンションのモデルを示す。 |
|
キューブのディメンションのシステム生成リレーショナル・ビューの列を示す。 |
|
OLAPディメンションのシステム生成リレーショナル・ビューを示す。 |
|
OLAPキューブのディメンションの順序を示す。 |
|
キューブのディメンションを示す。 |
|
キューブのディメンションの階層レベルを示す。 |
|
キューブのディメンションのリレーショナル階層ビューの列を示す。 |
|
キューブのディメンションの階層を示す。 |
|
OLAPディメンション階層を示す。 |
|
OLAPキューブのメジャーを示す。 |
|
OLAPキューブのリレーショナル・ビューの列を示す。 |
|
OLAPキューブのシステム生成リレーショナル・ビューを示す。 |
|
OLAPキューブを示す。 |
|
OLAPメジャー・フォルダの内容を示す。 |
|
OLAPのメジャー・フォルダを示す。 |
関連項目:
データ・ディクショナリ・ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。