Excel Services architecture consists of a number of components that can be grouped as follows:

Of course, you can scale the infrastructure to meet your organization's needs. While the components are logically separated as shown in Figure 5-1, they can all be installed one server if you like.

Figure 5-1. Excel Services 2010 architecture

Before going any further, let's set up the basic infrastructure for using Excel Services.

Excel Services are installed by default with the SharePoint Server 2010 Enterprise version; you don't need to install anything else. But you do need to configure it. First, on your SharePoint site, ensure that the SharePoint Server Enterprise Site Collection Features feature is activated under the Site Collection Features.

Excel Services in MOSS 2007 are managed via the Shared Service Provider (SSP). In SharePoint 2010, however, the Shared Service Provider is no longer available; it has been replaced with Service Applications, which allow better management, control, and deployment. You can access them from Central Administration

To create a new Excel Services application, click the New button on the Service Applications menu and choose Excel Services Application (Figure 5-2). In the Create New Excel Services Application pop-up window, fill in the values for Name and Application Pool and choose whether the service application should be added to farm's default proxy list.

Figure 5-2. Creating a new Excel Services Application

To edit and configure an existing Excel Service application instance, click on the Excel Service Application link from the list of available Service Applications (see Figure 5-3). You can also select the row and click the Manage button.

Figure 5-3. Managing an existing Excel Service Application instance

The Manage Excel Services Application page (Figure 5-4) shows the various settings that can be configured.

Figure 5-4. The Excel Services Application configuration page

While most of the settings can be left to the default values, some need to be configured to address specific requirements.

Global Settings

Use global settings to configure the security model, session management, load balancing of ECS sessions, and memory utilization (Figure 5-5).

Security

