Presentation Title

Size: px

Start display at page:

Download "Presentation Title"

ふじよしとどろき
5 years ago
Views:

1 Oracle Direct Seminar <Insert Picture Here> Excel 2010 PowerPivot で Oracle 日本オラクル株式会社

2 以下の事項は弊社の一般的な製品の方向性に関する概要を説明するものですまた情報提供を唯一の目的とするものでありいかなる契約にも組み込むことはできません以下の事項はマテリアルやコード機能を提供することをコミットメント ( 確約 ) するものではないため購買決定を行う際の判断材料になさらないで下さいオラクル製品に関して記載されている機能の開発リリースおよび時期については弊社の裁量により決定されます Oracle と Java は Oracle Corporation 及びその子会社関連会社の米国及びその他の国における登録商標です文中の社名商品名等は各社の商標または登録商標である場合があります 2

3 Agenda 1. Excel と Oracle Database 2. PowerPivot とは? 3. PowerPivot と Oracle Database を組み合わせて使う 4. BIセルフサービスで考慮すべきポイントと機能 1.SQL Server の新機能とされる PowerPivot が Oracle Database でも使えるのか? 2.BI セルフサービスとして利用する際の Oracle Database の強みとは? 3

4 Excel と Oracle Database Excel をフロントツールする主な方法連携方法メリット主な用途 MS Query を使用 VBA から ODBC などで接続 KeySQL を使用 PowerPivot を使用 Oracle Database のデータを Excel から簡単に抽出可能 VBA のコーディング次第で柔軟な処理 (Excel から Oracle Database の検索更新 ) が可能 Oracle Database に対する Excel からの検索更新の設定を GUI で簡単に行える Oracle Database のデータを Excel から簡単に抽出し高度な分析が可能参考 :@IT Excel Oracle 連携表のデータ確認単純なデータ抽出マスタ表メンテナンスなどマスタ表のメンテナンスデータ分析などデータ抽出データ分析 4

5 Agenda 1. Excel と Oracle Database 2. PowerPivot とは? 3. PowerPivot と Oracle Database を組み合わせて使う 4. BIセルフサービスで考慮すべきポイントと機能 5

6 PowerPivot とは? PowerPivot for Excel 2010 マイクロソフトが新たに提供を開始した Excel 2010 用のアドイン使い慣れた Excel をフロントツールとしてデータ分析やレポート作成を行うことができる 6

7 PowerPivot とは? PowerPivot は Oracle Database もソースとすることができる PowerPivot の特徴インメモリアーキテクチャによる高速なデータ処理 64 ビット (x64) にも対応 (32 ビット版と 64 ビット版の提供 ) さまざまなデータソースへの対応 SQL Server Excel ファイル Oracle, DB2, Teradata, Sybase, Infomix SQL Azure, Reporting Services, テキストファイル, Web ページ ( HTML ファイル ), Analysis Services, Microsoft Access 等 Excel の上限である 100 万件を超えるデータに対応 Excel で扱えるデータ量の上限の 1,048,576 行以上のデータにも対応 x64 環境でメモリを多く搭載していれば数億件のデータでも高速なデータ処理が可能参考 : PowerPivot for Excel 2010 によるセルフサービス分析 7

8 Agenda 1. Excel と Oracle Database 2. PowerPivot とは? 3. PowerPivot と Oracle Database を組み合わせて使う 4. BIセルフサービスで考慮すべきポイントと機能 8

9 PowerPivot から Oracle Database のデータを取得する流れ 1. 事前準備 Oracle Client / 接続ドライバをインストールする接続情報を設定する 2. PowerPivot から Oracle Database に接続する 3. データを取得する 9

10 Oracle Client / 接続ドライバをインストールする以下の 2 つをインストールします Oracle Client 接続ドライバ Excel に組み込まれているドライバを使用することもできますがパフォーマンス等に優れたオラクルが提供する Oracle Data Provider for.net(odp.net) を使用することをお勧めします ( 後述 ) なお上記 2 つは Oracle Data Access Components (ODAC) をインストールすることで同時に導入することができます 10

