赤レンガ倉庫6丁目2番地: 第3回 システム構築に有効なソリューション・テンプレート（通信事業編）

ページオプション JavaScript を要するドキュメントオプションは表示されません

堤 保晴, ソフトウェアサポート&サービス IMテクニカルセールス, IBM

2005年 9月 30日

IBM Red Brick Warehouseの連載「赤レンガ倉庫6丁目2番地 」第3回です。

はじめに

第3回目の今回は、IBM Red Brick Warehouseでシステムを構築する上で有効なソリューション・テンプレートをご紹介いたします。

IBM Red Brick Warehouseは、1992年からData Warehouseに特化したRDBMSエンジンとして市場に投入され、世界中で数多くの実績を残してきました。IBM Red Brick Warehouse によるシステム開発を行う場合、データ構造をスタースキーマ構成にすることが、システム構築には必須の作業ですが、その構築を簡易に行うために必要なノウハウを社内のコンサルティング部隊がテンプレートとして独自に開発、使用してきました。今回ご紹介するソリューション・テンプテートは、そのノウハウを用いて作成されています。

ご紹介するソリューション・テンプレートは、業種ごとに異なる内容となっており、特に実装レベルで使用されるテーブル・スキーマ構造のテンプレートは、実際のシステム構築においてデフォルトで使用するスキーマ構造として、実績のある内容になっています。

ここでは、開発工程の作業を補完する複数のコンポーネントを、ソリューション・テンプレートと呼んでいます。

今回ご紹介しますソリューション・テンプレートは、通信事業、およびその販売のマーケット向けのソリューション・テンプレートです。特定の通信会社のサービス内容について、マーケット分析を行うようなアプリケーションを作成する場合に、利用できる内容になっています。それでは、最初にソリューション・テンプレートとして提供します個々のコンポーネントの紹介から始めたいと思います。 　

上に戻る

通信事業、およびその販売、マーケット向けソリューション・テンプレート

通信事業における販売およびマーケティング向けの分析ソリューション用テンプレートとして、提供している基本コンポーネントを御紹介いたします。今回御紹介いたしますテンプレートは、通信業種、業態に特化し、かつマーケット分析に必要な基本的なデータ項目を元に作成されています。 では最初に、ソリューション・テンプレートを構成する各コンポーネントについて、紹介していきます。

ソリューション・テンプレートで提供するコンポーネント

• ビジネス上の質問セット
  どのような分析業務をシステム化したいかを、質問の形で表現しています。ここで紹介しているビジネス上の質問は、今回の説明で作成するシステムを想定して書かれています。実際には、このような問い合わせをシステム要件から抽出し、例えば、ここで提供していますテンプレートに不足している項目やテーブルの洗い出しを行い、実際のシステムに反映させる形で作成するのも、利用法の一つになります。ここでは、通信業界におけるマーケット向け分析の基本的な要求を質問する内容になっています。
  
  
• 論理データモデルに含まれる各エンティティと属性
  通信業界用のシステム構築において、必要と思われるデータ項目についての詳細情報を、このテンプレートでは提供しています。実際には、これらの情報を用いて論理データモデルを構築するのが手順としてありますが、論理データモデルに含まれる各エンティティと属性の各項目を利用して、前述したビジネス上の質問セットに答えるために必要なスキーマ構成の例を、テンプレートとして提供しています。これらの詳細情報の一部が各テーブルのデータ項目としてモデル化されています。
  
  
• スキーマの設計及び実装例
  OrderItemとProductRevenueという2つのFACTテーブルが存在するスター・スキーマ構造が、今回の通信事業の販売、マーケット向けのテンプレート・スキーマになります。
  ここでは、ビジネス上の質問を考慮し、2FACTの構成を採用しています。
  
  
• システムで使用されるDBの項目定義書
  前述したビジネス上の質問に答えるために必要な、テーブルとそのテーブルを構成しているデータ項目についての定義情報を提供しています。各テーブル単位で、項目の内容、キーやIndexの定義が記述されています。
  
  
• テーブルの作成とスターインデックスの設定
  スキーマの設計及び実装例で示したスキーマ構成を実際に作成するDDLの記述例を提供しています。またINDEXの設定、特にスタースキーマ構成に最適化されたインデックスである、スターインデックスの定義例も提供しています。スターインデックスを設定することが、IBM Red Brick Warehouseのパフォーマンスを享受するための、必須条件となります。
  
  
• ビジネス上の質問に答えるための SQL ステートメント 作成したスタースキーマ構造のデータベースに対して、ビジネス上の質問を行う場合に使用されるSQLステートメントの例を提供しています。SQLの記述については、この例以外にも色々と考えられると思いますが、このDMLを利用して、より最適なクエリーの記述の考察とか、要求以外の処理を実施する場合の元になるSQLとして使用することもできます。

以上に挙げたような、基本コンポーネントの全部、あるいは一部を利用して、通信業向けの分析アプリケーションを作成することが可能となります。それでは、次にソリューションテンプレートを使ったシステムの構築について、構築の手順に沿って見て行きましょう。

上に戻る

構築手順

ソリューション・テンプレートをより効果的に使用するためには、以下の手順で作業を行うことを、推奨しています。ここでは、実際のシステム構築手順の流れを示しながら、ソリューションテンプレートの利用ポイントとその使用方法を説明いたします。

ビジネス上の質問セットの内容から、システムを構築する上で必要なデータ項目を洗い出し、データを検証しながら、論理データモデルを作成していきます。

