PleasanterとPower BIをPowerQueryで連携する方法

はじめに

Pleasanterでは保存された各データは一覧画面、カレンダー、クロス集計など様々な方法で表示させることができますが、これらの表示方法では満たされない要件で集計をしたい場合や、リンクしていない別のサイトやPleasanter以外のデータと連携してデータを分析したいケースなども多くあるかと思います。
そのようなケースに対応する手段してBIツールを使う方法があります。この記事では、MicrosoftのPower BIとPleasanterを連携させる例をご紹介します。

連携イメージ

サンプルデータ

サンプルデータとして、国土交通省で公開されている行政区域の情報を利用します。このデータは、都道府県、市区町村、大字町丁目の情報に加えて、各丁目ごとの緯度・経度の位置情報を含んでいます。

このデータを 都道府県、市区町村、大字町丁目の3つのサイトに保存し、各IDをもとにリンク関係を持った状態でPleasanterに作成します。

例えば、大字町丁目のサイトは以下のように取り込んでいます。

Pleasanter上でのサイト設定の説明は割愛します。

項目の表示名について

Pleasanterは各サイト毎に項目の表示名を自由に設定できますが、データベースのテーブルの列名は、項目の属性ごとに汎用的な名称が設定されています。例えば、分類項目を表す列はClassA、ClassB…と続き、数値を表す列はNumA、NumB…と続きます。外部に連携した場合には、この列名とサイトに設定した名称を一致させる必要があります。

この問題の解決策の一つとして、以下の記事にあるように、サイト設定をもとにデータベースへ表示名を列名に反映したビューを作成する手法があります。

上記のビューを作成する方法の利点としては、一度データベースにビューを作成してしまえば、以降はシンプルなテーブルのように扱えることです。一方で、サイトの数だけビューを作成することになり、サイト数が多い場合や頻繁にサイト構造を変化させていく使い方の場合は管理が煩雑となります。

この記事では、別の解決策として、Power BIのデータ取得方法の一つである Power Query（M言語）を使用し、APIを経由したデータ取得の段階でこのサイトごとの項目の名前解決を一括して行います。

Power Queryで使用するM言語のクエリ

はじめに、本記事の主題となるPower Queryで使用するM言語のクエリを提示します。

基本的には後述の手順でPower BI上のPower Queryエディタにこのクエリを貼り付け、冒頭の4つの変数を各自の環境と取得したいサイトに合わせて書き換えるだけで動作します。

