CLB205 - Creating Self-Service Analytic BI Applications with Microsoft SharePoint 2010

1. CLB205 - Creating Self-Service Analytic BI Applications with Microsoft SharePoint 2010 Kevin Harmer Principal Consultant, VTS Thoughtcorp kevin.harmer@thoughtcorp.com

2. Agenda • Overview of SharePoint and Self Service BI • Chart / Status List Web Parts • Excel Services • Excel PowerPivot Services • PerformancePoint Services • Visio Services • Reporting Services Integration • Additional Resources • Questions

3. SharePoint 2010 Pillars Enterprise Content Management From Web Content to Business Intelligence Web Content Records Management Enterprise Search Intranet Content Workflow Data Insight

4. SharePoint 2010 - Data Insight (Microsoft BI) PerformancePoint Reporting Services Data Insight Excel Services Visio Services

5. Microsoft BI – Self Service Architecture

6. Some Keys to Successful Self-Serve BI /w SharePoint 2010 • Gain a robust understanding of the entire suite of SharePoint 2010 BI tools • Implement SharePoint 2010 BI services correctly to support a stable, scalable, flexible BI environment • Ensure the right data is available to support BI requirements • Successful data flow relies on good plumbing • Align BI requirements to the right SharePoint 2010 BI tool • Empower analysts to leverage SharePoint BI tools directly • Training, knowledge transfer • Ensure analysts fully leverage SharePoint as their BI repository and BI presentation portal

7. Business Intelligence Center • Site template which pre-provisions BI libraries and provides “getting started” guidance • SharePoint BI can be implemented in any type of SharePoint site, but required libraries will have to be manually added if the site is not a Business Intelligence Center • Before deploying a Business Intelligence Center site, be sure to configure SharePoint BI services • Otherwise, BI content deployed to the site will not render • Pre-provisioned libraries: • Data Connections library • PerformancePoint Content List • Dashboards library • Documents library

8. Business Intelligence Center

9. Chart and Status List Web Parts • Enable the addition of charts and scorecards to SharePoint pages, with no programming • Chart Web Parts have a good selection of chart types available, and support the following datasources: • Other Web Parts, SharePoint Lists, Business Data Catalog, and Excel Services • Status List Web Parts enable the assignment of a status indicator (a basic KPI), using the following datasources: • SharePoint Lists, Excel Services, Analysis Services, or Fixed Values • Both Web Parts can be filtered using a Filter Web Part • Use Case: Quick addition of simple BI chart/scorecard views to SharePoint web part pages

10. Chart and Status List Web Parts

11. Excel Services • Enables server-side calculation and browser-based rendering of Excel workbooks • Workbook data can be internal to the workbook or external to the workbook • External workbook data is usually accessed through a PivotTable or PivotChart • Through the client browser, users can refresh, filter, and drill workbook views • To perform additional analytics, users seamlessly open the workbook locally from the Excel Services view • Use Case: Team sharing of Excel views to support organizational analytics

12. SharePoint Excel Services

13. PowerPivot Services • PowerPivot is an Excel 2010 add-in, which enables self-service data integration by analysts • With PowerPivot, external data is imported into an Excel workbook , and is accessed locally using Excel views (PivotTables, …) • SharePoint PowerPivot Services enables the publishing, hosting, and refreshing of Excel workbooks with PowerPivot content • PowerPivot Services handles the workbook data • Excel Services handles the workbook views • PowerPivot data can be accessed as a cube source by other cube-compliant BI tools (i.e. PerformancePoint) • Use Case: Tactical data integration of data that is not already integrated in the enterprise

14. SharePoint PowerPivot Services

15. PerformancePoint Services • Enables organizational scorecards , dashboards, and web-based analytics of corporate performance • PerformancePoint is the most robust “zero-footprint” Microsoft BI analytical tool • PerformancePoint supports Cube, Table, SharePoint List , Excel, and PowerPivot datasources • Create flexible scorecards and KPIs, supporting balanced scorecard analysis of the organization • Create grid/chart analytic views for analysis • Integrate other SharePoint views (web page, excel service, reporting service, …) together in a consolidated dashboard • Use Case: Robust zero-footprint analytics, unified dashboard views, and organizational performance management

16. SharePoint PerformancePoint Services

17. Visio Services • Enables browser-based rendering of Visio diagrams • Stand-alone or data connected Visio diagrams can be published to SharePoint, rendered by SharePoint, and delivered to users via their browser • Data connected Visio diagrams support rich, custom visualization of status and performance • Supports table datasources for custom diagrams and cube datasources for pivot diagrams • Use Case: Custom graphical visualization of enterprise status and enterprise performance

18. SharePoint Visio Services

19. Reporting Services • SQL Server Reporting Services can be integrated with SharePoint, so users only interact with SharePoint when creating or consuming reporting service reports • Reporting Services enables pixel-perfect reports, with filtering, paging, and export user functionality • Analysts use Report Builder 3.0 to generate reports • Developers use BIDS (Visual Studio) to generate reports • Reports are published to SharePoint and rendered on demand via the user browser, or by schedule • Use Case: Robust enterprise reporting with powerful visualization options, where users expect a predictable, repeatable report layout

