Self Service Business Intelligence Keith BurnsData Architect ISV Team, DPE Microsoft Ltd
Session Abstract The challenge of providing decision makers with meaningful information dates back to the dawn of the computing age. On the one hand the raw data is understood and controlled by the IT department and on the other the business user know what it needs. There are several tools in Office and SQL Server 2008 to facilitate this, so the first part of this session, will show what can be achieved today form reporting to data mining. Microsoft’s have also launched project Gemini which is designed to provide much richer capabilities in this area. The idea is that an Information Worker will be able to get at the data they need by using the office tools they understand to turn this (data) into the information they need. Gemini is still in the early phases of development, so the second part of the session will of necessity be an overview and early look at how it will work and how it fits in with the existing tools.
Agenda • 09.00 - 09.45 Registration • 09.45 – 11:00 BI Overview Reporting Services (Report Builder 2.0) • SQL Server 2008 Data Mining & the Excel 2007 DM addin • 11.00 - 11.15 Break • 11.15 - 12.30 DM (cont ... Cloud based DM) • BI Futures (Project Gemini) • 12:30 Close
Today’s BI Reality For Most Organizations DifficultTo Use “Foreign” tools inhibit adoption and use Functionality split across multiple tools Fragmented Rigid and unable to change with the business Inflexible Narrowly deployed without a complete view Departmental Too costly for broad deployment Costly
The Microsoft BI Difference World-ClassExperience IT Flexibilityand Control + Get faster results with afluent User Interface Work easily across web,mobile device, desktop Personalize BI experiences to fit individual role and work-styles Interoperate with existing systems using servicesand connectors Scale on secure and reliable enterprise class infrastructure More secure information withpolicy, workflow, and IRM Empowered People Dynamic Systems
Delivering The Right BI Tool ForEvery Role In The Organization Agility Personal BI Accountability Team BI Alignment Corporate BI
Familiar Microsoft Environment Drives BI AdoptionAmplify the impact of your people with the tools you already know how to use
PerformancePoint ServerRoadmapPerformance Management is enabled through SharePoint ProClarity Analytics Server SharePoint “14” PerformancePoint Services Monitoring Monitoring Business Scorecard Manager 2005 Analytics Analytics Planning Scorecards Analytics “Biz#” “Biz#” Beta PerformancePoint Server 2007 PPS Service Pack 3 Planning Supported 10 yrs H1 CY10 Mid CY09 Today November 06 Mid CY ‘07 8
Integrated End-to-End BI Offering DELIVERY COLLABORATION CONTENT MANAGEMENT SharePoint Server SEARCH Reports Dashboards Excel Workbooks Analytic Views 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
Report Design Goals • SQL Server 2005: two distinct authoring tools Report Designer and Report Builder • Unify Report Designer and Report Builder • Shared design surface that understands all RDL elements • Tablix support • Data visualization support • Usability Enhancements (Zoom, Snaplines, Tokens) • New Property Dialogs
Demo Basic Authoring using Report Builder 2.0
Tabl e + Matr ix
Tablix = Best of Both Worlds ++ • Matrix plus: • Multiple parallel row/column members at each level • Each member may be dynamic or static • Optional omission of member headers • Table plus: • Dynamic, nested column groups • Multiple parallel row groups • Static rows • Optional spanning row headers
Parallel Dynamic Groups Current Desired
Mixing Dynamic and Static Columns Current Desired
Hierarchical Rows with Dynamic Headers Current Desired ... ...
Other Tablix Examples Non-detail aggregates in subtotals Non-aggregate details in subtotals ... Scope-specific aggregates in subtotals
Data Visualization Improvements • Additional Chart Types • Stock, Funnel, Range, Boxplot, Funnel, Pyramid, Pie (Call Out) • Additional Chart Functionality • Multiple Chart Areas (+ Custom Positioning), Titles and Legends • Calculated Series • Scale Break • Secondary Axis • Gauge • Linear and Radial
Chart Examples Frame Effects Scale Breaks Drawing Styles
Chart Examples Multiple Legends Calculated Series Strip lines Multiple Chart Areas
Introducing Gauge • Gauge is a new data region in SQL Server Reporting Services 2008 • 2 Main Gauge Types • Radial and Linear • Scenarios • Typically bound to a single or a few data points (value, target, goal, etc.) • Perfect for Analysis Services KPIs • Stand-alone or embedded in a tablix (bullet graph)
Gauge Samples Radial Gauge Linear Gauge
Demo Chart and Gauge
Richly Formatted Text • Mixed formatting in textboxes • Mail-merge functionality with expression placeholders • Paragraphs • Numbered/bulleted lists • Paragraph alignment • HTML tag-based formatting • Support for complex international scripts • Word DOC export
Report Builder 2.0 Positioning • Report Builder 2.0 will replace Report Builder 1.0 (2005) long term • Report Builder support for report models will continue and be enhanced moving forward • Report model technology will continue and be enhanced moving forward • Post-SQL Server 2008: Report Builder 2.0 will encompass all functionality in Report Builder 1.0
Report Designer Feature Comparison VS Integration Office 12 Look & Feel Full RDL Support Shared Layout Surface Shared Dialogs Report Builder 2.0 Report Models Integrated Query and Layout Full Report Model Support Clickthrough Reports Limited RDL Support Report Builder 1.0
Summary • Author your own reports with Report Builder 2.0 • Report structures possible with tablix • Data visualization in SSRS 2008 • Authoring tools functional comparison
Data Mining Enabling Predictive Analysis Predictive Analysis Role of Software Data Mining Proactive Interactive OLAP Ad-Hoc Reporting Canned Reporting Business Insight Passive Presentation Exploration Discovery
The Value of Predictive Analysis Inform Common Business Decisions with Actionable Insight
What’s New in SQL Server 2008? Enhanced Mining Structures • Split data into training and testing partitions more effectively. • Query against structure data to present complete information beyond the scope of the model. • Build models over filtered data. • Create incompatible models within the same structure. • Use cross-validation to: • Test multiple models simultaneously. • Confirm the stability of results given more or less data. Better Time Series Support • Accuracy & Stability • Combine best of both worlds blending ARTXP for optimized near-term predictions and ARIMA for stable long term predictions • Prediction Flexibility • Build a forecasting model on one series and apply the patterns to data from another series. • What If • Anticipate the impact of changes in near-term future values, on long-term forecasts More Data Mining Add-Ins for Office 2007 • New Analysis Tools • Generate interactive forms for scoring new cases with Prediction Calculator. • Discover the relationship between items that are frequently purchased together with Shopping Basket Analysis. • New Query and Validation Tools • Choose training and test sets from mining structures. • Render richly formatted cross validation and accuracy reports in Excel. • Leverage model documentation for reference and collaboration.
Data Mining Add-In for Microsoft Office 2007 DIG for Insight at Your Desktop Define Data Identify Task Get Results “What Microsoft has done is to make data mining available on the desktop to everyone” - David Norris, Associate Analyst, Bloor Research
Data Mining Add-In for Office 2007 Full Development Life Cycle within Excel • Data Preparation • Explore, clean, and set up your data for data mining • Data Modeling • Build patterns and trends from data to make predictions • Accuracy and Validation • Test and validate your model • Model Usage & Management • Browse, modify, and manage existing mining models that are stored on an instance of Analysis Services • Documentation • Trace your actions as Data Mining Extensions (DMX) statements or as Analysis Services Scripting Language (ASSL).
Demo Excel 2007 Data mining extensions
Integrated Predictive Analysis Native Reporting Integration • Create reports that include prediction • Build reports by using data mining queries as your data source • Access visual prediction Query Builder directly within Report Designer • Generate parameter-driven reports based on predictive probability • For example, present high-risk customers • Probability to churn is over 65%
© 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.