はじめに

はじまして、CSIG（Cyber Security Innovation Group）の松本です。現在専門ドメインはセキュリティで、データのセマンティックに関心があります♡

今回は、データ分析ソフトウェアとして知られるBIツールについての入門記事です。PowerBI Desktopを活用して、データ取り込み～分析結果の可視化の一連の手順を紹介します。

これからPowerBIを試してみようと考えている方の導入を支援できれば幸いです！

PowerBI とは

Power BI（パワービーアイ）は、Microsoft社が提供しているビジネスインテリジェンス（BI）ツールです。企業が持つ様々なデータを収集・分析し、その結果を視覚的に分かりやすいレポートやダッシュボードとして作成することで、迅速な意思決定を支援する目的で作られました。PowerBIには以下3種類のプロダクトがあります。

1. PowerBI Desktop
   Microsoftが提供するビジネスインテリジェンスツール。データ分析、可視化、レポート作成に特化しており、様々なデータを元にインサイトを導出可能。
2. PowerBI Service
   レポートの共有・コラボレーション・スケジュール更新を含むPowerBIの共同利用に関するWebクラウドサービス。
3. PowerBI Automate
   Power BIの様々な管理タスクや操作を、手動ではなくPowerBI REST APIやPowerShellコマンドレットを利用して、PowerBIの管理や操作を自動化するためのツール。

この記事では、PowerBI Desktopを取り上げます。

具体的には、以下のようなことができます。

• データの接続と準備
  Excelファイル、データベース、クラウドサービスなど、様々な場所にあるデータに接続し、分析しやすいように整理・加工する
• データの可視化
  グラフや表、地図など、多彩なビジュアル表現を使って、データを分かりやすく表示）
• レポートとダッシュボードの作成
  分析結果をまとめたレポートや、複数のグラフを一覧できるダッシュボードを作成し、関係者と共有
• インタラクティブな操作
  作成したレポートやダッシュボードは、フィルターをかけたり、ドリルダウンしたりするなど、対話的に操作しながら深掘り分析を行うことが可能
• クラウドベースでの共有と共同作業
  Power BI Serviceを利用することで、作成したレポートやダッシュボードをクラウド上で安全に共有し、チームで共同作業を行うことが可能
• モバイル対応
  スマートフォンやタブレットからもレポートを閲覧できるため、場所を選ばずに最新のデータを確認可能

監査ログ自動化について

背景と目的

先日、PowerBIを用いて監査ログの一部自動化（実アクセスログデータと使用申請ログデータの突合）を実施し、以下を達成しました。

1. 取り込み先対象ファイルや時間軸含めたデータフローの可視化が同一ファイル内で実現できた
2. 属人性の排除に役立った
   コンサル的な視点から述べると、
3. 実務担当者の工数数日分の短縮を実現し、生産性の向上に役立った

完全自動化には別途PowerBI AutomateやRPAなどのオーケストレーション機能をもつツールの使用が必須ですが、今回は入門編ということでPowerBI Desktopのみで実現できる範囲での自動化についてお話しします。

データフロー図 (DFD: Data Flow Diagram)

今回はPCスペックの都合上、➀～➂の分割レポートとして作成しています。クレンジング処理や置換処理は主に➀で行い、突合処理は主に➂で行っております。

必要なPowerBIの知識

Microsoft公式が丁寧なサポートページを提供してくれているため、基本的にこちらを読んでいただければ理解できると思います。

• Power BI とは? - Power BI | Microsoft Learn

気を付けた方がよさそうなところは、MSが “レポート” と称しているものは、各ステップや加工処理が取り込まれた[.pbix]データそのもののことを指している場合と、最終的に出力されるレポートビューもしくはそこからエクスポートされるpdfなどを指している場合があり、文脈依存である点です。

データソースの豊富さ

データソースとして指定できる方法は以下の２タイプです。

1. File
2. Database

DirectQueryを直接使用する際に見るサイトは以下です。

• Power BI Desktop の DirectQuery - Power BI | Microsoft Learn

そして、データソースとして利用できるものは以下になります。

• Power BI Report Server での Power BI レポート データ ソース - Power BI | Microsoft Learn

