Microsoft Excelを使用したOracle機能の活用

Microsoft Excel を使用した

Oracle 機能の活用

オラクル・ホワイト・ペーパー

Microsoft Excel を使用した Oracle 機能の活用

概要 ... 3

はじめに ... 4

Oracle Business Intelligence Spreadsheet Add-in... 4

主要コンポーネント... 5

OLAP接続... 6

問合せ ... 6

QueryBuilder ... 7

CalcBuilder... 8

Microsoft Excelを使用したOLAP分析 ... 9

サンプル・ワークシートの概要 ... 9

Excelを使用したBI問合せの作成... 10

Excel機能の問合せへの追加... 14

まとめ ... 17

デモンストレーションで使用した製品 ... 18

データベースの実装... 18

Oracleデータベース ... 18

データ・ソース ... 18

論理データ・モデル ... 18

物理モデル ... 19

ETLプロセス... 19

共通用語 ... 21

Microsoft Excel を使用した Oracle 機能の活用

概要

多くの組織では、これまで、データ抽出プログラムを増設し、静的データを Excel へダウンロードしてきました。これにより、技術およびビジネスに関連する分野 で深刻な問題が発生しています。頻繁にダウンロードと操作が行われ、結果とし て、Excel スプレッドシートが拡張してしまいます。様々なソースに基づいた履歴 データの取扱い方については、Excel 分析では究明できません。たとえば、ソース・ データへのリンクがなく、そのソース・データに対して、エンド・ユーザーはリ フレッシュしたデータの可用性を見落している場合、ソース・システムを更新/再 起動するとどうなるでしょうか。どのスプレッドシートが最新バージョンなのか 解らなくなります。この問題に対し、ほとんどのユーザーは複雑なネーミング規 則を作成して解決しようとします。 しかし、スプレッドシート数が増えるにつれ、複雑さもさらに深刻になります。 この場合、ユーザーは、リンクしたスプレッドシートを作成しがちですが、その シートを使用して計算処理を手動追跡するのはきわめて困難です。また、土壇場 の修正処理も難しくなります。一般に、多くの Excel ユーザーは、データを分析 したり有益なビジネス情報を作成するより、データ・セットの作成に多くの時間 を費やします。ほとんどの Excel アプリケーションは、静的データに基づいてい ますが、このデータは、手動で更新する、またはエラーが発生しやすいマクロを 介して更新する必要があります。このデータは、何百もの複雑な計算式で構成さ れているため、理解したりデバッグするのが困難です。 必要なことは、ビジネス・データベースを Excel 内で理知的に直接表示し、Excel でデータを変更・改善する機能です。全体的な利点は、Excel ユーザーは、もはや 孤立して仕事するのではなく、常に最新データにアクセスし、ビジネス・ルール や計算の共通セットを使用して仕事し、他の Excel および Microsoft Office ユーザー と結果を共有できることです。この機能は、組織の他の部署、特に IT 部では、非 常に役立ちます。IT 部では、スプレッドシートおよび個人データ・マートを率先 して使用しません。複雑に繋がったワークシートが一人の主要ユーザーにより管 理される非文書化個人アプリケーションの増加は、迅速かつ容易に解消できます。 このホワイト・ペーパーでは、BI Spreadsheet Add-in の主な構成要素および強力な データ駆動型レポートの作成方法を再確認します。

はじめに

このレポートは、2 つのセクションで構成されています。最初のセクションでは、 Oracle BI Spreadsheet Add-in のアーキテクチャおよび主要コンポーネントについて 説明します。このセクションでは、Oracle BI Discoverer、Oracle BI Beans および Oracle Application Server Reports など Oracle の OLAP ツールや製品の主要コンポー ネントである OLAP Query や Calculation Builder ウィザードについて重点的に説明 します。

第 2 のセクションでは、Excel ユーザーが Excel から直接 OLAP 問合せを作成、管 理し、標準的な Excel 機能を組み込む方法を説明します。

Oracle Business Intelligence Spreadsheet Add-in

