FIXER Tech Blog - Power Platform
FIXER cloud.config Tech Blog
SQL Serverの階層型DBをPowerBIで可視化、Excel出力する
2023年01月31日 16時00分更新
本記事は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のススメ
この連載の記事
-
TECH
Power BI Tips - 任意の順番で並び替える方法 -
TECH
Power AppsのUser().FullNameで取得した苗字/名前が逆になる問題の解決方法 -
TECH
手書きのイラストがアプリになる!? Power Appsのエクスプレスデザインを試してみた! -
TECH
Power QueryでExcelの複数ファイルを一括で整形して結合する方法 -
TECH
Power Queryでシート名が異なるデータを結合する方法 -
TECH
Power QueryでExcelのセル内改行データを縦に分割する方法 -
TECH
Power Queryで設定しておきたい便利なオプション設定 -
TECH
MCP受験料が無料に! 今年も「Microsoft Learn Cloud Skills Challenge」開幕! -
TECH
AI Builderの使用容量「クレジット」についてのまとめ -
TECH
Power Queryでフォルダ内から最新ファイルのデータを取得する方法 -
TECH
Power Queryの「重複行の削除」で下の行(最新行)を残す簡単な方法 - この連載の一覧へ