1 / 59

SharePoint Business Intelligence

SharePoint Business Intelligence. Randy Williams, MVP MOSS Synergy Corporate Technologies rwilliams@synergyonline.com. Who is Synergy?. Global company with offices in USA, UK, Australia and Singapore Premier SharePoint consulting Architecture, Implementation, Development, Design

linh
Download Presentation

SharePoint Business Intelligence

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SharePoint Business Intelligence Randy Williams, MVP MOSS Synergy Corporate Technologies rwilliams@synergyonline.com

  2. Who is Synergy? • Global company with offices in USA, UK, Australia and Singapore • Premier SharePoint consulting • Architecture, Implementation, Development, Design • SharePoint Training • Custom-crafted curriculum • Three, one-week technical (Master) courses • Three, one-day end-user courses • Offered through COMAT in Singapore • http://www.synergyonline.com/sg

  3. Agenda • What is Business Intelligence? • Microsoft’s Business Intelligence Offering • SQL Server 2005/2008 • 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

  4. “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?

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

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

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

  8. Business Intelligence Statistics • Are you currently deploying BI in your organization? Source: WiseAnalytics, 2008 survey

  9. 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 &2008 • Performance Management is a key growth area

  10. Types of Business Intelligence

  11. Microsoft Tools for Business Intelligence

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

  13. Data Warehousing Basics

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

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

  16. 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, Date/Time, etc. that will be used to summarize data.

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

  18. Microsoft SQL Server

  19. Note: I changed builds and added logos – change colors so we get 3 distinct colors Integrate Analyze Report SQL Server 2005/08 – 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

  20. 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)

  21. 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 back ends SQL Server Integration Services (SSIS)

  22. 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)

  23. 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)

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

  25. Reporting Services Visual Studio .NET For developers Leverages .NET Report Builder End user reporting Ad-hoc PerformancePoint Best for OLAP Methods of Report Design

  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. SharePoint Demo

  31. SharePoint 2007 Business Intelligence Features • Excel Services • Dashboards • Key Performance Indicators (KPI’s) • Filter Web Parts • Report Center/Report Library • Business Data Catalog (BDC)

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

  33. Excel Services – What is it? • New server technology in MOSS 2007 Enterprise • 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

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

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

  36. Example – Create spreadsheet in Excel 2007

  37. Example – Publish to Excel Services

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

  39. Excel Services Demo

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

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

  42. Reporting Services Demo

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

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

  45. KPI Demo

  46. Dashboard Pages in the Report Center • SharePoint page • Create individual dashboards • Contains a number of different web parts • Content Editor • Filter Actions • Excel Web Access • KPI • Reporting Services • Others

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

  48. Business Data Catalog • Expose Data to SharePoint • Custom Line of Business Data • Database or Web Services • Can be used as metadata • Automatically provides lookup functions

  49. SharePoint Business Data Catalog (BDC) Expose Data to SharePoint Custom Line of Business Data Database or Web Services Can be used as metadata Search Data

  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

More Related