生産設備の稼働データを外部から取ってみよう(活用編)

京セラコミュニケーションシステムの大山です。

前回、生産設備の稼働データを外部から取ってみよう[ネットワーク編]で京セラIoTゲートウェイKC4-C-100A(以降、KC4と称します)から送信されてくる稼働データをSORACOMを経由してGoogleCloudPlatform(以降、Google Cloudと称します)のBigQueryに入れるところまで作ってみました。
今回はBigQueryに入れた稼働データをクエリ抽出してLooker Studioで表示するところまでを作ってみたいと思います。

この記事の対象者

設備稼働データを取りたいけど設備の中から線を引き出すのはちょっといやだな・・・という方
設備稼働データ取りたいけどPLCのプログラムはいじりたくないよ・・・という方
メンテナンスの停止ができない生産設備をお持ちの方
デジタル化担当になったが何から始めていいか分からない方

積層信号灯から設備稼働データ収集までの経路

大まかなデータの流れについては以下となります。

今回は上図の赤線で囲っているBigQueryからLooker Studioでグラフ表示するところとなります。
※2022年10月にDataPortalからLooker Studioに名称が変更されました
※SORACOM Beam は、株式会社ソラコムまたはその関連会社の商標または登録商標です。

クエリの集計・抽出について

BigQueryエクスプローラ画面のプルダウンでテーブル名まで下りるとプレビュー画面を見ることができます。

ただ、そのままの状態では使えないため、データの抽出加工が必要となります。
クエリ又は＋ボタンを押すとクエリ実行できる画面が出ますのでSQLを書いて実行します。

・プレビューのカラムについて
　device_code:どのKC4から送信されたデータなのかを表します。今回は1台なので1のみ
　inserted_at:Google Cloudでデータを受け取った時間
　timestamp:KC4から送信した時間
　color:KC4で認識した色情報
　ls1:KC4に入力された赤色の信号灯ステータス(1:点灯、0:消灯)
　ls2:KC4に入力された黄色の信号灯ステータス(1:点灯、0:消灯)
　ls3:KC4に入力された青色の信号灯ステータス(1:点灯、0:消灯)

・BigQueryに取り込んだデータについて
　BigQueryのデータは信号灯の発光パターンを
　通常動作　黄色(4秒)→青色(5秒)
　待機動作　黄色(1秒点滅)
　異常停止　赤色(1秒点滅)　
　3種類をランダムに積層信号灯で点灯/消灯させ、センサーを介して収集した点滅データを7時間BigQueryに取り込んでいます。

SQLについて

SQLについては取っ掛かりとして簡単なご紹介だけいたします。
SQLには以下の表のように処理を行う順番がありますのでSELECT文で使いそうな命令を抜粋して紹介します。
※SQLについてはいろいろなサイトや書籍でもっと詳しく紹介されてますので見て試してみるのがいいかと思います。

順番	命令	処理内容
1	FROM	出力テーブル指定
2	JOIN	テーブル結合
3	WHERE	条件の指定
4	GROUP BY	カラムを指定してグループ化
5	HAVING	グループ化後の条件の指定
6	SELECT	表示カラムを指定
7	ORDER BY	昇順・降順並び替え
8	TOP(LIMIT)	特定の範囲のレコード抽出

例えばある30分間の信号灯(R,B,Y)ステータスが点灯/消灯した回数を出したい場合
colorとtimestampのカラムを使ってSQLを以下のように書きます。

クエリ

select 
values.color,
count(values.color) as count_color ,

from `プロジェクトID.データセット名.テーブル名`, unnest(values) as values 
where values.timestamp between '2022-09-07 00:00:00' and '2022-09-07 00:30:00' 
group by values.color;

実行順で説明すると
①FROM　'任意のテーブル'指定
②WHERE　9/7の0時～0時30分を指定
③GROUP BY　カラム'color'をグループ化
④SELECT　color と　R,B,Yそれぞれをカウントした個数をcount_colorとしてカラム表示
といった感じになります。
※そのままではvalues配下のls1,2,3やtimestampを参照することができないため、unnestを使いValuesでくくられているREPEATED型のカラムを展開する必要があります。

エラーがなければ右上に”このクエリを実行すると～”と出ますので処理容量に問題なければ実行ボタンを押してクエリ実行します。

R,B,Yそれぞれの回数が集計されたクエリ結果が出力されます。

SQLコードを残しておきたい場合は保存ボタンを押して名前を付けて保存できます。

Looker Studioでグラフ表示

1分毎の設備の状態を積み上げ棒グラフ表示
総時間に対して設備がどういった状態にあったかの割合を円グラフ表示
それぞれの設備の状態の総時間を横棒グラフ表示
させてグラフにまとめてみたのが以下となります。

グラフの挿入方法について
編集画面のデータを追加タブを押す。
データレポートへの追加からBigQueryを選択する。


カスタムクエリを選択しプロジェクトを選ぶとクエリ入力画面が出るのでBigQueryで作成していたSQLを持ってきて追加ボタンを押す。

