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

Microsoft Excel を使用した

Oracle 機能の活用

Oracle ホワイト・ペーパー

2004 年 12 月

Microsoft Excel を使用した Oracle 機能

の活用

概要 ... 3

はじめに ... 4

Oracle Spreadsheet Add-In ... 4

コア・コンポーネント... 5

OLAP 接続... 6

問合せ ... 6

Query Builder ... 7

Calculation Builder... 8

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

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

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

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

結論 ... 17

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

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

Oracle データベース ... 19

データソース ... 19

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

物理モデル ... 20

ETL プロセス... 20

一般用語 ... 21

Microsoft Excel を使用した Oracle 機能

の活用

概要

これまで、多くの組織では、データ抽出プログラムを増設して、静的データを Microsoft Excel にダウンロードしてきました。そのため、技術およびビジネス面 で深刻な問題が発生しています。頻繁にダウンロードや操作が行われるため、 Microsoft Excel スプレッドシートが際限なく拡張してしまうのです。Microsoft Excel 分析では、さまざまなソースに基づいた履歴データの処理方法を究明するこ とができません。たとえば、ソース・データへのリンクがなく、エンド・ユーザー がリフレッシュされたデータの可用性に気付かない場合、ソース・システムを更 新/再起動するとどうなるでしょうか。どのスプレッドシートが最新バージョンな のか分からなくなってしまいます。この問題に対し、ほとんどのユーザーは、複 雑なネーミング規則を作成して対処しようとします。 しかし、スプレッドシート数が増えるにつれ、複雑さはさらに増します。ユーザー は、リンクしたシートを作成しがちですが、リンクしたスプレッドシートを使用 して計算を手動で追跡することは極めて困難です。また、土壇場での修正の管理 も難しくなります。一般に、Microsoft Excel ユーザーの多くは、データを分析し て有益なビジネス情報を作成するよりも、データセットの作成に多くの時間を費 やします。Microsoft Excel アプリケーションのほとんどは、静的データに基づい ていますが、これは手動更新するか、エラーの発生しやすいマクロを介して更新 する必要があります。たいていのデータは何百もの複雑な計算式で構成されてい るため、理解やデバッグが困難です。

必要なのは、ビジネス・データを Microsoft Excel 内で理論的に直接表示し、Microsoft

Excel を使用してデータを変更および改善する機能です。この場合の総合的な利点 は、Microsoft Excel ユーザーが、孤立して作業するのではなく、最新データに常 にアクセスでき、ビジネス・ルールや計算の共通セットを使用して作業し、結果 を他の Microsoft Excel および Microsoft Office ユーザーと共有できることです。こ れは、組織の他の部門、特に IT 部門において非常に役立ちます。IT 部門は、スプ レッドシートや個人データ・マートの使用の推進には極めて慎重です。1 人の主 要ユーザーの管理する、複雑に繋がったワークシートを含むドキュメント化され

はじめに

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

2 番目のセクションでは、Microsoft Excel ユーザーが Microsoft Excel から直接 OLAP

問合せを作成および管理し、標準的な Microsoft Excel の機能を組み込む方法につ いて説明します。

Oracle Spreadsheet Add-In

Oracle Spreadsheet Add-In により、エンド・ユーザーは Microsoft Excel 内から直接 Oracle OLAP データを表示し、ナビゲートできます。Oracle OLAP データを通常の

Microsoft Excel データとして処理して、計算式やグラフの作成などが行えるため、

Oracle OLAP の強力な分析機能と標準的な Microsoft Excel の機能を組み合わせる ことができます。ここでは、Microsoft Excel は、Oracle OLAP 対応データベースに 直接接続するインテリジェントなフロントエンドとして機能します。

この Oracle Spreadsheet Add-In は、他の Microsoft Excel アドインと同様にインス トールされますが、Oracle サーバーには実際のアプリケーション機能と柔軟性が 含まれているため、ローカル・メンテナンスは不要です。Microsoft Excel が正常 に使用されている場合、このアドインは Microsoft Excel を停止することはないた め、複数のアドインをインストールしても相互干渉しません。通常、アドインに は、メイン・メニューがあり、右クリックするとそれに関連するコンテキスト・ メニューが表示されます。多くのアドインは、他にも 1 つ以上のツールバーを持 ちますが、不要な場合は無効にできます。

