- Virtual Tables 概要
- データフローとの違い
- 何が良いの？
- やってみよう
- 動作検証
- 分かったこと
- リレーションシップの作成
- Dataverseを操作した時のSQL Serverの挙動
- 列を増やすとき
- その他
Virtual Tables 概要
Power AppsのVirtual Tablesを使ってオンプレSQL Serverと接続してみました。
Virtual Tablesは「仮想コネクタプロバイダー」によって、対応するデータソースのリストやテーブルを「仮想エンティティ」と呼ばれる形でDataverseのテーブルとして扱えるようにする仕組みのようです？
※まだよく分かっていないので少しずつ理解していきます。
今回のプレビュー機能により接続すると、モデル駆動型アプリからオンプレSQL Serverのデータを読み書きできるようになります！
今後のアップデートにより他のDBMSの仮想コネクタも登場すると思われます。
データフローとの違い
従来からデータフローと呼ばれる仕組みがあり、オンラインPowerQueryを使ってオンプレデータソースと接続することが可能でした。
ただ、こちらは一方的にデータを読み込むだけのもので、データソース側に書き込みはできませんでした。
また、キャンバスアプリにはSQL Server用のコネクタがあるため書き込みも実行できましたが、こちらはモデル駆動型アプリでは使えませんでした。
何が良いの？
Virtual Tablesによって、オンプレSQL Serverをデータベースとしながら、モデル駆動型アプリの強力なローコード開発によってフロントアプリがサクッと作れるという訳です。
社内基幹システムをSQL ServerやOracleなどのオンプレDBMSで運用しており、ERPやローコード開発に移行できなかった企業には朗報ですね。
やってみよう
ブログにしたのですが、下記のdocsやYouTube動画の手順ほぼそのままなので、 細かい画面遷移などは省略します。
仮想コネクタを使用して仮想テーブルを作成する (プレビュー) (Microsoft Dataverse) - Power Apps | Microsoft Learn
Create a Virtual Table for SQL with the Virtual Connector Provider - YouTube
①環境にアドインを追加
VTを作成したい環境に仮想コネクタ プロバイダーをインストールします。
この手順は今後数週間で不要になるようです。
https://appsource.microsoft.com/ja/product/dynamics-365/mscrm.connector_provider?tab=overview
②接続の作成
SQL Serverへの接続を作成します。
事前にオンプレミス データ ゲートウェイのセットアップが必要です。
接続するユーザーは、操作対象テーブルのCRUD権限があるかも確認しておきます。
③ソリューション内に接続参照を追加
ソリューションを新規作成して、新規→その他→接続参照から先ほど作成した接続を呼び出して紐づけます。
仕組みはよくわかりませんが動画の通りに実行します。
Dataverseがどのコネクタを使用してデータソースと接続するかを紐づけるようなステップでしょうか。
作成したものをソリューション内で確認すると「つながり参照」と呼ばれるリソースになっています。
④仮想エンティティ データソースを追加
環境の詳細設定→管理から仮想エンティティ データ ソースを追加します。
先ほど作成したつながり参照を検索して指定します。
データセット値は、SQL Serverの場合は空白で良いようです。
※コネクタの段階でデータベース名を選択しているため？
Excelの場合はファイル名、SharePointの場合はサイトURLを指定するようです。
⑤Dataverseのテーブルから仮想エンティティを作成
ここまでの手順を踏むと、テーブル一覧には「Entity Catalog for ～」の名前のテーブルが作成されているので開いてみます。
すると、SQL Serverデータベース内のテーブル一覧が表示されています。
最初はVirtual Entity Name列しか表示されていないので、その他から以下の列を表示させます。
そして、取り込みたいテーブルの行を以下のように変更します。
- Create or Refresh Entity →Yesに
- Primary key of the Virtual Entity →テーブルの主キーとなる列を指定
- Primary field of the Virtual Entity →Dataverseで主キーにする列を指定。
下記はPower BI用サンプルDBであるContosoRetailデータベースですが、画像のように設定しました。
プレビュー版だからなのか、中々正常にPrimary Keyなどが選択できない、保存できない、といったことがありましたが、根気よく頑張れば上手くいきます。
※何か違う？
⑥仮想エンティティが作成できればOK
テーブル一覧を参照し、SQL Serverのテーブルと同じものが作成されていればOKです。
動作検証
Dataverse→SQL Server
さくっとモデル駆動型アプリを作成して、試しにデータを編集してみます。
ローカルのSSMSからデータを確認してみると、先ほど編集したデータがすぐに反映されています。
SQL Server→Dataverse
今度はAzure Data Studioを使ってローカルのSQL Serverのデータを編集すると・・
モデル駆動型アプリ側(Dataverse)にも反映されています。
どの程度遅延があるか分かりませんが、Azure Data Studioの画面からデータを編集して、
モデル駆動アプリに切り替えてデータ更新を一回行ったころにはしっかり反映されていました。
双方から編集できるということで、データの競合や、ゲートウェイが機能しない場合にどうなるのかなど検証してみたいですね。
分かったこと
- 仮想エンティティで作成される列はデータソースにある列のみで、通常のカスタムテーブルのように作成日、作成者などは勝手に作ってくれない。
→セットしたい場合はモデル駆動型のスクリプトやAutomateで設定する必要あり？ - Dataverseに値を保存しているわけではなく都度SQL発行している、と思われる。→SQL プロファイラーを見るとDataverseの操作によってオンプレ側にSQLが発行されている。
リレーションの作成はできない。外部キー列も数値型として作成されるだけで、N側テーブルが参照できる訳ではない。→できるようです。docsに書いてありました。次で解説します。
リレーションシップの作成
Dataverseには「参照列」があり、データの登録時は参照先のマスターからレコードを検索して値をセットできます。
一方、SQL Serverから読み込んだテーブルの参照列(外部キー列)は、既定だと参照先テーブルのIDが整数型で登録されるだけで、上記のような動作はしてくれません。
ユーザーにIDを入力させるようなやり方は不便なので、手動でリレーションシップを作成します。
リレーションシップは環境の詳細設定 -> 設定 -> カスタマイズ -> システムをカスタマイズする の画面から作成可能なようです。
現在のところ、モダンUIの画面から作成しようとするとエラーが発生します。
以下のドキュメントに沿って作成します。
参照列化したい列を削除→リレーションシップの作成といった手順です。
外部名を削除前の列と一致させる必要があります。
仮想コネクタを使用して仮想テーブルを作成する (プレビュー) (Microsoft Dataverse) - Power Apps | Microsoft Learn
完成すると以下のようにデータ型が「検索」となりました。
これで、外部キー列への入力も、Dataverseと同じように名前などで検索して登録できるようになりました。
Dataverseを操作した時のSQL Serverの挙動
Dataverseやモデル駆動型アプリを開いたとき、接続先であるSQL Serverにどんなアクセスが行われるのかを確認してみました。
結果、どうやらデータは都度参照しにいっているようです。
※Dataverseに一旦値を保存してからバックグラウンドで同期しているわけではない。
テーブルを開いてレコード一覧を表示するとき
Dataverse側のビューでレコードを一覧表示すると、まずテーブルのレコードがtop2048で取得されます。
次に、フィールド内に参照列があると、参照先のテーブルから必要なレコード一覧を取得します。
参照列を多用しすぎると重くなるってことで、SharePointリストなどと考え方は同じかもしれません。
単一レコードを表示するとき
先ほどと同じ要領で単一レコードに絞って、テーブル→参照先テーブルの順でレコードを取得しています。
値を更新するとき
ここまでくると予想通りです。
Dataverseで値を更新して保存すると、SQL Serverに対しupdate文が発行され、
その後更新後のレコードがフェッチされます。
Virtual Tablesが分かってきたような気がします。
仮想コネクタというものはとんでもない技術ですね。
列を増やすとき
データソース側で列を追加したときは、Dataverse側に自動反映はしてくれないよう？なので、手動で列の追加を行います。
外部名をデータソース側の列名と合わせると正常に登録可能でした。
その他
ここからは素人の妄言です。
仮想テーブルは中小企業の救世主？
製造業などでは受注、生産、出荷といった情報を管理するための基幹システムが必要ですが、予算や要件の都合上でシステムを内製してきたところも多いと思います。
そして、その多くは比較的開発が容易な2 層クライアントサーバシステムと呼ばれ、クライアント(フロントエンドアプリケーション)とデータベース(DBMS/SQL Serverなど)を直接接続し、クライアントの操作に応じてSQLを発行してデータベースを直接更新するといった方法ですね。
ここで、フロント側の開発は、コードを使ってフルスクラッチで作っていくか、MS-Accessのようなレガシーツールで開発することとなります。これは生産性が低く、特に近年の業務変化のスピードに追い付くことができませんでした。
SQL ServerなどのDBMSはあくあでミドルウェアなので、Dataverseのように更新者、更新日といった証跡情報は完全自動でマネージしてくれません。また、AzureADのようなユーザー管理の仕組みも独自に作成する必要があります。
※上述の通り現時点では仮想エンティティは証跡情報まで管理してくれない。
かといって、ERPやローコードプラットフォームを導入しようとする場合、DBMSに持っているデータを全てサービス側に移行することが前提となり、そうなると既存のフロントアプリは作り直しが必要です。
オンプレで複雑なシステムを組んでいたり、規模が小さく大きな投資ができない企業だと、最新の開発環境への移行が中々できない状況があると思います。
Virtual Tablesを使うと、既存のオンプレ環境を維持しつつ、ハイブリッドに開発・運用が可能になるため、少しずつDataverseやDynamicsに移行するといったことも可能なのではないでしょうか？
また、このようなシステムは一般的に「情シス」しか構築できませんでしたが、特定のテーブルに限り公開することで、「市民開発者」でもローコード開発によってマスターデータをメンテナンスしたり、トランザクション系のデータを基幹システムに書き込むようなアプリも作成できるのではないでしょうか？
ということで、今後の展開に超期待したい機能です。
仮想テーブルに関する入門情報 (エンティティ) (Microsoft Dataverse) - Power Apps | Microsoft Learn
仮想テーブルは、これまでの外部データを統合するためにカスタマイズされたコードが必要で、不完全な統合、データの重複、大量の開発リソースなどの多くの制約があるクライアント側とサーバー側のアプローチに代わるものです。 さらに、管理者およびシステム カスタマイザーにとって、仮想テーブルの使用により管理および構成が大幅に単純化されます。
