new_ssas_deploy_cubes.ps1

Function publish-SsasDb {
    [CmdletBinding()]
    param(
        [Parameter(Position = 0, mandatory = $true)]
        [string] $TargetSsasSvr,
        [Parameter(Position = 1, mandatory = $true)]
        [string] $asSsdtBuildOutputFolder,
        [Parameter(Position = 2, mandatory = $true)]
        [string] $targetDatabase,
        [Parameter(Position = 3, mandatory = $true)]
        [int] $sqlServerVersionNumber,
        [Parameter()]
        [switch] $runscript,
        [Parameter()]
        [switch] $createScript,
        [Parameter()]
        [switch] $deployScript)
    $supportedVersionsOfSql = 12, 13, 14
    if ($sqlServerVersionNumber -notin $supportedVersionsOfSql ) {
        $msg = "version not supported. Define 12, 13 or 14."
        Throw $msg
    }
    $ASDeployWizard = "C:\Program Files (x86)\Microsoft SQL Server\$($sqlServerVersionNumber)0\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe"
    if (!($ASDeployWizard)) {
        $msg = "AS Deployment Wizard not installed."
        Throw $msg
    }
 
    if (!(Test-Path $asSsdtBuildOutputFolder)) {
        $msg = "The path to [$asSsdtBuildOutputFolder] is either not accessable or does not exist"
        Throw $msg
    }
 
    Write-Verbose "$(Get-Date): Starting" -Verbose
 
    Trap {
        Write-Error "Failed to publish database ($_.Exception.Message)" 
        break
    }
    $outputScript = Join-Path $asSsdtBuildOutputFolder "$targetDatabase.xmla" 
    $logPath = Join-Path $asSsdtBuildOutputFolder "$targetDatabase.log"
    $sourcedb = Join-Path $asSsdtBuildOutputFolder "$targetDatabase.asdatabase"
 
    if ($runscript -or $createScript) {
        $arguments = @("`"$sourceDb`"", "/s:`"$logPath`"", "/o:`"$outputScript`"")
    }
    if ($deployScript) {
        $arguments = @("`"$sourceDb`"", "/s:`"$logPath`"")
    }
    Write-Verbose "$(Get-Date): Start deployment wizard from deployment script located at [$sourceDb]. See log file [$logPath] for more info." -Verbose
    Start-Process -FilePath $ASDeployWizard -ArgumentList $arguments -Wait -WindowStyle Hidden
    Write-Verbose "$(Get-Date): End deployment wizard from deployment script located at [$sourceDb]. See log file [$logPath] for more info." -Verbose
 
    if ($runscript -or $createScript) {
        if (!(Test-Path $outputScript)) {
            Write-Error -message "Path [$outputScript] does not exist. Deployment script has not been generated as expected." -category InvalidOperation
            return
        }
    }
    if ($runscript) {
        $xmla = [string]::join([Environment]::NewLine, (Get-Content $outputScript))
        Write-Verbose "$(Get-Date): Start Database deployment script [$outputScript]" -Verbose
        $xmlaresult = Execute-Xmla $TargetSsasSvr $xmla $sqlServerVersionNumber
        Write-Verbose "Xmla Result: $xmlaresult" -Verbose
        Write-Verbose "$(Get-Date): Finished Database deployment script [$outputScript]" -Verbose
    }
    Write-Verbose "$(Get-Date): Finished" -Verbose
}
function Execute-Xmla {
    [CmdletBinding()]
    param(
        [Parameter(Position = 0, mandatory = $true)]
        [string] $TargetSsasSvr,
        [Parameter(Position = 1, mandatory = $true)]
        [string] $xmla,
        [Parameter(Position = 2, mandatory = $true)]
        [int] $sqlServerVersionNumber

    )
    begin {
        $AdomdClient =  "Microsoft.AnalysisServices.AdomdClient, Version=$sqlServerVersionNumber.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
        Add-Type -AssemblyName $AdomdClient
        Switch ($sqlServerVersionNumber)
        {
            12 {$provider = "MSOLAP.5"}
            13 {$provider = "MSOLAP.6"}
            14 {$provider = "MSOLAP.7"}
        }
        $connectionectionString = "Data Source=$TargetSsasSvr;Provider=$provider;Integrated Security=SSPI;Impersonation Level=Impersonate;"
        $connection = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection($connectionectionString)
    }
    process {
        $connection.Open()
        $comand = $connection.CreateCommand()
        $comand.CommandTimeout = 20000
        $comand.CommandType = [System.Data.CommandType]::Text
        $comand.CommandText = $xmla
        $reader = $comand.ExecuteXmlReader()
        if ($reader.Read()) {
            $return = $reader.ReadOuterXml()
        }
        return $return
    }
    end {
        $connection.Close();
        $connection.Dispose();
    }
}
cls 
get-date
publish-SsasDb -TargetSsasSvr "everestpublic\hh" -asSsdtBuildOutputFolder "C:\Users\SQLTraining\Downloads\sql-server-samples-master\sql-server-samples-master\samples\databases\wide-world-importers\wwi-ssasmd\wwi-ssasmd\bin" -targetDatabase "WideWorldImportersMultidimensionalCube" -sqlServerVersionNumber 14 -createScript
publish-SsasDb -TargetSsasSvr "everestpublic\hh" -asSsdtBuildOutputFolder "C:\Users\SQLTraining\Downloads\sql-server-samples-master\sql-server-samples-master\samples\databases\wide-world-importers\wwi-ssasmd\wwi-ssasmd\bin" -targetDatabase "WideWorldImportersMultidimensionalCube" -sqlServerVersionNumber 14 -deployscript