SharePoint 2010 Business Intelligence

1. SharePoint 2010 Business Intelligence Module 8: Excel Services

2. Overview • Excel Services

3. Lesson: Excel Services • Excel Services Overview • 2010 Changes • Excel Services Trusted Locations • Shared Data Connections • User Defined Functions • Excel Services Is Threating • Excel Services Web Parts • Excel Services Object Model/Web Service • Excel Services Performance

4. Excel Services Overview • Enables you to load, calculate, and display Excel workbooks in a browser and BI web parts • Made of several components: • Web Front End (Web Parts) • Application Service • Web Service • Zero foot print client • Do Not Need Office! • No ActiveX controls to install! • JavaScript and DHTML only! • Support for IE, Firefox, Safari! • Only works with Excel 2007+ documents! • Excel Services is part of Enterprise SharePoint SKU

5. Excel Services 2010 Changes • Load any spreadsheet rather than complete refusal • Unsupported features simply won’t do anything but the spreadsheet will attempt to load • Write back interactive mode • Users can type into the web part to change the spreadsheet (no changes are made to source file) • More Ajax-based interface • Less post backs • Improvements around programmability • Web services, JS Object Model and REST API

6. Excel 2010 Changes • Excel 2010 introduces some powerful features • More features for the BI power users (edit MDX, What-if analysis/writeback) • Sparklines • Search in Filters, Slicers, Conditional Formatting • Enhance Show Value As • Pivot Charts (similar to Pivot Tables) • Office Web Apps (real time, multi-user editing) • Separate product from Excel and Excel Services • Includes PowerPoint broadcast feature

7. Excel Services Trusted Locations • Excel Trusted Locations specify where “Safe” excel files reside • Prevent the sending of large Excel files and storing in Exchange/Lotus Notes • Bad things can happen in Excel files • Excessive data queries • Processor intensive calculations • Intense memory usage • Network usage with large queries

8. Excel Services Evolution • Be careful, Excel Services can cause you to slid down a slippery slope! • It will force you to review all your business analyst Excel files for “Safeness” • You may find that reports are being run against production and that you need to build a DataMart/DataWarehouse! • In the end, you may eliminate Excel files completely for a total reporting solution like Report Services!

9. Excel Services Is Threating • Business Analyst don’t always design their reports and spreadsheets the optimal way • You will have to review every spreadsheet that is destined for Excel Services • A majority will not be able to be posted in their current state • That means rewriting and re-designing a majority of them! • Some will just disappear and give way to Reporting Services .rdl files

10. Shared Data Connections • Excel files can have embedded DataSource connections • These can be stored centrally on the SharePoint server to facilitate easy management of the connections • Authentication to Windows when data sources reside on multiple servers will require Kerberos! • Need to ensure that credentials are securely transmitted across the network • Unattended account allows a “None” and “SSO” type to connect using that account • Authentication Communications: • Trusted subsystem (farm) – uses Excel Services account • Delegation (single server) – uses current user to retrieve data, requires Kerberos on different servers

11. User Defined Functions • Advanced .NET methods can be written and called from Excel • These must be loaded to SharePoint in order for them to be trusted and executed in the Excel files • Excel Services does NOT support Macros! • Must wrap any functionality into a UDF

12. Excel Services Web Parts • Web Parts that render an Excel file • Allows you to Edit in 2010, but it does not change the source file • Can display “Named” items like ranges and charts • Web Part Filters can be “connected” to filter data inside the Excel file • Parameters can be used to “update” cells in the spreadsheets

13. Programming Excel Services • Excel Services offers a robust object model to manipulate spreadsheets • Update cells • Refresh the data and calculations • Excel Services Web Services allow any platform to update an excel file • Can be used to Edit, utilize parameters, send charts and create snapshots of Excel Spreadsheets • REST • No Coding, ATOM, Images and HTML supported, JSON • Session-less

14. Excel Services Performance • Fully load balanced application • Can balance the calculations and sessions across multiple SharePoint servers • Caching employed across multiple levels • Objects that span multiple clients with similar permission levels are cached! • Permissions are cached

15. Demonstration: Excel Services • Let’s look at: • Trusted Locations • Excel Web Parts

16. Lab 1: Excel Services • Complete the lab exercises: • Create/Upload an Excel File • Add a Trusted Location • Configure Excel Web Parts

17. Lab 2: Excel & MDX • Complete the lab exercises: • Use MDX Features of Excel 2010

18. Lab 3: MDX • Complete the lab exercises: • Use Excel 2010 to work with MDX queries

19. Lab 4: Sparklines • Complete the lab exercises: • Use the new Sparklines feature of Excel 2010

20. Lab 5: Filters & Excel Services • Complete the lab exercises: • Use Filters with Excel Services

21. Lab 6: Excel REST-ful services • Complete the lab exercises: • Learn to use Excel REST-ful service interfaces

22. Review • Your instructor will ask a series of questions on this module

23. Summary • Continue the movement away from Email! • Excel Services allows the centralization of your excel files and the rendering of those files as part of a business intelligence solution • Prevent “bad” excel files from being uploaded by using trusted locations