none
SSAS performance maintenance powershell script with AggregationDesign.DesignAggregations method

    General discussion

  • hey there.

    currently i am thinking about how to establish some basic aggregations level in our system, especially with respect to 

    https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models-olap-logical-cube-objects/aggregations-and-aggregation-designs

    After the aggregation has been created, if the structure of a cube ever changes, or if data is added to or changed in a cube's source tables, it is usually necessary to review the cube's aggregations and process the cube again.

    It has occurred that there were some efforts in order to solve the problem which use

    AggregationDesign.DesignAggregations Method (Double, Double, Int64, Boolean) method

    https://sqlkiller.wordpress.com/2014/06/23/ssas-performance-maientnance-powershell-script/

    With some corrections the script seems to be a working one (and i am going to enrich it with the second usage log designaggregations method) but perhaps there are some more current approaches to the problem - please advise if there are

    [CmdletBinding(DefaultParameterSetName="DB")]
    Param(
        [Parameter(Mandatory=$true, 
                   Position=0, 
                   ParameterSetName="DB", 
                   ValueFromPipelineByPropertyName=$true,
                   HelpMessage="Server is required")]
        [ValidateNotNullOrEmpty()]
        [STRING] $Server,
        [Parameter(Mandatory=$true, 
                   Position=1, 
                   ParameterSetName="DB", 
                   ValueFromPipelineByPropertyName=$true,
                   HelpMessage="Database is required")]
        [ValidateNotNullOrEmpty()]
        [STRING] $Database,
        [Parameter(Mandatory=$false,
                   Position=2, 
                   ParameterSetName="DB", 
                   ValueFromPipelineByPropertyName=$true,
                   HelpMessage="Password to Cube's DataSource, if needed")]
        [STRING] $SQLDataSourcePassword = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR((Read-Host -asSecureString SQLDataSourcePassword))),
        [Parameter(Mandatory=$false,
                   ParameterSetName="DB", 
                   ValueFromPipelineByPropertyName=$true,
                   HelpMessage="1 Aggregation per MG's Data Partition/1 Aggregation per MG (default)")]
        [SWITCH] $AggregatePerPartition,
        [Parameter(Mandatory=$false,
                   ParameterSetName="DB", 
                   ValueFromPipelineByPropertyName=$true,
                   HelpMessage=" if AggregatePerPartition eq true this is invalid")]
        [SWITCH] $CreateInexistingGlobalAgg,
        [Parameter(Mandatory=$false,
                   ParameterSetName="DB", 
                   ValueFromPipelineByPropertyName=$true)]
        [SWITCH] $DontProcessCube,
        [Parameter(Mandatory=$false,
                   ParameterSetName="DB", 
                   ValueFromPipelineByPropertyName=$true)]
        [SWITCH] $DontCubeEstimateCount,
        [Parameter(Mandatory=$false,
                   ParameterSetName="DB", 
                   ValueFromPipeline=$true, 
                   ValueFromPipelineByPropertyName=$true)]
        [SWITCH] $DontDimensionEstimateCount,
        [Parameter(Mandatory=$false,
                   ParameterSetName="DB", 
                   ValueFromPipelineByPropertyName=$true)]
        [SWITCH] $DontProcessDimensions,
        [Parameter(Mandatory=$false,
                   ParameterSetName="DB", 
                   ValueFromPipelineByPropertyName=$true)]
        [SWITCH] $ProcessDimensionsFull,
        [Parameter(Mandatory=$false,
                   ParameterSetName="DB", 
                   ValueFromPipelineByPropertyName=$true)]
        [SWITCH] $DontProcessAggregations,
        [Parameter(Mandatory=$false, 
                   ParameterSetName="DB", 
                   HelpMessage="Filter Cubes to Process")]
        [ValidateNotNullOrEmpty()]
        [STRING] $FilterCube,
        [Parameter(Mandatory=$false, 
                   ParameterSetName="DB", 
                   HelpMessage="Filter MGs to Process")]
        [ValidateNotNullOrEmpty()]
        [STRING[]] $FilterMGs
         )
    
    #Region Try
    function Try_PS1{
        Param(
            [ScriptBlock] $Command = $(throw "The parameter -Command is required."),
            [ScriptBlock] $Catch   = { throw $_ },
            [ScriptBlock] $Finally = {}
        )
        & {
            $local:ErrorActionPreference = "SilentlyContinue"
           
            trap
            {
                trap
                {
                    & {
                        trap { throw $_ }
                        &$Finally
                    }
                   
                    throw $_
                }
               
                $_ | & { &$Catch }
            }
           
            &$Command
        }
    
        & {
            trap { throw $_ }
            &$Finally
        }
    }#End Function Try
    #endregion
    #region replaceFieldsInString
    function replaceFieldsInString{
        Param([STRING] $Str2replace,
              [Data.DataRow] $row
             )
        For($i = 0;$i -lt ($row.Table.Columns.Count);$i += 1) {
                $Str2replace = $Str2replace.Replace("@" + $row.Table.Columns[$i].ColumnName + "@", $row[$i].ToString())
        }#Next
        Return $Str2replace
    }#End function replaceFieldsInString
    #endregion
    #region DBQuery_Conn
    function DBQuery_Conn{ #Data.DataTable
        Param([STRING] $SQL,
              [System.Data.OleDb.OleDbConnection] $connection)
        Try_PS1{
            If ($connection.State -ne ([System.Data.ConnectionState]"Closed")){$connection.Close()}
            $connection.Open() >$null
            #$tran = $connection.BeginTransaction
            $cmd = New-Object Data.OleDb.OleDbCommand($SQL, $connection, $tran)
            $cmd.CommandTimeout = 0
            $da = New-Object Data.OleDb.OleDbDataAdapter($cmd)
            $dt = New-Object "Data.DataTable"
            $da.SelectCommand = $cmd
            $da.FillSchema($dt, [Data.SchemaType]'Source') >$null
            $da.Fill($dt) > $null
            return @(,$dt)
            #$tran.Commit()
        }-Catch{
            If ($tran){$tran.Rollback()}
            $(Throw ("DBQuery_Conn: " + $error[0]))
        }
        #If ($dt){
        #    $dt.Dispose() > $null
        #    $dt = $NULL
        #}#End If
        If ($tran){
            $tran.Dispose() > $null
            $tran = $NULL
        }#End If
        If ($da){
            $da.Dispose() > $null
            $da = $NULL
        }#End If
        If ($cmd){
            $cmd.Dispose() > $null
            $cmd = $NULL
        }#End If
    }#End Function DBQuery
    #endregion
    #region DBQuery_ConnString
    function DBQuery_ConnString{ # As Data.DataTable
        Param([STRING] $SQL,
              [STRING] $ConnectionString)
        $conn = New-Object Data.OleDb.OleDbConnection($ConnectionString)
        $dt = DBQuery_Conn -SQL $SQL -connection $conn
        return @(,$dt)
        DestroyConnection -Connection $conn > $null
    }#End Function DBQuery
    #endregion
    #region DestroyConnection
    function DestroyConnection{
        Param([System.Data.OleDb.OleDbConnection] $connection)
        If ($connection){
            If ($connection.State -ne ([System.Data.ConnectionState] "Closed")){$connection.Close()>$NULL}
            $connection.Dispose() > $null
            $connection = $NULL
        }#End If
    }#End Function DestroyConnection
    #endregion
    #region GetConectionString from AS
    function DS_GetConnectionString{
        param([Microsoft.AnalysisServices.DataSource] $xmlaDS,
              [String]$pass)
        $connStringWPass = $xmlaDS.ConnectionString
        IF ($xmlaDS.ConnectionStringSecurity -eq ([Microsoft.AnalysisServices.ConnectionStringSecurity]"PasswordRemoved")){
            $connStringWPass = $connStringWPass.trimEnd([CHAR[]](";"))
            $connStringWPass += ";PASSWORD=$pass;"
        }
        IF ($connStringWPass -notmatch "Application Name"){
            $connStringWPass = $connStringWPass.trimEnd([CHAR[]](";"))
            $connStringWPass = "$connStringWPass;Application Name=AutoPartition PS (" + $xmlads.parent.name + ");"
        }#End If
        return $connStringWPass
    }
    #
    
    #region Estimate Partition Count
    function PT_EstimateCount{
        Param([Microsoft.AnalysisServices.Partition] $xmlaPT,
              [switch] $CommitChanges,
              [String] $pass
             )
        Try_PS1{
            
            Out-Log ("                Partition: $xmlaPT - Estimating Count")
            If ($xmlaPT.Source.gettype().name -eq "QueryBinding"){
                $SQLCount = "SELECT Contagem = COUNT(*) FROM (" + ([Microsoft.AnalysisServices.QueryBinding] $xmlaPT.Source).QueryDefinition + ")x"
            }elseif ($xmlaPT.Source.gettype().name -match "TableBinding"){#DsvTableBinding or TableBinding
                if (($xmlaPT.Source.DbSchemaName.DbSchemaName -ne $null) -and ($xmlaPT.Source.DbSchemaName.DbTableName -ne $null)){
                     $SQLCount = "SELECT contagem = COUNT(*) FROM " + $xmlaPT.Source.DbSchemaName.DbSchemaName + "." + `
                                 $xmlaPT.Source.DbSchemaName.DbTableName
                }elseif (($xmlaPT.Source.DbSchemaName -ne $null) -and ($xmlaPT.Source.DbTableName -ne $null)){
                     $SQLCount = "SELECT contagem = COUNT(*) FROM " + $xmlaPT.Source.DbSchemaName + "." + `
                                 $xmlaPT.Source.DbTableName
                }else{
                    $SQLCount = "SELECT contagem = COUNT(*) FROM "
                    $Tableprop = ($xmlapt.datasourceview.schema.tables|select *|?{$_.tablename -eq $xmlaPT.Source.tableid}).extendedproperties
                    if (($Tableprop.TableType.ToString() -eq "Table") -or (($Tableprop.TableType.ToString() -eq "View"))){
                        IF ($tableProp.QueryDefinition -ne $null){
                            $SQLCount += "(" + $Tableprop.QueryDefinition + ") x"
                        }else{
                            IF (($Tableprop.DbSchemaName + ".") -ne "."){$SQLCount += $Tableprop.DbSchemaName + "."}
                            $SQLCount += $Tableprop.DbTableName
                        }
                    }else{
                        Out-Log ($Tableprop.TableType.ToString()) -COLOR Orage
                    }
                }
            }
            $countTable = DBQuery_connstring -SQL $SQLCount -ConnectionString (DS_GetConnectionString -xmlaDS $xmlapt.DataSource -pass $pass)
            If ($countTable.Rows.Count -eq 1) {
                Out-Log ("                Partition: $xmlaPT - Estimated Count = " + ("{0:N0}" -f($countTable.Rows[0][0])))
                $xmlaPT.EstimatedRows = [Long] $countTable.Rows[0][0]
                If ($CommitChanges) {
                    Out-Log ("                Partition: $xmlaPT - Commiting Estimated Count = " + ("{0:N0}" -f($countTable.Rows[0][0])))
                    $xmlaPT.update() > $NULL
                }
            }
        } -Catch{
            Out-Log $error[0] -color red
            Out-Log ("                Partition: $xmlaPT- Error Estimating Count") -color darkblue
        }
    }
    #endRegion
    
    #region Estimate dimension attribute Count
    function DimAtt_EstimateCount{
        Param([Microsoft.AnalysisServices.DimensionAttribute] $xmlaDimAtt,
              [switch] $CommitChanges,
              [String] $pass
             )
        Try_PS1{
            
            Out-Log ("                Attribute: <$xmlaDimAtt> - Estimating Count")
            $count = $true
            $tabela = $null
            $SQLCount = "SELECT Contagem = COUNT(*) FROM (SELECT DISTINCT ";
            ForEach ($keycol in $xmlaDimAtt.KeyColumns){
                If ($keycol.Source.gettype().name -eq "ColumnBinding"){
                    If (($tabela -eq $null) -or ($tabela -eq $keycol.Source.TableID)){
                        $tabela = $keycol.Source.TableID
                    }else{$count = $false;break}
                    $Tableprop = ($xmlaDimAtt.parent.datasourceview.schema.tables|select *|?{$_.tablename -eq $tabela})
                    if ($TableProp -ne $null){
                        $col = $TableProp.Columns[($keycol.Source.ColumnId)]
                        $colExt = $col.ExtendedProperties
                        $SQLCount += ((((($colExt.ComputedColumnExpression),('[' + $colExt.DbColumnName + ']'),('[' + $keycol.Source.ColumnId + ']')) -ne $null) -ne "") -ne "[]")[0]
                        $SQLCount +=  " AS [" + $keycol.Source.ColumnId + "],"
                    }
                }else{
                    Out-Log ($keycol.Source.gettype().name) -COLOR Orage
                    $count = $false
                    break
                }
            }
            if ($count){
                $Tableprop = ($xmlaDimAtt.parent.datasourceview.schema.tables|select *|?{$_.tablename -eq $tabela}).extendedproperties
                if (($Tableprop.TableType.ToString() -eq "Table") -or (($Tableprop.TableType.ToString() -eq "View"))){
                    $SQLCount += "fake = 0 from "
                    IF ($tableProp.QueryDefinition -ne $null){
                        $SQLCount += "(" + $Tableprop.QueryDefinition + ")y ) x"
                    }else{
                        IF (($Tableprop.DbSchemaName + ".") -ne "."){$SQLCount += $Tableprop.DbSchemaName + "."}
                        $SQLCount += $Tableprop.DbTableName + ") x"
                    }
                }else{
                    Out-Log ($Tableprop.TableType.ToString()) -COLOR Orage
                    $count = $false
                }
            }
            if ($count){
                $countTable = DBQuery_connstring -SQL $SQLCount -ConnectionString (DS_GetConnectionString -xmlaDS $xmlaDimAtt.parent.DataSource -pass $pass)
                If ($countTable.Rows.Count -eq 1) {
                    Out-Log ("                Attribute:<$xmlaDimAtt> - Estimated Count = " + ("{0:N0}" -f($countTable.Rows[0][0])))
                    $xmlaDimAtt.Estimatedcount = [Long] (($countTable.Rows[0][0]),1|SORT-OBJECT -DESC|SELECT -FIRST 1)
                    If ($CommitChanges) {
                        Out-Log ("                Attribute: <$xmlaDimAtt> - Commiting Estimated Count = " + ("{0:N0}" -f($countTable.Rows[0][0])))
                        $xmlaDimAtt.update() > $NULL
                    }
                }
            }
        } -Catch{
            Out-Log $error[0] -color red
            Out-Log ("                Attribute: <$xmlaDimAtt> - Error Estimating Count") -color darkblue
        }
    }
    #endRegion
    
    
    #region Out-Log
    function script:Out-Log{ # As Data.DataTable
        Param([STRING[]] $LogString,
              [STRING] $Color = "")
        IF(($Color -eq "") -or ($Color -eq $NULL)){
            (get-date -format s) + " - " + $LogString
        }ELSE {(get-date -format s) + " - " + $LogString|write-host -foregroundColor $Color}
    }#End Function Out-Log
    #endRegion
    #region PS Body
    $ProcessCube = -not $DontProcessCube
    $CubeEstimateCount = -not $DontCubeEstimateCount
    $DimensionEstimateCount = -not $DontDimensionEstimateCount
    $ProcessDimension = -not $DontProcessDimensions
    $ProcessAggregations = -not $DontProcessAggregations
    Out-Log "AggregatePerPartition $AggregatePerPartition"
    Out-Log "ProcessCube $ProcessCube"
    Out-Log "Cube EstimateCount $CubeEstimateCount"
    Out-Log "Process Dimension $ProcessDimension"
    Out-Log "Dimension EstimateCount $DimensionEstimateCount"
    Out-Log "AutoPartition Analysis Cube"
    Try_PS1{
        [reflection.assembly]::loadwithpartialname("Microsoft.AnalysisServices") | Out-Null
        $ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Application Name = AutoPartition (PShell);"
        Out-Log "Connecting to server $Server"
        $xmlaServer = New-Object Microsoft.AnalysisServices.Server
        $xmlaServer.Connect($ConnectionString) > $NULL
        Out-Log ("Connected to Server <" + $xmlaServer.ConnectionInfo.Server + '\' + $xmlaServer.ConnectionInfo.InstanceName + ">; Catalog <" + $xmlaServer.ConnectionInfo.Catalog + ">")
        $xmlaDB = $xmlaServer.Databases.item($xmlaServer.ConnectionInfo.Catalog)
        
        Out-Log "Dimensions - Begin"
        Try_PS1{
            $xmlaServer.BeginTransaction()
            foreach ($xmlaDim in $xmlaDB.Dimensions) {
                Out-Log "   Dimension <$xmlaDim>   Begin"
                IF ($DimensionEstimateCount){
                    Out-Log "   Dimension <$xmlaDim>     Estimating Count"
                    Try_PS1{
                        ForEach($xmlaDimAtt in $xmlaDim.Attributes){
                            Try_PS1{
                                Out-Log "   Dimension <$xmlaDim>; Attribute <$xmlaDimAtt>     Estimating Count"
                                DimAtt_EstimateCount -xmlaDimAtt ([ref] $xmlaDimAtt) -pass $SQLDataSourcePassword
                            }-Catch{
                                Out-Log $error[0] -color red
                                Out-Log "   Dimension <$xmlaDim> Estimating Count - Skipping to next Attribute" -color darkblue
                            }
                        }
                        Out-Log "   Dimension <$xmlaDim>   Commiting Counts"
                        $xmlaDim.update() > $NULL
                    }-Catch{
                        Out-Log $error[0] -color red
                        Out-Log "   Dimension <$xmlaDim> Estimating Count - Skipping" -color darkblue
                    }
                }
                If ($ProcessDimension){
                    Out-Log "   Dimension <$xmlaDim>     Processing"
                    Try_PS1{
                        IF ($ProcessDimensionsFull) {$xmlaDim.Process("ProcessFull")}
                        else {$xmlaDim.Process("ProcessDefault")}
                    }-Catch{
                        Out-Log $error[0] -color red
                        Out-Log "   Dimension <$xmlaDim> Skipping to next dimension" -color darkblue
                    }
                }
                Out-Log "   Dimension <$xmlaDim>   Finished"
            }
            Out-Log "Dimensions - Finishing"
            $xmlaServer.CommitTransaction()
        }-Catch{
            Out-Log $error[0] -color red
            $xmlaServer.RollbackTransaction() >> $NULL
        }
        Out-Log "Dimensions - Finished"
        Out-Log "Cubes - Begin"
        foreach($xmlaCubo in ($xmlaDB.Cubes|?{("",$null,$_.name) -contains $filterCube})) {
            Out-Log "   Cube <$xmlaCubo>   Begin"
            ForEach($xmlaMeasureGroup in ($xmlaCubo.MeasureGroups|?{$filterMGs -eq $null -or $filterMGs -contains $_.name})) {
                Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>  Begin"
    			$AggregateMG = -not ($xmlaMeasureGroup.Annotations.getText("DesignAggregations") -eq "FALSE")
                Try_PS1{
                    $xmlaServer.BeginTransaction() > $NULL
                    If (($xmlaMeasureGroup.IsLinked -eq $TRUE) -and ($ProcessCube)){
                        Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>  is Linked - Processing only"
                        $xmlaMeasureGroup.Process( [Microsoft.AnalysisServices.ProcessType]::ProcessFull)
                        Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>  Finished Process"
                    }Else{
                        If ($xmlaMeasureGroup.Annotations.getText("AutoPartition") -eq "TRUE") {
                            Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>  Querying Partitions"
                            $partitionListSQL = $xmlaMeasureGroup.Annotations.getText("PARTITION_SPLIT_SQL")
                            $connStringWPass = (DS_GetConnectionString -xmlaDS $xmlaCubo.DataSource -pass $SQLDataSourcePassword)
                            $Data = DBQuery_ConnString -SQL $partitionListSQL -ConnectionString $connStringWPass
                            Out-Log ("   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>  Returned " + $Data.rows.count + " Rows")
                            If ($Data.rows.count -gt 0){
                                "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>  Deleting existing " + ($xmlaMeasureGroup.Partitions).Count + " Partitions"
                                If (($xmlaMeasureGroup.Partitions).Count -gt 0){
                                    For($i = ($xmlaMeasureGroup.Partitions).Count - 1; $i -gt -1; $i-= 1){
                                        Out-Log ("           Partition: " + $xmlaMeasureGroup.Partitions[$i].Name)
                                        $xmlaMeasureGroup.Partitions[$i].Drop() > $NULL
                                    } #Next
                                }#End If
                                Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>  Deleting Existing AggregationDesigns"
                                If (($xmlaMeasureGroup.AggregationDesigns).Count -gt 0){
                                    For ($i = ($xmlaMeasureGroup.AggregationDesigns).Count - 1; $i -gt -1; $i-=1){
                                        Out-Log ("           Aggregation Design: " + $xmlaMeasureGroup.AggregationDesigns[$i].Name)
                                        $xmlaMeasureGroup.AggregationDesigns[$i].Drop()  > $NULL
                                    } #Next
                                }#End If
                                Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>  Finished Deleting Partitions & AggregationDesigns"
                                Out-Log ("   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup> Creating " + $Data.Rows.Count + " partitions & Aggregation Designs")
    							$local:MGEstimatedCount = 0;
                                foreach($row in $Data.Rows) {
                                    IF ($row["TIPO"].ToString() -eq "MIDDLE"){
                                        $partitionName = "_" + (replaceFieldsInString -str2replace $xmlaMeasureGroup.Annotations.getText("PTNAMESUFFIX") -row $row)
                                    }else{$partitionName = ""}
                                    $PartitionName = $xmlaMeasureGroup.Name + "_" + $row["TIPO"].ToString() + $PartitionName
                                    Out-Log  "                Partition: $PartitionName"
                                    $xmlaPT = $xmlaMeasureGroup.Partitions.Add($PartitionName, "PT $PartitionName")
                                    $xmlaPT.StorageMode = [Microsoft.AnalysisServices.StorageMode]::Molap
                                    $xmlaPT.Source = New-object Microsoft.AnalysisServices.QueryBinding($xmlaCubo.DataSource.ID, `
                                                     (replaceFieldsInString -str2replace ($xmlaMeasureGroup.Annotations.getText("QUERY_" + $row["TIPO"].ToString())) -row $row))
                                    PT_EstimateCount -xmlaPT ([ref] $xmlaPT) -pass $SQLDataSourcePassword
                                    #FIX#part.Slice = "[Date].[Calendar Year].&[2001]";
    								$MGEstimatedCount += $xmlaPT.EstimatedRows
                                    If ($AggregateMG){
                                        If ($AggregatePerPartition){
                                            $AggName = "AGG $PartitionName"
                                            $xmlaAGG = $xmlaMeasureGroup.AggregationDesigns.Add($PartitionName, $AggName)
                                            $xmlaAGG.EstimatedRows = $xmlaPT.EstimatedRows
                                            $xmlaAGG.Update() > $NULL
                                            Out-Log ("                     AG <$xmlaAGG> EstimatedRows " + ("{0:N0}" -f $xmlaAGG.EstimatedRows))
                                        }Else{
                                            $AggName = "AGG " + $xmlaMeasureGroup.Name
                                            If (($xmlaMeasureGroup.AggregationDesigns).Count -eq 0){
                                                $xmlaAGG = $xmlaMeasureGroup.AggregationDesigns.Add($xmlaMeasureGroup.Name, $AggName)
                                                $xmlaAGG.Update() > $NULL
                                            }else{
                                                $xmlaAGG = $xmlaMeasureGroup.AggregationDesigns[$AggName]
                                            }#End If
                                            $xmlaAGG.EstimatedRows += switch ($xmlaPT.EstimatedRows){0{1}default{$xmlaPT.EstimatedRows}}
                                            Out-Log ("                     AG <$xmlaAGG> EstimatedRows " + ("{0:N0}" -f $xmlaAGG.EstimatedRows))
                                        }#End If
    									$xmlaAGG.update() > $NULL
    									$xmlaAGG.Dispose() > $NULL
                                        $xmlaPT.AggregationDesignID = $AggName
    								}
    								Out-Log ("                   MG <$xmlaMeasureGroup> EstimatedRows " + ("{0:N0}" -f $MGEstimatedCount))
                                    $xmlaPT.Update() > $NULL
                                }#Next
                            }#End If
                        }#End If
                        Elseif ($CubeEstimateCount) {
                            Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>  Started Estimating counts"
                            ForEach($xmlaAGG in $xmlaMeasureGroup.AggregationDesigns) {$xmlaAGG.EstimatedRows = 0}
                            $xmlaMeasureGroup.EstimatedRows = 0
                            ForEach($xmlapt in $xmlaMeasureGroup.Partitions) {
                                PT_EstimateCount -xmlaPT ([ref]$xmlaPT) -CommitChanges -pass $SQLDataSourcePassword
                                [Microsoft.AnalysisServices.AggregationDesignCollection] $ADC = $xmlaMeasureGroup.AggregationDesigns
                                $xmlaMeasureGroup.EstimatedRows += $xmlaPT.EstimatedRows
                                $xmlaMeasureGroup.update()
                                IF (($xmlaPT.AggregationDesignID -ne $NULL) -and (($ADC|?{$_.ID -eq $xmlaPT.AggregationDesignID}).Name -eq $xmlaPT.AggregationDesignID)){
                                    $ADC[$xmlaPT.AggregationDesignID].EstimatedRows += $xmlaPT.EstimatedRows
                                    $ADC[$xmlaPT.AggregationDesignID].update()
                                    Out-Log ("   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>; AG <" + $ADC[$xmlaPT.AggregationDesignID] + "> EstimatedRows " + ("{0:N0}" -f $ADC[$xmlaPT.AggregationDesignID].EstimatedRows))
                                }else{Out-Log ("   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>; PT <$xmlapt>  AD <" + $xmlaPT.AggregationDesignID + "> not located") -color darkblue}
                            }
                            Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>; PT <$xmlapt>  Finished Estimating counts"
                        }
                    }
                    $xmlaServer.CommitTransaction() > $NULL
                    Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>  Processed all Partitions."
                } -Catch{
                    Out-Log ($error[0]|select *) -color red
                    $xmlaServer.RollbackTransaction() >> $NULL
                }
    			IF($AggregateMG){
    				If (-not $AggregatePerPartition`
                        -and $CreateInexistingGlobalAgg`
                        -and $xmlaMeasureGroup.AggregationDesigns.Count -eq 0){
                        out-log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>; Creating Aggregation Design"
                        $xmlaServer.BeginTransaction()
                        Try_PS1{
                            $xmlaAgg = $xmlaMeasureGroup.AggregationDesigns.Add("AutoAggregationDesign")
                            $xmlaAgg.EstimatedRows = $xmlaMeasureGroup.EstimatedRows
                            $xmlaAgg.update()
                            ForEach($xmlaPT in $xmlaMeasureGroup.Partitions){
                                $xmlaPT.AggregationDesignID = $xmlaAgg.ID
                                $xmlaPT.Update()
                            }
                            $xmlaServer.CommitTransaction()
                        } -Catch{
                            Out-Log ($error[0]|select *) -color red
                            $xmlaServer.RollbackTransaction() >> $NULL
                        }
                        #$xmlaMeasureGroup.update("ExpandFull")
                    }
    				Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>  Designing Aggregations"
                    ForEach($xmlaAGG in $xmlaMeasureGroup.AggregationDesigns) {
    					Try_PS1{
    						Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>; AD <$xmlaAGG> Starting Design"
                            if ($xmlaAGG.EstimatedRows -eq 0){
                                $xmlaAGG.EstimatedRows = 1
                                $xmlaAGG.Update() > $NULL
                            }
    						$hasfinished = $FALSE
    						$storage = 0
    						$opt = 0
    						$aggCount = 0
    						$xmlaAGG.InitializeDesign()
    						While (($storage -lt (1000 * 1024 * 1024)) -And (!$hasfinished)) {
    							$xmlaAGG.DesignAggregations([ref] $opt, [ref] $storage, [ref] $aggCount, [ref] $hasfinished)
    						}#End While
    						Out-Log ("                            opt=" + ("{0:N2}" -f$opt) + " storage=" + ("{0:N0}" -f($storage/1kb)) + "Kb aggCount=" + ("{0:N0}" -f$aggCount))
    						$xmlaAGG.FinalizeDesign()
    						$xmlaAGG.Update()
    						If ($xmlaAGG.Annotations.Contains("AggregationPercent")){
    							$xmlaAGG.Annotations.Remove("AggregationPercent")
    						}#End If
                            If ($xmlaAGG.Annotations.Contains("EstimatedSize")){
    							$xmlaAGG.Annotations.Remove("EstimatedSize")
    						}#End If
    						$xmlaAGG.Annotations.Add("AggregationPercent", $opt.ToString()) > $NULL
    						$xmlaAGG.Annotations.Add("EstimatedSize", $storage.ToString()) > $NULL
                            $xmlaAGG.Update() > $NULL
    						Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>; AD <$xmlaAGG> Finished Design"
                            if (-not $ProcessCube -and $ProcessAggregations){
                                Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup> Processing Indexes"
                                $xmlaMeasureGroup.Process([Microsoft.AnalysisServices.ProcessType]::ProcessIndexes) > $NULL
                            }
    						$xmlaAGG.Dispose() > $NULL
    					}-Catch{
    						Out-Log $error[0] -color red
    						Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup> Skipping to next design" -color darkblue
    					}
    				}# Next
    				Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup> Finished Designs."
    			}
                If ($ProcessCube){
                    Out-Log ("   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>   Processing " + ($xmlaMeasureGroup.Partitions).count + " Partitions")
                    ForEach($xmlapt in $xmlaMeasureGroup.Partitions) {
                        Try_PS1{
                            Out-Log "                           PT: <$xmlapt>"
                            $xmlapt.Process([Microsoft.AnalysisServices.ProcessType]::ProcessFull) > $NULL
                        }-Catch{
                            Out-Log $error[0] -color red
                            Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>; PT <$xmlapt>  Skiping to next partition" -color darkblue
                        }
                    }#Next
                    Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>  Finished Processing"
                }#End If
                Out-Log "   Cube <$xmlaCubo>; MG <$xmlaMeasureGroup>  Finished"
                $xmlaMeasureGroup.Dispose() > $NULL
            }#Next
            $xmlaCubo.Dispose() > $NULL
            Out-Log "   Cube <$xmlaCubo>   Finished"
        }#Next
        Out-Log "Cubes - Finished"
        $xmlaDB.Dispose() > $NULL
        $xmlaServer.Disconnect() > $NULL
        $xmlaServer.Dispose() > $NULL
    }-catch{
        OUT-log $error[0] -color red
        Out-Log " Disconecting and cleaning" -color darkblue
        Try_PS1{
            If ($xmlaServer){
                If ($xmlaServer.Connected){
                    $xmlaServer.RollbackTransaction() > $NULL
                    $xmlaServer.Disconnect() > $NULL
                }#End If
                $xmlaServer.Disconnect() > $NULL
                $xmlaServer.Dispose() > $NULL
            }#End If
        }-Catch{}
    }
    #endregion



    Sergey Vdovin

    Thursday, October 19, 2017 9:28 PM