Oracle BI Spreadsheet Add-in により、エンド・ユーザーは Excel 内から直接 Oracle OLAP データを表示し、ナビゲートできます。ユーザーは、計算式やグラフの作 成など、Oracle OLAP を通常の Excel データとして処理でき、Oracle OLAP の強力 な分析機能と標準的な Excel 機能を組み合わせることができます。ここでは、Excel は、Oracle OLAP 使用可能データベースへ直接接続するインテリジェントなフロ ントエンドとして機能します。

この BI Spreadsheet Add-in は、他の Excel Add-in と同様にインストールされます。 ただし、Oracle サーバーには、実際のアプリケーション機能および柔軟性が内蔵 されているので、ローカル・メンテナンスは不要です。また、Excel が正しく使用 されていれば、Add-in は、Excel を停止することはないので、複数の Add-in をイ ンストールしてもお互いに干渉しません。Add-in には、通常、主要エントリとそ の関連データが右クリックのコンテキスト・メニュで提供されます。他にも複数 のツールバーがありますが、不要な時には解除できます。

Excel に対して Oracle OLAP を使用すると、ユーザーは、あらゆるデータベース処 理機能を利用して OLAP のディメンションやメジャーに対して直接問合せを作成、 管理、実行できます。ユーザーは、その十分に裏づけされた構造的制限により、 Excel 機能より高度な大規模データベースのウェアハウス・インスタンスについて 問合せできます。問合せを一度組み込むと、データは、ディメンション・ページ ング用付加制御機能により、通常のスプレッドシートとして Excel 内に提示され ます。OLAP 問合せは、正常な Excel スプレッドシートと同様のルック・アンド・ フィールで動作するので、チャートの追加など、通常の Excel ビジネス・ツール を使用して問合せを自由に改善、拡張できます。下記図 1 参照。

図 1: OLAP データおよび Excel チャートを使用した BI Spreadsheet Add-in レポート

複合レポートを作成しておくと、新しいデータがウェアハウスにロードされるた びに問合せを再構築する必要がなくなります。この問合せ条件を使用することに より、スプレッドシートは事実上動的になり、問合せを書き直すことなく最新の データで継続的にリフレッシュできます。また、OracleOLAP の Query Builder を 使用すると、ビジネス用語や定義に基づいた問合せを構築できます。計算および 問合せは共に、メジャーとその関連ディメンションに基づいています。ウィザー ドベースのインタフェースを使用すると、値リストから選択するか、または例外、 上位/下位、階層ベースの問合せなど選択内容を詳細に作成することにより、簡単 に Oracle OLAP からデータを選択できます。さらに、同様のウィザードを使用し て、Oracle OLAP ベースの計算を作成できます。

Excel に組込まれている問合せウィザードは、Oracle Business Intelligence Beans Query Wizard に基づいています。このウィザードは、すべての Oracle OLAP 製品 で使用されているので、他の Oracle Business Intelligence Beans ベースのアプリケー ションと一貫性のあるインタフェースを確保できます。

主要コンポーネント

Oracle BI Spreadsheet Add-in のアーキテクチャ図を次に示します。後述のセクショ ンでは、各コンポーネントについて説明します。

図 2: BI Spreadsheet Add-in の概要

OLAP 接続

Excel 内から Oracle OLAP インスタンスへの接続は、Oracle Java OLAP API を使用 して作成されます。接続には、Thin JDBC ドライバを使用するので、SQLNet 等の クライアント・サイドのインストールは不要です。必要な JDBC ファイルはすべ て、Add-in インストールの一部としてバンドルされます。接続すると、OLAP Metadata Services レイヤーで Excel セッションを登録します。

ウェアハウスの設計およびエンド・ユーザーからの問合せは複雑なので、相互に 関連する様々な参照表やファクト表は、一番簡単な OLAP 問合せに基づいて使用 される可能性があります。また、ユーザーの簡単な質問に回答する最適化 SQL は、 コード行が多くなる場合があります。Oracle OLAP API により、クライアント・ア プリケーションはメタデータを使用して複合・多次元またはリレーショナルなス タイルの問合せや計算を指定できます。メタデータを直接操作することにより、 複雑な問合せも非常に簡単になります。

問合せ

