Office 365 Access Services で作成された Azure SQL database を Excel で使う

この投稿は Facebook の Office 365 コミュニティの企画の「Office 365 Advent Calendar 2013」の 12 月 7 日分の投稿としてアップしたものです。

みなさん、こんにちは。クリエ・イルミネートの沼口(非トレーナー/ノンテク担当)です。

今日はとても素敵な SharePoint(本当は違う)と Excel の連携機能のご紹介です。ただし、利用する一部の機能 (PowerBI – Power Query) はまだプレビュー版ですので「へぇ、そんなこともできるんだ」程度に捉えて頂ければ幸いです。また、私はトレーナーではありませんが、SharePoint のトレーナー、コンサルタントが身近にいることと、業務で毎日のように SharePoint と Excel を使っていることから以下をまとめていますので、弊社のトレーナーによるブログ投稿のような「会社としての技術情報の公開」ではないことをご了承・ご理解ください。

私が会社の SharePoint でよく使う機能としてドキュメントライブラリやリストの「Excel にエクスポート」機能があります。SharePoint 上でもライブラリの設定で集計やフィルター機能があるのでビュー切り替えで済ませることも多いのですが、Excel に取り込むことで Pivot テーブルを使ったり、報告用のデータ集計、グラフ化ができるようになります。

データそのものはサーバー側で管理して、そのスナップショットを Excel に持ってきて集計する、というのは理想的な使い方です。クリエ・イルミネートではあらゆる業務で InfoPath フォームを使い、SharePoint のフォームライブラリーに蓄積・一元管理しています。蓄積されたデータの集計・分析などは Excel エクスポートを使って Excel で行っています。

SharePoint アプリとしての Access Services

一方、オンプレの SharePoint Server のみならず、Office 365 の SharePoint Online の機能でも  Access Services というものがあります。InfoPath が 2013 であまり進化しなかったのに比べて、Access Services はものすごく変わりました。その話しは弊社の奥田が本ブログに投稿しております。

InfoPath 2013 & Access 2013 新機能と SharePoint 連携(クリエ・イルミネート ブログ)

この大進化した Access Services、SharePoint アプリといいながら、データそのものは SharePoint のリストを使っていません。バックエンドで Windows Azure SQL Database を使っているのです。

Microsoft Access 2013: クラウド対応の手軽で斬新な開発ツール(Windows Azure JP Team Blog) (モバイル用サイトなので画像が。ブラウザの幅狭めてみてください。)

なんと、、、Azure SQL ですよ!以下のお値段のサービスを(今のところは)追加料金なしで利用できるんですよ!

Windows Azure SQL データベースの料金詳細(価格が「日割り」に注意してください。)

SharePoint アプリとして Access Services から Azure SQL database を利用した場合、そのデータベースの上限サイズは 1GB だそうです。

Access 2013 app と Access desktop database の比較(英語 office.microsoft.com)

また、テーブルのレコード数(アイテム数)の既定の制限は 50,000 件とのこと。

http://office.microsoft.com/ja-jp/HA102771361.aspx#_Using_Access_Services

オンプレミスだと既定の上限を変えられると思います。SharePoint Online の場合は、現時点で最新の Online モジュールを追加した PowerShell を使い Get-Help Set-SPO* で見る限りだと上限変更のためのコマンドレットらしきものがありません。

オンプレミス用 Set-SPAccessApplication のオプション RecordsInTableMax

とはいえ、5万件のテーブルは魅力的です。
そうすると、このテーブルにデータが入力され蓄積されれば、Excel のデータ接続を使って使いたくなりますよね?

SharePoint の [アプリの追加] から Access アプリを追加して、ローカルの Access 2013 を使ってのデータのインポートやフォーム設定はほんとうにあっさりと終わります。そのインポートされたデータが Azure SQL Database に入っています。これを Excel にエクスポート(データ接続)して使うのが最初の目標です。

SQL Server へのデータ接続ではつながらない

オンプレミスの SharePoint Server 2013 と SQL Server の組み合わせで Access Services を使った場合はたぶん問題なく [データ] タブの [その他のデータソース] にある [SQL Server] で接続できると思いますが、Office 365 の場合は Windows Azure SQL Database です。Access のバックステージの [接続 – 管理] の [読み取り専用接続の情報の表示] から [SQL Server の接続情報] を取得できますが、、、、

