使用PowerShell TOM在SSAS表格模型创建分区时Add方法失效求助

使用PowerShell通过TOM连接SSAS表格模型创建分区时，代码执行到创建$NewPartition步骤均正常，但执行$_table.Partitions.Add($NewPartition)时无法生效，分区未被添加到模型中。

原代码 #

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Tabular")
#Here, the variables are declared to be used
$ServerName = "<analysis SERVICE URI>"
$DatabaseName = "ProductReportingModelPartitionTest"

$Server = New-Object Microsoft.AnalysisServices.Tabular.Server
#Connecting to the Tabular Server
$Server.Connect("$ServerName")
#Declaring which Tabular database to connect to
$DatabaseName = $Server.Databases["$DatabaseName"]
$Model = $DatabaseName.Model

function Add-NewPartition ($_table,$_originalPartition) {
   #Find the partition by name in the model
   $PartitionExists = $_table.Partitions.Find("DevPartition")
   echo($PartitionExists | Format-Table)
   #Check if the partition was already created, if not exit. 
   if (!$PartitionExists)
   {
        #Clone the reference partition that has placeholder 1=2
        $NewPartition = $_originalPartition.Clone()
        
        #Give a name to the new partition, something that can be found
        $NewPartition.Name = "deve0osl"
        #Add the new partition
        $_table.Partitions.Add( $NewPartition )
        
        #Call the refresh data process method to refresh the data of the new partition 
        $NewPartition.RequestRefresh("Data") 
   }
}


$Table = $Model.Tables["Online Stock - Seasons"]


$OriginalPartition = $Table.Partitions[0]

Add-NewPartition $Table $OriginalPartition

问题分析与修复方案 #

核心原因 #

TOM对SSAS模型的修改默认仅存在于内存中，未显式提交到服务器，导致添加的分区不会持久化。此外还有几个可能的辅助原因：

• 权限不足：执行脚本的账号缺少SSAS模型的结构修改、数据处理权限
• 异步刷新未等待：RequestRefresh是异步操作，脚本提前结束可能导致分区未完成创建
• 克隆分区属性冲突：原始分区的部分属性（如查询过滤器）可能需要调整，避免冲突

修改后的代码 #

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Tabular")
# 声明变量
$ServerName = "<analysis SERVICE URI>"
$DatabaseName = "ProductReportingModelPartitionTest"

$Server = New-Object Microsoft.AnalysisServices.Tabular.Server
# 连接表格服务器
$Server.Connect("$ServerName")
# 指定要连接的表格数据库
$Database = $Server.Databases["$DatabaseName"]
$Model = $Database.Model

function Add-NewPartition ($_table,$_originalPartition) {
   # 检查分区是否已存在
   $PartitionExists = $_table.Partitions.Find("DevPartition")
   Write-Output ($PartitionExists | Format-Table | Out-String)
   if (!$PartitionExists)
   {
        # 克隆参考分区
        $NewPartition = $_originalPartition.Clone()
        
        # 设置新分区名称
        $NewPartition.Name = "deve0osl"
        # 可选：替换原始分区的占位符查询（如果原始分区用1=2作为过滤条件）
        # $NewPartition.Source.Query = "SELECT * FROM YourSourceTable WHERE YourPartitionCondition"
        
        # 添加新分区到表
        $_table.Partitions.Add( $NewPartition )
        
        # 关键步骤：将内存中的模型更改提交到SSAS服务器
        $Model.SaveChanges()
        
        # 触发分区数据刷新
        $refreshRequest = $NewPartition.RequestRefresh("Data")
        # 等待刷新操作完成
        $Server.WaitForCompletion($refreshRequest.RefreshRequestID)
   }
}

$Table = $Model.Tables["Online Stock - Seasons"]
$OriginalPartition = $Table.Partitions[0]

Add-NewPartition $Table $OriginalPartition

关键修复点 #

1. 添加$Model.SaveChanges()：将内存中的分区添加操作提交到SSAS服务器，使修改持久化
2. 替换echo为Write-Output+Out-String：保证分区检查结果正确输出
3. 添加WaitForCompletion：等待异步刷新操作完成，确保分区数据加载完成
4. 调整数据库变量名：原代码中$DatabaseName被重复赋值为数据库对象，修改为$Database避免混淆

内容的提问来源于stack exchange，提问作者AmilaMGunawardana