編集画面にデータが置かれます。そのデータを選択してグラフを追加タブを押すとグラフの種類が出るので選択するとグラフが表示されます。
思っていたグラフと違っていた場合はグラフをクリックすると設定とスタイル調整の画面が現れますのでそちらで調整してください。

集計に使ったSQLは以下です。
el_timeは該当のtimestampからひとつ前のtimestampを引いて何秒間そのステータスにあるのかを出しています。
それ以外はそのままかカラム名を変えてtimestampの昇順で出力しています。

時系列グラフ用クエリ

select 
device_code, 
values.timestamp, 
DATETIME_DIFF(LEAD(values.timestamp) over(order by values.timestamp), values.timestamp, SECOND) as el_time, 
values.color, 
values.ls1 as anomaly, 
values.ls2 as stand_by, 
values.ls3 as operation, 

from `ds-poc-220705.cat4.cat4-ls-landing`, unnest(values) as values 
order by values.timestamp;

上記で作成したクエリをそのまま利用して
通常、待機、異常、トータルそれぞれの集計時間を出力しています。

各ステータス総時間グラフ用クエリ

WITH EL_T as(select 
      device_code, 
      values.timestamp, 
      DATETIME_DIFF(LEAD(values.timestamp) over(order by values.timestamp), values.timestamp, SECOND) as el_time, 
      values.color, 
      values.ls1,
      values.ls2,
      values.ls3,
      from `ds-poc-220705.cat4.cat4-ls-landing`, unnest(values) as values 
      order by values.timestamp)
select *
from(
select 
device_code, 
(select sum(el_time) from EL_T where color = 'R' and el_time < 10) as anomaly, 
(select sum(el_time) from EL_T where color = 'Y' and el_time < 10) as stand_by, 
(select sum(el_time) from EL_T where color = 'B' and el_time < 10) as operation,
(select sum(el_time) from EL_T where el_time >= 10) as pwr_off,
(select sum(el_time) from EL_T ) as total,
from `ds-poc-220705.cat4.cat4-ls-landing`, unnest(values) 
 group by device_code )
UNPIVOT(
  PT_el
  for EL in (anomaly, stand_by, operation, pwr_off, total)
)

データ活用について

最後に集めたデータを使ったグラフをどう活用するか設備稼働効率改善を目的として考えてみました。

グラフ	色	確認内容	判断・行動
円グラフ	赤黄	各ステータスの比率を見る	異常・待機状態のどちらに対して改善のテコ入れをしてみるか検討する。
棒グラフ	赤	異常時間を見る	頻発している異常や復旧までに時間が掛かっていた時間帯をピックアップして作業者や段取工にヒアリングする
棒グラフ	赤	異常の時間帯を見る	時間的な傾向があれば同時間帯に設備的な問題か人的問題か設備に張り付いてみる。
棒グラフ	黄	設備待機時間を見る	待機時間が長い所をピックアップして単に生産していないだけなのか、段取りに時間が掛かっているのか、動線に問題があるのか同時間帯に設備に張り付いてみる
棒グラフ	黄	時間によって待機時間に違いがあるか見る	人によって作業手順に違いがないか、又は効率的な動きをしている作業者を参考に作業手順書を見直し、作業者を教育

データ収集だけで改善個所がすぐにわかるといったことはありませんが、感覚的に見えていた設備の停止を異常が多いのか待機が多いのか各状態の時間帯や発生頻度が見えることにより、どこにフォーカスをあてて改善を行うかの指標になるかと思います。
また、工場により積層信号灯の点灯ステータスは違うと思うのでここからは仮定の話となりますが、Looker Studioのグラフ画像の上側にある3つの数値は青色の点灯を1個の生産としてカウントした生産個数、部品単価をかけ合わせたサンプル時間の生産金額、生産金額からサンプル時間を割った時間当たり生産金額を出したものです。
生産設備が会社にどれだけ貢献しているのか、改善前後でどれだけ生産金額に変化があったかなど設備改善の効果が金額的に見れますし、それらを指標に設備自体の要否判断や内外製の判断材料にもできるのではと思いますので改善活動も少し面白くなるのではないでしょうか。
あと最後に今のままでは見栄えがあれなので・・・いい感じに作ってもらった画面がこちらです。

文字フォントや配置等だけでもいい感じになるかと思いますので色々試してみるのもいいかと思います。
(@kccs_haruki-nukumi 忙しい中画面作成に協力いただきありがとうございました。<(_ _)>)

まとめ

外部から生産設備の稼働データ収集をするということで
積層信号灯から光センサーによるKC4でのデータの収集からGoogle Cloudへの送信、見える化まで一連の流れをやってみましたが積層信号灯一つでも改善のきっかけになるような情報が取れることがわかるかと思います。また、扉開閉センサーや可能であればライトカーテンなどからもデータを取れたら設備や人がどう動いていたのかもっと細かく見れるのではと思いました。
PLCの方が取得できるデータの種類も多いと思うのでそちらからデータ収集するのが一番かと思いますがそういった事がどうしてもできない場合は外部からとってみることに挑戦してみるのはいかがでしょうか。

※記事に掲載しているスクリーンショットは2022年12月時点となります。