Business Intelligence with SharePoint and Excel

[LEFT]Microsoft Office
Business Intelligence with SharePoint and Excel
Ben Curry and Bill English with the Microsoft SharePoint teams

At a Glance:

[LIST][*]The Microsoft BI architecture[*]Using Excel for analyzing data[*]Publishing and updating SharePoint lists[*]Working with Excel Services[/LIST]

[IMG]http://i.msdn.microsoft.com/Global/Images/clear.gif[/IMG] Contents
[B]The Microsoft BI Big Picture
BI Integration with SharePoint Server 2007
Using Excel for Analyzing Data
Excel Add-Ins
Office BI Integration with SharePoint Lists
Excel Services
Authentication between Client and Server
Publishing Excel Workbooks
Configuring Excel Web Access Web Parts
Unsupported Features in Excel Services
[/B] [B]Options and Costs

[/B]



Today's business decision-makers have a distinct advantage over their predecessors when it comes to business intelligence (BI) tools that they can use for analysis, trending, and presentation of data. A good BI strategy, supported by a solid base of BI technologies, can streamline business processes while also boosting productivity and profit.
The overall BI strategy of Microsoft comprises a suite of server and client-side data integration tools. Powerful analytical and reporting tools in SQL Server 2005 provide the backbone data management infrastructure, while Microsoft Office applications, specifically Microsoft Office Excel, provide the flexibility for information workers to remotely interact with centralized and secure data sources.
This article, which is adapted from the Microsoft Press book [I]Microsoft Office SharePoint Server 2007 Best Practices[/I], focuses on how you can use Excel and Excel Services to analyze business intelligence data within SharePoint sites. Note that some features discussed here, such as data connection libraries, are offered only with SharePoint Server 2007 Enterprise licensing.

The Microsoft BI Big Picture
There are many different ways to analyze external data and publish it to SharePoint sites, including using Excel Services. Depending on your particular business requirements, you may choose a data integration method that will allow an administrator to more selectively create and deploy data reports and minimize end user control, or you may choose to empower end users by enabling them to access data sources and build custom reports directly inside SharePoint. The Microsoft BI suite includes the flexibility to securely and selectively deploy data based on user credentials.
The Microsoft BI strategy includes three main components:

[LIST][*]The BI platform[*]End-user tools and performance management applications[*]The delivery mechanisms[/LIST]

The BI platform contains the various database and data warehousing components for data storage and schema. SQL Server 2005 Analysis Services (SSAS) provides a semantic model, referred to as a unified dimensional model (UDM). The UDM defines business entities, business logic, calculations, and metrics and provides a bridge between end users and data sources. End users can run queries directly against the UDM using BI tools, such as Excel and Office PerformancePoint Server 2007. [B]Figure 1[/B] illustrates how a user can query SQL Server data sources via the UDM.
[IMG]http://i.technet.microsoft.com/dd392007.fig01a%28en-us%29.gif[/IMG] Figure 1 [B]Interaction between end user tools and the BI platform [/B]

[B]Figure 2[/B] illustrates the overall integrated Microsoft BI solution. SharePoint Server 2007 is at the top of the stack and acts as the hub through which information workers can collaboratively access and analyze data.
[IMG]http://i.technet.microsoft.com/dd392007.fig02a%28en-us%29.gif[/IMG] Figure 2 [B]Architecture of the integrated MicrosoftBI solution[/B]


BI Integration with SharePoint Server 2007
Excel, Excel Services, SharePoint Report Center, and the Business Data Catalog (BDC) are the default, or core, BI features within the Office suite of products. SQL Server 2005 Reporting Services, Analysis Services, and PerformancePoint Server 2007 extend the core BI functionality by offering enhanced reporting capabilities, data warehousing, in-depth data analysis, and real-time monitoring. [B]Figure 3[/B] shows the overall relationship between the various BI applications. Note how pivotal SharePoint Server 2007 is as the negotiation point for data between server and client applications.
[IMG]http://i.technet.microsoft.com/dd392007.fig03a%28en-us%29.gif[/IMG] Figure 3 [B]SharePoint Server 2007 at the heart of the Microsoft BI solution [/B]