かなりの数が使えるようになっています。特にsharepointあたりは、企業で導入しているところであればデータ管理にクラウドを別で立てる必要がないため要件次第ではありますが、よい連携になると思います。今回データソースとして取り込んだものは、[.csv] 形式です。

データ分析メイン処理

今回、PowerBIで実装した内容としては「生データのインポート → クレンジング処理 → 参照/置換/突合 処理 → ビジュアライゼーション」という一連のフローです。ここでは、メイン処理 “参照置換処理” + “参照突合処理” を掲載します。

使用言語

PowerBI Desktopで使用する言語は2種類です。

• M語: 主に、クレンジング処理などのデータの分析以前の準備に際し使用する言語
• DAX関数: 主に、BIツールのメイン処理に相当するデータ分析に際し使用する言語

M語

ノーコードと言われるゆえんはここにあり、M語で記載される各クエリはGUI上でステップという概念に置き換わり、反対に、GUI上でボタンをぽちぽちしてステップという概念を形作ると自動的にそれに対応するコードが作成され、[詳細エディタ] にM語として反映される仕組みになっています。どちらかをいじれば、双方に反映され、同期的に動きます。

下記は分割レポート➀の抜粋です。M語にて置換処理をしている箇所です。

let
    // --- old-windows フォルダ読み込みと基本加工 ---
    ソース = Folder.Files("C:\Users\xxxx\Downloads\xxx\old-windows"),

    ~~~ クレンジング処理（省略） ~~~

    // ★参照するテーブルBのクエリ名を指定してください
    Source_TableB = T_情シスメンバー一覧,

    // テーブルA (区切り記号の後の抽出されたテキスト) とテーブルBを account id でマージ
    MergedTables = Table.NestedJoin(
        #"名前が変更された列 2", // 左側のテーブル (テーブルA)
        {"account id"},     // テーブルAの結合キー列名
        Source_TableB,      // 右側のテーブル (テーブルB)
        {"account id"},     // テーブルBの結合キー列名
        "MatchedInfo_B",    // 結合結果を一時的に格納する列名 (任意)
        JoinKind.LeftOuter  // 結合の種類 (テーブルAの全行を残す)
    ),

    // マージ結果を展開して、テーブルBの user id 列を取得
    Expanded_UserId = Table.ExpandTableColumn(
        MergedTables,
        "MatchedInfo_B",      // 展開する一時列名
        {"user id"},          // テーブルBから取得したい列名 (今回は "user id")
        {"取得したUserID"}    // テーブルAに追加される新しい列名 (一時的な名前)
    ),

    // ★新しく追加した列の名前を "user id" に変更 (後続の処理で参照するため)
    Renamed_UserId_Column = Table.RenameColumns(Expanded_UserId, {{"取得したUserID", "user id"}}),

    ~~~ 省略 ~~~

    変更された型1 = Table.TransformColumnTypes(並べ替えられた列,{{"user id", type text}, {"source file", type text}, {"terminal access program", type text}})

in
    // ★最終的に出力するステップ名を指定
    変更された型1

DAX関数

そして次元を一つ引き上げて、データテーブル間を複数またいでデータの分析処理をするために各テーブルからデータを引き出して突合などを行う際にはDAX関数を使用します。

下記は分割サポート➂の抜粋です。突合項目3つを突合して条件に合致したらBooleanで値をテーブルに新規追加させています。返り値を実体としてを内部的に保持させることで、このあとフィルタリングさせることが可能となります。

