- 操作環境：
- 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以上の有料ライセンスは必要となりません。しかし、他メンバーと共有するためのワークスペースを作成する場合は、有料ライセンスへのアップグレードが必要となります
- システム構成について
- SQL Serverインストール
- SQL Server初期設定
- SQL Serverテーブル・データ作成
- On-premises data gatewayインストール
- Power BI Desktopインストール、Power BI Service設定
- Power BI DesktopからSQL Server接続、レポート作成・発行
- Power BI Service発行レポート確認
- おわりに
システム構成について
この例では、以下のようなシステム構成で、Power BIからのSQL Server(オンプレミス)接続を行います。
On-premises data gatewayは、Power BIだけでなくPower Apps等の他のサービスでも使用でき、オンプレミスのデータベースとの接続を担います。
SQL Serverインストール
SQL Server Expressは無料のオンプレミス版SQL Serverです。
ダウンロードサイトの少し下の方にある無料の専用エディション(Express)のモジュールをダウンロードします。
[基本]を選択します。
[同意する]を選択します。
[インストール]を選択します。
[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テーブル・データ作成
データベースから切断し、作成した管理ユーザーで接続し直します。
- 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]を選択します。
エラーがなければこれでテーブルが作成されます。
[New Query]を選択し、データ追加用のINSERT文を入力し、[Execute]を選択します。
エラーがなければこれでデータが追加されます。
On-premises data gatewayインストール
ダウンロードサイトからモジュールをダウンロードします。
ダウンロードが完了したら、ファイルをダブルクリックしてインストールを開始します。
[次のものに同意します]にチェックをつけ、[インストール]を選択します。
ゲートウェイ管理者(この例ではシステム管理者)のメールアドレスを入力し、[サインイン]を選択します。
図表5-2で指定したゲートウェイ管理者のアカウントでサインインを行います。
サインインに成功したら、[このコンピューターに新しい]ゲートウェイを登録します。]を選択し、[次へ]を選択します。
ゲートウェイ名と回復キーを適宜設定し、[構成]を選択します。回復キーは忘れずに控えておきます。
[閉じる]を選択します。
Power BI Desktopインストール、Power BI Service設定
Microsoft Storeアプリを起動し、検索欄に[powerbi]と入力し、[Power BI Desktop]が表示されたらこれを選択します。
[入手]を選択します。
インストールが完了すると、ボタンの名前が[開く]に変わりますので、これを選択します。
Power BI Desktopが起動すると、メールアドレスの入力を求められますので、レポート作成者(この例ではシステム管理者)のメールアドレスを入力し、[続行]を選択します。
図表6-3で指定したレポート管理者のアカウントでサインインを行います。
Power BIを初めて使用する場合、Microsoft Fabric freeの使用が開始されます。
①の画面で[続ける]を選択します。
②の画面で[勤務先の電話番号]を適宜入力し、[作業の開始]を選択します。
③の画面で[作業の開始]を選択します。
Webブラウザーが起動し、Power BI Serviceのホーム画面が表示されます。
右上の設定アイコンを選択し、[接続とゲートウェイの管理]を選択します。
[接続]タブを選択し、[新規]を選択します。
[オンプレミス]を選択し、以下の通り設定し、[作成]を選択します。
- ゲートウェイクラスター名：(工程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]を選択します。
工程4で作成したテーブルを選択し、[読み込み]を選択します。
右側のデータペインにインポートされたテーブルが表示されます。
このテーブルデータを使ってレポートを作成することになります。
レポート作成が終わったら、左上の[ファイル]を選択します。
[発行>Power BIへ発行]を選択します。
宛先に[マイワークスペース]を選択し、[選択]ボタンを押します。
[成功しました!]と表示されたら、その下の[Power BIで’xxxxx’を開く]を選択します。
Power BI Service発行レポート確認
Webブラウザーが起動し、Power BI Service上で発行したレポートが表示されます。
左メニューの[マイワークスペース]を選択します。
発行したレポートのセマンティック モデルの行で、[更新のスケジュール設定]アイコンを選択します。
[ゲートウェイとクラウド接続]を展開し、[マップ先]として工程6の最後の方で作成した接続を選択し、[適用]を選択します。
同じ画面の少し下にある[最新の情報に更新]を展開し、以下の通り設定し、[適用]を選択します。
- タイムゾーン：(UTC+09:00)大阪、札幌、東京
- 情報更新スケジュールの構成：オン
- 更新の頻度：毎日
- 時刻：6:00AM
この設定がSQL Serverからのデータ更新の頻度と時刻になります。
おわりに
SQL Serverにはクラウド版もありますが、無料の試用期間が設けられているとは言え、試用期間が終了すれば有料となります。今回は極力費用をかけない想定とし、オンプレミスの無料版(Express)を使用した設定方法を紹介しました。設定は少々手間ですが、費用がかからないので、お試し利用や小規模組織での限定的な利用に向いています。
この記事ではSQL Serverの管理方法やSQLの実行、Power BIのレポート作成の説明は行いませんでした。Power BIのレポート作成については、備忘録を兼ねてちょっとした小技のようなものを今後紹介予定です。
当ブログ内の関連記事
