Deploying SSAS MDX Cubes

[riksmith101]

Hi, I am a newbie to Octopus, and was wondering how to deploy cubes using the community SSAS deploy object? This for deployment to a SSAS instance.

I have set it up and currently it complains about:
Extracting package ‘C:\Octopus\PROD\Files\0.51@S200@680BB085BCD0AD408238F74AB3A1075D.nupkg’ to ‘C:\Octopus\PROD\Work\20200923163259-233-42\0.51’

Error

NotSpecified: Exception setting “CurrentDirectory”: “Path cannot be the empty string or all whitespace.”
Error At C:\Octopus\PROD\Work\20200923163259-233-42\Script.ps1:110 char:1
Error

• [System.Environment]::CurrentDirectory = $installDirPath
  Error

Error
at , C:\Octopus\PROD\Work\20200923163259-233-42\Script.ps1: line 110
Error
at , : line 1
Error
at , C:\Octopus\PROD\Work\20200923163259-233-42\Octopus.FunctionAppenderContext.ps1: line 116
Error

at , C:\Octopus\PROD\Work\20200923163259-233-42\Bootstrap.Octopus.FunctionAppenderContext.ps1: line 1714

Any ideas?

[Justin_Walsh]

Hi @riksmith101!

Thanks for reaching out - looking at that community template, it’s kind of a funky way of using this one. It’s coded in such a way that it’s actually the second step in a process, after a Deploy a Package step, which will push the SSAS package to the target.

The Deploy SSAS from Package step then looks at the output of the Deploy a Package step for the installation directory, and performs the actual SSAS deployment.

In the parameters in your SSAS step, you’d reference the name of the Deploy a Package step to obtain the information.

Here’s some screenshots to illustrate the workflow:

I hope this helps, and please don’t hesitate to let me know if you have any further questions!

[riksmith101]

Thanks Justin. Yup after some more digging I added a Deploy a Package task and then referenced it in the Deploy SSAS from the package task.

Thereafter I got quite a random error saying “The term ‘s’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again”

Any ideas? Ta

Updating C:\Install\OctoCube.deploymenttargets
Updating C:\Install\OctoCube.configsettings
Updating C:\Install\OctoCube.deploymentoptions
Beginning search of registry starting in HKLM:\Software\Wow6432Node\Microsoft\Microsoft SQL Server\ … Found 7 item(s) … Searching keys for ClientSetup folder … Found 2 item(s), searching in descending order … Found C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe … C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe “C:\Install\OctoCube.asdatabase” /s:“C:\Install\Log.txt”
ObjectNotFound: The term ‘s’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:120

• … ow6432Node\Microsoft\Microsoft SQL Server\ … Found 7 item(s) … Se …

[Justin_Walsh]

Thanks for getting back to me, @riksmith101, I’m glad you were able to get further in your deployment. It looks like something strange is going on with the script in the community template, like it’s putting a carriage return in to split the command args into a separate command. We’re digging into this, and I’ll post my findings to you ASAP

[riksmith101]

Thanks Justin.

[riksmith101]

Hi @Justin_Walsh

Thought to add some screenshots for both tasks:

Cheers

[Justin_Walsh]

Thanks Rik,

We took a look at the SSAS package, and found a few areas where we could improved, we’ve done these updates and it is currently going through the review process. A new version of the step template should be available today with these updates.

[riksmith101]

Fab, I will test as soon as it’s available.Cheers

[riksmith101]

Hi Justin, there was a new version available which I downloaded and configured. However the error message is similar:

16:53:21 Verbose | Setting CurrentDirectory to ‘C:\error’
16:53:29 Info | Updating C:\error\OctoCube.deploymenttargets
16:53:29 Info | Updating C:\error\OctoCube.configsettings
16:53:29 Info | Updating C:\error\OctoCube.deploymentoptions
16:53:29 Info | C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Microsoft.AnalysisServices.Deployment.exe “C:\error\OctoCube.asdatabase” /s:“C:\error\Log.txt”
16:53:29 Error | ObjectNotFound: The term ‘C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Microsoft.AnalysisServices.Deployment.exe’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
16:53:29 Error | At C:\Octopus\PROD\Work\20200929155320-449-8\Script.ps1:87 char:5
16:53:29 Error | + & $exe $ssasArguments
16:53:29 Error | + ~~~~
16:53:29 Error | at , C:\Octopus\PROD\Work\20200929155320-449-8\Script.ps1: line 87
16:53:29 Error | at , : line 1
16:53:29 Error | at , C:\Octopus\PROD\Work\20200929155320-449-8\Octopus.FunctionAppenderContext.ps1: line 116
16:53:29 Error | at , C:\Octopus\PROD\Work\20200929155320-449-8\Bootstrap.Octopus.FunctionAppenderContext.ps1: line 1975
16:53:29 Error | at , : line 1
16:53:29 Error | at , : line 1
16:53:29 Verbose | Process C:\Windows\system32\WindowsPowershell\v1.0\PowerShell.exe in C:\Octopus\PROD\Work\20200929155320-449-8 exited with code 1
16:53:29 Verbose | Updating manifest with output variables
16:53:29 Verbose | Updating manifest with action evaluated variables
16:53:29 Verbose | Released worker worker 1 from lease WorkerTaskLeases-282
16:53:29 Fatal | The remote script failed with exit code 1
16:53:29 Fatal | The action Cube deployment on a Worker failed
|
| == Success: Apply retention policy on Tentacles ==
16:53:20 Info | Apply retention policies…
16:53:20 Info | Tentacle retention policy set to ‘keep all’
16:53:20 Verbose | Apply Tentacle Retention Policy completed

