DEV 14 Building Business Dashboards Excel Services KPIs

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

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

Who uses Business Intelligence? “Analysts” Execs Operations Middle Managers

Our Integrated BI Offering DELIVERY Share. Point Server Excel Analytic Scorecards Reports Dashboards. Workbooks Views Plans END USER TOOLS & PERFORMANCE MANAGEMENT APPS Performance. Point Server Excel BI PLATFORM SQL Server Reporting Services SQL Server Analysis Services SQL Server DBMS SQL Server Integration Services

Server investments Share. Point is “BI Portal and Report Center” • Excel Services • Dashboards & Web Parts • Report Center • Reporting Services • Pro. Clarity • Business Scorecard Manager

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

Solutions for Different Work. Styles • 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

Driving Adoption • Software that is Familiar Centers of Excellence • Ease of Use Communities of Practice • Competency Centers - Business Intelligence, Integration Communities of Interest • 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

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

Links & Resources Microsoft Business Intelligence Demos and White Papers http: //www. microsoft. com/bi/resources/dem os. aspx CRM Analytics Foundation & the Technical White Paper http: //codeplex. com/crmanalytics ARC 04 – BI with Excel Services http: //www. microsoftsharepoint. com/agenda and Reporting Services /Pages/newdefault. aspx th May 15 2. 25 pm: Session Three Darwin Schweitzer Enterprise Tech. Strategist Darwin. Schweitzer@microsoft. com Grant Paisley MVP SQL Server Angry Koala grant@angrykoala. com. au

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

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

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

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

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

Excel Services Architecture • Web frontend - Share. Point 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 Web front end Excel web access services ECS Proxy Independent Scale-Out Flexible Topology Application Server ECS interface Excel calculation service Application Server • Independent scale out External data sources

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

Key Performance Indicators in Share. Point 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, Share. Point list, Excel workbook, SQL Server Analysis Services • Important! • Share. Point is not intended to be a scorecarding solution - We have specialized solutions like BSM 2005 and in future Performance. Point Server 2007

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, Share. Point list, Analysis Services, Text) • Provide a great way to customize user experience

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

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

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 Share. Point • Supports full integration with SQL Server 2005 Reporting Services (SP 2) • Important! • All BI features are available through the entire portal