1 / 29

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 PeterDB@Boost.net. Business Intelligence Peter Blackburn. Published Books:

tammy
Download Presentation

Business Intelligence in SQL Server 2005 Technical Overview

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Business Intelligence in SQL Server 2005 Technical Overview Peter Blackburn Speaker, Trainer, Developer, Mentor, AuthorWindows Server Systems – SQL Server MVPPeterDB@Boost.net

  2. Business IntelligencePeter 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 • PeterDB@Boost.NET

  3. Business IntelligencePresentation Goal • To provide an introduction to as many of the new Business Intelligence features as time permits

  4. Business IntelligenceThe 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.”

  5. BI Component Microsoft SQL Server Technology Extract Transform and Load (ETL) Bulk Copy Program (BCP)2000: Data Transformation Services (DTS)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 IntelligenceSQL Server 2005 BI Platform

  6. Business IntelligenceSQL Server 2005 BI Tools

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

  8. Business IntelligenceETL: Database Snapshots • Create with TSQL script • Enable data consistency of extracts • Performance considerations

  9. Business IntelligenceETL: 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

  10. Business IntelligenceETL: 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

  11. Business IntelligenceETL: 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

  12. Analysis Services 2005 Business Intelligence Development Studio Demo

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

  14. Business IntelligenceAnalysis 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 )

  15. Business IntelligenceAnalysis 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.

  16. Business IntelligenceUDM = The best of Relational and OLAP

  17. Business IntelligenceData 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.

  18. Business IntelligenceData 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?

  19. Business IntelligenceData 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

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

  21. Business IntelligenceData Mining – Which Algorithm ?

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

  23. Reporting Services 2005 Demo

  24. BI Dev Studio Report Designer Web Report Builder ReportServer WebService 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 Report Rendering Scheduling & Delivery Security Data Processing Delivery Targets (E-mail, FileShare, Sharepoint Custom) SQL Server Catalog Output Formats HTML, Excel, PDF, CSV, XML… SQL Server 2005 Business IntelligenceReporting Services Architecture

  25. Business IntelligenceReporting 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

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

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

  28. © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only.MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

  29. © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only.MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

More Related