Excel Services – One version of the truth

1. Excel Services – One version of the truth Paul Cross Architect, Microsoft

2. Office System Timeline October 2003 OfficeSystem May 2004 Office 12Pillars Q4 CY 2005 TechBeta • ~$700M annual R&D investment across Office programs, servers, and services • Driven by customer, partner and analyst feedback • Enterprise focus with emphasis on empowering the individual • Long-term commitment to investment areas • Requirements: • Client programs: Windows XP SP1 or later • Servers: Windows Server 2003, SQL Server 2000 or later Q4 CY 2006 Public Availability

3. Enterprise Content Management Make it simple to author and managecontent and documents Knowledge Discovery and Insight Make the right information available to more people Collaboration Keep co-workers, partners and customers in sync PersonalProductivityIncrease employee self-sufficiency andeffectiveness Office System Investments InformationWorker Solutions Build client and web-basedapplications with workflow and line-of-business interoperability Fundamentals Make it more secure, manageable and reliable

4. Office SharePoint Server Business Intelligence Collaboration Portal Docs/tasks/calendars, blogs, wikis, e-mail integration, project management “lite”, Outlook integration, offline docs/lists Server-based Excel spreadsheets and data visualization, Report Center, BI Web Parts, KPIs/Dashboards Platform Services Rich and Web forms based front-ends, LOB actions, pluggable SSO Enterprise Portal template, Site Directory, My Sites, social networking, privacy control BusinessProcesses Workspaces, Mgmt, Security, Storage, Topology, Site Model Content Management Search Enterprise scalability, contextual relevance, rich people and business data search Integrated document management, records management, and Web content management with policies and workflow

5. Excel on Servers today Automating spreadsheet creation, update and calculation is complex Excel was designed as a client program; not robust and scalable on servers Publishing spreadsheets to users leads to many versions of the truth Incorporating Excel logic into applications often requires re-coding

6. Why Excel on the Server? Provide browser-based access to spreadsheets Incorporate spreadsheets in portals and dashboards Limit access to spreadsheets for regulatory concerns and to protect proprietary information Eliminate “multiple versions of the truth” caused by distributing copies of spreadsheets Leverage servers to offload long-running calculations from desktop machines Reuse logic & business models built in Excel in applications written in other languages without having to re-code the logic/business models

7. Excel Services What is it? Server-side Excel calculation engine Browser access to live, interactive server spreadsheets Programmatic Web service access to server-side Excel calculation What are the benefits? Server-grade: scalable, performant, robust Easy to deploy: zero-footprint on client machines Lockdown access to protect sensitive Excel models “BI Portals” with Excel dashboards – built by users Automate creation and update of server spreadsheets Create solutions using Excel authored business logic

8. Excel Services Excel “12” Excel “12”client Customapplications Browser100% thin View and Interact Author & Publish Spreadsheets Open Spreadsheet/Snapshot Web Services Access

9. Architecture Web front-end SharePoint UI HTML rendering + web services Application server Loads spreadsheets, refreshes data, calculates Maintains state for interactivity File + query caches for performance The Office servers provide Solution platform Store: spreadsheets, connection files Management: settings, UI, scripting Security: Authentication, Authorization Single box or multi-tier Independent scale-out Web front end Web front end Web front end Web front end Web front end Web front end Web front end Excel web access Excel web services Excel wb access Excel wb access Excel wb access Excel wb access Excel wb access Excel wb access Excel web sevices Excel web sevices Excel web sevices Excel web sevices Excel web sevices Excel web sevices ECS Proxy ECS Proxy ECS Proxy ECS Proxy ECS Proxy ECS Proxy ECS Proxy Application Server Application Server Application Server Application Server Application Server Application Server Application Server ECS interface ECS interface ECS interface ECS interface ECS interface ECS interface ECS interface Excel calculation service Excel calculation service Excel calculation service Excel calculation service Excel calculation service Excel calculation service Excel calculation service External data sources

10. Architecture Three components divided into two major groups Front-end (“web front end”) Back-end Configurations Simple - Single box Complex – Multiple boxes (scale up/out) Security provided by SharePoint Performance and scalability Optimised for multi-user access Caching at multiple levels (sheets, external data, etc.)

11. What Excel Services is not… A spreadsheet creation tool Unable to edit spreadsheets in the browser Multi-user spreadsheet authoring Workbook loaded once in memory Each user has there own session (UI or WS-*) High-end calculation server Optimised for multi-user access as opposed to calculations Some tweaks made for leveraging server-side hardware but spreadsheets are not spread across multiple boxes

12. Excel Web Services - Purpose Build custom .NET applications around server-side Excel spreadsheets Scenarios Using server-side Excel logic in applications Author part of the business logic in Excel Protect and maintain Excel IP on the server Automating spreadsheet updates on servers Refresh external data and parameterize Process generated spreadsheets Create, store and deliver snapshots Custom UI to server-side Excel calculation

