社内のネットワーク環境を可視化・分析してみた

### はじめに
  　この記事はMicrosoft Power BI Advent Calendar 2022　12月20日の記事になります。
　
　佐賀県の小売業（和服業）で情シスをしており、複数のグループ会社（約５社）と約８０店舗（約８００名）のシステム関連全般を２名の情シスで行っています。　

https://qiita.com/advent-calendar/2022/powerbi



### 記事の内容
　社内でネットの速度（ウェブ会議や業務システム）が遅いとの問合せ（特に朝）があり、社内のネットワーク改善で「どこの拠点・時間帯が遅くなっている原因なの？」とよく言われるので、Power BIにて可視化して分析を行ってみました。
　
　

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2277732/be1f03ba-eee6-b81b-a906-ed8faf765eef.png)



### 通信データ(CSV)のダウンロードと格納
　各拠点の月単位での通信データをＣＳＶファイルを同一フォルダにファイルを格納する。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2277732/3e64f0ca-8923-58e9-af54-1aec33177eec.png)


## Power Query部分
### １．データ内容について
　データはＣＳＶ形式で、拠点で月単位にファイルが分かれている。
　ＣＳＶは、1行目～11行目がヘッダ部分で、12行目以降に日付・時間帯毎の通信量が格納されている。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2277732/56a3581c-3c20-bc76-ba1e-1564e21abbff.png)


　
### ２．データ取込・加工・変換（ファクトテーブル）

　Power BI Desktopのデータ取得にて「フォルダ」を選択しＣＳＶファイルが格納されているフォルダを選択
　不必要な列を削除し、ファイル名から拠点IDを取得、ヘッダ部分の行削除を行いデータを整えます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2277732/c9ea9c7d-c224-06d0-9be7-93f904a22a16.png)


#### 　①拠点IDの取得
　ファイル名の左１０桁が拠点IDのため、Text.Startにて最初の10文字を抽出する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2277732/fb7ecf04-72e7-7996-87a2-2e5a3350789d.png)

#### 　② ヘッダ行の削除
　CSVデータの上位11行はヘッダ情報のため、ヘルパークエリ内で上位の行を削除する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2277732/ca1cade6-ba43-9d6c-1f8d-52d506e2a1a1.png)

#### 　③列は分析に必要な項目のみとする
　ファクトテーブルの列数は分析に必要な項目のみ（拠点・日付・時間帯・通信量）とし不必要なの列は削除する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2277732/cfc0a8cb-d1c3-da7d-3d1c-a75d11d83854.png)



### ３．ディメンションテーブルの作成
　日付、拠点、時間帯のディメンションテーブルを作成する。

#### 　①日付テーブル
　　今回はPower Queryにて作成を行う。

https://learn.microsoft.com/ja-jp/power-bi/guidance/model-date-tables


#### 　②拠点情報(Area)
　　拠点情報（ＩＤと拠点名）は、システム部門で管理しているのでそのデータを利用してディメンションテーブルを作成

#### 　③時間帯(Time)
　　時間帯についてはＣＳＶファイルを見ると５分刻みだったので、List.Times関数で0時から5分刻みで23:55までのリストデータを作成しテーブルに変換する

```
List.Times(
            #time(0, 0, 0), 
            288, 
            #duration(0, 0, 5, 0)
           )
```


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2277732/c30791c6-533c-a21b-01d2-bfd207de40e1.png)


#### 　④業務時間帯の区分（時間帯)
業務時間帯の通信量を調べるメジャーを作成するため、Power Queryの条件列にて、時間帯のディメンションテーブルに業務時間帯のフラグをつける。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2277732/0cbcf680-62ef-b462-0753-59107f9464df.png)



## レポート部分

###  １．データモデリング
　ファクトテーブルがTraficDataとし、日付テーブル・拠点（Area)・時間帯(Time)をディメンションテーブルとしスタースキーマにしファクトテーブルの項目は非表示にする。

https://learn.microsoft.com/ja-jp/power-bi/guidance/star-schema


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2277732/451780d0-e579-93bc-9e72-047f08d8c71f.png)



###  ２．メジャーの作成
　集計・計算用のメジャーを作成します。Power Queryで区分を立てたWorkkbn(業務時間帯)のデータ量のメジャーを入力します。
　
```
勤務時間データ量 = 
CALCULATE(
    sum(
    TraficData[Out_data]
    ),
    'Time'[Workkbn] = 1
)
 ```


###  ３．グラフを作成
　時間帯毎の通信量はリボングラフにし、スライサーを曜日・年月・会社名にし条件による時間帯毎の通信量が分かるようにする。
　また会社別・1拠点あたり通信量の割合を把握できるよう円グラフにする。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2277732/ee959245-f147-037f-dcb0-c2e198c2f521.png)



## 分析してみたところ

### １．朝・夕の通信量が多すぎる

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2277732/8b678456-0295-f260-ae6f-8a7296ad5712.png)
　　午前９時～午前１０時にかけて通信量が多いのは、毎日ウェブでの朝会が行われているため。
　　全店での店舗でのリモート打合せ（開店前）が多いため全体的に通信量が多いものと思われる。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2277732/2d49fdec-09ea-fa6e-398a-4bbef3acc5d2.png)


### ２．１拠点だけ通信量が常に多い

　　会社単位では水色の通信量が多いが、拠点数が多い（約５０拠点）ので１拠点単位では一番少ない。
　　ただ紫色の会社については、１拠点当たりの通信量も大きい。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2277732/34b9c73f-652a-d8f6-74e8-c88bfb014ee1.png)

　
　　考えられるのは会長が複数のＰＣでオンデマンド動画をほぼ毎日同時視聴しているのが原因。
　　常に一定量なので、動画を開きっぱなしというのが推測される。

　![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2277732/00bcfe80-b666-9aa7-2e4e-30119942d5ca.png)



## まとめ
　通信改善の提案は費用面で却下になったが、データを可視化してみることにより、以前までは「通信が遅いので改善したい」という曖昧な部分がデータを可視化することにより根拠を示すことができた。