Deploying SSAS MDX Cubes

riksmith101 · September 23, 2020, 7:09pm

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 · September 23, 2020, 7:09pm

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 · September 24, 2020, 12:13pm

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 · September 24, 2020, 7:09pm

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 · September 24, 2020, 7:26pm

Thanks Justin.

riksmith101 · September 25, 2020, 2:26pm

Hi @Justin_Walsh

Thought to add some screenshots for both tasks:

Cheers

Justin_Walsh · September 28, 2020, 1:45pm

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 · September 28, 2020, 4:02pm

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

riksmith101 · September 29, 2020, 4:03pm

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 · September 29, 2020, 5:00pm

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 · September 29, 2020, 5:36pm

Hi Justin,

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

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

riksmith101 · September 30, 2020, 9:27am

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 · October 1, 2020, 6:00pm

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.