前回と少し重複する箇所があるかもしれませんが、今回はさらに具体例を交えながらモデリングのベストプラクティスについて探っていきたいと考えています（一部は重要な補足も含まれています）。
重要な基礎項目
- 列削除
これを行うだけでセマンティックモデルのデータサイズの圧縮に貢献できる - 計算列の使用
10万、20万行の場合は問題ないが、億単位の行がある場合、計算列は良いアイデアではない -
ユニーク値が多い列に注意
行数が多いのは構わない。しかし、ユニーク値が多い列があると留意が必要。例えば、1列に3,000万行もユニーク値があった場合、Power BI セマンティックモデルのリレーションシップのコストが非常に高くなってしまう。ここで重要なことは、データの分布についてDBAではなく、モデリングを行う人に質問すること。 - リレーションシップや階層にはコストが存在する
Power BIの分析エンジンはメモリ上に追加のストラクチャを作ることになり、メモリを消費する。MDXクエリを最適化するためのストラクチャーがメモリ上に作られる（DAXは使用しない）。IsAvailableInMDXをFalseにし、リレーションをむやみに多く作らないこと（スタースキーマはGood、スノーフレークはNot so good＝リレーションシップの数が多いためコストが高くなる）
データモデルの確認
- メモリのアロケーション
メモリのアロケーションに関する分布をDAX StudioのVertipaq Analyzerで確認する（どのテーブルのどの列にメモリが割り当てられているか）
例えば、下図では、SalesテーブルとInventoryテーブルに約9割のメモリが割り当てられていることを確認できる。
DAX Studioの使い方については以下を参照（13頁）。
- リレーションシップと階層に対するメモリアロケーションが高いかどうかを確認
下図より、どちらもそれほど大きいものではないことが分かる。
- 日付テーブルを作る
前回の通り - DateTime列からDateとTimeを別々の列に分解する
こちらの記事にある通り、モデルサイズや効率的にレポートを作成するためにはモデリングを工夫する必要がある
全てに共通することですが、スケーラビリティとユーザビリティの両方を考慮することが重要となります。
DAXの最適化
データ量が膨大であるか、多数のユーザーが存在する場合、DAXの最適化が必要となります。いくつか重要なコンセプトを理解する必要があります。
- セマンティックモデル（Tabular Model）の概念
Power BIのセマンティックモデル（主にDAX）は、アグリゲーション（多次元モデルのように、事前に計算してクエリのパフォーマンスを向上させ、計算負荷を軽減する機能）概念を持っておらず、インデックス（最適化用）も持たない。Tabular Modelはメモリにインジェストされたデータをスキャンするだけ。 - エンジンによって処理されるジョブはクエリプランに左右される
すなわち、クエリプランが良いモデルはパフォーマンスも良いことになる。SQLサーバーのように、クエリプランを最適化するための長い歴史が存在しており、これをDAXでも同じように考えることが重要となる（DAX is SQL on Steriod） - DAXの書き方一つでパフォーマンスが大きく異なる
柔軟性が高いがゆえに、DAXコードを最適化する必要がある。DAXを極める必要がある場合、エンジンの動きについて学ぶ必要があるし、クエリプランの読解（エンジンから提供される情報の読解）を身につけられるとベスト。DAX Studioでこれを行うことが可能であるが、上級者向けのコンテンツとなる - DirectQueryの場合、DAXは全てSQLクエリに翻訳されて実行されるため、SQL側の最適化を行うことが重要となる。DirectQueryのベストプラクティスはChris Webbの動画が分かりやすい
- FE (Formula Engine) and SE (Storage Engine): 参考記事（SQLBI）
- DAX最適化の考え方
- FEの使用を抑えることが肝要。FEの時間が長い場合、Physical Query Planを見てどの程度の行に対してスキャンを行ったかを調べる。テーブル全体の行数に対して多くの行に対するスキャンが発生した場合、クロスジョインが発生していることを意味するため、ボトルネットを特定しやすい
- SEを最適化してもFEで時間が掛かり、時間短縮が難しい場合はレポートキャッシュを利用する手もある。キャッシュに関する考え方はGuy in a CubeのMarco先生の説明が分かりやすい。
- データモデルの最適化
時にデータ量が多くSEとFEの構成でSEが99%、CPU 時間が30秒、Total 時間が4秒（SE CPUが x7.6（7.6倍の速度で並列処理） = 30秒 ÷ 3.96秒（4秒の99%））程度の場合、DAXではなく、データモデルを変更する必要が出てくる。なぜなら、DAXコードはこれ以上、最適化が困難な状態となっているため。データモデルの圧縮効率を変化させるなど、別のアプローチが必要になってくる。また、列ごとにカーディナリティが異なるため、カーディナリティの高い列に対する処理はそうでない列（モデル）に対して、同じ行数であっても時間がかかる掛かることになる
スケール戦略
最適化をしてもでもダメな場合、スケールアップ（高いSKUを購入）やスケールアウト（多くの低いSKUを購入）することを考えていくことになります。
- データサイズについて確認・施策
※マシーンスペックによって異なるため、下記はあくまで目安- 1千万行: 問題なし
- 1億行: モデル・DAXの最適化
- 10億行: 圧縮の最適化及びセグメントサイズのチェック
これだけのデータ量だと、保存するたびにGB単位でメモリを消費する。メモリの節約が必要であり、セグメントサイズを800万行（Power BI Large Semantic Model Storage Formatの場合）から3,200万行や6,400万行に増やすことで、高いパフォーマンスの最適化を実現できる場合がある - 100億行: 異なる粒度で集計（アグリゲーション)することを考える。また、例えばDim顧客[顧客Key] - Fact売上[顧客Key]というリレーションシップがあり、DimCustomerが3,000万行であった場合、敢えて2行だけ存在するDim性別というDimensionテーブルを作り、Dim性別[性別] - Fact売上[性別] というリレーションシップを構築しなおすことで、パフォーマンスを著しく改善できる可能性がある。この場合、Dim性別[性別]をよく使用する場合、検討に値するやり方となる。なお、このレベルのデータになると、ドリルダウンですぐに結果を見るのが難しくなる（スキャンするだけで数秒必要な場合がある）。
- 1,000億行～: 行数を減らす努力をしたほうが良い。Power BI Desktopで処理できないボリュームのデータになっている可能性があり、データを保存するRAMが足りない可能性が高い。Power BIサービス側でサードパーティツールを活用した開発になるが、Microsoft FabricのOneLakeを活用する手がある
-
-
クエリパフォーマンスが悪い時
-
大容量モデルを取り扱う必要がある時
-
クエリのレイテンシーが見られる時
-
同時接続ユーザー数が多い時
スケールアップ・アウトする場合
-
なお、現在はFabric容量となっていますが、Power BIの観点からGuy in a Cubeの容量に対する考え方が非常に役に立ちます。
- Amount of Datasets -データセットが何個あるか？
- Dataset Composition - データサイズ、列数、行数は？
- Model Schema -スタースキーマになっているかどうか？
- Peak Performance Optimization -パフォーマンス最適化されているか？
- RLS - Row Level Securityを使っているかどうか？
- Amount of Visuals - レポートにあるレポートが多すぎていないか？
- Concurrent Users - 同時接続ユーザー数が何人か？
- Dataflows - データフローを使っているかどうか？
等について、押さえておくと良いポイントが多く解説されています。
ディメンションテーブルのボトルネック
-
ひと昔（Power BIが登場した当初）は100万レコード以上のユニークレコードがあるDimテーブルは、計算を遅くしてしまうが、現在はハードウェアのスペックが上がってきているので、数百万行でも問題なくレポートを操作可能
- 上述の通り、よく使用する属性（列）で低いカーディナリティのDimを作り、それをFactとリレーションシップを作ることでレポート操作時のパフォーマンス改善に繋がる
ハードウェアセレクション
Power BI Desktopを使う場合に考慮が必要となりますが、下記参照頂きたい。
- CPU L2、L3キャッシュが大きいPCを選ぶことが重要
-
列を何度もスキャンする必要がある場合、RAMよりL3キャッシュの方が重要となる。例えば、1～200万行のユニーク顧客がある場合、ジョインによって生成される顧客リストを何度もイテレート（反復）スキャンすることは稀ではない。このデータをL3に一時的に格納できると、RAMよりも数倍早くなるため、L3キャッシュが可能な限り大きいPCを選択することが重要。
- 総CPUパワーよりもクロック周波数の方が重要。処理の一部は常にFEで行われ、シングルスレッドとなっていることが理由。コアが少なく、高いクロック周波数が良いものとなる。それゆえ、実はゲーミングPCがPower BIをローカルで使用する際に最もパフォーマンスを発揮すると言われている
- バーチャルマシーン（VM）
VMはそれ自体悪くなく、Allocated RAMを更新できる。搭載される物理的なマシーンに影響される（RAMやCPUがスペックが低いなど）
最後に
前回の記事の補足となっていますが、今回はもう少し細かいところの話をしました。これ以外にも最適化テクニックはありますが、前回と合わせてこれだけ把握しておけば概念的な部分も分かるようになるでしょう。最重要ポイントは以下3つ。
- データモデルを最適化
メモリ使用量を減らすことができれば、様々なところでパフォーマンスが改善される - DAXを最適化
レスポンス時間が改善され、スケーラビリティにも貢献できる - 正しいハードウェアの選択（ローカルマシーン限定）
PC等のハードウェアではストレージではなく、CPUとRAMにお金を掛ける
