SSAS Tabula Dynamic cube partition Automation By using Windows Powershell script

sudheer 1 Reputation point
2021-09-09T09:32:47.51+00:00

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

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,260 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,455 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. CarrinWu-MSFT 6,866 Reputation points
    2021-09-10T08:20:30.08+00:00

    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.


  2. Limitless Technology 39,501 Reputation points
    2021-09-14T14:44:10.22+00:00

    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,