Power BIからのSQL Server(オンプレミス)接続

Microsoft社が提供するBI(Business Intelligence)ツール「Power BI」のデータソースとして、同社のデータベース「SQL Server」(オンプレミス版)を使用する場合の設定例を紹介します。

・操作環境：
　・OS：Windows 11
　・Webブラウザー：Edge
・DB環境：SQL Server Express 2022、SQL Server Management Studio 20.2
・BI構成：Power BI Desktop、Power BI Service(Fabric無料版)(※)
※この例ではPower BI Serviceで個人のワークスペースを使用するため、Power BI Pro以上の有料ライセンスは必要となりません。しかし、他メンバーと共有するためのワークスペースを作成する場合は、有料ライセンスへのアップグレードが必要となります

この例で扱うシステム構成について

この例では、以下のようなシステム構成で、Power BIからのSQL Server(オンプレミス)接続を行います。

On-premises data gatewayは、Power BIだけでなくPower Apps等の他のサービスでも使用でき、オンプレミスのデータベースとの接続を担います。
※On-premises data gatewayに関する公式記事：
　オンプレミス データ ゲートウェイとは | Microsoft Learn

SQL Serverをインストールする

SQL Server Expressは無料のオンプレミス版SQL Serverです。
以下ダウンロードサイトの少し下の方にある無料の専用エディション(Express)のモジュールをダウンロードします。
※SQL Serverダウンロード：SQL Server ダウンロード | Microsoft
ダウンロードが完了したら、ファイルをダブルクリックしてインストールを開始します。

[基本]を選択します。

[同意する]を選択します。

[インストール]を選択します。

[SSMSのインストール]を選択します。

[SQL Server Management Studio (SSMS) xx.x のダウンロード]を選択します。
ダウンロードが完了したら、ファイルをダブルクリックしてインストールを開始します。

[Install]を選択します。

[Close]を選択します。

SQL Serverのインストール画面に戻り、[閉じる]を選択します。
終了確認のメッセージが表示されたら、[はい]を選択します。

SQL Serverを初期設定する

スタートメニューから工程2でインストールした[SQL Server Management Studio]を選択します(見つけにくい場合はキーワード検索します)。

初回はSQL Serverを導入したデバイスのWindowsアカウントでサインインします。
・Server type：Database Engine
・Server name：リスト選択からこのSQL Serverを選択
・Authentication：Windows Authentication
・Encryption：Mandatory(必須)
・Trust server certificate：チェックをつける

左側ツリーの一番上の行で右クリックして[Properties]を選択します。
[Security]を選択し、[SQL Server and Windows Authentication mode]を入力し、[OK]を選択します。
これにより、Windows認証に加え、SQL Serverのユーザー/パスワードで認証できるようになります。

この例で使用するデータベースを作成します。
左側ツリーの[Database]で右クリックして[New Database]を選択します。
[Database name]を適宜入力し(この例ではtest-dbとします)、[OK]を選択します。

作成したデータベースの管理ユーザーを作成します。
左側ツリーの[Security>Logins]で右クリックして[New Login]を選択します。
以下の通り設定し、[OK]を選択します。
・Login name：(適宜入力)
・SQL Server authentication：選択
・Password(二箇所)：(適宜入力)
・チェックボックス：(すべて外す)
・Default database：(先ほど作成したデータベースを選択)

作成したユーザーに作成したデータベースの管理権限を割り当てます。
左側ツリーの[Security>Logins>(作成したユーザー)]で右クリックして[Properties]を選択します。
[User Mapping]を選択し、以下の通り設定し、[OK]を選択します。
・Users mapped to this login：(作成したデータベース)
・Database role membership for (データベース名)：db_owner,public

SQL ServerにPower BIで使うテーブル・データを用意する

データベースから切断し、先ほど作成したユーザーで接続し直します。
・Server type：Database Engine
・Server name：初回接続時と同じSQL Serverを選択
・Authentication：SQL Server Authentication
・Login：工程3で作成したユーザーの名前
・Password：工程3で作成したユーザーのパスワード
・Encyption：Optional
・Trust server certificate：チェックを外す

以前の記事で、Excel VBAでe-Stat APIを使って政府統計データを取得する方法を紹介しました。今回はその時の取得データをデータベースに設定します。
左側ツリーの[Database>(作成したデータベース)]を選択し、[New Query]を選択し、テーブル作成用のCREATE文を入力し、[Execute]を選択します。
エラーがなければこれでテーブルが作成されます。
※この記事はオンプレミスのデータベースへの接続方法の紹介が主旨のため、CREATE文の説明は割愛します

[New Query]を選択し、データ追加用のINSERT文を入力し、[Execute]を選択します。
エラーがなければこれでデータが追加されます。
※この記事はオンプレミスのデータベースへの接続方法の紹介が主旨のため、INSERT文の説明は割愛します

On-premises data gatewayをインストールする

以下ダウンロードサイトからモジュールをダウンロードします。
※On-premises data gatewayダウンロード：Download On-premises data gateway - 日本語 from Official Microsoft Download Center