ワークシートのバックエンドには問合せがあります。問合せは、メジャー（販売 およびコストなど）、ディメンション（製品、地域、チャンネル、プロモーショ ンおよび時間など）、各ディメンションごとの選択（最近半年間での選択など） およびディメンションのレイアウト（地域（行）、時間（列）、製品、チャンネ ルおよびプロモーションのページング）など、Excel で表示する内容を正確に指定 します。

問合せは、Oracle OLAP API を使用してデータを取得・操作し、Oracle データベー スに対して高度な分析機能を実現します。Oracle OLAP API は、非常によくチュー ニングされた SQL を生成して、問合せからの要求を解決します。

QueryBuilder

QueryBuilder は、簡単なユーザー・インタフェースを提供して洗練された問合せ を定義します。このユーザー・インタフェースは、特許を得ており、その使い勝 手については何度もテストされています。QueryBuilder は、非常に強力なツール なので、基礎的な問合せ言語を知らなくても次の問合せプロパティを指定できま す。 • メジャーとディメンション − 階層リストを表示するリスト・ツールを使 用して選択します。 • ディメンションの選択 − 例外、ランキング、上位/下位、階層選択（子お よび祖先）、テキスト一致などを定義して選択します。たとえば、「各 市で販売される製品上位 5 品」 • プレゼンテーション内のディメンション・レイアウトなど。 また、QueryBuilder を使用すると、定義した選択をお気に入りとして保存、取得 できるので、選択肢を簡単に再利用できます。ユーザーは、通常、複数のレポー トまたはプレゼンテーションで、一般的な選択肢または問合せ（「製品上位 5」 など）を使用する傾向があります。 QueryBuilder の主な長所は、エンド・ユーザーが問合せを定義するために問合せ 言語を理解する必要がないということです。エンド・ユーザーが必要に応じて変 更できるビジネス用語で問合せ定義を提示することにより、強力な問合せを簡単 に作成できます。たとえば、「販売実績に基づいた製品上位 5 品を表示する」を デフォルト・問合せ定義として指定できます。エンド・ユーザーは、用語の「上 位」を「下位」に変更することにより、販売実績に基づいた製品下位 5 品を表示 できます。

図 3: BI Spreadsheet Add-in の問合せウィザード

CalcBuilder

CalcBuilder ウィザードにより、ユーザー・インタフェースは導出計算を作成でき ます。QueryBuilder と同様に、エンド・ユーザーはテンプレートを使用して新し い検索基準を定義するので、基礎的な SQL を理解する必要がありません。たとえ ば、データベース管理者が特定製品ラインに関する Sales Growth（売上げ増加）を 定義しない場合でも、エンド・ユーザーは、それを知りたい場合があります。 CalcBuilder を使えば、ユーザーはこの計算を定義できます。計算のテンプレート は、タイプ別に分類されます。たとえば、エンド・ユーザーは適切な時系列を選 択し、新しいメジャーを定義します。

図 4: BI Spreadsheet Add-in Calculation Builder Wizard

Microsoft Excel を使用した OLAP 分析

次に、BI Spreadsheet Add-in の作成・使用方法の例を示します。この例から、Oracle OLAP データと一緒に通常の Excel 機能を使用する方法が理解できます。

サンプル・ワークシートの概要

BI Spreadsheet Add-in をインストールすると、Excel に新しいメニューが追加され ます。

このメニューは、様々な Oracle OLAP オプションのアクセスを制御します。この 最初の手順では、「新規クエリー」オプションを使用して、OLAP 問合せビルダー を起動します。これにより、接続ダイアログが起動され、ユーザーは、OLAP 接 続の詳細を定義できます。

図 5: BI Spreadsheet Add-in Excel メニュー 図 6: BI Spreadsheet Add-in 接続ダイアログ

ユーザーが一度認証されると、OLAP Query Builder が起動し、必要なメジャーお よびディメンションを選択できます。

Excel を使用した BI 問合せの作成