このステップで提供しますソリューションテンプレートビジネス上の質問セットは、今回構築するシステムのビジネス上の質問になります。 また論理データモデルを構成する各エンティティーと属性についての詳細情報を論理データモデルに含まれる各エンティティと属性で提供します。この情報をベースに論理データモデルを作成することが可能になります。

論理データモデルを元に、テーブルスキーマの設計を行います。この時、一番の注意点は、スタースキーマ構成を前提として設計を行うため、定義するテーブルをFactとDimensionに分けるように設計します。また、論理データモデルのエンティティや属性が各テーブルのデータ項目に該当するかどうかを判断、考慮しながら設計する必要があります。今回のソリューションテンプレートには、上記手順を踏んで作成したテーブルスキーマ構造が含まれています。

設計が完了したテーブルスキーマの各テーブルに対して、必要とするデータの内容についての検証を行います。この場合、予め用意したサンプルデータを利用することで、このステップでの作業効率が図れます。ソリューションテンプレートとしては、システムで使用されるDBの項目定義書を提供しています。

IBM Red Brick Warehouse 製品をインストールして、実行可能な状態にします。

再度、スキーマ定義を検証し、データ要件の正確さを高め、必要であればスキーマ構造の変更を行います。

ソリューションテンプレートで提供しているデータベース作成スクリプトを、現行システム用にカスタマイズし、実行してデータベースを作成します。ここでは、テーブル作成用のスクリプトを提供しています。

サンプルのデータ、あるいは実際に作成したデータをデータベースにロードします。

テンプレートで提供しているSQLステートメントをビジネス上の質問として、クライアントツールから実行します。このステップではクエリーパフォーマンスを考慮するインデックス作成用のスクリプトと、最終目的であるビジネス上の質問に答えるための SQL ステートメントを利用して、実際のビジネス上の質問に答えていきます。

この二つのソョリューション・テンプレート（インデックス作成用のスクリプト、ビジネス上の質問に答えるための SQL ステートメント）を有効に使うことで、最終的に出来上がったシステムのパフォーマンスと製品のクオリティを上げる効果があります。

それでは、最後にソリューション・テンプレートの詳細を示します。構築手順に従って、以下のテンプレートを利用することが、システム開発の過程で有効であることを御確認いただければと思います。

上に戻る

ビジネス上の質問

ソリューション・テンプレートとして提供しているビジネス上の質問ですが、今回構築するシステムの要求仕様であり、システム用件の範囲を定義しています。またこれらのビジネス上の質問に答えるために使用するSQL ステートメントの例を、テンプレートの一部として提供しています。

商品の傾向

以下が今回のマーケット分析に使用するサービスのカテゴリーになります。

システムの分析対象としては、2001 年の各カテゴリー別の収入（料金）の総額を対象としています。

• 通話料 (LATA 内市外通話および LATA 間通話)
• 市内通話サービス
• 専用線
• 800 (フリー・ダイヤル) 回線
• WATS 回線
• 度数制サービス

2001年に収入の最も多かった上位 5 商品についての、各商品の総収入におけるシェアは？

2001 年の第 1 四半期と 2002 年の第 1 四半期を比較した場合の、各カテゴリー別の収入における成長率または下降率は？

昨年の第 4 四半期において、企業顧客による注文が行われた時点から、注文への対応までに要した時間の平均は？　この期間中の注文件数は？

過去 2 年間にわたる、ホリデー期間における有料通話の平均収入は？

一年前の時点で、各商品を利用している顧客の総数は？　 また今年の初めと比較した場合の顧客の増減は？

本年 4 月分の料金通話の収入を 3 月分と比較した場合の増減は？

年初来の売上累計を基準とした場合の、収益性の低い商品は何か？

2001 年の第 1 四半期と 2002 年の第 1 四半期を比較した場合の、製品の利益幅の違い、および利益率の変化は？

昨年の収入が多かった上位 10 業種をランク付けしなさい。

2001 年の、ニューヨーク州とカリフォルニア州を比較した場合の、収入カテゴリー別の年間収入比率の違いは？

サービス・プランやプロモーションに参加しないのはどのようなアカウントか？(場所別にリスト表示)

プロモーションの効果

前回実施したプロモーション中に販売数量と売上金額はどれだけ増加したか？

(過去 2 年間の売上金額と販売数量の基準値を算出することにより季節変動を除去し、プロモーション期間中の値と比較できる日次の平均を割り出す)

次に論理データモデルに含まれる各エンティティと属性の詳細について、記述しています。

上に戻る

エンティティーと属性の説明

この章では、論理データ・モデルに含まれる各エンティティーと属性について説明します。ここで説明されている各エンティや属性がその後に作成されるテーブルスキーマ構成に対応していきます。

Account (アカウント)

請求のために、特定の商品と電話の利用を顧客に関連付けます。各アカウントは、請求、販売、および保守を担当する顧客連絡先に関連付けられています。

• Account Number - Account (アカウント) から取得されます。
• Customer ID - Customer (顧客) から取得されます。
• Contact Name - Contact (連絡先) から取得されます。
• Bill Cycle - 請求サイクルの日数です。
• Bill Media - 顧客への請求時に使用する媒体のタイプです。

Adjustment (調整)

調整は、さまざまな理由 (機器の障害、通話の切断など) で、利用者によって申請され、特定の発着信詳細記録に適用されます。

