この章では、データ・ウェアハウスでのディメンションの使用方法について説明します。内容は次のとおりです。
ディメンションとは、エンド・ユーザーがビジネス上の質問に答えることができるように、データを分類する構造です。最も一般的なディメンションは、顧客(customers)、製品(products)および時間(times)です。たとえば、衣料品の小売店チェーンの各店舗では、各種衣類の売上と再生利用に関するデータを収集および格納している場合があります。小売店チェーンの管理者は、データ・ウェアハウスを作成して、全店舗にわたる長期間の製品の売上を分析できます。また、次のような質問に答えることができます。
1つの製品の宣伝が、宣伝していない関係製品の売上に対してどのような影響があるか
宣伝前後の製品の売上はいくらか
宣伝が各種物流チャネルにどのように影響するか
小売店のデータ・ウェアハウス・システムのデータには、ディメンションとファクトという2つの重要なコンポーネントがあります。ディメンションは、製品、顧客、宣伝、チャネルおよび時間です。ディメンションを識別する1つの方法は、製品に関するすべての情報を含む製品表や、宣伝に関するすべての情報を含む宣伝表など、参照している表を調べることです。ファクトは、売上(売上数量)および利益です。データ・ウェアハウスには、1日ごとの各製品の売上に関するファクトが含まれます。
このようなデータ・ウェアハウスの典型的なリレーショナル実装が、スター・スキーマです。ファクト情報はファクト表に格納され、ディメンション情報はディメンション表に格納されます。たとえば、それぞれの売上トランザクション・レコードは、顧客別、製品別、販売チャネル別、宣伝別、および日付(時間)別に一意に定義されます。
Oracle Databaseでは、ディメンション情報自体がディメンション表に格納されます。また、データベース・オブジェクト・ディメンションにより、ディメンション情報を階層形式に編成してグループ化できます。これは、制約では表すことのできない列間または列グループ(階層レベル)間の1:n
の関係を表します。階層内でのレベルを上げることはデータのロールアップと呼ばれ、レベルを下げることはデータのドリルダウンと呼ばれます。小売店の例では、次のようになります。
時間
ディメンションでは、月は四半期に、四半期は年に、年は全年にそれぞれロールアップされます。
製品
ディメンションでは、製品はサブカテゴリに、サブカテゴリはカテゴリに、カテゴリは全製品にそれぞれロールアップされます。
顧客
ディメンション内では、顧客は市
にロールアップされます。市は州
にロールアップされます。州は国
にロールアップされます。国は地区
にロールアップされます。最後に、地区は地域
にロールアップされます。これを図11-1に示します。
通常、データ分析は、ディメンション階層の上位レベルから始まり、この分析が正当化されると順にドリルダウンします。
ディメンションを定義する必要はありません。ただし、アプリケーションでディメンショナル・モデリングを使用する場合は、ディメンションを注意深く作成すると、クエリー・リライトで複雑なタイプのリライトを実行できるようになり、大きなメリットが得られます。また、ディメンションは、マテリアライズド・ビューの特定のタイプのリフレッシュ操作およびSQLアクセス・アドバイザに対してもメリットをもたらします。ディメンションが必須になるのは、SQLアクセス・アドバイザ(マテリアライズド・ビューおよび索引管理用のGUIツール)を使用して、ワークロードを発生させることなく、作成、削除または保持すべきマテリアライズド・ビューおよび索引を推奨させる場合のみです。
関連項目:
|
この章で説明するディメンションの関係を完全に満たさないスキーマには、メリットの有無に関係なく、ディメンションを作成しないでください。作成すると、問合せで不適切な結果が戻される場合があります。
ディメンション・オブジェクトを作成する前に、ディメンション・データを含み得るディメンション表が、データベース内に存在する必要があります。たとえば、顧客ディメンションを作成する場合は、市、州および国の情報を含む1つ以上の表が存在する必要があります。スター・スキーマ・データ・ウェアハウスには、これらのディメンション表がすでに存在しています。したがって、どれが使用されるかを簡単に識別できます。
図11-1のようなディメンションの階層を定義できます。たとえば、市
は州
および国
の子です(市レベルのデータは州レベルまで集計できるため)。この階層情報は、データベース・オブジェクト・ディメンションに格納されます。
複数の表に格納されているディメンションが正規化、もしくは部分的に正規化されている場合、これらの表がどのように結合されているか把握する必要があります。ディメンション表同士の結合においては、親表の各行と子表の各行の間に1対多の関係が保証されているかどうかに注意してください。また非正規化ディメンションの場合、子の列は親(または属性)の列を一意に決定できなくてはなりません。制約を使用してこれらの関係を表す場合、制約によって表される関係が他の手段で保証されるのであれば、NOVALIDATE
句およびRELY
句を使用して制約を使用可能にできます。
ディメンションのNULLのレベルは必要に応じてスキップできます。たとえば、プエルトリコの例を考えてみます。プエルトリコを北アメリカ地域に含めたいが、州のカテゴリには含めたくないとします。これを実行するには、
SKIP
WHEN
NULL
句を使用します。詳細は、この章の後半に記載されているサンプル・ディメンションを参照してください。構文および制限については、『Oracle Database SQL言語リファレンス』を参照してください。
ディメンションを作成するには、CREATE
DIMENSION
文またはOracle Enterprise Managerのディメンション・ウィザードのいずれかを使用します。CREATE
DIMENSION
文中では、LEVEL
句を使用してディメンション・レベルの名前を指定します。
関連項目: CREATE DIMENSION 文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
図11-1に示すとおり、この顧客ディメンションには地理的にロールアップする階層が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言語リファレンス』を参照してください。 |
単一のディメンション定義に、複数の階層を含めることができます。小売店で、ある品目の売上を長期間追跡すると想定します。最初のステップは、売上が追跡される時間の時間ディメンションを定義することです。図11-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;
この情報は、クエリー・リライトにも使用されます。詳細は、第18章「基本的なクエリー・リライト」を参照してください。
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
文を使用すると、ディメンションを変更できます。このコマンドを使用して、レベル、階層または属性をディメンションに対して追加または削除できます。
図11-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を使用しても変更または削除できます。