Business Intelligence in SQL Server 2005 Technical Overview
29 Slides3.11 MB
Business Intelligence in SQL Server 2005 Technical Overview Peter Blackburn Speaker, Trainer, Developer, Mentor, Author Windows Server Systems – SQL Server MVP [email protected]
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 [email protected]
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) 2000: Data Transformation Services (DTS) and Load (ETL) 2005: SQL Server Integration Services (SSIS) Reporting Reporting Services Data Mining Analysis Services Multidimensional Database Analysis Services Relational Data Warehouse RDBMS Ad hoc query and analysis Microsoft Office
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 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 AdventureWorks.Production.Product format nul -c -x -f Product XML.fmt -T Extract the Data: bcp AdventureWorks 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 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 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 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 MovieModel NATURAL PREDICTION JOIN (SELECT (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 ReportServer Web Service Report Manager Internet Information Server (IIS) URL & SOAP Interfaces Data Sources (SQL, OLE DB, XML/A, ODBC, Oracle, Custom) Analysis Server Report 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, FileShare, Sharepoint Custom)
Business Intelligence Reporting Services New for SQL 2005 ReportBuilder; 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/vs2005 My Site: www.sqlreportingservices.net
Event Information What’s Next? Technical Roadshow Post Event Website www.microsoft.com/uk/techroadshow/postevents Available from Monday 18th April Please complete your Evaluation Form!
2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
