2017/05/23

[Excel BI] ピボット テーブルとメジャー

先日、Power BI 勉強会で「使える!クイック メジャー」というお題でお時間をいただき、メジャーの紹介をいたしました。勉強会にご参加いただいたみなさま、ありがとうございました。また、このブログで「Excel ユーザーにとってのメジャーとは?」というタイトルの記事を過去に投稿しています。参照数などから、この話題は興味のある人が多いようで、エクセル ユーザーも「メジャー」や Power BI といったキーワードが気になっているようですね。

Office 製品は、機能はもう十分です、追加は要りません、と言われた時期もありましたが、現在、Excel はちょっとした改善ではなく、ガラリと使い勝手が変わる機能強化が押し寄せてきているように感じます。

そのような機能強化の中で「これは・・・」と私も驚いた機能をご紹介します。先日の勉強会では、メジャーを理解するにはいったんExcelを(セルを基本とするワークシートを)忘れたほうがいいかもしれない、という紹介をしたのですが、実は、Excel のピボット テーブルで「メジャー」を使う、というお話です。

データ モデル利用のデメリット

VLOOKUP関数などを使わず、いわゆる「表引き」を実現する「リレーションシップ」の機能は、Excel 2013から標準機能として搭載されました。

[ブログ] テーブルのすすめ ピボット テーブルとリレーションシップ
https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html

[Office Support] Excel のテーブル間にリレーションシップを作成する
https://support.office.com/ja-jp/article/fe1b6be7-1d85-4add-a629-8a3848820be3

このリレーションシップ機能は、分析するデータを「データ モデル」に格納して、ピボット テーブルを作成しますが、当初、データ モデルにより使えなくなる機能がいくつかありました。その代表が「日付のグループ化ができない」と「集計フィールドが使えない」でした。
データ モデル利用によりグレイアウトされた集計フィールド
日付のグループ化などは機能拡張で後日対応しましたが、集計フィールドは日付のグループ化のような後日対応はありませんでした。集計フィールド同等の機能は、Power Pivot のメジャーや計算列で対応が可能だったからだと思います。

しかし、Power Pivot はすべてのエディションで利用可能ではありません。一部古いバージョン用を除いて、ダウンロードセンターから Power Pivot アドインのダウンロードもできなくなりました。
例えば、Office 365 Business の Excel で Power Pivot を利用することはできません。Office 365 ProPlus と Excel 2016 の Professional Plus のエディションに限られ、このエディションにはアドインとして最初から同梱されてます。つまり、所有している Excel のアドインに同梱されていなければ利用できない、ということです。

Excel 2016 のデータ モデルのピボット テーブル

では、Power Pivot を利用できないエディションでメジャーの作成ができないのか、ということです。
Excel でメジャーの作成を検索すると Power Pivot を使う情報がほとんどですが、Excel 2016 以降、Power Pivot アドインを含んでいない Office 365 Business 含め、いずれのエディションでも、ピボット テーブルでデータ モデルを利用すると、Power Pivot なしでも「メジャー」の作成が可能になっています。

以下のようなシンプルな列で構成されるテーブルを使って検証してみます。


このような表やテーブルからピボット テーブルを作成する時、[ピボットテーブルの作成] ダイアログ ウィンドウの [このデータをデータ モデルに追加する] のチェックを入れて、データ モデルにテーブルを追加します。ピボットテーブルは「データ モデル」に追加され、そこから作成されます。


複数テーブルの分析をしなくても、このチェックをオンにしてデータ モデルからピボットテーブルを作ることができます。
ピポット テーブル作成後、フィールド リスト ウィンドウに表示されるテーブル(サンプルでは [Table01])上で右クリックをすると、[メジャーの追加] を含むコンテキストメニューが表示されます。


[メジャーの追加] をクリックすると、メジャー のダイアログ ウィンドウが表示されます。ここで DAX を使ってメジャーの数式を作成・編集することが可能です。


集計フィールドの代替でメジャーを利用

Excel の集計フィールドの説明や解説では、テーブルの列の数値から消費税を計算したり、売上から原価を引いて粗利を計算したり、といった例が紹介されます。
データ モデルを使ったピボット テーブル レポートでは、集計フィールドのかわりにメジャーを使うことができますが、数式に相当する表記方法はメジャーの表記方法(=DAX数式)を使う必要があります。

たとえば、数値A列の数値に 0.08 を乗算する消費税の計算のようなものだと、集計フィールドを使うと以下のような数式になります。
集計フィールドによる消費税計算
メジャーでそのまま同じ式を使うと以下のようになります。

これは本物のメジャーなんですね。ですから、ワークシート上の構造化参照式のような数式はエラーになるんですね。='table01'[数値A]*0.08 という数式を入れた時のエラーは以下です。

