こんにちは。Keepdataのピー（ニックネーム）です。BIによる可視化の案件に携わって十数年になりますが、今回はその中でも、特に手ごわかった課題についてご紹介したいと思います。
ちなみにこの課題の解決結果は、弊社製品KeepData Hubの医療パッケージとして息づいています。
業種は医療で、病院の診療データの活用に関する課題です。試してみたBIツールは、世界的に有名なBIツールAと、国内で有名なBIツールB、弊社のKeepData HubのBI機能です。SI企業在職中から今に至るまでに様々なBIツールに触ることができたことは、財産だったとつくづく感じます。
ある病院様での課題はこうです。
課題①
病院にある電子カルテのデータから投薬データ、検査システムからCSVの血液検査データ、ある診療科の部門システムから出力された症状の数値データを透過的に検索をかけて、それぞれの条件にヒットする患者の人数を把握したい。
課題②
患者を検索したら、その患者について時系列に投薬した結果、症状の数値データや血液検査結果がどう推移したかをグラフで見たい。また、薬を変えたタイミングを俯瞰してみたい。
①は治験（薬剤を開発した後に行う臨床試験）候補者を探したり、学会発表で統計データを取る前に発生するニーズです。最近では治験で患者を探すにも、薬が良くなってきている関係もあり、患者を探すこと自体苦労するようです。学会発表の場合には、統計を取ろうにも、条件によってはそもそも母集団が足りない場合が多々あり、先行して人数を調べる必要に迫られます。また、単純に医療現場で類似した患者を探して過去の処置を参考にしたい場合にも活用できるとの事です。
普通に考えると何のことはない課題ですが、いざやってみるとシステム的な課題が結構あることが分かりました。
まず、最初の課題です。
それぞれのデータに結合キーとして使える項目が存在しません。
あっさり書くと一言ですが、かなり深刻な問題です。
＜データ構造簡略図＞
そもそも全く別のシステムから出力されたデータです。患者ID以外、共通となるコードがありません。それぞれのデータに日付はありますが、1日に複数の処方や検査があるのが当然で、多レコード対多レコードの結合になってしまいます。
正確に言いますと、データベース的には複合キーになりえるキーはあるものの、結合キーとして使えないという事です。
詳しく説明しましょう。
例えば、どちらかを主表とする左結合を使った場合、副表にデータがあっても、該当する主表の日付データが存在しないと消えてしまうという問題が発生します。
仮に左結合で検査テーブルを主表として、副表に薬剤の処方テーブルを設定した場合、検査を行った日の処方データは表示されますが、処方のみ存在する日のデータは欠落してしまいます。
ここで双方の欠落分を補う完全外部結合を利用すると、前述の処方のみ存在する日も漏れなく表示されるようになります。ただ、レコードが「患者ID＋実施日」の結合キーに対して1対1のや多対1である場合はきれいに結果を出せるのですが、多対多の場合は、補完作業を右と左で交互に行ってしまうため、結果が掛け算的に増えてしまいます。
これはデータウェアハウスを構築する上でよくぶつかる課題ですが、経験上、良い解決方法がない場合が多いようです。よくある解決方法としては、片方を集計して無理やり多対1にするという方式ですが、それで済む場合とそれでは要件を満たさない場合があります。今回は、処方と検査双方すべて検索したいため、これでは要件を満たせないようです。
くよくよしても仕方がありませんので、一つずつ問題を解決していきましょう。
よくよくお客様にヒアリングすると、部門システムと検査システムは、1対多の関係にあるようです。これはラッキーです。
部門システムから出力された症状の数値データは、診察の際に待合室で入力されるタッチパネル形式のソフトであるため、1日1回しかデータは発生しません。しかもそれぞれの数値データは、きちんと列項目として格納されているため、きれいに横並びになっています。
であれば、検査データが一日に複数あっても、部門システムの問診データをマスターとして扱えば、リレーションを張ることが可能です。
こんな感じです。
＜リレーションイメージ＞
この調子で乗り越えられる！と思ったのですが、想いはすぐに覆されました。薬は患者毎に1日に何種類も処方されるため、1日1患者に複数データが存在してしまいます。 つまり検査データと処方データの関係は、多対多の関係となります。データベースで結合すると前述のように検索結果の行が無駄に増えてしまいます。患者人数を調べるだけなら、重複を除くという手もありますが、明細も見たいという要件もあるため、DBでの解決は難しそうです。
DBでの施策はここまででしょうか。それではいよいよそれぞれのBIツールでこの問題を解消できたかどうか見てみましょう。
まず世界的に有名なBIツール。仮にAとします。
Aでは、データソースにそれぞれ直接アクセスし、集計前の結果をデータベースと同様のリレーション機能で結合できるという優れた機能を持っています。
ただ、これではデータベースと同じ機能ですので、多対多の結合ではレコードが増えてしまいます。違う方法を探してみます。
Aにはもう一つ、集計結果した同士を結合させる機能があるようです。
患者IDと日付で集計した結果同士を突き合せれば、正確な人数が出るかもしれません。
やってみたところ、検査データの件数は正確に出るのですが、処方の件数は全く反映されないようです。どうやらこの機能は左結合が前提のようです。つまり、検査か、処方のどちらかを主表とすると、主表にした結果の件数が全件出てきてしまいます。これでは、それぞれのデータに共通した人数を調べることはできません。
次に国内で有名なBIツール、仮にBとします。
Bにも集計結果同士を結合する機能があります。しかも完全内部結合。つまり全てのテーブルの集計結果で共通したレコードだけを取得することができます。
すばらしい。これで正しい結果が出せそうです。
＜主表をベースとした内部結合と完全内部結合のイメージ＞
結果のイメージをエクセルで表すとこのような形になります。
＜BIツールBの結果表イメージ＞
患者IDが2か所にあるのが違和感があると思います。患者ＩＤをコピーして使いたいという要件に基づいて表示しました。実はこのBIツールの仕様上、行項目（背景色緑の部分）をコピーできないようですので、仕方なく集計結果エリア（背景色白の部分）に患者IDを数値として表示しています。
ここはちょっと苦しい仕様でしたので、最終的にはCSVダウンロードボタンも取り付けてみました。
最後に弊社KeepData Hubではどうかという話に移りたいと思います。そもそもKeepData Hubは、データベースを用いずにCSVのまま集計結果を高速に返すという機構を持っています。
よって、それぞれの検索結果を合成するという事はできそうです。もちろん完全内部結合です。最終的には明細表示などの基本パーツはあるため、見た目や使い勝手などの微調整をJavaScriptによって加え（ここは開発者に依頼）、完成。以下のようになりました。
＜KeepData Hub 医療パッケージ検索画面＞
＜KeepData Hub 医療パッケージ結果画面＞
検索条件は1画面ながら、結果を複数のCSVファイルそれぞれに調べに行き、それぞれに共通する患者IDだけを抜き出しています。それぞれのデータのヒット件数は、18件、5件、36件と異なりますが、このすべてに存在する患者IDから重複を除くと2件(2人)になるようです。
そして得られた表の患者IDをクリックして明細を表示する機能も実現しています。
＜KeepData Hub 医療パッケージ明細画面＞
①の課題は、BとKeepData Hub のBIツールで実現できました！
続いて②の課題に移りましょう。
時系列グラフです。
イメージは手書きで伺いました。エクセルで作画してみるとこのような感じになります。
＜課題② 時系列グラフイメージ＞
結構シンプルな形です。何も考えずにとりあえずBIツールでグラフ作成を行いました。
そしてものの数分で問題にあたってしまいました。
あれ、折れ線と積み上げ棒グラフのコンボってどうやって項目を配置すればよいのでしたっけ？
長くなりましたので、今日はこのあたりにしておきましょう。
続きは次回のブログで。
今回のまとめ
・多対多のレコード結合は、BIツールの結果表結合機能がおすすめです。
・BIツールであと一歩できないことを、プログラムで補完できる機能があると提案の幅が広がります。