Using Excel for Analyzing Data
Excel integrates well with both SQL Server 2005 and SharePoint Server 2007 and plays a pivotal role in providing end users with the tools for data analysis, data-mining construction, and the creation of PivotTables. Excel 2007 enhances the user's ability to access and analyze data from SSAS cubes using PivotTables and PivotCharts. Analysis Services data can also be accessed with Excel 2003 via PivotTables, but this involves some additional configuration.
Excel 2007 provides new features, such as the ability to view and work with Analysis Services KPIs and flexibility for building KPI scorecards. (Note that we refer to reports, dashboards, scorecards, and key performance indicators—or KPIs—as the means for analyzing, preparing, and presenting data within SharePoint Server 2007.) The screen in [B]Figure 4[/B] shows an Excel PivotTable. The workbook is directly linked to an SSAS cube. Fields from the PivotTable Field List, shown to the right of the figure, can be dragged and dropped onto the main workspace to create scorecards, including KPIs.
[IMG]http://i.technet.microsoft.com/dd392007.fig04_L%28en-us%29.gif[/IMG] Figure 4 [B]Excel workbook connected directly to SSAS showing PivotTable functionality [/B]

Excel workbooks can be stored within SharePoint document libraries, where they can be shared and versioned. Historical details can be dynamically captured and stored for each workbook. More importantly, Excel workbooks containing active connections to Analysis Services can be published to Excel Services and made available for viewing through a Web browser without the need for an Excel client.

Excel Add-Ins
Two Excel BI add-ins assist with remote data analysis and end-to-end data submission. The SQL Server 2005 Data Mining add-ins for Office 2007 include custom data mining and forecasting tools for Excel, such as the ability to estimate and predict based on certain patterns within selected data fields. [B]Figure 5[/B] shows the Data Mining menu on the Excel 2007 Ribbon.
[IMG]http://i.technet.microsoft.com/dd392007.fig05_L%28en-us%29.gif[/IMG] Figure 5 [B]Excel 2007 Ribbon showing the Data Mining menu options [/B]

A data mining Visio template is also included as part of the Data Mining add-in, and you can easily generate dependency network, cluster, and decision trees based on mining models from SSAS. The data mining add-in can be used to create data mining models on existing analysis server data or to perform additional queries in Excel workbooks. [B]Figure 6[/B] shows an example of a predictive model based on existing analysis server data.
[IMG]http://i.technet.microsoft.com/dd392007.fig06_L%28en-us%29.gif[/IMG] Figure 6 [B]Data-mining model decision tree based on an existing Excel 2007 workbook analysis server connection [/B]

As a best practice, use the PerformancePoint add-in for Excel to enable users to access assigned tasks remotely using Excel and submit updated worksheets to PerformancePoint Server for approval. ([B]Figure 7 [/B]shows the PerformancePoint add-in for Excel actively selected in the Excel 2007 Ribbon.) The add-in provides equivalent functionality in both Excel 2007 and Excel 2003, with the exception that some formatting features specifically related to pivot styles will not be available in Excel 2003. Note that the PerformancePoint add-in for Excel requires an existing installation of either Excel 2003 (SP2) or Excel 2007. Further details about prerequisites can be found in the article "[URL="http://technet.microsoft.com/library/bb838749.aspx"]Install the PerformancePoint Add-in for Excel[/URL]."
[IMG]http://i.technet.microsoft.com/dd392007.fig07_L%28en-us%29.gif[/IMG] Figure 7 [B]Excel 2007 Ribbon showing the PerformancePoint menu options [/B]


Office BI Integration with SharePoint Lists
Both Excel and Access continue to play a key role in the negotiation between client and server BI functionality within SharePoint Server 2007, and they provide the ability to expose data within SharePoint sites. For example, Excel worksheets can be published as a SharePoint list in which the data can be accessed and updated by multiple users.
The following are a few scenarios you should consider when using Access databases and assessing your data analysis needs:

