Power BI データセットの増分更新 / Incremental refresh で大事なこと

7578 ワード
PowerBI PowerQuery PowerBI テキストリンク

Power BI Pro ライセンスだけでも 増分更新 / Incremental refresh が利用できるようになったことだし、大事なことを確かめるのに都合がよい機能も出てきたので併せてまとめておきたい。設定方法とか使い方とかは難しくないのでなんか見てチャレンジどぞ。

Power BI データセットの増分更新とは

まずはそもそものところから。
ソースデータ(主にファクト テーブル) が多くの行を持つとき効果的な機能である。インポート モードの時、データセットの更新ではすべての行をロードしなおすことになり、更新完了までの時間が掛かるということが起こりえる。それが想定の範囲だったとしても、リソースの維持もしくは消費について検討しなければならなくなる。なので、変化のない過去のデータについてはデータセットに維持しつつ増分データのみを検知してデータセットに追加すればいいんじゃね？を実現する機能。Guy in a Cube など観ると使い方とかわかるはず。

新しいお話については、常に en-us の記事を読む。とても大事📢

データセット更新のタイムアウト

Scheduled refresh timeout
Scheduled refresh for imported datasets timeout after two hours. This timeout is increased to five hours for datasets in Premium workspaces. If you encounter this limit, consider reducing the size or complexity of your dataset, or consider breaking the dataset into smaller pieces.

2h でタイムアウト、Premium キャパシティで 5h。多くの場合ここまでいかないんですけどね、時間も掛けられないし、できるだけ確実な更新が望ましいとなったら 増分更新つかうとよいと思うのです。

大事なことは クエリ フォールディング

効いてる？

Power Query で特に重要な機能なのだけど、増分更新ではクエリ フォールディングが利用されることが前提になっている。前回のデータセット更新時以降に追加された増分データのみ取得することできるかどうかなのです。極端な話、クエリ フォールディング が有効になっていなくても増分更新は行われるはず。ただ、全行取得することになるのでメリットがほとんどなくなる。なので、きちんと確認をしておくべき。

確認しよう

実際のデータセット更新時にデータソースに対し問い合わせがあったクエリを観察するということも必要なのだろうけど、必ず確認できるということではないだろうから Power BI Desktop で確認する。
Premium キャパシティであれば パーティションがどうなっているかまでわかるけど Power BI Pro ライセンスだけではできないし、予め可能な確認はしておくべきですよね。慣れちゃえばやらなくてもよいけど。

クエリ診断 / Query Diagnostics
クエリ診断機能でクエリ フォールディングが有効であるかを確認するとよい。
ポイントは、予約されたふたつのパラメータ( "RangeStart", "RangeEnd" ) の値がクエリに利用されているかどうか。手順は、
クエリ診断をスタートして Power Query エディターは閉じない。

データセットを更新する。

クエリ診断を停止する。

増分更新を予定するクエリのクエリ フォールディングが無事できているか。
増分であることを検知する列を利用したフィルタのステップで、パラメータ値が折りたたまれた問い合わせに含まれているかどうかがとても大事。クエリ フォールディングが無効になってしまうステップ(マージなどPower Query 独自の処理)以降にフィルタを適用したときにはこのようにはならない。クエリ フォールディングが維持できているステップの中では順番を気にしなくてもよいことが多いから、真っ先にフィルタを適用してしまうこともありだと思う。

OData.Feed でも。ちょっと見にくいけど。

パラメータは DateTime.Type (Power Query)でなければならないのだけど、データソースの列をDateTime.Type に変更してクエリ フォールディングが終了してしまうなら パラメータ値を変換してデータソースに合わせる感じにするとよい。

PowerQuery
// RangeStart
#datetime(2020, 1, 1, 0, 0, 0)
    meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true]
// RangeEnd
#datetime(2020, 2, 1, 0, 0, 0)
    meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true]

// Orders
let
    Source = OData.Feed(
        "http://services.odata.org/V4/Northwind/Northwind.svc",
        null, [Implementation="2.0"]
    ),
    Orders_table = Source{[Name="Orders",Signature="table"]}[Data],
    FilteredRows = Table.SelectRows(
        Orders_table,
        each // データソースの列が DateTimeZone (UTC+0) だったので
                [OrderDate] >= DateTime.AddZone( RangeStart, 0 )
            and [OrderDate] <  DateTime.AddZone( RangeEnd, 0 )
            //     [OrderDate] >= DateTimeZone.SwitchZone( DateTime.AddZone( RangeStart, 9 ), 0 )
            // and [OrderDate] <  DateTimeZone.SwitchZone( DateTime.AddZone( RangeEnd, 9 ), 0 )
    )
in
    FilteredRows

あとは Power BI service におまかせ

Power BI Desktop で更新ポリシーを設定する。そして、Power BI service にアップロード。

Power BI Desktop は 最新のバージョンを
以前のバージョンではここで Premium キャパシティしか選択できなかったんですよね。

"RangeStart" / "RangeEnd" パラメータの値はスケジュール更新時に書き換えられ、Power BI Desktop で設定した増分データだけを取得する。タイムゾーンも考慮されるということなのできちんと設定する。

でも少しは気にかけて

Power BI Pro ラインセンスの範囲で試していたんだけど...

初回の更新は全行取得してパーティションの構成まで行うから時間が掛かるのはわかるのだけど、共有リソースでの処理だからちょっとムラがある感じもする。なので、可能な限り設定しておきましょうか。

思ったこと🙄

便利な機能ではあるけど、本当に必要？と検討してから使いたいねとは思う。
増分を検知する列は高いカーディナリティになりやすいから、ちょっと考えておかないとね。うっかりデータセットサイズの半分近く消費することもありそうかな。時刻部分を 00:00:00 に丸めるとか🙄 時刻でも集計するデータモデルならば "日付" と "時刻" は分けたい。
初回のデータセット更新でまるっと全行取得することになるはずだから、Power BI Desktop 作業中においては程よくフィルタしておいてもよいかなと。

Power BI Pro ライセンスのみでサポートされる最大データセットサイズは 1 GB です。

その他