• Adjustment Date - 調整が適用された日付です。
• Terminating Number - Call Detail (発着信詳細) から取得されます。
• Originating Number - Call Detail (発着信詳細) から取得されます。
• Call Time - Call Detail (発着信詳細) から取得されます。
• Call Date - Call Detail (発着信詳細) から取得されます。
• Reason Code - 調整の理由です。
• Adjustment Amount - 特定の通話に適用される金額です。

暦年中の特定の月です。

• Calendar Month Number - 月を示す整数値です。
• Calendar Year Number - Year から取得されます。
• Calendar Quarter Number - Quarter から取得されます。
• Calendar Month Name - 暦年中の標準的な月の正式名称 (January (1 月)、February (2 月) など) です。
• Calendar Abbr Month - 暦年中の標準的な月の 3 文字の省略名 (Jan、Feb など) です。

暦年を、1 月から順に 3 か月ごとに区切った期間です。単一の暦年内では、暦四半期は固有です。

• Calendar Quarter Number - 暦年中の暦四半期を示す整数値です。
• Calendar Year - Calendar Year (暦年) から取得されます。

ユリウス暦によって定義される年 (2001、2002 など) です。

• Calendar Year Number - 年を示す整数値 (2001、2002 など) です。

サービス利用の結果として生成される記録です。発着信詳細記録は、発信元および相手先の電場番号、発信元および相手先の場所、通話時間、利用したサービスの種類 (WATS、800 など)、料金の適用時間帯、および割引前の料金を示します。請求書には、発着信詳細記録の要約が記載されます。

• Terminating Number - Phone Number (電話番号) から取得されます。
• Originating Number - Phone Number (電話番号) から取得されます。
• Call Time - 通話が開始された時刻です。
• Call Date - 通話が開始された日付です。
• Destination Trunk - Trunk Group ID (トランク群 ID) から取得されます。
• Switch Number - 通話の発信元から識別される交換機です。
• Orig Auth Code - 発信元の交換機によって通話に割り当てられるコードで、通話の当事者を識別します。交換サービスの場合は、発信元の電話番号になります。コーリング・カードを利用する場合は、その電話の電話番号と認可コードになります。800 番サービスの場合は、ダイヤルされた 800 番の番号が含まれます。
• ANI Ind - この通話で自動番号識別 (ANI) を使用できたかどうかを示します。ANI を使用できるようにするには、通話が交換サービス・タイプでなければなりません。
• Swap Flag - 通話が回線網上の別の 場所からの着信であるか、あるいは別の場所への発信であるかを示します。通話が着信の場合は課金されません。
• Origination Trunk -Trunk Group ID (トランク群 ID) から取得されます。
• Orig Port ID - 発信元のシステムに関連付けられている物理的なポートです。
• MCA - 通話の発信元に関連付けられている都市通話エリアです。
• Duration - 0.1 秒単位の通話時間です。
• Rate Type - Rate Type (料金タイプ) から取得されます。
• Answer Sup - 相手先の応答機器のタイプ (すなわち、ハードウェアによる応答、ソフトウェアによる応答、話中音、応答なし) を示す応答監視の値です。
• Service Type - その通話で使われたサービスのタイプ (800、WATS、コーリング・カード、携帯電話) を示します。
• Phone Card Flag - 通話を行うためにコーリング・カードが使われたかどうかを示すフラグです。
• Cost - 割り引かれる前の使用料です (ドル・セント単位)。
• Mileage - 通話の発信元から相手先までの距離 (直線距離のマイル数) です。
• Call Type - オペレーター・サービス通話のタイプ (番号指定通話、指名通話、番号案内、国際番号指定通話) を示します。

郡内の 1 つの地域を示します。

• City Name - 郡内の 1 つの地域です。
• Zip Code - Zip Code (郵便番号) から取得されます。

その顧客の連絡先となる、顧客に関連付けられた特定の個人を示します。連絡先は、請求、販売、または保守についての連絡先に分類されます。

• Customer ID - Customer (顧客) から取得されます。
• Contact Name - 顧客の連絡先として指定される個人の名前です。
• Lat - 連絡先の場所の緯度です。
• Lon - 連絡先の場所の経度です。
• Contact Type - 連絡先のタイプ (請求、保守、販売) です。
• Contact Number Type - その連絡先に連絡するために使用される番号のタイプ (電話、FAX、ポケット・ベルなど) です。
• Contact Number - 連絡先に連絡するために使用される番号です。

州内の 1 つの地域です。

• County Code - 郡を表す固有の ID です。
• State Abbr - State (州) から取得されます。
• County Name - 州内の郡の名前です。

現在サービスを使用中である、以前にサービスを使用したことがある、または当社が提供するサービスについて問い合わせたことがある企業顧客または個人顧客を識別します。各顧客は、単独で存在する場合もあれば、より高いレベルの会社の一部である場合 (つまり子会社など) もあります。

• Customer ID - 特定の顧客を識別するために使用する固有の ID です。
• SIC - Industry (産業) から取得されます。
• Parent Customer ID - 親の顧客を識別するために使用する固有の ID です。
• Name Abbr - 顧客の省略名です。
• Name Full - 顧客の正式名称です。
• Customer Type - 顧客のタイプ (個人顧客か企業顧客か) です。
• Number of Employees - 特定の顧客に関連付けられている従業員数です。

ユリウス暦の年を基にした、午前 0 時から始まる特定の 24 時間です。各日は、平日、週末、または祝祭日として分類されます。

