Power BI Direct Query機能の使用方法 – techsyncer

Power BIレポートを作成する際、閲覧者はレポートが定期的にリフレッシュされ、データが常に更新されることを期待しています。どのように実現するのでしょうか？レポートを手動で更新することもできますが、Power BI Direct Queryはスケジュールされたリフレッシュというより優れた機能を提供しています。

このチュートリアルでは、サーバーに保存されたデータセットに接続し、データに対してクエリを実行することで、Power BI Direct Query機能を活用する方法を学びます。

ぜひお楽しみにし、生産性を向上させながら時間を節約しましょう！

前提条件

このチュートリアルは実際のデモンストレーションです。参加するには、以下のものを用意してください：

Power BIデスクトップ – このチュートリアルではPower BIデスクトップバージョン2.109.1021.0を使用します。

SQLサーバー。

A code editor – This tutorial uses VS code version 1.71.

Microsoftオンプレミスデータゲートウェイ。

SQL Server Management Studio（SSMS）- このチュートリアルでは、SQL Server Management Studio 18.12.1を使用しています。

Power BI Direct Queryで管理するSQLデータベースを作成する

Power Bi Direct Queryを使用すると、データセットに直接接続し、プロジェクトにライブデータをアップロードするオプションが提供されます。ただし、SQLデータベースと接続を作成する前に、正しい詳細を取得するために、サーバー名とデータソース名を知っておく必要があります。

SQLデータベースを作成するには、次の手順に従ってください：

1. 検索バーでODBCを検索し、ODBCデータソース管理者（64ビット）を探してクリックして開きます。

2. 次に、ODBCデータソース管理者ウィンドウのSystem DSNタブに移動し、新しいデータソースを追加するために追加をクリックします。

3. 以下のリストからSQL Serverドライバを選択し、SQLデータソースを作成するために完了をクリックします。

DirectQueryはすべてのデータソースをサポートしていません。MySQLはサポートされていませんが、SQLはサポートされています。

4. 今、データソース（MssqlDataSource）の名前を付け、マシンにインストールされている SQL インスタンス（POWERSERVER\POWERSERVER）を選択し、[完了] をクリックしてください。

Python 接続文字列に必要な SQL Server とデータソース名をメモしてください。

5. データソースの詳細を確認し、[データソースのテスト] をクリックしてデータソースをテストしてください。

接続が正常な場合、以下に示すように [テストが正常に完了しました] メッセージが表示されます。

Verifying testing the data source completed

SQL サーバーに接続する

SQL サーバーのデータソースを作成したので、Python コードでそのデータソースを使用して接続文字列を作成します。