True_False_Filtering_Table =
ADDCOLUMNS (
    'allserver-logs',
    "IsMatchingSmartDBLog", // 新しい列名 (この監査ログに対応する申請ログがあるか)

    // 1. 現在の 'allserver-logs' の行の値を取得 (計算列の行コンテキスト)
    VAR currentServerLog_UserId = 'allserver-logs'[user id]
    VAR currentServerLog_ServerName = 'allserver-logs'[server name]
    VAR currentServerLog_Timestamp = 'allserver-logs'[timestamp]

    // 2. 現在の行の値が有効か確認 (元のメジャーの isSingleServerLogRowContext に相当)
    VAR isCurrentRowContextValid =
        NOT ISBLANK( currentServerLog_UserId ) &&
        NOT ISBLANK( currentServerLog_ServerName ) &&
        NOT ISBLANK( currentServerLog_Timestamp )

    // 3. 条件に合致する 'allsmartdb-logs' (Web申請ログ) の行数をカウント
    VAR MatchingSmartDbLogsCount =
        IF (
            isCurrentRowContextValid, // 有効な行コンテキストの場合のみカウント
            COUNTROWS (
                FILTER (
                    'allsmartdb-logs', // Web申請ログテーブルを検索対象とする
                    'allsmartdb-logs'[user id] = currentServerLog_UserId &&
                    'allsmartdb-logs'[server name] = currentServerLog_ServerName &&
                    'allsmartdb-logs'[timestamp_start] <= currentServerLog_Timestamp &&
                    'allsmartdb-logs'[timestamp_end] >= currentServerLog_Timestamp
                )
            ),
            0 // 無効な行コンテキストの場合はカウント0 (結果としてBLANK()につながる)
        )

    // 4. 結果を返す
    RETURN
        IF (
            isCurrentRowContextValid, // 有効な行コンテキストの場合のみ TRUE/FALSE を返す
            IF ( MatchingSmartDbLogsCount > 0, TRUE(), FALSE() ), // 合致あればTRUE
            BLANK () // 無効な行コンテキストの場合は BLANK() を返す
        )
)

ビジュアライゼーション

実際にPowerBI Desktop上で見られる最終的な画面は下記レポートビューになります。スペック次第ではありますが、こちらの画面はリアルタイムでカスタマイズできるため、今後を見越して閲覧可能性のあるデータをこの出力手前の時点まで過不足なく保持しておくと、のちのち見たいデータを反映させたくなったときにモデル改修が不要になります。

• 上青枠） データテーブルの右上部から [データのエクスポート] を選択すると、[csv形式] でファイルをエクスポートすることが可能です。
• 下青枠） 出力されたデータテーブルにの各カラムに対しフィルタリングが可能です。

PowerBI Serviceとの連携

※今回は連携しない方向になりましたのでこちらを実装していませんが、完全自動実行にあたりトリガーなども自動プッシュしたい際には下記の On-premis Gateway というソフトウェアとの連携が必須となるため紹介だけしておきます。

• オンプレミスのデータ ゲートウェイ - Power BI | Microsoft Learn

オンプレミスサーバとPower BI間のデータ転送をセキュリティで保護するには、On-premis Gatewayが必要です。VPNのような役割を担っていると考えてもらってよいと思います。

最後に

PowerBIによる監査ログ自動化に関する取り組みと、その前提となる基礎知識や専門用語についてご紹介しました。

PowerBIは、直感的で使いやすいインターフェースが提供されたBIツールで初心者でもすぐに使いこなすことが可能です。しかし、使いこなす上では “データアーキテクト” の知見が必要だと感じました。初心者においてはデータの構成や構造についてはすでに設計構築が完了している前提で、その後設計書に基づいて組み上げる際に有用なツールではないでしょうか。

今回のような監査ログなど大量のデータを加工したり突合したりという処理を含めると、PowerBI特有のM語とDAX関数の他、特に実用的な「設計」を行う上では、データアーキテクチャに関する知識は非常に重要であると言えます。知識が浅いと、パフォーマンス問題、メンテナンス性の低下、誤った分析結果などに繋がる可能性があります。

またPowerBIそれ自体は、以下の点からプログラミング初学者が勉強するには有用なソフトウェアだとも感じております。

1. M語やDAX関数というプログラミング言語を実際に触ることができる
   命名方法や関数のスコープの切り方を学べる（リーダブルコード的な観点）
2. CLIとGLIを相互比較しながら挙動を確認することができる
3. 常にデータ型を意識する練習になる
4. データアーキテクト設計能力を養う練習になる
   PowerBI上でも、大規模データや複雑な変換ロジックを扱う場合、データアーキテクチャの理解がボトルネックの特定やパフォーマンス改善に役立つことがあります。

ぜひ今回の記事を参考にしていただきPowerBIの理解やご自身の勉強に役立てていただくきっかけとなれば幸いです :)