| Oracle Databaseデータ・ウェアハウス・ガイド 11gリリース1（11.1） E05763-01 |
|
 前へ |
 次へ |
この章では、データ・ウェアハウスでのディメンションの使用方法について説明します。内容は次のとおりです。
ディメンションとは、エンド・ユーザーがビジネス上の質問に答えることができるように、データを分類する構造です。最も一般的なディメンションは、顧客（customers）、製品（products）および時間（times）です。たとえば、衣料品の小売店チェーンの各店舗では、各種衣類の売上と再生利用に関するデータを収集および格納している場合があります。小売店チェーンの管理者は、データ・ウェアハウスを作成して、全店舗にわたる長期間の製品の売上を分析できます。また、次のような質問に答えることができます。
1つの製品の宣伝が、宣伝していない関係製品の売上に対してどのような影響があるか
宣伝前後の製品の売上はいくらか
宣伝が各種物流チャネルにどのように影響するか
小売店のデータ・ウェアハウス・システムのデータには、ディメンションとファクトという2つの重要なコンポーネントがあります。ディメンションは、製品、顧客、宣伝、チャネルおよび時間です。ディメンションを識別する1つの方法は、製品に関するすべての情報を含む製品表や、宣伝に関するすべての情報を含む宣伝表など、参照している表を調べることです。ファクトは、売上（売上数量）および利益です。データ・ウェアハウスには、1日ごとの各製品の売上に関するファクトが含まれます。
このようなデータ・ウェアハウスの典型的なリレーショナル実装が、スター・スキーマです。ファクト情報はファクト表に格納され、ディメンション情報はディメンション表に格納されます。たとえば、それぞれの売上トランザクション・レコードは、顧客別、製品別、販売チャネル別、宣伝別、および日付（時間）別に一意に定義されます。
Oracle Databaseでは、ディメンション情報自体がディメンション表に格納されます。また、データベース・オブジェクト・ディメンションにより、ディメンション情報を階層形式に編成してグループ化できます。これは、制約では表すことのできない列間または列グループ（階層レベル）間の1:nの関係を表します。階層内でのレベルを上げることはデータのロールアップと呼ばれ、レベルを下げることはデータのドリルダウンと呼ばれます。小売店の例では、次のようになります。
時間ディメンションでは、月は四半期に、四半期は年に、年は全年にそれぞれロールアップされます。
製品ディメンションでは、製品はサブカテゴリに、サブカテゴリはカテゴリに、カテゴリは全製品にそれぞれロールアップされます。
顧客ディメンションでは、顧客は市に、市は州に、州は国に、国は地区にそれぞれロールアップされます。最後に、地区は地域にロールアップされます。これを図10-1に示します。
通常、データ分析は、ディメンション階層の上位レベルから始まり、この分析が正当化されると順にドリルダウンします。
ディメンションを定義する必要はありません。ただし、アプリケーションでディメンショナル・モデリングを使用する場合は、ディメンションを注意深く作成すると、クエリー・リライトで複雑なタイプのリライトを実行できるようになり、大きなメリットが得られます。また、ディメンションは、マテリアライズド・ビューの特定のタイプのリフレッシュ操作およびSQLアクセス・アドバイザに対してもメリットをもたらします。ディメンションが必須になるのは、SQLアクセス・アドバイザ（マテリアライズド・ビューおよび索引管理用のGUIツール）を使用して、ワークロードを発生させることなく、作成、削除または保持すべきマテリアライズド・ビューおよび索引を推奨させる場合のみです。
|
関連項目: クエリー・リライトの詳細は、第17章「基本的なクエリー・リライト」を参照してください。SQLアクセス・アドバイザの詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。 |
この章で説明するディメンションの関係を完全に満たさないスキーマには、メリットの有無に関係なく、ディメンションを作成しないでください。作成すると、問合せで不適切な結果が戻される場合があります。
ディメンション・オブジェクトを作成する前に、ディメンション・データを含み得るディメンション表が、データベース内に存在する必要があります。たとえば、顧客ディメンションを作成する場合は、市、州および国の情報を含む1つ以上の表が存在する必要があります。スター・スキーマ・データ・ウェアハウスには、これらのディメンション表がすでに存在しています。したがって、どれが使用されるかを簡単に識別できます。
図10-1のようなディメンションの階層を定義できます。たとえば、市は州および国の子です（市レベルのデータは州レベルまで集計できるため）。この階層情報は、データベース・オブジェクト・ディメンションに格納されます。
複数の表に格納されているディメンションが正規化、もしくは部分的に正規化されている場合、これらの表がどのように結合されているか把握する必要があります。ディメンション表同士の結合においては、親表の各行と子表の各行の間に1対多の関係が保証されているかどうかに注意してください。また非正規化ディメンションの場合、子の列は親（または属性）の列を一意に決定できなくてはなりません。制約を使用してこれらの関係を表す場合、制約によって表される関係が他の手段で保証されるのであれば、NOVALIDATE句およびRELY句を使用して制約を使用可能にできます。
ディメンションのNULLレベルをスキップしたい場合があります。たとえば、プエルトリコの例を考えてみます。プエルトリコを北アメリカ地域に含めたいが、州のカテゴリには含めたくないとします。これを実行するには、SKIP WHEN NULL句を使用します。詳細は、この章の後半に記載されているサンプル・ディメンションを参照してください。構文および制限については、『Oracle Database SQL言語リファレンス』を参照してください。
ディメンションを作成するには、CREATE DIMENSION文またはOracle Enterprise Managerのディメンション・ウィザードのいずれかを使用します。CREATE DIMENSION文中では、LEVEL句を使用してディメンション・レベルの名前を指定します。
|
関連項目: CREATE DIMENSION文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
この顧客ディメンションには地理的なロールアップによる単一階層が含まれており、図10-1のように子レベルから親レベルへと矢印が描かれています。
この図式内の各矢印は、すべての子に対して親が1つのみあることを示します。たとえば、各市は1つの州のみに含まれる必要があり、各州は1つの国のみに含まれる必要があります。2つ以上の国に属する州は、階層の整合性に違反します。また、州に属さない市（ワシントンD.C.など）を含めたい場合は、SKIP WHEN NULL句を使用する必要があります。階層の整合性は、集計を含むマテリアライズド・ビューに対する管理機能を正確に操作するために必要です。
たとえば、product、subcategoryおよびcategoryのレベルを含むディメンションproducts_dimを宣言できます。
CREATE DIMENSION products_dim
LEVEL product IS (products.prod_id)
LEVEL subcategory IS (products.prod_subcategory)
LEVEL category IS (products.prod_category) ...
ディメンション内の各レベルは、データベースにある表の1つ以上の列に対応付けられている必要があります。したがって、レベルproductはproducts表の列prod_idで識別され、レベルsubcategoryは同じ表の列prod_subcategoryで識別されます。
この例では、データベース表は非正規化され、すべての列は同じ表に存在します。ただし、これはディメンション作成の前提条件ではありません。JOIN KEY句を使用した、正規化スキーマ設計を持つディメンションcustomers_dimの作成方法については、「正規化ディメンション表の使用」を参照してください。
次のステップでは、HIERARCHY文でレベル間の関係を宣言し、階層に名前を付けます。階層関係とは、階層内の1つのレベルから次のレベルに対する機能的な依存関係です。前に定義したレベルの名前を使用して、CHILD OF関係によって、各子のレベル値が1つのみの親レベル値に対応付けられていることが示されます。次の文では、prod_rollupという階層を宣言し、product、subcategoryおよびcategory間の関係を定義しています。
HIERARCHY prod_rollup (product CHILD OF subcategory CHILD OF category)
1:nの階層関係に加えて、ディメンションには、階層レベルとその依存ディメンション属性との間の1:1の属性関係も含まれます。たとえば、『Oracle Databaseサンプル・スキーマ』で定義されているように、ディメンションtimes_dimにはfiscal_month_desc、fiscal_month_nameおよびdays_in_fiscal_monthの各列があります。この関係の定義は、次のとおりです。
LEVEL fis_month IS TIMES.FISCAL_MONTH_DESC
...
ATTRIBUTE fis_month DETERMINES
(fiscal_month_name, days_in_fiscal_month)
ATTRIBUTE ... DETERMINES句は、fis_monthをfiscal_month_nameとdays_in_fiscal_monthに関係付けます。これが単方向の依存関係であることに注意してください。保証されているのは、特定のfiscal_month（1999-11など）について、fiscal_month_nameおよびdays_in_fiscal_monthに一致する値がそれぞれ1つ（Novemberおよび28など）のみであることです。各会計年度のNovemberであるfiscal_month_nameに基づいて特定のfiscal_month_descを判断することはできません。
この例では、fiscal_month_descではなくfiscal_month_nameで問合せが発行されたとします。この1:1の関係は属性とレベルの間に存在するため、fiscal_month_descを含んでいる集計済のマテリアライズド・ビューをディメンション情報に後戻り結合し、データの識別に使用できます。
ディメンション定義の例を次に示します。
CREATE DIMENSION products_dim
LEVEL product IS (products.prod_id)
LEVEL subcategory IS (products.prod_subcategory) [SKIP WHEN NULL]
LEVEL category IS (products.prod_category)
HIERARCHY prod_rollup (
product CHILD OF
subcategory CHILD OF
category)
ATTRIBUTE product DETERMINES
(products.prod_name, products.prod_desc,
prod_weight_class, prod_unit_of_measure,
prod_pack_size, prod_status, prod_list_price, prod_min_price)
ATTRIBUTE subcategory DETERMINES
(prod_subcategory, prod_subcategory_desc)
ATTRIBUTE category DETERMINES
(prod_category, prod_category_desc);
または、次の例に示すように、attribute_clauseのかわりにextended_attribute_clauseを使用することもできます。
CREATE DIMENSION products_dim
LEVEL product IS (products.prod_id)
LEVEL subcategory IS (products.prod_subcategory)
LEVEL category IS (products.prod_category)
HIERARCHY prod_rollup (
product CHILD OF
subcategory CHILD OF
category
)
ATTRIBUTE product_info LEVEL product DETERMINES
(products.prod_name, products.prod_desc,
prod_weight_class, prod_unit_of_measure,
prod_pack_size, prod_status, prod_list_price, prod_min_price)
ATTRIBUTE subcategory DETERMINES
(prod_subcategory, prod_subcategory_desc)
ATTRIBUTE category DETERMINES
(prod_category, prod_category_desc);
ディメンションの設計、作成およびメンテナンスは、データ・ウェアハウス・スキーマの設計、作成およびメンテナンスの一部です。ディメンションを作成したら、次の要件が満たされているかどうかを検証してください。
親と子の間に、1:nの関係がある必要があります。親は、1つ以上の子を持つことができますが、子は1つの親しか持てません。
階層レベルとその依存ディメンション属性の間に、1:1の属性関係がある必要があります。たとえば、fiscal_month_desc列がある場合、可能な属性関係はfiscal_month_desc対fiscal_month_nameになります。スキップ・レベルにある関係の行にNULL値を持つレベル列がある場合にNULLレベルをスキップするには、その行にもNULL値を持つ属性関係の列がなければなりません。
親レベルと子レベルの列が異なる表に存在する場合、これらの間の結合も1:nの結合関係がある必要があります。SKIP WHEN NULL句を使用しない場合、子表の各行は、親表の1つの行のみと結合している必要があります。この関係は、子の結合キーがNULLでないこと、子の結合キーと親の結合キーの参照整合性が保持されること、および親の結合キーは一意であることが必要であるため、参照整合性のみより強力です。
SKIP WHEN NULL句を使用しない場合は、各階層レベルの列がNULLでないこと、および階層の整合性が保たれていることを確認する必要があります（必要に応じて、データベース制約を使用してください）。
オプションの結合キーは、階層内のスキップ・レベル（存在する場合）にあるスキップ対象外の子であるCHILDLEVと、そのスキップ・レベル（同様に、存在する場合）にあるスキップ対象外の最も近い祖先であるANCLEVを接続する結合キーです。また、この結合キーは、異なる関係にわたってCHILDLEVおよびANCLEVが定義されている場合にのみ使用可能です。
ディメンションの階層は、相互にオーバーラップすることも切り離されることもあります。ただし、階層レベルの列を2つ以上のディメンションに関連付けることはできません。
ディメンションの図式内で循環を形成する階層の定義はサポートされません。たとえば、階層レベルは、それ自体とは直接的にも間接的にもつなげることはできません。
|
注意: ディメンション・オブジェクトに格納される情報は宣言のみです。前述の依存関係は、ディメンション・オブジェクトの作成のみでは施行されません。「ディメンションの妥当性チェック」で説明するように、すべてのディメンション定義はDBMS_DIMENSION.VALIDATE_DIMENSIONプロシージャで検証する必要があります。 |
CREATE DIMENSION文でATTRIBUTE句を使用すると、階層レベルによって一意に決定される1つ以上の列を指定できます。
extended_attribute_clauseを使用して、階層レベルによって決定される複数の列を作成する場合は、すべての属性列を削除することなく1つの属性列を削除できます。また、CREATEまたはALTER DIMENSION文で各ATTRIBUTE句に属性名を指定すると、ATTRIBUTE句ごとに属性名が付けられ、複数のレベル-列関係を個々に指定できるようになります。
次に、すべての列を削除することなく1つの列を削除できるようにする文を示します。
CREATE DIMENSION products_dim
LEVEL product IS (products.prod_id)
LEVEL subcategory IS (products.prod_subcategory)
LEVEL category IS (products.prod_category)
HIERARCHY prod_rollup (
product CHILD OF
subcategory CHILD OF category)
ATTRIBUTE product DETERMINES
(products.prod_name, products.prod_desc,
prod_weight_class, prod_unit_of_measure,
prod_pack_size,prod_status, prod_list_price, prod_min_price)
ATTRIBUTE subcategory_att DETERMINES
(prod_subcategory, prod_subcategory_desc)
ATTRIBUTE category DETERMINES
(prod_category, prod_category_desc);
ALTER DIMENSION products_dim
DROP ATTRIBUTE subcategory_att LEVEL subcategory COLUMN prod_subcategory;
|
関連項目: CREATE DIMENSION文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
単一のディメンション定義に、複数の階層を含めることができます。小売店で、ある品目の売上を長期間追跡すると想定します。最初のステップは、売上が追跡される時間の時間ディメンションを定義することです。図10-2に、2つの時間階層を持つディメンションtimes_dimを示します。
この図から、次のCREATE DIMENSION文によって定義された非正規化time_dimディメンションの階層を作成できます。完全なCREATE DIMENSION文およびCREATE TABLE文は、『Oracle Databaseサンプル・スキーマ』を参照してください。
CREATE DIMENSION times_dim
LEVEL day IS times.time_id
LEVEL month IS times.calendar_month_desc
LEVEL quarter IS times.calendar_quarter_desc
LEVEL year IS times.calendar_year
LEVEL fis_week IS times.week_ending_day
LEVEL fis_month IS times.fiscal_month_desc
LEVEL fis_quarter IS times.fiscal_quarter_desc
LEVEL fis_year IS times.fiscal_year
HIERARCHY cal_rollup (
day CHILD OF
month CHILD OF
quarter CHILD OF
year
)
HIERARCHY fis_rollup (
day CHILD OF
fis_week CHILD OF
fis_month CHILD OF
fis_quarter CHILD OF
fis_year
) <attribute determination clauses>;
ディメンションの定義に使用される表は、正規化または非正規化されている場合があります。また、個々の階層は、正規化または非正規化できます。1つの階層のレベルが同じ表のものである場合は、完全な非正規化階層と呼ばれます。たとえば、times_dimディメンション内のcal_rollupは、非正規化階層です。1つの階層のレベルが異なる表に存在する場合、このような階層は、完全なまたは部分的な正規化階層です。この項では、正規化階層の定義方法を示します。
顧客の所在地が市、州および国別に追跡されているとします。このデータは、customers表およびcountries表に格納されます。データ・エンティティcust_idおよびcountry_idが別々の表から取り出されるため、顧客ディメンションcustomers_dimは部分的に正規化されます。ディメンション定義内のJOIN KEY句では、階層内のレベルの結合方法が指定されます。ディメンションの文の一部を次に示します。完全なCREATE DIMENSION文およびCREATE TABLE文は、『Oracle Databaseサンプル・スキーマ』を参照してください。
CREATE DIMENSION customers_dim
LEVEL customer IS (customers.cust_id)
LEVEL city IS (customers.cust_city)
LEVEL state IS (customers.cust_state_province)
LEVEL country IS (countries.country_id)
LEVEL subregion IS (countries.country_subregion)
LEVEL region IS (countries.country_region)
HIERARCHY geog_rollup (
customer CHILD OF
city CHILD OF
state CHILD OF
country CHILD OF
subregion CHILD OF
region
JOIN KEY (customers.country_id) REFERENCES country);
SKIP WHEN NULL句を使用する場合、JOIN KEY句を使用して階層内に存在しないレベルを持つレベル同士をリンクできます。たとえば、次の文によって、SKIP WHEN NULLと宣言された州レベルを市および国に結合できます。
JOIN KEY (city.country_id) REFERENCES country;
これにより、顧客レベルおよび市レベルの行が、国レベル、地区レベルおよび地域レベルの行に関連付けられます。
ディメンションは、次のいずれかの方法で参照できます。
Oracle Enterprise Managerを使用すると、データ・ウェアハウス内にあるすべてのディメンションを表示できます。すべてのディメンションを表示するには、「スキーマ」アイコンから「ディメンション」オブジェクトを選択します。特定のディメンションを選択すると、その定義された階層、レベルおよび属性がグラフィカルに表示されます。
ディメンションの定義を表示するには、DBMS_DIMENSIONパッケージのDESCRIBE_DIMENSIONプロシージャを使用します。たとえば、ディメンションが次の文でshサンプル・スキーマに作成されているとします。
CREATE DIMENSION channels_dim
LEVEL channel IS (channels.channel_id)
LEVEL channel_class IS (channels.channel_class)
HIERARCHY channel_rollup (
channel CHILD OF channel_class)
ATTRIBUTE channel DETERMINES (channel_desc)
ATTRIBUTE channel_class DETERMINES (channel_class);
次のようにしてDESCRIBE_DIMENSIONプロシージャを実行します。
SET SERVEROUTPUT ON FORMAT WRAPPED; --to improve the display of info
EXECUTE DBMS_DIMENSION.DESCRIBE_DIMENSION('SH.CHANNELS_DIM');
結果が次のように出力されます。
EXECUTE DBMS_DIMENSION.DESCRIBE_DIMENSION('SH.CHANNELS_DIM');
DIMENSION SH.CHANNELS_DIM
LEVEL CHANNEL IS SH.CHANNELS.CHANNEL_ID
LEVEL CHANNEL_CLASS IS SH.CHANNELS.CHANNEL_CLASS
HIERARCHY CHANNEL_ROLLUP (
CHANNEL CHILD OF
CHANNEL_CLASS)
ATTRIBUTE CHANNEL LEVEL CHANNEL DETERMINES
SH.CHANNELS.CHANNEL_DESC
ATTRIBUTE CHANNEL_CLASS LEVEL CHANNEL_CLASS DETERMINES
SH.CHANNELS.CHANNEL_CLASS
制約は、ディメンションに対して重要な役割を果たします。データ・ウェアハウスでは、完全な参照整合性が有効化されますが、常にそうである必要はありません。これは、通常、業務系データベースは完全な参照整合性を持っており、データ・ウェアハウスに送られるデータがすでに設定されている整合性ルールに違反しないことを保証できるためです。
制約を使用可能にし、妥当性チェックの時間を考慮する必要がある場合に、次のようにNOVALIDATE句を使用することをお薦めします。
ENABLE NOVALIDATE CONSTRAINT pk_time;
主キーおよび外部キーも実装する必要があります。ファクト表の参照整合性制約およびNOT NULL制約によって、マテリアライズド・ビューの有用性を拡張するクエリー・リライトの機能で利用する情報が提供されます。
また、次のようにRELY句を使用して、制約が正しいことをクエリー・リライトに提示する必要があります。
ALTER TABLE time MODIFY CONSTRAINT pk_time RELY;
この情報は、クエリー・リライトにも使用されます。詳細は、第17章「基本的なクエリー・リライト」を参照してください。
SKIP WHEN NULL句を使用する場合、NOT NULL制約を持たない参照レベル列が少なくとも1つ必要です。
ディメンション・オブジェクトの情報は宣言のみで、データベースでは規定されません。ディメンションによって表された関係が不適切な場合は、不適切な結果が戻される可能性があります。したがって、DBMS_DIMENSION.VALIDATE_DIMENSIONプロシージャを定期的に使用して、CREATE DIMENSIONで指定される関係を検証する必要があります。
このプロシージャは使いやすく、指定するパラメータは次の4つのみです。
dimension: 所有者と名前。
incremental: このディメンションの表の新しい行のみをチェックするにはTRUEに設定。
check_nulls: SKIP WHEN NULL句を含むレベルにないすべての列がNULLではないことを検証するにはTRUEに設定。
statement_id: プロシージャの各実行結果を識別するためにユーザーが指定する一意の識別子。
次の例では、shスキーマ内のTIME_FNディメンションの妥当性チェックが行われます。
@utldim.sql
EXECUTE DBMS_DIMENSION.VALIDATE_DIMENSION ('SH.TIME_FN', FALSE, TRUE,
'my first example');
VALIDATE_DIMENSIONプロシージャを実行する前に、提供されているスクリプトutldim.sqlを実行して、ローカル表DIMENSION_EXCEPTIONSを作成しておく必要があります。VALIDATE_DIMENSIONプロシージャでエラーが検出されると、この表に書き込まれます。この表を問い合せると、検出されたエラーを識別できます。次に例を示します。
SELECT * FROM dimension_exceptions WHERE statement_id = 'my first example'; STATEMENT_ID OWNER TABLE_NAME DIMENSION_NAME RELATIONSHIP BAD_ROWID ------------ ----- ---------- -------------- ------------ --------- my first example SH MONTH TIME_FN FOREIGN KEY AAAAuwAAJAAAARwAAA
ただし、この表を問い合せるより、無効な行のROWIDを使用して、制約に違反する実際の行を取り出す問合せの方が適している場合があります。この例では、TIME_FNディメンションが、month表をチェックしています。これにより、制約に違反する行が検出されています。ROWIDを使用すると、次のように、month表のうち問題の原因となっている行を正確に確認できます。
SELECT * FROM month
WHERE rowid IN (SELECT bad_rowid
FROM dimension_exceptions
WHERE statement_id = 'my first example');
MONTH QUARTER FISCAL_QTR YEAR FULL_MONTH_NAME MONTH_NUMB
------ ------- ---------- ---- --------------- ----------
199903 19981 19981 1998 March 3
ALTER DIMENSION文を使用すると、ディメンションを変更できます。このコマンドを使用して、レベル、階層または属性をディメンションに対して追加または削除できます。
図10-2の時間ディメンションの場合では、属性fis_year、階層fis_rollupまたはレベルfiscal_yearを削除できます。また、次のようにf_yearという新しいレベルを追加できます。
ALTER DIMENSION times_dim DROP ATTRIBUTE fis_year; ALTER DIMENSION times_dim DROP HIERARCHY fis_rollup; ALTER DIMENSION times_dim DROP LEVEL fis_year; ALTER DIMENSION times_dim ADD LEVEL f_year IS times.fiscal_year;
ディメンションの作成時にextended_attribute_clauseを使用した場合は、すべての属性列を削除することなく1つの属性列を削除できます。これは、「属性列の削除および作成」に示されているように、次のような文で行うことができます。
ALTER DIMENSION product_dim DROP ATTRIBUTE size LEVEL prod_type COLUMN Prod_TypeSize;
ディメンション内でさらに依存性を持つオブジェクトの削除を試みると、そのディメンションの変更はOracle Databaseにより拒否されます。ディメンションが参照しているスキーマ・オブジェクトのいずれかを変更した場合、そのディメンションは無効になります。たとえば、ディメンションが定義されている表が変更されると、そのディメンションは無効になります。
次の文のように、SKIP WHEN NULL句を含むレベルを追加することによってディメンションを変更できます。
ALTER DIMENSION times_dim ADD LEVEL f_year IS times.fiscal_year SKIP WHEN NULL;
ただし、SKIP WHEN NULL句を含むレベルは変更できません。かわりに、そのレベルを削除して再作成する必要があります。
ディメンションの状態をチェックするには、ALL_DIMENSIONSデータ・ディクショナリ・ビューにあるinvalid列の内容を参照します。ディメンションを再検証するには、次のようにCOMPILEオプションを使用します。
ALTER DIMENSION times_dim COMPILE;
ディメンションは、Oracle Enterprise Managerを使用しても変更または削除できます。
