Power BIを使い始めてもう4年目に入りました。プログラマではないわたしは、データソースはいつもCSVかEXCELなどファイルでした。
最近大きなソースデータを扱うことが多く、パフォーマンスや安定性の問題でいろいろ悩んでいました。ソースデータのファイルをSharepointに置いていたので、Sharepointが遅いのかと、DataFlow、Azure Lake Storageなど高速で有名なサービスも使ってみましたが、結果は変わらず、ついにインポートモード（データを最初にすべて取り込む方式）と惜別しなければいけない状態になったかと。
勇気を出してソースデータをRDBに配置しました。今回はAzure SQL DataBaseとオンプレミスのSQL Serverにソースデータを配置していろいろ確認してそのメリットを実際に体験しました。RDBにデータを登録するところは少々手間かかりますね、それは仕方ないです。
１．DirectQuery
Power BIのストレージモードにはインポートモード、Direct Query、デュアルモードの三種類があります。CSVなどはインポートモードでしか読み込めません。RDBはDirectQueryが使えます。Direct Queryはレポートのビジュアルが変更される度に必要なソースデータのみが取得されます。したがってPowerQueryによるデータ更新時には全体のデータを読み込む必要がなくなります。
さらにDirectQueryは都度読み込む方式ですから、ソースデータが変化する場合、リアルタイムでグラフが変更するレポートを作ることができます。わたしはここに着目しています。当然、インポートモードで読み込めるデータはグラフの表示に関してはそちらの方が速いのですが、ソースデータが度々変更される場合、インポートモードではいちいち全体のソースデータを読み込まないといけないのでリアルタイムでの表示は実現できません。
ただし、DirectQueryは単純なクエリしか書けません、複雑なことを記述するとエラーになります。実際に複雑な処理が必要な場合は、工夫が必要で、そこがクエリを作る人の腕の見せ所ではないかと思います。
２．SQLステートメント
RDBからデータを取得する際に、SQLステートメントを記載し、最初にSQLを実行して、RDB側でデータを絞り込んでPower BIにデータが送信することができます。これがとても便利なんです。DB側とPBI側で処理の分散が出来るので、パフォーマンスが上がります。joinとかgroupingはRDBに任せてしまえばいいのです。
３．クエリフォールディング
RDBに対してPower BIがアクセスする際はクエリフォールディングが自動的に効きますので、PowerQueryで列削除などを記述するとＤＢから取得されるものが自動的にフィルターされています。ソースデータを全部読み込んでいるわけではなく、パフォーマンスの向上になります。
４．増分更新
RDBにソースデータを配置すると増分更新が可能です。
すでにPower BIに登録したデータに変更がある場合は使えませんが、時系列で増加していくソースデータは全体を入れ替えるのではなく、増分のみ更新することが可能です。追記更新ですので、各段にデータの読み込み速度が上がります。
５．中間テーブルをSQLトリガーで作成更新（これはRDBの機能）
これまで集計用に、PowerQueryやDAXで作っていた中間テーブルをソースデータとしてあらかじめ作っておくことが簡単にできます。関係する指定のテーブルが更新されたらSQLのトリガー機能で関係するテーブルが自動的に作成されるように仕込んでおきます。これ手動で作ると整合性の問題など出てきて面倒ですし、神経使いますが、トリガー機能でその課題は解消できます。
そんなわけで1000万行以上の大きなソースデータを扱うときは、ソースデータをRDBに配置することが圧倒的に有利だとわかりました。
ただしわたしには乗り越えないいけないもう一つの壁がありました。
個人的な壁
わたしはプログラマではないのでSQLは意識的に避けてきました。でもついにSQLに手を出すことになりました。そうでないといちいちエンジニアに頭を下げて作業を依頼しないといけません。これではセルフサービスBIではないです。
5月の連休はどこに行けなかったので、「今さら」という感じですが、SQLの勉強をしました。
何冊か本を買ったのですが私の目的に合うものではなく、最後にアマゾンから届いたこの本がわたしをSQLに導いてくれました。筆者の「朝井淳」さんに感謝します、同じ名前です。
SQLはRDBによって方言がありますが、これをそれぞれ記載してくれているのが助かります。
[改訂第4版]SQLポケットリファレンス
そう、もう一つ壁がありました。SQLServerの管理ツールです「Microsoft SQL Server Management Studio 18」。これネットのドキュメント読みながら操作していては時間がかかりすぎるので、ここだけはセルフサービスあきらめて、スタッフに教えてもらいました。スタッフに感謝します。
0 件のコメント:
コメントを投稿