• Date - 月、日、および年を結合した値 (5/4/94 など) です。
• Calendar Year Number -Calendar Year (暦年) から取得されます。
• Calendar Quarter Number - Calendar Quarter (暦四半期) から取得されます。
• Calendar Month Number - Calendar Month (暦月) から取得されます。
• Fiscal Week Number - Fiscal Week (会計週) から取得されます。
• Fiscal Period Number - Fiscal Period (会計期間) から取得されます。
• Fiscal Quarter Number - Fiscal Quarter (会計四半期) から取得されます。
• Fiscal Year Number - Fiscal Year (会計年) から取得されます。
• Day Number - 月内の日を表す整数値 (1、2、3 など) です。
• Day Name - 曜日の正式名称 (Monday (月曜日)、Tuesday (火曜日) など) です。
• Day of Week Number - 週内の特定の日を示す整数値 (1 〜 7) です。
• Day Type - 日のタイプ (平日、週末、祝祭日) です。

物理的または主観的 (無形の) 特徴によって関連付けられられる値のグループです。 これを使用して、顧客を説明することができます。

• Attribute ID - 人口統計上の属性の固有の ID です。
• Attribute Description - 人口統計上の属性の固有の説明です。

顧客の特徴を示す値です。

• Demographic Value ID - 値の固有の ID です。

属性とその値の有効なすべての組み合わせを表す関連エンティティーです。

• Attribute ID - Demo Attribute (人口統計上の属性)から取得されます。
• Demographic Value ID - Demo Value (人口統計値) から取得されます。

拡張アカウントは、同じサブ・アカウント (コーリング・カードのアクセス番号) を共用する特定の利用者をさらに詳しく定義します。

• Enhanced Account Number - 拡張アカウント番号を定義する固有の番号です。
• Sub Account Number - Sub Account (サブ・アカウント) から取得されます。
• Account Number - Account (アカウント) から取得されます。

会計年度を 12 分割した時間グループです。この時間グループは、複数の完全な週 (4 週または 5 週) で構成される必要があり、単一の会計年度内で固有です。

• Fiscal Period Number - 会計年度内の会計期間を示す整数値 (1 〜 12) です。
• Fiscal Year Number - Fiscal Year (会計年度) から取得されます。
• Fiscal Quarter Number - Fiscal Quarter (会計四半期) から取得されます。
• Number of Weeks - 会計期間内の週の数 (1、2 など) です。
• Number of Days - 会計期間内の日数 (28、35 など) です。

企業の会計年度を 4 分割した時間グループです。この時間グループは、単一の会計年度内で固有です。

• Fiscal Quarter Number - 会計年度を示す整数値 (1999、2000 など) です。
• Fiscal Year Number - Fiscal Year (会計年度) から取得されます。

会計年度中の連続する 7 日をグループ化したもので、会計年度中の固有の週を表します。

• Fiscal Week Number - 会計年度中の会計週を示す整数値です。
• Fiscal Quarter Number - Fiscal Quarter (会計四半期) から取得されます。
• Fiscal Year Number - Fiscal Year (会計年度) から取得されます。
• Fiscal Period Number - Fiscal Week (会計週) から取得されます。

会計報告年に相当する、企業の時間グループです。

• Fiscal Year Number - 会計年度を示す整数値 (1999、2000 など) です。

顧客の業種を分類するために使用する特定の産業を示します。

• SIC - 顧客の業種のタイプを示すために使用する標準産業分類コード (SIC) です。
• Description - テキストによる特定の産業の説明です。

LATA (Local Access Transport Area) は、請求のために地域電話会社に割り当てられる特定の領域です。

• LATA ID - 特定の LATA を示すために使用する固有の ID です。
• LATA Description - LATA (Local Access Transport Area) の説明です。

特定の場所の緯度と経度です。

• Lat - 地図上の特定の垂直位置を、秒数の合計で示した緯度です。
• Lon - 地図上の特定の水平位置を、秒数の合計で示した経度です。
• Zip Code - Zip Code (郵便番号) から取得されます。
• City Name - City (都市) から取得されます。

顧客が持つ可能性のある、属性と値のすべての組み合わせを表す関連エンティティーです。人口統計は通常、郵便番号別に示されます。

• Zip Code - 郡内の地域を示すコードで、 米国郵政公社によって郵送地域を一意に識別するために使用されます。
• Attribute ID - 人口統計の属性を示す固有の ID です。
• Demographic Value ID - 値の固有の ID です。

電話番号の形式を示します。国によって異なる電話番号形式が使用されている場合があります。

• Number Plan ID - 特定の番号計画を示すために使用する固有の ID です。
• Number Plan Description - 特定の番号計画の説明です。

アカウントに関連付けられた商品を追加、削除、または変更するための、顧客からの特定の注文を示します。

• Order ID - 特定の顧客の注文を定義する固有の ID です。
• Account Number - Account (アカウント) から取得されます。
• Order Received Date - 注文を受けた日付です。

アカウントに関連する特定の商品を追加、削除、または変更するための、顧客からの特定の要求を示します。1 つの注文は、複数の注文品で構成されます。

• Order ID - Order (注文) から取得されます。
• Item Number - 注文内の特定の品目を識別するために使用する番号です。
• Account Number - Account (アカウント) から取得されます。
• Product Code - Product (商品) から取得されます。
• Activity Type - 顧客が行う注文のタイプ (追加、削除、変更) です。
• Requested Start Date - 顧客が希望する、商品の使用開始日です。
• Planned Start Date - 商品のサービス開始または提供の予定日です。
• Actual Start Date - 顧客に対して商品のサービスが実際に開始された、または使用のために提供された日付です。

