Technology architecture is one of the foundation for evaluating whether it would be the right choice to cater requirements of your solution. A general human characteristic is that "perception becomes reality". Flashy graphics, elegant color, super speedy slice and dice are such bullets that most business users cannot dodge, but here is where architects need to pitch-in to find the how does the entire machinery work i.e. architecture of the product. Below mentioned are certain characteristics of Excel services which one should keep in view while technical evaluation.
1) Excel services is an enterprise edition only feature in MOSS 2007 and MOSS 2010. So if you are planning to use lower editions of MOSS, this feature won't be available.
2) MOSS 2010 has REST API to support excel services which helps to bring selective data at client side. This API is not available in MOSS 2007.
3) Ability to query data from relational tables and display it in a tabular format is not supported out-of-box. With UDFs this is possible, but it would require custom .net programming, deploying the same on sharepoint. You define the functions when you author the workbook, but you see effect only when the workbook is accessed inside the excel web access web part on the moss site on which excel services is activated.
4) Standard connection methodology to connect to data sources is using ODC files stored in data connection libraries which would be configured as trusted for excel services. Dynamic parametrized queries are not supported at all in excel services. The only way to achieve a flavor of it is by using UDFs only.
5) Fetching data in a pivot table from OLAP data sources is well supported out-of-box. The report that you built out of this data can work smoothly if you have filters fields within the workbook itself. If your filter values are flowing outside your excel workbook, you would be required to use filter web parts.
6) Many features of excel which are frequently used by excel users are not supported, for ex. freeze panes, external pictures etc. So do not be in the impression that excel services is a full fledged excel on sharepoint.
7) If you intend to show data with user specific authorization, you need to contain all the data in the excel workbook that you would host on sharepoint, which in turn would be displayed to users from excel web access web part. Passing parameters to data sources from the client side through excel workbooks is not a straight-through process.
8) You can filter the data visible to end users in the workbook, but if the user chooses to edit the workbook by downloading it locally, the entire data is available to users.
9) You can define parameters in excel workbooks when they are published to sharepoint. The same parameter values can be fed in by users, and the entire report (i.e. data in the workbook) can be programmed using simple excel formulas to respond to the parameter values.
10) Excel is used as the authoring mechanism for excel workbooks and this workbook acts as the template for the data that would be hosted in this workbook from data sources. Conditional formatting is very well supported, but for any web based formatting would require using JQuery on the sharepoint page where excel web access web part would be hosted.
Few helpful links to get started with excel services:
- http://technet.microsoft.com/en-us/library/cc262899(office.12).aspx#subsection8
- http://msdn.microsoft.com/en-us/library/aa973804(v=office.12).aspx#Y2512
- http://blogs.msdn.com/b/cumgranosalis/archive/2006/03/24/excelserviceshelloworld.aspx
- http://blogs.office.com/b/microsoft-excel/archive/2008/10/15/using-office-data-connection-files-odc-and-the-dataconnections-web-part-in-sharepoint-to-specify-external-data-connections-in-newly-created-excel-workbooks.aspx
- http://office.microsoft.com/en-us/sharepoint-server-help/connect-filter-web-parts-to-excel-web-access-HA010105470.aspx?CTT=5&origin=HA010105476
Summary: My conclusion for excel services is that it's a suitable candidate when you need excel like capabilities over static data. It's a nice candidate for what-if analysis. If you intend to use it in the way you use SSRS with OLTP sources, Excel Services is definitely NOT the right choice. It's a good candidate for context-sensitive non-parameterized data, which is mostly the nature of data in OLAP sources having structures like dimensions and hierarchies. It's definitely not the right choice where you need to display data dynamically, real-time, and based on conditional parameterized querying mechanism.
Article 
No comments:
Post a Comment