👻

Power BIでCosmos DBの列が消える怪奇現象を解決した話

に公開
DB
Power BI
tech

はじめに

お疲れ様です！

Cosmos DBの生データにはあるはずの列が、Power BIで読み込むとなぜか表示されない...そんな経験ありませんか？😭

「あれ、DBのデータには確かにあるのに、Power BIだと列がないぞ？」と一人で混乱...。

今回は、そんな私がまんまとハマったPower BIの深い沼と、そこからの脱出劇をシェアします！💪

怪奇現象？DBとPower BIで見える世界が違う

今回のレポート作成では、「HogeHogeId（仮名）というIDを使って、データの種類別に件数を集計したい」と考えていました。

Cosmos DBの生データの世界 👀

  • 新しいデータには HogeHogeId フィールドが存在する
  • 古いデータには HogeHogeId フィールドが存在しない

私の見ていた世界（Power BIのクエリエディタ） 🤔

  • HogeHogeId という列が影も形もない

この認識のズレが、すべての混乱の始まりでした。😱

真犯人判明！Power BIの"賢すぎる"スキーマ推論 🕵️‍♀️

長い調査の末、ついに真犯人を突き止めました。

それは... Power BIのスキーマ推論機能 でした！

🚀 高速化のためのサンプリング

Power BIは、データを高速に読み込むために、データセットの先頭から約1000件だけをサンプリングします。

🤔 列構造を自動判断

そのサンプルデータを見て、「このテーブルにはこういう列があるんだな」と全体の構造（スキーマ）を自動で判断します。

😵 今回の悲劇

私のデータでは、たまたま先頭のレコード群が HogeHogeId を持たない古いデータでした。

💥 Power BIの誤判断

サンプルに HogeHogeId がないのを見たPower BIは「なるほど！このテーブルにHogeHogeId列は存在しないんだな！」と判断し、それ以降に登場するHogeHogeIdを持つ新しいデータをすべて無視していたのです！

これが、DBの生データとPower BIとで見えている世界が違った理由でした。

賢い機能が、時には仇となるんですね...。

脱出計画！根本解決への道のり 🗺️

原因がわかれば、あとは解決するのみ！

以下のステップでこの問題を完全に解決しました。

1. 接続コネクタの変更 (v2 → v1) 🔄

まず、Power BIの標準接続（v2コネクタ）では、初回接続時にSQLを流し込むのが難しいことが判明。

そこで、より柔軟な旧バージョンの「v1コネクタ」に切り替えました。

v1コネクタは、接続の初回段階でSQLクエリを直接実行できるのが神でした✨。

2. SQLクエリによるスキーマの固定化 💻

v1コネクタの機能を使って、Cosmos DB側でこんな感じのSQLクエリを実行させました。

-- HogeHogeIdがあればその値を、なければnull(空)を返すクエリ
SELECT
    c.id,
    c.age,
    -- ... 他の列 ...
    IS_DEFINED(c.HogeHogeId) ? c.HogeHogeId : null AS HogeHogeId
FROM c

このクエリのおかげで、全レコードに必ずHogeHogeId列が存在する状態になり、Power BIのスキーマ推論に「ここに列があるぞ！」と無理やり認識させることができました。

まさに力技！💪

3. 処理の共通関数化 🧙‍♀️

同じ問題を抱えるテーブルが複数あったので、この接続・データ取得処理を一つの再利用可能なカスタム関数 f_GetMyData にまとめました。

// M言語でこんな感じの関数を作成
(containerName as text) =>
let
    // ... v1コネクタでの接続＆SQL実行処理 ...
in
    結果テーブル

これを作ったおかげで、各テーブルのクエリはこんなにシンプルに！

// HogeHogeLogsテーブルのクエリ
let
    ソース = f_GetMyData("HogeHogeLogs")
in
    ソース

これでメンテナンス性も爆上がりです！🚀

4. 仕上げのデータクレンジング ✨

共通関数の中に、ついでにデータ整形処理も追加しました。

例えば、HogeHogeId が null の場合に規定のデフォルトIDを付与する処理です。

まとめ

長かった戦いをまとめると...

  • Power BIのスキーマ推論は、データ構造が不統一だと罠になることがある！
  • そんな時はv1コネクタとSQLクエリで、Power BIに列を強制認識させるのが有効！
  • 繰り返し処理はカスタム関数にまとめて、未来の自分を助けよう！

今回の件で、Power BIの挙動についてまた一つ詳しくなれました。
Happy Power BI-ing! 🎉

Discussion