この数式は無効または不完全です: 'Calculation error in measure 'table01'[mj2]: A single value for column '数値A' in table 'table01' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'。

'table01' の数値Aは単一の値として定義されていない(列で複数の行を持っている)から計算できません。min や max、count や sum などの集計関数を使って、単一の結果を取得して、それを使いなさい、というような内容です。

ピボット テーブル レポートの基本的な「動き」は、レポートの行ラベルまたは列ラベルとして設定された要素でグループ化をして集計するものです。よくあるのは行は製品区分、列は年度、のようなマトリクス表です。また、集計対象の要素が数値データで構成されていれば「合計」を計算し、数値以外を含んでいるデータであれば「個数」を集計します。これがいわゆる「暗黙のメジャー」というわけです。


ピボット テーブル レポートで計算が合わない原因

暗黙といわれるせいか、あまり意識されていないかもしれませんが、ピボット テーブルにおける数値列の計算は、まずは合計を計算します。たとえそれが列に対するパーセンテージを計算するものであっても、行ラベルのグループで「合計」をしてから、列合計を分母として、行ラベル合計を分子にして割合を計算、という順序です。(それゆえ実務では、サンプルとして使っている消費税の計算は、メジャーではなく、元のテーブルに列を追加して各行にて端数処理を行い合計すべきか、合計してから消費税を計算し、端数処理を行うかは注意しなければなりません)

ちょっと話がそれますが、ピボット テーブル機能が今ひとつ一般的にならず、人気がない、もしくは難しいといって避ける人が多いのは、個人的に上述の「まず、行・列のラベルのグループで合計しているんだよ」という暗黙の計算を意識していないせいではないかと思います。

ピボットあるあるの話で、かつ、間違った解説をしている記事がネット上にいくつかあるのですが、集計フィールドの解説で「受注金額は、単価*数量 で求められるので、ピボット テーブル レポートで 単価*数量 の数式の集計フィールドを追加すればいいんですよ。」と紹介しているものがあるのですが、これは完全な誤情報です。それ、本当に間違いです。Google での検索で結構上位にくるものでも、このような説明をしている記事を見ることができます。本当に注意してください。以下その解説です。

単純に以下を考えてみましょう。佐藤さんは、5月に、単価 100円のものを1個、単価1000円のものを1個売りました。佐藤さんの5月の売上はいくらでしょう?

100円*1個で100円と、1000円*1個で1000円、合計は 1,100円ですよね。

これをピボット テーブルの集計フィールドで、受注は 単価*数量 だからといって、受注の集計フィールドを作って計算すると、2,200円になります。2件しかありませんからすぐに間違いに気が付きますが、もし、件数が多い場合などすぐには気が付かないでしょう。

この計算は、まず、佐藤さんというレコード(行)を集めます。その次に、単価の列で合計します。単価は 100 と1000 が1つづつあるので 1100 です。次に数量の列で合計をします。1が2つあるので 2 になります。最後に集計フィールドで指定されている数式による演算を行います。1100 * 2 なので結果は 2200 です。

つまり、集計フィールドの例としてあげている数式の、受注金額=単価*数量 は、

受注金額 = SUM(テーブル[単価]) * SUM(テーブル[数量])

を計算しています。

これは、利益のような加算、減算であれば問題ありません。利益 = 売上 - 原価 の数式は、1行ごとに計算したものを最後に合計しても、総売上から総原価を引いても同じです。

ピボット テーブル レポートの計算結果が間違っていることがあるので使いたくない、という話を聞きます。「元データが汚いよね」というのも現実ですが、この行・列ラベルでの小計が優先されることを意識していない場合もあるかもしれません。

ここでメジャーに話が戻るのですが、その意味で、ピボット テーブルのメジャーの数式では、=[単価] * [数量] という数式を記入すると上述のエラーになります。明確に以下のように数式を書かなければなりません。そして、その段階で数式がおかしいことに気が付いてほしいわけです。

= SUM('テーブル'[単価]) * SUM('テーブル'[数量])

なお、受注金額の合計を求めたいのであれば、元データに単価*数量の列を追加するか、Power Query/取得と変換 のクエリエディターで計算列を追加し、あらかじめデータ モデルに計算データを取り込むことになります。Power BI Desktop の場合はデータを取り込んだ後で、計算列の追加を使ってもいいでしょう。

2017/05/12

[Power BI] 新しいライセンスと無料版の変更についての発表

日本では GW の真っ只中 5/3 に、Power BI の新しいライセンスと、それにともなう無料版の変更についての発表がありました。

