Enhancing Business Intelligence with Excel Services and KPIs

Explore the world of Business Intelligence through Excel Services, KPIs, and Report Centers. Learn about deployment considerations, user adoption tactics, and solutions for various work styles. Discover the integrated BI offering and driving adoption strategies in familiar software environments.

• Business Intelligence
• Excel Services
• KPIs
• Report Center
• User Adoption

jburg Follow

Uploaded on Sep 19, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

2. DEV14 Building Business Dashboards: Excel Services, KPIs and Report Centers Grant Paisley MVP SQL Server Angry Koala grant@angrykoala.com.au http://sqlserver.org.au Darwin Schweitzer Enterprise Technology Strategist Darwin.Schweitzer@microsoft.com

3. Agenda Business Intelligence Overview - Self-directed People Ready BI Demo - Excel Services, KPIs, Filter Web Parts, Report Center Deployment Considerations - Solutions for different WorkStyles - Using SharePoint to Drive Adoption Q&A

4. Who uses Business Intelligence? Analysts Execs Operations Middle Managers

5. Our Integrated BI Offering DELIVERY COLLABORATION CONTENT MANAGEMENT SEARCH SharePoint Server Excel Workbooks Analytic Views Reports Dashboards Scorecards Plans END USER TOOLS & PERFORMANCE MANAGEMENT APPS Excel PerformancePoint Server BI PLATFORM SQL Server Reporting Services SQL Server Analysis Services SQL Server DBMS SQL Server Integration Services

6. Server investments SharePoint is BI Portal and Report Center Excel Services Dashboards & Web Parts Report Center Reporting Services ProClarity Business Scorecard Manager

7. Demo Excel Services, KPIs, Filter Web Parts, Report Center Grant Paisley MVP SQL Server Angry Koala grant@angrykoala.com.au Darwin Schweitzer Enterprise Technology Strategist Darwin.Schweitzer@microsoft.com

8. Solutions for Different WorkStyles Customized delivery of information - PM dashboards & scorecards - Embedded BI in applications BI self-service - Enterprise-wide self-service reporting - Parameterized & Operational Reporting Ad hoc Reporting and Analysis - End-user analytics and reporting Business User Innovation Incubation - Flexible Empowerment with Responsibility

9. Driving Adoption Software that is Familiar Ease of Use Competency Centers - Business Intelligence, Integration Relevance of Tools to performing job function - Information Worker - Employee self-service (role based portals) More than a BI Portal - Collaboration & Communication - Enterprise Content & Project Management Centers of Excellence Communities of Practice Communities of Interest

10. More than a BI Portal Content Management Project Management Presence Collaboration Training User Adoption

11. Links & Resources Microsoft Business Intelligence Demos and White Papers http://www.microsoft.com/bi/resources/de mos.aspx CRM Analytics Foundation & the Technical White Paper ARC04 BI with Excel Services and Reporting Services May 15th 2.25pm: Session Three http://codeplex.com/crmanalytics http://www.microsoftsharepoint.com/agend a/Pages/newdefault.aspx Darwin Schweitzer Enterprise Tech. Strategist Darwin.Schweitzer@microsoft.com Grant Paisley MVP SQL Server Angry Koala grant@angrykoala.com.au

12. Q&A Grant Paisley MVP SQL Server Angry Koala grant@angrykoala.com.au Darwin Schweitzer Enterprise Technology Strategist Darwin.Schweitzer@microsoft.com

13. Appendix Grant Paisley MVP SQL Server Angry Koala grant@angrykoala.com.au Darwin Schweitzer Enterprise Technology Strategist Darwin.Schweitzer@microsoft.com

14. Dashboards Aggregate various artifacts (workbooks, reports, scorecards) Make BI information relevant Annotate core BI data with relevant information - Unified filtering ( Slicing ) across web parts - KPIs, Excel workbooks and SQL Reporting Services reports - Simple to create, simple to customize

15. Excel Services Features Publish Excel sheets, workbooks, pivot tables and charts Rendering in pure DHTML Server side Excel calculation engine - Named ranges as parameters (single cell) - Access using browser or web services Complete web services API Extensible using .NET UDFs Access to external data from SQL Server, Analysis Services & OLEDB provider - Pivot tables incl. filtering, drilldown

16. Limitations of Excel Services Excel Services is NOT Excel on the Server - No authoring No data entry outside of parameters, no layout changes - No VBA, no non-database data sources, many other features not supported Does not Solve the problem of multi-user spreadsheet authoring

17. Excel Services Architecture Web front end Web front end Excel web access Web frontend - SharePoint UI - HTML Rendering + Web Services Application server - Spreadsheet loading, data refresh, calculations - Holds state for interactivity - File and query cache for performance Single server or multi tier Independent scale out Excel web services ECS Proxy Flexible Topology Independent Scale-Out Application Server ECS interface Excel calculation service Application Server External data sources

18. Web Parts Components - Excel Services workbooks - Key Performance Indicators (KPIs) Describe important business measures Value, target, status, trend Sources: SQL Server Analysis Services (SSAS), SharePoint lists, Excel worksheets or manual entry - Filter From SSAS, lists, text, query string, current user

19. Key Performance Indicators in SharePoint Server 2007 Fairly simple to create Many scenarios where code is not required at all Types ranging from very simple to enterprise-level - Manually entered, SharePoint list, Excel workbook, SQL Server Analysis Services Important! SharePoint is not intended to be a scorecarding solution - We have specialized solutions like BSM 2005 and in future PerformancePoint Server 2007

20. Personalizing the BI Experience Filter Web Parts Make dashboards relevant to individual users They come in two forms: - Hidden (current user, query string) - Visible (business data catalog, choice, date, page field, SharePoint list, Analysis Services, Text) Provide a great way to customize user experience

21. Filter information consumers In SharePoint Server 2007 - Excel Web Access - KPI List - Business Data Catalog List - WSS List View SQL Server 2005 Reporting Services Custom consumers

22. Data connection libraries Central repository of important data connections Simple discovery of new data sources Central update of connections in many spreadsheets and solutions Management of one version of the truth

23. Bringing all the Features Together with the Report Center Out of the box site template, optimized for report access and management Helps you to get started Consistent management of reports, spreadsheets and data connections Provides a specialized library for reports the Report Library - Very powerful when combined with the view history feature in SharePoint Supports full integration with SQL Server 2005 Reporting Services (SP2) Important! All BI features are available through the entire portal