Archive for June 5th, 2007

Excel Services – Office at TechMela June 5th, 2007

Vinod Kumar

In this segment I wanted to talk about the Excel Services part which I mentioned when I wrote the Excel Post. Though we are not talking much on this topic at TechMela, I thought it is important to understand this nevertheless. So here we go !!!!

Excel Services, part of SharePoint Server 2007 (MOSS), extends the capabilities of Excel 2007 by allowing broad sharing of spreadsheets, improved manageability and security and the ability to re-use spreadsheet models through a scalable server-based calculation service and interactive web-based user interface.  Excel Services provides both a web-based UI for browser-based access and a web services API for programmatic access that have robust options for controlling what data is available to users and applications. As part of Microsoft Office SharePoint Server 2007, Excel Services also takes advantage of the other capabilities of SharePoint including the enterprise content management features such as: check-in/check-out, auditing and versioning capabilities. This is huge …

The Excel Services architecture consists of a web front end and an application-server tier. The Excel Calculation Server loads requested spreadsheets and performs any calculations required. Excel Web Access renders the results in HTML, while Excel Web Services provides a Web services interface to allow applications to access the spreadsheets. The application server contains Excel Calculation Service which loads spreadsheets, calculates them, and provides access to external data. Either or both of these tiers can be scaled up or out, including scaling to high-performance computing clusters. Excel Services supports configurable load balancing and a number of options that can improve performance. Excel Services is one app that you can think when it comes to implementing and testing your HPC ideas.

Excel 2007 also includes a simple means by which spreadsheet authors can make use of Excel Services. Using the File | Publish menu option, a new menu item called “Excel Services” enables the user to save the spreadsheet to a SharePoint (or any UNC path that is accessible by the server) server. While a full copy of the spreadsheet is copied to the server, the Excel Services Options dialogue enables the author to specify what aspects of it are accessible to users from the web browser.

Excel Services draws its mechanisms for authentication and access permissions from MOSS 2007.  This allows spreadsheet authors to grant users Reader rights, Contributor rights, or Administrator rights (full control). In addition to these rights, the new Viewer right allows authors to lock down spreadsheets for server-only viewing through a Web browser. Next, the mechanism for controlling who can act as an author for spreadsheets available under Excel Services is managed by a system of trusted file locations, which can be designated as SharePoint locations, UNC paths, or HTTP paths.

Excel Services supports getting external data from the same data sources Excel client supports, such as SQL Server Analysis Services, SQL Server relational databases and other database platforms using mechanisms such as ODBC and OLE-DB. Data can be returned into PivotTables or new formulas that can access SQL Server Analysis Services data. One powerful way of governing access to external data sources by Excel Services is by using the Data Connection Libraries (DCLs). Created by database administrators, DCLs contain .ODC (Office Data Connection) files that persist specific data connections, enabling business users to take advantage of them, using the Excel client via a directory service operated by the server. I would urge you to use this because the DCLs help you centralize all your connections in one repository under MOSS.

So this is a quick tour of what we have, but I have not talked about the Authentication models for queries, content approval process, using it as BI reporting and dashboards, Web Services API’s available, User-defined functions etc. But these all fall under this big umbrella. So before I sign off, let me give few interesting links that might interest all:

Cum Grano Salis 
Microsoft Office SharePoint Server 2007 (Beta) SDK 
SharePoint Server 2007 Developer Portal 
Downloadable SharePoint Server 2007 SDK 
LuisBE on Services 
Excel Services Technical Overview 
Using Excel Web Services in a SharePoint Web Part 
Deploying and Optimizing a SharePoint Web Part That Calls Excel Web Services 
SharePoint Server 2007 Sample: Web Part That Calls Excel Web Services 
Excel Services Information Center 
Introducing Excel Services 
Extending the Excel Services Programmability Framework 
Sherman On Excel and BI Technology 

Though we are not talking on this very topic, feel free to catch me on the Office System Track and we can discuss during TechMela. Cya all in a weeks time :).

Continue reading...