SSAS Tabula Dynamic cube partition Automation By using Windows Powershell script

Question

Hi,

I am trying to develop a code for dynamic SSAS Cube partition automation by using windows power shell script. But I am getting compatibility issues with my version.

I found the code for SQL 2017 version but mine version is 2016.

Step 1.

Function CreatePartition {
param( [Microsoft.AnalysisServices.Tabular.Table] $table,
[string]$Expression,
[string]$Name )
System.Reflection.Assembly::LoadWithPartialName("Microsoft.AnalysisServices")
System.Reflection.Assembly::LoadWithPartialName("Microsoft.AnalysisServices.Tabular")
$newpartition = New-Object -TypeName Microsoft.AnalysisServices.Tabular.Partition;
$newpartition.Source = New-Object -TypeName Microsoft.AnalysisServices.Tabular.MPartitionSource;
$newpartition.Mode = [Microsoft.AnalysisServices.Tabular.ModeType]::Import;
$newpartition.Source.Expression = $Expression
$newpartition.Name = $Name;

        $table.Partitions.Add($newpartition);

       $result = $model.SaveChanges()
        if (-Not $result.Impact.IsEmpty)
        {
            write-output("Partition " + $Name + " been created successfully")
        }

 }

STEP. 2:- It gives existing partitions in required cube.

$server = New-Object Microsoft.AnalysisServices.Tabular.Server

$server.Connect("Server")

$db = $server.Databases["Partition"]

$model = $db.Model

$baseTable = $model.Tables["base"]

$pnames = $baseTable.Partitions.Name

foreach ($pname in $pnames|sort) {

Write-Output($pname)

}

1. it should give create new partitions dynamically.

$ProcessDate= (Get-Date)

$i=0

$PartitionToBeChecked =""

while($PartitionToBeChecked -ne "base 201701")

{

  $PartitionToBeChecked="base " + $ProcessDate.AddMonths($i).Year + $ProcessDate.AddMonths($i).Month.ToString("00")

    if ($pnames -contains $PartitionToBeChecked -eq $False)

   {

    $ThisMonth=$ProcessDate.AddMonths($i).Year.ToString() + $ProcessDate.AddMonths($i).Month.ToString("00")+"01"

   $NextMonth=$ProcessDate.AddMonths($i+1).Year.ToString() + $ProcessDate.AddMonths($i+1).Month.ToString("00")+"01"

     $Expression="""name"": ""base " + $ThisMonth + "," +

     """source"": { +
      """"query"": ""SELECT  * FROM [schema].[base] where [MONTH] = " + $ThisMonth + "," +
      """dataSource"": ""Partition""" +
      "}"

 CreatePartition $basesTable $Expression $PartitionToBeChecked

}

  $i--

}

But while executing 3rd step I got error as compatibility issues with my version. the above steps modules are works only for 2017 version. but I need 2016 version module for above steps.

Could you please help me on above issues and let me know.

Best regards,
Sudheer

Answer 1

Hi @sudheer ,

Welcome to Microsoft Q&A!

Sorry about that I just work with SSAS, not familiar with PowerShell. Based on my researched, you could use PSScriptAnalyzer to check PowerShell version compatibility. The syntax check rule simply requires a list of PowerShell versions you want to target, and will tell you if a syntax used in your script won’t work in any of those versions. For more information, please refer to Using PSScriptAnalyzer to check PowerShell version compatibility.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hello,

Thank you for reaching out .

Additionally,

Tabular Model Scripting Language (TMSL) is the command and object model definition syntax for tabular databases at compatibility level 1200, created for the SQL Server 2016 version of Analysis Services.

As per Microsoft the language this is using is Analysis Services Scripting Language (ASSL for XMLA). The importance of your database’s compatibility level & keeping it consistent is that this script will not work if you execute it against a tabular model with a Compatibility Level of 1200. XMLA is no longer used for tabular models as Microsoft changed the scripting language. SQL Server 2016 now uses TMSL for scripting Tabular model databases

Best Regards,