Cabana A Using SQL Server Analysis Services Reporting

Cabana A Using SQL Server Analysis Services, Reporting Services and Office System 2003 Thierry D’hers Lead Program Manager tdhers@microsoft. com SQL Server BI Microsoft

Microsoft BI Architecture SQL Server Relational Engine Reporting Services Analysis Services OLAP & Data Mining Windows Server W i n d o w s Cl i en t • • BI Solution Accelerators l Business Scorecards l Excel Add-in for SQL Server Portals • Messaging Dashboards Services Data Analysis • Project Visualization Management Ma n a g e m e n t Tools Devel o p er T o o l s Microsoft Business Solutions F i n a n c i a l A n a l y ti c s Office System

Agenda OLAP fuels BI applications The UDM Provides the one version of the truth throughout the enterprise BI can be exposed to users in various ways: Reporting Services Excel / OWC Excel Addin for OLAP Balance Scorecard Sharepoint More in the Office family Data Analyzer Mappoint Project…

Common problems… How do users: Datamart SQL Server Teradata Oracle DW DW DB 2 LOB Get to heterogeneous data? Combine it & understand it? Easily navigate through it? Explore it interactively? Get consistent business metrics? Gain unique insight into it? Gain competitive advantage? Share the insight with the rest of the enterprise? Spreadsheets BI Front Ends Dashboards Ad-Hoc Reports Production Reports

A Solution… Analysis Services Datamart SQL Server Teradata Oracle DW DW DB 2 LOB Heterogeneous data access End user oriented data model Proactive caching Performance and scale Single version of truth Spreadsheets BI Front Ends Dashboards Reporting Services Enterprise reporting platform Report management (scheduling, caching, …) Multiple delivery formats Ad-Hoc Reports Production Reports

A Solution… Spreadsheets Datamart SQL Server Teradata Oracle DW DW Analysis Services Dashboards UDM DB 2 LOB BI Front Ends Reporting Services Ad-Hoc Reports Cache Production Reports

UDM as a report source End User oriented data model Business entities vs. tables/columns Relationships predefined as part of UDM Friendly names Formatting, Coloring Hierarchies for easier navigation Grouping/folders for easier exploration Perspectives

Accessing Formatting Properties in Reports Reporting Services 2005 makes it easier to access “smarts” defined in UDM It is done via Extended Properties Value, Formatted. Value Unique. Name Color, Background. Color Font. Family, Font. Size, Font. Weight, Font. Style Key Level. Number Parent. Unique. Name …

demo Building Reports on top of UDM

UDM as a report source Key Performance Indicators Important metrics about the health of the organization High level, visual, quick to grasp Organized in scorecards Displayed in dashboards KPI framework is part of UDM KPI components Value, Goal, Status, Trend Graphic indicators Other properties: Folders, Parent, Weight

demo KPIs in Reports

Report Builder and Analysis Services Report builder – end user oriented report building tool Enables power users (non-IT) to build/publish reports Report Builder works with SQL Server and Analysis Services UDMs are automatically available in Report Builder Different (Entity-Relationship) view of the data

demo Report Builder and UDM

Accessing UDM data from Office Leveraging Excel investments Microsoft Excel is a native client to OLAP and the UDM No Client component deployment needed Excel: Everyone has it, loves it and know how to work with it. No training required Basic slice and dice Office 12: Expect more, way more… Stay tuned

demo Connecting to UDM from Office: Excel Pivot Tables Office Web Components

Business Scenarios End User Reporting & Analysis Project Monitoring & Reporting Flexible BI Business Scorecard Accelerator Excel Add-in for SQL Server EPM Solution Custom BI Solutions MS Integrated BI Capabilities Corporate Performance Management Solution Enabler Microsoft Office System Business Intelligence Solutions Microsoft Office System Windows Server System

Excel Add-In for SQL Server Microsoft Office Excel add-in Free-form and Semi-Structured reporting Multiple data sources on the same report Based upon Microsoft Excel 2003 or Excel XP Microsoft SQL Server 2000 and Analysis Services Microsoft Windows XP or Windows 2003 Server

Business Value Excel Add-In for SQL Server Powerful Excel reporting and analysis Key BI Features Free-form reporting Semi-structured reporting Write-back to OLAP capability Out of the box deployment Key Benefits Deliver business analytics Empower employees with business analysis capabilities Increase ROI on existing tools Reduce training costs Create sophisticated budget and modeling applications Rapid report development

Free-Form Reports Sales Cube Mktg Campaign Cube • • Excel Calculations Capability Limitation Access multiple OLAP sources Intersperse OLAP and Excel data Insert rows and columns Excel’s native formatting and sorting Member properties in rows and columns Create nested axis – cross-join Analysis Services’ Actions, drill through, write back and server side formatting • Cannot Use refresh report or a page member change, to bring new members for row or column headers.

Structured Reports Geography Order Type Customer Capability • Using dynamic set expressions on an axis • Drill Up, Drill Down, Expand, Collapse, Focus and Eliminate, Pivoting • Create reports with nested axis • Analysis Services’ Actions, drill through, write back and server side formatting • Refresh reflects member changes • Create custom filter expressions Limitation • Create Reports from single OLAP source only • Cannot Insert row or column • Cannot add external data within the report • Cannot add member properties to the report • Cannot sort members

demo Excel Add-In for SQL Server Analysis Services

Office Scorecards Product Summary Advanced KPI and Scorecard Authoring and Management Cascading of metrics across organizations Sourcing of metrics from multiple data sources Multiple targets (plan, budget, forecast) to compare and contrast performance Dashboard functionality Charts, graphs, spreadsheets and pivot tables driven by data from Scorecards and KPIs Analyze, interact with, drilldown into and annotate data in the context of consistent business definitions Scorecards and reports move in concert as a user drills down for detailed analysis RTM Fall 2005, Beta 2 currently available Localized into French, Japanese, German and Spanish (2006) First commercial Microsoft Business Intelligence Application

