www.sharexxx.net - free books & magazines
Beginning
Excel
®
Services
Liviu Asnash
Eran Megiddo
Craig Thomas
104899ffirs.qxd 1/28/07 4:30 PM Page 3
104899ffirs.qxd 1/28/07 4:30 PM Page 6
Praise for
Beginning Excel
®
Services
“A must read for anyone wanting to learn how Excel Services revolutionizes the world of
spreadsheets!”
Richard McAniff
Corporate Vice President,
Microsoft Office
“This book will guide you through everything you need to know about the great new func-
tionality in Excel Services. Microsoft Office SharePoint Server 2007 is Microsoft’s platform for
business applications. Excel Services provides great new business intelligence functionality
and helps users control and manage their spreadsheets. We’re already seeing these tools and
services unleashing tremendous creativity in the developer community.”
Kurt DelBene
Corporate Vice President,
Office Business Platform Group
“Microsoft Excel’s sophisticated calculation and analysis capabilities, combined with ease of
use and programmability, have led to wide deployment in Capital Markets for critical applica-
tions such as pricing and risk management. To date, management and auditing, of what can
be highly complex linked workbooks, have posed challenges.

Group Program Manager,
Microsoft Excel
“Now, the analysis and models in your spreadsheets can instantly be part of a scalable
enterprise-class application. I spent eight years working at Wells Fargo and Ernst & Young,
and I have seen hundreds of examples of spreadsheets being used as part of a reccurring
business process. Beginning Excel Services clearly and concisely shows you how to use Excel
Services to start to automate those spreadsheet-based business processes and thus save sub-
stantial amounts of time, effort, and money.”
Rod Boothby
Senior Director, Solution Marketing,
Teqlo Inc.
104899ffirs.qxd 1/28/07 4:30 PM Page 2
Beginning
Excel
®
Services
Liviu Asnash
Eran Megiddo
Craig Thomas
104899ffirs.qxd 1/28/07 4:30 PM Page 3
Beginning Excel
®
Services
Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada

For general information on our other products and services please contact our Customer Care
Department within the United States at (800) 762-2974, outside the United States at (317) 572-3993
or fax (317) 572-4002.
Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Programmer to Programmer, and related
trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates,
in the United States and other countries, and may not be used without written permission. Excel is a
registered trademark of Microsoft Corporation in the United States and/or other countries. All other
trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with
any product or vendor mentioned in this book.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print
may not be available in electronic books.
104899ffirs.qxd 1/28/07 4:30 PM Page 4
To my amazing wife, Nurit, who inspires me in everything I do. You mean everything to me.
And to my son, Edan, who reminds me that everything is possible.
—Liviu Asnash
To my wife, Inbal, for making me laugh when I need it most, and our two beautiful daughters,
Yael and Adi, for making me smile every step of the way. You make life fun and wonderful.
And to the Maximal team who “make things happen.”
—Eran Megiddo
To my wife, Mele, and daughters, Emma and Cecelia, for inspiring me each and every day.
I’m truly blessed. I love you all very much.
—Craig Thomas
104899ffirs.qxd 1/28/07 4:30 PM Page 5
104899ffirs.qxd 1/28/07 4:30 PM Page 6
About the Authors
Liviu Asnash is a Principal Development Lead at Microsoft, and currently manages the development of
Excel Services (which is part of Microsoft Office SharePoint Server 2007). Asnash has more than 15 years
of experience in enterprise-level software development. In the past five years, he has worked on Microsoft
Office in areas related to Business Intelligence, Excel, and Excel Services. Prior to joining Microsoft, he was
the director of Research and Development at Maximal Innovative Intelligence, a software company that

Dan Battagin
Copy Editor
Kathryn Duggan
Editorial Manager
Mary Beth Wakefield
Production Manager
Tim Tate
Vice President and Executive Group Publisher
Richard Swadley
Vice President and Executive Publisher
Joseph B. Wikert
Compositor
Laurie Stewart, Happenstance Type-O-Rama
Proofreader
Jen Larsen, Word One
Indexer
Melanie Belkin
Anniversary Logo Design
Richard Pacifico
104899ffirs.qxd 1/28/07 4:30 PM Page 9
104899ffirs.qxd 1/28/07 4:30 PM Page 10
Acknowledgments
This book is the culmination of an effort we’ve all been involved with for more than three years as Excel
Services progressed from thoughts and ideas to code running in production. Along the way, a lot of people
crossed our paths and helped us get to where we are today. The words here cannot adequately express
our gratitude to the Excel Services Testing, Development, and Program Management teams at Microsoft,
who put together this amazing product and gave us something to write about. Thank you all.
Also, thanks to Wiley Publishing, especially Katie Mohr, for the support and for giving us this opportu-
nity to complete our first book.
104899ffirs.qxd 1/28/07 4:30 PM Page 11

What Excel Services Is Not 27
Summary 28
104899ftoc.qxd 1/29/07 1:44 PM Page xiii
xiv
Contents
Chapter 2: Getting Started with Excel Services 29
Installation Types 29
Platform Requirements 30
Hardware 30
Software 31
Excel 2007 Client 32
Installing an Evaluation Copy 32
Setup Wizard 33
Configuration Wizard 37
After Setup Completes 38
Excel Web Access 39
Excel Web Services 42
Summary 42
Chapter 3: Architecture 45
Getting to Know Excel Services 45
Understanding the Architecture 47
Office SharePoint Server Architecture 48
The Excel Calculation Server (ECS) 50
The Excel Web Access (EWA) Web Part 51
The Excel Web Services API 52
Understanding Operational Concepts 53
Sessions 53
Workbook Operations 55
Publishing and Loading Workbooks 56
Querying Data from External Databases 58

