question

sudheer-8702 avatar image
0 Votes"
sudheer-8702 asked sudheer-8702 commented

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 sudheer-8702 commented

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.

· 2
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 ·

Thanks for your answer Carrin. Actually Your provided link is not helpful getting my expected result.
Apart from that Do you have any Idea about how to automate cube partitions and please guide me how can we automate.

0 Votes 0 ·
LimitlessTechnology-2700 avatar image
0 Votes"
LimitlessTechnology-2700 answered sudheer-8702 commented

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,

· 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 Do u have any idea about How can we automate cube partitions dynamically by using Power shell script please let me know.

0 Votes 0 ·