File Access Method: Set the authentication method either to impersonate or to use the process account that ECS will use for all non-SharePoint trusted file locations. Use impersonation when you want ECS to authorize users when they try to access workbooks stored in UNC and HTTP locations. Impersonation also allows a thread to run in a different security context from the context in which the process that owns the thread runs. (Note that this setting doesn't have any impact on the workbooks that are stored in a SharePoint Server 2010 database.) Use a process account when users can't be impersonated and the ECS application server opens UNC shares or HTTP web sites. A process account is technically a local Windows account that has permissions on the shared folder where the workbook files are stored.

Note

The use of a process account in combination with anonymous access to SharePoint Foundation presents the risk that information may be disclosed. For more information on best practices, follow the recommendations at msdn.microsoft.com/en-us/library/ms545631.aspx.

Connection Encryption: Choose whether or not to encrypt the communication between the client and the front-end components of the Excel Services Application. The default setting is not required, so change the setting to Required to enable encryption. When you choose to encrypt communication, ECS transmits data over an SSL connection that you need to configure manually.

Allow Cross Domain Access: Select this option if a page in one HTTP domain needs to display files from a different HTTP domain.

Load Balancing

Load-Balancing Scheme: If your infrastructure has multiple application servers, Excel Services handles the load by distributing requests across ECS occurrences on the application servers in the farm. For the scheme, you can choose among Workbook URL, Round Robin with Health Check, and Local in order to load-balance sessions across ECS processes. With Workbook URL, a URL specified in the workbook is used to indicate which ECS process should run the workbook. This way, requests are always routed to the same ECS session. When Round Robin with Health Check is used, the ECS process is selected using the round-robin load-balancing mechanism to open a selected workbook. Finally, local should be used when the ECS process and workbook are on the local computer. If a local ECS process is not available, the round-robin scheme will be assigned to the ECS process.

Session Management

Maximum Sessions Per User: Set the maximum sessions allowed per user with the ECS. Any positive integer is a valid value. If the maximum number of sessions is reached, a new session will take the place of the oldest session. Set the value to −1 to enable unlimited sessions per user. Note that the higher the value, the greater the resource consumption for these concurrent ECS sessions. So, keep this value low for better performance.

Memory Utilization

• Maximum Private Bytes: Set the maximum number of private bytes (in MB) that ECS processes can use. Valid values are any positive integer or −1. If the value is −1, the limit is set to 50 percent of the physical memory of the machine hosting the ECS process.

• Memory Cache Threshold: Set the memory cache threshold (as a percent) for inactive objects. Valid values are 0 through 95. Caching of inactive objects is disabled if the value is set to zero. Unused cached objects are released when the maximum value is exceeded.

• Maximum Unused Object Age: Set the value (in minutes) that inactive objects can remain in the memory cache. Valid values are from 1 through 34560 (24 days maximum). Set the value to −1 for unlimited utilization of the memory.

Workbook Cache

• Workbook Cache Location: Set the location of the file system hosting the ECS server where workbook files are cached. If the location isn't set, the subdirectory under system temporary directory will be used.

• Maximum Size of Workbook Cache: Set the maximum workbook cache (in MB) that ECS can use on the disk. This value also counts for recently used files that are currently not opened. Valid values are any positive integers.

• Caching of Unused files: If you need to cache files that are unused in any session by ECS, check the Caching Enabled box.

External Data

Connection Lifetime: Set the external data connection lifetime. Valid values are −1 and 0 through 2073600 (24 days). In the event of new queries, older, expired connections are reopened. If no connections are to be closed or reopened, set the value to −1. It is highly recommended you limit this value to reduce the risk of a denial-of-service attack.

Unattended Service Account and Application ID: Set the Unattended Service Account ID for single sign-on using the Secure Store Service application. The Application ID must be preconfigured in the Secure Store Services application. You'll learn how to configure Secure Store Services shortly in the "Excel Services and SSAS" section.

Figure 5-5. Excel Services Global Settings

Trusted File Locations

Remember how you used SSP in MOSS 2007 to make an Excel file location trusted? In SharePoint 2010, by default all SharePoint (Microsoft SharePoint Foundation) locations are trusted. However, administrators can set rules to protect or unprotect specific file locations. Click on Add Trusted File Location (Figure 5-6) to add and configure trusted file locations.

Figure 5-6. Excel Services Trusted File Locations

Location

• Address: Set the trusted location from which workbooks should be loaded. These can be any Microsoft SharePoint Foundation or network file share or a web folder address.

• Location Type: Choose the storage location type based on the address you set. If the address is a SharePoint document library, select Microsoft SharePoint Foundation. If the address is a network share, choose UNC Path. And for a web folder address, select http web site.

• Trust Children: Choose whether or not directories or child libraries can be trusted.

Session Management

When a user requests a workbook from server, the server creates a new session and opens the latest version of the workbook. Note that there may be different users accessing the same workbook and hence you may have multiple sessions opened for the same workbook. It's important to control the resource consumption as well as duration of any concurrently open ECS sessions. With the help of the following settings, you can manage resource availability and sessions, which can improve the performance of ECS.

• Session Timeout: Set the maximum timeout value (in seconds) for an ECS session to open and remain inactive before shut down. This value is measured from the end of each open request. Valid values are −1 through 2073600 (24days). If the value is set to zero, the session expires by end of each single request. Set the value to −1 for no time out.

• Short Session Timeout: Set the maximum timeout value (in seconds) for an Excel Web Access session to open and remain inactive without any user interaction before shutting down. This value is measured from the end of original open request. Valid values are −1 through 2073600 (24 days). If the value is set to zero, the session expires by the end of each single request. Set value to −1 in order to disable short session timeout.

• New Workbook Session Timeout: Set the maximum timeout value (in seconds) for an ECS session to open a new workbook and remain inactive before shutting down. This value is measured from the end of each request. Valid values are −1 through 2073600 (24days). If the value is set to zero, the session expires by the end of each single request. Set the value to −1 for no time out.

• Maximum Request Duration: Set the maximum duration (in seconds) of a single request in a session. Valid values are −1 through 2073600 (24 days). For no limit, set the value to −1.

• Maximum Chart Render Duration: - Set the maximum duration (in seconds) to render a single chart. Valid values are −1 through 2073600 (24 days). For no limit, set the value to −1.

Workbook Properties

Maximum Workbook Size: Set the maximum size (in MB) for a workbook that can be opened by ECS. Valid values range from 1 to 2000 MB.

Maximum Chart or Image Size: Set the maximum size (in MB) of chart or image that ECS can open. Valid values are any positive integers.

These values impact the performance and resources of the server. The bigger the workbook size or the chart or image size, the greater the resource consumption and, thus, the worse performance.

Calculation Behavior

Volatile Function Cache Lifetime: The maximum time a volatile function is cached in order to compute values for automatic recalculations of sheets containing volatile information. Volatile functions, such as RAND(), TODAY(), and NOW(), are those that are always calculated. Valid values range −1 to 2073600 (24 days). If set to −1, calculations are loaded once while opening. Set to zero in order to always calculate.

Workbook Calculation Mode: Set the value to

• File to load calculations specified in a file.

• Manual to recalculate only when a request is received.

• Automatic to recalculate on any change to a value that all other values are dependent on,

• Automatic except data tables to recalculate on any change to a value that all other values are dependent on as long as the values are not in a data table.

External Data

Allow External Data: To allow ECS to process external data connections, set to

• None to disable all external data connections.

• Trusted data connection libraries only to allow data connections that are saved to a trusted data connection library and ignore those embedded in the worksheet.

• Trusted data connection libraries and embedded to allow data connections to data sources that are saved to a trusted data connection library. If the connection fails, the server will enable connections embedded in the worksheet.

• Warn on Refresh: Choose this option to display a warning message before refreshing the external data for files in this location.

• Display Granular External Data Errors: Choose to display error messages when files in the location have data failures.

• Stop when Refresh on Open Fails: Choose to stop opening a file when the user does not have permissions to open the workbook.

• External Data Cache Lifetime: Set the maximum time that external data query results can be used by the system. Set the maximum refresh time value in seconds for either automatically or manually refreshing external query results the system can use. Valid values range from 0 to 2073600 (24 days). Set to −1 to prevent data refresh after initial query.

• Maximum Concurrent Queries Per Session: Set the maximum number of data queries that can run simultaneously in a single session. Valid values are positive integers.

• Allow External Data Using REST: Choose to enable the data refresh from REST APIs. However, if the Allow External Data is set to none, this wouldn't be in effect.

User-Defined Functions

User-defined functions allowed': Set this if user-defined functions in ECS for workbooks under the trusted file locations are allowed.

Trusted Data Providers

ECS uses data providers that are trusted to access data from external data sources for use in Excel workbooks. There are many data providers that are trusted natively. However, when you have to retrieve external data using custom data provider information in the connection, you need to manually define the provider as trusted. Click on Add Trusted Data Provider and furnish Provider ID, Type (OLE DB, ODBC, or ODBC DSN), and Description values (Figure 5-7). After adding the trusted data provider, use the Provider ID in the connection string.

Figure 5-7. Trusted Data Provider settings

Trusted Data Connection Libraries

You can set a SharePoint document library as trusted. Data connection files (*.odc) hosted in this document library will be loaded by ECS for connecting to databases. To create a Trusted Data Connection Library click on the Add Trusted Data Connection Library link. Type the document library Address where files will be saved and fill in the Description as shown in Figure 5-8.

Figure 5-8. Trusted Data Connection Library settings

User-Defined Function Assemblies

Click on Add User-Defined Function Assembly to set and enable .NET assemblies containing user-defined functions that extend ECS capabilities. The assembly details are shown in Figure 5-9.

• Assembly: Type the assembly path, either the full path or the strong name of the assembly.

• Assembly Location: Choose between GAC or the file path for the assembly location.

• Enable Assembly: If checked, ECS will load and use the assembly. Unchecking this option disables the assembly without deleting the assembly from the entry list.

• Description: Optional text describing the user-defined function assembly.

Figure 5-9. Setting up UDFs for Excel Services

Note

For more information on what UDFs are and how to create your own, visit blog.srinisistla.com/Lists/Posts/Post.aspx?ID=207.

Now that we've have set up Excel Services, let's author a simple Excel file and publish it to Excel Services.

One fine morning my program manager came to me and said, "Hey, why not take the tasks from Team Foundation Server (TFS), export them to Excel, and display status charts based on those tasks on our intranet web site?" Well, that's a great idea—and it can serve as a dashboard for our project status. So we exported tasks for all work items from TFS and saved the file to one of our local drives as TeamTasks.xlsx, as shown in Figure 5-10.

Figure 5-10. Sample data exported from TFS into Excel

Since the idea is to have this information on our intranet SharePoint site, we created a document library named Excel Library to act as a container for the TeamTasks.xlsx file. We then followed these steps to publish TeamTasks.xlsx to Excel Library.

Figure 5-11. Published Excel Workbook in SharePoint document library

Figure 5-12. SharePoint 2010 Excel Viewer rendering the Excel file in a browser

That was pretty simple. Well, you can add a lot of other functionality to the file. But first, let's discuss some of the highlights of Excel Services, which

Using Excel as the client and SharePoint 2010 with Excel Services as the platform is the perfect blend for Business Intelligence. Excel Services 2010 includes a number of new enhancements, as we'll see.

So far we've experienced Excel Services from the UI. Now we'll take a look at some alternative mechanisms for configuring Excel Services settings.

The functionalities we'll discuss now are the same ones we looked at earlier in the "Setting up Excel Services" section, but now we'll use PowerShell commands.

These commands can be classified into four basic groups: Create (new) operations, Set operations, Get operations, and Delete (remove) operations, as described in Tables 5-1 through 5-4. You will notice that these commands use the Excel Service Application name to get the identity. Make sure you are using the correct name. Again, these commands use other commands to get references, so we recommend you go through all the commands first and then use them according to your needs.

We will use some of the new features to add more functionality to the TeamTask.xlsx file we created earlier.

PROBLEM CASE

Author and publish the TeamTasks.xlsx file to SharePoint Excel Services, and use an Excel Web Access web part to view the workbook.

Solution:

1. Open the TeamTask.xlsx file you created earlier. Create a new calculated column to compute the difference between Estimated Hours and Completed Hours. Name this column heading Remaining Work (hours).

2. Select all the other rows in the Remaining Work column and click on the Conditional Formatting button on the Home tab of the ribbon, then select Data Bars

   
   

   Figure 5-13. Adding conditional formatting to the row data

3. Select all rows under the Title and the Remaining Work columns. From the Insert tab, click on Pie and select 3-D Pie, as shown in Figure 5-14. Choose one of the Chart Styles available from Chart Tools

   
   

   Figure 5-14. Adding a chart to workbook sheet

4. Once the Chart is rendered, set Chart Layouts to display the legend and slice details (Layout 1 in this example). From Chart Tools

   
   

   Figure 5-15. Setting the Chart Name

5. Create another calculated column of data for computing Task Allocation estimated hours per assignee. To compute the sum of task allocation estimated hours per assignee, you can create a pivot table from the Excel data. From the Insert tab, select Pivot Table. In the Create PivotTable window, choose the range to extend from the Assigned To column to the Estimated Work (hours) column, then choose to place the PivotTable in a New Worksheet and click OK. A new sheet will be created and you'll see a PivotTable added to the sheet. From the PivotTable Field List, select the Assigned To and Estimated work (hours) items and see how that data is added to the PivotTable as shown in Figure 5-16.

   

   Figure 5-16. Using a pivot table to sum data in an Excel workbook

6. Return to Sheet1 and insert another 3-D Pie chart, then select the previously created pivot table as the data range for this chart. Set the chart layout (Layout 1) to display percentage of task allocation.

7. Your Excel worksheet should now look similar to the one in Figure 5-17.

   

   Figure 5-17. Excel workbook loaded with data and graphs, and with conditional formatting

8. From the Backstage View, click on Save & Send. Choose Save to SharePoint and save the worksheet to the SharePoint document library you created. Overwrite or provide a new file name if a file already exist with the same name.

9. Open the SharePoint site, and go to the document library where you saved the Excel file.

10. Click the Excel file. It should render in the browser as shown in Figure 5-18.

    

    Figure 5-18. Excel workbook published to SharePoint 2010

Amazing, isn't it? All the calculation, conditional formatting, and charts are rendered very much as in the Excel client.

Note

The Excel viewer supports 3-D charts by converting them to 2-D charts. Graphic effects such as Shadow, Glow, Soft Edges, Bevel, 3-D Rotation will be converted to their respective 2-D counterparts.

Now we've seen how a published Excel file renders in a browser. It can also be loaded using an Excel Web Access (EWA) web part, which has the same rendering behavior—but EWA comes with more functionality that we will discuss now.

Using an Excel Web Access Web Part (EWA)

1. Using the Copy Shortcut link from the context menu, retrieve the URL of TeamTasks.xlsx.

2. Create a page under Site Pages library called Excel Demo.

3. Open and edit the page. Choose Web Part item from the Editing Tools

   

4. Edit the EWA web part or choose to open the tool pane. From the configuration window, enter the TeamTasks.xlsx URL path under the workbook input box. You can also use the text box's pick URL icon.

5. Leave all other entries with the default settings and click OK.

6. Team Tasks.xlsx will render in the EWA web part, as shown in Figure 5-19.

Figure 5-19. The Excel workbook loaded using an EWA web part

Now let's see how easy it is to extract content from this Excel sheet—including the charts—and use it in a Word application. To do this, we'll use the REST-based API.

As mentioned earlier, the REST API was introduced in Excel Services in SharePoint 2010. It enables simple yet flexible and secure access to Excel workbook content via a URL. You can either manually or programmatically access resources such as ranges, charts, tables, PivotTables, and Atom feeds over HTTP.

A URL through which you can access workbook content consists of three parts:

Here is a sample URL that contains all three elements.

http://<site>/_vti_bin/ExcelRest.aspx/<exceldocumentlibraryname>/<excelfilename>/<resoucelocat
ion>

In the this example,

http://<site>/_vti_bin/ExcelRest.aspx is the URI;

/<exceldocumentlibraryname>/<excelfilename> is the location of the workbook; and

<resourcelocation> is the location of the resource.

Let's use the TeamTasks.xlsx file and retrieve the embedded chart using the REST API. But first let's generate an Atom feed from the Excel workbook using the discovery user interface in the Excel Services REST API. This mechanism enables us to explore the elements that exist in the workbook. Copy the shortcut to the Excel file from the document library. Construct a URL like the following that generates output as shown in Figure 5-20.

http://<yourServerName>/_vti_bin/excelrest.aspx/<Excel lib>/<Excel file>.xlsx/model

If www.sharepointsite.com is your SharePoint site, Excel Library is the document library where your Excel file is published, and TeamTasks_data.xlsx is your Excel file, the URL to obtain the feed would look like this:

http://www.sharepointsite.com/_vti_bin/excelrest.aspx/Excel%20Library/TeamTasks_data.xlsx/model

Figure 5-20. Excel Services REST API delivering an Atom feed

In order to access and retrieve the chart image from your Excel workbook using REST, you need to build the URL with a different resource location. Remember the Chart Name you provided in the previous exercise (Figure 5-15)? You'll have to use that chart name to retrieve the chart from your Excel workbook. Construct the URL as shown here and enter it in the address location of the browser to get the output.

http://<yourServerName>/_vti_bin/excelrest.aspx/<Excel lib>/<Excel
file>.xlsx/model/charts('<chart name>')?$format=image

Let's use the previously constructed URL to generate the chart image and embed it into a Word document. Open the Word 2010 client. From the Insert tab, choose Quick Parts, then Field. In the Field window, choose IncludePicture from Field names section, then paste the URL in the Filename or URL text box as shown in Figure 5-21.

Figure 5-21. Configuring a Word document with the Excel Services REST API URL

In this example, if www.sharepointsite.com is your SharePoint site, Excel Library is the document library where your Excel file is published, TeamTasks_data.xlsx is your Excel file, and Task Status is the chart name, then the URL to obtain the chart as an image should look like this:

http://www.sharepointsite.com/_vti_bin/excelrest.aspx/Excel%20Library/TeamTasks_data.xlsx/mode
l/charts('Task%20Status')?$format=image

Leave other settings to the defaults and click the OK button. The chart embedded in the Excel workbook that is chosen using the <chart name> parameter in the URL will be rendered as an image in the document as shown in Figure 5-22. You can add a title to the chart and any additional information related to the chart as needed.

Figure 5-22. A Word document displaying a chart from Excel Services using REST API

What's so interesting about the chart image in Figure 5-22 is that it is real-time, live, and up-to-date. This embedded chart image will reflect changes immediately, as soon as data changes in the workbook and the field is updated in the Word document.

Note

To retrieve external data using REST, ensure that the "Allow external data using REST" box is checked under Central Administration

That was easy, effective, and very useful. However, there are few features that are not supported in Excel Services REST API.

Excel Services 2010 can now also use the Excel Services SOAP API. By using the SOAP API, you can not only read the workbook content but also edit and save the workbook programmatically. In the next section, we will demonstrate on how to use the Excel Services SOAP API with a simple example.

Excel Web Services can now expose an Excel workbook and its sheets using the SOAP API. You can both read and write programmatically to the Excel worksheet while other users are editing the workbook, which allows multiuser collaboration. All you need is a simple reference to the SOAP API.

You can communicate with the Excel file securely either by impersonation or by using credentials while connecting to data. Let's see how to connect with TeamTasks.xlsx file using the SOAP API.

SQL Server Analysis Services (SSAS) and Excel together—now that's a great combination for those who would like to analyze and slice and dice cube data. To do this, we will use the AdventureWorks DW database and demonstrate how to configure and connect to SSAS.

As a prerequisite, Secure Store Services need to be configured to authenticate with SSAS. This is similar to what you've done in previous chapters, but we will run through the exercise once again.

Configuring Secure Store Services

From Central Administration

Figure 5-26. Setting up a new SSS Application ID for Excel Services

In the credentials fields, choose Windows User Name and Windows Password as shown Figure 5-27 and click Next.

Figure 5-27. Setting up credentials for the SSS Application ID

Enter user(s) who can manage the application as Target Application Administrators, and users who are Members who are allowed to use the application (Figure 5-28) and click OK.

Figure 5-28. Setting up Target Application Administrators and Members for SSS

After returning to the SSS home screen, choose the Application from the store you just created and set the credentials by clicking on the Set button as shown in Figure 5-29.

Figure 5-29. Set credentials for Target Application ID in SSS

Enter values in the credential fields for the Target Application that has access to the SSAS database as shown in Figure 5-30.

Figure 5-30. Setting credentials for the SSS Target Application group

Now that your credential cache is ready, let's see how to connect Excel with SSAS using the Secure Store Service Application ID.

PROBLEM CASE

Connect Excel with the multidimensional cube and analyze the Internet Sales information from Adventure Works DW database

Solution:

1. Open the Excel 2010 client. On the Data tab, choose From Analysis Services, as shown in Figure 5-31.

   

   Figure 5-31. Choose the SSAS data source for the Excel workbook

2. In the Data Connection Wizard, enter a Server name and choose the Log-on credentials. Use Windows Authentication for impersonation or enter a User Name and Password to use specific credentials. Click the Next button.

3. On the Select Database and Table screen, choose the Adventure Works DW 2008 database as shown in Figure 5-32.

4. Under Connect to a specific cube or table, select the Adventure Works cube as shown in Figure 5-32. Click Next to continue.

   

   Figure 5-32. Choose the Adventure Works database and its cube

5. In the Save Data Connection File and Finish window (Figure 5-33), enter a valid file name and choose a location to save the data connection file (*.odc).

6. Enter a logical description and a friendly name.

7. Make sure to check the Always attempt to use this file to refresh data box.

8. Click the Authentication Settings button to choose the type of authentication that will be used for the data.

   

   Figure 5-33. Saving the Office Data Connection file to disk

9. In the Excel Services Authentication Settings window (Figure 5-34), choose SSS - SSS ID and provide the Application ID you created in earlier section (ExcelServices_SSS), then click OK. After returning to the Save Data Connection File and Finish window, click Finish to complete the Data Connection Wizard.

   

   Figure 5-34. Setting up the SSS Application ID for ODC

10. In the Import Data window, choose PivotChart and PivotTable Report and for the existing worksheet leave the default selection (=Sheet1!$A$1) as shown in Figure 5-35. Click on the Properties button to view and alter connection settings, then click OK to close the window.

    

    Figure 5-35. Import Data wizard window

    Note

    If you choose only PivotTable Report, only the Pivot Table wizard is displayed. If you need both PivotTable and PivotChart, choose the PivotChart and PivotTable Report.

    The Excel sheet will prepopulate the PivotTable and Pivot Chart, as shown in Figure 5-36. The worksheet includes the PivotTable, PivotChart, and PivotTable Field List. You use the PivotTable field list to select the fields for the PivotTable and the PivotChart.

    

    Figure 5-36. Excel workbook with pivot table, chart, and fields

11. From the PivotTable Field List, choose Sales Amount from the Sales Summary.

12. From Date

    

13. From Product, choose Product Categories.

    Choosing these three fields adds a pivot table with data and the chart to the worksheet. Now let's now add a little more functionality to the sheet. We will create some trends based on the data using sparklines.

14. Select cells ranging from B3 through F7 and from the Insert tab choose Sparklines, then choose Line.

15. In the Create Sparklines window with the data range selected, enter the location range where you want to display the Sparklines (in this case, use G3:G7) and click OK. Sparklines will be created in the selected range as shown in Figure 5-37.

16. Choose a specific design format for the sparklines from Sparkline Tools

    
    

    Figure 5-37. Excel sparklines and conditional formatting

    Let's add a trend to this sheet, a rather simple one. Let's get trends on the difference in Sales Amount from FY 2004 and FY 2002. Note that if you're using the Adventure Works DW 2008R2 database, you'll have data for FY2006 through FY2009.

17. Place the difference formula between D3 and B3 and choose column H to display the results. Repeat the step for the range D4:D7 and B4:B7.

18. Select all the calculated columns (H3:H7). From the Styles section on the Home tab, click on Conditional Formatting, then on Data Bars. Select the green data bar and then Gradient Fill.

    Note

    There are many other conditional formats, designs, and layouts you can use. We recommend you try other options for styling the sparklines and charts.

    After you add sparklines and conditional formatting to the sheet, it should look similar to what's shown in Figure 5-38.

    

    Figure 5-38. Sparklines, conditional formatting and graphs in Excel

    What would be more interesting is if you could add an intuitive mechanism to the sheet for analyzing data. And you can—the Slicer is another utility available in Excel that you can use to filter data, and slice and dice it.

19. From the Filter section on the Insert tab, click on Slicer. If you have more than one connection file related to this Excel workbook, you'll be prompted to choose the Existing Data Connections. Select the previously created data source connection and click the Open button. Otherwise, skip this step.

20. From the Insert Slicers window, choose Fiscal Year from the Date section and Product from the Product Categories section and click on OK. This adds two slicers, Fiscal Year and Product, as shown in Figure 5-39.

21. Place the slicers below the graph and from Slicer Tools Options buttons, change the value in the Columns input. Increase the value to fit the columns in single row.

    The final sheet should look like the one in Figure 5-39.

    

    Figure 5-39. Slicers in Excel

22. Click on any of the Slicer filters and see how the data gets filtered on the PivotTable and Chart.

    You saw earlier how to publish an Excel workbook to a SharePoint library. Figure 5-40 shows the final Excel workbook sheet after publishing to SharePoint. On the Product slicer, click on Hitch Rack-4-Bike and view the output as shown Figure 5-40.

    

    Figure 5-40. All together—slicers, sparklines, and conditional formatting

Important: Let's understand an important step in the previous exercise: precisely, step #5. You've saved the data connection file (.odc) to the local file system. As you are aware, this will definitely not be the scenario in a production environment. You will have to save the file to a location that is trusted. To accomplish this, you'll have to upload the .odc file to the Data Connection Library (DCL) on your SharePoint site.

To create a DCL, go to your SharePoint site, then to Site Actions

Figure 5-41. Adding an .odc file to a Data Connection Library

After approving the file, click on it. This opens a new Excel workbook using the Excel client on your local machine, which will be readily connected to the data source embedded the .odc file.

The Excel workbook will be prepopulated with the PivotTable. However, to add a PivotChart, select a chart from the Charts section of the Insert tab. Following steps 8 through 16 in the previous exercise would produce the results shown in the Figure 5-40.

Important: Since SQL Server Analysis Server uses an Active Directory account, be sure to do the following:

• Monitor errors in the %program files%/common files/Microsoft shared/web server extensions/14/logs folder.

• Ensure that the logged-in user or the unattended account under Secure Store Services has the required level of access permissions for SQL Server Analysis Services.

• Make sure that Windows Firewall between the SharePoint server and the SSAS server allows SSAS ports for operations (the defaults are 2382 and 2383).

• Ensure that the firewall is not blocking SQL Server Analysis Services or Excel Services.

Be aware that if SSS is not configured properly, you might end up with the error shown in Figure 5-42 when refreshing or filtering the data in the Excel workbook. In such situations, verify that the unattended service account credentials are accurate and have sufficient privileges. Also ensure that in the Trusted File Locations (Excel Service Application on Central Administration)

Figure 5-42. Data connection authentication error

So far we've seen how to consume data from data sources small and large in Excel and how to publish Excel workbooks to SharePoint. However, an even bigger question is whether Excel is capable of doing complex calculations and processing lots of information in a short time. Let's see how some of these challenges can be addressed in the next section.

As it becomes increasingly important to support large amounts of data, the need for additional infrastructure that can support Excel also becomes significant. The PowerPivot add-in helps support such requirements. Here's a summary of PowerPivot features and capabilities.

The major components involved are the Excel 2010 client, the PowerPivot Excel add-in, and PowerPivot for SharePoint. Excel workbooks with PowerPivot data can be published to SharePoint Server 2010 with Excel Services. You can open workbooks with PowerPivot data using Excel 2007 in read-only and non-interactive mode. However, to use all the features of PowerPivot, you need Excel 2010.

Download and install PowerPivot for Excel from www.powerpivot.com/download.aspx. For instructions related to PowerPivot for SharePoint 2010, visit msdn.microsoft.com/en-us/library/ee210654(SQL.105).aspx. And for hardware and software requirements, go to msdn.microsoft.com/en-us/library/ee210640.aspx.

After installing PowerPivot on compatible hardware (x86 or x64), the Excel client will include a new PowerPivot menu, as shown in Figure 5-43. It is highly recommended to use the 64-bit edition of Excel 2010 as well as a 64-bit operating system.

Figure 5-43. The PowerPivot tab in Excel

Launching PowerPivot opens a new design window. With PowerPivot, you can simply copy and paste a large chunk of data into the Excel sheet and perform analysis on the data. You can also connect to other data sources and perform the same operations as you can perform with Excel.

Figure 5-44 shows various data sources that can be connected to PowerPivot. You can access the list of data sources from Get External Data

Figure 5-44. Data sources available with PowerPivot

Setting Up PowerPivot

Before you begin to use PowerPivot, you do need to perform a few manual steps. After installing PowerPivot for SharePoint Server 2010, two solutions are deployed at the farm level, namely powerpivotfarm.wsp and powerpivotwebapp.wsp. You can view them under Central Administration

Figure 5-45. PowerPivot solution files under Central Administration

After the initial setup, powerpivotwebapp.wsp will only be deployed to a Central Administration web application. Click on the .wsp link to open the properties window (Figure 5-46).

Figure 5-46. PowerPivot solution deployment status

Click Deploy Solution to deploy the solution to the required web application as shown in Figure 5-47.

Figure 5-47. PowerPivot solution deployment to web application

After successful deployment, make sure that PowerPivot Feature Integration for Site Collection is activated in the Site Collection Features of your SharePoint site (Figure 5-48).

Figure 5-48. Activating PowerPivot Feature Integration for Site Collections

You are now ready and can fully utilize the capabilities of PowerPivot in your SharePoint site. Let's do a simple exercise and see what PowerPivot brings to the table.

PROBLEM CASE

Connect the Sales data cube and use PowerPivot to retrieve data and populate charts.

Solution:

PowerPivot for SharePoint Server 2010 brings with it a special document library called the PowerPivot Gallery. To create a PowerPivot Gallery, go to your SharePoint site and from Site Actions

Figure 5-49. Creating PowerPivot Gallery

Once the PowerPivot Gallery library is created, go to Library Tools

Open the PowerPivot Library. From Library Tools

1. In the PowerPivot window, in Get External Data

   
   

   Figure 5-50. Choosing SSAS or PowerPivot as database

2. In the Table Import Wizard, provide a friendly connection name, a server to connect, and the log-on credentials.

3. For the database, choose either Adventure Works DW or Adventure Works DW 2008R2, whichever you selected earlier. Click on Test Connection to ensure the success of the connection.

4. Click on the Advanced button to view the connection properties. After successful verification, you can click either OK or Test Connection.

5. Click Next to configure the MDX query. Provide a valid Friendly Query Name and click on Design to open the Table Import Wizard.

6. The Table Import Wizard lets you select dimensions and measures from the available cube in the Adventure Works database.

7. In the Measure Group pane, expand the Measures. Select and expand Internet Sales and choose Internet Sales Amount. Drag and drop to the pane on the right.

8. From the Dimensions, choose Date

   

9. Drag and drop the attribute Date Fiscal to the section on the right.

10. Repeat step 9 for the Dimension - Product. Choose Category and drag and drop Category on to section on the right.

11. Add Subcategory in the case of Adventure Works DW (or Sub-Categories in case of Adventure Works DW 2008 R2) to the section on the right.

    Your final window, before you click OK, should look like the one in Figure 5-51. Once you verify, click OK.

    

    Figure 5-51. Importing Measure and Dimension data

12. The window returns to the Specify a MDX Query screen and the MDX Statement is now populated with the MDX query as shown in Figure 5-52. Click the Validate button to ensure the generated query is correct. Once it's been validated, click the Finish button.

    

    Figure 5-52. Generate and Validate MDX query

    Note

    If you already know how to build a MDX query, you can create and paste it in the MDX Statement window and avoid the design step.

    When you click the Finish button, the wizard imports the data and displays a success or failure notification. You can close the window on successful confirmation. PowerPivot will display the data from the MDX query. Change the column header names (titles) to make them more logical; for instance, change MeasureInternet Sales Amount to Internet Sales Amount.

13. Select the column Internet Sales Amount and from the Formatting section, select the Data Type and choose Currency.

14. After formatting the data and changing the column names, the final PowerPivot data should look like what's shown in Figure 5-53.

    

    Figure 5-53. Data retrieved from the MDX query displayed in the PowerPivot window

15. From the Reports section, choose PivotTable (or Chart and Table) from the PivotTable drop-down menu. In the Create PivotChart and PivotTable window, select New Worksheet and click OK.

16. From the PowerPivot Field List, drag and drop Internet Sales Amount to the Values section of the configuration window

17. Drag and drop Fiscal Year to the Row Labels section of the configuration window

18. Drag and drop Product and Subcategory to the Slicers Vertical section of the configuration window

19. Click on Chart and configure it in the same way as the previous steps. Drag and drop Internet Sales Amount to Values section of the configuration window.

20. Drag and drop Fiscal Year to the Axis Fields section of the configuration window.

21. Drag and drop Category to the Axis Fields section of the configuration window. Rename the sheet to an appropriate name and save the file.

22. You can save the file by using the Save & Send option from the Backstage and saving it directly to the SharePoint library (PowerPivot Library), or you can first save the file manually to the file system and later upload it to SharePoint PowerPivot library.

    Your final Excel workbook with the PowerPivot data will look like the one in Figure 5-54.

    

    Figure 5-54. PowerPivot window after setting up the data source

23. In the Connections section of the Data tab, click on Properties. In the Connection Properties window, select the Definition tab and click on the Authentication Settings button. Select SSS - SSS ID and provide the SSS Application ID you set earlier to connect to the SSAS database. Click OK as needed to close all the open windows. Finally, save the Excel sheet to your local disk.

24. Open the SharePoint site and go to the PowerPivot Library. From the ribbon, choose Upload Document to upload your saved Excel file. Enter the Name and Title and save the file. Your PowerPivot library will now display the document as shown in Figure 5-55, using a Silverlight control.

    

    Figure 5-55. PowerPivot library on the SharePoint site

    Tip

    Save the data connection file (.odc) to the data connection library as we discussed in the earlier examples and then perform the steps in this section.

25. Click on the Internet Sales sheet to load the Excel worksheet in the browser as shown in Figure 5-56.

    

    Figure 5-56. Excel Viewer displaying Excel with PowerPivot and data on SharePoint

26. You can now click on the slicer (filter) to analyze the filtered data.

Tip

There is much more to this Excel data. You can use the path of the published Excel file as a data source and connect using SQL Server Analysis Services. You can also load the data while using SQL Server Reporting Services.

Silverlight PivotViewer is a powerful web control that can be used for Business Intelligence. You can download Silverlight PivotViewer and get more information at www.silverlight.net/learn/pivotviewer/.

You've already seen how to use Excel Services with the REST-based API. Another API introduced in SharePoint 2010 is the JavaScript Object Model (JSOM) for Excel Services. With JSOM you can handle events on user actions and connect with one or more EWA web parts on the page.

You can use JSOM to retrieve data from sheets, tables, PivotTables, chart, and cells or a range of cells. You can subscribe to many events in order to handle various user actions, such as active selection changed or cell value editing.

JSOM is very simple to use. All you need is to write some JavaScript and add it to the page either directly or by using Content Editor web part. The JSOM classes and members are available in the EwaMoss.js file located in the %ProgramFiles%Common FilesMicrosoft SharedWeb server extensions14 emplatelayouts directory.

Let's now see how to use JSOM to retrieve data from an Excel workbook that is published to SharePoint 2010.

Figure 5-57. Using JSOM with Excel Services

Now that you have seen some working examples, you need to be aware of best practices, as well as some of the known issues and tips for Excel Services 2010.

It's not just enough to use what is available. You need to plan very well before implementing them. You'll find guidelines from Microsoft for Mitigating Threats, Setting up of UDFs, and other general topics at msdn.microsoft.com/en-us/library/ms545631.aspx.

You can read about some of the known issues and tips for Excel Services at msdn.microsoft.com/en-us/library/ms501525.aspx.

And, finally, here are the Error Codes generated by Excel Services during various errors msdn.microsoft.com/en-us/library/ms575980.aspx.

As shown in Figure 5-58, in this chapter we have looked at the following:

Figure 5-58. Excel and Excel Services

In the next chapter, you will learn about PerformancePoint Services and its BI capabilities. This is the platform where everything comes together. We will walk you through installation and setting up of PerformancePoint Services, as well as administration and migration. We will also demonstrate authoring KPIs and scorecards, and integrating dashboards with various other services.