BIとは Business Intelligence の略です。簡単に説明すると ビジネス数値の見える化 です。
会社などの組織にいると、お金や作業工程など様々な目に見えない数値を管理する必要があります。
人間は不思議なもので、数字だけ聞かされてもピンと来ないものでも、目に見える 形にするとクリアに理解できて、課題点が見えてきたりするものです。
ところが、この数値の「見える化」は様々な数値データを取り寄せて加工、集計しなければならず、非常に手間がかかるケースがほとんどです。
手間がかかるために「見える化」できておらず、大きな問題を見過ごしてしまうケースも多々あります。データをちゃんと見ていたらトラブルを未然に防ぐことができたはず、ということが後から分かっても、『後悔先に立たず』ですね。
このように「BI」「見える化」は管理者にとって無くてはならないものです。
BI システムはこの数値の「見える化」を行うシステムですが、高額な製品が多く、ハードルが高いのがほとんどです。
ところが Excel と無料ツールと組み合わせることで、十分使える BI システムを構築することが可能なのです。
この記事では、この無料ツールと Excelで BIシステムを構築する方法をご紹介したいと思います。
是非参考にして、皆さんの業務に役立てて頂ければと思います。
では早速いってみましょう。
- BIシステムでできること
- BIシステムの構成
- BIシステムの構築手順
- データの形を決める
- サーバーPCに SQL Server Express版をインストール&設定
- Power Automate で会社のシステムからデータをダウンロード
- Excel の Power Query でデータ整形
- Excel VBAマクロ で SQL Server へデータをアップロード
- Power Automate で Excel の処理を動かす
- Excel Power Query で必要なデータを取得
- Excel Power Query でデータを加工
- Excel Power Pivot でデータを見える化
- 全自動機能を走らせてみる
- まとめ
BIシステムでできること
BI システムはクルマのダッシュボードのメータのようなものです。クルマのダッシュボードにはスピード、燃料残量、エンジン回転数など運転に必要な情報が一目で分かるように表示されています。
クルマのダッシュボードは当然ながら、人が何もしなくても今の瞬間の情報を表示します。
ビジネスの世界でも同じように重要な管理数値を タイムリー かつ 手間なく 把握できる必要があり、これを実現するのが BIシステム です。
この記事で紹介する BIシステム は たった2アクション で数値の見える化が可能です。実際に使ってみると、余計なエネルギーを割くことなく、ビジネス上の重要な課題に頭を集中させることができるので、非常に有効なツールであることが分かると思います。
BIシステムの構成
では BIシステム とはどういう構成になっているのでしょうか？下の図をご覧ください。やはり BIシステム は データ処理 が中心になっていますね。
データ処理は Excel
ご存じの通り データ処理 は Excel、特に Power Query が得意とするところです。ですから、ここは Excel を使います。
データ ダウンロードは Power Automate Desktop
SAPなどの組織の会計システムや各種基幹システムからのデータ ダウンロードには Microsoft Power Automate Desktop を使います。これは Microsoft が提供する無料のRPAアプリで、無料ながら十分実用に耐えるものになっています。
RPA は Robotic Process Automation の略で、ユーザーのマウス操作やキー入力と同じ動作を自動で実行してくれるバーチャルなロボットのようなアプリです。これを使えば、基幹システムが専用アプリであっても、ウェブアプリであっても自動操作でデータをダウンロードしてくれます。
データ管理は SQL Server Express
それからデータ管理には SQL Server Expressを使います。これも Microsoft が提供する無料版のデータベースアプリで有料版とほぼ同じ機能を使うことができます。無料版の主な制約はデータ容量ですが、プロのレベルで使わない限り使いきれないほど大きいので、心配する必要は無いと思います。
BIシステムの構築手順
ここで紹介する BIシステムの構築は最初は面倒かもしれません。
しかし、一度 BIシステム を構築してしまえば、一昔前、いや、もしかしたら現在でも多くの会社で数名がかりで何時間もかけて行っている処理を、全自動で行うことができるようになりますので、労力をかける価値は十分あると思います。
さて、その長い道のりを見てみましょう。作業を順に並べると以下のようになります。
- データの形を決める
- サーバーPCに SQL Server Express版をインストール&設定
- Power Automate で会社のシステムからデータをダウンロード
- Excel の Power Query でデータ整形
- Excel VBAマクロ で SQL Server へデータをアップロード
- Power Automate で Excel の処理を動かす
- Excel Power Query で必要なデータを取得
- Excel Power Query でデータを加工
- Excel Power Pivot でデータを見える化
データ処理の流れは下の図のようになります。
では、次の項から順に説明します。
データの形を決める
まず最初に BIシステム に保管するデータの形を一度決めましょう。後で変更したくなったら変更できますので、ご安心ください。
決めたデータの形があれば、この先の作業を迷うこと無く進められます。
データの形とは、図のように1行目は項目名で、２行目から値となっている表で、列ごとに数値、日付、文字列などのデータ種類が同じものです。
データの形を決める上で大切なことは次の通りです。
- 必要かつ十分なデータ項目になっている
- それぞれのデータ型は適切に設定されている
特に1番目の 必要十分 なデータ項目を決めるにはそのデータを使って何をするかを良く理解している必要があります。
しかし、ここではとにかく一度決めることを優先に考えましょう。このシステムを成熟させる過程で洗練されたデータ項目になっていくと思います。
このデータの形を決めたらどこかにメモしておきましょう。このメモはこの先で使いますので、いつでも参照できるようにしておいてください。
サーバーPCに SQL Server Express版をインストール&設定
BIシステムの構成図からも分かるように、このシステムの中心にあるのはデータベースです。
データベースは他の人とデータを共有することもできるので、できれば共用のパソコンにインストールします。
サーバーの位置付けではありますが、プロ用途ではないので高性能である必要はありません。今時のパソコンはデータベースを動かすには十分な性能があります。
具体的なインストール手順と Excel から接続できるようにする設定の方法は以下の記事にまとめていますので、こちらで進めて下さい。
データベース、テーブルの作成
リンクの記事で SQL Server を Excel から使える状態になったら、次はこの BIシステムのデータを保管するテーブルの作成を行います。
データベースの作成
テーブルはデータベースの下に作成する必要がありますので、まずデータベースを作成します。
図のように SQL Server Management Studio を操作してデータベースを作成します。
ここでデータベースの名称は “DMS” （Data Management System ／ データ管理システム の略） など、お好きな名前を設定して下さい。
テーブルの作成
続いてテーブルを作成します。
ここで前の項で決めたデータの形を使います。メモを参照してください。
図のように SQL Server Management Studio を操作してテーブル作成画面からを出します。
テーブル作成画面で、あらかじめ決めたデータの形の通りテーブルの列の タイトル* と データ型 を設定していきます。
データの型はざっくり以下の中から選んで使って頂ければ大抵のニーズは満たせると思います。
| データ型 | 内容 | 解説 |
| int | 整数 | |
| numeric(x, y) | 数値、桁制限付き | xが全体の桁、yが小数点以下の桁 |
| float | 数値、浮動小数点 | |
| date | 日付 | |
| nvarchar | 文字列 | xは文字数上限 |
サンプルの事例として設定したものは下の図のようになります。参考にされて下さい。
Power Automate で会社のシステムからデータをダウンロード
データベースの準備が出来たら早速 BI のデータ処理の最初の部分から構築を始めます。
最初はデータ集めです。会社の様々なシステムからデータを自動でダウンロードする処理を Power Automate にやってもらいましょう。
まさにロボットに手作業の代行をお願いする感じです。
起動、フロー作成
Windows 11 なら Power Automate は最初からインストールされています。無ければ以下のサイトからインストールしましょう。
スタートメニューから Power Automate を起動して、図のように新しいフローを作成します。
システムを立ち上げ
最初に会社のシステムを立ち上げる必要があります。
Webベースのシステムの立ち上げ
システムが Web ベースのものなら、図のようにアドレスを指定してブラウザを立ち上げます。
SAPの起動方法
SAPは会社の基幹システムとして利用されているケースが多いと思います。
Power Automate は SAP コントロールの機能が標準でついていますので、これを活用して起動します。
下の図のようにして SAP起動のフローを作成します。設定パラメータは、サーバ、クライアント番号、ユーザID、パスワード などです。ご自分の利用されている環境に合わせて設定して下さい。
フローを実行してみる
まだシステムを起動する部分を作っただけですが、実際に動くか確認してみましょう。
図のようにフローの再生ボタンを押すとフローが動き出します。
無事システムが立ち上がればOKです。
データ ダウンロード操作の記録
Power Automate は Excel のマクロの記録と同じように、マウスやキーボードの操作を記録して再生することができます。ここがRPAの便利なところです。
システムが起動できることを確認したら、続いてデータのダウンロード操作を記録します。
図のようにして操作の記録を開始します。続いてマウスやキーボードを操作してデータをダウンロードします。
ここはシステムごとに操作方法が異なるので、それに従って下さい。
無事記録が完了したら続いてシステムを閉じる操作も加えます。
システムを閉じる
立ち上げたシステムのウィンドウを閉じるところまで Power Automate のフローを作成します。
Webベースのシステムの場合
Webベースのシステムの場合はブラウザを閉じれば良いので、図のようにコマンドを追加します。
SAPの場合
同様にメニューに「SAP接続を閉じる」がありますので、これをフローに追加します。
Power Automate フロー作成まとめ
以上で Power Automate でシステムからデータをダウンロードするところまで仕組みを作ることができました。
ここで使った Power Automate のフローは後の工程でも使います。Power Automate 自体は他にも色々と便利に使えますので、使い方を勉強してみてください。
Excel の Power Query でデータ整形
ダウンロードしたデータの形はシステムごとに様々です。特に、人の目で見やすい形になっているものは、データベースなどのアプリでは上手く読み取れないという問題があります。
これを解消するのに最適なのが Excel の Power Query です。Power Query はデータの形がどのような形であっても、大抵はアプリで扱えるデータの形に加工できます。
そこで、システムからダウンロードしたデータを Power Query を使って形を整えていきます。
形を整える上で必要なことは、前の項で決めたデータの形に完全に一致させるということです。
列の名前、データ型が一致していないとデータを SQL Server にアップロードする際にエラーとなってしまいます可能性があります。
前の項のサンプルのテーブルに合わせて Power Query でデータを整えた例は下の図のようになります。参考にしてみて下さい。
Power Query の使い方は以下のシリーズ記事にまとめていますから、ご自分のレベルに合わせて読んでみて下さい。
ここでの Power Query の処理工程は、次の処理に繋げるために、以下の設定をします。
- 作成したクエリの処理結果をテーブルに出力
- 作成したクエリは「バックグラウンド更新」の設定を外す
- 作成したクエリの名前を SQL Server 上のテーブル名と同じにする
それぞれ次のように操作します。
作成したクエリの処理結果をテーブルに出力
図のようにクエリをサイドウィンドウに出して操作します。
これでクエリの処理結果は Excel ファイル内のテーブルに出力されます。
これを次のステップでVBAで読み取って SQL Server に登録するのです。
作成したクエリはバックグラウンド更新の設定を外す
クエリのバックグラウンド更新が有効になっていると、次のステップのVBAの処理の中で、クエリ更新が終わる前にデータ登録処理が始まってしまい、正しくデータが登録できません。
このため、バックグラウンド更新の設定を外しておきます。
前の操作と同様に、クエリをサイドウィンドウに出して図のように設定します。
これでクエリのバックグラウンド更新はされないように設定できました。
作成したクエリの名前を SQL Server 上のテーブル名と同じにする
次のステップで使うVBAマクロは Excel のテーブルを同名の SQL Server 上のテーブルに登録する仕様になっているので、クエリの名称を変更しておきます。
図のようにクエリをサイドウィンドウに表示させて、対象のクエリを右クリックします。「名前の変更」を選択してクエリの名前を変更します。
なお、既に他のテーブルやセルに同じ名前が使われていると、その名前を設定することができませんので、名前が重ならないよう注意してください。
Power Query の処理 まとめ
以上で Power Query による処理は終了です。
システムからダウンロードしたデータを、データベースに登録するために形を整えて、Excel ファイルのテーブルに出力することができました。
また、更新はバックグランドで動かないため、VBAで処理する際は、更新が終わってからデータベースへの登録処理に進むことができます。
続いて VBA マクロの処理を設定します。
Excel VBAマクロ で SQL Server へデータをアップロード
ここでは VBAマクロで次の処理を行います。これにより、システムからダウンロードしたデータを SQL Server に登録するまでの一連の処理が完了します。
VBAマクロによる処理
- 作成した Power Query のクエリを更新
- SQL Server 上のテーブル内のデータを一旦全て削除
- Power Query で加工したシステムのデータを SQL Server に登録
では、この仕組みを実装する方法を解説していきます。
手順としては以下のようになります。ちなみにVBAマクロはこのページからダウンロードできるので作業自体は簡単だと思います。
- Power Query のクエリを作成したファイルに VBA マクロを追加
- VBAマクロを環境に合わせて編集
では順に詳細を解説します。
Power Query のクエリを作成したファイルに VBA マクロを追加
この処理をするVBAマクロは以下のリンクからダウンロードできます。
前の項で Power Query のクエリを作成したファイルを開きます。メニューから「開発」→「Visual Basic」をクリックして VBAエディタを開きます。
VBAエディタで下の図のようにしてダウンロードしたファイルをインポートします。
これでファイルに VBAマクロを登録することが出来ました。
VBAマクロを環境に合わせて編集
ご提供している VBAマクロは環境に合わせて少しだけ編集が必要です。
編集箇所は次の通りですので、コードの中身を見てご自分の環境に合わせて変更して下さい。コードの中にコメントをつけてある２か所が編集箇所になります。
- SQL Server への接続設定
- Excel の設定（シート名、Power Query 名）
動作チェック
これで VBAマクロが動かせるようになりましたので、動作確認をしてみます。
ファイルを開いた状態で図のように操作してマクロを実行します。
エラー無く完了したら、SQL Server Management Studio でテーブルにデータが入っているか確認してみましょう。
無事データが表示されたら、データ登録の部分はほぼ完成です。
Power Automate で Excel の処理を動かす
最後に前の項で作成した Excel の VBAマクロもまとめて Power Automate で実行してもらいましょう。
ここでの処理は次の通りです。
Power Automate の処理を追加
- 作成した Power Query & VBAマクロのファイルを開く
- VBAマクロを実行
- Excel ファイルを閉じる
Power Automate で前に作成したフローを開いて、フローの続きを作成していきます。
最初は図のようにして Excel ファイルを開きます。
続いてはマウス操作の記録の機能を使って、マクロ起動の操作を記録します。
最後に Excel を閉じて完了です。
最終チェック、フロー全体を実行
ここまででシステムからデータをダウンロード、データを整形、データを登録する一連の処理が完成しました。しかも、Power Automate によって全ての処理が全自動化できています。
最後に Power Automate のフロー全体を実行してみて下さい。まるで熟練オペレーターが操作しているかのように次々と処理が進みデータの登録まで終わったかと思います。
エラーが起きたら、上記の手順を参考に修正して下さい。
続いて SQL Server からデータを取り出してデータを見える化する部分を作っていきます。
Excel Power Query で必要なデータを取得
データベースにデータが登録されていれば Excel Power Query でデータを取得可能です。
図のようにして SQL Server のテーブルのデータを取得します。
すると SQL Server のテーブルデータが Excel のシートに出力されます。
更に、見かけの変化はありませんが、SQL Server から取り込んだデータが「データモデル」に追加されています。これにより Power Pivot でのデータ処理が可能になります。
Excel Power Query でデータを加工
システムからダウンロードしたデータがデータベースに登録され、それを Excel Power Query で取得できました。
元のデータがそのままで使える場合はこれで後はPower Pivotで集計をします。
しかし、元のデータに加工が必要な場合は、Power Query で更に加工していきます。
図のように操作してサンプルデータの日付データから年度の列を作ってみます。
これで元のデータに年度の列が追加されました。
Excel Power Pivot でデータを見える化
Power Query の出力をデータモデルに追加したので、Power Pivot による集計が可能です。
図のようにして Power Pivot を作成しましょう。
図のように Power Pivot での集計ができるようになりました。
この状態でこの Excel ファイルを名前を付けて保存します。これが今後自動BIツールとして活用できるものになります。
全自動機能を走らせてみる
以上でBIシステムの構築は完了です。
実際に使ってみましょう。と言っても次の2アクションを実行するだけです。
- Power Automate で作ったフローを実行
- Power Query を作成した Excel ファイルを開いて、メニューから、データ>すべてを更新
これで最新の会社システムのデータを見える化できます。どうです？とても簡単ではありませんか？
まとめ
この記事では Excel の Power Query、 VBAマクロ、Power Pivot、更に Power Automate の機能を駆使して、BIシステムを構築しました。
データの見える化は手作業でやる場合、かなり手間がかかる上に、工程が多いためミスも発生する可能性が高いです。
このように、作業効率や作業品質の観点から自動のBIシステムを構築することは非常に有効です。
また、手間がかからないことから、しっかりデータを見る管理が行き届き、様々な業務上のトラブル未然防止が図れます。
このように、業務管理の強化の観点からもBIシステムを構築することは大変有益です。
また、一連のシステム構築作業はかなり手間に感じられたかと思います。しかし、一つこのBIシステムを構築すると、簡単に次のデータの見える化もできるようになります。
そうすると次々とBIシステムの資産が増えていき、いつの間にか手作業では到底到達できないようなレベルの管理が実現することになります。
皆さんも是非一歩を踏み出してBIシステムの構築に取り組んでみて下さい。