[LIST][*]You can publish an Access database as a SharePoint list and perform additional data analysis in SharePoint. Other key benefits include filtering and sorting.[*]Access 2007 databases can be upgraded to SQL Server 2005 databases using the Access 2007 Upsizing Wizard. For instance, you could upsize an existing Access 2007 database to SQL Server 2005 for a scalable database solution and then use Reporting Services to create custom reports, which you could then publish directly to SharePoint Server 2007.[*]You could use Access 2007 as the front-end reporting mechanism for SharePoint lists. Access 2007 includes the ability to import a SharePoint list, or multiple SharePoint lists, into an Access 2007 database to perform additional reporting while maintaining an active link back to the list on the SharePoint server.[/LIST]

There are some key differences between working with data between Excel 2003 and Excel 2007, which are covered in the following sections.
Excel-to-SharePoint and Data Synchronization New lists within SharePoint sites can be created directly from existing Excel spreadsheets (for example, by selecting Create  | Custom Lists | Import Spreadsheet). SharePoint will import all columns and any existing data, or you can specify a range of cells to import, such as a range of cells, a table range, or a named range. Existing formulas within the source Excel worksheet will be converted to calculated columns.
If you are using Excel 2003, you can create a SharePoint list directly from within Excel by clicking a single cell in the Excel spreadsheet and selecting List | Publish List. This creates a new SharePoint list in Datasheet view. Note that when you are publishing from Excel 2003 to a SharePoint list, only a single worksheet will be published—in other words, if you have an Excel workbook with multiple worksheets, not all worksheets will be published.
Linkage can be maintained between the original Excel 2003 file and the newly created SharePoint list and synchronization can be performed both ways. That is, you can add new rows to the source Excel 2003 worksheet and have the linked SharePoint list updated. Likewise, you can update the SharePoint list and have the source Excel worksheet updated.
Using Excel 2007, you have two options for publishing data to SharePoint:

[LIST][*]You can publish to Excel Services. If Excel Services is installed as part of your SharePoint Server 2007 deployment, a one-way sync will exist between the source Excel 2007 file and the published server version. In other words, updates to the source file can be pushed to an existing published version and changes reflected within a Web page.[*]You can export a table within Excel 2007 to a SharePoint list. Once a table has been exported, no linkage remains between the source and destination data. That is, updating the original Excel file will not then update data in the SharePoint list, and data updated in the SharePoint list will not update the original Excel 2007 file.[/LIST]

The main difference between publishing from Excel 2003 and publishing from Excel 2007 to a SharePoint site is that you can publish not only a single Excel worksheet but an entire Excel workbook if you are using Excel 2007 with Excel Services installed. In Excel 2007, the publishing option has been built into the main File menu and will publish an entire Excel workbook to Excel Services.
SharePoint-to-Excel and Data Synchronization Lists within SharePoint sites can be exported to Excel, and you can choose to maintain a link between the exported data and the original SharePoint list. Updates to the SharePoint list will be synced to the external Excel spreadsheet. This is a one-way sync—updates to the exported Excel spreadsheet will not replicate to the original SharePoint list. Note that using the Export To Excel option from the Actions menu within a SharePoint Server 2007 document library or list, either in Standard or Datasheet view, requires the Excel 2007 client to be present.
Using the Datasheet view, you can directly interact with and manipulate data (such as selecting and deleting multiple rows of data) within the Datasheet view in the browser. Datasheet views also enable you to perform additional data analysis, such as exporting and reporting on list data within Access and using Excel to query, print, chart, and create PivotTables, as shown in the right task pane in [B]Figure 8[/B]. Both Excel 2003 and Excel 2007 are supported when you select the Query List with Excel task pane option within Datasheet view.
[IMG]http://i.technet.microsoft.com/dd392007.figure08_L%28en-us%29.gif[/IMG] Figure 8 [B]The Datasheet view [/B]