次のスクリーン・ショットでは、2001 年 Sales Revenue %Growth Based on Prior Year （前年販売収益増加率）に基づいたすべてのチャンネル、地域およびプロモーショ ンに対して、サブ項目レベルの製品上位 10 位を示すレポートの作成方法を示して います。手順 1 では、必要なメジャーを選択します。このレポートでは、Sales Revenue and Margin %（販売収益とマージン率）が選択されています。デフォルト では、関連したディメンションが自動的に選択されます。これにより、ユーザー は基礎的なデータベース・スキーマや複数の表を結合する方法を理解する必要が ありません。

図 7: BI Spreadsheet Add-in Query Builder − メジャー選択

次の手順では、レポートのレイアウトを決定します。次の図に示すように、製品 は行枠に表示され、メジャーと時間は列の位置に表示されます。地域、プロモー ションおよびチャネルは、ページアイテムに表示されます。

図 8: BI Spreadsheet Add-in Query Builder − レポート・レイアウト画面

ユーザーは各ディメンションごとに選択肢を決定できます。選択プロセスは、次 に示すように、マウスで必要なディメンション値を手動で選択できます。

図 9: BI Spreadsheet Add-in Query Builder − ディメンション・メンバーの手動選択

手動選択は、ディメンションが製品ディメンションなど多くのメンバーを含んで いる場合には、少し複雑です。通常、ユーザーは LOV をフィルタにかけたいと考 えます。OLAP Query Builder には、様々なテンプレートが提供されているので、 ユーザーは複合的な問合せを迅速かつ容易に構築できます。

次のスクリーン・ショットは、2001 年 Sales Revenue %Growth Based on Prior year （前年販売収益増加率）に基づいたすべてのチャンネル、領域およびプロモーショ

ンに対して、サブ項目レベルの上位 10 位を示す選択肢の作成方法を示しています。 各テンプレートには、ハイパーリンクが組み込まれているので、ユーザーはデフォ ルト・チャンネルを変更したり、予備のダイアログ・ボックスで問合せにパラメー タを追加表示できます。

図 10: BI Spreadsheet Add-in Query Builder − ディメンション・メンバーの条件付き選択 ディメンションの選択がすべて完了すると、ユーザーは問合せを実行し、データ をワークシート内に取得できます。下記のレポートでは、製品カテゴリトップ 10 が表示されています。製品ディメンション自体には多数のメンバーが組み込まれ ていますが、上位 10 位の製品カテゴリーのみが問合せによって返されます。デー タベースは、あらゆる分析とフィルタリングを行って、Excel ワークシートの使用 と関連する拡張性の問題を克服します。

Excel 機能の問合せへの追加

問合せを一度定義すると、チャートなどの標準的な Excel 機能を追加して、レポー ト・イメージを改善することによりレポート機能を拡張できます。BI Spreadsheet Add-in には、強力な Calculation Builder ウィザードが提供されています。このドキュ メントの上記セクションを参照してください。このような計算にも、通常の Excel 機能や計算機能を使用すると性能が向上します。現在のワークシートへ小計を挿 入するには、Excel の機能を使用する必要があります。このデモでは、合計値は Sales Revenue（販売収益）欄に表示され、平均値はマージン欄に表示されます。 図 12: Excel SUM 機能ウィザード 図 13: Excel AVERAGE 機能ウィザード

図 14: 更新した BI Spreadsheet Add-in ワークシート

グラフは、比較、パターン、データ・セット内のトレンドを簡単に表現できる非 常にビジュアルで優れた手段です。たとえば、グラフを使うと、ユーザーは、迅 速かつ容易に販売収益の上昇/下落を判断できます。

図 15: Excel チャートを BI Spreadsheet Add-In ワークシートへ追加する 手順 3 では、別の統合レベルを追加します。Excel ツールボックスのラジオ・ボタ ンを使用すると、Excel マクロへリンクするページング制御機能を拡張できます。 この機能により、ユーザーは迅速、容易かつ視覚的にページ・ディメンションを 変更できます。この機能は、レポート内のチャネルや地域の選択を制御するため に使用できます。オリジナルのページング制御は、行を非表示にする、または行 の高さを「0」に設定することにより簡単に非表示にできます。上記操作の結果は 両方共同じです。ラジオ・ボタンを使用するには、Excel ツールボックスでラジオ・ ボタンをレポートの脇に表示します。ボタンを正しく一列に並べると、マクロが 各ボタンに割当てられ、ページ・ディメンション値の変更が可能です。