Oracle OLAP を Microsoft Excel に使用するおもな利点は、あらゆるデータベース 処理機能を使用して、OLAP ディメンションやメジャーに対して直接問合せを作 成、管理、実行できる点です。このため、十分に裏付けられた構造上の制限によ り、通常は Microsoft Excel では処理できない非常に大規模のデータベース・ウェ アハウス・インスタンスの問合せが可能になります。一度問合せを組み込むと、 データはディメンション・ページングの追加制御機能により、Microsoft Excel 内 に通常のスプレッドシートとして表示されます。OLAP 問合せは、通常の Microsoft Excel スプレッドシートとまったく同様のルック・アンド・フィールで動作するた め、グラフの追加など、通常の Microsoft Excel ビジネス・ツールを使用して、問 合せを自由に改善および拡張できます。図 1 に例を示します。

図1：OLAPデータとMicrosoft Excelグラフを使用した Oracle Spreadsheet Add-Inレポート

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

この組込みの Query Wizard は、Oracle BI Beans Query Wizard に基づいています。 このウィザードは、すべての Oracle OLAP 製品で使用されているため、他の Oracle BI Beans ベースのアプリケーションと一貫性のあるインタフェースを実現します。

コア・コンポーネント

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

図2：Oracle Spreadsheet Add-Inの概要

OLAP 接続

Microsoft Excel 内から Oracle OLAP インスタンスへの接続は、Oracle Java OLAP API を使用して作成されます。接続には、シン JDBC ドライバを使用するため、 SQLNet のクライアント側のインストールは不要です。必要な JDBC ファイルはす べて、アドイン・インストールにバンドルされます。接続すると、OLAP メタデー タ・サービス・レイヤーで Microsoft Excel セッションを登録します。 ウェアハウスの設計やエンド・ユーザーからの問合せは複雑なため、非常に単純 な OLAP 問合せに回答するために相関参照表やファクト表を使用する場合もよく あります。また、ユーザーの単純な問合せに回答するための最適化された SQL の コード行が増加する場合があります。Oracle OLAP API により、クライアント・ア プリケーションは、メタデータを使用して、複雑な多次元またはリレーショナル 形式の問合せや計算を指定できます。メタデータを直接操作することにより、複 雑な問合せが大幅に簡素化されます。 問合せ ワークシートの裏側には問合せがあります。問合せは、メジャー（売上やコスト など）、ディメンション（製品、地理区分、チャネル、販売促進、時間など）、 各ディメンションの選択（過去 6 ヶ月間など）、およびディメンションのレイア ウト（行の地理区分、列の時間、および製品、チャネル、販売促進のページング）

など、Microsoft Excel での表示内容を正確に指定します。

問合せは、Oracle OLAP API を使用してデータを取得および操作し、Oracle データ ベースに高度な分析機能を提供します。Oracle OLAP API は、高度に調整された SQL を生成して、問合せからのリクエストを解決します。 Query Builder Query Builder は、簡単なユーザー・インタフェースを提供して、高度な問合せを 定義します。このユーザー・インタフェースは特許を取得しており、使いやすさ についての十分なテストが行われています。Query Builder は非常に強力なツール であり、基本となっている問合せ言語を知らなくても、次の問合せプロパティを 指定できます。 • メジャーとディメンション - 階層リストを表示するリスト・ツールを使 用して選択します。 • ディメンションの選択 - 例外、ランキング、上位/下位選択、階層選択（子 および祖先）、テキスト一致などを定義して指定します。"Top 5 Products sold in each City"などです。

• プレゼンテーション内のディメンションのレイアウト。 また、Query Builder では、定義した選択をお気に入りとして保存および取得でき るため、選択を簡単に再利用できます。通常、複数のレポートやプレゼンテーショ ンで、一般的な選択や問合せ（"Top 5 Products"など）が使用されます。 Query Builder のおもな長所は、エンド・ユーザーが問合せを定義するために問合 せ言語を理解する必要がないということです。エンド・ユーザーが必要に応じて 変更できるビジネス用語で問合せ定義を表示することにより、強力な問合せを簡 単に作成できます。たとえば、"Show the Top 5 Products based on Sales"を、デフォ ルトの問合せ定義として指定できます。エンド・ユーザーは、この定義の"Top" を"Bottom"に変更すれば、売上ベースの下位 5 位までの製品を表示できます。