[Justin_Walsh]

Hi @riksmith101,

Thanks for getting back to us - it looks like it’s finding multiple versions of Microsoft.AnalysisServices.Deployment.exe and that’s throwing off the script (and something we didn’t account for in our updates - we’re adjusting the template to account for this now, and hope to have an update in the Library ASAP.

If you’re comfortable manually editing the powershell script inside the step template, you could solve this in your particular scenario by changing

$ssasExe = Get-ChildItem -Path \"C:\\Program Files (x86)\" -Recurse | Where-Object {$_.Name -eq \"Microsoft.AnalysisServices.Deployment.exe\"}\n

to

$ssasExe = Get-ChildItem -Path \"C:\\Program Files (x86)\\Microsoft SQL Server\" -Recurse | Where-Object {$_.Name -eq \"Microsoft.AnalysisServices.Deployment.exe\"}\n

Apologies for the inconvenience caused by this!

[riksmith101]

Hi Justin,

Just hacked the PS script with the new changes and it’s thrown an error:

Invoking target script C:\Octopus\PROD\Work\20200929173123-465-5\Script.ps1 with parameters.
18:31:24 Error | ParserError: At C:\Octopus\PROD\Work\20200929173123-465-5\Script.ps1:16 char:119
18:31:24 Error | + … )\Microsoft SQL Server" -Recurse | Where-Object {$.Name -eq "Micro …
18:31:24 Error | + ~
18:31:24 Error | You must provide a value expression following the ‘-eq’ operator.
18:31:24 Error | At C:\Octopus\PROD\Work\20200929173123-465-5\Script.ps1:16 char:120
18:31:24 Error | + … -Object {$.Name -eq “Microsoft.AnalysisServices.Deployment.exe”}\n
18:31:24 Error | + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
18:31:24 Error | Unexpected token ‘“Microsoft.AnalysisServices.Deployment.exe”’ in expression or statement.
18:31:24 Error | At C:\Octopus\PROD\Work\20200929173123-465-5\Script.ps1:16 char:119
18:31:24 Error | + … )\Microsoft SQL Server" -Recurse | Where-Object {$_.Name -eq "Micro …
18:31:24 Error | + ~
18:31:24 Error | at , : line 1
18:31:24 Error | at , C:\Octopus\PROD\Work\20200929173123-465-5\Octopus.FunctionAppenderContext.ps1: line 116
18:31:24 Error | at , C:\Octopus\PROD\Work\20200929173123-465-5\Bootstrap.Octopus.FunctionAppenderContext.ps1: line 2122
18:31:24 Error | at , : line 1
18:31:24 Error | at , : line 1
18:31:24 Verbose | Process C:\Windows\system32\WindowsPowershell\v1.0\PowerShell.exe in C:\Octopus\PROD\Work\20200929173123-465-5 exited with code 1
18:31:24 Verbose | Updating manifest with output variables
18:31:24 Verbose | Updating manifest with action evaluated variables
18:31:24 Fatal | The remote script failed with exit code 1
18:31:24 Fatal | The action Run a CUBE Deploy on PROD Deployment failed

[riksmith101]

Hi @Justin_Walsh

I changed the PS further and forced the registry scan to only look C:\Program Files (x86)\Microsoft SQL Server\140\ and this worked:

Search for file

$ssasExe = Get-ChildItem -Path "C:\Program Files (x86)\Microsoft SQL Server\140\" -Recurse | Where-Object {$_.Name -eq "Microsoft.AnalysisServices.Deployment.exe"}

BR

[Justin_Walsh]

Just a quick note, @riksmith101 - we’ve updated the step template better detect and handle multiple versions and implementations of the tools now. This should be live in the library.