Power BI はいくつかのサービスやコンポーネントから成り立つ「サービス ブランド名」です。今回の変更は、「Power BI Service」にかかわるライセンスの変更で、Excel アドオンの「Power Query」や「取得と変換」の機能そのものへの影響はありません。まず、ここがポイントですね。ただし、Power BI Desktop については、利用できる機能の範囲や容量がライセンスによって変わります。

以下は、英語版の情報と、日本マイクロソフトさんによる日本語訳の情報です。

Microsoft accelerates modern BI adoption with Power BI Premium

Power BI Premium の発表 および Power BI (無料) における変更点について

日本語訳情報のページにある以下の図がわかりやすいと思います。

Power BI Premium は Power BI Pro ライセンスと、ユーザー数カウントしない Power BI user ライセンスの組み合わせです。クラウド上の Power BI の「ノード」を占有することで、パフォーマンスの確保が可能で、ユーザー数でライセンスカウントをしないものです。ただし、ユーザー数が多くなれば、推奨されるノード数を増やさなければなりません。必要なノード数は「カリキュレーター」で求めることができます。

頻繁に使う、使わない、という一般ユーザーの想定にもよりますが、1ノードで1500人くらいには対応するようです。実際にカリキュレーターで試してみてください。なお、P1ノードと呼ばれる1ノードあたりの月額使用料は 545,000円 です。また、オンプレミスで Power BI Report Server の利用もノード数と同等分追加コストなしで利用可能になる、とのことです。

利用数が多くなれば、ユーザーあたりのラインセンス量が低くなることから、Power BI Premium の恩恵は規模の大きな組織、企業向けと言えるでしょう。

一方、Power BI Free は良い話と、注意しなければならない変更があります。
良い話は、多くの「Pro」と同じ機能が利用可能になる、という点です。
・接続できるデータソースの種類
・容量の上限
・更新頻度
・ストリームデータの行数制限

一方、Power BI Free の位置づけは「個人」(つまり、企業・組織での共同作業でなない)と明確に位置付けられたため、ダッシュボードの共有機能が利用できなくなります。
この意味は「共有の設定」ができないだけでなく、共有コンテンツの閲覧もできない、というものです。
上述については、Power BI の Community の掲示板のディスカッションから判断できました。
If I am a "Pro" user, can I share my dashboards with "Free" users? In our organization, there are really two types of individuals, Report Writers and Report Consumers. It would not make sense for us to pay for Pro licenses just for the users that we would be viewing some of the reports that we are putting together since they would not be using any of the other features of a pro license.
もし自分がProユーザーであれば、Freeのユーザーと自分のダッシュボードの共有はできますか?組織にはレポートを作る人と、レポートを見る人がいます。Proライセンスの機能を使わないユーザーであっても、レポートを見るためには Pro ライセンスが必要というのは理解できない、、、と言っているユーザーに対する管理人(MSFT)の返答は以下です。
That’s correct @nickjgill, as we announced this morning Power BI Premium tackles that issue. We have a calculator that can help organizations figure out which option makes sense depending on their mix of Pro Users and readers. Depending on size and usage, the solution might be different.
そのとおりで、その問題に対しての取り組みが Power BI Premium です。Proユーザーとリーダーの割合などカリキュレーターが支援します、、、

ソースはこちらです。

常時、Power BI Service を利用して、データインサイトの共有を即時にしたいのであれば、基本は Power BI Pro を1ユーザーあたり月額 1,090円でサブスクリプション購入する、共有コンテンツを利用する人数が多いようであれば、Power BI Premium の購入を検討する、という流れでしょう。ワークスペースにあるコンテンツ(ダッシュボード、App など)へのアクセスができないのが Free 版、と思って現時点は間違いないでしょう。

これらの変更は 6月1日からなので、今後さらに詳細の情報が公開されると思います。

こちらのブログも参考にしています。
Powered by Blogger.

自己紹介

自分の写真
1989年新卒で日本IBMに入社しダウンサイジング担当としてホストコンピュータと繋げるオフコン、UNIX、PCサーバーのプロジェクトを担当。1997年 MSKK(現日本マイクロソフト)入社、NT4出荷に伴い企業向けサポート部門のビジネスマネージャーとして Excel 使いとなり、2002年 にMSMVPなどをサポートするユーザーコミュ二ティ部門を設立、部門をリード。2006年にMSKK退職後、企業向けのITトレーニング会社・団体に携わり、2014年頃よりPowerBI勉強会主催メンバーの一人として参画、そのコミュニティ活動で MSMVP for Data Platform PowerBI 2017受賞。https://mvp.microsoft.com/ja-jp/PublicProfile/5002635 同年にMVP Awardを返上し、アマゾン ウェブ サービス ジャパンに入社、コミュニティプログラム担当として現在に至る。