Add new partition for analysis services by powershell

最新推荐文章于 2024-04-15 16:46:25 发布

原创最新推荐文章于 2024-04-15 16:46:25 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

SSAS 专栏收录该内容

3 篇文章

订阅专栏

本文展示了一个使用PowerShell脚本创建SQL Server Analysis Services (SSAS)中多维数据集分区的具体实例。通过加载必要的组件并连接到SSAS服务器，脚本能够查找指定的数据库、立方体和度量组，并基于现有分区创建新的分区。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices");

$ASConnectionString = "Data Source = lewis-server ; Initial Catalog = CapitalDW ; Provider = MSOLAP; Impersonation Level = Impersonate " ;
$ASServer = New-object Microsoft.AnalysisServices.Server ;

$ASServer.connect($ASConnectionString);

##get-content Variable::ASConnectionString
##get-content Variable::ASServer
$DatabaseName = "CapitalDW"
$DatabaseID = $ASServer.Databases.FindByName($DatabaseName).ToString() ;
##get-content Variable::DatabaseID;
write-host "Databaseid:$DatabaseID";

##Cube
$dbName = $ASServer.Databases[$DatabaseID];
##get-content Variable::dbName

$cubeName = "Capital DW" ;
$cubeId = $dbName.cubes.FindByName($cubeName).ToString();

$measuregroupName = "Capital History" ;
$measuregroupid = $dbName.cubes[$cubeID].Measuregroups.FindByName($measuregroupName).ToString();
write-host "measuregroupid is : $measuregroupid ";

$newPartitionName = "Capital History 2012";

$sourceID = $dbName.cubes[$cubeID].Measuregroups.FindByName($measuregroupName).Partitions.FindByName("Capital History 2011").DataSource.ToString();
write-host "data source id : $sourceID";

$partition= $dbName.cubes[$cubeID].Measuregroups.FindByName($measuregroupName).Partitions.Add($newPartitionName);
$partition.Source = new-object Microsoft.AnalysisServices.QueryBinding( "$sourceID", "SELECT [dbo].[CapitalHistory].[itm_system_ky],[dbo].[CapitalHistory].[capital_value],[dbo].[CapitalHistory].[PK_Date]
FROM [dbo].[CapitalHistory]
WHERE pk_date>='2012-01-01' and pk_date<'2013-01-01'" );
$dbName.cubes[$cubeID].Measuregroups.FindByName($measuregroupName).Partitions.FindByName($newPartitionName).update();
write-host "new partition: $partition"