Office 2010 Serial Excel Services in SharePoint 20

Thanks to John Campbell for putting together this post.
Ongoing management is key to any successful server product’s deployment. It can be challenging to figure out the right initial combination of settings, and to know how to further change those settings when problems arise. With that in mind, I have put this blog article together to go into what’s new in the Excel Services administration space.
First Things First: SharePoint Administration and Service Applications
Excel Services is a “Service Application” in SharePoint. Service Applications are the replacement for what used to be known as a Shared Service Provider (or SSP).

A service application is basically a global entity in the farm that performs some useful piece of functionality. In our case, Excel Services provides all the ‘Excel’ goodness in the browser that you see in SharePoint. The big things worth knowing about service applications are:
You can delegate administration of them – so the central administrator can find the Excel Service administrator and give that person rights to administer that service and nothing else.Although in general you can have multiple service applications in a farm, Excel Services is really geared with the idea of a single service application per farm in mind. The reason for this is because only the default Excel Service application will respond to requests.You can configure which physical machines in the farm run certain service applications and which don’t. So you can dedicate certain machines to be used only by certain service applications. This can be helpful when planning your deployments.Unlike the old flavor of ‘Shared Services’ in 2007, you can choose which service applications to run independently, and you aren’t forced to provision and manage a separate database for them all.They all support PowerShell! And now that the topic of PowerShell has come up, let’s talk about PowerShell…PowerShell Support
Excel Services fully supports PowerShell in the 2010 release. PowerShell is the product that provides a simple command line style scripting interface, but it can be used to go much farther than that since it supports .Net and can be used for more advanced things like calling OM directly. PowerShell scripts are also reusable – i.e. you write it once, and can run it anytime in the future. So scripting setup, or performing some regular maintenance task, etc and reusing it when the need arises is a snap.
Commands you perform in PowerShell are done using “command-lets” or cmdlets for short. All the Excel Services PowerShell cmdlets are fairly simple in nature and are all flavors of Get, Set,Office Standard 2007 Key, New, and Remove. They basically all boil down to the action of getting some object (like an Excel Service application object, or a Trusted Location object from the trusted locations list) and modifying settings on it. To read the settings you use Get-object, to change settings you use Set-object. If the object is one of many in a list (like trusted locations or trusted UDFs), you use New-object to make a new entry in the list, or Remove-object to get rid of one. Let’s take a look at a couple of quick examples to illustrate what I mean.
At the highest level, there are general, global, settings for Excel Services. What if you want to see what all the setting values are? In the 2007 release this actually wasn’t possible using stsadm. With PowerShell you can use:
Get-SPExcelServiceApplication
That will show you the ‘basic’ settings for the service application (name, type, and Id). If you want to see all the settings and their values, then you send the object (via piping) to a different cmdlet to format it as a list:
Get-SPExcelServiceApplication | Format-list *
Since I’m not specifying a name of a specific application, it will get every Excel Service running in the farm, and for each one display all its settings.
Now suppose I want to change how many sessions a user is allowed to have on a single back end server machine running Excel Services. Here is the PowerShell to do that:
Set-SPExcelServiceApplication -Identity MyExcelService –SessionsPerUserMax 50
I call the Set cmdlet on the global Excel Services service application cmdlet (SPExcelServiceApplication), I specify which Excel Service application I am talking about in case there is more than one (in this case I want the one named MyExcelService), and then I instruct it to set the SessionsPerUserMax property to the value of 50.
Let’s look at a more advanced example.
A common request from the 2007 release was to look at all the trusted locations, and when some particular value was less than some amount, set that value to something new. (See the next section for an overview of trusted locations.) This wasn’t possible in 2007, but is pretty simple in 2010.
The following example will look at every trusted location for every Excel Service application running in the farm, and if external data refresh is allowed using embedded connections or connections from a data connection library (DCL), it makes it more secure via only allowing connections that are stored in a DCL.
Get-SPExcelServiceApplication | Get-SPExcelFileLocation | where { $_.ExternalDataAllowed –eq “DclAndEmbedded”} | Set-SPExcelFileLocation –ExternalDataAllowed Dcl
Notice the use of the “|” or the pipe operator. It just takes whatever the output from one cmdlet is, and sends it as the input to the next cmdlet. This is what makes it so easy to write a cmdlet that ‘loops’ over all the Excel Services in the farm,Office 2010 Serial, and for each one get all the trusted locations. The “where” clause just filters down the set of trusted locations to only the ones that match the logical criteria we are looking for, and then sends those results on to the next Set cmdlet to change the actual settings of that trusted location.
If you are interested in going a little deeper on PowerShell for SharePoint in general, check out the SharePoint team blog.
Default Settings for Trusted Locations
Excel Services has global settings, and also has trusted location level settings. Trusted locations are essentially just file paths (to a site, document library, portal, UNC share, etc) to places where the administrator has explicitly allowed workbook files to be loaded from. Excel Services will ONLY load workbooks that are stored in trusted locations. Excel Services can have many trusted locations, and each trusted location has a bunch of settings that dictate how the workbook can be used when it is loaded on the server. Simple example: by creating two trusted locations with different settings,microsoft Office 2010 Serial, you could specify that workbooks loaded from an externally facing site can only be up to 1MB in size, but workbooks from an internal site may be up to 15MB.
In Excel Services 2007 the administrator was forced to define at least one trusted location because by default in 2007, there were no trusted locations. And trusting something like,Microsoft Office Pro Plus, “all workbooks from anywhere in the SharePoint farm,” was non obvious.
By default in 2010 we load workbooks from anywhere in the farm, and we even allow data refresh (with warnings shown) by default. So no more trying to figure out what the settings should be, what level you should define them at, etc – it all works out of the box. Most administrators won’t need to make any changes here, but for those that do, they still have all the power and flexibility they had in 2007.
Tip – if you are trying to find an easy way to trust the entire farm in the 2007 release, you can add a trusted location as follows to achieve this:
Address: Make sure the trust children checkbox is selectedMake sure the type is SharePoint

External Data – Better Error Messaging
Anyone that configured external data connectivity in 2007 probably has memories of struggling to figure out where and why things weren’t working. A lot can go into getting a back end application server running Excel Services connected to a data source. For a better treatment of the subject in 2007, including background, security issues, and step by step instructions with screen shots,Office Home And Stude/nt 2010 Key, see this whitepaper I wrote.
The thing worth calling out in this blog article is that the error messages in this space have gotten much better. Excel Services now has error messaging with specific information about what/where the external data failure is being caused. In most of the common cases, these errors should help you quickly pinpoint what needs to be updated to get connectivity working.

For the security minded administrators, you can explicitly turn these more detailed error messages off and just show something generic instead. There is a setting to enable granular external data error messages on each trusted location.
We hope you find the changes we have made in the administration space useful for Excel Services. We think we have made some of the common administration tasks easier – initial settings config, scripting, and troubleshooting external data. As always, we would love to hear your feedback about how we can continue to improve. This post only scratched the surface and was by no means a comprehensive guide to all things administration in Excel Services or in SharePoint. To learn more about SharePoint in general, see the SharePoint team blog.
<div