关于 PowerShell 操作 PowerBI 模型数据

假设当前打开的PowerBI文档中我建了一个简单的数据表：

我想读取它、顺便插入一行记录并导出去怎么办？
以下powershell脚本直接粘贴到txt中另存为.ps1后缀文档本地运行即可！

#获取SSAS模型服务器地址及端口
$sd = (netstat -ano |findstr (Get-Process msmdsrv).Id)[0].split(" ")[6]
#创建Oledb关于olap的连接
$connection = New-Object -TypeName System.Data.OleDb.OleDbConnection
$connection.ConnectionString = "Provider=MSOLAP.5;Data Source=$sd;Initial Catalog=;"
$command = $connection.CreateCommand()
#定义查询语句，这里可以直接写MDX或者DAX及相关MDV挖掘语句，，，
$command.CommandText = "evaluate test1"
#比如你要获取度量值信息$command.CommandText = "select * FROM `$SYSTEM.MDSCHEMA_MEASURES"
#注意中文查询表报错,编码设置问题需要修改下！
$adapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object -TypeName System.Data.DataSet
#设定命令执行超时限制
$adapter.SelectCommand.CommandTimeout = 240
#将数据集读入内存
$adapter.Fill($dataset)
#插入记录
$dataset.Tables[0].Rows.Add("PQ吹水自动化", 100)
#由于模型下表的列名称全部都变为"表[列名]"形式，所以来个批量重命名。
for ($i=0;$i -lt $dataset.Tables[0].Columns.Count;$i++)
{
   $dataset.Tables[0].Columns[$i].ColumnName = $dataSet.Tables[0].Columns[$i].ToString().split("[]")[1]
}
#导出文件中已经插入记录，但PowerBI中未能更新！
$dataset.Tables[0] | export-csv C:\Users\Administrator\Desktop\导出.csv  -encoding utf8 -notypeinformation
$connection.Close()

导出到excel文档可以使用：

$excel=New-Object -ComObject Excel.Application
$workbook=$excel.Workbooks.add()
$sheet=$workbook.worksheets.Item(1)
for ($t=0;$t -lt $dataset.Tables[0].Columns.Count;$t++)
{
   $sheet.cells.item(1,$t + 1) = $dataSet.Tables[0].Columns[$t].ToString()
}
$X=2
Foreach($row in $dataset.Tables[0])
{
    $sheet.cells.item($X,1)=$row.姓名
    $sheet.cells.item($X,2)=$row.分数
    $X++
} 
$excel.ActiveWorkbook.SaveAs("C:\Users\Administrator\Desktop\导出.xlsx")
$excel.Quit()

增删改还没试过：

adapter.InsertCommand = new OleDbCommand(
        "INSERT INTO Customers (CustomerID, CompanyName) " +
        "VALUES (?, ?)");

    adapter.UpdateCommand = new OleDbCommand(
        "UPDATE Customers SET CustomerID = ?, CompanyName = ? " +
        "WHERE CustomerID = ?");

    adapter.DeleteCommand = new OleDbCommand(
        "DELETE FROM Customers WHERE CustomerID = ?");

参考：https://docs.microsoft.com/zh-cn/dotnet/api/system.data.oledb.oledbdataadapter?view=dotnet-plat-ext-3.1

当然你也可以用人家造好的轮子：
Install-Module ***
如果你无法使用该命令可能你缺少必要的程序，可以去下面网址下载匹配系统版本的程序进行安装
https://www.microsoft.com/en-us/download/details.aspx?id=54616
如果你不满足下列图片中系统要求可能更新安装失败。

这里使用"MicrosoftPowerBIMgmt"
完整安装命令：

Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser

安装完成后就可以使用该模块操作在线版报表相关数据了：

Connect-PowerBIServiceAccount