Technology Adoption Program TAP Validation Customers and Partners (private beta) Hewlett Packard, Cap. Gemini United States Navy, Pro. Clarity State of Washington, Cizer Software Development Bank of South Africa, Harvey Jones Expedia, Two Degrees Consulting TDC, Microsoft Consulting Services Acosta, Avanade Deacons, Aderant Accenture (internal) Reynolds & Reynolds (internal) Skanska USA (internal) Xerox (internal) Microsoft Windows Vista (internal) Microsoft IT (internal)

Technology Adoption Program Beta Program Participants (public beta) 1953 Registered Beta. Place Participants (non-unique) 1052 BSM Beta 2 Server Installer downloads (4 weeks) 920 Survey Responses 58 Countries 389 Newsgroup postings Top 10 Countries by Survey Response

What Scorecard Stakeholders Want: Business Decision Makers (C-Level on down) Business Analysts IT Administrators Metrics from multiple data sources Integrated collaboration environment Alerting on changes in data Summary reporting with analysis in context Powerful and flexible business definitions Empowering KPI and Scorecard designer Metrics easily re-used and cascaded Centrally managed, scalable BI platform Partnership with business users in Scorecard and KPI authoring

Office Scorecards Architecture Dashboards and Scorecards, Analysis and Collaboration Business Decision Maker Office Scorecard Webpart Office Report Viewer Webpart Scorecard Reporting Business Decision Maker Web Components 2005/2000 Reporting Services Excel, TIFF, XML, PDF Definition Authoring Business Analysts Scorecard Builder Application Metadata Scorecard Application Centralized Management Source Systems 2005/2000 Scorecard Cubes 2005/2000 Analysis Services IT Administrator Business Intelligence Platform

Dashboarding and Scorecarding in Share. Point Portal Server

Legend: System Components Clients and server Scorecard Component Scorecard Dependency Scorecard Light-up Windows XP, SP 2+ Internet Explorer 5. 5+ Office Web Components 2003 Dashboard Clients Scorecards DLL Scorecards Database Scorecard Web Parts Web Service SSRS Export (optional) SSNS Alerts (optional) Authoring Clients Scorecard Server Scorecard Builder Windows Server 2003, SP 1+ SPS / WSS SSAS SSRS Database SSNS Client + Engine SSNS Database

One server, two server, three server, N Business Scorecard Manager Scale-out Scenarios Typically, not all data sources will not be owned by the scorecard application builder Topologies should anticipate this Sample Configurations One Server Proof-of-concept Two Servers Use unowned data sources Three Servers Separate IIS from databases N-Servers Scale-out for enterprise Scorecard Installation Components

Two server deployment Legend: Scorecard Component Scorecard Dependency Use unowned data sources Scorecards DLL Scorecards Database Scorecard Web Parts Web Service SSRS Export SSNS Alerts SPS / WSS SSRS Database SSAS Cubes Web Server Analysis Services Server SSNS Client + Engine SSNS Database Remember: App. Pool account needs to have read access to your SSAS cubes

Three server deployment Legend: Scorecard Component Scorecard Dependency Separate IIS from databases Scorecard Database Server SSNS, SSRS Database Scorecards DLL Scorecard Web Parts Web Service SSAS Cubes SSRS Export SSNS Alerts SPS / WSS SSRS SSNS Client + Engine Web Server Analysis Services Server

Three server deployment Legend: Scorecard Component Scorecard Dependency Separate IIS from databases Scorecard Database Scorecard DLL attaches to Scorecard Database Server via web. config setting Database Server SSNS, SSRS Database Scorecards DLL Scorecard Web Parts Web Service SSAS Cubes SSRS Export SSNS Alerts SPS / WSS SSRS SSNS Client + Engine Web Server Analysis Services Server

N-server deployment Legend: Scorecard Component Scorecard Dependency Scale out for enterprise Scorecards DLL Scorecard Database Scorecard Web Parts Web Service Database Server SSRS Export SSNS Alerts SSNS, SSRS Database SPS / WSS SSRS Web Server SSAS Cubes SSNS Client + Engine Remember: Scorecards DLL needs to be installed on all web servers Remember: Scorecard Web Service is required where SSRS Export or SSNS Alert is installed Analysis Services Server

Security and Scorecard Stakeholders How security maps to business roles Business Decision Makers (C-Level on down) Business Analysts IT Administrators Readers – Consume KPIs and Scorecards in dashboards and reports. Analyses, evaluates and annotates information Scorecard Server Manager – Provisions KPIs and Scorecards for other business users Editor – Enriches KPIs and Scorecards with definitions, business logic, charts, graphs, and other supporting detail Scorecard Server Administrator – Deploys Scorecard Server Instance Data Source Manager – Provisions data sources to be accessed by business users

System Security Internet Information Services Site-level security Scorecard Manager Instance and element-level security Application Pool Thread pool identity SQL Server Analysis Services Role-based dimension and member-level security Active Directory Share. Point Page-level Permissions AD Federation Services The scorecard application permissions stack

Beyond Microsoft Office System… Data Analyzer Shows you highly dimensional data graphical Help detect problems and anomalies quickly Not part of Office systems 2003 Mappoint Yes it does BI! Can read external data from SQL or OLAP… Not part of Office system 2003 MS Project Enterprise’s reporting feature is using cubes under the cover…

demo Data Analyzer and Mappoint in action…

We invite you to participate in our online evaluation on Comm. Net, accessible Friday only If you choose to complete the evaluation online, there is no need to complete the paper evaluation