Keep in mind that the Datasheet view requires the presence of a client-side Windows SharePoint Services-compatible list datasheet control (such as Excel 2003, Excel 2007, Access 2003, or Access 2007) and ActiveX control support. This differs from the Excel Web Access Web Part, which is used in conjunction with Excel Services, does not require ActiveX control and does not have client-side dependencies.
Consequences of Excel Add-Ins
Be aware that certain Excel add-ins may cause an issue when you attempt to integrate Excel with SharePoint lists. Specifically, if you're attempting to create a new SharePoint (SharePoint Server 2007 or Windows SharePoint Services 3.0) list from either an Excel 2007 or Excel 2003 spreadsheet and you receive the error "Method 'post' of object 'IOW-SPostData' failed," the problem could be due to one or more Excel add-ins.
I've experienced a problem specifically with the Analysis ToolPack add-in, which is included as part of Excel 2007 out-of-the-box add-in options. There is a client-side solution to this problem that involves editing the EXPTOOWS.XLA file on the client and editing the file in Excel using the Visual Basic code editor. For details on how to accomplish this, see my blog post at "[URL="http://mindsharpblogs.com/kathy/archive/2008/03/16/4450.aspx"]Method 'post' of object 'IOWSPostData' failed[/URL]."
—Kathy Hughes, Microsoft MVP


Excel Services
Excel Services is part of SharePoint Server 2007 Enterprise Edition. A benefit of this edition is that you can publish an Excel workbook to a Web page and end users can view workbook contents without the presence of the Excel client. However, for users to interact with Excel Services, clients require the Office SharePoint 2007 Enterprise Client Access License (CAL).
The published workbook is effectively a read-only version of the source Excel workbook. This provides a good way to share information while protecting the integrity of the data—a published Excel workbook, such as an annual or fiscal budget, is a centralized, single version accessed by all. Here we will cover the key features of Excel Services.
Unlike creating a new SharePoint list by importing an Excel spreadsheet or publishing an Excel 2003 worksheet, you can use Excel 2007 to publish an entire Excel workbook to Excel Services. Within each workbook, you can limit the number of worksheets to be published or elect to publish all.
The three main Excel Services components are detailed here:

[LIST][*]The Excel Calculation Services (ECS) component loads the Excel workbook into a Web page, performs server-side calculations, and refreshes external data.[*]The Excel Web Access (EWA) Web Part displays a workbook or interconnecting parts of a workbook on a Web page or dashboard. It uses DHTML and JavaScript for Web page interaction, thus avoiding the need to download ActiveX controls to the client. EWA Web Parts can be configured to filter on specific values. EWA is unlike the Datasheet view available in SharePoint lists, which is dependent upon ActiveX controls along with the Excel or Access client. And EWA offers additional features, such as the ability to create a snapshot of an Excel workbook.[*]Excel Web services (EWS) is a Web service API that developers can use to develop custom or add-on applications for Excel Services. The typical address to access these Web services is [URL="http://server_site_name/_vti_bin/excelservice.asmx"]server_site_name/_vti_bin/excelservice.asmx[/URL].[/LIST]

Numerous configuration options are available when you deploy Excel Services. Typically, Excel Services installs EWA and EWS on the front-end Web server and one ECS on the application server. In a single-server deployment, EWA, EWS, and ECS are installed on the same server. In multi-server deployments, the components can be installed on separate servers. A major consideration in deploying Excel Services, and one you will want to plan carefully, is how the authentication between servers and clients is negotiated.

Authentication between Client and Server
In a distributed server environment where SQL Server 2005 and Analysis Services are on a different machine than SharePoint Server 2007 and Excel Services, and where you are leveraging external data connections—that is, an Office Data Connection (ODC) file—to your SSAS as the basis for your published Excel workbook, you need to configure Kerberos to impersonate the user accessing the database server and trust/delegation between servers. The same also applies when you are implementing Reporting Services integration where SQL Server 2005 and Reporting Services are on a different machine than SharePoint Server 2007.