1. Visual Studio を起動し、CTRL+SHFT+` を押して新しいターミナルを開きます。

2. 次に、以下の sqlcmd コマンドを実行して SQL サーバーインスタンスにログインします。

sqlcmd -S SQL_SERVER -E

3. ログインしたら、次のクエリを実行して MSSQLDB という新しいデータベースを作成します（CREATE DATABASE）。

CREATE DATABASE MSSQLDB;
GO

4. Python ファイル DBconnect.py を作成し、以下のコードを追加して、Python で SQLAlchemy ORM を使用して SQL データベースに接続できるようにします。

エンジンは SQLAlchemy アプリケーションの開始点です。エンジンは Python Database API Specification（DBAPI）の接続プールと方言を記述します。Python DBAPI は、すべてのデータベース接続パッケージの共通の使用パターンを定義するための Python 内の仕様です。この仕様は、指定されたデータベースと通信します。

//DBconnect.py
import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib
import pyodbc
# 接続文字列を作成し、テキストをフォーマットするためにurllibを使用します。
conn = urllib.parse.quote_plus(
# 接続文字列の値はデータソースから取得されます
'Data Source Name=MssqlDataSource;'

'Driver={SQL Server};'

'Server=POWERSERVER\POWERSERVER;'

'Database=MSSQLDB;'

'Trusted_connection=yes;'

)

try:
# sqlalchemyエンジンで接続を作成します
	coxn = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(conn))
	print("Passed")
except:
	print("failed!")

5. メインフォルダにCreateTable.pyという名前のPythonファイルを作成し、以下のコードを追加して実行します。以下のコードは、SQLデータベースにstudentsという名前のテーブルを作成します。

//CreateTable.py
from DBconnect import coxn
from sqlalchemy import Table, Column, Integer, String, MetaData

# テーブルの定義を含むMetadata構造を作成します
  # インデックス、ビュー、トリガーなどの関連オブジェクトを含みます
meta = MetaData()

# テーブルを作成するための通常のSQL構文でCREATE TABLEを表します。
students = Table(
'students', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)

# エンジンオブジェクトを使用してすべての定義されたテーブルオブジェクトを作成します
  # および情報をメタデータに格納します。
meta.create_all(coxn)

6. 次に、SSMSを開き、作成されたデータベース、テーブル、および列を確認します。

7. WriteToTable.pyという名前のPythonファイルを作成し、以下のコードを追加して実行します。

このコードには、データベーステーブルに値を書き込むためのロジックが含まれています。

//WriteToTable.py
# CreateTable.pyファイルからstudentsテーブルをインポートします。
from CreateTable import students
# DBconnect.pyファイルからcoxn接続文字列をインポートします。
from DBconnect import coxn
# 複数の値をstudentsテーブルに挿入します。
ins = students.insert().values([
   {'name':'Bob','lastname':'Marley'},
   {'name':'Bob','lastname':'Dylan'}
])
conn = coxn.connect()
conn.execute(ins)

オンプレミスデータゲートウェイの準備

データベースへの接続文字列を作成し、データベースへの接続に成功したら、データゲートウェイを作成する必要があります。このデータゲートウェイは、データベースをPower BIに接続する役割を果たします。

1. オンプレミスデータゲートウェイを開き、サインインします。

2. オンプレミスデータゲートウェイの状態を確認し、以下に示すようにオンラインであることを確認します。

Checking if the On-premises data gateway is online

3. 次に、Power BIを開き、ホームリボンタブの下にある「データの取得」をクリックし、SQL Serverを選択してデータソースをPower BIに接続します。

4. 以下を使用してDirecQueryに接続します：

サーバー名とデータベース名を対応するフィールドに挿入してください。DirectQueryオプションが選択されていることを確認します。「OK」をクリックしてDirectQueryに接続します。

5. データベースからテーブル（students）を選択し、データを読み込むために「読み込む」をクリックします。

6. 次に、データを表示するためにテーブルビジュアルを使用するためにテーブルアイコンをクリックします。

以下のテーブルは、データベーステーブルから読み込まれたデータを示しています。

7. ファイルメニューをクリックしてPower BIで実行するアクションにアクセスします。

8. 今、公開をクリックしてPower BIにデータレポートを公開します。

9. パブリッシングが成功したら、を開くために「オープン」をクリックし、ブラウザをPower BIで最近行ったアクティビティのリストにリダイレクトします（ステップ10）。

10. 以下に示すように、レポートを開くためにレポートをクリックしてください。

最初のセットアップ時に、プロジェクトのデータソースをPower BIのデータゲートウェイに接続していないため、以下のようなゲートウェイの問題に遭遇するかもしれません。

11. アクション列の下にある設定アイコンをクリックして新しいゲートウェイ接続を追加するためにクリックしてください。これにより、ゲートウェイの構成エラーを修正できます。

Initiating adding a new gateway connection

12. 新しいゲートウェイ接続を構成し、「作成」をクリックしてデータソースとゲートウェイの間に新しい接続を作成します。

13. 最後に、ゲートウェイが実行されているのを確認したら、Maps toドロップダウンフィールドからデータソース名（MssqlDataSource）を選択し、「適用」をクリックしてください。

データレポートの表示と管理

Power BIとゲートウェイの間に接続を確立した後、レポートを表示し、リフレッシュスケジュールを設定できます。

1. Power BIのレポートホームページに移動してください。

Power Bi Direct Queryのパフォーマンスは基礎となるデータセットのソースに依存します。データセットがリクエストに応答するのにかかる時間が、ユーザーがレポートを表示する速さ、または最初にレポートを正常に表示できるかどうかを決定します。

2. 次に、以下に示すようにレポートをクリックして開いてください。

レポートを開いたら、以下に示すデータが表示されるはずです。

3. レポートホームに戻り、リフレッシュ → スケジュールリフレッシュをクリックしてレポートの定期的なリフレッシュを設定してください。

Power Bi Direct Query は、ライブレポートを作成するだけでなく、スケジュールされた更新を設定し、レポートを自動的に更新することができます。

4. 今、ドロップダウンフィールドから選択した更新間隔を選択し、変更を適用するには、[適用] をクリックします。

5. Python スクリプトの WriteToTable.py を開き、以下のコードを追加して、データベースにさらにエントリを挿入し、スクリプトを実行します。

このコードを実行しても出力は提供されませんが、後続の手順でテーブルに挿入された値を確認します。

SAP Business Warehouse のような多次元ソースに対する Power Query Editor はありませんので、データを利用するための可能性が制限されます。

//WriteToTable.py
# CreateTable.py ファイルから学生テーブルをインポートします。
from CreateTable import students
# DBconnect.py ファイルから coxn 接続文字列をインポートします。
from DBconnect import coxn

# 学生テーブルに複数の値を挿入します
ins = students.insert().values([
{'name':'Damian','lastname':'Marley'},
{'name':'Rita','lastname':'Marley'},
{'name':'Ziggy','lastname':'Marley'},
{'name':'Sam','lastname':'Dylan'},
{'name':'Jakob','lastname':'Dylan'},
{'name':'Maria','lastname':'Dylan'}
])
# 接続カーソルを作成します。
conn = coxn.connect()
# insert() コマンドを実行します。
conn.execute(ins)

6. 次に、Power BI ブラウザに切り替えて、[データセット + データフロー] タブをクリックします。

すべてがうまくいけば、以下のスクリーンショットのように、データセットの最後の更新時刻と次の更新時刻が表示されます。

Power Bi Direct Query は変更やフォーマットに敏感です。Power Query Editor に複雑な手順が含まれている場合は、エラーが発生する可能性があります。

Verifying the last refreshed time and next refresh of the dataset

期待通り、リロードは15分ごとに自動的に行われ、次のリフレッシュはさらに15分後に設定されています。

結論

このチュートリアルでは、Power Bi Direct Query機能を使用して、サーバーに保存されているデータセットに接続する方法を学びました。また、データに直接クエリを実行する方法にも触れました。

レポートの作成は、データの収集、クリーニング、データソースへのアップロード、Power BIへのデータのロード、Power BIビジュアルの作業など、長いプロセスです。このプロセスは述べられています。ただし、その後、データセットを手動で更新するか、設定されたスケジュールに従ってデータを自動的に更新することができます。

Power BI Direct Queryは、データセット内のデータを処理および操作する際に欠点があります。ただし、この機能を使用する際に、一度レポートを作成してバックエンドでの作業を再び行う必要がないという事実は、非常に大きなインセンティブです。