question

sudheer-8702 avatar image
0 Votes"
sudheer-8702 asked LimitlessTechnology-2700 answered

SSAS Tabula Dynamic cube partition Automation By using Windows Powershell script

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.

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

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


$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


windows-server-powershellsql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @sudheer-8702,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @sudheer-8702, I have not get a reply from you. Could the answer help you? If yes, please do "Accept Answer". By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
LimitlessTechnology-2700 avatar image
0 Votes"
LimitlessTechnology-2700 answered

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,

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.