#概要
- PowerBIサービスでオンプレミスデータを自動更新するには「オンプレミス データ ゲートウェイ」が必要です。
- しかしながら企業によってはセキュリティ上の懸念からこれのインストールが禁止されており、更新は手動といった場合もあるかと思います。
- オンプレデータに接続したExcelをSharePoint上に配置し、これを定期的にPowerShellで起動して更新することで、ゲートウェイを実現しようという発想です。
※PADでExcelの定期起動を試みましたが有償プラン必要につき断念、方法だけ載せました。
#シチュエーション(昭和企業)
- OPDG？オンプレデータを自動でクラウドに？危なそうだから個別のそういったものは許可できないなあ・・
- ゲートウェイを複数人で共有すると、他の人からローカルのデータソースにアクセスできるとな？それは危険だねえ・・
- オンプレミスデータゲートウェイ禁止ね。
- むむむ・・・
流石にこれは冗談ですが、実際大規模テナントなんかだとガバナンス体制が～という理由で運用ルールが決まるまで利用制限・利用禁止というのはよくあります。
#方法
- ExcelのPowerQueryを使って任意のオンプレデータに接続する(昭和企業製のExcelやAccessデータベースなど)
- SharePointのドキュメントライブラリ上に配置する
- PowerBIからSharePointに接続する
- 定期的にPowerShellからSharePoint上のExcelを開いてオンプレデータを更新する
##Excelの準備
###①PowerQueryでデータ接続
- ExcelのPowerQueryを使用して好きなデータソースに接続してテーブルを読み込みます。
- 今回の例では共有フォルダに置いたExcelに入力された以下のような表で考えてみます。 簡単な分析なのでシングルテーブルでいきます。
###②ファイルをSharePointに置いてパスを取得
- オンプレデータを読み込んだExcelを任意のSharePointのドキュメントライブラリにアップロードします。
- SharePointのファイルにチェック→ⓘ(詳細ウィンドウ)→パスのコピーから、ファイルのパスをコピーしてメモしておきます。
ちなみにこのパスとURIスキーマを使うとデスクトップからSharePoint上のファイルを開けるショートカットを作成できます。
##PowerBIの準備
###①データ接続の準備
- PowerBIDesktopで新規ファイル作成し「SharePointフォルダー」コネクタを使用してSharePointのExcelに接続します。
- サイトのトップURLを貼り付けるとファイル一覧が現れるので、Binaryをクリックしてファイルを抽出します。
- KindがTableとなっている行のTableをクリックしてテーブルデータを読み込みます。
- あとは適当に前処理してビジュアルを作ります。
PowerBIからドキュメントライブラリへの接続は以下の記事を参考にさせて頂きました。
###②データ接続
- PowerBIを発行してサービス上にアップロードします。 今回は検証としてマイワークスペースを使いました。
- データセット＋データフローのタブから、データソース→設定→データソースの資格情報を設定します。
- 認証はOAuth2で設定しSharePointのファイルからデータ更新できるようにします。
- 必要に応じデータ更新のスケジュールも設定します。
##PowerShellの設定
①スクリプトの作成
以下のようなPowerShellスクリプトを作成します。
SharePoint上のExcel自動更新.ps1
function main(){
try{
$excel = Start-Excel
}catch{
throw "excelを起動できませんでした。"
}
$book = $excel.Workbooks.Open('https://コピーしたExcelファイルのSharePointパスを入力')
#ここはPowerQuery接続を作成したシート名、テーブル名に変更
$table = $book.Worksheets('売上明細').ListObjects('t_売上明細')
$table.Refresh()
#データ更新の完了を待機
while ($table.QueryTable.Refreshing -eq $true)
{
#0.5秒待機
Start-Sleep -m 500
}
Write-Host "Excelの更新が完了しました。"
$book.Close($true)
$excel.Quit()
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($table)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($book)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
function Start-Excel{
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
return $excel
}
main
②定期実行
後は作成したスクリプトをタスクスケジューラに登録しておくだけです。
PowerBIで設定した更新時間より少し前にしておきます。
#あとがき
- 当然ですがデスクトップのExcelはM365と同じアカウントでログインしておく必要があります。
- 最初はPowerShellではなくPAD(PowerAutomateDesktop)で自動的に開いて更新を考えたいたのですが、PADは有料プランかつOPDGがないと定期起動できないと気づき、急遽PowerShellで代替しました。
#定期実行できず断念したPADでのやり方
PADでは現状SharePoint上のExcelを直接開けそうにないですが、これと起動中のExcel取得アクションを組合わると、SharePoint上のExcelファイルも自動化できそうです。
- SharePoint上のExcelは、ブックを開いたときに自動的にデータ更新される設定にする
- デスクトップのショートカットをクリックしてExcelを開く
※上記のリンクを参考に、SharePointへ配置したファイルをデスクトップアプリで開くショートカットを作成しておき、それを開きます。
- 画像を待機アクションで、Excelのステータスバーの読込完状態をキャプチャして待機する
読込中→
読込完→
- ウィンドウを閉じるアクションでExcelを終了する