電話番号は、注文の結果として生成され、特定のアカウントに関連付けられます。この番号は、発信元番号や相手先番号として、発着信詳細記録に記載されます。

• Phone Number - 特定の通話の発信元または相手先を示すために使用される電話番号です。この番号の形式は、番号計画で指定された形式になります。
• Enhanced Account Number - Sub Account (サブ・アカウント) から取得されます。
• Sub Account Number - from Sub Account (サブ・アカウント) から取得されます。
• Account Number - Account (アカウント) から取得されます。
• Lat - Lat Lon (緯度/軽度) から取得されます。
• Lon - Lat Lon (緯度/軽度) から取得されます。
• LATA ID - LATA から取得されます。
• Number Plan ID - Number Plan (番号計画) から取得されます。

企業が提供する商品とサービスを示します。これには、繰り返し発生する料金と 1 回限りの料金とその両方を含む商品があります。商品の例としては、長期契約、通話パッケージ、機能 (キャッチ・ホン、転送電話サービスなど) などがあります。

• Product Code - 商品を識別する固有のコードです。
• Product Description - 商品の説明です。
• Product Type - 品物やサービスとして提供される、商品のタイプを示します。
• Quantity - 商品に含まれる数量 (通話プラン内の通話時間分数など) を示します。
• Unit of Measure - 商品を定義するために使用する測定単位 (分など) です。
• Cost Per Unit - 商品の 1 単位当りの内部コストです。
• Price Per Unit - 顧客に請求する、商品の 1 単位あたりの料金です。
• Recurring Charge Flag - その商品に、繰り返し発生する料金が含まれているかどうかを示すフラグです。
• Parent Product Code - 商品を示す固有のコードです。

特定の期間に顧客に提供される、特定のプロモーションを示します。 プロモーションは、顧客がアカウント・ベースで申し込みを行うオファーであり、期間限定で割引を提供したり、サービスの開始や機能の追加にかかる料金を無料にしたりします。

• Promotion ID - 特定のプロモーションを示すために使用する固有の ID です。
• Start Date - プロモーションの開始日です。
• End Date - プロモーションの終了日です。
• Promotion Description - プロモーションの説明です。
• Promotion Type ID - Promotion Type (プロモーションのタイプ) から取得されます。

Promotion Products (プロモーション商品) は、特定の商品に関連付けられた特別価格 (割引) を定義します。

• Promotion ID - Promotion (プロモーション) から取得されます。
• Start Date - プロモーションの開始日です。
• End Date - プロモーションの終了日です。
• Product Code - Product (商品) から取得されます。
• Quantity - プロモーション対象の商品に含まれる単位数 (通話プランの通話時間分数など) です 。
• Units of measure - 商品を定義するために使用する測定単位です。
• Cost per Unit - 商品の 1 単位当りの内部コストです。
• Price per Unit - 顧客に請求する、商品の 1 単位あたりの料金です。

顧客を勧誘するために使用するプロモーション/広告のタイプを示します。

• Promotion Type ID - プロモーションのタイプの固有の ID です。
• Promotion Type - プロモーションのタイプの固有のタイトル (ダイレクト・メール、テレマーケティングなど) です。

料金が関連付けられている、1 日の中の特定の時間帯を示します。

• Rate Type - 特定の時間帯や日に関連付けられた料金 (日中、夕方、夜間、週末) を定義します。
• Day Type - 日のタイプ (平日、週末、または祝祭日) です。
• End Time - 関連付けられた料金の適用時間帯の終了時刻です。
• Start Time - 関連付けられた料金の適用時間帯の開始時刻です。

販売構造に対応する地理的領域です。

• Region ID - 地図上の特定の地域を示すために使用する固有の ID です。
• Region Description - 地域の名前または説明です。

顧客との接点となる特定の販売チャネルを説明します。

• Channel ID - 特定の販売チャネルを示すために使用する固有の番号です。
• Region ID - Region (地域) から取得されます。
• Direct Indirect Flag - チャネルが直接か、あるいは間接かを示すフラグです。
• VAR Name - 販売チャネルに関連付けられた VAR (付加価値再販業者) を示します。
• Sales Channel Description - 販売チャネルの説明です。

顧客を訪問する特定の営業担当員を示します。

• Employee Number - 特定の営業担当員を示す、固有の従業員番号です。
• Channel ID - Channel (チャネル) から取得されます。
• Employee Name - 従業員番号に関連付けられている、従業員の名前です。
• Rank - 社内におけるその営業担当員の職位です。
• Experience - 関連商品についての販売経験年数です。

営業担当員と企業の連絡先の、有効なすべての組み合わせを表す関連エンティティーです。

• Customer ID - Customer (顧客) から取得されます。
• Contact Name - 顧客の連絡先として示される担当者の名前です。
• Employee Number - 特定の営業担当員を示す、固有の従業員番号です。

米国を構成する構成単位の 1 つを示します。

• State Abbr - 郵政公社が使用する、州の 2 文字の省略名です。 （CA　.etc）
• State Name - 米国内の特定の地理的領域を示す固有の名前です。

サブ・アカウントは、アカウント内の特定の利用者 (コーリング・カード番号など)を示すために使用されます。

• Sub Account Number - サブ・アカウントを定義する固有の番号です。
• Account Number - Account (アカウント) から取得されます。

1 日の中の特定の時間、分、秒、および 0.1 秒を示します。

• Date - Date (日) から取得されます。
• Time of Day - その日の時間、分、秒、および 0.1 秒単位の時刻です。

