Power BIでPostgreSQLを使う

静岡にしかないハンバーグ屋さわやかだが、土日祝はもちろん、GWはさらなる混雑具合が想像できる。
さわやかは静岡県内に34店舗あるので、混雑する店舗とそうでない店舗があり、混雑しない店舗がどこだろうと分析を行いたい。
分析にはPower BI Desktopを使用し、データはあらかじめVPS上のPostgreSQL内にスクレイピングで蓄えるようにした。

PostgreSQLを接続できない…？

Power BIにはあらかじめPostgreSQLを接続できる機能が入っているので、その機能を使って接続しようとしたところ

“検証プロシージャによると、リモート証明書は無効です。”と表示され接続できないと怒られてしまった。
SSL周りがいけてないのかなと思い、”ファイル”→”オプションと設定”→”データソース設定”を開き、

接続したいDBを選択し”アクセス許可の編集”のウィンドウから”接続を暗号化する”のチェックボックスを外しOKをクリック。
再度PostgreSQLの接続を試みると、

無事データを読み込めそうだ。

Web上でも見れるようにしたい

DB上に15分間隔でデータが登録されるようになっているので、リアルタイムとまではいかなくてもBIのレポートをWeb上で確認できるようにしたい。
そのためにはPower BIを発行してあげればよい。（”発行”→”Power BIへ発行”）
発行後Web上のPower BI（Power BI Service）からWebに公開をしてあげれば自分のWebサイトへ埋め込むことができる。

Power BI Serviceには自動更新機能がついており、任意の時間に1日最大7回までDBからデータを取得する機能がついているのだが、

“資格情報が正しくないため、データ ソースを更新できません。資格情報を更新して、もう一度お試しください。”や
“An error happened while reading data from the provider: ‘The remote certificate is invalid according to the validation procedure.'”
と表示されて更新機能が使えない。どうやら真面目にSSL対応をしないといけなさそうだ。

PostgreSQLのSSL設定をする

SSL設定には証明書が必要である。ここではLet’s Encryptで作成した証明書を使う。
まずはpostgresqlのフォルダ直下に証明書を置く。

# postgresqlフォルダ直下に証明書を置き、所有者を変更
$ cp /etc/letsencrypt/live/<サイトURL>/fullchain.pem /etc/postgresql/12/main/
$ cp /etc/letsencrypt/live/<サイトURL>/privkey.pem /etc/postgresql/12/main/
$ chown postgres:postgres /etc/postgresql/12/main/*.pem

証明書を置いたら、設定ファイルに証明書の場所を記載する。

$ vim /etc/postgresql/12/main/postgresql.conf

ssl_cert_file = '/etc/postgresql/12/main/fullchain.pem'
ssl_key_file = '/etc/postgresql/12/main/privkey.pem'

ここまで終わったら、サービスを再起動する。

$ systemctl restart postgresql

再起動が終わったら、Power BI Service上で再度資格情報を入力する。
入力し終わったら、正常に反映されているはず。
その後、Power BI Desktopでのデータベース接続の暗号化設定を有効にしておくといいだろう。