11 Visual Studio と融合 Oracle Data Access Components(ODAC) Oracle Developer Tools for Visual Studio.NET (ODT) 開発作業 C++ / C# VB.NET アプリケーション配布 IIS(ASP/ASP.NET) MTS/COM アプリケーション Microsoft Visual Studio データベース接続 IIS ストアド開発作業データベース接続 Oracle Database Extensions for.net (ODE.NET) Oracle Data Provider for.net (ODP.NET) Oracle Services for MTS Visual Studio 2008,2010 および.NET Framework 2.0 以上に対応 11

12 Oracle Client / 接続ドライバをインストールする ODAC のインストール :GUI で導入できます 12

13 Oracle Client / 接続ドライバをインストールする大量に導入する Instant Client XCopy により効率的な配布が可能 OUI Oracle GUI ベースのインストール DB Server, Client 共 GUI ベースで容易にインストール Silent install インストール作業の自動化 Microsoft SMS もしくは他のインストール製品を利用して配布可能大量クライアントの配布に最適 Instant Client XCopy ファイルコピーとバッチの実行でインストール可能大量クライアントの配布に最適 13

14 Oracle Client / 接続ドライバをインストールする XCopy Install.bat 14

15 接続情報を設定する設定した接続文字列が後で必要になります tnsnames.ora ORCL = (DESCRIPTION = ) (ADDRESS_LIST = ) (ADDRESS = (PROTOCOL = TCP) (HOST = host1)(port = 1521)) (CONNECT_DATA = ) (SERVICE_NAME = orcl) ネットワーク定義ファイルリスナー host1 Connect ユーザ名 / 15

16 PowerPivot から Oracle Database のデータを取得する流れ 1. 事前準備 Oracle Client をインストールする接続情報を設定する 2. PowerPivot から Oracle Database に接続する 3. データを取得する 16

17 PowerPivot から Oracle に接続する (1) PowerPivot ウィンドウから外部データの取り込みその他のソースを選択 17

18 PowerPivot から Oracle に接続する (2) テーブルのインポートウィザード Oracle を選択 18

19 PowerPivot から Oracle に接続する (3) テーブルのインポートウィザードで必要な情報を入力サーバー名 : 設定した接続文字列を入力 (P.15) 19

20 PowerPivot から Oracle に接続する (4) 詳細設定プロバイダー Oracle Data Provider for.net を選択 20

21 PowerPivot から Oracle Database のデータを取得する流れ 1. 事前準備 Oracle Client をインストールする接続情報を設定する 2. PowerPivot から Oracle Database に接続する 3. データを取得する 21

22 データを取得する (1) テーブルのインポートウィザードから取得データを選択 22

23 データを取得する (2) PowerPivot ウィンドウに取得されたデータが表示される 23

24 PowerPivot から Oracle Database のデータを取得する流れ 1. 事前準備 Oracle Client をインストールする接続情報を設定する 2. PowerPivot から Oracle Database に接続する 3. データを取得する 24

25 Agenda 1. Excel と Oracle Database 2. PowerPivot とは? 3. PowerPivot と Oracle Database を組み合わせて使う 4. BIセルフサービスで考慮すべきポイントと機能 25

26 BI セルフサービスで考慮すべきポイントと機能ユーザーが自由にアクセスする DB に必要な機能同時実行性通常のアプリケーションであればある程度考慮できるがセキュリティパフォーマンス 26

27 同時実行性 : 読み取り一貫性 Oracle Database 更新前確定行データを Undo 表領域から読み込む他社 DB 未コミット読み取りデータの更新が確定していない更新中のデータを読み込む更新前確定行データ更新中更新中更新前確定行データダーティーリードコミット読み取りデータの更新は確定するまでデータの読み込み待ちが発生更新前確定行データ更新前確定行データ更新中 Undo 表領域待ちが発生読み取り一貫性が保障される更新中データを読み込むダーティーリードが発生データ更新が確定するまで読込み待ちが発生 27

28 <Insert Picture Here> 他社にない行レベルロック機能もデータのロックを意識せずに開発ができるため今回のフレームワークを利用したシステムに重要な役割を果たしていた株式会社エムアンドシーシステム高橋純一氏これまで開発及び運用保守の作業を委託してきたパートナー企業に対して調査を実施したところ Oracle の技術を保有する技術者が多かったことそして他社にない行レベルロック機能もデータのロックを意識せずに開発ができるため今回のフレームワークを利用したシステムに重要な役割を果たしていた 28