図3：Oracle Spreadsheet Add-In Query Wizard Calculation Builder Calculation Builder ウィザードは、新たな計算式を作成するユーザー・インタフェー スを提供します。Query Builder と同様に、エンド・ユーザーはテンプレートを使 用して新しいメトリックを定義するため、基本となっている SQL を理解する必要 がありません。たとえば、データベース管理者が売上増加を定義していない場合 でも、エンド・ユーザーが特定製品ラインの前年比売上増加を知りたい場合があ ります。Calculation Builder を使用すれば、この計算を定義できます。計算テンプ レートはタイプ別にまとめられています。次の例では、エンド・ユーザーは適切 な時系列テンプレートを選択して、新しいメジャーを定義します。

図4：Oracle Spreadsheet Add-In Calculation Builderウィザード

Microsoft Excel を使用した OLAP 分析

次に、Oracle Spreadsheet Add-In の作成および使用方法の例を示します。この例に より、Oracle OLAP データと一緒に通常の Microsoft Excel の機能を使用する方法 を説明します。

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

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

このメニューは、Oracle OLAP の各種 オプションへのアクセスを制御します。 最初の手順では、「New Query」オプ ションを選択して、Oracle OLAP Query Builder を起動します。これにより、接 続ダイアログが起動して、OLAP 接続 の詳細を定義できるようになります。

図6：Oracle Spreadsheet Add-Inの

接続ダイアログ

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

図5：Oracle Spreadsheet Add-Inの Microsoft Excelメニュー

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

次のスクリーンショットは、2001 年の前年比売上収益増加率に基づいたすべての チャネル、地域および販売促進について、サブカテゴリ・レベルで上位 10 位まで の製品を表示するレポートの作成方法を示したものです。Query Wizard では、ユー ザーを支援する一連の手順が提供されています。手順 1 では、必要なメジャーを 選択します。このレポートでは、売上収益と利幅率が選択されています。デフォ ルトでは、関連するディメンションが自動的に選択されます。そのため、ユーザー は基本となるデータベース・スキーマや、複数表を結合する方法を理解する必要 がありません。

図7：Oracle Spreadsheet Add-In Query Builder - メジャーの選択

次の手順では、レポートのレイアウトを決定します。次の図に示すように、製品 は行項目に表示され、メジャーと時間は列の位置に表示されます。地理区分、販 売促進、チャネルは、ページ項目に表示されます。

ここで、各ディメンションの選択を決定できます。選択の際は、次に示すように、 マウスで簡単に必要なディメンション値を手動選択できます。

図9：Oracle Spreadsheet Add-In Query Builder - ディメンション・メンバーの手動選択

手動選択は、ディメンションに製品ディメンションなどの多数のメンバーがある 場合にはあまり簡単とは言えません。通常は、値のリストをフィルタリングする ことが多いでしょう。Oracle OLAP Query Builder には一通りのテンプレートが準 備されているため、複雑な問合せを迅速かつ簡単に構築できます。 次のスクリーンショットは、2001 年の前年比売上収益増加率に基づいたすべての チャネル、地域および販売促進について、サブカテゴリ・レベルで上位 10 位まで の製品を表示するレポートの作成方法を示したものです。 各テンプレートにはハイパーリンクがあり、これによりデフォルト値や、問合せ の追加パラメータを表示する追加ダイアログを変更できます。

図10：Oracle Spreadsheet Add-In Query Builder - ディメンション・メンバーの 条件付き選択 ディメンションの選択が完了すると、問合せを実行して、データをワークシート に取得できます。次のレポートでは、製品カテゴリの上位 10 位までが表示されて います。製品ディメンションには多数のメンバーがありますが、問合せによって 返されるのは、上位 10 位までの製品カテゴリのみです。データベースは、あらゆ る分析とフィルタリングを行って、Microsoft Excel ワークシートの使用に伴う拡 張性の問題を解決します。

Microsoft Excel 機能の問合せへの追加

