テクテク日記

テクテク=テクノロジー&一歩ずつ(テクテク)

Power BIの差別化要素4 -Excelで分析機能

Power BIの差別化要素その4は、「Excelで分析」機能となります。その名の通り、Power BIで構築済のデータモデルをデータセットとして、定義済メジャー*1やリレーションシップを活用し、Excelで通常のPivotテーブルでは実現できないような計算を行うことができるようになります。

※記事の一番下に「Excelで分析」に関するMicrosoftの公式ドキュメントを載せていますので、そちらが必要条件や制限事項等についてはそちらをご参考ください

  •  ツール費用(無料)
  • モデリング機能(データモデル、複合モデル)
  • ETL機能( Power Queryによるステップ記録機能で完全自動化)
  • Excelで分析」機能
  • 時系列操作関数の扱いやすさ
  • 関数言語(Excelから継承した関数の数々)及びクエリ言語
  • 計算エンジン(SSAS Tabularモデルをベースとした強力な分析エンジン)
  • Microsoftの他のサービスとの連携
  • 1つのテクノロジー(BIとしてのExcel
  • SaaS型BIソリューション

Excelで分析とは

Excelで分析」は、Power BIやPower BI Serviceに発行した構築済モデル(データセット)をベースに、ExcelのPivotテーブルやPivotチャート等で分析できる機能です。この機能はMicrosoftのAnalysis Service(分析サービス)を活用したもので、主に以下のようなメリットがあります。

  • データ操作における柔軟性の高いExcelで大量データの分析が可能
  • 使い慣れたPivotテーブルで素早く分析が可能
  • 定義済データモデルやDAXで計算されたメジャーを使う
  • 物理的にデータを読み込まないため、ファイルサイズが非常に軽い

なお、Analysis ServiceはMicrosoft社が持つ技術で、Tabularモデルと言われます。分析用データベースエンジンを用いて、データモデルが構築されたデータセットDAX*2を記述し、大量データの分析を容易にするサービスです。

概念図

以下は、「Excelで分析」の概念図となります。

f:id:marshal115:20210613171340p:plain

 

図の通り、要するに今までExcelでやっていた作業をPower BIやPower BI Serviceのデータセット(=データベース)に繋げて、その中身のデータをPivotで可視化させることをイメージしてもらえれば分かりやすいと思います。このような形でデータ作業を行うニーズの背景には、

  1. Power BIは使えないが、Excelユーザーであり、Pivotテーブルであれば問題なく操作できる
  2. Power BIで作った数字が果たして正しいかどうかの確認(例:各分類の数値の合計と全体合計が合っているかどうか)
  3. BIにある各種指標をExcelのPivotテーブルのフォーマットで共有したい

等の理由があることが想定されます。

概要図の用途や特徴は以下の通り

  • データモデルが構築されたPower PivotやPower BIはデータベースサーバーとして機能
  • Cloud Databaseはそのままでは分析ができないが、Power BIやExcelといったクライアントを介すればデータ分析が可能
  • Power BIをデータベースサーバーとしてExcelで分析することができる。この場合、Excelをクライアントとして、Power BI Desktop、もしくはクラウド版のPower BIサービス(Power BI Proライセンス必要)に接続させてAnalysis Serviceの機能を使用することができる

こちらの説明がピンとこない方はいずれ、この意味とすることを理解できるようになると思いますので、今はPower BI→Excelベースで分析ができる、と覚えておけば良いかと思います。

Power BIサービスからExcelで分析を使用する

以下、Microsoftクラウドサーバーから「Excelで分析」を使用するやり方を紹介します。なお、下記やり方はPower BI Proのアカウントを取得していることが必須条件となります。

  1. Power BIサービスに接続する
    https://app.powerbi.com/

  2. 下図の通り、右上の「・・・」より、「Excel更新プログラムで分析」をクリック

    f:id:marshal115:20210427004741p:plain

  3.  初回使用時は「Microsoft Analysis Service OLE DBプロバイダー」をインストール

    f:id:marshal115:20210427005128p:plain

    f:id:marshal115:20210427005143p:plain

    f:id:marshal115:20210427005146p:plain

    f:id:marshal115:20210427005149p:plain

  4.  インストール完了後、対象ファイルの横の:から「Excelで分析」をクリック

    f:id:marshal115:20210427005533p:plain

  5. ODC(Office Data Connection)*3ファイルのダウンロードが始まり、クリックする

    f:id:marshal115:20210427010055p:plain
    注:2022年5月時点では、以下のように「Excel for webで開く」という選択肢があり、これをクリックした場合、Excelアプリケーションではなく、Web版Excelが立ち上がることに

    一方、これとは別に、ExcelファイルはDownloadフォルダではなく、OneDriveの直下に保存されるようになるため、DownloadフォルダからExcelファイルが消えたとならないよう注意して頂きたい
  6. Excelが立ち上がるため、有効化という表示があれば有効化を行う(最大で2回実施する可能性あり)
  7. Excelが立ち上がり、下図のようにフィールドが表示されれば成功となる

    f:id:marshal115:20210427010524p:plain

  8. これで、クラウドに発行されたレポートにExcelで繋げることに成功したため、好きなデータ粒度で分析ができるようになる

なお、クラウドベースの「Power BIサービスからExcelで分析を使用する」を行った場合、サインイン情報が残っている限り、Excelを閉じて再度開いてからでも、Pivotテーブルの更新が可能となります。留意点として、当該機能が使用でいるのは、同じテナント(例:同じ会社)に限られることであり、外部とレポートを共有して「Excelで分析」を使用したい場合は、この方法ではできません(別途機会があれば解説予定)。

また、下記説明する通り、Power BI Desktopをサーバーとして「Excelで分析」はやや癖があるため、留意が必要となります。Power BI Desktopファイルを使って分析する方法は主に2つあり、それぞれ異なるツール(どちらも必須インストールツール)を使います。

① Power BI Desktopをサーバーとして「Excelで分析」(DAX Studio使用)

Power BI Desktopをデータベースサーバーとして、構築済モデルを使ってExcelで分析(オンプレ版Excelで分析)する手法は以下の通りとなります。

  1. Power BIとDAX Studioがインストールされていることを確認
    Power BI: https://powerbi.microsoft.com/ja-jp/desktop/
    DAX Studio: https://daxstudio.org/

    ※どちらも必ず最新版がインストールされていること

  2. Power BIを立ち上げる(データモデルが存在していることが必須)

    f:id:marshal115:20210502163756p:plain

  3. ファイル > オプションと設定 > オプション > プレビュー機能 > 拡張メタデータ・・・に✅

    f:id:marshal115:20210502165021p:plain

  4. Power BIを一回再起動
  5. 「外部ツール」タブ > DAX Studioの順にクリック

    f:id:marshal115:20210502165157p:plain

  6. DAX Studioが立ち上がるため、Option > Advanced > 全てチェックオンf:id:marshal115:20210502164218p:plain
  7. ①Modelビューでデータが取り込まれていることを確認し、②Advanced > Excel

    f:id:marshal115:20210502164326p:plain

  8. Excelが立ち上がり、セキュリティ通知を「有効にする」 > ④Pivotテーブルが作成される

    f:id:marshal115:20210502164357p:plain

  9. 通常のPivotテーブルを操作するように、作業する
    ※下記画像のΣSalesにある各種指標(NetSales、GM%等)はPower BIにて作成されたメジャー。なお、メジャーの更新や追加はExcel側ではなく、Power BI側で行う必要がある

    f:id:marshal115:20210502164502p:plain

  10. 一度ファイルをどこかに保存し、ファイルサイズの確認
    ※物理的にデータを読み込んでいないため、保存ファイルも非常に軽い

    f:id:marshal115:20210502164612p:plain
    通常Excelで作るピボットテーブルと比べ、ファイルサイズがこれほど小さい理由は、「Excelで分析」機能を使って作ったピボットはシートに出力された分の情報しかメモリを使用しないことです。通常の場合、ピボットは全てのデータをキャッシュしてしまうため、ファイルサイズが大きくなる仕掛けというわけです。

② Power BI Desktopをサーバーとして「Excelで分析」(SQLBI製ツール使用)

  1. Power BIとDAX Studio、Analyze in Excelの3つがインストールされていることを確認(下記3つ)
    Power BI: https://powerbi.microsoft.com/ja-jp/desktop/
    DAX Studio: https://daxstudio.org/
    Analyze in Excel for Power BI: https://www.sqlbi.com/tools/analyze-in-excel-for-power-bi-desktop/
  2. Analyze in Excel for Power BIは上記リンク(Analyze in Excel for Power BI:)の右にある「DOWNLOAD NOW」をクリックし、そのままインストールを済ませる

    f:id:marshal115:20210502165815p:plain

  3. 前述の方法と同じように、Power BIの「外部ツール」 > Analyze in Excelをクリック

    f:id:marshal115:20210502170046p:plain

  4.  Excelが立ち上がり、前述と同じく、Pivotを作ることが可能になります

この方法が「Excelで分析」を使うにあたって最も簡単な方法となります。また、Analyze in Excel for Power BIはBIの神であるMarcoさんが作ったものであることから、信頼性も抜群と言えます。

留意事項

Analyze in Excelは非常にパワフル、かつ便利である一方、現時点克服できない弱点が1つあります。それは、オンプレデータ*4で作り、Power BIを閉じた場合、Pivotを更新しようとすると下記のメッセージが出現してしまうことです。以下、詳細及び解決法となります。

  • Power BIを閉じた場合、Pivotを更新しようとすると下記のメッセージが出現
    Excelで作業を行っている間は常にPower BIを立ち上げておく必要がある

    f:id:marshal115:20210502170701p:plain

  • エラー出現後(全て「はい」or「OK」)、更に下記ウィンドウが出現するため、Serverに正しいlocalhost番号(毎回変わる)を再度入力してあげる必要あり(Pivot数だけエラーメッセージが何度も出現するため、根気よく全て変更すること

    f:id:marshal115:20210502170713p:plain
    localhost番号が必要となるため、以下のように取得を行います

  • 対処法として、まずは対象Power BIを再度立ち上げ、「外部ツール」 > DAX Studioで再Connect。すると、右下にlocahost:◎◎◎◎◎が出現するため、隣にあるアイコンをクリックし、コピーする

    f:id:marshal115:20210502171007p:plain

  • 上記Server入力ウィンドウにそのままペーストし、Next > Finish
  • Pivotテーブルが更新できるようになったことを確認

本件、注意する必要があることとして、上記画像のAnalysis Service Connection 14.0は作られたPivotテーブルの数だけ、何度も出現するため、このやり方で運用を行っていく場合、Pivotテーブルをあまり作らないことを推奨します。

まとめ

  • Power BIとExcelの連携は「Excel で分析」機能を使う
  • BIをExcelで連携する理由は、Excelで活用するニーズに対応するため等がある
  • Excelで分析」はクラウドにあるデータから接続する場合と、オンプレにあるデータ(Power BI Desktopに格納されたデータ)の2パターンあり、前者のほうが手軽、かつ、メンテナンスの効率性が高い
  • オンプレデータで「Excelで分析」を行った場合、Power BI Desktopを常に立ち上げておく必要がある。閉じてしまった場合はlocalhost番号を再取得する必要がある

BIが普及しつつある世の中ですが、最も使用されているBIツールが依然としてExcelになるため、Excelベースで数字を分析するニーズが根強く残っていることを考えると、「Excelで分析」はなくてはならない機能の1つであると言えます。 なお、公式ドキュメントは下記より更に詳細を確認できます。

docs.microsoft.com

*1:DAXで記述した定義式で例えばSalesAmt = SUM ( Sales[SalesAmount] )のように記述したもの

*2:DAXはPower BI、Power Pivot for Excel、Analysis Serviceで使用される言語

*3:ODCはMicrosoftが開発したデータ接続ファイルであり、スプレッドシート/ワークブックのデータを更新するために実行される外部データベース接続の認証設定が保存されている

*4:Power BI Desktopに格納したデータのこと