13. Excel Web Services - Functionality “Open” a spreadsheet file (start a session with Excel services) Set values to cells and (named) ranges Process the session spreadsheet: Refresh external data sources Calculate spreadsheet or specific ranges Get results Entire calculated spreadsheet – live or snapshot Values from cells and (named) ranges

14. Excel Web Services – Functionality GetApiVersion Returns a version string of the installed web service API build. sessionId = OpenWorkbook(filePath,…) Open a server-side calculation session, returning a sessionid. GetSessionInformation Returns properties associated with the server session, e.g. the language context of the session. SetCell Set a value into a cell on one of the workbook’s sheets (Accessed using cell address, e.g. “B52”, named range, e.g. “Interest” or integer coordinates (may be more appropriate for programmatic access) SetRange Same as SetCell, but for setting values into an entire contiguous range. Same two flavours exist.

15. Excel Web Services - Functionality Refresh Read data from an external data connection (or all of the workbook’s connections) and refresh the values in the relevant cells, e.g. in PivotTable cells or in the results of cube formulas. Calculate Recalculate the formulas in a specific range or in the entire workbook. Useful when the workbook author has turned off automatic calculation. Two flavours – using a string or integer coordinates to refer to a range – much like in the Set methods. CalculateWorkbook Calculate the entire workbook, using one of two calculation methods: Recalculate - Calculate only formulas that have dependencies that changed (aka “dirty” formulas). CalculateFull - Calculate all formulas, regardless of dependency changes. GetCell Get a value out of a cell. Two regular addressing flavours exist. Formatted string values, or raw binary values. GetRange Get a set of values out of a contiguous range of cells. Same addressing flavours.

16. Excel Web Services - Functionality GetWorkbook Get the entire calculated workbook into application memory as a byte Llive result, or a snapshot (essentially a workbook with the layout of the original workbook, with all the original formatting and with up-to-date values – but with all the formulas and external connections stripped, and without the portions of the workbook that were marked not for viewing during publish. CancelRequest If your application runs the Excel Web Services session in a separate thread, and wishes to abort a long-running server request (e.g. a long calculation that the user got tired of waiting to) – it can do so by calling this method. CloseWorkbook Tell the server to close the workbook that it opened for this session, thereby also allowing the server to release all the resources that it maintained for the context of your session.

17. Excel Services - Error handling Errors are exposed to developers in three ways: Calculation errors appear as they do in traditional Excel, e.g. #VALUE! Calling GetCell or GetRange requesting formatted values, #-style error string returned. Calling GetCell or getRange requested unformatted values, enumerated error code returned. Web services errors exposed as SOAP exceptions Less critical errors returned as part of the method arguments, e.g. those errors that do not cause abnormal results to be returned. Checking for these errors is optional

18. Excel Services - Web Service Example private void CalculateUsingWebService() { Status[] status; string sessionId = null;     // Step 1: Instantiate the web serviceXlMortgageCalcWebPart.Es.ExcelService es = new XlMortgageCalcWebPart.Es.ExcelService(); // Step 2: Set web service link es.Url = this.ExcelWebServiceUrl;// Step 3: Set credentialses.Credentials = System.Net.CredentialCache.DefaultCredentials; // Step 4: Start the session try { sessionId = es.OpenWorkbook(this.MortgageCalculatorWorkbookUrl, String.Empty, String.Empty, out status); }catch{ sessionId = null;}

19. Excel Services - Web Service Example if (sessionId == null) {_lblError.Text = "Error opening workbook.  Please make sure that the correct MortgageCalculatorWorkbookUrl and ExcelWebServiceUrl are specified in the Web Part Properties.";this.Controls.Clear();this.Controls.Add(_lblError);return; } // Step 5: Set parameters es.SetCellA1(sessionId, "SimpleCalculator", "MortgageAmount", _txtMortgageAmount.Text.Trim());   es.SetCellA1(sessionId, "SimpleCalculator", "MortgageLength", _txtMortgageLength.Text.Trim());   es.SetCellA1(sessionId, "SimpleCalculator", "InterestRate", _txtInterestRate.Text.Trim());

20. Excel Services - Web Service Example // Step 6: Get result object o = es.GetCellA1(sessionId, "SimpleCalculator", "Payment", true, out status); if (o != null) {  _lblTotal.Text = Convert.ToString(o); } Else { _lblError.Text = "Error getting total value from workbook."; this.Controls.Clear(); this.Controls.Add(_lblError);return; } // Step 7: End the session status = es.CloseWorkbook(sessionId); }

21. Threading Client Is multi-threaded Supports multi-threaded recalculation Only functions defined in XLLs able to participate (XLL interface modified to allow developer to specify) UDFs/VBA unable to participate However, XLL -> .NET a possibility… Server Is multi-threaded Each user session or workbook session opens on a different thread Box with multiple CPUs or cores, we can calculate multiple spreadsheets or instances of a spreadsheet simultaneously Multi-threads external data queries (native to Excel such as PivotTables not UDFs) Does not support multi-threaded recalculation

22. Multi-threaded Calculation (MTC/R) Utilises multi-processor or dual core-core chips Excel spots formulae that can be calculated concurrently Default is 1 thread per processor Monte Carlo calcs will benefit (large number of independent functions) Functions defined in XLLs able to participate VBA and automation add-in UDFs will not be multi-threaded XLL interface has been updated to allow developers to advertise their XLL functions as thread-safe

23. Multi-threaded Calculation (MTC/R) Calculation results unaffected by MTC/R Capability may be switched off Some overhead first time workbook loaded Information cached and subsequent calls unaffected User may override thread settings

24. Excel Services – Limitations? In this first release of Excel services: No Excel object model Extensibility not supported No VBA / Excel Macros No add-ins... But 

25. Excel Services Management - Configuration Security Authentication Impersonation or Process account. Default = Impersonation. Communication Connection encryption (None, All). Default = None. Load Balancing Schemes Workbook URL (Default) Round Robin Local Retry Interval (secs) Valid values: from 5 through 2073600 (24 days). Default = 30. Session Management Maximum sessions per user Valid values: -1 (no limit); any positive integer. Default = 25. Memory Utilization Maximum Private Bytes Valid values: -1 (the limit is set to 50% of physical memory on the machine); any positive integer. Default = -1

26. Excel Services Management - Configuration Workbook Cache Location The local file system location of the workbook file cache. No value indicates that a subdirectory in the system temporary directory is the location of the workbook file cache. Maximum Size (MB) Valid values: -1 (no limit); any positive integer. Default = 40960. Includes the maximum disk space that can be allocated for recently used files that are not open. Caching of Unused Files Caching Enabled/Disabled. Default = Enabled. External Data Query Timeout (secs) Valid values: -1 (no enforced timeout, but workbook timeouts still respected); from 0 through 2073600 (24 days). Default = 30. Connection Lifetime Valid values: -1 (never recycle); from 0 through 2073600 (24 days). Default = 1800. Unattended Service Account

27. Excel Services Management List of Excel 12 workbook file locations that you consider trustworthy Requests to open files that are not stored in one of the trusted locations are denied Location Address Location Type Windows SharePoint Services UNC HTTP Trust Children (Trust child libraries or directories) Children trusted (Enabled/Disabled) Description Session Management As per Configuration Workbook Properties Maximum size (in MB) of a workbook that can be opened by Excel Calculation Services Valid values: from 1 through 2000. Default = 10. Maximum Chart Size (MB) Valid values: any positive integer. Default = 1.

28. Excel Services Management Calculation Behaviour Volatile Function Cache Lifetime Maximum time (secs) that a computed value for a volatile function is cached Automatic calculations Manual calculations Valid values: -1 (calculate once per session); 0 (always calculated); from 1 through 2073600 (24 days). Default = 300. Workbook Calculation Mode All of the settings except the File setting override the workbook settings Valid values: File/ManualAutomatic/Automatic except data tables. Default = File. External Data Allow External Data None/DCL/DCL and embedded. Default = None. Warn on Refresh Enabled/Disabled. Default = Enabled. Stop When Refresh on Open Fails File cannot be refreshed while it is opening and the user does not have an Open user right to the file. Default = Enabled. External Data Cache Lifetime (secs) Automatic refresh (periodic / on-open) Manual refresh Valid values: -1 (never refresh after first query); from 0 through 2073600 (24 days). Default = 300. Maximum Concurrent Queries Per Session Valid values: any positive integer. Default = 5.

29. Key Take-Aways New Excel services: scalable, performant, and robust Excel spreadsheet calculation on the server Excel Web services enable you to Create solutions using Excel authored business logic Automate creation and update of server spreadsheets Protect the IP in spreadsheets Excel services also enable Browser access to interactive spreadsheets Spreadsheet lockdown and control “BI Portals” with Excel based dashboards

30. © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

31. Server-based SpreadsheetsPublish Spreadsheets to the Server to Share Securely Define parameter cells that can be changed on the server Control what is visible on the server Saves regular Excel “12” files Confidential – Microsoft Corporation

32. Server-based SpreadsheetsView and interact with spreadsheets in the browser Excellent visual fidelity including all new conditional formatting 100% HTML and script no client components Server side charting Confidential – Microsoft Corporation

33. Server-based SpreadsheetsIncorporate spreadsheet components in BI dashboards Display specific spreadsheet ranges or charts Unified filtering across all parts on the dashboard 10+ out-of-the-box filter parts Confidential – Microsoft Corporation

34. Server-based SpreadsheetsBuild on SharePoint content management features Search for reports, spreadsheets and dashboards Personalized and targeted content delivery Out-of-the-box template Confidential – Microsoft Corporation