接続情報

それを [SQL Server] で接続しようとすると、、、

サーバーに接続

怒られます。。。

エラーダイアログ

メッセージは「Cannot open server ‘xxxxxxx’ requested by the login. Client with IP address ‘XXX.XX.XX.XXX’ is not allowed to access the server. To enable access, user the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.」です。ファイアウォールでブロックしてるから IP アドレスを認めるように。反映されるまで 5分はかかる、、、、と。

このメッセージに従って Windows Azure Management Portal を探したり、PowerShell のやり方を、、、となると残念な結果になります。

Access app のバックステージの以下の部分で firewall に関する設定は本来されているはずです。

AccessBackStage1

Power Query Preview for Excel

現時点(2013/12)では Office 365 SharePoint Online の Access 2013 app で作成された Azure SQL database へのアクセスは「Power Query Preview for Excel」を使います。9月くらいの Power Query では一時期「データベース」の指定ができずに同様のエラーになった時期もあったと記憶していますが、11月版ではデータベースの指定ができるようになっています。また、最新版ではリボンの表示も日本語化されました。ちなみに Power Query は以前は “Data Explorer” と呼ばれていました。

PowerBI for Office 365 サイト

Power Query ダウンロード
注:Power Query はダウンロードセンターに複数あります。最新版は 2013/11/8 に公開されている version 2.8.3476.202 で、現時点での正式名称は「Microsoft Power Query Preview for Excel」になります。32bit 版と 64bit 版がありますが、Windows ではなく Office に依存です。32bit 版 Office を使っている場合は Windows が 64bit でも 32bit 版の Power Query をダウンロードします。

日本語化された Power Query のリボンと [Windows Azure SQL データベースから] の接続

PQ1

サーバーとデータベースの指定に Access のバックステージから取得した接続情報をいれます。

PQ2

認証は [データベース] を選び、Access のバックステージから取得したユーザー名とパスワードを入れます。

PQ3

接続がうまくいけば、ナビゲーター ウィンドウにシステム テーブルを含めたテーブル一覧が表示されます。Access Services で作成したテーブルを選べばデータがプレビューウィンドウに表示されます。

PQ5

[読み込む] ボタンで、データがワークシート上にテーブル形式で読み込まれます。
(データは実在しないサンプルデータです)

PQ6

これで Azure SQL database 上にあるデータを Excel に取り込むことができました!!!

しかし、、、データベース的な使い方、すなわちデータベースの設計をすると正規化によりテーブルを分けることになります。顧客データ(マスター)や売上データ、社員データやもろもろです。結果、Azure SQL データベース上にある、たった1つのテーブルをデータ接続(Power Query)で Excel で利用できるようになっても多くの場合は「必要なデータが足りない」ということになります。

Power Pivot が必要になる

Access アプリを使って Azure SQL database を使うとなると、テーブル設計をして、データの正規化を行い、テーブルを分割することになるでしょう。単純な Access フォームと単体のテーブルの組み合わせも当然あるでしょうが、たとえば顧客マスターとトランザクション系テーブル(受発注など)は分けるのが普通です。

となると、テーブルを JOIN させないと分析のための適切なデータを収集できません。

そのために「Power Pivot」が PowerBI for Office 365 の一部として扱われています。
Power Pivot で複数のテーブル間にリレーションを張って、レコードに関連するデータを集約することができます。

現在、Power Pivot は COM アドインとして Office 365 ProPlus および Office Professional Plus 2013 の Excel に最初から組み込まれています。
http://office.microsoft.com/ja-jp/excel-help/HA102837097.aspx

Excel 2010 の場合はアドインをダウンロードする必要があります。
http://office.microsoft.com/ja-jp/excel/HA101959985.aspx

Power Pivot のタブを有効化して、実際にテーブルを JOIN し、Pivot テーブルを作ってみます。

たとえば、顧客マスターみたいなものがあり、もうひとつトランザクション系のテーブルとして [日時] [メールアドレス] [アクセスURL] のみのアクセスログのようなテーブルがあるとします。このアクセスログを使って、顧客マスターにある「名前」、「都道府県」や「性別」や「利用キャリア」などの情報を付加して、Pivot テーブルを作る、みたいなことをやってみます。いずれも Access アプリを使って、Azure SQL database 上にテーブルを作っています。