一度問合せを定義すると、グラフなどの標準的な Microsoft Excel の機能を追加し て、レポートの視覚化を向上してレポートを拡張できます。Oracle Spreadsheet Add-In には、強力な Calculation Builder ウィザードが用意されています。これにつ いては、前のセクションを参照してください。このような計算についても、通常 の Microsoft Excel 関数と計算機能を使用して、機能を向上できます。現在のワー クシートに小計を挿入するには、Microsoft Excel 関数ウィザードを使用するだけ です。このデモでは、合計値は Sales Revenue 列に表示され、平均値は Margin に 表示されます。

図12：Microsoft Excel SUM関数ウィザード

図14：更新されたOracle Spreadsheet Add-Inワークシート

グラフは、比較、パターン、およびデータセット内のトレンドを簡単に表示でき る、非常に視覚的で魅力的な手段です。たとえば、グラフを使用して、簡単かつ 迅速に売上収益の増減を経時的に表示できます。

手順 2 では、Microsoft Excel のグラフ・ウィザードを使用して、Microsoft Excel グ ラフを追加します。

図15：Microsoft ExcelグラフのOracle Spreadsheet Add-Inワークシートへの追加

手順 3 では、別のレベルの統合を追加します。ページング・コントロール機能を 拡張して、Microsoft Excel ツールボックスのラジオ・ボタンで Microsoft Excel マク ロにリンクできます。これにより、ページ・ディメンションを迅速かつ簡単に、 視覚的に変更できます。この機能を使用して、レポート内のチャネルや地理区分 選択を制御できます。元のページング・コントロール機能は、行を非表示にした り、行の高さをゼロに設定したりして、簡単に非表示にできます。いずれの操作 をしても、同様の結果が得られます。ラジオ・ボタンを使用するためには、Microsoft Excel ツールボックスを使用して、ラジオ・ボタンをレポートの横に配置します。 ラジオ・ボタンを正しく並べると、マクロが各ボタンに割り当てられ、ページ・ ディメンション値を変更できるようになります。

Private Sub SetChannelRange(SetText As String)

Range("Channel_Range").Select Range("Channel_Range") = SetText

End Sub

図16：ラジオ・ボタンのOracle Spreadsheet Add-Inワークシートへの追加

結論

一般に、Microsoft Excel ユーザーの多くは、データを分析して有益なビジネス情 報を作成するよりも、データセットの作成に多くの時間を費やします。Oracle Spreadsheet Add-in を使用することで、Microsoft Excel ユーザーは、Oracle Discoverer でも使用されている一貫性のある各種ウィザードを使用し、強力な Oracle OLAP 環境に直接アクセスできます。そのため、統合ユーザー・インタフェースが使用 でき、習熟期間は必要ありません。これで、Microsoft Excel ユーザーは、次を実 現できます。 • テラバイト・サイズのデータ・ウェアハウスへの直接アクセス • ビジネス用語と定義を使用した OLAP 問合せの作成 • 最新データを自動取得する問合せの作成

これにより、もっとも一般的なデスクトップの分析インタフェース内で、基本的 な Microsoft Excel の機能を活用して OLAP のメリットをすべて享受できます。

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

このデモンストレーションは、オラクルのビジネス・インテリジェンス OLAP ソ リューションとその計算機能、およびデータ・アクセス方法について重点的に説 明しています。 当然、他のツールも Oracle ツールと同じ機能を持ちます。OLAP オプションの OLAP API はすでに公開されており、サード・パーティ製品を使用して、データ ベース上でディメンション対応アプリケーションを開発できます。SQL インタ フェースを使用すると、他の SQL ベースのツールで Oracle 10g の多次元データ型 にアクセスできます。これら SQL ベースのアプリケーションには、多次元データ 型を認識できないものや、多次元データ型および多次元エンジンとの交信が可能 なものがあります。 次の表に、デモンストレーションで使用した製品を示します。 ロール 製品 説明

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

OLAP オプションにより、ビジネス言語レイ ヤーを作成して、データベース・ストレー ジ・オブジェクトをビジネス用語に変換でき るため、複雑なビジネス問合せを簡単に作成 できます。 スプレッド シート

Oracle Business Intelligence Spreadsheet Add-In for Microsoft Excel

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

データベースの実装

Oracle データベース

