How to setup your computer to run PowerShell with SSAS
This information is for people who have not used PowerShell before, but would like to start using it with the Analysis Services.
How to installing Powershell:
- Windows 2008 includes powershell, so no extra installation is required
- Powershell is installed for you during SQL Server 2008 installation
- You can download PowerShell from Microsoft website
How to setup PowerShell to run your scripts:
- Before running PowerShell scripts you will need to setup Execution Policy. Please read this great article about options you have. If you choose to sign scripts, make sure you do this for all scripts you find on our website.
- You might get following error message when you try to run "Set-ExecutionPolicy" command on Windows Vista:
Set-ExecutionPolicy : Access to the registry key 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft
.PowerShell' is denied.
At line:1 char:20
+ set-executionpolicy <<<< unrestricted
To fix this start your powershell environment using "Run As Administrator" option.
- Store your PowerShell scripts in a folder and create related batch jobs that start your scripts and pass required parameters. If I actively work on 3 different databases, I usually create 3 different batch jobs that points to these databases. Here is the example of such batch job ssas-db-Vidas-AW.bat:
powershell.exe c:\projects\ps\ssas\ssas-db.ps1 -ServerName Vidas-lenovo -DatabaseName AdventureWorks > c:\projects\ps\ssas\ssas-db.log
When creating PowerShell script, add the AMO namespace definition just after the parameter definition section:
$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
If you are planning to write PowerShell scripts yourself, consider purchasing PowerShell Interactive scripting environment. I am using PowerShellPlus from Idera. I love that I have intellisense on object and that I can explore earch object and see its properties.