As many know Business Intelligence is something that many Business users strive for. Now Microsoft has undergone many variations of their Business Intelligence offerings. There was the BI Centre in 2007, and as I’m sure many would agree, was not spectacular. Then can the buy-out of ProClarity and everyone thought this was start of something amazing for Microsoft, but sad to say it was not. From the ProClarity purchase came the first introduction of Performancepoint Monitoring, which was a good start to dashboarding, KPI representation and very basic BI. In 2010 the next release of PerformancePoint Monitoring was released and even though an improvement, it was still not “ProClarity”! So then the question is then what is Microsoft’s answer to Business Intelligence for the Client and the Web. Well Surprise, surprise…. It is Excel. To be completely honest, Microsoft has made great strides to the use of Excel as a BI tool in 2007 but in 2010 they have made huge leaps!

In this Post, I’m not going to discuss the in-depth uses of Excel as a BI Tool; I’ll leave that to my BI colleague Gavin. The focus of this and other Post will be how do Excel 2010 and SharePoint 2010 come together through the use of Excel Services? I’ll look at some of the key enhancements as well as looking at some technical setup changes that are needed to get Excel Services 2010 working in SharePoint 2010.

What’s New

Excel Services 2010 has made some major improvements. Here are some of the key ones (compliments of TechNet)

Several of these new features originate in SharePoint Server but directly affect all of the shared services. New shared features and the exclusive features that are added when you deploy Excel Services are discussed in this section.

• Unattended Service Account
  Excel Services provides a low privilege unattended service account for customers to use as a single “get data” type of account. Customers can use this as a privileged account in Microsoft Office 2010. Excel Services relies on the Secure Store Service to store the encrypted unattended account. The unattended account credentials are stored or cached as needed per session or connection so that when a workbook is loaded that contains a data connection for the unattended account this account is called from the Secure Store and used. The Secure Store stores the Excel Services secured data and is present on all SharePoint Server farms. The Secure Store functions regardless of how authentication is configured in a farm.
• Manage Service Applications
  The SharePoint Central Administration Web site contains a link to the Manage Service Applications page, which lists all of the services the user has rights to administer. Essentially, all available services for a particular user or role are collected on the Manage Service Applications page. Each service has its own administration page, for instance Manage Excel Services.
• Windows PowerShell
  Windows PowerShell is capable of a complete Excel Services deployment, as well as the unattended installation and deployment of SharePoint Server Technical Preview. Administrators who need to look up Trusted Locations and user-defined functions are now able to do this by using a single Windows PowerShell key. All Stsadm commands used against Excel Services–specific settings will fail; instead use the SPServiceApplication Windows PowerShell command.
• Trusted Locations
  Trusted locations are now provided by default; no administrator action typically is needed. However, if Universal Naming Convention (UNC) types of trusted folders or locations are used with Excel Services, the administrator must create new trusted locations for these.
• Multi-user Collaboration
  Multi-user collaborative environment provides multiple users with the ability to edit any workbook simultaneously. (When user is active, the polling rate is determined by an adaptive algorithm executed on the ECS. All edits are processed in the order in which they are received by the ECS so the last edit overwrites any previous edit to the same workbook cell.)
• Delegate Services Permissions
  SharePoint Server contains a new shared service infrastructure that allows the central administrator to delegate permissions to manage other services to users.
• Slicer feature
  The Slicer feature is a new type of data filter in Microsoft Excel 2010 that is interactive, flexible in design and layout, and always conveys the current filtering state. With these data filters more people benefit from the power of analyzing data using PivotTables and OLAP Functions. The Slicer feature gives Excel 2010 authors the ability to easily write OLAP data models and build rich, interactive reports around them. The reports can then be published to Excel Services and will display and interact just like they do in the Excel client. The Slicer feature also is parameterized by other Web Parts in BI dashboards.

  The Slicer feature does manual filtering only and does not provide advanced filtering such as label, date, value, and top-10 types of filtering. The Slicer feature can be connected to multiple PivotTables and act as a common, shared filter so selections made in this Slicer feature are automatically propagated to all PivotTables that are connected to it. Additionally, the Slicer feature can be formatted by applying styles.

How do we set it up? (see http://technet.microsoft.com/en-us/library/ee662528%28office.14%29.aspx For details)

So during this section, I’m not going over the “finer” how to install, but rather on how a SharePoint 2010 administrator can configure and setup items such as unattended user service.

All Management of Excel Services is done through the Microsoft SharePoint 2010 Central Administration and requires local administrative permissions. The system administrator can delegate Excel Services administrator permissions without providing permissions to manage other services or settings in Central Administration.

• Open the Excel Services Settings page.

  To open the Excel Services Settings page, do the following:
  

  • On the SharePoint 2010 Central Administration Home page, click Manage service applications.
    
    
  • On the Manage service applications page, click Excel Services.
    

    
    

  • On the Manage Excel Services page, click Global Settings.
    
    
• On the Excel Services Settings page, in the Security section, under File Access Method, select one of the following:
  
  
• In the Load Balancing section, under Load Balancing Scheme, select one of the following:
  

  
  

• In the Session Management section, in the Maximum Sessions Per User box, type the maximum number of sessions with Excel Services that an individual user is allowed per ECS application server (type -1 for no limit). The default is 25 sessions. If the maximum number of sessions is reached, then the oldest session is deleted whenever a new session is started.

  
  

• In the Memory Utilization section, in the Maximum Private Bytes box, type a value in megabytes (MB) for the maximum number of MB that an ECS process can use (type -1 for the value to equal 50% of physical memory on the computer hosting the ECS process).
  
  • In the Memory Cache Threshold box, type a value from 0 to 95 that represents the percentage of the Maximum Private Bytes that can be allocated to inactive objects. When the memory cache threshold is exceeded, any cached objects that are not currently in use are released.
    
  • In the Maximum Unused Object Age box, type the maximum time (in minutes) that inactive objects remain in the memory cache.
    

  In the Workbook Cache section, in the Workbook Cache Location box, type a path for the workbook file cache on the computer hosting the ECS server. If the box is left empty, a subdirectory in the system temporary directory will be used.
  

  • In the Maximum Size of Workbook Cache box, type a value in MB that can be allocated to workbooks that are being used by Excel Services. Recently used files that are not currently open count against this value.
    
  • To cache objects that are not being used in any sessions, select the Caching Enabled check box.
    

    
    

• In the External Data section, in the Connection Lifetime box, type a value in seconds for the maximum time for a connection to remain open. Connections that expire are reopened when the next query is received (type -1 to never close and reopen connections).
  
  You can reduce the risk of a denial-of-service attack by limiting the amount of time that sessions remain open.
  
• In the External Data section, under the Unattended Service Account section, provide the target application ID from Secure Store Service in the Application ID box.
  

  
  

  That covers the introduction to the key settings of Excel Services 2010 (compliments in-part to TechNet). What I’ll do in the next couple of editions is discuss these settings and how to get them working and for what reasons. Take for instance in Part 2 of this section we’ll cover on how to setup the Application ID that can be used with the “Unattended Service Account” option.