取り込みは Power Query によるデータの取り込みだけではなく、Power Pivot ウィンドウで利用できる「データモデル」としてテーブルを取り込むことができます。Power Query のナビゲーター ウィンドウで [複数のアイテムの選択] をオンにして、ウィンドウ下部の [データ モデルに読み込む] のチェックを確認して読み込みます。(注:11月の Power Query のバージョンでこのデータ モデルのみの取り込みには若干動きが怪しいときがあるようです。一度、ワークシートにデータを読み込み、そこからモデルを追加したほうが安定しているようです)

PQ7

データモデルの読み込みが完了すると [ブック クエリ] ウィンドウにその旨のメッセージが表示されます。

PQ8

ここから Power Pivot の出番です。
Power Pivot タブのデータモデル セクションの [管理] をクリックします。

PP1

Power Pivot ウィンドウが表示されるので、表示セクションの [ダイアグラム] をクリックします。

PP3

そうすると、データモデルをダイアグラム ビューで確認することができます。

PP4

顧客テーブル(Access CustTable)の [アドレス] とアクセスログ(のようなもの)の [メールアドレス] の間にリレーションを張ります。アクセスログの [メールアドレス] から顧客テーブルの [アドレス] を参照するので、[メールアドレス] をドラッグして [アドレス] の上にドロップします。

PP5

最終的に欲しいのは アクセスログの [メールアドレス] のテーブル追加列情報として、顧客テーブルの [アドレス] と一致しているレコードから [名前] や [都道府県]、[性別] などのデータを持ってきたい、ということです。

[データビュー] に表示を切り替え、[Access アクセス情報テーブル] タブをクリックして、データビューでテーブルを表示します。

PP6

ここで [列の追加] に関数をいれて関連データを参照させます。

それが RELATED 関数です。RELATED 関数は「DAX関数」と呼ばれる Power Pivot 独自の関数です。DAX 関数のリファレンスはこちらを参照ください

