SharePoint 2010 Business Intelligence

1. BUSINESS INTELLIGENCE GROWS UP SharePoint 2010 Business Intelligence

2. What is Business Intelligence? • Any information that pertains to the history, current status or future projections of a business organization • Systems that provide directed background data and reporting tools to support and improvethe decision-making process

3. The Three Stages of Business Intelligence • Organizing, cleansing and collecting data • Delivering the data in a consistent and appropriate, meaningful and easy-to-useformat • Using the data to make effective decisions

4. Stage 1: Organizing Data OLAP (Online Analytical Processing)Precalculates and stores aggregates in SSAS databases • Use Business Intelligence Studio (BIDS) to create SSAS databases • Cubes: The basic unit of storage and analysis in Analysis Services • Dimensions: a category for analyzing business data, ex: States,Cities • Measures: column in a table that you'd like to analyze, ex: sales

5. Stage 2: Delivering Data • A team effort – need at least business analyst with a good knowledge of data warehouse • Design of dashboards critical – consult a web designer if possible • Establish governance rules upfront – only stored procedures, all connections in libraries, etc. • Enlist business power users to manage rights of BI objects • Test user acceptance at draft stage

6. The Business Intelligence Market By 2014 the total sales of BI products is expected to hit $12bn

7. Microsoft SharePoint 2010The business collaboration platform for the Enterprise and the Web Sites Connect and Empower People Composites Communities Cut Costs with a Unified Infrastructure Insights Content Rapidly Respond to Business Needs Search

8. SharePoint 2010 BI Pros • Less expensive than other solutions—free with Enterprise version of SharePoint 2010 • Completely customizable and extensible with .NET and Silverlight • Good support from Microsoft premier support • Brings together your entire environment • Offers users more “self-service” options • Is “embedded” with SharePoint rather than separate

9. SharePoint 2010 BI Cons • More work to get started—have to build or purchase a risk analysis calculation engine, for instance, while other products offer this OTB • SharePoint 2010 setup and admin requires senior IT support • Requires SharePoint Enterprise license • Need to be a entirely Microsoft shop to reap the full benefits • New kid on the block

10. When to Use SharePoint BI? • When your organization is looking for a long term solution • When you are an all-Microsoft shop • When flexibility and extensibility are paramount concerns • When your users make extensive use of Excel for modeling critical data • When delivering data worldwide is important

11. What Comprises SharePoint BI? The following services are the primary SharePoint Business Intelligence services • Excel Services • PowerPivot for Excel 2010 • Performance Point • SQL Server Reporting Services • Business Connectivity Services • Visio Graphic Services

12. Supporting BI Services The following services play a supporting role in SharePoint Business Intelligence services • Enterprise Search • Secure Store Service • Metadata Service

13. Business Intelligence Design Apps • Business Intelligence Studio – for report and cube creation • Report Builder – for report creation by business users • Dashboard Designer – the client part of Performance Point • SharePoint Designer 2010 – for Business Connectivity connections, etc. • Visio 2010 – for creating data-based diagrams

14. SharePoint BI Brings it All Together Functional ViewPoint

15. SharePoint BI Brings It All Together Structural Viewpoint

16. SharePoint 2010 BI Architecture Software Viewpoint

17. BI Deployed as Service Apps • No longer a separate SSP website (managed via Central Administration) • Pick and choose the services you need • Web applications can consume services on an individual basis • Deploy multiple instances of the same SA • Reuse SA instances across multiple Web applications in farm

18. Service Model – Architectural View • Service: Actual program (binaries) deployed to servers in farm • Service Machine Instance: Actual instance of the running service binaries on a server Service Application • Service Application:Configuration of the service in a farm Service Proxy Service Proxy • Service Application Proxy:Reference to the Service Application • Service Consumer:Bits that utilize the service’s logic Web Part, Pages (Service Consumer) *.SVC’s, PowerShell Cmdlets (Service Consumer)

19. What is a Service Application? • Provides data or computing resources • Exposes administrative interfaces • Uses resources like application pools • Runs one or more instances • Contains the configured user accounts the service will run as • Contains connection details for the configured databases utilized by the service

20. Service Application Proxy • A proxy is a virtual entity that connects Web applications to service applications • Consumers interact with the service via proxies • Installed on the WFE servers • Associated with a service application • Knows how to connect to the service on the app servers via WCF services • Talks to a round-robin load balancer when the service is installed on multiple app servers

21. Service Application Proxy Groups • A proxy group is a group of Service Application proxies that are selected for a web application • By default, all SA proxies are included in the default proxy group • When you create a web app you can: • select the default proxy group • create a custom proxy group by selecting which SA proxies should be included

22. SharePoint 2010 Authentication

23. Excel Services Architecture W E B F R O N T E N D Excel Web Access Excel Web Services REST API JSON Excel Proxy (connector-part of Excel Service Application) A P P L I C A T I O N S E R V E R User-Defined Functions Excel Calculation Service SharePoint Content Database External Data Sources XLSX / b / m Oracle OLAP

24. Excel Services Limitations • Supported and Unsupported Featureshttp://msdn.microsoft.com/en-us/library/ff595319.aspx • Differences between using a workbook in Excel and Excel Serviceshttp://office.microsoft.com/en-us/excel-help/differences-between-using-a-workbook-in-excel-and-excel-services-HA010021716.aspx

25. PPS Architecture

26. Dashboard Designer Download

27. Visio Services, At a Glance“Data-Driven Diagrams in SharePoint” 1 hour later… • Top Features: • Diagrams all live in SharePoint • Diagrams viewable in the browser • Diagrams created once only • Diagrams containing data graphics are refreshable • Diagrams give data context and improve insights into the state of a system Data Source

28. FAST BI Indexing Connector • Add-in to FAST Search Server for SharePoint • Query and Crawl XLSX/XSLM and RDL reports • Dedicated Reports tab • Use filters to refine and narrow results • BI Search identifies Table, Pivot Tables, and Chart Data • Crawls data not visible in report using data connection

29. Detailed Report Information Location of Search Term Find Similar Reports Report Preview

30. Do Some Tutorials • Create a Performance Point Sales Dashboardhttp://office2010.microsoft.com/en-us/dashboard-designer-help/up-to-speed-with-performancepoint-dashboard-designer-HA101818158.aspx • BI Scenario: Reports and Subscriptions Scenario http://technet.microsoft.com/en-us/bi/ff769487.aspx • Create Your First PowerPivot Workbookhttp://technet.microsoft.com/en-us/library/ee835510.aspx • SQL Server Analysis Services Tutorialhttp://technet.microsoft.com/en-us/library/ms170208.aspx

31. Create a Sales Dashboard in PPS http://technet.microsoft.com/en-us/bi/ff643005.aspx

32. SharePoint BI Resources http://blog.sharepointsalvation.com