let
    // サーバー名、APIキー、サイトID、ページサイズの定義
    serverUrl = "http://localhost",
    apiKey = "XXXXXXXXXX...",
    siteID = "1000",
    pageSize = 200,

        // サイト情報を取得する関数を定義
    getSiteInfo = (siteID as text) as record =>
        let
            // APIのURLを設定
            url = serverUrl & "/api/items/" & siteID & "/getsite",
            // APIリクエストを送信してサイト情報を取得
            response = Web.Contents(url, [
                Headers = [#"Content-Type" = "application/json"],
                Content = Json.FromValue([ApiVersion = "1.1", ApiKey = apiKey])
            ]),
            // レスポンスをJSONとして読み込む
            jsonResponse = Json.Document(response),
            // サイト情報を抽出
            siteInfo = jsonResponse[Response][Data]
        in
            siteInfo,

    // サイト情報を取得
    siteInfo = getSiteInfo(siteID),
    // サイト名を取得
    siteTitle = siteInfo[Title],
    // 列名のペアを取得
    columns = siteInfo[SiteSettings][Columns],
    columnNames = List.Transform(columns, each [ColumnName]),
    labelTexts = List.Transform(columns, each try [LabelText] otherwise [ColumnName]),

    // ページごとのデータを取得する関数を定義
    getPage = (offset as number) as record =>
        let
            // APIのURLを設定
            url = serverUrl & "/api/items/" & siteID & "/get",
            // APIリクエストを送信してページごとのデータを取得
            response = Web.Contents(url, [
                Headers = [#"Content-Type" = "application/json"],
                Content = Json.FromValue([ApiVersion = "1.1", ApiKey = apiKey, Offset = offset])
            ]),
            // レスポンスをJSONとして読み込む
            jsonResponse = Json.Document(response),
            // データ部分を抽出
            data = jsonResponse[Response][Data],
            totalCount = jsonResponse[Response][TotalCount]
        in
            [Data = data, TotalCount = totalCount],

    // 初期オフセットを設定
    offset = 0,
    // 初期ページを取得
    firstPage = getPage(offset),
    // 総レコード数を取得
    totalCount = firstPage[TotalCount],
    // すべてのページを取得するためのリストを作成
    pageOffsets = List.Generate(
        () => offset,
        each _ < totalCount,
        each _ + pageSize
    ),
    // すべてのページを取得して結合
    allPages = List.Transform(pageOffsets, each getPage(_)[Data]),
    combinedTable = Table.Combine(List.Transform(allPages, each Table.FromList(_, Splitter.SplitByNothing(), null, null, ExtraValues.Error))),

    // ClassHashなどのHashを展開する関数を定義
    expandHashColumns = (table as table, columnName as text, fields as list) as table =>
        let
            // 指定されたカラムが存在する場合のみ展開
            expandedTable = if Table.HasColumns(table, {columnName}) then Table.ExpandRecordColumn(table, columnName, fields, fields) else table
        in
            expandedTable,

    // 必要なカラムを展開（ReferenceTypeがIssuesの場合とResultsの場合で分岐）
    expandedTable = if siteInfo[ReferenceType] = "Issues" then
        Table.ExpandRecordColumn(combinedTable, "Column1", {
            "SiteId", "UpdatedTime", "IssueId", "Ver", "Title", "Body", "Status", "Manager", "Owner", "Locked", "Comments", "Creator", "Updator", "CreatedTime", "ItemTitle", "ApiVersion", "ClassHash", "NumHash", "DateHash", "DescriptionHash", "CheckHash", "AttachmentsHash", "StartTime", "CompletionTime", "WorkValue", "ProgressRate"
        }, {
            "SiteId", "UpdatedTime", "IssueId", "Ver", "Title", "Body", "Status", "Manager", "Owner", "Locked", "Comments", "Creator", "Updator", "CreatedTime", "ItemTitle", "ApiVersion", "ClassHash", "NumHash", "DateHash", "DescriptionHash", "CheckHash", "AttachmentsHash", "StartTime", "CompletionTime", "WorkValue", "ProgressRate"
        })
    else
        Table.ExpandRecordColumn(combinedTable, "Column1", {
            "SiteId", "UpdatedTime", "ResultId", "Ver", "Title", "Body", "Status", "Manager", "Owner", "Locked", "Comments", "Creator", "Updator", "CreatedTime", "ItemTitle", "ApiVersion", "ClassHash", "NumHash", "DateHash", "DescriptionHash", "CheckHash", "AttachmentsHash"
        }, {
            "SiteId", "UpdatedTime", "ResultId", "Ver", "Title", "Body", "Status", "Manager", "Owner", "Locked", "Comments", "Creator", "Updator", "CreatedTime", "ItemTitle", "ApiVersion", "ClassHash", "NumHash", "DateHash", "DescriptionHash", "CheckHash", "AttachmentsHash"
        }),

    // ClassHashを展開
    classFields = List.Transform({"A".."Z"}, each "Class" & _),
    expandedClassHash = expandHashColumns(expandedTable, "ClassHash", classFields),

    // NumHashを展開
    numFields = List.Transform({"A".."Z"}, each "Num" & _),
    expandedNumHash = expandHashColumns(expandedClassHash, "NumHash", numFields),

    // DateHashを展開
    dateFields = List.Transform({"A".."Z"}, each "Date" & _),
    expandedDateHash = expandHashColumns(expandedNumHash, "DateHash", dateFields),

    // DescriptionHashを展開
    descriptionFields = List.Transform({"A".."Z"}, each "Description" & _),
    expandedDescriptionHash = expandHashColumns(expandedDateHash, "DescriptionHash", descriptionFields),

    // CheckHashを展開
    checkFields = List.Transform({"A".."Z"}, each "Check" & _),
    expandedCheckHash = expandHashColumns(expandedDescriptionHash, "CheckHash", checkFields),

    // AttachmentsHashを展開
    attachmentsFields = List.Transform({"A".."Z"}, each "Attachments" & _),
    expandedAttachmentsHash = expandHashColumns(expandedCheckHash, "AttachmentsHash", attachmentsFields),

    // 列の型を定義
    typedColumns = Table.TransformColumnTypes(expandedAttachmentsHash, 
        List.Transform(List.Select(dateFields, each Table.HasColumns(expandedAttachmentsHash, {_})), each {_, type datetime}) & 
        List.Transform(List.Select(numFields, each Table.HasColumns(expandedAttachmentsHash, {_})), each {_, type number})
    ),

    // ユーザー定義の表示名を置換
    userDefinedColumns = Table.RenameColumns(typedColumns, List.Zip({columnNames, labelTexts})),

    // システム標準の表示名を置換
    systemDefaultLabels = {
        {"SiteId", "サイトID"}, {"IssueId", "ID"}, {"ResultId", "ID"}, {"Ver", "バージョン"}, {"Title", "タイトル"}, {"ItemTitle", "結合タイトル"}, {"Body", "内容"}, {"StartTime", "開始"}, {"CompletionTime", "完了"}, {"WorkValue", "作業量"}, {"ProgressRate", "進捗率"}, {"Status", "状況"}, {"Manager", "管理者"}, {"Owner", "担当者"}, {"Comments", "コメント"}, {"CreatedTime", "作成日時"}, {"UpdatedTime", "更新日時"}, {"Creator", "作成者"}, {"Updator", "更新者"}
    },
    systemDefinedColumns = List.Accumulate(systemDefaultLabels, userDefinedColumns, (state, current) =>
        if Table.HasColumns(state, {current{0}}) then Table.RenameColumns(state, {current}) else state
    ),

    // データが一件もない列を削除
    nonEmptyColumns = Table.SelectColumns(systemDefinedColumns, List.Select(Table.ColumnNames(systemDefinedColumns), each List.NonNullCount(List.RemoveNulls(Table.Column(systemDefinedColumns, _))) > 0)),

    // 最終的なテーブル名を設定
    outputTable = nonEmptyColumns
in
    outputTable meta [Name = siteTitle]

クエリの解説

PleasanterのAPIを実行すると、レスポンスとしてJSON形式の文字列が返されます。これをPower BIでデータとして利用するためには、JSONの分解、Class等のHash展開、表示名への置換、データ型の定義など、データクリーニングやデータ整形の様々な処理が必要になります。

このクエリではこれらの作業を一括で自動化するようにしています。これにより、手動でのデータクリーニングや整形の手間を省き、分析やレポート作成に集中することができます。

以下、クエリのポイントとなる処理を解説します。

接続先情報の設定

クエリの冒頭では Pleasanterのサーバー名、APIキー、取得対象となるサイトID、APIで取得するページサイズを変数として設定します。本クエリのなかで利用者が修正を加えるのはこの4項目のみです。

この例ではPower Queryの変数としてPleasanterのAPIキーを直接記述しています。このクエリを含むファイルを他人と共有する場合は注意が必要です。

未検証ですが Azure KeyvaultへのAPIキーの格納などよりセキュアにする方法もあるようです。また、Pleasanter側でキーを払い出す際は、管理者アカウントの権限ではなく、APIの取得用に読み取り専用で参照先を絞った専用ユーザのキーを利用してください。

ページサイズは、パラメータ設定 Api.json で設定されている値と一致させます。

サイトの設定情報取得

次に、指定されたサイトIDをもとにサイトの情報を取得します。Pleasanterのサイト情報は、以下の サイト取得（getsite）で取得が可能です。

サイト情報から項目名とそこに設定された表示名を取得し、後続の処理で使用する項目名のリストを作成します。

サイトのレコードデータ取得

指定されたオフセットからデータを取得する関数を定義し、ページごとにデータを取得します。Pleasanterのレコードデータは、以下の 複数レコード取得（get）で取得が可能です。

PleasanterのAPIはページネーションをサポートしているため、この関数を使用して複数ページにわたるデータを取得します。

Hash列の展開

上記で取得されたJSONデータには、分類や数値などの属性ごとのデータがそれぞれ ClassHash、NumHash などにネストされて格納されており、これらを展開する必要があります。また、サイトの利用状況によってHashに含まれる項目数が変化するため、この仕様を考慮した展開を行います。

さらに、期限付きテーブル（Issues）と記録テーブル（Results）では、日付などの基本項目が異なるため、先に取得したサイト情報のReferenceTypeに基づいて、展開操作を分岐して行います。

列の型を定義

APIから取得したJSONデータは、そのまま読み込むとすべてテキスト文字列として型定義されてしまいます。これを、Dataとサイト標準の日付項目は日付型へ、Numは数値型へと自動で変換します。

列名の置換

最初にサイト設定から取得したユーザが定義した表示名をもとにテーブルの列名を置換します。これに続けて、Pleasanterのシステム標準の表示名をもとに置換します。

クエリ内で利用している M言語の詳細については、公式のマニュアルをご確認ください。

Power BIでの設定方法

クエリの設定

前項で提示したクエリをPower BIで設定する方法を説明します。

• Power BI Desktop で 空のレポート を作成

• データを取得 -> 空のクエリ を選択

  

• Power Query エディタ -> 空のクエリを右クリック -> 詳細エディタを開く

  

• 詳細エディタにクエリを貼り付け、冒頭の変数内を書き換え -> 完了

  

※環境によっては、ここでプライバシーレベルの選択が必要となる場合があります。

プライバシーレベルの設定内容については、以下のドキュメントを参考に、各環境にあわせて設定してください。

取り込みが成功すると、以下のようにテーブルとしてデータが取り込まれます。

以上の設定を、必要なサイトの数だけ実施します。

各サイトデータの管理者、担当者項目やユーザテーブルにリンクしたデータはユーザIDが格納されており、名前などはユーザテーブルから取得します。ユーザテーブルからのデータの取得は、文末に 参考情報 として記載しました。

その他、Power BIでの設定については以下のマニュアルをご確認ください。

モデルビューの設定

前項で読み込んだ各テーブルをClassとIDの値によってリレーションを作成します。

例として、都道府県と市区町村のテーブルは以下のようにリレーションを貼ります。

Power BIの詳細な操作手順は割愛します。以下のドキュメントを確認してください。

最終的な完成品のイメージ

ここまでの操作により、Pleasanterの3つのサイトをPower BIに取り込み、サンプルとして以下のようなダッシュボードを作成しました。

Pleasanterの 都道府県、市区町村、大字町丁目の3つのサイトを連携し、フィルタで絞り込んだ結果を地図にマッピングしているデモンストレーションです。

まとめ

以上のような手順で、PleasanterのデータをPower BIへ取り込むことができます。後述のような制限事項はありますが、サイトごとに全て手作業で取り込む作業を繰り返す場合と比較すると、圧倒的に楽に取り込みが実現できるかと思ます。

---

制限事項

本記事で提示したクエリは、以下の制限があります。

1. 画面の表示名を一切変更していないすべてデフォルトのままのサイトではデータが正しく取得できません
2. 一つのサイト内で項目の表示名が同一の項目が複数ある場合にはエラーとなります
3. 分類項目の選択肢のうち、値と表示名をカンマで区切った記法、wikiをマスタとする記法には未対応です（※詳細は後述）

制限事項 3.についての解説

Pleasanterの分類項目には、直接文字列を自由入力で記入する、他サイトのリンクを作成して選択肢とする設定のほか、サイト設定の選択肢に直接選択肢を記述する方法があります。その場合、以下の様な複数の記述方法があります。

分類項目の選択肢一覧 記述例1

要件定義
設計
構築
テスト
リリース・展開
初期サポート
運用

分類項目の選択肢一覧 記述例2

AC7459,製品A
UW9336,製品B
PX1223,製品C

分類項目の選択肢一覧 記述例3

100,未着手,未,status-new
150,準備,準,status-preparation
200,実施中,実,status-inprogress
300,レビュー,レ,status-review
900,完了,完,status-closed
910,保留,留,status-rejected

本記事でのクエリではこの記法の違いを吸収できず、記述例2 と 記述例3 のように格納された値(キー値)と表示名が異なる場合のケースには対応できていません。
PleasanterのAPI取得の方法には、ViewのApiDataTypeプロパティを"KeyValues"と指定することで表示名を取得する方法もありますが、他サイトを取り込んだテーブルとリレーションを貼る場合はIDを保持したほうが好都合のため、この方法による表示名での取得は未対応としました。
また、同じ理由により、Wikiを選択肢一覧のマスタとする方法にも対応していません。

それぞれ、分類項目のデータに対応したCSVやExcelファイルを作成して取り込むか、以下の記事のようにPower BI上で直接テーブルを作成し、それらとリレーションを作成してください。

参考情報

ユーザテーブルを取得するクエリ

let
    // サーバー名、APIキーの定義
    serverUrl = "http://localhost",
    apiKey = "XXXXXXXXXX...",
    apiVersion = "1.1",
    pageSize = 200,

    // ユーザデータを取得する関数を定義
    getUserData = (offset as number) as record =>
        let
            // APIのURLを設定
            url = serverUrl & "/api/users/get",
            // APIリクエストを送信してユーザデータを取得
            response = Web.Contents(url, [
                Headers = [#"Content-Type" = "application/json"],
                Content = Json.FromValue([ApiVersion = apiVersion, ApiKey = apiKey, Offset = offset])
            ]),
            // レスポンスをJSONとして読み込む
            jsonResponse = Json.Document(response),
            // ユーザデータを抽出
            userData = jsonResponse[Response][Data],
            totalCount = jsonResponse[Response][TotalCount]
        in
            [Data = userData, TotalCount = totalCount],

    // 初期オフセットを設定
    offset = 0,
    // 初期ページを取得
    firstPage = getUserData(offset),
    // 総レコード数を取得
    totalCount = firstPage[TotalCount],
    // すべてのページを取得するためのリストを作成
    pageOffsets = List.Generate(
        () => offset,
        each _ < totalCount,
        each _ + pageSize
    ),
    // すべてのページを取得して結合
    allPages = List.Transform(pageOffsets, each getUserData(_)[Data]),
    combinedTable = Table.Combine(List.Transform(allPages, each Table.FromList(_, Splitter.SplitByNothing(), null, null, ExtraValues.Error))),

    // 必要な項目のみを抽出して日本語の列名に変換
    selectedColumns = Table.ExpandRecordColumn(combinedTable, "Column1", {
        "UserId", "LoginId", "Name", "UserCode", "Language", "TimeZone"
    }, {
        "ユーザID", "ログインID", "名前", "ユーザコード", "言語", "タイムゾーン"
    }),

    // 最終的なテーブル名を設定
    outputTable = selectedColumns
in
    outputTable