PowerShell for Business Intelligence – Day 1

Can PowerShell help Business Intelligence specialists?

I was very fortunate to present recently at TechEd North America on my favourite topics, Business Intelligence and Data Visualisation. Whilst I was there, I learned that the most-attended session involved PowerShell for beginners. I thought that Aaron Nelson ( blog ¦ twitter ) did a fantastic job of his presentation, and I had the good luck to car-share with Aaron on our way down to SQL Saturday South Florida, where he shared his boundless enthusiasm for all things PowerShell.

That got me thinking. I can see that DBAs and sysadmins use PowerShell to automate things, and make their lives easier. I couldn’t see anything similar for Business Intelligence developers, however, and I wondered how PowerShell could make our lives easier by automating certain aspects of our work. For example, it’s possible to use PowerShell to install Sharepoint-integrated Reporting Services, and I also use it to administer the Office365 account. 

Therefore, I’m starting a blog series which starts right at the beginning, and it’s aimed at helping Business Intelligence people to use and adopt PowerShell to make their day-to-day work easier. It’s aimed at people who might not have discovered PowerShell yet, but are happy with the way that they do things already. I like to challenge myself to ‘work smarter rather than harder’
Since this is the first part in this series, we are going to look very simply at the very basics of PowerShell. Let’s start by describing what PowerShell actually is.

Well, PowerShell is a scripting language, that is part of the standard installation of Windows 7 and Windows Server 2008 R2.  You an find it if you go to your ‘Accessories’ folder from the Start Prompt.  PowerShell allows you to write little scripts, which are simply text files which have the extension ‘.ps1’ Since the commands are stored in text files, they are accessible using your preferred text editor.

We are going to practice PowerShell by:

  • Starting an SSRS service
  • Stopping an SSRS service
  • Doing some Fact Finding about SSRS using PowerShell 
  • Installing SSRS in Integrated Mode for Sharepoint using PowerShell

Launch the Sharepoint 2010 Management Shell by going to the Start button, go to Microsoft Sharepoint 2010 Products group. You need to run the Shell as Administrator. To do this, right click on it, and select ‘Run as Administrator.

Starting the SSRS service:

get-spserviceinstance -all |where {$_.TypeName -like “SQL Server Reporting*”} | Start-SPServiceInstance

Stopping the SSRS service:

get-spserviceinstance -all |where {$_.TypeName -like “SQL Server Reporting*”} | Stop-SPServiceInstance

Here is a sample of how that might look.  Here, we have an attempt to start a service that is already started. We can see this in Yellow.

The command is then executed to stop the service. The default ‘Y’ was chosen. The service was stopped since the next command line does not show any information.

PowerShell Start Stop Service 

Doing some Fact Finding about SSRS using PowerShell 

If the service is installed and configured, here is the command to get the URLs for accessing SSRS as a service.


The following command will tell you any other servers in the Sharepoint farm where there is an install of the SSRS shared service.


Installing SSRS in Integrated Mode for Sharepoint using PowerShell

It is possible to install SSRS using PowerShell, if it is not installed already.

I’ve particularly found PowerShell an easy – and quick – way to install Reporting Services as part of an integrated Sharepoint installation. Instead of navigating the Sharepoint Central Administration and installing and configuring the SSRS service, it is straightforward to run and execute the PowerShell cmdlets. The instructions to do this activity can be found here.

Install the SSRS integrated service for Sharepoint 

Copy and paste the following command, and then press return.


If the command was successful, then there will be no message returned to the command prompt.

Install the SSRS Service Proxy


That’s all for now! In the next episode, we will look at doing more with PowerShell in Business Intelligence, along with some common error troubleshooting.

I hope that helps!