日付時刻を表す列は "日付" と "時刻" を表す列に分解したほうがよいですよというお話

4530 ワード
PowerBI PowerBI テキストリンク

パフォーマンスなんてまったく気にしないし、容量的な問題はすべて💰で解決するし。と、いうならそのままで構わない。

どういうことか

こういうこと

DateTime
2020-05-13 10:00:00
2020-05-13 10:15:00
2020-05-13 10:30:00
Date Time
2020-05-13 10:00:00
2020-05-13 10:15:00
2020-05-13 10:30:00

なぜか

大量データであっても速やかに処理されるようできるだけ整理をしてほしいから。
データモデルにデータがロードされるときイイ感じにデータを整理する仕組みを持っているのだけど、その管理単位は "列"。特に日付時刻の値は、テーブルの列の中でユニークな値となる傾向が高く、整理はされるもののその効果が低い。効果が低いのでデータモデルの容量が大きくなり大きなメモリを消費することになるし、結果的に集計のパフォーマンスが低下する傾向になる。

"yyyy-MM-dd hh-mm-ss" を "yyyy-MM-dd" と "hh-mm-ss" に分割し、それぞれの列で整理しておけば、行で 日付時刻を表現しつつデータを整理しておくことができる。

別の表現をするとこういうことなのだけど、

Date Time
2020-05-13 00:00:00 1899-12-30 10:00:00
2020-05-13 00:00:00 1899-12-30 10:15:00
2020-05-13 00:00:00 1899-12-30 10:30:00

整数部分 と 小数部分 を分けるということについては、超絶に行数が多いデータモデルで検討することがある手段ではある。だけど、日付時刻についてはタイム インテリジェンス関数が使えるようにもなるということでもある。

具体的に起きること

極端な例で
2020-01-01 00:00:00 から 2020-12-31 23:59:59 の連続した日付時刻を持つ、わずか 31,622,400 行
Table1 : 列 "DateTime" のみ
Table2 : 列 "Date" と "Time"

// RowCount
31622400 meta [
    IsParameterQuery=true,
    Type="Number",
    IsParameterQueryRequired=true
]

// TimeStampList
let
    Source = List.DateTimes(
        #datetime( 2020, 1, 1, 0, 0, 0 ),
        RowCount,
        #duration( 0, 0, 0, 1 )
    )
in
    Source

// Table1
let
    Source = Table.FromColumns( { TimeStampList }, { "DateTime" } ),
    ChangedType = Table.TransformColumnTypes(
        Source,
        {"DateTime", type datetime}
    )
in
    ChangedType

// Table2
let
    Source = Table.FromColumns(
        { TimeStampList, TimeStampList },
        { "Date", "Time" }
    ),
    ChangedType = Table.TransformColumnTypes(
        Source,
        {
            {"Date", type date},
            {"Time", type time}
        }
    )
in
    ChangedType

DAX Studio の Vertipaq Analyzer
DMV をポチポチと参照するのもよいけれども便利なツールを使う。

テーブルと各列が消費しているメモリサイズのざっくり見方、[Cardinality] は 列に含まれるユニーク値の数を示し、[Col Size] [Data] [Dictionary] [Hier Size] キャプチャでは切れてしまったけど、[User Hier Size] [Rel Size] との合計が [Table] になる。
Table1 : 列 "DateTime" のみでは 1.8 GB
Table2 : 列 "Date" と "Time" では 90 MB
と桁違いの消費になる。半分で済む容量の行数になったとしても消費しすぎと考えるべきなのでしょう。データモデルにはもっと列が含まれるはずだし。Table1 だけのデータセットストレージサイズ(おおむね pbix ファイルのサイズ)は300 MB とかになるはず。

検討しておきたいこと

  • 日付レベルの集計のみ必要ならば、日付時刻の値は "日付"( yyyy-MM-dd 00:00:00 と丸める)に。
  • 時刻によるの集計も必要になる場合、秒単位 / 分単位 / 時間単位など集計単位で丸める処理が必要
  • 丸める処理はロードされるデータに対し行われなければならない

思ったこと🙄

不用意に Power BI Pro で許容される最大データセットサイズ( 1 GB )の多くの占める可能性を考えたら必須に近いと思うのです。

その他