Chapter 5: External Data 91
Connecting to Data 92
Data Connection Libraries 93
Data Connections in Excel Services 95
Trusted Data Providers 96
Supported Data Objects 99
Using UDFs to Query External Data 99
Security 99
Security Threats 100
Security Mitigations 101
Credentials Delegation 103
Performance 109
Caching and Sharing 109
Connection Pooling 111
Parallel and Asynchronous Queries 111
OLAP Formulas 111
Excel Web Access 112
Refresh on Open 112
Interactivity 113
Periodic Refresh 114
Common Configuration 116
Line of Business Data 116
104899ftoc.qxd 1/29/07 1:44 PM Page xv
xvi
Contents
Reporting on Restricted Data 116
Business Intelligence Portal 117
Summary 117
Chapter 6: Capacity and Deployment Planning 119
Choosing a Deployment Topology 119

xvii
Contents
Opening Workbooks 193
Protecting the Workbook Content 195
Configuring and Delegating Credentials 196
Protecting Against Attacks 198
Threats 198
Spoofing 199
Tampering with Data 200
Repudiation 202
Information Disclosure 202
Denial of Service 204
Elevation of Privilege 207
Extensibility 207
Summary 209
Part III: How-To Scenarios 211
Chapter 9: Sharing Workbooks with the Browser 213
Publishing a Workbook to the Server 213
The Simple Case 213
Controlling What’s Visible in the Browser 217
Other Ways to Save Your Workbook to the Server 220
Common Issues with Publishing Workbooks 221
Viewing Workbooks in the Browser 225
Viewing a Workbook from a Document Library 225
Changing the Default Click to View in Browser 226
The Excel Viewer Page 227
Sending a Link to View a Workbook in the Browser 227
Summary 229
Chapter 10: Interacting with Workbooks in the Browser 231
Navigating Workbooks and Hyperlinks 232

Alerts and RSS Feeds 283
Auditing Workbook Usage 286
The End-to-End Scenario 290
Summary 292
Chapter 12: Business Intelligence Solutions 293
Report Centers 293
Report Libraries 296
Key Performance Indicators 300
Building Business Intelligence Dashboards 305
Business Intelligence Everywhere 317
Working with SQL Server Analysis Services 318
Connecting to Analysis Services from Excel 319
Building a Data-Bound Dashboard 324
Summary 329
104899ftoc.qxd 1/29/07 1:44 PM Page xviii
xix
Contents
Chapter 13: Offloading Workbook Calculation to the Server 331
The Benefits 331
Setup and Configuration Considerations 332
Excel Services Settings 332
Deployment Topology Considerations 333
Sample Solution Designs 334
Integrating with Windows Compute Cluster Server 2003 335
Summary 335
Chapter 14: Building Custom Solutions 337
Excel Services Web Service 337
First-Use Information 338
Creating a Custom API Application 340
Local Linking 343

Creating a UDF 372
ECS XLL UDF 375
Xlviewer Customizations 376
Setting New Defaults and URL Parameters 376
Configurable Properties 378
EWA and API Working Together 379
JavaScript Approach 380
Managed-Code Approach 383
Summary 389
Appendix: Troubleshooting 391
Index 393
104899ftoc.qxd 1/29/07 1:44 PM Page xx
Introduction
Excel is, by far, the most popular spreadsheet tool available. It is used by millions of end users each day
to solve a wide range of problems. It provides solutions that span the gamut from simple list-keeping to
mission-critical trading solutions that drive multimillion dollar trades on Wall Street. Everyone with an
M.B.A. knows how to use it (and probably was already introduced to it somewhere back in middle
school). Every financial department relies on it. Managers in business in every industry and at every
level of the organization make decisions based on the numbers presented by and calculated in Excel.
Given how critical Excel is for business and users, it is not surprising that the need for better management,
distribution, and incorporation of spreadsheets in larger applications is ever-increasing. Excel Services is an
exciting new technology being delivered as part of Microsoft Office SharePoint Server 2007 to address this
need. Excel Services enables the execution and sharing of workbooks on the server, thereby providing
one version of the truth. It enables managed and secure distribution of Excel reports, incorporating
spreadsheets in business intelligence (BI) dashboards and portals, protecting the proprietary informa-
tion in spreadsheets, and building custom applications with Excel-based logic.
The authors of this book led the development of Excel Services at Microsoft. By sharing their insights
into the benefits and usage of Excel Services here, they hope to help you solve your business problems.
Whom This Book Is For
This book is an introduction to Excel Services for all those who want to understand what it is, what it

version of the product on your server.
❑ Chapter 3, “Architecture,” explains the main components and how they work.
The second part of this book, “Working with Excel Services,” goes into more detail about various aspects
of the technology, and includes the following chapters:
❑ Chapter 4, “Workbook Support,” discusses what types of workbooks and which Excel features
are supported by Excel Services.
❑ Chapter 5, “External Data,” discusses querying data from external databases into Excel Services,
and the relevant security and performance implications.
❑ Chapter 6, “Capacity and Deployment Planning,” starts by explaining the supported topologies
and then discusses capacity planning.
❑ Chapter 7, “Administration of Excel Services,” drills down into the various administrative settings
and other features intended for system administrators.
❑ Chapter 8, “Security,” discusses the most important security threats, and then explains the features
and recommended configurations to mitigate them.
The third part of this book, “How-To Scenarios,” provides several step-by-step procedures to implement-
ing important Excel Services scenarios, and includes the following chapters:
❑ Chapter 9, “Sharing Workbooks with the Browser,” describes the steps needed to publish a
workbook to the server and view it in the browser.
❑ Chapter 10, “Interacting with Workbooks in the Browser,” describes the functionality available
to interact with the workbook in the browser.
Introduction
xxii
104899flast.qxd 1/28/07 4:30 PM Page xxii