Private Sub SetChannelRange(SetText As String) Range ("Channel_Range").Select

Range ("Channel_Range")= SetText End Sub

Private Sub OptAll_Click()

SetChannelRange ("Channel total") End Sub

図 16: ラジオ・ボタンを BI Spreadsheet Add-In ワークシートへ追加する

まとめ

一般的に、多くの Excel ユーザーは、データ分析時間や有益なビジネス情報の作 成時間より、データ・セットの作成に多くの時間を費やしています。Oracle BI Spreadsheet Add-in を使用することにより、Excel ユーザーは、統一ユーザー・イン タフェースを持つ Oracle BI Discoverer のゼロ・ラーニング・カーブでも使用され る一貫性のあるウィザードの集合を持つ、強力な Oracle OLAP 環境へ直接アクセ スできます。これで、Excel ユーザーは次の機能が使用できるようになります。 • テラバイトサイズのデータ・ウェアハウスに直接アクセスする • ビジネス用語および定義を使用して OLAP 問合せを作成する • 最新のデータを自動的にフェッチする問合せを作成する • 問合せを複数の OLAP キューブから単一のスプレッドシートに作成する • BI Spreadsheet Add-In Calculation ウィザードを利用して OLAP 環境を拡張

する

デモンストレーションで使用した製品

このデモンストレーションは、Oracle のビジネス･インテリジェンス OLAP ソ リューション、計算機能およびデータ・アクセス方法を明らかにします。 当然のことながら、他のツールにも、Oracle ツールと同じ機能があります。OLAP オプションの OLAP API はパブリックなので、サード・パーティもデータベース 上でディメンショナル・アプリケーションを開発できます。SQL インタフェース を使用すると、他の SQL ベース・ツールが Oracle10g のマルチディメンション・ データ型にアクセスできます。上記 SQL ベース・アプリケーションは、マルチディ メンション・データ型を認識できないか、またはマルチディメンション・データ 型や多次元エンジンと相互作用できます。 次の表に、デモンストレーションで使用する製品を列挙します。 ロール 製品 説明

データベース Oracle Database Enterprise Edition with OLAP Option

OLAP オプションにより、ビジネス言 語レイヤーを作成し、データベース記 憶域オブジェクトをビジネス用語に変 換できるので、ユーザーは複雑なビジ ネス問合せを作成できます。 スプレッドシート Oracle Business Intelligence

Add-in for Microsoft Excel

Microsoft Excel を Oracle Database との OLAP インタフェースとして使用でき ます。

データベースの実装

Oracle データベース

Oracle10g Enterprise Edition Database は、業務系データ・ソースおよびデータ・ウェ アハウスの実装両方に使用されます。Oracle10g Database の OLAP オプションには、 高度な分析機能があります。

データ・ソース

データ・ソースは、Oracle10g の共通スキーマで、次のメジャーを収益やコストな どのデータ・ウェアハウスへ供給する、出荷および総勘定元帳システムに基づい ています。

論理データ・モデル

詳細な分析をサポートし、使いやすくするために、ディメンショナル・モデリン グが実装されています。このタイプのモデルは、5 個のディメンション、時間、 チャンネル、製品、プロモーションおよび地域で構成されます。各ディメンショ ンは 7 つのレベルに分類され、複数の階層に分かれています。次にメジャー・リ ストを示します。

Sales Revenue（販売収益） コスト マージン Sales Revenue（販売収益） コスト マージン 6 か月移動平均 6 か月移動平均 6 か月移動平均 12 か月移動 12 か月移動 12 か月移動

平均 平均 平均

Prior Period % Growth （前期増加率）

Prior Period % Growth （前期増加率）

Prior Period % Growth （前期増加率） Prior Year % Growth

（前期増加率）

Prior Year % Growth （前期増加率）

Prior Year % Growth （前期増加率） 季節的予測 最適な予測 最適な予測 指数関数的予測 線形予測 非線形予測 トレンド予測 最適な予測

物理モデル