トランク群とは、ハブ間を接続するデジタル・サービス・マルチプレクサーと回線のセットを表します。これらのハブ間では、最小限の回線セットを使用して、大量のデータを転送することができます。

• Trunk Group ID - 特定のトランク群を示す固有の ID です。
• Trunk Group Description - 交換機に関連付けられているトランク群の固有の説明です。

郡内の各地域を示すコードです。米国郵政公社および国際郵便サービスによって郵送地域を一意に識別するために使用されます。

• Zip Code - 郡内の各地域を示すコードで、米国郵政公社が郵送地域を一意に識別するために使用します。
• County Code - County (郡) から取得されます。
• State Abbr - State (州) から取得されます。

スキーマの設計

通信事業の販売およびマーケティング向けソリューション・テンプレートのスキーマ設計は、Order Items (注文品) と Product Revenue (商品収入) の 2 つのファクト表に重点が置かれています。これら両方のファクト表は、Product (商品)、Promotion (プロモーション)、Customer Account (顧客のアカウント)、およびDate (日付) の各ディメンション表を共用する構成を取っています。

Order Item (注文品) ファクト表には、顧客の商品注文の結果が取り込まれています。 Order Item (注文品) ファクト表内の各レコードは、アカウントに関連付けられている商品 (またはサービス) の追加、削除、または変更を求める、顧客の要求を識別します。このファクト表は、商品の傾向、プロモーションの効果、および顧客の反応を調べるために使用します。

Product Revenue (商品収入) ファクト表には、商品の利用によって発生した収入が取り込まれます。 Product Revenue (商品収入) ファクト表内の各レコードは、顧客のアカウントに関連付けられた特定の商品によって特定の日付に発生した収入を定義します。このファクト表は、商品の傾向、商品の収益性、プロモーションの効果、および顧客の収入を調べるために使用します。このファクト表には、自社に固有の要件に基づいて集計された商品収入を含めることができます。

今回作成するビジネス上の質問に回答するためには、2つのFACT表をそれぞれの要求に沿った形でディメンジョンテーブルとジョインさせながら、SQLステートメントを使って照会を行います。

次に、ビジネス上の質問の要件を踏まえて作成した、スタースキーマ構成とその定義情報を示します。

上に戻る

テーブルの作成とスターインデックスの設定

ここでは、テーブル作成用のスクリプトとインデックス作成用のスクリプトを一つの例として提供しています。

                
    create table date (

	date_key		integer not null, 
	date_full		date,
	fiscal_year_num		integer,
	fiscal_quarter_num	integer,
	fiscal_month_num	integer,
	fiscal_month_name	char(10),
	fiscal_month_abbr	char(3),
	fiscal_week_num		integer,
	calendar_year_num	integer,
	calendar_quarter_num	integer,
	calendar_month_num	integer,
	calendar_month_name	char(10),
	calendar_month_abbr	char(3),
	calendar_day_num	integer,
	day_of_week_num		integer,
	day_name		char(10),
	day_type		char(4),
	primary key (date_key))
	maxrows per segment  1461;
create index date_index1 on date(calendar_year_num);
create index date_index2 on date(date_full);

                
create table promotion (

	promotion_id		integer not null,
	promotion_desc		char(30),
	promotion_type_id	integer not null,
	promotion_type		char(15),
	start_date		date,
	end_date		date,
	quantity		integer,
	unit_of_measure		char(5),
	cost_per_unit		decimal(6,2),
	price_per_unit		decimal(6,2),
	primary key (promotion_id))
maxrows per segment 8;

                
create table product (

	product_id		integer not null,
	product_desc		char(30),
	product_type		char(1),
	quantity		integer,
	unit_of_measure		char(5),
	cost_per_unit		decimal(6,2),
	price_per_unit		decimal(6,2),
	primary key (product_id))
	maxrows per segment 18;
create index product_index on product(product_desc);

                
create table parent_customer (

parent_id		integer not null,
	parent_name_abbr	char(10),
	parent_name_full	char(40),
	primary key (parent_id))
maxrows per segment 3;

                
create table customer_account (
account_id		char(13) not null,
customer_id		integer not null,
parent_id		integer not null,
	cust_name_abbr		char(10),
	cust_name_full		char(40),
    	cust_type		char(4),
	sic			char(3),
	latitude		integer,
	longitude		integer,
	city_name		char(20),
	county_name		char(20),
	state_name		char(20),
	state_abbr		char(2),
	zip_code   		integer,
	bill_media		char (10),
	bill_cycle		integer,

primary key (account_id),
	foreign key (parent_id) references parent_customer (parent_id))
	maxrows per segment 32025;
create index cust_index1 on customer_account
	(state_abbr);
create index cust_index2 on customer_account
	(cust_name_full);
create index cust_index3 on customer_account
	(sic);
create index cust_index4 on customer_account
	(parent_id);

                

create table order_item (

	order_number		integer not null,
	item_number		integer not null,
	account_id		char(13) not null,
	promotion_id		integer not null,
	product_id		integer not null,
	activity_type		char(3),
	received_date_id	integer not null,
	requested_date_id	integer not null,	
	planned_date_id		integer not null,
	actual_date_id		integer not null,	

	primary key (order_number, item_number),
	foreign key (received_date_id) references date (date_key),
	foreign key (account_id) references customer_account (account_id),
	foreign key (product_id) references product (product_id),
	foreign key (promotion_id) references promotion (promotion_id),
	foreign key (requested_date_id) references date (date_key),
	foreign key (planned_date_id) references date (date_key),
	foreign key (actual_date_id) references date (date_key)
);
create star index order_index1 on order_item
	(account_id, received_date_id, actual_date_id);

                