[列の追加] ラベル下の空白のセルを選んで、数式バーで「=RE」とタイプすると RE から始まるオブジェクトや関数が表示されます。RELATED 関数を選択してから続けて「=RELATED(acce」と入れると Excel 構造化参照と同様に acce… から始まる参照可能な構造化データがドロップダウンリストで表示されるので、Access CustTable の[名前] を選択します。

PP7

Enter を押せば、すべての行に [メールアドレス] と [アドレス] で関連付けられた Access CustTable の [名前] が挿入されます。

これ、普通にすごいですよね。VLOOKUP 関数や MATCH, INDEX 関数使わなくてもいいんですから!

PP8

同様に欲しい列を追加していきます。なお、列名は後から変更が可能です。

PP9

準備ができたら、リボンの [ピボットテーブル] から [ピボットテーブル] を選びます。

pp10

すると Power Pivot ウィンドウから Excel のウィンドウに切り替わり、見慣れた Pivot Table 作成の画面になります。[Access アクセス情報テーブル] を展開すると、追加した列が表示されていることがわかります。

PP11

DAX の RELATED 関数を使って Access アクセス情報テーブルに列の追加をしました。列が追加されたテーブルを使って Pivot を作るのはそう難しくはありません。そこは Pivot を使ったことがある Excel ユーザーであれば理解できると思います。実は、RELATED を使わなくても名前や都道府県や性別を知る(表示させる)こともできます。

Pivot Table ですから、リレーションをはった上で、集計列なしで(RELATED 関数を使わず)以下のような集計を出すことが可能です。アクセス情報テーブルのデータが、顧客マスタの行列クロス集計上どこにあるか、という Pivot です。メールアドレスでリレーションが張られているので、該当する行・列に、件数が表示されています。

PP13

PP14

この Pivot テーブルはデータ接続されているサーバー側のデータソースを参照していますから、サーバー側のテーブルの更新を反映させることも可能です。(自動(間隔設定)、手動、即時更新の選択ができます)

PP15

複数のテーブルから情報を抜き出して、情報リスト一覧を作るのであれば DAX を使って表を作ることになるでしょう。分析・集計という「数値」を扱いたいのであれば、行・列の部分を決めて、集計データ [値] の部分にトランザクション系のテーブルから数値フィールドを入れて集計、という使い方になりますね。

Power Pivot および DAX については SQL Server の自習書シリーズがいいと思います。前のバージョンベースですが、唯一日本語で書かれた詳しい解説でしょう。

SQL Server 技術情報 自習書シリーズ

「Power Pivot for Excel によるセルフ サービス分析」からドキュメントをダウンロードしてください。

エンドユーザーにリレーション設定を求めるのムリがある

これまでは単純な例なのでテーブル数もリレーション数も少ないのですが、データの正規化を行っていけば、テーブルの数、リレーションの数はさらに多くなっていきます。業務向けに正規化されたテーブルのリレーションをエンドユーザーにゆだねるのは現実にはきびしい話です。分析用のリレーションをはったデータ モデル群を用意して使ってもらうことになるでしょう。

通常、これが BI でいうところの「キューブ」や「多次元データベース」に近いものになります。

BI は言葉としては難しい印象がありますが、Excel の Pivot テーブル、Power View などでやっていることそのものです。作成した Pivot テーブルの数値をダブルクリックすると該当するデータ(レコード)が表示されるのは「ドリリング(ドリルダウン)」ですし、スライサーやフィルターをつかって店舗別や時系列にデータを分けることは「スライシング」、Pivot で列や行を入れ替えて見方を変えることを「ダイジング」と BI の世界では言い、それらの元となるデータが「キューブ」や「多次元データべ―ス」になります。

分析用データさえ用意できれば、Power Query という強力なデータ接続機能があるので、さまざまな方法でエンドユーザーの Excel に提供することが可能になるのが魅力です。また、Power Query を使わなくても、SharePoint Online の BCS (Business Connectivity Service, Office 365 では E3 以上で利用可能) や Excel Services を使って、適切なアクセス権を設定しながらエンドユーザーに提供することが可能ですよね。

SharePoint Online での Business Connectivity Services (BCS) に関する特記事項(日本マイクロソフト 米田さんブログ)
Excel 2013 新機能 Overview(SharePoint 連携も) (奥田さんのクリエ・イルミネート ブログ)

CSV エクスポート/インポートで上記を実現されている企業様も多いですが、PowerBI for Office 365 のように一度設定すればリアルタイムでデータが参照できる、分析できる方式にそろそろ移ることができる時期かもしれません。

データ量が多いとどうなるか?

残念ながら、自ら試していないのでパフォーマンス含め「こうだ!」と言い切れませんが、マイクロソフトから以下のような記事が公開されています。データ モデルとしてテーブルを扱うのであれば、ワークシート上限の100万レコードを超えたテーブルを扱うことが可能とのことです。また、64bit 版の Excel および PowerBI アドインを使えば、実質 PC に搭載されているメモリー依存になり、メモリーを積めば積むほど数億件のような巨大なデータも扱えるようになりビッグデータ分析も可能とのことです。

データ モデルの仕様と制限 (office.microsoft.com)

Excel 2013 と Power Pivot アドインを使用して、メモリを効率的に使用するデータ モデルを作成する  (office.microsoft.com)

Workbook Size Optimizer ダウンロード (Microsoft Download Center)

まとめ

PowerBI for Office 365 が発表された時、そのブランドの “Office 365” の部分に非常に違和感を感じていました。PowerBI for Azure とか、Cloud とか、そんな感じのほうがしっくりきていたのですが、SharePoint Online や Access Service を使って企業内のデータを Office 365 のサービスに蓄積していくことで、SharePoint リストや Access アプリ経由で作成した Azure SQL database、さらにはオンプレミスも含めた他のインフラ/サーバー上のデータを非常に簡単に、かつコスト負担増なしで Excel から利用できるとなると、この PowerBI for “Office 365” の部分の意味合いが理解できたような気がしました。

[追記] 実は本当の意味での「PowerBI for Office365」を思い知らされるのは Power BI Admin Center と OData フィードとデータマネジメント ゲートウェイによるクラウド~オンプレミス連携になるんですけどね。
http://www.microsoft.com/ja-jp/showcase/details.aspx?uuid=87533af0-431e-49c5-a60b-939aa9b547de

広告

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト / 変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト / 変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト / 変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト / 変更 )

%s と連携中