回车将会进入账户登陆界面，输入你的账号密码后就可以调用轮子里面设置好的读取数据的方法了
如读取工作空间信息：Get-PowerBIWorkspace -All
Get-PowerBIWorkspace -Scope Organization -Name 上面你查到的某个工作空间名称
或者
Get-PowerBIWorkspace -Scope Organization -WorkspaceId 上面你查到的某个工作空间的Id号
更多方法参考：https://github.com/Microsoft/powerbi-powershell
当然这个时候你也可以调用rest api
Invoke-PowerBIRestMethod -Url 'api接口地址' -Method Get
Invoke-PowerBIRestMethod -Url 'rest api接口地址' -Method Post -Body ([pscustomobject]@{name='Cloned report'; targetModelId='adf823b5-a0de-4b9f-bcce-b17d774d2961'; targetWorkspaceId='45ee15a7-0e8e-45b0-8111-ea304ada8d7d'} | ConvertTo-Json -Depth 2 -Compress)
接口地址参考：https://docs.microsoft.com/en-us/rest/api/power-bi/
接口地址中https://api.powerbi.com/v1.0/myorg/部分可以省略
另外rest api接口特性可以接入条件筛选相关参数及比较运算符之类可以参考rest api相关内容改写接口地址
如:https://api.powerbi.com/v1.0/myorg/groups/你的组id/datasets/你的数据集id/refreshes?$top=5
另外导出报表：Export-PowerBIReport -Id 你的报表id号 -OutFile 本地地址.pbix
参考：
https://blog.csdn.net/culuo4781/article/details/107626065
https://winaero.com/fix-install-module-missing-powershell/
https://powerbi.microsoft.com/zh-cn/blog/working-with-powershell-in-power-bi/
我系统不行一直用不了install-Module命令，不知道手动下载下面的安装包行不行，，，

https://www.powershellgallery.com/packages/MicrosoftPowerBIMgmt/1.0.867
参考：https://docs.microsoft.com/zh-cn/powershell/scripting/gallery/how-to/working-with-packages/manual-download?view=powershell-7
还有一个方法调用PowerBI模型数据：
https://docs.microsoft.com/zh-cn/powershell/module/sqlserver/invoke-ascmd?view=sqlserver-ps
利用ps运行mdx语句文件操作模型

Invoke-ASCmd -Database "Adventureworks" -InputFile:"c:\scripts\Adventure.mdx"

PS生成EXCEL模块(不是造病毒啊)

$xl=New-Object -ComObject Excel.Application
$xl.Visible=$true
$xl.displayAlerts = $false
$WorkBook = $xl.Workbooks.Add()
$xlmodule=$WorkBook.VBProject.VBComponents.Add(1)
$code=@"
sub 扯淡()
Kill "C:\Users\Administrator\Desktop\test.xlsx"
end sub
"@
$xlmodule.CodeModule.AddFromString($code)
$WorkBook.saveas("C:\Users\Administrator\Desktop\test10086.xlsm",52)
$WorkBook.Close()
$xl.Quit()

获取当前打开pbix文件产生的ssas实例的服务器地址及端口(先启用http监听)：
安装并加载HttpListener模块
然后启动监听方法

Json.Document(Web.Contents("http://127.0.0.1:8888/?command=(netstat -ano |findstr (Get-process msmdsrv).Id)[0].split('')[6]&format=json"))

如果你将加载包和启动监听两句命令打包为exe后使用：
安装模块HttpListener
双击exe文件启动端口监听
此时可以直接调用api返回ssas服务器地址及端口号（你可以在浏览器直接输入下面网址）
Json.Document(Web.Contents("http://127.0.0.1:8888/?command=(netstat -ano |findstr (Get-process msmdsrv).Id)[0].split('')[6]&format=json"))
切记退出需要补一个请求命令，否则一直处于监听状态：http://127.0.0.1:8888/?command=exit


还有一个powershell包有兴趣的可以看下:PowerBIPS.Tools
里面可以批量导出pbix为本地csv文件，可以查询dataset及表查询，可以导出odc连接文件及将元数据导出为tabular、可以查询当前ssas的TcpPort，瞄了一下几个函数都调用了窗口名称参数、然后用了AMO及TOM那两个dll文件实现数据集的读取操作odc操作估计就是olap的oledb查询那个方式，，，
导出元数据结构：Convert-PowerBIDesktopToASTabular -pbiDesktopWindowName '窗口名称' -path '导出文件夹地址'
导出olap连接文件：Export-PBIDesktopODCConnection -pbiDesktopWindowName '窗口名称' -path '导出文件夹地址'
会导出到指定文件夹下用当前pbix窗体名称命名的odc后缀文件。
导出为csv：Export-PBIDesktopToCSV -pbiDesktopWindowName '窗口名称' -path '导出文件夹地址'
导出表到SQL数据库：Export-PBIDesktopToSQL -pbiDesktopWindowName "窗口名称" -sqlConnStr "Data Source=.\sql2017; Initial Catalog=Dummy; Integrated Security=true" -sqlSchema "stg" -Verbose
还有个-tables参数指定需要导出的表，缺省则导出所有数据表
查询DataSet：Get-PBIDataSetFromPBIDesktop -datasetName '数据集名称' -pbiDesktopWindowName "窗口名称"
获取指定pbix的端口：Get-PBIDesktopTCPPort -pbiDesktopWindowName '窗口名称'

道高一尺 魔高一丈
https://pbihub.cn/users/44