create table product_revenue (

	product_id		integer not null,
	promotion_id		integer not null,
	account_id		char(13) not null,
	date_key		integer not null,
	quantity		integer,
	revenue			decimal (6,2),
        cost			decimal (6,2),
	primary key (date_key, promotion_id, product_id, account_id),
	foreign key (date_key) references date (date_key),
	foreign key (promotion_id) references promotion (promotion_id),
	foreign key (product_id) references product (product_id),
	foreign key (account_id) references customer_account (account_id)
);
create star index revenue_index1 on product_revenue 
	(date_key, account_id, product_id, promotion_id);
create star index revenue_index2 on product_revenue 
	(date_key, product_id);
create star index revenue_index3 on product_revenue
	(product_id, date_key);

  select product_desc, sum(revenue) as total_revenue
from date, product, product_revenue
where product.product_id = product_revenue.product_id
     and date.date_key = product_revenue.date_key
     and date.calendar_year_num = 2001
     and product_desc in ('intralata toll', 'interlata toll', 'local service', 
	'private line', '800 service', 'WATS service', 'measured service')
group by product_desc
order by total_revenue desc; 

2001 年の第 1 四半期と 2002 年の第 1四半期を比較した場合の、各カテゴリー別の収入における成長率または下降率は？

   set arithignore;
drop macro mprod_sales_by_year;
create macro mprod_sales_by_year (yr_num) as
	sum (case calendar_year_num when yr_num then revenue else 0 end);