29 BI セルフサービスで考慮すべきポイントと機能ユーザーが自由にアクセスする DB に必要な機能同時実行性セキュリティデータベース側でアクセス制御する必要があるパフォーマンス 29

30 セキュリティユーザー毎にアクセスできるデータを制限する必要があるあるデータはユーザー A と C に見せても良いがユーザー B には制限をかけたい 30

31 SE アクセス制御の方法 VIEW とオブジェクト権限ユーザ毎に View を作成しアクセスできるデータを制御仮想プライベートデータベース (VPD) ポリシー管理による柔軟なアクセス制御管理 EE 経理部 (FIN) CONNECT 経理部営業部 (SALES) CONNECT 営業部ユーザ : 経理部ユーザ : 管理者参照権限経理部 VIEW 別名 : 経理部 VIEW ユーザ : 営業部参照権限人事マスタ表経理部 (FIN) 営業部 (SALES) SELECT * FROM HR_DETAIL SELECT * FROM HR_DETAIL アクセス制御ポリシー A アクセス制御ポリシー B SELECT * FROM HR_DETAIL WHERE DEPT= FIN ; 人事マスタ表 SELECT * FROM HR_DETAIL WHERE DEPT= SALES ; 特徴 : 別名 : 営業部 VIEW それぞれのユーザ毎で View 等の設定を行うので管理が煩雑営業部 VIEW 特徴 : アプリケーションからは透過的に行レベルのアクセス制御が可能内部的に自動で Where 句が付与され検索できるデータを制限するアプリケーションの変更が不要 31

32 セキュリティ :VPD 見る必要のないデータへのアクセスを容易に制限 32

33 <Insert Picture Here> Oracle Database Vault をはじめとするオラクル製品の活用により低コストかつスピーディに最先端のセキュリティを実現できました株式会社三井住友銀行橋本智幸氏セキュリティは作り込めば作り込むほどシステムの拡張性を犠牲にしますしかもセキュリティ対策に時間をかけて業務ユーザが求める機能のリリースが遅れてしまうのでは本末転倒です今回は Oracle データベースの先進的なオプション機能を活用することで高いパフォーマンスを低コストでスピーディに提供できました DBA 用の ID に対するアクセス制御機能はほかのデータベース製品にはなく 2 つの機能 (Oracle Database Vault, 仮想プライベートデータベース ) を組み合わせることで見せる必要のない人に見せないというコンセプトをシステム保守面からも実現することができました 33

34 BI セルフサービスで考慮すべきポイントと機能ユーザーが自由にアクセスする DB に必要な機能同時実行性セキュリティパフォーマンスパフォーマンスが出なければ使われないサービス環境になってしまう 34

35 パフォーマンス ~ ODP.net 35

36 Visual Studio と融合 Oracle Data Access Components(ODAC) Oracle Developer Tools for Visual Studio.NET (ODT) 開発作業 C++ / C# VB.NET アプリケーション配布 IIS(ASP/ASP.NET) MTS/COM アプリケーション Microsoft Visual Studio データベース接続 IIS ストアド開発作業データベース接続 Oracle Database Extensions for.net (ODE.NET) Oracle Data Provider for.net (ODP.NET) Oracle Services for MTS Visual Studio 2008, 2010 および.NET Framework 2.0 以上に対応 36

37 Oracle Data Provider for.net 2002 年より提供開始データベースサーバーのプラットフォームを選びません VS.NET 2003 および 2005, 2008, 2010 に対応.NET Framework 1.0 以上に対応 ADO.NET に準拠したデータプロパイダ Oracle Database に特化したデータプロバイダのためパフォーマンス機能面で優れている 37

38 パフォーマンス ~ キャッシュの利用 38

39 結果キャッシュ (Result Cache) Oracle Database 11gR1 の新機能 11g から新たなキャッシュ機能が登場結果キャッシュクライアントキャッシュ Inventory Orders Line Items PL/SQL キャッシュ Inventory Orders Line Items 従来のキャッシュ Back Orders 読み込みブロック全体をキャッシュ Pick Lists Back Orders Pick Lists 結果キャッシュ SQL 検索結果のみキャッシュ PL/SQL ファンクションの結果もキャッシュ可能 OCI クライアント側キャッシュメモリの使用効率をあげレスポンスの高速化をはかる 39