Oracle Database 10g Enterprise Edition は、運用データソースおよびデータ・ウェア ハウスの実装に使用されます。Oracle Database 10g Enterprise Edition の OLAP オプ ションは、高度な分析機能に使用されます。

データソース

データソースは Oracle 10g Common Schema です。これは、次のメジャーを収益や コストなどのデータ・ウェアハウスに供給する出荷および総勘定元帳システムに 基づいています。

売上収益 コスト 利幅 売上収益 コスト 利幅 6 ヶ月間移動平均 6 ヶ月間移動平均 6 ヶ月間移動平均 12 ヶ月間移動 12 ヶ月間移動 12 ヶ月間移動 平均 平均 平均 前期比増加率 前期比増加率 前期比増加率 前年比増加率 前年比増加率 前年比増加率 季節予測 最適予測 最適予測 指数関数予測 線形予測 非線形予測 トレンド予測 最適予測 物理モデル

多次元モデルを使用した Oracle 10g Common Schema の選択と重大な分析要件によ り、Oracle Database 10g の OLAP オプションで管理する多次元データ型を使用した 物理モデルが実装されました。 メジャーはすべて同一のサブジェクト領域（Common Schema の販売データ）に関 連し、論理ディメンションを共有しているため、データ・モデル全体が単一のア ナリティック・ワークスペースに組み込まれています。アナリティック・ワーク スペースは、関連する多次元データ型データベースのコンテナです。 アナリティック・ワークスペースは、データベースの標準形式の仕様に基づいて 構築されています。これは、Oracle 管理ツールとエンド・ユーザー・ツールの理 解するデータ構造とメタデータを含むアナリティック・ワークスペースの物理設 計です。Oracle 管理ツールは、データベースの標準形式の仕様に基づいて、アナ リティック・ワークスペースを構築します。 ETL プロセス

Oracle Warehouse Builder が、アナリティック・ワークスペースの構築に使用され ています。Oracle Warehouse Builder を使用して、ソースの運用システムのマッピ ング、論理モデルの定義、論理モデルのアナリティック・ワークスペースへの配 置が行えます（従来のリレーショナル・データ・ウェアハウスと同様）。 Oracle Warehouse Builder は、AW_CREATE パッケージの提供する API を使用しま す。これは、OLAP オプションのコンポーネントで、アナリティック・ワークス ペースを作成して管理します。

また、AW_CREATE コール付きの SQL スクリプトや、Analytic Workspace Manager GUI（AW_CREATE パッケージ上に階層化）を使用して、アナリティック・ワー クスペースを構築および管理できます。 多次元データ型の SQL インタフェースを使用すると、多次元データ型で SQL ベー スのアプリケーションにアクセスできるため、リレーショナル表のデータを複製 する必要がありません。

一般用語

OLAP - オンライン分析処理（OLTPと比較）。 ROLAP - リレーショナルOLAP。ディメンショナル・ビューをリレーショナル・ データに組み込みます。 MOLAP - 多次元OLAP。アナリティック・ワークスペースに多次元形式で格納さ れるデータ。

アナリティック・ワークスペース（AW） - Oracle LOBに格納された多次元スキー マ。ディメンション、変数、階層、プログラムなどを含みます。 ディメンション - データを分類するための構造（時間、地理区分、製品など）。 階層 - 集計およびドリルダウンに使用するディメンションの定義レベル。 メジャー - 多次元データを格納（または計算）。ファクト列に相当。 計算式 - アナリティック・ワークスペース（AW）の計算メジャー。 キューブ - 同一ディメンションを有するメジャーの論理的組織。 メタデータ（CWM） - データなどの構成を説明するデータ。OLAPカタログとは、 キューブ、メジャー、ディメンション、および属性などの多次元用語でデータを 説明するメタデータのことです。 DML - データ操作言語。アナリティック・ワークスペース（AW）の多次元対応 プログラミング言語。 標準形式 - アナリティック・ワークスペース（AW）内のオブジェクトのロール と関係を識別する階層ディメンション、レベル・ディメンション、親子関係など の特定の構造。OLAPツールへのアクセス時にAWに必要です。 使用可能性 - アナリティック・ワークスペース（AW）のオブジェクトがOLAP API に認識されるプロセス。

OLAPI - Oracle BI Beanがアナリティック・ワークスペース（AW）と通信する場

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.