Publishing Excel Workbooks
As a best practice, be sure to establish trusted file locations and trusted data connection libraries, as defined in the Excel Services Settings in Shared Services, before publishing your Excel workbooks. They form the basis for successfully publishing Excel workbooks, such as publishing an online analysis processing (OLAP) PivotTable, which uses an ODC file located within a Trusted Data Connections Library. Excel 2007 includes an option within the main File menu to publish to Excel Services. (Note that the Excel workbook must first be saved to a trusted file location.)
ECS performs calculations included within the source Excel workbook on the server, which means that actual functions and formulas are not directly accessible for end users.
The main inference in utilizing Excel Services is that an Excel workbook can be published to a Web page, but the published version is not editable. Users cannot make edits or enter new data and then save the changes to the source Excel file/workbook. Instead, users can perform run-time calculations via the browser based on parameters created in the source Excel workbook. Once a Web page containing EWA is refreshed or a user ends her browser session, any calculated changes are lost.
Snapshots of Excel workbooks can be captured and saved as separate Excel files to the client machine. A snapshot includes the data values within the published Excel workbook and excludes any formulas or other business logic from the source workbook. End users with viewer permissions to the document library containing the source Excel file and location of the published Excel file are able to copy the published Excel workbook to an Excel file (including data values and formulas) and open a snapshot of an Excel workbook. [B]Figure 9[/B] shows both the Open In Excel and Open Snapshot In Excel options available from the EWA Web Part toolbar.
[IMG]http://i.technet.microsoft.com/dd392007.fig09_L%28en-us%29.gif[/IMG] Figure 9 [B]Excel export options available within the EWA Web part toolbar [/B]

Excel 2007 is required for Excel snapshots and Excel copies from an EWA Web Part. If Excel 2007 is not installed on the client machine where a snapshot or copy is being attempted, the following message is displayed: "The workbook cannot be opened. Excel may not be installed properly (or was set to install on first use), the path to the document may be incorrect, or your session may have timed out. Try opening the workbook again, or clicking Reload on the Excel Web Access toolbar."
Excel 2007 workbooks that contain multiple worksheets can be published to Excel Services. The person publishing the workbook can choose which worksheets within a workbook to display at the time of saving and publishing to Excel Services. Portions of an Excel worksheet can also be published based on named cells, or parameters, defined within the source Excel workbook.
As a best practice, you should enable auditing, versioning, and content approval on the Reports document library (or the nominated document library location) for published Excel workbooks to monitor edits and updates to the source Excel workbooks.

Configuring Excel Web Access Web Parts
Once an Excel 2007 workbook is published to Excel Services, the EWA Web Part provides the mechanism for viewing and manipulating the published workbook within the browser. (The EWA does not have client-side dependencies, nor does it require an ActiveX control to be downloaded to the client.)
An entire Excel workbook can be displayed in a single EWA. End users can navigate worksheets within a published workbook just as they would when using the Excel client. Sections of Excel workbooks can also be divided into individual EWA Web Parts on a Web page (also referred to as a dashboard) based on named parameters within the source Excel 2007 workbook. [B]Figure 10[/B] shows several EWA Web Parts on a single Web page, which includes filtering and Web Part connections between each EWA Web Part to dynamically refresh and display data based on the name of the account representative selected.
[IMG]http://i.technet.microsoft.com/dd392007.fig10_L%28en-us%29.gif[/IMG] Figure 10 [B]Dashboard showing multiple instances of EWA Web parts [/B]

Using filtering, Excel parameters, and Web Part connections between EWA Web Parts, you can generate powerful and intuitive dashboards within SharePoint Server 2007.

