Welcome to the Minnesota SharePoint User Group

1. Welcome to the Minnesota SharePoint User Group

2. Agenda • Quick Intro • Announcements and News • Business Intelligence in SharePoint 2007 • Excel Services • Dashboards • Key Performance Indicators (KPI’s) • Reports • Filters • Other Microsoft BI Solutions • Q&A

3. User Group Goal / Objectives • Develop and support a local community focused on Microsoft SharePoint Technologies • Educate user group members about SharePoint Technologies • Transfer knowledge within the community • Communicate best practices • Introduce new products / solutions

4. Introductions – MNSPUG Sponsors • Inetium (www.inetium.com) • Twin Cites based Microsoft consulting company • Microsoft Gold Certified Partner • Practice Area focused on Information Worker Technologies • New Horizons – Minnesota (www.newhorizonsmn.com) • Microsoft Gold Certified Partner • Training on many technologies • Microsoft (www.microsoft.com) • I’m guessing you’ve heard of them

5. www.sharepointmn.com • Website for user group • SharePoint resource documents • SharePoint resource websites links • RSS Feeds • Meeting Schedule • Past User Group Presentations • Watch for changes soon! V3…? www.sharepointmn.com

6. News and Announcements • Public preview release of 2007 Microsoft Office System • Server and client applications available for download (requires registration) • More info at: http://www.microsoft.com/office/preview/default.mspx • Beta Downloads  $1.50 + =

7. Upcoming Schedule • Next Meeting • September 13th 9:00 AM to 11:30 AM • Microsoft’s Bloomington Office • Topics: • Web Content Management in SharePoint 2007 • Ongoing Schedule • 2nd Wednesday of every month • 9:00 to 11:30 am • Microsoft’s Bloomington Office

8. Collaboration Portal Business Intelligence SharePoint 2007 – Key Microsoft Investment Areas • Business Intelligence • Collaboration • Search • Business Forms / Processes • Portals • Content Management Platform Services BusinessForms / Processes Workspaces, Mgmt, Security, Storage, Topology, Site Model Content Management Search

9. SQL Server 2005The Foundation

10. Analyze Drill down, exception reports, root-cause analysis, multidimensional analysis Report Detailed operational data Sharing standardized reports Monitor Dashboards, scorecards and alerts Plan Create plans, models and scenarios, which are then fed back into the monitoring layer and encoded as targets and thresholds What is Business Intelligence? “A broad category of applications and technologies for gathering, storing, analyzing, sharing andproviding accessto datato help enterprise users make better business decisions”– Gartner ?

11. Microsoft’s Business Intelligence Strategy Improving organizations by providing business insights to all employees leading to better, faster, more relevant decisions • Complete and integrated BI offering • Widespread delivery of intelligence through Microsoft Office • Enterprise grade and affordable

12. SQL Server 2005 Analysis Services (SSAS) • OLAP Engine • Advanced business intelligence • KPI/Perspectives • Measures • Dimensions • Web services • Data mining in the platform • Integrated developer tools

13. SQL Server 2005 Reporting Services (SSRS) • Introduced with SQL Server 2000 • Open, extensible enterprise reporting solution • Report authoring, management, delivery • Office System integration • Visual Studio .NET development environment • SQL Server 2005 enhancements • Integration with SSAS, SSIS, and management tools • Rich end-user reporting

14. SQL Server 2005 Integration Services (SSIS) • Extract, Transform and Load (ETL) • High performance • High scale • Rich development environment • Source control • Visual debugging of control flow and data • Extensible • Custom tasks • Custom enumerations • Custom transformations • Custom data sources

15. Note: I changed builds and added logos – change colors so we get 3 distinct colors Integrate Analyze Report SQL Server 2005 – Business Intelligence • Data acquisition from source systems and integration • Data transformation and synthesis • Data enrichment, with business logic, hierarchical views • Data discovery via data mining • Data presentation and distribution • Data access for the masses

16. Business Intelligence inSharePoint Server 2007

17. Microsoft SharePoint 2007 BI Server Investments • Excel Services • Share spreadsheets via zero-footprint browser experience • Live, interactive Excel 2007 spreadsheets in SharePoint • Only publish what you want, control versions • Use Excel models in custom applications via programmatic access • Dashboards & Web components • Common filtering (“slicing”) across web parts • KPIs, Excel workbooks, and SQL Reporting Services reports • Easy to create and personalize dashboards

