SSIS中刷新Power BI数据集
前面介绍过了怎么在Azure云中刷新Power BI数据集，感兴趣的可以阅读
但有很多公司可能并没有完全上云，比如某些公司还在使用SSIS调用ETL工程，那么有没有办法在本地也实现执行完SSIS包后就刷新数据呢？
之前有介绍过使用python来刷新Power BI数据集，感兴趣的可以阅读
当然，除了使用python,还可以使用power shell 来刷新Power BI数据集，本文也将基于python和power shell来介绍怎么在ssis中实现刷新Power BI数据集
应用设置
创建应用
首先仍然是先创建一个应用，在这里要注意先前的创建应用的网址微软已经弃用了，最新的可以Power BI嵌入式操场[1]查找
目前是
国际：Onboarding Embed Tool (powerbi.com)[2]
国内：Onboarding Embed Tool (powerbi.cn)[3]
选择为客户创建
选择权限，如果我们只需要刷新数据集，只勾选读写数据集权限即可
后面工作区可以不用创建，创建完应用后，登录到Azure门户
国际：https://portal.azure.com
国内：https://portal.azure.cn
登录后找到应用注册，然后找到刚注册的应用程序，添加权限，选择PowerBI，委托的权限，选择Dataset.ReadWrite.All
添加完权限后授权为管理员同意确认
也可以在Azure中创建应用，然后再添加权限
创建安全组
Azure门户中选择Azure Active Directory，然后选择组
创建安全组，创建工作组的时候添加成员，成员就是我们先前创建的应用
管理门户设置
登录Power BI门户，然后设置，管理门户，添加上面创建的安全组，之后返回工作区，在工作区中添加安全组
使用python刷新Power BI数据集
使用python刷新数据集有很多现在的库来获取token，当然也可以使用最原始的方式，使用requests库发送请求。新建python脚本,命名为refreshDataset.py
import requests
data = {
"client_id": "",
"client_secret": "",
"grant_type": "",
"resource": "https://analysis.windows.net/powerbi/api"
}
tenantId = "60c3ffd8-1d3e-40a0-bfb7-83f5388cf0e2"
group = ""
dataset = ""
url = "https://login.microsoftonline.com/" + tenantId + "/oauth2/token"
res = requests.post(url, data=data)
access_token = res.json().get('access_token')
header = {'Authorization': f'Bearer {access_token}'}
refresh_url = 'https://api.powerbi.cn/v1.0/myorg/groups/' + group + '/datasets/' + dataset + '/refreshes'
r = requests.post(url=refresh_url, headers=header)记事本创建bat文件，这里要注意另存时的编码为ansi
cd C:UsersmuxiaoqiDesktop
start python refreshDataset.py
exit
接下来就是最后一步在SSIS中创建执行进程任务，选择bat文件所在路径即可
PowerShell
首先要确保电脑上已安装Power Shell,如果是win10以上版本则无需再安装，但仍需安装一些模块
安装Power BI Cmdlet,文档如下：
Power BI Cmdlet 引用|微软学习 (microsoft.com)[4]
Install-Module -Name MicrosoftPowerBIMgmt安装 Azure Az Power Shell,文档如下
安装 Azure Az PowerShell 模块|微软学习 (microsoft.com)[5]
Install-Module -Name Az -Scope CurrentUser -Repository PSGallery -Force虽然具体代码不同，但代码逻辑和python是一样的，都是先获取token，然后请求API刷新数据，并且需要把执行命令写入bat文件中，然后SSIS调用该bat命令。
以下代码为github上的代码，原文如下：
Automate Power BI Dataset Refresh using PowerShell (thirdrock.com.au)[6]
#AUTHOR: 3rd Rock Business Solutions
$dataset = "bXXXXXX-XXXX-XXXX-XX-XXXXXXbb4"
$groupID = "6cXXXXX-XXXX-XXXX-XXXX-XXXXXX0374" #WorkSpace ID
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$clientid = "7dXXX-XXXX-XXXX-XXXXX-XXXXXXa4c9"
$clientsecret = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXo"
$scope = "https://analysis.windows.net/powerbi/api/.default"
$tenantid = "06XXXXX-XXXX-XXXX-XXXXX-XXXXXXXXdd75"
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("Content-Type", "application/x-www-form-urlencoded")
$body = "client_id=$clientid&client_secret=$clientsecret&scope=$scope&grant_type=client_credentials"
try
{
$response = Invoke-RestMethod "https://login.microsoftonline.com/$tenantid/oauth2/v2.0/token" -Method 'POST' -Headers $headers -Body $body
$token = $response.access_token
$token = "Bearer $token"
# Building Rest API header with authorization token
$authHeader = @{
'Content-Type'='application/json'
'Authorization'= $token
}
$groupsPath = ""
if ($groupID -eq "me") {
$groupsPath = "myorg"
} else {
$groupsPath = "myorg/groups/$groupID"
}
# Trigger refresh of the dataset
$uri = "https://api.powerbi.com/v1.0/$groupsPath/datasets/$dataset/refreshes"
Invoke-RestMethod -Uri $uri –Headers $authHeader –Method POST
$DateTime = Get-Date -Format G
Write-Host "Job Refresh Started at $DateTime"
Start-Sleep -s 30
# Check the status
$count = 1
$success = 0
$statusURL = "https://api.powerbi.com/v1.0/$groupsPath/datasets/$dataset/refreshes"
while($count -le 50)
{
#get job status
$response = Invoke-RestMethod $statusURL -Method 'GET' -Headers $authHeader
$status = $response.value[0].status
$DateTime = Get-Date -Format G
Write-Host "$count. Dataset Refresh Status: $status at $DateTime"
$count = ++$count
if($status -eq "Completed")
{
$success = 1
break
}
elseif($status -eq "Failed")
{
throw $_.Exception
break
}
Start-Sleep -s 60
}
if($success -eq 0)
{
#Refresh token valid for an hour
Write-Host "Program aborded. The job has been running for more than an hour. Please monitor the job directly in the powerbi portal"
}
}
catch {
Write-Host "Status Code:" $_.Exception.Response.StatusCode.value__
Write-Host "Status Description:" $_.Exception.Response.StatusDescription
Write-Host "Error" $_
Write-Host $_.ScriptStackTrace
Write-Host "Error occurred due to previous job has not been finished yet."
throw $_.Exception
}方法2
获取token的时候也可以使用Power BI Cmdlet封装好的方法，参考以下文章
Using SQL Server Agent & PowerShell to Refresh Power BI Dataset with REST API – Top Microsoft Dynamics and NetSuite Partner & Dynamics CRM Consultant in San Diego (alphabold.com)[7]
$clientId = ""
$datasetId = ""
$groupId = ""
$tenantId = ""
$clientSecret = ""
$password = ConvertTo-SecureString $clientSecret -AsPlainText -Force
$Creds = New-Object pscredential $clientId, $password
Login-PowerBIServiceAccount -ServicePrincipal -Credential $Creds -Tenant $tenantId
$headers = Get-PowerBIAccessToken
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
# Refresh the dataset
$restUrl = "https://api.powerbi.com/v1.0/myorg/groups/$groupId/datasets/$datasetId/refreshes"
Invoke-RestMethod -Uri $restUrl -Headers $headers -Method POST -verbose
并且这篇文章给出了另一个思路，在sql server中创建一个job来执行Power Shell脚本，然后再创建一个存储过程根据表的变化来执行该job，当然我们可以直接将SSIS 中的ETL任务和Power Shell中数据刷新的任务直接串联起来。
注意
实际在创建应用的过程中，因为创建方式不同或者权限的不同，遇到了很多问题，有些应用必须在Power BI中加入到安全组中，有些应用则不需要，详细的区别还未找到相关的官方文档说明，再次吐槽微软的文档太不友好了。
引用链接
[1] Power BI嵌入式操场: https://microsoft.github.io/PowerBI-JavaScript/demo/v2-demo/index.html#[2] Onboarding Embed Tool (powerbi.com): https://app.powerbi.com/embedsetup[3] Onboarding Embed Tool (powerbi.cn): https://app.powerbi.cn/embedsetup[4] Power BI Cmdlet 引用|微软学习 (microsoft.com): https://learn.microsoft.com/en-us/powershell/power-bi/overview?view=powerbi-ps[5] 安装 Azure Az PowerShell 模块|微软学习 (microsoft.com): https://learn.microsoft.com/en-us/powershell/azure/install-az-ps?view=azps-9.2.0[6] Automate Power BI Dataset Refresh using PowerShell (thirdrock.com.au): https://www.thirdrock.com.au/post/automate-power-bi-dataset-refresh-using-powershell[7] Using SQL Server Agent & PowerShell to Refresh Power BI Dataset with REST API – Top Microsoft Dynamics and NetSuite Partner & Dynamics CRM Consultant in San Diego (alphabold.com): https://www.alphabold.com/using-sql-server-agent-powershell-to-refresh-power-bi-dataset-with-rest-api/
本篇文章来源于微信公众号: PowerBI木小桼