FIXER Tech Blog - Power Platform

FIXER cloud.config Tech Blog

SQL Serverの階層型DBをPowerBIで可視化、Excel出力する

　本記事はFIXERが提供する「cloud.config Tech Blog」に掲載された「PowerBI　SQL Serverのススメ」を再編集したものです。

　Power BIといえば、手軽にExcelデータなどを視覚化するために使われることが多いですが、逆にリレーションの張られたDBを手っ取り早く可視化・分析するためにも有効です。PowerBIは動的で見やすく、多くのユーザーに共有できるため手軽なUIのように利用することができます。

　既に実際に使われているDBを閲覧するにも便利ですし、非エンジニアにも手軽に共有できる点がよいです。また、Excelファイルを簡単に出力できるため、Excelの代わりに共有のマスタとして扱うこともできると思います。

　今回、簡単なSQLやBIの可視化方法は既知として、詳しい機能や手順には簡単にしか触れませんが、実際にSQL Serverの階層型DBとPowerBIの接続を行い、実際にどんなデータがエクスポートできるかを見ていきます。

1. SQL DBとクエリを作成

　まずは、Azure SQL Serverを立ててテーブルを作成していきます。

　どんなテーブルがいいかChatGPTに聞いたところ、販売管理を勧められたので作ってみます（列が増えると面倒なのでいくつか削ります）。

　実際に作ったテーブルがこちら

　BIを作るまえに、データソース指定するためのSQLクエリを考えておきます。

　今回は、顧客ごとの製品の売上個数と売上金額を見れるようなクエリを使ってみます。

select s.[date],
c.name ,
p.name,
p.price,
s.quantity,
p.price * s.quantity as total_amount
from
Sales s
inner join Customer c ON
s.customer_id = c.id
inner join Product p ON
s.product_id = p.id

　ちなみに実行するとこんな感じです。

2. Power BI レポートの作成

　次に、上記で作ったデータソースを使ってBIを作っていきます。PowerBIにはローカルで動くデスクトップアプリと、ウェブアプリがあります。

　作るのはローカルが向いているので、デスクトップアプリで作って「発行」という操作を行ないウェブアプリ上に公開して共有することにします。

　まずはPowerBI Desktopを開き、上部のSQLserverを選択します。

　接続するSQL Serverの情報と、クエリを入力します。

　この時テーブルをそのままインポートする場合は、クエリは空欄でOKです。接続モードにはインポートと、DirectQueryがあります。

　DirectQueryは、リアルタイムでのデータ更新が可能です。インポートは、1日8回or48回程度（ライセンスに依る）の自動更新しかできないですが、DirectQueryに比べて軽い、制限が少ないなどの利点があります。

　ここではインポートを使用します。OKを押下します。

　初回のみ、認証情報を求められます。認証には、Windows認証、SQL認証、AAD認証があり選択することができます。

　ここではSQL認証を使用します。認証情報を入力し、接続を押下します。

　インポートするデータのプレビューが表示されます。問題なければ読み込みを押下します。

　データのインポートが完了しました。このデータを使ってBIを作ります。できたものがこちら。

　このようにDBのデータを見やすく表示できるのはいいですね。見たいデータが可視化できたら発行していきます。

　ホームタブの右上。[発行]を選択し、発行する環境を選択し、［選択］を押下します。

　発行されました。

　ウェブアプリから、発行された環境に行ってみます。

　無事発行されていますが、次の更新がN/Aになっています。

　このままだと自動更新が行なえないため、設定していきます。

　発行されたデータセットを選択し、［⋯］⇒［設定］の順に選択します。

　設定画面に遷移し、データソースの資格情報を見ると、DBへの接続ができていないことが分かります。

　［資格情報を編集］を選択し、資格情報を入力します。黄色いエラー表示が消えればOKです。

　次にスケジュールされている更新を開き、トグルをオンにします。

　更新の頻度を選択し、更新したい時間を追加します。適応を押して、もう一度ワークスペースに戻りましょう。

　次の更新が、正しく表示されていれば完了です。

　お疲れ様でした。

3. Excelデータ出力

　実際にできたBIと、どんなデータが出力できるかを確かめます。

　先ほど発行したレポートを開き、データ出力したい図にカーソルを合わせます。

　［⋯］⇒［データのエクスポート］の順に選択します。ダイアログが出現するので形式を選んでエクスポートします。

　データの出力方法は2種類あります。

　まずは【概算データ】

　これは、現在の図で使われているデータセットをエクスポートする形式になります。実際に見てみるとこんな感じ、図で使われている列しか出力されませんが、SQLクエリの結果そのままですね。

　次に【現在のレイアウト】のデータ

　これは、今図に表示されているテーブルをそのままエクスポートする形式になります。

　試しに、図上で「株式会社A」のみドリルダウンしてからエクスポートしてみます。こんな感じ、とても直感的で使いやすいですね。

　通常、DBから好きにデータを落としたければ、権限をもらって自分でクエリを書くか、データを閲覧、出力できるUIを作る必要がありますが、発行したBIは他のユーザーに共有することができるので、簡単に共有した全ユーザーが好きに閲覧やデータの出力ができるというわけです。テーブルの閲覧や、共有資料などを作るのに便利ですね。

　以上です。お疲れ様でした。

萩原 広揮／FIXER
M.Sc. 物理と数学と情報に興味があります。
Azure / Quantum Inf. / stats. / ML

[転載元]
　PowerBI　SQL Serverのススメ

■関連サイト

• cloud.config Tech Blogのエントリー
• cloud.config Tech Blog
• FIXER