第 5 章 「サイト分析 ～ Analysis ～」～ Commerce Server 2002 で学ぶ ASP.NET ～
NRI ラーニングネットワーク株式会社
溝端 二三雄
2003 年 2 月 17 日
目次
.gif){kind=icon}
5-1 サイト分析のためのインフラ構築の必要性
5-2 Subject Matter データベースのスキーマ設計
5-3 ディメンショナル モデリング
5-5 DTS を使用したデータロード
5-5-1 接続
5-5-2 ディメンションテーブルへのデータロード
5-5-3 ファクトテーブルへのデータロード
5-6 OLAP キューブの構築
5-6-1 ディメンションの定義
5-6-2 OLAP キューブの作成
5-7 Ｃｏｍｍｅｒｃｅ Server の分析・対象化機能
これまで、E コマースサイトのプロファイル機能、カタログ管理機能、オーダー管理機能について Commerce Server 2002 をモデルとした設計・構築の考え方を紹介してきました。本章では、構築された E コマースサイトにサイト分析機能を付加するためのインフラ設計とその実装について説明します。
従来のビジネスにおいて商品を供給する企業側と消費者の間には流通に関わる卸業者や小売店舗が介在し、商品普及のためのマーケッティング活動、商品の注文・購入などの購買行動、料金の支払い、発送などのプロセスは独立して行われてきました。したがって商品を購買される顧客も匿名性が高く、顔が見えない顧客に対してプロダクトアウトな販売形態がとられがちでした。
しかし、インターネット上で展開される E ビジネスサイトでは、これらのプロセスをひとつのサイト上に完結させることができるため、サイトに集約されるユーザー情報、受注情報などの分析を行うことで、市場ニーズを汲み取ることだでき、ビジネスをマーケットインの流れへ切り替えていくことができます。
5-1 サイト分析のためのインフラ構築の必要性
E コマースサイトの分析を行う場合、これまで構築してきたデータベースをそのまま利用することはできません。オンライントランザクション処理(OLTP)用に最適化されたテーブルスキーマは、新規データの挿入、既存データの参照、更新、削除を複数のユーザーが同時に高速かつ安全に行えるよう設計されています。これは利用者から受注出荷情報や入出庫情報などのように「今、現在のステータスがどうなのか」という問いに対する正確な答えを提供するためのデータベースです。
一方、オンライン分析処理 (OLAP) 用のシステムでは、業務システムから抽出された過去の膨大な履歴データが蓄積されます。分析者は、これらのデータから一定のパターンや異常値などを発見するため、さまざまな角度からの分析を行います。一般的にはOLAP用のデータベースは、企業規模をカバーするセントラルデータウェアハウスや部門単位のデータマートという形で形成され、そこから更に分析者のニーズに合った多次元分析用のデータストアとしてキューブを作成します。このため業務用のデータベースをソースとして、それを変換して利用するため、図のようなインフラを構築していく必要があります。
.gif)
図 5-1 サイト分析のためのインフラ構成
5-2 Subject Matter データベースのスキーマ設計
OLAP 用のデータストアになるセントラルデータウェアハウスやデータマートの特性にサブジェクト指向であることがあげられます。サブジェクト指向とは分析者が分析の対象としたい主題 (Subject) を中心としてデータを収集し統合することです。では、今回のサンプルアプリケーションで使用しているデータベースを使用し、サブジェクト指向なテーブルスキーマに変換してみます。図 5-2 が、Fabrikam データベースの正規化されたテーブル構造です。サンプルアプリケーションなので最低限のテーブル構造しか定義されていませんが、この OLTP 用のテーブルスキーマを基にして OLAP 分析用のスキーマを考えてみましょう。
.jpg)
図 5-2 Fabrikam データベースの正規化されたテーブル構造
ここでまず分析者が、何をサブジェクトとして分析したいかを考えてみます。例えば分析者がマーケテイング担当者であれば、「何がよく売れているのか?」、「たくさん購入してくれるお客様は誰だろうか?」、または「たくさん購入してくれる客層はどこなのだろうか?」、「年間を通じて売上はどのような推移をしているのだろうか?」、「Web サイトで行った販促キャンペーンは効果があったのだろうか?」などを調べたいと思うでしょう。ここから分析用のテーブルスキーマを考えます。分析対象として、期間の顧客情報、商品情報、売上、プロモーション情報などをサブジェクトとして扱うことを考え、分析対象となる数値項目 (ファクト) を洗い出します。
この例では売上金額、売上数量、経費、利益などがファクトになります。これらの分析対象は日々の販売トランザクションから作成されるため膨大な量になります。分析者は、これらを 1 件ずつ確認することはできないため、このファクト値を、日別、週別、月別、四半期別、または商品カテゴリ別、顧客の所属している地域別などのレベルまで集計されたデータを使用することになります。この分析の切り口となる集約レベルの度合いを粒度と呼んでいます。時間軸で考えると最も詳細な日々のトランザクションデータが最も粒度が細かく「詳細データ」と呼ばれます。これが日別、週別、月別、四半期別、年別と集計レベルが高くなります。この集計されたデータを 「要約データ」 とも呼びます。図は、ファクト値を格納するテーブルの記憶領域のサイズと集計レベル、粒度の関係を示しています。より細かい粒度のデータを分析対象とする場合、必要な記憶領域のサイズも増えてしまいパフォーマンスへの影響がでてくるため、粒度の選択は慎重に行う必要があります。
.gif)
図 5-3 ファクトテーブルの粒度
5-3 ディメンショナル モデリング
ディメンショナルモデリングでは、分析対象となる数値データ (ファクト) を考え、どのようにそれを分析するのかという分析の切り口を明確にします。この分析の切り口となる視点を次元 (ディメンション) と呼びます。具体的にはファクトを分析するとき時間ごと (When) にとか、顧客別で (Who)、商品ごと (What) に、地域別で (Where) など、購買動機は? (Why) などといった分析の切り口 (次元) が考えられます。そして次元ごとに分析に必要とされる集計レベルを確認します。これらの内容を使用してディメンショナル モデリングを行います。
図 5-4 は、今回使用したサンプルアプリケーションのデータベースを使用し、売上金額を分析対象として作成したディメンショナル モデリングによるダイアグラムの例です。真中に置かれているテーブルはファクトデータを格納するためファクトテーブルと呼ばれます。その周りに配置されているのは、ファクトを説明するためのデータフィールドが定義される次元 (ディメンション) テーブルになります。ディメンションテーブルの各列が分析の切り口の各レベルを構成することになるのですが、第 1 章で説明してきた Profile クラスにより、分析者が途中で顧客ごとの売上について、「性別による傾向が知りたい」とか 「年齢別でも分析してみたい」 と言い出したとしても、ディメンションテーブルとそのソースになる UserObject テーブルに性別や年齢といった顧客のプロパティとなる列フィールドを追加するだけで、アプリケーションがその変更に対応しうる設計となっていたため保守の工数が低減されます。
.jpg)
図 5-4 Subject Matter データベースで採用されるスタースキーマ
5-4 代理キーによるディメンションテーブルとファクトテーブルの関連付け
製品次元 (product_dim) テーブルと顧客次元 (customer_dim) テーブルでは代理キーを使用しています。これはいずれもソーステーブルでキー列が整数型ではなくて文字型のデータフィールドとして定義されているためで、なるべくファクトテーブルのサイズを小さくするために、整数型の代理キーを使用するよう変更しています。
また、一般的に、組織変更などがあると、階層内でキーが移動します。たとえば、営業の社員が新しい担当地区に異動した場合、異動日より前の元の地区での、その社員の売上げデータと、異動後の新しい地区での売上げデータの両方を参照したいと考えます。そのためには、社員のレコードは社員ディメンション テーブルの 2 つの場所にメンバーとしてなくてはなりません。しかし、社員番号をディメンション テーブルの主キーとして使用してしまうと、重複データとして扱われてしまいます。代理キーを使用することで、同じ社員をディメンション階層の複数の位置に配置することができるようになります。
この社員番号は、アプリケーションキーとも呼ばれますが、テーブル内の結合には使用せず、別の列に格納しておく必要があります。そうすることで、社員についての情報をディメンション テーブルでその社員のレコードが出現する回数には関係なく要約することも可能になります。
代理キーの実装は、IDENTITY プロパティ列として定義し、データロード時にシステムで自動生成するようにします。
5-5 DTS を使用したデータロード
データ分析用のデータストアのテーブルスキーマが定義されたら、業務システムからのデータロードを行い、分析者が必要とする分析が行えることを検証します。不正データのチェック、スキーマ変換などもこのデータロードのステップで行います。ツールとしては、SQL Server のデータ変換サービス (DTS) を使用します。DTS は、データの変換元と変換先に接続し、列マッピング機能、SQL クエリ、ActiveX スクリプティングなどのさまざまなデータ変換機能を使用しデータロードを行えます。
5-5-1 接続
図 5-5 は、データ変換サービス (DTS) デザイナの初期画面です。まず、データの変換元と変換先に接続する必要があります。操作は、[接続] ツールバーで、目的の接続をデータ変換サービス (DTS) デザイナのデザイン シートまでドラッグすることで行えますが、保守性を考え、データリンク接続を使用し接続定義を外部ファイル化することをお勧めいたします。
.jpg)
図 5-5 データ変換サービス (DTS) デザイナ
5-5-2 ディメンションテーブルへのデータロード
業務データベースに置かれたソーステーブルからディメンションテーブルへのデータロードを行う際、スキーマ変換やデータ変換を行う必要が生じる場合があります。ここでは、データ変換タスクの ActiveX スクリプト変換機能を使用する例をご紹介します。データ変換タスクを使用すると図のように変換元の各列と変換先の各列をマッピングできます。
.jpg)
図 5-6 データ変換タスクによる列マッピング
この図 5-6 から、変換先の Customer_Name 列が、変換元の FirstName 列と LastName 列から結合されていること、また、変換先の Country 列、Prefecturte 列、City 列は、変換元の Address 列を分解していることがわかります。これらの列の分解、結合は ActiveX スクリプティングで行えます。サンプルコードは、変換元列の結合により、Customer_Name 列を作るためのスクリプトです。
Function Main()
DTSDestination("Customer_Name") = DTSSource("FirstName") + " " + DTSSource("LastName")
Main = DTSTransformStat_OK
End Function
代理キーとして設定された Customer_Dim_Key 列は Identity プロパティ列として定義されるので、作成された DTS パッケージの実行中に連番が自動生成されます。また、図は、時間次元のベースとなるテーブルのスキーマのサンプルですが、ここに格納するデータは、下記のストアドプロシジャで作成することができます。
.jpg)
図 5-7 時間次元テーブルのスキーマ
時間次元のメンバを生成するストアドプロシジャー
CREATE PROCEDURE demo @p_start_date DATETIME, @p_end_date DATETIME AS DECLARE @full_date DATETIME, @day_of_month INTEGER, @day_of_year INTEGER, @day_full_name VARCHAR(30), @week_number INTEGER, @week_full_name VARCHAR(30), @month_full_name VARCHAR(10), @month_number INTEGER, @calendar_year INTEGER, @quarter INTEGER WHILE @p_start_date < @p_end_date BEGIN SELECT @full_date = @p_start_date, @day_of_month = DATEPART(DD, @p_start_date), @day_of_year = DATEPART(DY, @p_start_date), @day_full_name = UPPER(DATENAME(weekday, @p_start_date)), @week_number = DATEPART(WK, @p_start_date), @month_full_name = UPPER(DATENAME(month, @p_start_date)), @month_number = DATEPART(M, @p_start_date), @calendar_year = DATEPART(YYYY, @p_start_date), @quarter = DATEPART(QQ, @p_start_date) INSERT INTO time_dim (full_date_app, day_of_month, day_of_year, day_full_name, week_number, month_full_name, month_number, quarter, calendar_year) VALUES (@full_date, @day_of_month, @day_of_year, @day_full_name, @week_number, @month_full_name, @month_number, @quarter, @calendar_year) SELECT @p_start_date = @p_start_date+1 END GO
5-5-3 ファクトテーブルへのデータロード
ファクトテーブルへのデータロードは、DTS デザイナで変換元と変換先の接続を定義し、データ変換タスクの変換元で、下記の様なファクトテーブルとディメンションテーブル間の内部結合を使用した SQL クエリを記述します。
SELECT Fabrikam_Mart.dbo.product_dim.Product_Dim_Key, Fabrikam_Mart.dbo.customer_dim.Customer_Dim_Key, Fabrikam_Mart.dbo.time_dim.Time_Dim_Key, Quantity, CyLineitemTotal FROM OrderFormHeader INNER JOIN OrderFormLineItems ON OrderFormHeader.PoNumber = OrderFormLineItems.PoNumber INNER JOIN Fabrikam_Mart.dbo.product_dim ON OrderFormLineItems.ProductId = Fabrikam_Mart.dbo.product_dim.Product_Id_App INNER JOIN Fabrikam_Mart.dbo.customer_dim ON OrderFormHeader.LogonName = Fabrikam_Mart.dbo.customer_dim.Customer_Id_App INNER JOIN Fabrikam_Mart.dbo.time_dim ON OrderFormHeader.OrderDate = Fabrikam_Mart.dbo.time_dim.Full_Date_App
5-6 OLAP キューブの構築
スタースキーマ構造のデータベースへのデータロードが完了したら、あとは OLAP キューブの構築を考えます。SQL Server には、Enterprise Edition、Standard Edition、Personal Edition、Developer Edition などの各エディションがありますが、いずれも分析サービスのコアな機能は標準で同梱されていますので、ここでご紹介する OLAP キューブの構築のステップも分析サービスを追加インストールしていただくだけで、実際に確認することができます。
5-6-1 ディメンションの定義
OLAP キューブの主要な構成要素は、分析者の分析の切り口となるディメンション (次元) と分析対象として扱われるファクトを格納するメジャーになります。ディメンションは、分析サービスの管理ツール (分析マネージャ) からディメンションウィザードを起動し対話的に作成できます。ディメンションウィザードでは、ベースとなるディメンションテーブルの選択 (図 5-7)、ディメンション階層を構成するレベルの選択 (図 5-8)が主要な操作になります。
.jpg)
図 5-7 ディメンションテーブルの選択
.jpg)
図 5-8 ディメンション階層を構成するレベルの選択
5-6-2 OLAP キューブの作成
必要なディメンション定義を終えたらキューブウィザードを起動しキューブを構成していきます。ここでは、ソースとなるファクトテーブルの選択 (図 5-9)、ファクトテーブルからメジャーを格納する列の選択 (図 5-10)、キューブで使用するディメンションの選択 (図 5-11)などが行われます。完成した OLAP キューブのストレージの種類と集計オプションを決めて処理します。以降、キューブの構成を編集する場合は、キューブエディタから行うことができます (図 5-12)。
.jpg)
図 5-9 ソースとなるファクトテーブルの選択
.jpg)
図 5-10 ファクトテーブルからメジャーを格納する列の選択
.jpg)
図 5-11 キューブで使用するディメンションの選択
.jpg)
図 5-12 キューブエディタによるキューブの作成
構築された OLAP キューブの参照は、キューブブラウザから行えます。図 5-13 では、顧客別にどのような商品カテゴリを購入しているかを参照しています。
.jpg)
図 5-13 キューブデータの参照
5-7 Ｃｏｍｍｅｒｃｅ Server の分析・対象化機能
Commerce Server では、サイト分析機能は主要機能のひとつなのですが、サイト分析に必要とされるデータストアの定義はすべて自動で行ってくれます。図 5-14 は、Commerce Server に E コマースサイトを作成した時に作られる、OLAP キューブを示しています。また Commerce Server をインストールすることで、SQL Server DTS が拡張され、データロードを行うためのいくつかのタスクも追加されます。
.jpg)
図 5-14 Commerce Server で自動生成されるディメンション定義と OLAP キューブ
これらは、Commerce Server の分析機能のインフラとして使用されます。また、Web クライアントとして E コマースサイトごとにビジネスデスクと呼ばれるビジネスマネージャ用のアプリケーションも自動生成されます。(図 5-15) ビジネスデスクでは、あらかじめ定義されたピボットテーブルコントロールによる動的なレポートやグラフ、フラットな静的レポートなどを作成する機能が利用できます。また、データ マイニングの機能を使用すると、セグメント化のルールや予測ルールを含むマイニングモデルを定義し、洗練されたデータ分析を自動的に実行させ、商品や顧客の層別を自動的に行ったり、隠れた傾向を見つけたりすることができます。
.jpg)
図 5-15 ビジネスデスクの分析画面
また、Commerce Server では、対象化の機能を使用し、サイトの分析結果を基に商品や顧客のフィルタリングを行って、割引・バーナー広告表示・DM 送信などの実装も簡単に行うことができます。 E コマースサイトに限らず、分析アプリケーションの構築は、ユーザーとの長期にわたるスパイラルワークが必要となるため、Commerce Server のようなアプリケーション開発フレームワークの使用は、開発や保守においても、生産性に対する効果が大きいといえましょう。
.jpg)
Fumio Mizobata: 東京を中心に .NET Framework ならびに .NET Enterprise Servers のトレーニングを開発、実施している。その多忙な業務の中、日々夜の街に吸い込まれ、そこでも大活躍しているようだが、実は大阪在住で2児の父親でもある。できるだけ多くの技術者のお役に立てる技術を伝達することに喜びを感じているので、トレーナーという職業はまさに天職である。