select product_desc,
   mprod_sales_by_year (2001) as sales_01,
   mprod_sales_by_year (2002) as sales_02,
   ((sales_02- sales_01) / mprod_sales_by_year (2001)* 100 as percent_change
from date, product, customer_account, product_revenue
where product_revenue.date_key = date.date_key
      and product_revenue.product_id = product.product_id
      and product_revenue.account_id = customer_account.account_id
      and (calendar_year_num = 2001 or calendar_year_num = 2002)
      and calendar_quarter_num = 1 
      and product_desc in ('intralata toll', 'interlata toll', 'local service', 
	'private line', '800 service', 'WATS service', 'measured service')
group by product_desc
order by percent_change; 

昨年の第 4四半期において、企業顧客による注文が行われた時点から、注文への対応までに要した時間の平均は？　この期間中の注文件数は？

 elect count(*) as Num_Orders, dec(avg (datediff (day, (select date_full from date 
where order_item.actual_date_id = date.date_key), date_full)), 5, 2) as Num_Days
from customer_account, date, order_item
where customer_account.account_id = order_item.account_id
      and order_item.requested_date_id = date.date_key
      and date.calendar_year_num = 2001
      and date.calendar_quarter_num = 4
      and cust_type = 'bus';

過去 2 年間にわたる、ホリデー期間における有料通話の平均収入は？

 select dec(avg (revenue), 5, 2) as avg_revenue
from date, product, product_revenue
where product_revenue.date_key = date.date_key
      and product_revenue.product_id = product.product_id
      and day_type = 'hol'
      and (calendar_year_num = 2001 or calendar_year_num = 2002)
      and product_desc in ('interlata toll', 'intralata toll');

一年前の時点で、各商品を利用している顧客の総数は？また今年の初めと比較した場合の顧客の増減は？

 >select calendar_year_num, product_desc, 
count (distinct customer_account.customer_id) as cust_count
from date, product, customer_account, product_revenue
where product_revenue.date_key = date.date_key
      and product_revenue.product_id = product.product_id
      and product_revenue.account_id = customer_account.account_id
      and (calendar_year_num = 2000
      or calendar_year_num = 2001)
      and calendar_month_num = 12
group by calendar_year_num, product_desc
order by calendar_year_num;

本年 4 月分の料金通話の収入を3月分と比較した場合の増減は？

    select sum (revenue) as april_revenue,
   (select sum (revenue)
   from date, product, product_revenue
   where product_revenue.date_key = date.date_key
   and product_revenue.product_id = product.product_id
   and calendar_month_num = 3
   and calendar_year_num = 2002
   and product_desc in ('interlata toll', 'intralata toll')) as march_revenue
from date, product, product_revenue
where product_revenue.date_key = date.date_key
      and product_revenue.product_id = product.product_id
      and calendar_year_num = 2002
      and calendar_month_num = 4
      and product_desc in ('interlata toll', 'intralata toll');
      

年初来の売上累計を基準とした場合の、収益性の低い商品は何か？

    select product_desc, sum(revenue) as tot_rev, sum (cost) as tot_cost
from date, product, product_revenue
where product_revenue.date_key = date.date_key
      and product_revenue.product_id = product.product_id
      and calendar_year_num = 2002
      and calendar_month_num = 1
group by product_desc
having tot_rev < tot_cost;

2001 年の第 1 四半期と 2002年の第 1 四半期を比較した場合の、製品の利益幅の違い、および利益率の変化は？

  set arithignore;
 drop macro mprod_sales_by_year;
create macro mprod_sales_by_year (yr_num) as
   sum (case calendar_year_num when yr_num then revenue else 0 end);
drop macro mprod_cost_by_year;
create macro mprod_cost_by_year (yr_num) as
   sum (case calendar_year_num when yr_num then cost else 0 end);
select 
   ((mprod_sales_by_year (2001) - mprod_cost_by_year (2001)) /
     mprod_cost_by_year (2001)) as prft_mrgn_94,
   ((mprod_sales_by_year (2002) - mprod_cost_by_year (2002)) /
     mprod_cost_by_year (2002)) as prft_mrgn_95,
   ((((mprod_sales_by_year (2002 - mprod_cost_by_year (2002)) /
      mprod_cost_by_year (2002)) -
   ((mprod_sales_by_year (2001) - mprod_cost_by_year (2001)) /
      mprod_cost_by_year (2001))) /
   ((mprod_sales_by_year (2001) - mprod_cost_by_year (2001)) /
      mprod_cost_by_year (2001)) * 100) as percent_change
from date, product, product_revenue
where product_revenue.date_key = date.date_key
      and product_revenue.product_id = product.product_id
      and (calendar_year_num = 2002 or calendar_year_num = 2001)
      and calendar_quarter_num = 1;

昨年の収入が多かった上位 10 業種をランク付けしてください。

  select sic as industry, sum(revenue) as total_revenue, 
   rank (total_revenue) as top_10_revenue
from date, customer_account, product_revenue
where product_revenue.date_key = date.date_key
      and product_revenue.account_id = customer_account.account_id
      and calendar_year_num = 2001
      and calendar_quarter_num = 4
      and cust_type = 'bus'
group by sic
when top_10_revenue <= 10
order by total_revenue desc;
  

2001年の、ニューヨーク州とカリフォルニア州を比較した場合の、収入カテゴリー別の年間収入比率の違いは？

  drop macro mprod_sales;
create macro mprod_sales (state) as
	sum (case state_abbr when state then revenue else 0 end);
select product_desc,
    mprod_sales ('NY') as new_york_rev,
    mprod_sales ('CA') as calif_rev,
    ((mprod_sales ('CA') - mprod_sales ('NY')) /
    mprod_sales ('NY')) * 100 as percent_diff
from date, customer_account, product, product_revenue
where product_revenue.date_key = date.date_key
  and  product_revenue.account_id = customer_account.account_id
  and  product_revenue.product_id = product.product_id
  and  (state_abbr = 'NY' or state_abbr = 'CA')
      and calendar_year_num = 2001
group by product_desc;
      

サービス・プランやプロモーションに参加しないのはどのようなアカウントか？(場所別にリスト表示)

    select distinct(customer_account.account_id) as Account_num, state_abbr
from date, customer_account, product_revenue
where product_revenue.date_key = date.date_key
  and  product_revenue.account_id = customer_account.account_id
  and  calendar_year_num = 2001
  and calendar_month_num = 12　
  and promotion_id = 0;
      

前回実施したプロモーション中に販売数量と売上金額はどれだけ増加したか？
(過去2 年間の売上金額と販売数量の基準値を算出することにより季節変動を除去し、プロモーション期間中の値と比較できる日次の平均を割り出す)

  select sum(revenue) - 
 ((select count(distinct date.date_key) 
  from date, promotion, product, product_revenue
   where product_revenue.date_key = date.date_key
       and product_revenue.product_id = product.product_id
       and product_revenue.promotion_id = promotion.promotion_id
       and promotion.promotion_id = 4
       and product.product_desc = 'new basic service') *
    (select sum(revenue) /
     count(distinct date.date_key) 
     from date, product, product_revenue
     where product_revenue.date_key = date.date_key
        and product_revenue.product_id = product.product_id
        and date_full between '12/31/99' and '12/31/01'
        and product.product_desc = 'new basic service')) as promo_rev_chng,
 sum(product_revenue.quantity) - 
 ((select count(distinct date.date_key) 
  from date, promotion, product, product_revenue
   where product_revenue.date_key = date.date_key
       and product_revenue.product_id = product.product_id
       and product_revenue.promotion_id = promotion.promotion_id
       and promotion.promotion_id = 4
       and product.product_desc = 'new basic service') *
  (select sum(product_revenue.quantity) /
   count(distinct date.date_key)
   from date, product, product_revenue
   where product_revenue.date_key = date.date_key
       and product_revenue.product_id = product.product_id
      and date_full between '12/31/99' and '12/31/01'
      and product.product_desc = 'new basic service')) as promo_units_chng
from date, promotion, product, product_revenue
where product_revenue.promotion_id = promotion.promotion_id
      and product_revenue.date_key = date.date_key
      and product_revenue.product_id = product.product_id
      and promotion.promotion_id = 4
      and product.product_desc = 'new basic service';

上に戻る

ダウンロード

	ダウンロード形式について			Adobe® Reader® が必要

参考文献

• developerWorks Japan: Information Management : Information Managementの日本語技術情報サイトです
  
  
• developerWorks: Information Management(US) : Information Managementの英語の技術情報サイトです

著者について

		堤 保晴 ソフトウェアサポート&サービス IMテクニカルセールス

記事の評価

サイト改善のため、ご意見をお寄せください。こちらのフォームからお願いいたします。 この情報はお客様が目的を達成するうえで役に立ちましたか？ はい いいえ わからない   お気軽にご意見・ご感想をお寄せください。   この記事は役に立ちましたか？ 1 2 3 4 5 不充分・不完全である 大変素晴らしい

この記事を共有する del.icio.us newsing FC2ブックマーク Choix！ ニフティクリップ Yahoo!ブックマーク MM/memo CZブックマーク livedoorクリップ はてなブックマーク Buzzurl（バザール）

上に戻る