Business Intelligence in SQL Server 2005 Technical Overview

Business Intelligence in SQL Server 2005 Technical Overview Peter Blackburn Speaker, Trainer, Developer, Mentor, Author Windows Server Systems – SQL Server MVP Peter. DB@Boost. net

Business Intelligence Peter Blackburn Published Books: Hitchhiker’s Guide to SQL Server 2000 Reporting Services ADO. NET Examples and Best Practices for C# Programmers Next Books: Hitchhiker’s Guide to SQL Server 2005 Reporting Services Hitchhiker’s Guide to Visual Studio 2005 and SQL Server 2005 Contact Peter. DB@Boost. NET

Business Intelligence Presentation Goal To provide an introduction to as many of the new Business Intelligence features as time permits

Business Intelligence The Original “BI” coined in 1992 by Howard Dresner – now a VP of Gartner Group “Business Intelligence (BI) is a conceptual umbrella. Underneath it are a variety of technologies that support end-user access to, and analysis of quantitative information sources. At the very basic level that means end-user reporting. It also includes OLAP (on line analytical processing), executive interfaces, end-user ad hoc query, enterprise business intelligence suites, and Business Intelligence platforms – which are essentially development environments for building custom decision support applications. ”

Business Intelligence SQL Server 2005 BI Platform BI Component Microsoft SQL Server Technology Extract Transform Bulk Copy Program (BCP) and Load (ETL) 2000: Data Transformation Services (DTS) 2005: SQL Server Integration Services (SSIS) Reporting Services Data Mining Analysis Services Multidimensional Database Relational Data Warehouse Analysis Services Ad hoc query and analysis Microsoft Office RDBMS

Business Intelligence SQL Server 2005 BI Tools BI Component SQL Server 2000 SQL Server 2005 Management Tools Enterprise Manager SQL Server Management Studio Analysis Manager Management Utilities SQL Server Service Manager (systray) SQL Computer Manager (mmc) Server Development Tools SQL Server Management Studio Enterprise Manager Query Analyzer Analysis Manager Client Development Tools Business Intelligence Development Studio Visual Studio. NET

Demo ETL – Simple new T-SQL Features Using SQL Server 2005 Management Studio

Business Intelligence ETL: Database Snapshots Create with TSQL script Enable data consistency of extracts Performance considerations

Business Intelligence ETL: Bulk Copy Program Consume File as ROWSET in TSQL Extract an XML Format File – new for 2005: bcp Adventure. Works. Production. Product format nul -c -x -f Product_XML. fmt -T Extract the Data: bcp Adventure. Works_1300. Production. Product out Product. tab -f Product_XML. fmt –T

Business Intelligence ETL: Database Mirrors Simple ‘hot’ standby servers Easier-to-manage than failover clustering Snapshots enable ‘Mirrors’ to be used as reporting servers with minimal data latency Principal and Mirror can switch ‘roles’ Automatically – requires Witness Server Manually - without a Witness Server Forced service - NB possible data loss

Business Intelligence ETL: SQL Server Integration Services (SSIS) DTS Replacement (DTS 2000 Runtime is included for backward compatibility) Completely re-engineered Enhanced performance & debugging Separation of control flow and data flow Matthew Stephen Drills into SSIS in session 5 today

Demo Analysis Services 2005 Business Intelligence Development Studio

Business Intelligence Analysis Services - UDM Unified Dimensional Model Bridge between the user and the data sources • Performance • Analytic functionality • Complex schemas • Low latency

Business Intelligence Analysis Services - UDM Supports OLAP and Data Mining Cubes & Dimensions No Access (MDB file) meta data repository XML/A Server side KPI calculations Perspectives Translations Stored Procedures (CLR – MDX functions, Cube Processing )

Business Intelligence Analysis Services - KPI Defined in Analysis Server 2005 as Value to be measured Goal for the value Status - range for value between very bad -1 very good +1 Trend – better or worse relative to its goal.

Business Intelligence UDM = The best of Relational and OLAP Feature Flexible schema Real time data access Single data store Simple management Detail reporting High performance End-user oriented Ease of navigation and exploration Rich analytics Rich semantics Relational OLAP ü ü ü û û û û û ü ü ü

Business Intelligence Data Mining Previously domain of big hitters with big budgets Data Mining enables pattern exploration, discovery and ultimately prediction. Predictive Analytics Ability to apply the data mining results against new data SQL Server Data Mining extracts information from data, and allows the user to integrate predictive analytics.

Business Intelligence Data Mining answers a variety of questions: Which customers will respond to my mailing? Is this transaction fraudulent? Will I lose this customer? How long will this asset be in service? How can I differentiate my customers? How are people using my Web site? What items are bought together? What products should I recommend to my customers? What are projected revenues for all products? How do I handle call center data?

Business Intelligence Data Mining What's New? Two enhanced and five new algorithms … Enhanced tools including custom visualization for each algorithm OLAP, DTS, and Reporting Integration. NET programming model Text Mining Completely expandable framework

Business Intelligence Decision Trees Sequence Clustering Time Series Association Naïve Bayes Neural Net

Business Intelligence Data Mining – Which Algorithm ? Analytical problem Examples Microsoft algorithms Classification: Assign cases to predefined classes such as "Good" vs "Bad" Credit risk analysis Decision Trees Churn analysis Naïve Bayes Customer retention Neural Nets Segmentation: Develop a taxonomy for grouping similar cases Customer profile analysis Clustering Mailing campaign Sequence Clustering Association: Advanced counting for correlations Market basket analysis Decision Trees Advanced data exploration Association Rules Time Series Forecasting: Predict the future Forecast sales Time Series Prediction: Predict a value for a new case (such as a new customer) based on values for similar cases (such as existing customers) Quote insurance rates Deviation analysis: Discover how a case or segment differs from others Credit card fraud detection Predict stock prices All Predict customer income Predict temperature Network infusion analysis All

Business Intelligence Data Mining Example Get top five movie recommendations based on shopping cart contents SELECT Predict(Movies, 5) FROM Movie. Model NATURAL PREDICTION JOIN (SELECT ‘Matrix’ as Movie UNION SELECT ‘Aliens’ as Movie) as Movies) as t

Demo Reporting Services 2005

Business Intelligence Reporting Services Architecture Web Report Builder BI Dev Studio Report Designer Report. Server Web Service Report Manager Internet Information Server (IIS) URL & SOAP Interfaces Data Sources (SQL, OLE DB, XML/A, ODBC, Oracle, Custom) Analysis Server Report Processor Data Processing Report Rendering Output Formats HTML, Excel, PDF, CSV, XML… Security Scheduling & Delivery SQL Server Catalog SQL Server 2005 Delivery Targets (E-mail, File. Share, Sharepoint Custom)

Business Intelligence Reporting Services New for SQL 2005 Report. Builder; based on Active Views ADO. NET Datasets as data sources MDX Designer Custom Report Controls Better integration with Analysis Server Reporting Services Configuration Manager SSIS – report directly from SSIS New for Visual Studio 2005 Client side rendering control

Resources SQL Server 2005 www. microsoft. com/sql/2005 Visual Studio 2005 lab. msdn. microsoft. com/vs 2005 My Site: www. sqlreportingservices. net

Event Information What’s Next? Technical Roadshow Post Event Website www. microsoft. com/uk/techroadshow/postevents Available from Monday 18 th April Please complete your Evaluation Form!