20. SharePoint Integrated Mode for Reporting Services

21. The Custom Web Part Option … • Occasionally, BI tools will not address a BI visualization requirement involving a very specific, controlled layout • To address this need, SharePoint partners provide Visual Studio 2010 frameworks, enabling custom web part development with minimal coding • Telerik, ComponentArt, … • Use Case: Highly customized dashboards and BI views, to address advanced, specific BI dashboard and BI view requirements

22. Summary • SharePoint 2010 provides a powerful suite of BI tools that enable self-service BI by power users and analysts • Each SharePoint 2010 BI tool has unique advantages that align to specific performance management use cases, empowering analysts to leverage the right tool for a given corporate BI goal • Chart / Status List Web Parts – quick web part analysis • Excel Services – corporate analytics • PowerPivot Services – self-service data integration • PerformancePoint Services – enterprise scorecards / dashboards • Visio Services – graphical analysis • Reporting Services – enterprise reporting • Microsoft BI tools are accessible, integrated, robust, and support your organizations datasources • Fully leverage your existing Microsoft software investment by practicing Microsoft BI!

23. Thoughtcorp Overview • 100 consultancy firm established in 1995 • 54% average annual growth rate over the past 5 years • Services: Architecture, Business Intelligence (BI) and System Integration/Systems Development • Industries: Telecommunications, Financial Services, Healthcare and Retail industries • Mission: Provide exceptional value and return on our Clients Information Technology investments.

24. Thoughtcorp BI Assessment Business Intelligence (BI) is about making informed decisions, faster. Today it is more important than ever to be responsive to business drivers such as changing market forces, consumer behavior, and the competitive landscape. The advantage of a BI solution from Microsoft? Leverages your investments in existing technologies, such as Microsoft® Office Excel®, Office Outlook®, and Office SharePoint® Server . Familiar to your staff. Easy to use. Integrated. Interoperable. And all part of Microsoft's ongoing commitment to provide the best and most cost-effective BI solutions. Request a free Thoughtcorp BI Roadmap Assessment Want to know what the best BI solution is for you -- or whether your current BI strategy is still optimal? You may be qualified for our one day custom workshop that will help you define or assess your organization's own BI roadmap. This four-step process includes: • STEP 1 | DEFINE - What are the real drivers of value in your organization? • STEP 2 | ALIGN -How do executive requirements align with organizational execution? • STEP 3 | LINK - How should your BI strategy link to operational objectives? • STEP 4 | STANDARDIZE - What should the definitions, rules, and calculations be? The direct result of the workshop: a customized document that outlines a formal BI recommendation for your organization—your roadmap to help drive business performance. Visit www.thoughtcorp.com to apply

25. Additional Resources • SharePoint 2010 Site • http://sharepoint.microsoft.com/en-us/Pages/default.aspx • Microsoft BI Site • http://www.microsoft.com/BI/ • Microsoft BI Blog • http://blogs.msdn.com/b/bi/

26. Related Sessions • DAT274 – A Lap Around Microsoft BI (Part 1): Oct 25, 10:30am • DAT275 – A Lap Around Microsoft BI (Part 2): Oct 25, 1:00pm • DAT297 – New Business Intelligence Functionality in SQL Server 2012: Oct 26, 10:30am • DATILL 301 – Base Metal Business Intelligence: Oct 25, 2:45pm

27. Remember To Complete Your Evaluations! You could WIN a Samsung Focus Windows Phone 7! Let us know what you liked & disliked! Remember, 1=Bad, 5=Good  Please provide comments! No purchase necessary. The contest is open to residents of Canada (excluding government employees). The Toronto Tech·Days evaluation form contest begins on October 25th, 2011 and ends on October 26th, 2011. The Vancouver Tech·Days evaluation form contest begins on November 15th, 2011 and ends on November 16th, 2011. The Montreal Tech·Days evaluation form contest begins on November 29th, 2011 and ends on November 30th, 2011. Participants can enter the contest in one of two ways: (1) complete and submit an evaluation form by the contest close date; or (2) provide contact information by the contest close date. The draw for Toronto will take place on October 31st, 2011. The draw for Vancouver will take place on November 21st, 2011. The draw for Montreal will take place on December 5th, 2011. The chances of being selected depend upon the number of eligible entries. Selected participants will be contacted by phone and/or e-mail and will be required to answer correctly a time-limited skill-testing question. There are three (3) prizes available to be won. One (1) prize will be given away for each Tech·Days event in Toronto (October 25-26 2011), Vancouver (November 15-16 2011) and Montreal (November 29-30 2011). The prize consists of a Samsung Focus Windows Phone 7 (handset only; voice and/or data plan not included) (approximate retail value of $499 CAD). The prize will be delivered to the shipping address designated by the winner within 6-8 weeks. The winner may be required to sign a declaration and release form. For full contest rules, please see a Microsoft Tech·Days representative. You can email anyadditionalcommentsdirectly to td_can@microsoft.com atany time.