前回はDynamicsと接続してデータセットの作成を行いましたが、デフォルトの状態だと使いづらさがあるので見やすく、使いやすく、軽くするようにします。
データセットをより使いやすく
Dynamicsのエンティティから作成したてのデータセットは、重たい、見辛い、分かり辛いの三拍子揃った使い勝手の悪さなので、少しでも使いやすくするための小ネタ集になります。
Power BIというよりは、Power Queryを使ってデータセットを編集していく形になります。
使いこなせればもっと小技があると思いますし、もしかしたら以下手順よりいい方法があるかもしれません。
全部のボタンを押して試した訳ではないので、「いろいろやろうぜ」精神でちょっと発掘してみてください。
フィールドをリネーム
取り込んだデータセットはフィールド名がスキーマ名（英数字）となっているため、分かりづらいです。
レポートでよく使う項目は日本語名などの分かりやすい名前にして列名を変えておきましょう。
オプションセットの表記設定
Dynamicsで使用するオプションセットはユーザ側から見やすくするためのラベルとデータ内で処理するコード値で構成されています。
ところが、データセットで取得する時は残念ながら、ラベル値が取れないみたいで、コード値のみの取得になってしまいます。
コード値だけだと、何を意味するかが分かりづらいため、オプションセットをグラフに取り入れる時はコード値変換が必要になります。
Power Queryの編集画面でコード値を変換する数式列を入れます。
まずは、Power Queryの[列の追加]→[条件列]を選択。
条件式の定義が出てくるので、ここでコード値と表示させるラベルを対応させます。
列名は日本語でもOKです。
注意点を挙げるとすると、こちらで設定した変換ルールはPower Query内のルールとなるため、Dynamicsで設定したオプションセットとは独立した設定となります。
よって、Dynamics側でラベルを変更した場合は、都度Power Queryでのメンテナンスが入ることを留意しましょう。
＜追記＞
上記の方法以外にも、オプションセット値をラベルに変更する方法があるため、こちらも併せてご紹介します。
数式列を使用する
取得したデータセットはWebAPIで定義された内容以外に数式列を挿入することができます。
数式列内で自レコードの内容を参照することが可能なので、これを使ってコードをラベルに変換する方法です。
リードの状態（statecode）をラベルに変換する場合は以下のような手順で数式列を作成します。
フィールド欄から列を挿入したいエンティティを右クリックで選択し、[新しい列]を選択
以下のように数式を入力します。
レコードの内容を参照する場合は、「エンティティ名[フィールド名]」とします。
これで、変換されたラベルが列に追加されます。
もちろん、グラフなどに使用することも可能です。
マスタテーブルを使用してテーブルをマージ
複数エンティティで同じオプションセットを使用していた場合、上記の数式列を使用した場合は、各テーブルそれぞれに対して数式列を挿入する必要があります。
ここでもしラベルの変更が入った場合、複数テーブルに対して数式の修正が入るためメンテナンス性が悪いです。
そこで、もう一つの方法がマスタテーブルを作成する方法です。
データセット作成後、取り込んだデータとは別にマスターテーブルを手動で作成します。
[ホーム]タブの[データの入力]を選択。
オプションセットのコードと値の対応表となるマスタテーブルを作成。
（もしかしたらオプションセットの定義情報はWebAPIから持ってこれるかもしれませんが、まだ模索中です）
[リレーションシップの管理]を選択。
リードと先ほど定義したリード状態を関連付けます。
[leads]の[statecode]と[リード状態]の[コード]が紐づくので、以下のように両フィールドを選択し、カーディナリティを設定する。
このように、リレーションが生成されます。
こちらの方法でも数式列は作成します。
でも、変換の仕方は異なります。
リレーションが生成された状態で、leadsに新しい列を作成し、以下のようにRELATED数式を入力します。
数式の詳しい解説はコチラ。
これにより、関連するテーブルから項目を取得することができます。
こちらの方法の良いところは、コードとラベルの関係性がマスタテーブルに集約されているため、ラベルに変更があった場合はマスタテーブルを修正すればすべての変更が反映されることです。
各テーブルの数式をいちいち修正する必要がなくなります。
複数エンティティに跨って使用されるオプションセット（グローバルオプションセット）に対して有効です。
関連エンティティの取得
データセットでエンティティを取得するときは、自身のエンティティだけでなく、関連するエンティティも実は取得しています。
プレビューを見ると、エンティティの検索フィールドは、このように「Record」と表記されます。
そして、右上に両サイドへ展開するようなアイコンも表示されるため、これを押すことで、参照先のレコードを取得することができます。
アイコンをクリックすると、参照先レコードで表示する項目が選択できるため、表示したいものを選択しましょう。
所有者の名前を表示したいときはこのように選択します。
このように、所有者の名前が表示されました。
もちろんレポートにも使えます。
検索フィールドは親エンティティの特定のレコードを指しているため、一意に特定が可能ですが、子エンティティに対しても展開することができます。
ただし、子エンティティのレコードは複数レコードある可能性もあるため、レコードでの展開ではなく、テーブルでの集計となります。
関連する子エンティティを集計する場合は、データセットを取得した時に、リレーション名の列で「Table」と表記されています。
こちらも右上に展開のアイコンがありますので押してみると、
このような、集計項目の選択となります。
よくみると、「〜のカウント」となっています。
つまり、子エンティティのレコードの値を取得する訳ではなく、集計した値を取得することになります。
このように関連する子エンティティの集計値を取得することができますが、注意点として集計時に条件をつけることができません。
SQLでいうHAVINGのようなヤツですね。
不要な列とテーブルは削除しよう
データセットを作成する時、エンティティ内のフィールドはデフォルトで全項目取得されます。
実際に標準で入っているグラフ表示には不要なエンティティが含まれることが多く、Power Query側で過剰に取得してしまうためメモリを圧迫して動作が重いです。
これ、ホントに重たくてイライラします。
データセット作成後、まずレポートで表示しない項目は削除してしまいましょう。
使わないフィールドは削除しておきましょう。
また、前述したように、1エンティティだけを取得したつもりでも関連する親と子のエンティティも同時に取得してしまうことになるので、データセットに取り込むエンティティは最小限としておきましょう。
例えば、取引先会社と取引先担当者をレポート表示する場合、取得対象のエンティティを取引先担当者だけにして、取引先会社は検索フィールドからのレコード展開で取ってくるといったやり方になります。
コメント