結果キャッシュ (Result Cache) Oracle Database 11gR1 の新機能 SQL の結果キャッシュによってキャッシュされた情報は SGA 上 ( 共有プール内の領域 ) に格納されユーザ間でその情報が共有されるアクセス対象のテーブルのデータの更新が行われた場合

40 結果キャッシュ (Result Cache) Oracle Database 11gR1 の新機能 SQL の結果キャッシュによってキャッシュされた情報は SGA 上 ( 共有プール内の領域 ) に格納されユーザ間でその情報が共有されるアクセス対象のテーブルのデータの更新が行われた場合そのキャッシュは無効になるキャッシュ済みの SGA 結果が返され高速レスポンスユーザー A Select 1 共有プール SQL 結果キャッシュ 2 3 Select ユーザー B 大量データにアクセスするが結果として返す行が少量な場合に非常に有効例 ) 定型的なレポートなどのクエリー 40

41 結果キャッシュ (Result Cache) Oracle Database 11gR1 の新機能リザルトキャッシュを有効化する alter system set RESULT_CACHE_MODE = 'FORCE'; MANUAL ( デフォルト ) ユーザが手動でリザルトキャッシュを行う指定をした場合のみリザルトキャッシュの機能が使用される FORCE 常にリザルトキャッシュの機能が働くリザルトキャッシュを有効化する select /*+ result_cache */ first_name, last_name from employees RESULT_CACHE_MODE の値が MANUAL となっていた場合は SQL の中にヒント句を入れることによりクライアントリザルトキャッシュ機能を有効にすることが可能 41

42 パフォーマンス ~ マテリアライズドビューの利用 42

43 SE マテリアライズドビューの使用マテリアライズドビュークエリーリライト EE 支店時間実データを持つビュー売上元表製品製品表売上表支店表時間表マテリアライズドビュー処理に時間がかかる結合処理や集計集計済みのマテリアライズドビューを参照通常 Mview ファクト表ディメンション表に対する問い合わせを自動的にマテリアライズドビューを参照するように書き換える支店時間売上元表製品オプティマイザ - マテリアライズドビュー売上集計 SELECT ~ FROM 売上製品支店時間 WHERE ~ SELECT ~ クエリーリライト FROM 売上集計 WHERE ~ 売上集計特徴 : 効率が悪い集計や結合を事前に計算し格納することでパフォーマンスの向上が可能アプリケーションの書き換えが必要特徴 : アプリケーションの書き換えなしに検索を高速化することが可能 43

44 パフォーマンス ~ パラレル処理の利用 44

45 パラレルクエリーの使用 SE シリアル処理パラレル処理 EE 1 つのプロセスで処理を実行複数の CPU を使用し並列で処理を実行 3CPU は使われない全部の CPU を使う P1 P2 P3 P4 P1 特徴 : 複数の CPU を使用せずシリアルで処理を行うため処理に時間がかかる特徴 : 複数 CPU を有効活用し検索 / DML /DDL 処理を高速化大量のデータを扱う DWH システムでは特に効果を発揮それぞれの SQL に対して Oracle が最適なパラレル度を自動設定 11g R2 45

46 参考 :In-Memory Parallel Query 11g R2 従来のパラレル処理 In-Memory Parallel Query 必ず Direct Path Read でディスクからデータにアクセスをして処理一度メモリ上にデータを読み込んでから処理を実行 QC QS QS QS QC QS QS QS 特徴 : 必ずディスクからデータを読み込んで処理を行うのでメモリ使用率が低下する特徴 : パラレルクエリー実行時のメモリ使用効率の最適化し従来のパラレル処理と比べ高速に処理を実行一度メモリ上にデータを読み込む際はボトルネックが発生 46

47 まとめ SQL Server の新機能とされる PowerPivot は Oracle Database でも使える PowerPivot から Oracle Database への接続にはパフォーマンスに優れる Oracle Data Provider for.net がお勧め BI セルフサービスとして利用する際の Oracle Database の強みとは? 同時実行性セキュリティパフォーマンス 47

48 48

49 49

Slide 1

Oracle Direct Seminar Excel 2010 PowerPivot で Oracle!! 日本オラクル株式会社 Agenda PowerPivot for EXCEL 2010(PowerPivot) とは PowerPivot から Oracle Database に接続 PowerPivot から Oracle Database の機能を利用する