SharePoint Business Intelligence

1. SharePoint Business Intelligence

2. Agenda • What is Business Intelligence? • Microsoft’s Business Intelligence Offering • SQL Server 2005 • SQL Server Reporting Services (SSRS) • SQL Server Analysis Services (SSAS) • SharePoint 2007 • Excel Services • KPIs • Business Data Catalog (BDC) • Report Center • Dashboards • PerformancePoint Server 2007 • Q&A

3. “A broad category of applications and technologies for gathering, storing, analyzing, sharing and providing access to data to help enterprise users make better business decisions.” – Gartner What is Business Intelligence?

4. Ad hoc Queries Dashboards Business Strategy Metrics KPI’s Analytics Operational Reporting Data Warehouse What is Business Intelligence?

5. Call Center CRM Marketing Campaign Mgmt Internet Financial/ Accounting Inventory Procure-ment HR Why is Business Intelligence Growing in Importance? • The amount of corporate data is doubling every 2-3 years • Barriers of entry (costs/technology) are being removed • Continued pressure on businesses to find efficiencies and new market opportunities, client expectations • More disparate data sources than ever before Intelligence is data with relevance and context

6. Key Business Drivers Behind BI Growth • Allow business users the ability to query and write reports • To simplify reporting across multiple transaction systems • To store historical data longer than you can/would in transaction system

7. Business Intelligence Statistics • BI Use by Small and Mid Market Companies • 48% - Using • 10% - Planning to implement solution in 2007. • 40% - Not using • Don't know 2% • Source: Forrester Research Inc., 2006 survey

8. Microsoft Business IntelligenceVision and Strategy Microsoft’s BI Strategy Improving organizations by providing business insights to all employees leading to better, faster, more relevant decisions • Major investment area • Leverage familiar tools such as the Office System and SharePoint • Built on top of SQL Server 2005 • Performance Management is a key growth area

9. Types of Business Intelligence

10. Microsoft Tools for Business Intelligence

11. Business Scorecard Manager 2005 PerformancePoint Server 2007 ProClarity Analytics 6 End User Applications Office SharePoint Server 2007 Office Excel 2007 SQL Server 2005 Reporting Services SQL Server 2005 Analysis Services SQL Server 2005 Integration Services BI Platform SQL Server 2005

12. Data Warehousing 101

13. What is a Data Warehouse? • A Data Warehouse is the main repository of an organization's historical data, its corporate memory. It contains the raw material of a company's business intelligence system. • It’s designed to store historical data • It brings information together from multiple source systems into a single data store. • The data is stored using special techniques that are optimized for analysis and reporting.

14. Extraction, Transformation & Loading (ETL) • The process of gathering data from the production systems, cleansing it, validating it and moving it into the Data Warehouse. This process can be considered part of the Data Warehouse Infrastructure.

15. Key Terms • Cubes: An enhanced storage mechanism that allows an end user to look at data from multiple perspectives. Think about pivot tables. • Facts: Numeric data we are evaluating (cost, count, etc.) • Dimensions: Attributes like Geography, Marital Status, SIC Codes, Date/Time, etc. that will be used to summarize data.

16. Reports Cubes Data Elements Ad hoc Queries Analytics/ Scorecards Metadata Facts/Dimension Data Warehouse Reporting Platform Business Intelligence Portal Property Management System Corporate Accounting System Analysis Platform CRM/ Contacts System ETL Process: Extract, Transform, & Load Human Resource /Payroll System End Users Computing Device Performance Management Platform Work Order System Forecasting/ Planning System Excel, PDF, DOC

17. Microsoft SQL Server 2005

18. 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

19. Extract Transform and Load (ETL) Updated DTS environment More trustworthy and reliable Development environment Many out-of-the-box transforms Extensible Custom tasks Custom enumerations Custom transformations Custom data sources SQL Server Integration Services (SSIS)

20. Business Perspective Data in different systems needs to be connected and related Reporting often requires looking beyond more than one system IT Perspective Provides the glue to connect systems SQL Server Integration Services (SSIS)

21. OLAP engine / Cubes Advanced business intelligence KPI/Perspectives Custom/limited aggregations and semi-additive measures Web services Data mining in the platform Integrated developer tools SQL Server Analysis Services (SSAS)