18. Microsoft SharePoint 2007 BI Server Investments • Report Center Websites • Out of the box website optimized for report access and management • Consistent management of reports, spreadsheets, and data connection • Supports integration with SQL Server 2005 Reporting Services • Data Connection Libraries • Centralize storage of important data connections • Update many spreadsheets, solutions with 1 central update • Maintain 1 version of the truth

19. SharePoint 2007 Excel Services – What is it? • New server technology in MOSS 2007 • Load, calculate and display Excel Workbooks in MOSS 2007 • Thin client view in browser • Published workbooks can connect to external data • Designed to cut down on unnecessary proliferation of Excel files

20. Excel Services – How does it work? • Web rendering • Zero-footprint • Interactive View and Interact Publish Spreadsheets Browser Excel 2007 • Design and author • BI Abilities • Visualization Export/Snapshot into Excel Programmatic Access Excel 2007 MOSS 2007 • Open snapshots • Open full spreadsheet Protect, Share & Reuse Spreadsheets • Spreadsheets stored in document libraries • Server calculation and rendering • External data retrieval and caching Customapplications • Set & get spreadsheet values • Perform calculations • Retrieve full workbook file

21. Excel Services 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 • Single box or multi-tier • Independent scale-out

22. Business Intelligence Pivot Tables KPIs from SQL Graphs & Charts Excel ServicesZero-footprint, interactive, browser access to spreadsheets Table Formatting Microsoft Confidential – Presentation under NDA

23. Basic Example – Publishing a Spreadsheet • End user creates spreadsheet • Wants to share it with others online • Publishes it to SharePoint • Configures the view of the spreadsheet

24. Basic Example – Publishing a Spreadsheet – Step 1 • Create Spreadsheet in Excel 2007

25. Basic Example – Publishing a Spreadsheet – Step 2 • Step 2 – Publish to Excel Services in MOSS 2007

26. Basic Example – Publishing a Spreadsheet – Step 3 • Step 3 – Add Excel Web Access web part to display published workbook Only shows published sheet

27. DemoPublishing an Excel 2007 file using Excel Services

28. Using Parameters in Excel Services • Allows publication of parameters in spreadsheet • Publisher defines what to expose to end users for data • Displays parameter pane • All other cells are read only

29. DemoSetting up Parameters using Excel Services

30. Excel Services for the Administrator

31. Excel Services 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 • Single box or multi-tier • Independent scale-out

32. Configuring Excel Services • Performed by the server administrator • Activate the Excel Services Feature • Edit Excel Services settings • Configure Trusted File locations

33. Activating the Excel Services Feature • Turn on or off in the Operations Page in the Global Configuration section

34. Starting the Excel Services Service • Start the Excel Calculation Services Service

35. Excel Services Configuration • Shared Service > Excel Services Settings • Edit Excel Services Settings • Trusted File Locations • Trusted Data Connection Libraries • Trusted Data Providers • User Defined Function Assemblies

36. Excel Services Settings • Configurable by administrator • Security • Load Balancing • Session Management • Memory Utilization • Workbook Cache • External Data

37. Adding a Trusted File Location • Configurable by administrator • Location • Session Management • Workbook Properties • Calculation Behavior • External Data • User Defined Functions

38. Using More Excel Services Features

39. Example – Spreadsheet w/ External Data • End user creates spreadsheet • Connects to external data (i.e. database, cube etc.) • Only wants to show a piece of the file

40. Spreadsheet w/ External Data – Step 1 • Create Office Data Connection (ODC) • Upload to SharePoint Data Connection Library

41. Spreadsheet w/ External Data – Step 2 • Connect Excel file to ODC created in step 1 • Build spreadsheet as desired (i.e. insert charts, pivot tables etc.)

42. Spreadsheet w/ External Data – Step 3 • Name chart • Publish to Excel Services  Select only the chart

43. Spreadsheet w/ External Data – Step 4 • Add and configure the Excel Web Access (EWA) web part

44. DemoConnecting to External data with Excel Services

45. The Report Center

46. The Report Center – What is it? • SharePoint 2007 Site Template • Repository for reports • Includes many features • Sample KPI’s • Dashboard Pages • Excel Web Access • Report Library • Report Calendar • Filter web parts

47. DemoCreating a Report Center Site

48. Key Performance Indicator (KPI) List • List template • Graphically displays KPI’s • Use to track metrics • Data can come from multiple sources • SharePoint Lists • Excel Services • SQL Analysis Services 2005 • Manually entered • Only show problems filter

49. KPI Web Parts • Key Performance Indicators • Displays KPI’s from a KPI List on the site • Can change icon • KPI Details • Displays a single KPI

50. DemoCreating a New KPI