知人の店舗のレジデータを可視化した話

　最初は知人の店のルーターの設定をするだけだったのだが、
レジデータがSQLで動いていることを知り、
　　　これ、可視化したら面白いのでは？
という興味からデータを抽出しようと計画しました。
　ただ、普段業務でTableauを使用する環境とは随分異なる環境で、
DBをろくに触ったことのない人間にはハードルが高かったです。

　レジのDBはMicrosoft SQL Server2014でSSMSを使用しており、
レジ付近のPCからSSMSを使用してアクセスできる、というものです。

何が課題となったか？

①　LANで構築されたDBに他のPCから接続できない
②　SQLServerユーザーの作成方法がわからない
③　データをCSVで抽出してからpythonで各マスタと結合するとエラー
④　Tableauで描けるvizを作りたくても作れない
⑤　データが大きすぎてクレンジングが必要

　…では一つずつ解説していきます。

①LANで構築されたDBに他のPCから接続できない

　これは、自分の知識不足の問題です。
　普段の業務の通り、レジデータを管理しているPCとは別のPCに
SSMS(別のアプリケーションでもOKですが)をインストールして、
IPアドレスを入れて…接続できないんだな、これが
　結局は「SQL Server Browser」を無効から自動に設定したら無事
接続できました(2hくらいかかりました)

SQL Server Browser起動 ｜ LAN対応版 ｜ 見積ソフト見積大臣&regシリーズ

②　SQLServerユーザーの作成方法がわからない

　まずやろうとしたのは、
　　　WireSharkを使ってパケットを解析
　　　　⇒パスワードを割り出す
という方法です。
　最初は、DBの構成を変えたためにデータ作成の
トランザクションや日時処理に影響を出しやしないか、ということで、
既存のユーザーデータからSelect文でデータを抽出してしまえ、と思った
わけです。
　ですが、これ結構危ない方法。（やったらダメな方法）
　というのは、レジPCのSSMSのログイン情報でログインすると
管理者としてログインすることになることに後々気づきました。
　結果として以下のサイトを参考にSelect文しか書けないユーザーを作成
して各種DBのデータを参照できるようにしました。
　念には念を入れて、作業手順書を作成し作業しましたので
事なきを得ました。（わからないことは調べてから作業しよう）

SQL Server でログイン、ユーザーを作成し、アクセス許可を付与する方法

③　データをCSVで抽出してからpythonで各マスタと結合するとエラー

　さて、データをsqlarchemyで無事取り出してcsv化してpythonで結合
しよう…
　　（⇒　テーブル定義がなかったので全データを見て
　　　　何のコードがどのマスタに対応するか、とか割り出しました）
　はい、結合できませんね。
　なぜか、というと、csvにした時点で再度pandasで読み込むと
　　　型がすべてobject型になっている
という地獄が発生しているのですね笑。
　つまり、年月日やら、時刻やら、がない、テーブル定義も無い、抽出したときのDB情報も無い、ので再度お店に出向いて型を割り出しました。
　上記の地獄の最中、マスタの結合、取得するカラム等、なるべくSQLで済むようにSQLクエリも作成しました。
　SQLServerはあまり詳しくないのでこれも時間がかかりました。

④　Tableauで描けるvizを作りたくても、作れない

　私はTableauユーザーなので
　　　①　TableauPublicでcsvを読み込みモックアップを作成
　　　②　python(plotly)コードに変換⇒streamlitに表示
とするのが、ビジュアライズには効率的かな、と考え作業しました。
　ですが、ここでTableauの優秀さを認識
　当然ですがTableauはビジュアライゼーションに特化したツール
なのでplotlyを使うと
　　　①　ダッシュボードアクションが使えない
　　　②　二重軸のグラフがうまく描画できない
　　　③　色が細かく設定できない
　　　④　シートの大きさ、配置を設定しづらい
という、Tableauでは当たり前にできていたことが、plotly*streamlit
だと難しい、という問題に直面してしまいました。(3週間くらいかかった)
　plotlyはシンプルなVizに限るのかなぁ…。

⑤　データが大きすぎてクレンジングが必要

　そもそもSelect文を書いてデータを抽出した時点から分かっていた
ことですが、レジデータは300MB以上あり、クレンジングは必須でした。
　まずやったのは、
　　　①　NULL、０しか入っていないカラムを削除
　　　②　値が２種類しか入っていないカラムを削除
の２点。
　この時点で180MBくらいになりました。
　さらに全データを見る＆事前にもらっていた正解値と突き合わせて
10カラムくらい減らして、マスタと結合して…140MBくらいになりました。
　クレンジングに使用したのはExcelとテキストエディタです。
　まあ、日付は勝手に数値に変えるわ、コードを1900/1/2とかにするわ、Excelに対してはしばらく恨みを持ちますね。

おわりに

　今回、実施したのは環境が整っていない＆お金をかけない、という条件で
データを可視化した場合どうなるのか、の実験(?)でした。
　感想としては、
　　Tableauは有能でDBが使える普段の環境に感謝
というもので、貴重な経験になりました。
（企業秘密だから無理なんだろうけどDB定義くらい用意しておいて欲しいナ）