 | 渡辺 義章, ソフトウェアサポート&サービス IMテクニカルセールス,
IBM
2004年 11月 26日 IBM Red Brick Warehouseの連載「赤レンガ倉庫6丁目2番地 」第6回です。
はじめに
第6回目は、ビジネス・ソリューション・テンプレートを紹介する最終回です。テンプレートは、今回分を含めた4つ以外にも、多業種・多業務に渡り存在しています。テンプレートとは、業種・業務のノウハウであり、パートナー様やシステムインテグレータ（SI）から多種販売がされています。それは、パッケージ製品、又はコンサルテーションとして形態は様々です。Red Brick用に作成された分析用テンプレートは、スタースキーマ構成で作成されており、独自のSTAR indexやTARGET indexの使用により他のデータベースにない高速性を実現しています。
最後にご紹介しますテンプレートは、小売業での販売およびマーケティング分析ソリューション用テンプレートです。お客様の商品購買データを格納し、商品構成、商品の傾向、商品の収益性、競合性、市場シェアの分析、プロモーションの効果分析に利用できる内容となっています。また、POS連係でメーカにフィードバックできれば、新製品の開発にも利用できます。それでは、ソリューション・テンプレートとして提供する個々のコンポーネントの紹介から始めたいと思います。
小売業 - 販売およびマーケティング向けソリューション・テンプレート
小売業 - 販売およびマーケティング向けソリューション・テンプレートとして、提供している基本コンポーネントを御紹介いたします。今回御紹介いたしますテンプレートは、業種、業態に特化し、かつ販売・マーケティング分析に必要な基本的なデータ項目を元に作成されています。
では最初に、ソリューション・テンプレートを構成する各コンポーネントについて、紹介していきます。
ソリューション・テンプレートで提供するコンポーネント：
-
ビジネス上の質問のセット
どのような分析業務をシステム化したいかを、質問の形で表現しています。ここで紹介しているビジネス上の質問は、今回の説明で作成するシステムを想定して書かれています。実際には、このような問い合わせをシステム要件から抽出し、例えば、ここで提供していますテンプレートに不足している項目やテーブルの洗い出しを行い、実際のシステムに反映させる形で作成するのも、利用法の一つになります。ここでは、小売業における販売およびマーケティング分析の基本的な要求を質問する内容になっています。
-
論理データモデルに含まれる各エンティティと属性
小売業の販売およびマーケティングシステム構築において、必要と思われるデータ項目についての詳細情報を、このテンプレートでは提供しています。実際には、これらの情報を用いて論理データモデルを構築するのが手順としてありますが、論理データモデルに含まれる各エンティティと属性の各項目を利用して、前述したビジネス上の質問セットに答えるために必要なスキーマ構成の例を、テンプレートとして提供しています。これらの詳細情報の一部が各テーブルのデータ項目としてモデル化されています。
-
スキーマの設計及び実装例
Point of Sale (POS)というFACTテーブルが存在するスター・スキーマ構造が、今回の小売業販売およびマーケティング向けソリューション・テンプレート・スキーマになります。
ここでは、ビジネス上の質問を考慮し、1FACTの構成を採用しています。
-
テーブルの作成とスターインデックスの設定
スキーマの設計及び実装例で示したスキーマ構成を実際に作成するDDLの記述例を提供しています。またINDEXの設定、特にスタースキーマ構成に最適化されたインデックスである、スターインデックスの定義例も提供しています。スターインデックスを設定することが、IBM
Red Brick Warehouseのパフォーマンスを享受するための、必須条件となります。
-
ビジネス上の質問に答えるための SQL ステートメント
作成したスタースキーマ構造のデータベースに対して、ビジネス上の質問を行う場合に使用されるSQLステートメントの例を提供しています。SQLの記述については、この例以外にも色々と考えられると思いますが、このDMLを利用して、より最適なクエリーの記述の考察とか、要求以外の処理を実施する場合の元になるSQLとして使用することもできます。
以上に挙げたような、基本コンポーネントの全部、あるいは一部を利用して、小売業における販売およびマーケティング分析アプリケーションを作成することが可能となります。それでは、次にソリューションテンプレートを使ったシステムの構築について、構築の手順に沿って見て行きましょう。
構築手順
ソリューション・テンプレートをより効果的に使用するためには、以下の手順で作業を行うことを、推奨しています。ここでは、実際のシステム構築手順の流れを示しながら、ソリューションテンプレートの利用ポイントとその使用方法を説明いたします。
Step 1： エンティティーと属性の定義を含む論理データ・モデルについて調べます。
ビジネス上の質問セットの内容から、システムを構築する上で必要なデータ項目を洗い出し、データを検証しながら、論理データモデルを作成していきます。
このステップで提供しますソリューションテンプレートビジネス上の質問セットは、今回構築するシステムのビジネス上の質問になります。
また論理データモデルを構成する各エンティティーと属性についての詳細情報を論理データモデルに含まれる各エンティティと属性で提供します。この情報をベースに論理データモデルを作成することが可能になります。
Step 2： スキーマについて調べます。
論理データモデルを元に、テーブルスキーマの設計を行います。この時、一番の注意点は、スタースキーマ構成を前提として設計を行うため、定義するテーブルをFactとDimensionに分けるように設計します。また、論理データモデルのエンティティや属性が各テーブルのデータ項目に該当するかどうかを判断、考慮しながら設計する必要があります。今回のソリューションテンプレートには、上記手順を踏んで作成したテーブルスキーマ構造が含まれています。
Step 3： データベースに格納するデータのソースを識別する。
設計が完了したテーブルスキーマの各テーブルに対して、必要とするデータの内容についての検証を行います。この場合、予め用意したサンプルデータを利用することで、このステップでの作業効率が図れます。
Step 4： 電子媒体からサーバーにファイルをインストールします。
IBM Red Brick Warehouse 製品をインストールして、実行可能な状態にします。
Step 5： 具体的なデータ要件を正確に表わすよう、スキーマを変更します。
再度、スキーマ定義を検証し、データ要件の正確さを高め、必要であればスキーマ構造の変更を行います。
Step 6： スクリプトを実行し、データベースを作成します。
ソリューションテンプレートで提供しているデータベース作成スクリプトを、現行システム用にカスタマイズし、実行してデータベースを作成します。ここでは、テーブル作成用のスクリプトを提供しています。
Step 7： データベースにデータをロードします。
サンプルのデータ、あるいは実際に作成したデータをデータベースにロードします。
Step 8： 選択したクライアント・ツールを利用してビジネス上の質問を実施します。
テンプレートで提供しているSQLステートメントをビジネス上の質問として、クライアントツールから実行します。このステップではクエリーパフォーマンスを考慮するインデックス作成用のスクリプトと、最終目的であるビジネス上の質問に答えるための
SQL ステートメントを利用して、実際のビジネス上の質問に答えていきます。
この二つのソョリューション・テンプレート（インデックス作成用のスクリプト、ビジネス上の質問に答えるための SQL ステートメント）を有効に使うことで、最終的に出来上がったシステムのパフォーマンスと製品のクオリティを上げる効果があります。
それでは、最後にソリューション・テンプレートの詳細を示します。構築手順に従って、以下のテンプレートを利用することが、システム開発の過程で有効であることを御確認いただければと思います。
ビジネス上の質問
ソリューション・テンプレートとして提供しているビジネス上の質問ですが、今回構築するシステムの要求仕様であり、システム用件の範囲を定義しています。またこれらのビジネス上の質問に答えるために使用するSQL
ステートメントの例を、テンプレートの一部として提供しています。
商品構成の分析
質問 1：
商品カテゴリー内の、販売数量を基準とした各ブランドの構成比は？
質問 2：
ブランド内の、販売数量を基準した各商品の特徴 (サイズ、タイプ、形) の構成比は？
商品の傾向
質問 3：
選択したブランドについての、過去 3 か月間の週ごとの売上金額は？
質問 4：
そのブランド内の、週ごとの商品の平均販売数量は？
質問 5：
カテゴリー内での、そのブランドのシェアは？
商品の収益性
質問 6：
年初来の売上合計を基準とした場合の、収益性の低い商品は？
質問 7：
2000 年の第 1 四半期と 2001 年の第 1 四半期を比較した場合の、商品の利益幅の違い、および利益率の変化は？
競合の分析
質問 8：
売上金額と販売数量を基準とした、本年第 4 四半期の上位 25 ブランドは？
質問 9：
1 年前と比較した場合のカテゴリー全体におけるシェアの割合、および 1 年前と比較した場合のシェアの変化は？
市場シェアの分析
質問 10：
各商品の販売数量を基準とした場合の、特定の市場におけるシェアは？商品をブランド別にグループ化し、本年の過去 2 四半期について分析します。
プロモーションの効果
質問 11：
前回実施したプロモーション中に販売数量と売上金額はどれだけ増加したか？(過去 2 年間の売上金額と販売数量の基準値を算出することにより季節変動を除去し、プロモーション期間中の値と比較できる日次の平均を割り出します)
次に論理データモデルに含まれる各エンティティーと属性の詳細について、記述しています。
エンティティーと属性の説明
この章では、論理データ・モデルに含まれる各エンティティーと属性の定義について説明します。この属性はスキーマにも対応しています。
Retail Year (小売年度)
会計報告年に相当する、企業の時間グループです。
-
Retail Year #- 計算に使用する小売年度 (すなわち 98、99 など) を示す固有の整数です。
-
Retail Year- 小売年度を示す固有の表示名 (FY98、FY99 など) で、主にレポートで使用されます 。
Retail Quarter (小売四半期)
企業の小売年度を 4 分割した時間グループです。この時間グループは、単一の小売年度内で固有です。
-
Retail Year #- Retail Year (小売年度) から取得されます。
-
Retail Quarter #- 小売年度内の小売四半期を示す整数値 (1、2、3、4 など) で、計算に使用できます。
-
Retail Quarter- 小売四半期を示す固有の表示名 (QTR 1 FY00、QTR 2 FY00 など) で、主にレポートで使用されます。
Retail Period (小売期間)
小売年度を 12 分割した時間グループです。この時間グループは、複数の完全な週 (通常は 4 週または 5 週) で構成される必要があり、単一の小売年度内で固有です。
-
Retail Year #- Retail Year (小売年度) から取得されます。
-
Retail Period #- 小売年度内の小売期間を示す整数値 (1、2、3、4 など) で、計算に使用されます。
-
Retail Period- 小売期間を示す固有の表示名 (PD1 FY00、PD2 FT00 など) で、主にレポートで使用されます。
-
Number of Weeks- 期間内の週数を示す整数 (4、5 など) です。
-
Number of Days- 期間内の日数を示す整数 (28、35 など) です。
Retail Week (小売週)
小売年度中の連続する 7 日をグループ化したもので、企業において販売プロモーションを行う週を表します。小売週は、単一の小売年度内で固有です。
-
Retail Year #- Retail Year (小売年度) から取得されます。
-
Retail Week #- 小売年度内の小売週を示す整数値 (1、2、3 など) で、計算に使用されます。
-
Retail Week- 小売週を示す固有の表示名 (Week 1 FY00、Week 2 FY00 など) で、主にレポートで使用されます。
Calendar Year (暦年)
ユリウス暦によって定義される年です。
-
Calendar Year- 年を示す固有の整数値 (2000、2001 など) です。
Calendar Quarter (暦四半期)
暦年を、1 月から順に 3 か月ごとに区切った期間です 。単一の暦年内では、暦四半期は固有です。
-
Calendar Year- Calendar Year (暦年) から取得されます。
-
Calendar Quarter #- 暦年中の暦四半期を示す整数値で、計算に使用されます。
-
Calendar Quarter- 暦年中の暦四半期を示す固有の名前 (QTR 1 2000、QTR 2 2000 など) で、主にレポートで使用されます。
Month (月)
暦年の標準の月です。月は、単一の暦年内で固有です。
-
Calendar Year- Calendar Year (暦年) から取得されます。
-
Month- 月を表す名前 (January (1 月)、February (2 月) など) です。
-
Month #- 暦年中の月を示す整数値 (1、2、3、4) で、計算や月のソートに使用されます。
-
Abbreviated Month- 月の省略名 (JAN、FEB、MAR など) で、主にレポートで使用されます。
Day (日)
ユリウス暦を基にした、午前 0 時から始まる特定の 24 時間です。
-
Calendar Year- Calendar Year (暦年) から取得されます。
-
Month- Month (月) から取得されます。
-
Day- 特定の年の月の日を表す整数 (1、2、3、4) です。
-
Date- 月、日、および暦年を結合した値 (すなわち 5/04/00、5/06/00 など) です。
Promotion/Advertising Type (プロモーション/広告のタイプ)
販売店への集客を増やすことを目的に企業が実施を選択する、小売業の販売プロモーション/広告のタイプの定義です。
-
Promotion Type ID- プロモーションのタイプを示す固有の ID です。
-
Promotion Type- プロモーションのタイプについての固有の説明またはタイトル (ダイレクト・メール、新聞、週の広告チラシなど)
です。
Promotion/Advertisement (プロモーション/広告)
指定された期間に特定の商品について実施される特定のプロモーション/広告のタイプの、特定のオカレンスを表す関連エンティティーです。
-
Promotion Type ID- Promotion Type から取得されます。
-
Date- Day (日) から取得されます。
-
UPC- UPC から取得されます。
-
Promotion Description- プロモーションの説明 (すなわち 「プレジデント・セール - 通常価格の 10 ％
オフ」、「メモリアル・デー・セール」など) です。
-
Start Date- プロモーションが発効する開始日 (2/02/00、3/03/00 など) です。
-
End Date- プロモーションが無効になる終了日 (5/04/00、6/04/00 など) です。
-
Value- プロモーションの実際の金額的な価値 (50、10.00 など)です。
-
Units- プロモーション中に 1 度に購入可能な数量 (1、50 など) です。
-
Cost- 広告またはプロモーションの費用の総額です。
Merchandise Attribute (商品の属性)
物理的または主観的 (無形の) 特徴によって関連付けられられる値のグループです。商品の属性を使用して、商品の概要と小売店内での商品の分類方法を説明することができます。
-
Merchandise Attribute ID- 属性の固有の ID です。
-
Merchandise Attribute - 属性の固有の説明 (つまり、Vendor (ベンダー)、Department (分野)、Category
(カテゴリー)、Brand (ブランド)、Class (種類) ) です。
Merchandise Value (商品の値)
商品の特徴を説明する値です。
-
Merchandise Value ID- 値の固有の ID です。
-
Merchandise Value- 固有の値 (つまり、靴、宝石、ホーム・テキスタイル、家庭用品 、既製服など) です。
Merchandise Attribute/Value (商品の属性/値)
属性とその値の有効なすべての組み合わせを表す関連エンティティーです。
-
Merchandise Attribute ID - Merchandise Attribute (商品の属性) から取得されます。
-
Merchandise Value ID- Merchandise Value (商品の値) から取得されます。
UPC/Attribute/Value (UPC/属性/値)
特定の商品が持つ属性と値のすべての組み合わせを表す関連エンティティーです。
-
UPC- UPC から取得されます。
-
Merchandise Value ID - Merchandise Value (商品の値) から取得されます。
-
Merchandise Attribute ID- Merchandise Attribute (商品の属性) から取得されます。
Merchandise (商品)
商品階層の最上位を説明します。通常、商品とは、小売店で販売されるすべての商品を指します。
-
Merchandise ID- 各属性値の固有の識別子です。
-
Merchandise Description- Total Merchandise (すべての商品) を表す説明です。
Department (分野)
すべての商品が分けられる各グループを表します。
-
Merchandise ID- Merchandise (商品) から取得されます。
-
Department ID- 各分野の固有の ID です。
-
Department Description- 各分野の説明です。
Class (種類)
Department (分野) の下にあるサブグループで、各商品をさらに細かく定義します。
-
Merchandise ID- Merchandise (商品) から取得されます。
-
Department ID- Department (分野) から取得されます。
-
Class ID - 各種類の固有の ID です。
-
Class Description- 各種類の説明です。
Category (カテゴリー)
Class (種類) の下にあるサブグループで、各商品をさらに細かく定義します。
-
Merchandise ID - Merchandise (商品) から取得されます。
-
Department ID- Department (分野) から取得されます。
-
Class ID- Class (種類) から取得されます。
-
Category ID- 各カテゴリーの固有の ID です。
-
Category Description- 各カテゴリーの説明です。
Sub Category (サブ・カテゴリー)
Category (カテゴリー) の下にあるサブグループで、各商品をさらに細かく定義します。
-
Merchandise ID- Merchandise (商品) から取得されます。
-
Department ID- Department (分野) から取得されます。
-
Class ID - Class (種類) から取得されます。
-
Category ID- Category (カテゴリー) から取得されます。
-
Sub Category ID- サブ・カテゴリーの固有の ID です。
-
Sub Category Description- 各サブ・カテゴリーの説明です。
Vendor (ベンダー)
Sub Category (サブ・カテゴリー) の下にあるサブグループで、各商品をさらに細かく定義します。ベンダーは、各商品の製造元を表します。
-
Merchandise ID- Merchandise (商品) から取得されます。
-
Department ID- Department (分野) から取得されます。
-
Class ID- Class (種類) から取得されます。
-
Category ID- Category (カテゴリー) から取得されます。
-
Sub Category ID- Sub Category (サブ・カテゴリー) から取得されます。
-
Vendor ID- 各ベンダーの固有の ID です。
-
Vender Description- 各ベンダーの説明です。
Brand (ブランド)
Vendor (ベンダー) の下にあるサブグループで、各商品をさらに細かく定義します。ブランドは、製造元が各商品に付けた商標を表します。
-
Merchandise ID- Merchandise (商品) から取得されます。
-
Department ID- Department (分野) から取得されます。
-
Class ID - Class (種類) から取得されます。
-
Category ID- Category (カテゴリー) から取得されます。
-
Sub Category ID- サブ・カテゴリーの固有の ID です。
-
Vendor ID- 各ベンダーの固有の ID です。
-
Brand ID- 各ブランドの固有の ID です。
-
Brand Description- 各ブランドの説明です。
Item (品目)
Brand (ブランド) の下にあるサブグループで、各商品をさらに細かく定義します。
品目は、同種の商品のグループを表し、小売店が商品の注文に使用します。
-
Merchandise ID- Merchandise (商品) から取得されます。
-
Department ID- Department (分野) から取得されます。
-
Class ID - Class (種類) から取得されます。
-
Category ID- Category (カテゴリー) から取得されます。
-
Sub Category ID- Sub Category (サブ・カテゴリー) から取得されます。
-
Sub Category Description- 各サブ・カテゴリーの説明です。
-
Vendor ID- Vendor (ベンダー) から取得されます。
-
Brand ID- Brand (ブランド) から取得されます。
-
Item ID - 各品目の固有の ID です。
-
Item Description- 各品目の説明です。
SKU
Item (品目) の下にあるサブグループで、各商品をさらに細かく定義します。SKU (在庫保管単位) は、品目の特徴、色、および/または状態を定義します。
-
Merchandise ID- Merchandise (商品) から取得されます。
-
Department ID- Department (分野) から取得されます。
-
Class ID - Class (種類) から取得されます。
-
Category ID- Category (カテゴリー) から取得されます。
-
Sub Category ID- Sub Category (サブ・カテゴリー) から取得されます。
-
Vendor ID- Vendor (ベンダー) から取得されます。
-
Brand ID- Brand (ブランド) から取得されます。
-
Item ID - 各品目の固有の ID です。
-
SKU- 各 SKU の固有の ID です。
-
SKU Description- 各 SKU の説明です。
UPC
企業が販売用に提供する特定の商品を表します。UPC (汎用製品コード) は、顧客が購入する特定の商品を一意に識別します。
-
UPC- 商品の固有の ID です。
-
SKU- SKU から取得されます。
-
UPC Description- 各 UPC の固有の説明です。
-
Cost- その商品を購入するために支払われる金額です。
Raw Material (仕入れ品)
小売店がベンダーから購入し、最終的にその小売店が販売する商品の UPC です。
-
UPC- 特定の商品の固有の ID です。
-
SKU - SKU から取得されます。
-
UPC Description- 各 UPC の固有の説明です。
-
Cost- その商品を購入するために支払われる金額です。
Merchandising/Distribution Process (商品/配送プロセス)
商品をベンダーに注文して購入するプロセスを表す関連エンティティーです。このプロセスは、出荷元、担当者、使用する設備、および目録への記載と販売店への配送において、購入した
UPC で行う必要のある作業を定義します。
-
Step ID- Merchandising & Distribution Step (商品/配送ステップ) から取得されます。
-
Employee ID- Employee (従業員)/Equipment (設備) から取得されます。
-
Equipment ID- Employee (従業員)/Equipment (設備) から取得されます。
-
Store ID- Store (販売店) から取得されます。
-
UPC- UPC から取得されます。
-
SKU- SKU から取得されます。
-
Cost- その商品を購入するために支払われる金額です。
-
Units Purchased- 購入される商品の数量です。
-
Freight Costs- 商品の出荷費用です。
-
Mark Up- 利益を得るために商品価格に付加される金額です。
Merchandising & Distribution Step (商品/配送ステップ)
商品の注文、購入、および配送のために実施する必要がある作業のタイプです。
-
Step ID- プロセス内の各ステップの固有の ID です。
-
Step Description- ステップの固有の説明です。
-
Step Specification- 実施する必要があるステップの説明です。
Employee (従業員)
企業内で特定の仕事を行うために雇われている個人です。
-
Employee ID - 従業員の固有の ID です。
-
Employee First Name- 従業員の名です。
-
Employee Last Name- 従業員の姓です。
-
Social Security #- 従業員の社会保障番号です。
-
Salary- 従業員の給与の金額です。
-
Head Count Cost- 従業員のための経費の金額です。
-
Equipment Cost - 従業員が仕事を行うために必要な仕入れ品/設備の費用です。
-
Benefit Cost- 企業が給付金として従業員に支払う金額です。
Equipment (設備)
商品の注文、販売、および配送のプロセスで従業員が使用する設備です。
-
Equipment ID- 設備の固有の ID です。
-
Store ID - Store (販売店) から取得されます。
-
Equipment Description- 設備の固有の説明です。
-
Unit Cost- 設備を購入またはレンタルするために支払う金額です。
-
Operating Cost- 設備を運用するために支払う金額です。
-
Production Capacity- 指定時間内に生産できる商品数です。
-
Life Span- 設備の耐用期間です。
-
Purchase Date- 設備を購入した日付です。
Employee/Equipment (従業員/設備)
特定の設備の操作経験を持つ従業員を表す関連エンティティーです。
-
Employee ID- Employee (従業員) から取得されます。
-
Equipment ID - Equipment (設備) から取得されます。
-
Store ID- Store (販売店) から取得されます。
-
Years of Experience - 設備の操作経験年数です。
Chain (チェーン)
企業の販売構造全体を表します。
-
Chain ID- 各チェーンの固有の ID です。
-
Chain Description- 各チェーンの固有の名前です。
Division (地方)
Chain (チェーン) の下にあるサブグループで、販売店の販売構造をさらに細かく定義します。通常は、実際の地理的領域に対応します。
-
Chain ID- Chain (チェーン) から取得されます。
-
Division ID- 各地方の固有の ID です。
-
Division Description- 各地方の固有の名前です。
Region (地域)
Division (地方) の下にあるサブグループで、販売店の販売構造をさらに細かく定義します。通常は、実際の地理的領域に対応します。
-
Chain ID- Chain (チェーン) から取得されます。
-
Division ID- 各地方の固有の ID です。
-
Region ID- 各地域の固有の ID です。
-
Region Description- 各地域の固有の名前です。
District (地区)
Region (地域) の下にあるサブグループで、販売店の販売構造をさらに細かく定義します。通常は、実際の地理的領域に対応します。
-
Chain ID- Chain (チェーン) から取得されます。
-
Division ID- 各地方の固有の ID です。
-
Region ID- 各地域の固有の ID です。
-
District ID- 各地区の固有の ID です。
-
District Description- 各地区の固有の名前です。
City (都市)
Region (地域) の下にある場所で、販売店の販売構造をさらに細かく定義します。販売店が置かれている都市を表します。
-
Chain ID- Chain (チェーン) から取得されます。
-
Division ID- 各地方の固有の ID です。
-
Region ID- 各地域の固有の ID です。
-
District ID- 各地区の固有の ID です。
-
City ID- 各都市の固有の ID です。
-
City Description- 各都市の名前です。
Store (販売店)
企業が所在し、商品の受領や、顧客に商品を販売するためのサービス提供を行う実際の場所/建物です。
-
Chain ID- Chain (チェーン) から取得されます。
-
Division ID- 各地方の固有の ID です。
-
Region ID- 各地域の固有の ID です。
-
District ID- 各地区の固有の ID です。
-
City ID- 各都市の固有の ID です。
-
Store ID- 販売店の場所の固有の ID です。
-
Market ID- Market (市場) から取得されます。
-
Store Description- 販売店の場所の固有の名前です。
Market (市場)
通常、都市エリアと一致し、その都市エリアのテレビ放送の視聴可能域によってカバーされる、特定の地理的領域です。
-
Market ID- 市場の固有の ID です。
-
Market Description- 市場の固有の説明です。
Overhead Type (経費のタイプ)
企業を運営するために使われるリソースで、商品の販売や配送には直接関係しません。
-
Overhead Type ID- リソースの固有の ID です。
-
Overhead Type Description- リソースの固有の説明です。
Overhead/Store (経費/販売店)
各販売店で発生する経費を表す関連エンティティーです。
-
Store ID- Store (販売店) から取得されます。
-
Overhead Type ID- Overhead Type (経費のタイプ) から取得されます。
-
Expense- リソースの使用に対して請求される金額です。
-
Date- 経費が請求された日付です。
State (州)
米国の構成単位の 1 つを表します。
-
State- 米国内の特定の地理的領域を示す固有の名前です。
-
Abbreviated State- 州の 2 文字の省略名です。
County (郡)
米国内の州内の地域です。
-
County Code- 米国内の郡を表す固有の ID です。
-
State- State (州) から取得されます。
-
County- 郡の名前です。
Zip Code (郵便番号)
郡内の地域を示すコードであり、米国郵政公社によって、郵送地域を一意に識別するために使われます。
-
Zip Code- 米国内の特定の地域を現す固有の ID です。
-
County Code- County (郡) から取得されます。
Customer (顧客)
企業が販売する商品を購入したことのある、または購入する可能性のある個人または組織です。
-
Customer ID- 顧客の固有の ID です。
-
Zip Code- Zip Code (郵便番号) から取得されます。
-
Customer Name- 顧客の固有の名前です。
Demographic Value (人口統計値)
顧客の特徴を示す値です。
-
Demographic Value ID- 値の固有の ID です。
-
Demographic Value- 固有の値です。
Demographic Attribute (人口統計上の属性)
物理的または主観的 (無形の) 特徴によって関連付けられられる値のグループです。これを使用して、顧客を説明することができます。
-
Demographic Attribute ID- 属性の固有の ID です。
-
Demographic Attribute- 属性の固有の説明です。
Demographic Value/Attribute (人口統計値/属性)
属性とその値の有効なすべての組み合わせを表す関連エンティティーです。
-
Demographic Value ID- Demographic Value (人口統計値) から取得されます。
-
Demographic Attribute ID - Demographic Attribute (人口統計上の属性)から取得されます。
Customer Demographics (顧客の人口統計)
顧客が持つ属性と値のすべての組み合わせを表す関連エンティティーです。
-
Customer ID- Customer (顧客) から取得されます。
-
Demographic Attribute ID- Demographic Attribute (人口統計上の属性) から取得されます。
-
Demographic Value ID- Demographic Value (人口統計値) から取得されます。
Point of Sale (POS)
顧客が特定の日に特定の商品を購入したというオカレンスです。
-
Date- Day (日) から取得されます。
-
Store- Store (販売店) から取得されます。
-
Customer ID- Customer (顧客) から取得されます。
-
UPC- UPC から取得されます。
-
Unit Price- 顧客が支払う単価です。
-
Dollar Sales- 顧客が商品に対して支払う合計金額です。
-
Unit Sales- 顧客が購入した商品の数量です。
-
Mark Down- 商品を販売するための割引額です。
スキーマの設計
小売業の販売およびマーケティング・ソリューション・テンプレートのスキーマの設計は、食料品店の品目の動向の分析、品目の収益性の分析、プロモーションの効果の分析、および収益の分析をサポートします。このスキーマには、Point
of Sale (POS) という 1 つのファクト表が含まれています。
Point of Sale (POS) ファクト表には、特定の日に顧客が購入した各商品のレコードが含まれています。このファクト表には、Store (販売店)、Promotion/Ad
(プロモーション/広告)、UPC、Customer (顧客)、Period (期間) の各ディメンションが関連付けられています。
ここで示すスキーマの設計は、考えられる数多くの設計の 1 つにすぎません。このような設計への一般的な拡張としては、集約表の使用があります。Customer
(顧客) のディメンションが非常に大きく、ディメンションの分析に Customer (顧客) の制約条件を必要としない場合には、集約表（＊1）が役立ちます。
（＊1）集約表
Red BrickのVista機能を使用し集約表を管理します。この機能はデータ一貫性を保証します。
小売業の販売およびマーケティングのスキーマ
拡大図
テーブルの作成とスターインデックスの設定
それでは、ソリューションテンプレートのサンプルとして提供いたします、テーブルスキーマを構成する、個々のテーブル作成用のスクリプトとインデックス作成用のスクリプトを一つの例としてご紹介いたします。
Period （期間）
create table period (
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 ;
|
Store (販売店)
create table store (
store_id integer not null,
chain_id integer,
chain_desc char(30),
division_id integer,
division_desc char(30),
region_id integer,
region_desc char(30),
district_id integer,
district_desc char(30),
city_id integer,
city_desc char(30),
store_desc char(30),
market_id integer,
market_desc char(30),
primary key (store_id))
maxrows per segment 100 ;
|
UPC
create table upc (
upc char(11) not null,
merchandise_id integer,
merchandise_desc char(30),
department_id integer,
department_desc char(30),
class_id integer,
class_desc char(30),
category_id integer,
category_desc char(30),
sub_category_id integer,
sub_category_desc char(30),
vendor_id integer,
vendor_desc char(30),
brand_id integer,
brand_desc char(30),
item_id integer,
item_desc char(30),
sku char(11),
sku_desc char(80),
upc_desc char(80),
est_oper_cost_upc decimal(9,2),
primary key (upc))
maxrows per segment 1000 ;
|
Promotion_Ad (プロモーション/広告)
Create table promotion_ad(
promotion_id integernotnull,
promotion_type_id integer,
promotion_type char(30),
promotion_desc char(30),
start_date date,
end_date date,
total_cost decimal(9,2),
primary key (promotion_id))
maxrows per segment 100;
|
Customer (顧客)
create table customer (
customer_id integer not null,
state char(20),
abbrev_state char(2),
county_code integer,
county char(30),
zip_code integer,
customer_name char(40),
primary key (customer_id))
maxrows per segment 27230 ;
|
Point of Sale (POS)
create table point_of_sale (
date_key integer not null,
customer_id integer not null,
store_id integer not null,
promotion_id integer not null,
upc char(11) not null,
unit_sell_price decimal (9,2),
dollar_sales decimal (9,2),
unit_sales decimal (9,2),
promoted_dollars decimal (9,2),
promoted_units decimal (9,2),
mark_up decimal (9,2),
mark_down decimal (9,2),
tot_est_upc_cost decimal (9,2),
est_upc_ad_cost decimal (9,2),
primary key (date_key,customer_id,
store_id,promotion_id, upc),
foreign key (date_key) references period (date_key),
foreign key (customer_id) references customer (customer_id),
foreign key (store_id) references store (store_id),
foreign key (promotion_id) references promotion_ad
(promotion_id),
foreign key (upc) references upc (upc));
create star index point_of_sale_index1 on point_of_sale
(date_key, store_id, upc);
|
ビジネス上の質問に答えるための SQL ステートメント
質問 1：
商品カテゴリー内の、販売数量を基準とした各ブランドの構成比は？
select brand_desc, sum(unit_sales) as total_units, ratiotoreport
(sum(unit_sales)) * 100 as pct_of_total_units
from period, upc, point_of_sale
where point_of_sale.date_key = period.date_key
and point_of_sale.upc = upc.upc
and category_id = 7 and calendar_year_num = 2000
and calendar_month_num = 12
group by brand_desc ;
|
質問 2：
ブランド内の、販売数量を基準した各商品の特徴
(サイズ、タイプ、形) の構成比は？
select brand_desc, upc_desc, sum(unit_sales) as total_units,
ratiotoreport(sum(unit_sales)) * 100 as
pct_of_total_units
from period, upc, point_of_sale
where point_of_sale.date_key = period.date_key and
point_of_sale.upc = upc.upc and brand_id = 3
and calendar_year_num = 1999 and calendar_month_num = 12
group by brand_desc, upc_desc ;
|
質問 3：
選択したブランドについての、過去 3 か月間の週ごとの売上金額は？
select fiscal_week_num, sum(dollar_sales) as weekly_sales
from period, upc, point_of_sale
where point_of_sale.upc = upc.upc and point_of_sale.date_key =
period.date_key and fiscal_year_num = 2000
and fiscal_month_num in (10, 11, 12) and brand_id = 1
group by fiscal_week_num
order by fiscal_week_num ;
|
質問 4：
そのブランド内の、週ごとの商品の平均販売数量は？
select brand_desc, fiscal_week_num, avg(unit_sales) as avg_num_prod
from period, upc, point_of_sale
where point_of_sale.upc = upc.upc and point_of_sale.date_key =
period.date_key and fiscal_year_num = 1998
and fiscal_month_num in (1, 2, 3) and brand_id = 6
group by brand_desc, fiscal_week_num
order by fiscal_week_num ;
|
質問 5：
カテゴリー内での、そのブランドのシェアは？
select sum(dollar_sales) as total_brand, dec(100 * sum(dollar_sales) /
(select sum(dollar_sales)
from period p2, upc u2, point_of_sale pos2
where pos2.upc = u2.upc and pos2.date_key = p2.date_key and
p2.fiscal_year_num = p1.fiscal_year_num
and p2.fiscal_month_num = p1.fiscal_month_num and u2.category_id =
u1.category_id), 7, 2) as ct_of_category
from period p1, upc u1, point_of_sale pos1
where pos1.upc = u1.upc and pos1.date_key = p1.date_key and
p1.fiscal_year_num = 2000 and
p1.fiscal_month_num = 6 and u1.category_id = 1 and u1.brand_id = 1
group by fiscal_year_num, fiscal_month_num, category_id, brand_id ;
|
質問 6：
年初来の売上合計を基準とした場合の、収益性の低い商品は？
select upc_desc, sum(dollar_sales) as tot_rev,
sum (tot_est_upc_cost) as tot_cost
from period, upc, point_of_sale
where point_of_sale.date_key = period.date_key and
point_of_sale.upc = upc.upc and calendar_year_num = 2001
and calendar_month_num = 1
group by upc_desc
having tot_rev < tot_cost;
|
質問 7：
2000 年の第 1 四半期と 2001 年の第
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
dollar_sales 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 tot_est_upc_cost else 0 end);
select ((mprod_sales_by_year (2000) -
mprod_cost_by_year (2000)) /
mprod_cost_by_year (2000)) as prft_mrgn_00,
((mprod_sales_by_year (2001) - mprod_cost_by_year (2001)) /
mprod_cost_by_year (2001)) as prft_mrgn_01,
((((mprod_sales_by_year (2001) - mprod_cost_by_year (2001)) /
mprod_cost_by_year (2001)) -
((mprod_sales_by_year (2000) - mprod_cost_by_year (2000)) /
mprod_cost_by_year (2000))) /
((mprod_sales_by_year (2000) - mprod_cost_by_year (2000)) /
mprod_cost_by_year (2000)) * 100) as percent_change
from period, upc, point_of_sale
where point_of_sale.date_key = period.date_key and
point_of_sale.upc = upc.upc
and (calendar_year_num = 2001 or calendar_year_num = 2000)
and calendar_quarter_num = 1;
|
質問 8：
売上金額と販売数量を基準とした、本年第
4 四半期の上位 25 ブランドは？
select brand_desc as brand, sum(dollar_sales) as tot_dollars,
sum(unit_sales) as tot_units,
rank(tot_dollars) as top_25_dollars, rank(tot_units) as unit_rank
from period, upc, point_of_sale
where point_of_sale.date_key = period.date_key and
point_of_sale.upc = upc.upc
and calendar_year_num = 2000 and calendar_quarter_num = 4
group by brand when top_25_dollars <= 25
order by tot_dollars desc ;
|
質問 9：
1年前と比較した場合のカテゴリー全体におけるシェアの割合、および
1 年前と比較した場合のシェアの変化は？
set arithignore;
drop macro pos_sales;
create macro pos_sales(yr) as sum(case calendar_year_num
when yr then dollar_sales else 0 end);
drop macro category_sales;
create macro category_sales(yr, category) as (select sum(dollar_sales)
from period, upc, point_of_sale
where point_of_sale.date_key = period.date_key and point_of_sale.upc =
upc.upc and calendar_year_num = yr
and category_id = category);
select brand_desc, pos_sales(2000) as first_yr,
pos_sales(2001) as second_yr,
((pos_sales(2001) - pos_sales(2000)) / pos_sales(2000)) * 100 as percent_chg,
pos_sales(2000) / category_sales(2000, 1) * 100 as share_first_yr,
pos_sales(2001) / category_sales(2001, 1) * 100 as share_second_yr,
((pos_sales(2001) / category_sales(2001, 1)) - (pos_sales(2000) /
category_sales(2000, 1))) /
(pos_sales(2000) / category_sales(2000, 1)) as percent_share_chg
from period, upc, point_of_sale
where point_of_sale.date_key = period.date_key and point_of_sale.upc = upc.upc
and (calendar_year_num = 2000 or calendar_year_num = 2001) and
category_id = 1
group by brand_desc ;
|
質問 10：
各商品の販売数量を基準とした場合の、特定の市場におけるシェアは？商品をブランド別にグループ化し、本年の過去
2 四半期について分析します。
set arithignore;
drop macro pos_units;
create macro pos_units(qtr) as sum(case calendar_quarter_num when qtr
then unit_sales else 0 end);
drop macro market_units;
create macro market_units(qtr, market) as
(select sum(unit_sales)
from period, upc, store, point_of_sale
where point_of_sale.date_key = period.date_key and
point_of_sale.upc = upc.upc
and point_of_sale.store_id = store.store_id and
calendar_quarter_num = qtr and market_id = market);
select brand_desc, pos_units(1) as first_qtr, pos_units(2) as second_qtr,
pos_units(1) / market_units(1, 10) * 100 as market_share_first_qtr,
pos_units(2) / market_units(2, 10) * 100 as market_share_second_qtr
from period, upc, store, point_of_sale
where point_of_sale.date_key = period.date_key and point_of_sale.upc = upc.upc
and point_of_sale.store_id = store.store_id and calendar_year_num = 2001
and (calendar_quarter_num = 1 or calendar_quarter_num = 2) and market_id = 10
group by brand_desc ;
|
質問 11：
前回実施したプロモーション中に販売数量と売上金額はどれだけ増加したか？(過去
2 年間の売上金額と販売数量の基準値を算出することにより季節変動を除去し、プロモーション期間中の値と比較できる日次の平均を割り出します)
select sum(dollar_sales) - ((select count(distinct period.date_key)
from period, promotion_ad, point_of_sale
where point_of_sale.date_key = period.date_key
and point_of_sale.promotion_id = promotion_ad.promotion_id
and promotion_ad.promotion_id = 4) *
(select sum(dollar_sales) / count(distinct period.date_key)
from period, point_of_sale
where point_of_sale.date_key = period.date_key
and date_full between '12/31/98' and '12/31/00'))
as promo_dollar_chg,
sum(point_of_sale.unit_sales) -
((select count(distinct period.date_key)
from period, promotion_ad, point_of_sale
where point_of_sale.date_key = period.date_key
and point_of_sale.promotion_id = promotion_ad.promotion_id
and promotion_ad.promotion_id = 4) *
(select sum(point_of_sale.unit_sales) / count(distinct period.date_key)
from period, point_of_sale
where point_of_sale.date_key = period.date_key
and date_full between '12/31/98' and '12/31/00'))
as promo_unit_chg
from period, promotion_ad, point_of_sale
where point_of_sale.promotion_id = promotion_ad.promotion_id
and point_of_sale.date_key = period.date_key
and promotion_ad.promotion_id = 4;
|
|
ダウンロード | 内容 | ファイル名 | サイズ | ダウンロード形式 |
|---|
| PDF形式 | redbrick06.pdf | 185KB | HTTP |
|---|
参考文献
著者について | | | 渡辺 義章
ソフトウェアサポート&サービス IMテクニカルセールス |
記事の評価
| |