Unsupported Features in Excel Services
Not all Excel client-application features are supported in Excel Services. For example, attempting to publish Excel files that contain code—such as Visual Basic for Applications macros, embedded pictures, or clip art—will result in failure to publish. Other non-supported client-side functionality includes, but is not limited to, data validation and external references to linked workbooks.
It is not always apparent whether an Excel workbook is compatible with Excel Services until you try to publish it. However, there is an open source Excel Services Compatibility Checker (see "[URL="http://blogs.msdn.com/cumgranosalis/archive/2007/06/29/excel-services-compatibility-checker-addin-beta.aspx"]Excel Services Compatibility Checker Addin - Beta[/URL]" for details and get the download at [URL="http://blogs.msdn.com/cumgranosalis/pages/excel-services-compatibility-checker-download-page.aspx"]Excel Services Compatibility Checker Download Page[/URL]).

Options and Costs
If you currently have the standard version of SharePoint Server 2007 and are considering integrating Excel workbooks with SharePoint sites as part of your overall BI solution, consider the following options:

[LIST=1][*]Upgrade to SharePoint Server 2007 Enterprise Edition and use Excel Services.[*]Keep SharePoint Server 2007 Standard Edition and use the built-in document management features within SharePoint document libraries to store, version, and share Excel workbooks in their native format.[*]Keep SharePoint Server 2007 Standard Edition and create a new SharePoint list from an existing Excel spreadsheet.[/LIST]

Option 1 incurs additional costs and deployment considerations, such as changing to Kerberos (if you're not already running in Kerberos mode) and upgrading to Excel 2007 (if you're currently using Excel 2003). However, it provides a more scalable Excel solution without the need for the Excel client or ActiveX control.
Option 2 means that the Excel workbook will be retained in its original native format and can be checked out and edited by authorized users. However, the Excel client must be present on those clients responsible for updating Excel workbooks. Excel workbooks uploaded to a SharePoint document library can be secured using the security features in Excel, but there is a risk that end users will still be able to republish the workbook elsewhere, resulting in duplicate copies of the same workbooks.
Option 3 means that, although a new list will be created from an Excel worksheet, there are implications in continuing to update the list from an Excel client or the portability of data within the list. SharePoint lists have limitations for data types and Excel functions, and it is difficult to append updates to an existing list from an Excel workbook. Also, if your organization is currently running Excel 2003, users will experience compatibility issues when you upgrade to Excel 2007. Unlike Excel 2003, Excel 2007 does not provide two-way synchronization with SharePoint.
If you plan to stay with the Standard Edition and you are using multiple Excel workbooks throughout your organization (and custom formulas), then we recommend that you maintain the existing Excel workbooks in their native Excel format and utilize the document management features within SharePoint document libraries, such as versioning and approval, so you can monitor who has updated what and when. In addition, you should lock down access to Excel workbooks using the built-in SharePoint security model and give edit rights only to those users who will actually need to update existing workbooks and upload new workbooks.
If you are contemplating upgrading at a later stage and the basis of that upgrade is BI—specifically, Excel features—carefully consider the business requirements. For instance, are you planning to deploy a read-only version of Excel workbooks, or are you planning to have a distributed Excel authoring environment? If it's the latter, consider purchasing PerformancePoint Server 2007.
Remember, while Excel Services enables you to publish an entire Excel 2007 workbook as a Web page in which end users can access Excel data and perform run-time calculations (without being able to save data back to the source Excel workbook), the onus of updating and republishing an Excel workbook falls upon one or several people. PerformancePoint Server 2007, on the other hand, offers the ability to distribute Excel tasks to end users, providing centralized and secure control over user access and Excel templates.
Using Analysis Services Data in Excel Services
I started working with Excel Services, in combination with Analysis Services data, in my virtual environment. This basically meant that everything including client, SQL Server 2005, SharePoint Server 2007, and Excel Services were all configured on the one virtual machine. The Web application was configured to use NTLM and everything worked seamlessly. However, enter a real-world scenario where the customer's deployment consists of a multiple-server environment—for instance, where SharePoint Server 2007 and Excel Services are hosted on one server and SQL Server 2005 and Analysis Services are hosted on another—and you suddenly realize that NTLM just won't cut it!
In order for Excel Services to successfully access a separate SQL server, you need to impersonate and delegate the user account. You can do this by implementing Kerberos authentication, or single sign-on (SSO). Simply using NTLM is not sufficient because the Web server cannot delegate the current user to the SQL server, also referred to as a double hop.
A best practice when testing an Excel Services and Analysis Services scenario is to test within a multi-server environment so you can accurately test authentication and ensure that clients and servers can successfully access back-end Analysis Services cubes and data. This will better prepare you for real-world scenarios and deployments.
—Kathy Hughes, Microsoft MVP

[/LEFT]