闭环-向当前 Power BI 模型以编程方式写入度量值
Microsoft.AnalysisServices.Server
Microsoft.AnalysisServices.Database
Microsoft.AnalysisServices.Tabular.Server
Microsoft.AnalysisServices.Tabular.Database
前两个用于AMO的原始Microsoft.AnalysisServices命名空间，
后面两个用于TOM的新Microsoft.AnalysisServices.Tabular命名空间。
TOM是AMO客户端库（Microsoft.AnalysisServices.dll）的扩展。要使用TOM，模型和数据库的兼容性级别必须为1200或更高。为了在数据库级别以下进行调用（例如，在Tabular 1200数据库中的模型上，或在多维或Tabular 1050-1103数据库中的Cube，Dimension或MeasureGroup上），AMO依赖于TOM。而TOM不依赖于AMO。尽管不能使用TOM来浏览多维元数据（多维数据集），但可以使用AMO来浏览多维和表格元数据。
TOM下请务必熟悉TMSL相关指令集，
[System.Reflection.Assembly]::LoadwithPartialName("Microsoft.AnalysisServices.Tabular");
$ServerName = (netstat -ano |findstr (Get-Process msmdsrv).Id)[0].split(" ")[6];
$Server = New-Object Microsoft.AnalysisServices.Tabular.Server;
$Server.Connect($ServerName);
$db =$Server.databases[0];
$度量值 = @{Name="吹牛逼";Expression="SUM([成绩])"};
$db.Model.Tables[0].Measures.Add($度量值);
$db.Model.SaveChanges();
$Server.Disconnect();或者
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$ServerName = (netstat -ano |findstr (Get-Process msmdsrv).Id)[0].split(" ")[6]
$server = New-Object Microsoft.AnalysisServices.Server
$server.Connect($ServerName)
$server.Databases[0].Model.Tables[0].Measures
[Microsoft.AnalysisServices.Tabular.JsonScripter]::ScriptCreateOrReplace($server.Databases[0])
#[Microsoft.AnalysisServices.Tabular.JsonScripter]::ScriptCreateOrReplace($server.Databases[0])| out-file -filepath C:\Users\Admin\Desktop\test.txt
# Alter command (TMSL), Create command (TMSL), CreateOrReplace command (TMSL), Delete command (TMSL), Refresh command (TMSL), MergePartitions command (TMSL).
#新建参考：https://docs.microsoft.com/en-us/analysis-services/tom/create-tables-partitions-and-columns-in-a-tabular-model?view=asallproducts-allversions插入列：
[System.Reflection.Assembly]::LoadwithPartialName("Microsoft.AnalysisServices.Tabular");
$ServerName = (netstat -ano |findstr (Get-Process msmdsrv).Id)[0].split(" ")[6]
$Server = New-Object Microsoft.AnalysisServices.Tabular.Server;
$Server.Connect($ServerName)
$x = New-Object Microsoft.AnalysisServices.Tabular.DataColumn
$x.Name = "扯淡"
$x.DataType="Int64"
$Server.Databases[0].Model.Tables[0].Columns.Add($x)
$Server.Databases[0].Model.SaveChanges()插入表：
[System.Reflection.Assembly]::LoadwithPartialName("Microsoft.AnalysisServices.Tabular");
$ServerName = (netstat -ano |findstr (Get-Process msmdsrv).Id)[0].split(" ")[6]
$Server = New-Object Microsoft.AnalysisServices.Tabular.Server;
$Server.Connect($ServerName)
$x = New-Object Microsoft.AnalysisServices.Tabular.Table
$x.Name = "扯淡1"
$x.Description = "插入表测试"
$y=New-Object Microsoft.AnalysisServices.Tabular.Partition
$z=New-Object Microsoft.AnalysisServices.Tabular.QueryPartitionSource
$z.DataSource=$Server.Databases[0].Model.DataSources[0]
$z.Query = "SELECT [教师] FROM [班级表]"
$y.Name="测试"
$y.Source=$z
$x.Partitions.Add($y)
$c = New-Object Microsoft.AnalysisServices.Tabular.DataColumn
$c.Name = "扯淡"
$c.DataType="Int64"
$x.Columns.Add($c)
$Server.Databases[0].Model.Tables.Add($x)
$Server.Databases[0].Model.SaveChanges()注意：AMO不能用于查询数据，若要查询数据可以使用ADOMD.NET，参考如下。
https://docs.microsoft.com/en-us/analysis-services/adomd/developing-with-adomd-net?view=asallproducts-allversions
你以为只有ps可以调用.net?
python怎么调用？R怎么调用？js怎么调用？vba怎么调用？一通百通
#python有pythonnet库与.net交互
import sys,clr
from clr import System
clr.AddReference ("C:/Users/Admin/Desktop/Microsoft.AnalysisServices.Tabular.DLL")
from Microsoft.AnalysisServices.Tabular import Server
tomserver=Server()
tomserver.Connect("127.0.0.1:5072")
coll=tomserver.Databases[0].Model.Tables
print([y.Name for x in coll for y in x.Measures])
print(list(coll)[0].Name)获取服务器地址端口可使用：
import subprocess
server=subprocess.Popen(['powershell','(netstat -ano |findstr (Get-Process msmdsrv).Id)[0].split(" ")[6]'],stdout=subprocess.PIPE).communicate()[0]
print(server.decode('utf-8')) 此时返回上面的"127.0.0.1:5072"完整的实现插入度量值：
import sys,clr
from clr import System
clr.AddReference ("C:/Users/Admin/Desktop/Microsoft.AnalysisServices.Tabular.DLL")
from Microsoft.AnalysisServices.Tabular import Server,Measure
tomserver=Server()
import subprocess
servern=subprocess.Popen(['powershell','(netstat -ano |findstr (Get-Process msmdsrv).Id)[0].split(" ")[6]'],stdout=subprocess.PIPE).communicate()[0]
tomserver.Connect(servern.decode('utf-8'))
coll=tomserver.Databases[0].Model.Tables
x=Measure()
x.Name="吹牛逼"
x.Expression="Sum([成绩])"
#思考三步x写为一步
list(coll)[0].Measures.Add(x)
tomserver.Databases[0].Model.SaveChanges()
tomserver.Disconnect()
//至此已经实现pbix文档能读能写，稍微加个UI配合编程语言轮子优势做复杂交互（用户只需填参数），你只是缺点想象力，dax studio算个球，中国的小伙伴们加油，哈哈，，，
PP的写入参考：https://pbihub.cn/blog/1209
关于模型控制还可以参考：
https://pbihub.cn/blog/1166
https://pbihub.cn/blog/1162
PS-GUI
$X=@{姓名='畅心'
成绩=100}
$X|Out-GridView -Title "吹牛逼" -OutputMode None
$X|Out-GridView -Title "吹牛逼" -OutputMode Single
$A=Get-Service |Out-GridView -Title "吹牛逼" -OutputMode Single
Stop-Service $A.Name
$A=Get-Process |Out-GridView -Title "吹牛逼" -OutputMode Single
Stop-Process -Name $A.Name
$A=Get-Process |Out-GridView -Title "吹牛逼" -OutputMode Multiple
Foreach ($b in $A){
Stop-Process -Name $b.Name
}
$nums=1..5
$nums | Out-GridView -Title "吹牛逼" -OutputMode Single
$nums=1,2,3 //$a=@('tt','ss','qq') $a=@{姓名="畅心";成绩=100}
$nums | Out-GridView -Title "吹牛逼" -OutputMode Single
$x=Import-Csv F:\test1.csv |Select-Object @{Name='班级';Expression={$_.班级}},@{Name='姓名';Expression={$_.姓名}}
#选择需要操作的pbix文件
Function Get-FileName($initialDirectory)
{
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
$OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
$OpenFileDialog.initialDirectory = $initialDirectory
$OpenFileDialog.Multiselect = $true
$OpenFileDialog.filter = "PowerBI文档(*.pbix)| *.pbix"
$OpenFileDialog.ShowDialog() | Out-Null
$OpenFileDialog.filename
}
#获取选择文件绝对路径变量
$pathn = Get-FileName
Invoke-Item $pathn
Start-Sleep -s 15
#绘制窗体及控件设置
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
$form = New-Object System.Windows.Forms.Form
$form.Text = '吹牛逼'
$form.Size = New-Object System.Drawing.Size(300,200)
$form.StartPosition = 'CenterScreen'
$okButton = New-Object System.Windows.Forms.Button
$okButton.Location = New-Object System.Drawing.Point(75,120)
$okButton.Size = New-Object System.Drawing.Size(75,23)
$okButton.Text = '撸一发'
$okButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
$form.AcceptButton = $okButton
$form.Controls.Add($okButton)
$cancelButton = New-Object System.Windows.Forms.Button
$cancelButton.Location = New-Object System.Drawing.Point(150,120)
$cancelButton.Size = New-Object System.Drawing.Size(75,23)
$cancelButton.Text = '回家吹水'
$cancelButton.DialogResult = [System.Windows.Forms.DialogResult]::Cancel
$form.CancelButton = $cancelButton
$form.Controls.Add($cancelButton)
$label = New-Object System.Windows.Forms.Label
$label.Location = New-Object System.Drawing.Point(10,20)
$label.Size = New-Object System.Drawing.Size(280,20)
$label.Text = '请输入操作维度表：'
$form.Controls.Add($label)
$label2 = New-Object System.Windows.Forms.Label
$label2.Location = New-Object System.Drawing.Point(10,80)
$label2.Size = New-Object System.Drawing.Size(280,20)
$label2.Text = '请定义度量值：'
$form.Controls.Add($label2)
$textBox = New-Object System.Windows.Forms.TextBox
$textBox.Location = New-Object System.Drawing.Point(10,40)
$textBox.Size = New-Object System.Drawing.Size(260,40)
$form.Controls.Add($textBox)
$textBox2 = New-Object System.Windows.Forms.TextBox
$textBox2.Location = New-Object System.Drawing.Point(10,100)
$textBox2.Size = New-Object System.Drawing.Size(260,20)
$form.Controls.Add($textBox2)
$form.Topmost = $true
$form.Add_Shown({$textBox.Select()})
$result = $form.ShowDialog()
#按钮事件触发绑定
if ($result -eq [System.Windows.Forms.DialogResult]::OK)
{
#捕捉用户输入维度表名称（文本字符串）
$x = $textBox.Text
#捕捉用户输入度量值定义（度量值名称=表达式，如：最高分=MAX([成绩])）
$y = $textBox2.Text
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Tabular")
$ServerName = (netstat -ano |findstr (Get-Process msmdsrv).Id)[0].split(" ")[6];
$Server = new-Object Microsoft.AnalysisServices.Tabular.Server
$ConnectionString = "Provider=MSOLAP;Data Source=$ServerName;";
$Server.Connect($ConnectionString)
$Database = $Server.Databases[0]
$Model = $Database.Model
$Table = $Model.Tables[$x]
$t= $y -split "=", 2
$度量值 = @{Name=$t[0];Expression=$t[1]};
$Table.Measures.Add($度量值);
$Model.SaveChanges();
$Table.Measures|Out-GridView -Title "吹牛逼" -OutputMode Single
$Server.Disconnect();
}最后将整个脚本打包为exe即可，，，
运行程序会先打开fso文件打开窗口供用户选择操作的pbix文件，然后启动文件弹出参数设置窗口，待用户输入好参数确定将会将新增度量值到当前模型，同时将当前维度的度量值信息通过Gridview展示出来，如果需要组装更多功能不就是零件加工、对象属性方法事件都有了，就缺你动手了，，，
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
$form = New-Object System.Windows.Forms.Form
$form.Text = '吹牛逼'
$form.Size = New-Object System.Drawing.Size(300,200)
$form.StartPosition = 'CenterScreen'
$okButton = New-Object System.Windows.Forms.Button
$okButton.Location = New-Object System.Drawing.Point(75,120)
$okButton.Size = New-Object System.Drawing.Size(75,23)
$okButton.Text = '确定'
$okButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
$form.AcceptButton = $okButton
$form.Controls.Add($okButton)
$cancelButton = New-Object System.Windows.Forms.Button
$cancelButton.Location = New-Object System.Drawing.Point(150,120)
$cancelButton.Size = New-Object System.Drawing.Size(75,23)
$cancelButton.Text = '取消'
$cancelButton.DialogResult = [System.Windows.Forms.DialogResult]::Cancel
$form.CancelButton = $cancelButton
$form.Controls.Add($cancelButton)
$label = New-Object System.Windows.Forms.Label
$label.Location = New-Object System.Drawing.Point(10,20)
$label.Size = New-Object System.Drawing.Size(280,20)
$label.Text = '选择需要修改的度量值：'
$form.Controls.Add($label)
$listBox = New-Object System.Windows.Forms.ListBox
$listBox.Location = New-Object System.Drawing.Point(10,40)
$listBox.Size = New-Object System.Drawing.Size(260,20)
$listBox.Height = 80
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Tabular")
$ServerName = (netstat -ano |findstr (Get-Process msmdsrv).Id)[0].split(" ")[6];
$Server = new-Object Microsoft.AnalysisServices.Tabular.Server
$Server.Connect("Provider=MSOLAP;Data Source=$ServerName;")
$Database = $Server.Databases[0]
$Model = $Database.Model
#遍历所有度量值并导入列表框供用户选择修改编辑
Foreach($a in $Model.Tables){foreach($b in $a.Measures){[void] $listBox.Items.Add($a.Name+":"+$b.Name)}}
$form.Controls.Add($listBox)
$form.Topmost = $true
$result = $form.ShowDialog()
if ($result -eq [System.Windows.Forms.DialogResult]::OK)
{
$x = $listBox.SelectedItem
$s=$x -Split ":",2
$Model.Tables[$s[0]].Measures[$s[1]].Expression=//这里就可以继续弹窗捕捉修改指定度量值表达式参数
$Model.SaveChanges();
}
$Server.Disconnect()
你以为窗体代码是我手写的吗？
https://poshgui.com/Editor
你也可以安装powershell studio或者visual studio
Add-Type -AssemblyName System.Windows.Forms
[System.Windows.Forms.Application]::EnableVisualStyles()
$Form = New-Object system.Windows.Forms.Form
$Form.ClientSize = New-Object System.Drawing.Point(625,400)
$Form.text = "吹牛逼"
$Form.TopMost = $false
$Form.BackColor = [System.Drawing.ColorTranslator]::FromHtml("#0e0e0e")
$ListBox1 = New-Object system.Windows.Forms.ListBox
$ListBox1.text = "listBox"
$ListBox1.width = 201
$ListBox1.height = 30
$ListBox1.location = New-Object System.Drawing.Point(153,25)
@("chrome","PQ","DAX","Python","SQL","VBA")|foreach{$ListBox1.Items.Add($_)}
#@("PQ","DAX","Python","SQL","VBA") | ForEach-Object {[void] $ListBox1.Items.Add($_)}
$Label1 = New-Object system.Windows.Forms.Label
$Label1.text = "请选择维度表："
$Label1.AutoSize = $true
$Label1.width = 25
$Label1.height = 10
$Label1.location = New-Object System.Drawing.Point(40,36)
$Label1.Font = New-Object System.Drawing.Font('Microsoft Sans Serif',10)
$Label1.ForeColor = [System.Drawing.ColorTranslator]::FromHtml("#e1d7d7")
$Button1 = New-Object system.Windows.Forms.Button
$Button1.text = "查询"
$Button1.width = 64
$Button1.height = 31
$Button1.location = New-Object System.Drawing.Point(395,24)
$Button1.Font = New-Object System.Drawing.Font('Microsoft Sans Serif',10)
$Button1.ForeColor = [System.Drawing.ColorTranslator]::FromHtml("#c9bebe")
$DataGridView1 = New-Object system.Windows.Forms.DataGridView
$DataGridView1.width = 565
$DataGridView1.height = 237
$DataGridView1.location = New-Object System.Drawing.Point(40,85)
$DataGridView1.BackColor = [System.Drawing.ColorTranslator]::FromHtml("#4f4e4e")
$DataGridView1.ColumnCount = 4
$DataGridView1.ColumnHeadersVisible = $true
$DataGridView1.RowHeadersVisible=$false
$DataGridView1.MultiSelect = $false
0..3|foreach{$DataGridView1.Columns[$_].Name = @("名称","ID号","描述","内存")[$_]}
$DataGridView1.Columns[0].width =180
#冻结前两列
$DataGridView1.Columns[1].Frozen = $true
get-process |foreach {$DataGridView1.Rows.Add($_.Name,$_.ID,$_.Description,$_.WorkingSet) | out-null}
$Form.controls.AddRange(@($ListBox1,$Label1,$Button1,$DataGridView1))
$result =$Form.ShowDialog()
TreeView
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
$Form = New-Object System.Windows.Forms.Form
$Form.Text = "PS-TreeView"
$Form.Size = New-Object System.Drawing.Size(390, 390)
$TreeView = New-Object System.Windows.Forms.TreeView
$TreeView.Location = New-Object System.Drawing.Point(48, 12)
$TreeView.Size = New-Object System.Drawing.Size(290, 322)
$TreeView.Nodes.Add("根节点")
$TreeView.Nodes[0].Nodes.Add("一级节点1")
$TreeView.Nodes[0].Nodes.Add("一级节点2")
$TreeView.Nodes[0].Nodes[1].Nodes.Add("二级节点2-1")
$TreeView.Nodes[0].Nodes[1].Nodes[0].Nodes.Add("三级级节点2-1-1")
$Form.Controls.Add($TreeView)
$Form.ShowDialog()
文件夹索引树
function Show-DirectoryTree {
[CmdletBinding()]
param (
[Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)]
[string]$RootFolder
)
function Add-Node {
param (
[System.Windows.Forms.TreeNode]$parentNode,
[System.IO.DirectoryInfo]$Folder
)
Write-Verbose "添加节点 $($Folder.Name)"
$subnode = New-Object System.Windows.Forms.TreeNode
$subnode.Text = $Folder.Name
[void]$parentNode.Nodes.Add($subnode)
Get-ChildItem -Path $Folder.FullName -Directory | ForEach-Object {
Add-Node $subnode $_
}
}
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
$Form = New-Object System.Windows.Forms.Form
$Form.Text = "PS-TreeView"
$Form.Size = New-Object System.Drawing.Size(390, 390)
$TreeView = New-Object System.Windows.Forms.TreeView
$TreeView.Location = New-Object System.Drawing.Point(48, 12)
$TreeView.Size = New-Object System.Drawing.Size(290, 322)
$Form.Controls.Add($TreeView)
$rootnode = New-Object System.Windows.Forms.TreeNode
$rootnode.Text = [System.IO.Path]::GetFileName($RootFolder.TrimEnd('\')) #'# or use: (Get-Item -Path $RootFolder).Name
$rootnode.Name = "Root"
[void]$TreeView.Nodes.Add($rootnode)
$rootNode.Expand()
Get-ChildItem -Path $RootFolder -Directory | ForEach-Object {
Add-Node $rootnode $_
}
$Form.Add_Shown({$Form.Activate()})
[void] $Form.ShowDialog()
$Form.Dispose()
}
Show-DirectoryTree -RootFolder 'D:\测试文件夹' -Verbose
内置树命令：tree "根目录"
如果需要获取文件名：tree /F "根目录"
听说有人分分钟造了一个格式化工具？
function DAXFORMAT ($x) { iwr 'https://www.daxformatter.com/' -Method 'POST' -ContentType 'application/x-www-form-urlencoded;charset=UTF-8' -body @{embed='1';version='0.5.3';l='short';fx=$x}|Select Content}
$DAXContent=DAXFORMAT "=CALCULATE(VALUES('价格表'[单价]),FILTER('价格表','价格表'[产品]='销售表'[产品]))"
$html = New-Object -ComObject "HTMLFile"
$html.IHTMLDocument2_write($DAXContent.Content)
$DAX=$html.body.innerText
$html.Close()
$DAX也可以：
$htm1=$DAXContent.ParsedHtml.getElementsByClassName('formatted')
$htm1[0].innerText
或
Add-Type -Path HtmlAgilityPack.dll
$HtmlDocument = New-Object HtmlAgilityPack.HtmlDocument
$HtmlDocument.LoadHtml($DAXContent.Content)
$div=$HtmlDocument.DocumentNode.SelectSingleNode("/html/body/div/div")
$div.InnerText.Replace(' ','' )如果连国外服务器发送失败，可以尝试加一句：
[System.Net.ServicePointManager]::SecurityProtocol=[System.Net.SecurityProtocolType]::Tls12有了上面的接口转换，将pbix内的度量值格式化就简单了：
...连模型过程省略，，，
$server.Databases[0].Model.Tables["度量值所在的维度表"].Measures["要修改的度量值"].Expression=($DAX -split "=",2)[1]
$db.Model.SaveChanges()
所以格式化工具的完整代码为：
#选择需要操作的pbix文件
Function Get-FileName($initialDirectory)
{
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
$OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
$OpenFileDialog.initialDirectory = $initialDirectory
$OpenFileDialog.Multiselect = $true
$OpenFileDialog.filter = "PowerBI文档(*.pbix)| *.pbix"
$OpenFileDialog.ShowDialog() | Out-Null
$OpenFileDialog.filename
}
#获取选择文件绝对路径变量
$pathn = Get-FileName
Invoke-Item $pathn
Start-Sleep -s 15
#绘制窗体及控件设置
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
$form = New-Object System.Windows.Forms.Form
$form.Text = '吹牛逼'
$form.Size = New-Object System.Drawing.Size(300,200)
$form.StartPosition = 'CenterScreen'
$okButton = New-Object System.Windows.Forms.Button
$okButton.Location = New-Object System.Drawing.Point(75,120)
$okButton.Size = New-Object System.Drawing.Size(75,23)
$okButton.Text = '撸一发'
$okButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
$form.AcceptButton = $okButton
$form.Controls.Add($okButton)
$cancelButton = New-Object System.Windows.Forms.Button
$cancelButton.Location = New-Object System.Drawing.Point(150,120)
$cancelButton.Size = New-Object System.Drawing.Size(75,23)
$cancelButton.Text = '回家吹水'
$cancelButton.DialogResult = [System.Windows.Forms.DialogResult]::Cancel
$form.CancelButton = $cancelButton
$form.Controls.Add($cancelButton)
$label = New-Object System.Windows.Forms.Label
$label.Location = New-Object System.Drawing.Point(10,20)
$label.Size = New-Object System.Drawing.Size(280,20)
$label.Text = '请输入操作维度表：'
$form.Controls.Add($label)
$label2 = New-Object System.Windows.Forms.Label
$label2.Location = New-Object System.Drawing.Point(10,80)
$label2.Size = New-Object System.Drawing.Size(280,20)
$label2.Text = '请定义度量值：'
$form.Controls.Add($label2)
$textBox = New-Object System.Windows.Forms.TextBox
$textBox.Location = New-Object System.Drawing.Point(10,40)
$textBox.Size = New-Object System.Drawing.Size(260,40)
$form.Controls.Add($textBox)
$textBox2 = New-Object System.Windows.Forms.TextBox
$textBox2.Location = New-Object System.Drawing.Point(10,100)
$textBox2.Size = New-Object System.Drawing.Size(260,20)
$form.Controls.Add($textBox2)
$form.Topmost = $true
$form.Add_Shown({$textBox.Select()})
$result = $form.ShowDialog()
#按钮事件触发绑定
if ($result -eq [System.Windows.Forms.DialogResult]::OK)
{
$x = $textBox.Text
#捕捉用户输入度量值定义（度量值名称=表达式，如：最高分=MAX([成绩])）
$y = $textBox2.Text
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Tabular")
$ServerName = (netstat -ano |findstr (Get-Process msmdsrv).Id)[0].split(" ")[6];
$Server = new-Object Microsoft.AnalysisServices.Tabular.Server
$ConnectionString = "Provider=MSOLAP;Data Source=$ServerName;";
$Server.Connect($ConnectionString)
$Database = $Server.Databases[0]
$Model = $Database.Model
$Table = $Model.Tables[$x]
$t= $y -split "=", 2
function DAXFORMAT ($m) { iwr 'https://www.daxformatter.com/' -Method 'POST' -ContentType 'application/x-www-form-urlencoded;charset=UTF-8' -body @{embed='1';version='0.5.3';l='short';fx=$m}}
$DAXContent=DAXFORMAT ("="+$t[1])
$DAX=$DAXContent.ParsedHtml.body.outerText
#此时的度量值表达式直接就被格式化过了
$度量值 = @{Name=$t[0];Expression=($DAX -split "=",2)[1]};
$Table.Measures.Add($度量值);
$Model.SaveChanges();
$Server.Disconnect()
}调用语音播放控件将txt转为语音：
Function Get-FileName($initialDirectory)
{
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
$OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
$OpenFileDialog.initialDirectory = $initialDirectory
$OpenFileDialog.Multiselect = $true
$OpenFileDialog.filter = "文本文档(*.txt)| *.txt"
$OpenFileDialog.ShowDialog() | Out-Null
$OpenFileDialog.filename
}
#获取选择文件绝对路径变量
$pathn = Get-FileName
$Voice = new-object -com SAPI.SpVoice
$Voice.Speak($pathn,5)|Out-Null
或者
Add-Type -AssemblyName System.speech
$syn=New-Object System.Speech.Synthesis.SpeechSynthesizer
Get-Content C:\Users\Admin\Desktop\test.txt | foreach {
$syn.Speak($_)
} 日期控件：
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
$form = New-Object Windows.Forms.Form -Property @{
StartPosition = [Windows.Forms.FormStartPosition]::CenterScreen
Size = New-Object Drawing.Size 243, 250
Text = '日期控件'
Topmost = $true
}
$calendar = New-Object Windows.Forms.MonthCalendar -Property @{
ShowTodayCircle = $false
MaxSelectionCount = 1
}
$form.Controls.Add($calendar)
$okButton = New-Object Windows.Forms.Button -Property @{
Location = New-Object Drawing.Point 38, 185
Size = New-Object Drawing.Size 75, 23
Text = '买定离手'
DialogResult = [Windows.Forms.DialogResult]::OK
}
$form.AcceptButton = $okButton
$form.Controls.Add($okButton)
$cancelButton = New-Object Windows.Forms.Button -Property @{
Location = New-Object Drawing.Point 113, 185
Size = New-Object Drawing.Size 75, 23
Text = '投降输一半'
DialogResult = [Windows.Forms.DialogResult]::Cancel
}
$form.CancelButton = $cancelButton
$form.Controls.Add($cancelButton)
$result = $form.ShowDialog()
if ($result -eq [Windows.Forms.DialogResult]::OK) {
$date = $calendar.SelectionStart
Write-Host "你选择的日期是: $($date.ToShortDateString())"
}
下拉框：
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
$Form = New-Object System.Windows.Forms.Form
$Form.Text = "下拉选择框"
$Form.Size = New-Object System.Drawing.Size(390, 390)
$DropDownBox1 = New-Object System.Windows.Forms.ComboBox
$DropDownBox1.Text = "请选择项目"
$DropDownBox1.Size = New-Object System.Drawing.Size(190, 20)
$DropDownBox1.Location = New-Object System.Drawing.Point(20, 20)
$Sites = 1..5
ForEach ($x in $Sites) {$DropDownBox1.Items.Add($x) | Out-Null}
$Form.Controls.Add($DropDownBox1)
$Form.ShowDialog()
图片控件：
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
$Form = New-Object System.Windows.Forms.Form
$Form.Text = "图片容器"
$Form.Size = New-Object System.Drawing.Size(250, 200)
$file = (get-item 'F:\test.gif')
$img = [System.Drawing.Image]::Fromfile($file)
$pictureBox = new-object Windows.Forms.PictureBox
$pictureBox.Location = New-Object System.Drawing.Size(0,1)
$pictureBox.Size = New-Object System.Drawing.Size($img.Width,$img.Height)
$pictureBox.Image = $img
$Form.controls.add($pictureBox)
$Form.ShowDialog()FSO:
Add-Type -AssemblyName System.Windows.Forms
$FileBrowser=New-Object System.Windows.Forms.OpenFileDialog -Property @{InitialDirectory = [Environment]::GetFolderPath('Desktop')}
[void]$FileBrowser.ShowDialog()
$FileBrowser.FileNamesFSO限定读取文件类型：
Add-Type -AssemblyName System.Windows.Forms
$FileBrowser=New-Object System.Windows.Forms.OpenFileDialog -Property @{InitialDirectory=[Environment]::GetFolderPath('Desktop');Filter='Documents (*.docx)|*.docx|SpreadSheet (*.xlsx)|*.xlsx'}
[void]$FileBrowser.ShowDialog()
$FileBrowser.FileNames允许多选：
Add-Type -AssemblyName System.Windows.Forms
$FileBrowser=New-Object System.Windows.Forms.OpenFileDialog -Property @{InitialDirectory=[Environment]::GetFolderPath('Desktop');Filter='Documents (*.docx)|*.docx|SpreadSheet (*.xlsx)|*.xlsx';Multiselect=$true}
[void]$FileBrowser.ShowDialog()
$FileBrowser.FileNames类似的另存为：
Add-Type -AssemblyName System.Windows.Forms
$saveFile=New-Object System.Windows.Forms.SaveFileDialog -Property @{InitialDirectory = [Environment]::GetFolderPath('Desktop');Filter = "Comma Delimited (*.csv) | *.csv "}
if ($saveFile.ShowDialog() -eq 'OK'){Get-Process|Out-File $saveFile.FileName}你要习惯PS的反射式事件驱动模式，，，
禁用form图标
$form.ShowIcon=$false
窗体参考：
https://docs.microsoft.com/zh-cn/dotnet/api/system.windows.forms?redirectedfrom=MSDN&view=net-5.0
https://docs.microsoft.com/zh-cn/dotnet/api/?view=netframework-4.7.1&redirectedfrom=MSDN
https://docs.microsoft.com/zh-cn/dotnet/api/system.windows.forms.form?redirectedfrom=MSDN&view=net-5.0[System.Reflection.Assembly]::LoadwithPartialName("Microsoft.AnalysisServices.Tabular") | Out-Null
$ServerName = (netstat -ano |findstr (Get-Process msmdsrv).Id)[0].split(" ")[6]
$Server = New-Object Microsoft.AnalysisServices.Tabular.Server
$Server.Connect($ServerName)
$x = New-Object Microsoft.AnalysisServices.Tabular.Table
$x.Name = "扯淡"
$x.Description = "插入表测试"
$y=New-Object Microsoft.AnalysisServices.Tabular.Partition
$y.Name="测试"
$z=New-Object Microsoft.AnalysisServices.Tabular.MPartitionSource
$z.Expression='let xyz=Table.FromColumns({{"D".."M"},{"畅心","0".."8"},{0..9}},{"班级","姓名","成绩"}) in xyz'
$y.Source=$z
$Server.Databases[0].Model.Tables.Add($x)
$Server.Databases[0].Model.Tables["扯淡"].Partitions.Add($y)
$Server.Databases[0].Model.RequestRefresh([Microsoft.AnalysisServices.Tabular.RefreshType]::Full)
$Server.Databases[0].Model.SaveChanges() | Out-Null
$Server.Databases[0].Model.Tables["扯淡"].Partitions[0].Source.ExpressionEPPlus.dll操作EXCEL:
Add-Type -AssemblyName System.Drawing
Add-Type -Path "D:\畅心学习文档\PQ\epplus\EPPlus.dll"
$pkg = New-Object OfficeOpenXml.ExcelPackage('C:\Users\Admin\Desktop\output.xlsx')
Get-ChildItem "D:\畅心学习文档\*.png" | ForEach-Object {
$name = $_.BaseName
$ws = $pkg.Workbook.Worksheets.Add($name)
$image = [System.Drawing.Image]::FromFile($_.FullName)
$picture = $ws.Drawings.AddPicture($name, $image)
$picture.From.Column = 1
$picture.From.Row = 1
$image.Dispose() }
$pkg.Encryption.Algorithm = [OfficeOpenXml.EncryptionAlgorithm]::AES256
$pkg.Encryption.IsEncrypted = $true
$pkg.Encryption.Password = 'Excel'
$pkg.Save()
$pkg.Dispose()
Start-Process -FilePath 'Excel.exe' -ArgumentList 'C:\Users\Admin\Desktop\output.xlsx' 操作VBA:
Add-Type -Path "C:\Users\Administrator\Desktop\EPPlus.dll"
$pkg = New-Object OfficeOpenXml.ExcelPackage('C:\Users\Administrator\Desktop\output.xlsm')
$pkg.Workbook.Worksheets.Add("扯淡")
$pkg.Workbook.CreateVBAProject()
$m=$pkg.Workbook.VbaProject.Modules.AddModule("test")
$m.Code='Public Function functest() As String
End Function'
$pkg.Save()
$pkg.Dispose()将模型数据写出到luckysheet:
[System.Reflection.Assembly]::LoadwithPartialName("Microsoft.AnalysisServices.Tabular")|Out-Null
$ServerName = (netstat -ano |findstr (Get-Process msmdsrv).Id)[0].split(" ")[6]
$Server = New-Object Microsoft.AnalysisServices.Tabular.Server
$Server.Connect($ServerName)
$tbs =$Server.databases[0].Model.Tables
$st=foreach($x in $tbs){foreach($y in $x.Measures){'["'+$x.Name+'","'+$y.Name+'","'+$y.Expression+'"]'}}
$pq='[["维度表","度量值名称","表达式"],'+(($st -join ",") -replace "\n", "")+"]"
$M='<head>
<title>吹牛逼</title>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/css/pluginsCss.css" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/plugins.css" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/luckysheet/dist/css/luckysheet.css" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/luckysheet/dist/assets/iconfont/iconfont.css" />
<script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/js/plugin.js"></script>
<script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/luckysheet.umd.js"></script>
<script>
$(function () {
var options = {
container: "pq",
title: "畅心测试",
lang: "zh",
myFolderUrl: "https://pbihub.cn/users/44",
userInfo: {userName:"畅心",userImage: "https://pbihub.cn/uploads/images/201809/12/44/LWNFWHW7Gj.png"},
data: [{"name":"吹水","index":0,"status":1,"order":0,"color":"#ff0066","row":18,"column":10,"celldata":luckysheet.transToCellData('+$pq+')}]
}
luckysheet.create(options)
})
</script>
</head>
<body>
<div id="md" style="position: absolute;z-index: 1000000;left: 0px;top: 0px;bottom: 0px;right: 0px; background: rgba(255, 255, 255, 0.8); text-align: center;font-size: 40px;align-items:center;justify-content: center;display: none;">Downloading</div>
<p style="text-align:center;"> <input style="font-size:16px;" type="file" id="test" name="test" change="demoHandler" /></p>
<div id="pq" style="margin:0px;padding:0px;position:absolute;width:100%;left: 0px;top: 50px;bottom: 0px;outline: none;"></div>
<script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/luckyexcel.umd.js"></script>
<script>
function demoHandler(){
let upload = document.getElementById("test");
let mask = document.getElementById("md");
if(upload){
window.onload = () => {
upload.addEventListener("change", function(evt){
var files = evt.target.files;
if(files==null || files.length==0){alert("没有待加载文件！");return;}
let name = files[0].name;
let suffixArr = name.split("."), suffix = suffixArr[suffixArr.length-1];
if(suffix!="xlsx"){alert("目前只支持xlsx文档导入！");return;}
LuckyExcel.transformExcelToLucky(files[0], function(exportJson, luckysheetfile){
if(exportJson.sheets==null || exportJson.sheets.length==0){alert("读取失败, 目前不支持xls文件!");return;}
console.log(exportJson, luckysheetfile);
window.luckysheet.destroy();
window.luckysheet.create({
container: "pq",
showinfobar:false,
data:exportJson.sheets,
title:exportJson.info.name,
userInfo:exportJson.info.name.creator
});
});
});
}
}
}
demoHandler();
</script>
</body>'
Set-Content C:\test666.html -Value $M
$Server.Disconnect()
ii 'C:\test666.html'生成Highcharts只需套个模板即可：
$st='
<html>
<head>
<meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1">
<meta http-equiv="x-ua-compatible" content="ie=edge">
<style>
#container {min-width: 300px;max-width: 800px;height: 400px;margin: 0 auto;border: 1px solid silver;}
</style>
<script src="F:\highcharts.js"></script>
<script src="https://code.highcharts.com.cn/highcharts/modules/exporting.js"></script>
<script src="https://code.highcharts.com.cn/highcharts/modules/sankey.js"></script>
<script src="https://code.highcharts.com.cn/highcharts/modules/oldie.js"></script>
<script src="https://code.highcharts.com.cn/highcharts/themes/sand-signika.js"></script>
</head>
<body>
<div id="container"></div>
<script>
Highcharts.chart("container", {
title: {
text: "吹牛逼我们是认真的"
},
series: [{
keys: ["from", "to", "weight"],
data: [
["吹水", "畅心", 5 ],
["吹水", "篮球", 1 ],
["吹水", "小学生", 1 ],
["开车", "畅心", 1 ],
["开车", "慢车道", 1 ],
["开车", "小学生", 5 ],
["扯淡", "慢车道", 6 ],
["小学生", "PQ", 1 ],
["小学生", "python", 1 ],
["小学生", "VBA", 1 ],
["慢车道", "DAX", 5 ],
["慢车道", "VBA", 1 ],
["畅心", "PQ", 1 ],
["畅心", "VBA", 1 ],
["篮球", "PQ", 1 ],
["篮球", "js", 5 ],
["篮球", "VBA", 2 ],
["篮球", "DAX", 1 ]
],
type: "sankey",
name: "打豆豆成绩"
}]
});
</script>
</body>
</html>
'
$st| Out-File -Encoding utf8 C:\Users\Administrator\Desktop\吹水.html
ii C:\Users\Administrator\Desktop\吹水.html
#你可以直接将html字符串中的图表参数如“data”部分在ps中重构后重组，，，
百度迁徙图：
$M='<html>
<head><style type="text/css">body {margin: 0;}#main {height: 100%;}</style></head>
<body><div id="main"></div>
<script src="https://cdn.jsdelivr.net/npm/echarts@5.0.2/dist/echarts.js"></script>
<script src=".//echarts-all.js"></script>
<script>var myChart = echarts.init(document.getElementById("main"));
var fx = {
backgroundColor: "#1b1b1b",
color: ["gold","aqua","lime"],
title : {text: "吹水",subtext:"吹牛逼我们是认真的",x:"center",textStyle : {color: "#fff"}},
tooltip : {trigger: "item",formatter: "{b}"},
toolbox: {show : true,orient : "vertical",x: "right",y: "center",feature : {mark : {show: true},dataView : {show: true, readOnly: false},restore : {show: true},saveAsImage : {show: true}}},
dataRange: {min : 0,show: false,max : 100,y: "60%",calculable : true,color: ["#ff3333", "orange", "yellow","lime","aqua"]},
series : [{
type:"map",itemStyle:{normal:{borderColor:"rgba(100,149,237,1)",borderWidth: 0.5,areaStyle:{color: "#1b1b1b"}}},data:[],geoCoord:{
"北京": [116.413554,39.911013],
"上海": [121.480237,31.236305],
"广州": [113.270793,23.135308],
"大连": [121.621391,38.919345],
"南宁": [108.373351,22.823037],
"南昌": [115.864528,28.687675],
"拉萨": [91.121025,29.650088],
"长春": [125.33017,43.82178],
"包头": [109.846755,40.663636],
"重庆": [106.557165,29.570997]},
markLine : {smooth:true,effect : {show: true,scaleSize: 1,period: 30,color: "#fff",shadowBlur:10},itemStyle:{color:"red",normal:{borderWidth:1,lineStyle:{type: "solid",shadowBlur: 10},label:{show:false}}},data:[
[{name:"北京"}, {name:"上海",value: 70}],
[{name:"北京"}, {name:"广州",value: 20}],
[{name:"北京"}, {name:"大连",value:100}],
[{name:"北京"}, {name:"南宁",value:100}],
[{name:"北京"}, {name:"南昌",value: 40}],
[{name:"北京"}, {name:"拉萨",value: 80}],
[{name:"北京"}, {name:"长春",value: 40}],
[{name:"北京"}, {name:"包头",value: 60}],
[{name:"北京"}, {name:"重庆",value: 70}]]},
markPoint:{symbol:"emptyCircle",symbolSize:function (v){return 10 + v/10},effect:{show: true,shadowBlur:0},itemStyle:{normal:{label:{show:true}}},data:[
{name:"上海",value: 70},
{name:"广州",value: 20},
{name:"大连",value:100},
{name:"南宁",value:100},
{name:"南昌",value: 40},
{name:"拉萨",value: 80},
{name:"长春",value: 40},
{name:"包头",value: 60},
{name:"重庆",value: 70}]}
}]
};
myChart.setOption(fx);</script></body>
</html>'
Set-Content C:\test666.html -Encoding UTF8 -Value $M
ii 'C:\test666.html'如果你安装了SqlServer模块则可以直接使用相关封装好的API命令：
https://docs.microsoft.com/zh-cn/powershell/module/sqlserver/invoke-ascmd?view=sqlserver-ps
$ServerName = (netstat -ano |findstr (Get-Process msmdsrv).Id)[0].split(" ")[6]
Invoke-ASCmd -Server:$ServerName -Query "SELECT {[Measures].[度量值]} ON COLUMNS FROM [Model]"Invoke-ASCmd -Server:$ServerName -Query:"TMSL命令（注意转义双引号）"
使用TMSL注意版本兼容级别compatibilityLevel参数，低版本可能需要嵌入xmla中使用，，，
Invoke-ProcessASDatabase -Server "127.0.0.1:1814" -DatabaseName "49b0538d-153c-44b0-ad0e-37c644d73219" -RefreshType "Full"
Invoke-ProcessCube -Server "127.0.0.1:1814" -Name "Model" -Database "49b0538d-153c-44b0-ad0e-37c644d73219" -ProcessType "ProcessDefault"
Invoke-ProcessTable -Server "127.0.0.1:1814" -TableName "table" -Database "49b0538d-153c-44b0-ad0e-37c644d73219" -RefreshType Full
Invoke-ProcessPartition -Server "127.0.0.1:1814" -PartitionName "table-a329063f-a0b1-414d-a0ab-6bfa1082fde1" -TableName "table" -Database "49b0538d-153c-44b0-ad0e-37c644d73219" -RefreshType Full除了上面的MDX、DAX语句以及xmla脚本（适用版本广些），还有TMSL的json命令也是相当强大！！！
<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
<Command>
<Statement>
SELECT [Measures].MEMBERS ON COLUMNS FROM [Model]
</Statement>
</Command>
<Properties>
<PropertyList>
<DataSourceInfo>Provider=MSOLAP;Data Source=127.0.0.1:2230;</DataSourceInfo>
<Catalog>e9a00366-0e9b-41ac-b6bc-7feab0901472</Catalog>
<Format>Multidimensional</Format>
<AxisFormat>ClusterFormat</AxisFormat>
</PropertyList>
</Properties>
</Execute> 
$TMSL=@"
{
"alter":{
"object":
{"database":$dbname,
"dataSource":$dsname
},
"dataSource":{
"name":"吹牛逼"
}
}
}
"@
TMSL-M数据源创建
{
"createOrReplace": {
"parentObject": {
"database": "MyDatabase",
"table": "吹牛逼"
},
"table": {
"name": "吹牛逼",
"columns": [
{
"name": "成绩",
"dataType": "int64",
"sourceColumn": "成绩"
},
{
"name": "姓名",
"dataType": "string",
"sourceColumn": "姓名"
}
],
"partitions": [
{
"name": "Partition",
"dataView": "full",
"source": {
"type": "m",
"expression": [
"let",
" Source=Table.FromColumns({{100,99},{\"畅心\",\"吹水\"}},{\"成绩\",\"姓名\"})",
"in",
" Source"
]
}
}
]
}
}
}另一个我从TOM里面使用命令：[Microsoft.AnalysisServices.Tabular.JsonScripter]::ScriptAlter($Server.Databases[0].Model.Tables[2].Partitions[0])可以得到TMSL相关命令，然后可以对下面代码简化修改为自己需要的命令即可。
https://docs.microsoft.com/zh-cn/analysis-services/tmsl/alter-command-tmsl?view=asallproducts-allversions
{
"alter": {
"object": {
"database": "49b0538d-153c-44b0-ad0e-37c644d73219",
"table": "表",
"partition": "表-f56011eb-9730-4f85-bcac-f753715e6ec3"
},
"partition": {
"name": "表-f56011eb-9730-4f85-bcac-f753715e6ec3",
"mode": "import",
"source": {
"type": "m",
"expression": "let 源 = Table.FromColumns({{1..9}}) in 源"
}
}
}
}其他TMSL命令：
Attach 命令 (TMSL)
Backup 命令 (TMSL)
Create 命令 (TMSL)
Delete 命令 (TMSL)
Detach 命令 (TMSL)
MergePartitions 命令 (TMSL)
Refresh 命令 (TMSL)
Restore 命令 (TMSL)
Sequence 命令 (TMSL)
Synchronize 命令 (TMSL)
xmla中引入TMSL
<Statement xmlns="urn:schemas-microsoft-com:xml-analysis">
{
"refresh": {
"type": "automatic",
"objects": [
{
"database": "********"
}
]
}
}
</Statement>另外也可以利用DMX语句从行集架构深度挖掘，，，
版本问题真的很头大，，，
https://docs.microsoft.com/zh-cn/analysis-services/tabular-models/tabular-model-programming-for-compatibility-level-1200?view=asallproducts-allversions
道高一尺 魔高一丈
https://pbihub.cn/users/44
自由转载-非商用-非衍生-保持署名（创意共享3.0许可证）