ダウンロードが完了したら、ファイルをダブルクリックしてインストールを開始します。

[次のものに同意します]にチェックをつけ、[インストール]を選択します。
ゲートウェイ管理者(この例ではシステム管理者)のメールアドレスを入力し、[サインイン]を選択します。

先ほど指定したメールアドレスの組織アカウントでサインインを行います。
サインインに成功したら、[このコンピューターに新しい]ゲートウェイを登録します。]を選択し、[次へ]を選択します。

ゲートウェイ名と回復キーを適宜設定し、[構成]を選択します。回復キーは忘れずに控えておきます。
[閉じる]を選択します。

Power BI Desktopをインストールし、Power BI Servicesの設定を行う

Microsoft Storeアプリを起動し、検索欄に[powerbi]と入力し、[Power BI Desktop]が表示されたらこれを選択します。

[入手]を選択します。
インストールが完了すると、ボタンの名前が[開く]に変わりますので、これを選択します。

Power BI Desktopが起動すると、メールアドレスの入力を求められますので、レポート作成者(この例ではシステム管理者)のメールアドレスを入力し、[続行]を選択します。

先ほど指定したメールアドレスの組織アカウントでサインインを行います。

Power BIを初めて使用する場合、Microsoft Fabric freeの使用が開始されます。
①の画面で[続ける]を選択します。
②の画面で[勤務先の電話番号]を適宜入力し、[作業の開始]を選択します。
③の画面で[作業の開始]を選択します。

Webブラウザーが起動し、Power BI Serviceのホーム画面が表示されます。
右上の設定アイコンを選択し、[接続とゲートウェイの管理]を選択します。
※Power BI Serviceのホーム画面：Power BI

[接続]タブを選択し、[新規]を選択します。
[オンプレミス]を選択し、以下の通り設定し、[作成]を選択します。
・ゲートウェイクラスター名：(工程5で設定したOn-premises data gateway)
・接続名：(適宜入力)
・接続の種類：SQL Server
・サーバー：(工程3のSQL Server名)
・データベース：(工程3で作成したデータベース名)
・認証方法：基本
・ユーザー名： (工程3で作成したユーザー名)
・パスワード：(工程3で作成したユーザーのパスワード)

[xxx(接続名)が作成済]と表示され、元の画面にこれが表示されれば問題ありません。

こちらは参考情報です。
[オンプレミスデータゲートウェイ]タブを選択すると、工程5で設定したOn-premises data gatewayが表示されます。

Power BI DesktopからSQL Serverに接続し、レポートを作成・発行する

Power BI Desktopの画面に戻り、データソース[SQL Server]を選択します。
SQL Server名と工程3で作成したデータベース名を入力し、データ接続モードは[インポート]を選択し(この例ではリアルタイム更新ではなく日次更新を想定しており、[DirectQuery]ではなく[インポート]とします)、[OK]を選択します。
※データ接続モードに関する公式記事：
　Power BI の DirectQuery - Power BI | Microsoft Learn

工程4で作成したテーブルを選択し、[読み込み]を選択します。

右側のデータペインにインポートされたテーブルが表示されます。
このテーブルデータを使ってレポートを作成することになります。
※この記事はオンプレミスのデータベースへの接続方法の紹介が主旨のため、レポート作成の操作説明は割愛します

レポート作成が終わったら、左上の[ファイル]を選択します。

[発行>Power BIへ発行]を選択します。
宛先に[マイワークスペース]を選択し、[選択]ボタンを押します。
[成功しました!]と表示されたら、その下の[Power BIで’xxxxx’を開く]を選択します。
※この記事の冒頭で注記した通り、他メンバーとの共有のため通常のワークスペースに発行する場合は、Power BIの有料ライセンスが必要となります

Power BI(クラウド上)で発行したレポートを確認する

Webブラウザーが起動し、Power BI Service上で先ほど作成したレポートが表示されます。
左メニューの[マイワークスペース]を選択します。

発行したレポートのセマンティック モデルの行で、[更新のスケジュール設定]アイコンを選択します。

[ゲートウェイとクラウド接続]を展開し、[マップ先]として工程6の最後の方で作成した接続を選択し、[適用]を選択します。

同じ画面の少し下にある[最新の情報に更新]を展開し、以下の通り設定し、[適用]を選択します。
・タイムゾーン：(UTC+09:00)大阪、札幌、東京
・情報更新スケジュールの構成：オン
・更新の頻度：毎日
・時刻：6:00AM
この設定がSQL Serverからのデータ更新の頻度と時刻になります。

おわりに

SQL Serverにはクラウド版もありますが、無料の試用期間が設けられているとは言え、試用期間が終了すれば有料となります。今回は極力費用をかけない想定とし、オンプレミスの無料版(Express)を使用した設定方法を紹介しました。設定は少々手間ですが、費用がかからないので、お試し利用や小規模組織での限定的な利用に向いているかもしれません。

この記事ではSQL Serverの管理方法やSQLの実行、Power BIのレポート作成の説明は行いませんでした。Power BIのレポート作成については、備忘録を兼ねてちょっとした小技のようなものを今後紹介できたらと思います。