ディメンショナル・モデルを利用した共通スキーマの実装を選択したため、また 重要な分析要求があったため、物理モデルは、Oracle10g Database の OLAP オプショ ンで管理する多次元データ型を実装しました。 メジャーはすべて同じサブジェクト領域（共通スキーマの販売データ）に関連し、 論理ディメンションを共有していたので、1 つの分析ワークスペースにデータ・ モデル全体を組み込んでいます。分析ワークスペースは、関連したマルチディメ ンション・データ型データベースに内蔵されたコンテナです。 アナリティック・ワークスペースは、データベーススタンダード・フォームに従っ て構築しました。このアナリティック・ワークスペースは、アナリティック・ワー クスペースの物理的設計になっており、Oracle 管理およびエンド・ユーザー・ツー ルを使用して理解されるデータ構造とメタデータを含んでいます。Oracle 管理 ツールは、データベーススタンダード・フォームに従ってアナリティック・ワー クスペースを構築します。

ETL プロセス

Oracle BI Warehouse Builder は、アナリティック・ワークスペースを構築するため に使用しました。Oracle Warehouse Builder を使用することにより、ソース業務系

ケージ上に階層化されている）を、アナリティック・ワークスペースの構築・管 理に使用します。

多次元データ型は、多次元データ型 SQL インタフェースを介して SQL ベース・ アプリケーションにアクセスできるので、リレーショナル表のデータをレプリ ケートする必要がありません。

共通用語

OLAP − On Line Analytical Processing（OLTP と比較してください。）

ROLAP − Relational OLAP: ディメンショナル・ビューをリレーショナル・データ 上へ組み込みます。

MOLAP − Multidimensional OLAP: Analytic Workspace に多次元形式で格納される データ。

Analytic Workspace（AW）− Oracle LOB に格納された多次元スキーマで、ディメ ンション、変数、階層、プログラムなどを含みます。 ディメンション − データを分類するための構造（時間、地域、製品など。） 階層 − 集計操作およびドリルダウンに対するディメンションの定義レベル。 メジャー − 多次元データをストア（または計算）します。ファクト列に相当しま す。 式 − AW で計算されるメジャー。 キューブ − 同じディメンションを持ったメジャーの論理的構成。 メタデータ（CWM）− データや他の構造体を説明するデータ。OLAP カタログは、 キューブ、メジャー、ディメンションおよび属性などの多次元用語でデータを説 明するメタデータです。

DML − Data Manipulation Language: Analytic Workspaces 用ディメンション認識プ ログラミング言語。

標準フォーム − AW 内のオブジェクト関係とロールを識別する階層ディメンショ ン、レベル・ディメンション、親子関係などの特定の構造。OLAP ツールのアク セス時 AW に必要です。

使用可能性 − AW オブジェクトが OLAP API に認識されるプロセス。

OLAPI − BI Beans が AW と通信する時に使用する OLAP API。OLAPI は、SQL を 生成します。

AW ビュー − SQL が AW 内の多次元オブジェクトをアクセスできるビュー。 マテリアライズド・ビュー − 集計したファクト・データを事前に計算したリレー ショナル表。ROLAP パフォーマンスに使用します。

Microsoft Excel を使用した Oracle 機能の活用 2004 年 12 月 著書: Keith Laker 寄稿者: Aneel Shenker Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065 U.S.A. 海外からのお問合せ窓口: 電話: +1.650.506.7000 ファックス: +1.650.506.7200 www.oracle.com

Copyright © 2004, Oracle. All rights reserved.

この文書はあくまで参考資料であり、掲載されている情報は予告なしに変更されることがあります。 オラクル社は、本ドキュメントの無謬性を保証しません。また、本ドキュメントは、法律で明示的または暗黙的に記載 されているかどうかに関係なく、商品性または特定の目的に対する適合性に関する暗黙の保証や条件を含む一切の保証 または条件に制約されません。オラクル社は、本書の内容に関していかなる保証もいたしません。また、本書により、 契約上の直接的および間接的義務も発生しません。本書は、事前の書面による承諾を得ることなく、電子的または物理 的に、いかなる形式や方法によっても再生または伝送することはできません。