22. Business Perspective Multi-dimensional views of information Improved speed Slicing and dicing IT Perspective Stores predetermined aggregations Reduces reporting load on transactional systems SQL Server Analysis Services (SSAS)

23. Reporting solution Report authoring, management, delivery Visual Studio .NET development environment Report Builder Integrates with SharePoint Reporting Services

24. Reporting Services Visual Studio .NET For developers Leverages .NET Report Builder End user reporting Ad-hoc Two Methods of Report Design

25. Demo

26. Microsoft Office SharePoint Server

27. Microsoft Office SharePoint Server 2007 (MOSS) Major component of Office System 2007 Web platform 6 major components Business Intelligence Business Process Content Management Search Portal Collaboration

28. Single Infrastructure for Intranet, Internet, and Extranet Portals SharePoint as a Platform • Single infrastructure • Multiple scenarios

29. Collaboration DiscussionsCalendarsE-MailPresenceProject MgtOffline Content Mgt AuthoringApprovalWeb PublishingPolicy & AuditingRights MgtRetentionMulti-LingualStaging Portal MySitesTargetingPeople Finding Social NetworkingPrivacyProfiles Site Directory Search IndexingRelevanceMetadataAlertsCustomizable UX BPM Rich\Web FormsBiz Data CatalogData in ListsLOB ActionsSingle Sign-OnBizTalk Integ. BI Excel ServicesReport Center KPIsDashboardsSQL RS\AS Integ. Data Con. Library Core Services Management DelegationProvisioningMonitoringStaging Security Rights\RolesPluggable Auth.Per ItemRights Trimming Storage RepositoryMetadataVersioningBackup Topology Config. Mgmt.Farm ServicesFeature PolicyExtranet Site Model RenderingTemplatesNavigationVisual Blueprint APIs Fields\Forms OM and SOAPEventsDeployment Web Parts | Personalization | Master Pages | Provider Framework (Navigation, Security…) Database services Search services Workflow services Operating System Services

30. Office 2007 Business Intelligence Investments • Excel client • Excel Services • Dashboards • Key Performance Indicators (KPI’s) • Filter Web Parts • Report Center/Report Library • Business Data Catalog (BDC)

31. Excel 2007 • Richer formatting • Improved pivot • Data connectivity

32. Analysis Services Data Mining Add-Ins for Office 2007 • Excel 2007 data mining add-in • Connect to OLAP environment • Perform data mining right in Excel • Visio data mining templates • Decision Tree

33. Demo

34. 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 proliferation of Excel files

35. 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

36. Example – Publishing from Excel to Excel Services • End user creates spreadsheet in Excel 2007 • Publishes it to SharePoint Excel Services • Configures the view of the spreadsheet in SharePoint

37. Example – Create spreadsheet in Excel 2007

38. Example – Publish to Excel Services

39. Example – Connect Excel Web Access Web Part Only shows published sheet

40. Demo

41. The Report Center • 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

42. SSRS Reports in SharePoint • Report Library • SQL 2005 Service Pack 2 • Report viewer web part • Publish Reports to SharePoint • Leverage features of SharePoint • Versioning • Workflow • Etc.

43. Demo

44. Key Performance Indicator (KPI) List • List template • Graphically displays KPI’s • Use to track metrics • Data from multiple sources • SharePoint Lists • Excel Services • SQL Analysis Services 2005 • Manually entered

45. KPI Web Part • Displays KPI’s in SharePoint List  Detail 

46. Demo

47. Dashboard Pages in the Report Center • SharePoint page • Create individual dashboards • Contains a number of different parts • Content Editor • Contact Details • Filter Actions • Excel Web Access • KPI • Summary Link

48. Filtering Data in SharePoint • Used to filter data • Multiple filter types • Authored List • Business Data Catalog • Current User • Date • Query String (URL) • SharePoint List • SSAS • Text

49. Authored List Filter Part • Allows user to enter a list of values • Manually entered into configuration • Good for hard coded situations

50. Business Data Catalog Filter Part • Leverages the Business Data Catalog (BDC) • Allows user to search by entity in BDC • Good for filters requiring relational data