1 / 51

Microsoft ® SQL Server ™ 2005 Business Intelligence

Microsoft ® SQL Server ™ 2005 Business Intelligence. Barnaby Jeans IT Pro Advisor Microsoft Canada http://blogs.technet.com/Barnaby_Jeans. Prerequisite Knowledge. Experience administering SQL Server Experience administering IIS Familiarity with Visual Studio.NET. Level 200.

mercury
Download Presentation

Microsoft ® SQL Server ™ 2005 Business Intelligence

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. Microsoft® SQL Server™ 2005 Business Intelligence Barnaby Jeans IT Pro Advisor Microsoft Canada http://blogs.technet.com/Barnaby_Jeans

  2. Prerequisite Knowledge • Experience administering SQL Server • Experience administering IIS • Familiarity with Visual Studio.NET Level 200

  3. What We Will Cover • Integration Services • Analysis Services • Reporting Services

  4. Microsoft Business Intelligence Vision Improving organizations by providing business insights to all employees leading to better, faster, more relevant decisions • Advanced Analytics • Self Service Reporting • Business Performance Management • Operational Applications • Embedded Analytics

  5. Integrate Analyze Report SQL Server Business Intelligence • Data enrichment, with business logic, hierarchical views • Data discovery via data mining • Data acquisition from source systems and integration • Data transformation and synthesis • Data presentation and distribution • Data access for the masses

  6. Customer challenges:business demands • Shorter processing windows • 24:7 businesses have no downtime • International business has no quiet time • Online business operates in real-time • More regulation • Sarbanes-Oxley, Basel II, impose strict compliance requirements • More sophisticated users • Drive smart decisions at every level from operational to strategic

  7. Customer challenges:data demands • Ever greater volumes of data • Clickstreams, e-commerce, RFID, call-centres • More diverse sources of data • Web services, RSS, unstructured and semi-structured, many locales • More diverse destinations • Mobile devices, personalized reporting, web publishing

  8. Integrate SQL Server Business Intelligence • Data acquisition from source systems and integration • Data transformation and synthesis

  9. What is SQL Server Integration Services? • A new SQL Server Business Intelligence application • The successor to Data Transformation Services • The platform for a new generation of high performance data integration technologies

  10. Alerts & escalation Call centre data: semi structured Data mining ETL Text Mining Staging Legacy data: binary files Staging Warehouse ETL Hand coding Staging Cleansing & ETL Reports Application database ETL Mobile data Example: before Integration Services • Integration and warehousing require separate, staged, operations. • Preparation of data requires different, often incompatible, tools. • Reporting and escalation is a slow process, delaying smart responses. • Heavy data volumes make this scenario increasingly unworkable.

  11. Alerts & escalation Mobile data Text mining components Call centre: semi-structured data Merges Data cleansing components Data mining components Standard sources Custom source Warehouse Legacy data: binary files Reports SQL Server Integration Services Application database Example: with Integration Services • Integration and warehousing are a seamless, manageable, operation. • Sourced, prepare and load data in a single, auditable process. • Reporting and escalation can be parallelized with the warehouse load. • Scales to handle heavy and complex data requirements.

  12. How SSIS Works • Data sources can be diverse, including custom or scripted adapters • Transformation components shape and modify data in many ways. • Data is routed by rules or error conditions for cleansing and conforming. • Flows can be as complex as your business rules, but highly concurrent. • And finally data can be loaded in parallel to many varied destinations.

  13. Components in the data flow • Some components work with data row by row • Calculating new columns • Converting data • Character conversions • Look-up joins to reference tables • These benefit more from parallelism than memory

  14. Components in the data flow • Some components need to work with the entire data set • Aggregation • Sorting • Fuzzy (best match) Lookups and Deduplication • These benefit from increased memory • 64 bit enables potentially huge data sets to be worked on in memory

  15. Enabling new architectures … Traditional warehouse loading • In this traditional scenario, the integration process simply conforms data and loads the database server • The database performs aggregations, sorting and other operations … • … but has to contend with competing demands for resources from user queries • This solution does not scale to very large volumes of data and multiple, complex aggregations

  16. Enabling new architectures … Warehouse loading with SQL Server Integration Services • Here, SQL Server Integration Services conforms the data as before … • … but also aggregates and sorts, and loads the database • This frees-up the database server for user queries • With 64-bit this solution scales well to very large volumes of data and multiple, complex aggregations • Even with 32 bit, this architecture can be scaled-out to use a separate box for the integration process

  17. Customer benefits of SSIS • Performance • Data flows process large volumes of data efficiently - even through complex operations • Facility • Many prebuilt adapters and transformations reduce hand coding • Extensible object model enables specialized custom or scripted components • Highly productive visual environment speeds development and debugging • “Smarts” • Data cleansing features enable difficult data to be handled during loading • Data mining brings intelligent handling of data for imputation of incomplete data, conditional processing of potential problems, or smart escalation of issues such as fraud detection

  18. demonstration • Integration Services • Build a package to import files in the database

  19. Integrate Analyze SQL Server Business Intelligence • Data enrichment, with business logic, hierarchical views • Data discovery via data mining • Data acquisition from source systems and integration • Data transformation and synthesis

  20. Analysis Services UDM Cache What Is SQL Server 2005 Analysis Services? Spreadsheets SQL Server Datamart BI Front Ends DW Teradata Ad-Hoc Reports Rich Reports Oracle DB2 LOB Dashboards

  21. The Role of OLAP • “Database” OLAP • “Spreadsheet” OLAP • The Value Add from Analysis Services

  22. Database Data Access • SELECT Sum(«Measure»)FROM «Source»WHERE «Slicers»GROUP BY «Dicers» • Potentially very slow

  23. Spreadsheet Data Access • Cell Reference =C5 • Retrieves data by position

  24. Users Need Both Complex Formulas Spreadsheet Simple Database Small Large Data

  25. Value Add from OLAP • Pre-stored Aggregations • Improve speed for summarized queries from additive measures • Metadata about dimension attributes and their relationships • Enables client to help user navigate • Facilitates relationship-based calculations

  26. demonstration • Analysis Services • Build a cube and implement KPIs

  27. Role of Data Mining Complex DataMining Formulas Spreadsheet Simple Database Small Large Data

  28. Data Mining Benefits • Explore your data • Who are my best customers • What are my sales likely to be next quarter • What else are they likely to buy • Act on patterns and trends

  29. Two Types of Skills for Data Mining • Technical • How to use build efficient model • How to train/update model • How to query model • Statistical • What questions are meaningful? • How representative is the data set? • How should we set model parameters?

  30. Decision Trees Clustering Time Series Naïve Bayes SequenceClustering Association Neural Net SQL Server 2005 Data Mining Algorithms

  31. Integrate Analyze Report SQL Server Business Intelligence • Data enrichment, with business logic, hierarchical views • Data discovery via data mining • Data acquisition from source systems and integration • Data transformation and synthesis • Data presentation and distribution • Data access for the masses

  32. Browser Management Custom App Data Sources (SQL, OLE DB, ODBC, Oracle, Custom) Output Formats (HTML, Excel, PDF, Custom) URL WMI Web Service Report Server Report Processing Rendering Data Retrieval Security Delivery SQL Server Catalog Delivery Targets (E-mail, File share, Custom) Architecture and DeploymentIntroduction to Reporting Services

  33. Data Report Definition Intermediate Format On-demand Access Scheduled or Push Access Rendered page in HTML, PDF or other format Layout Snapshot or Report History Architecture and DeploymentReport Processing

  34. Architecture and DeploymentReporting Services Client Components • Report Designer • Integrated with Visual Studio.NET 2003 • Web Browser • View and Manage Reports • Third Party Tools • Management • Authoring • Supported through open interfaces or Web Services API

  35. Authoring Delivery Authoring Management Management Online Access Report Definition ManagedReport ManagedReport Delivery Channels Architecture and DeploymentReport Authoring Report Definition Report resources published/managed as Web Service Report resources published/managed as Web Service Create Reports with RDL Create Reports with RDL Supports pull and push delivery of reports

  36. Server Client Report Manager Report Server Report Designer Report Server Database Architecture and DeploymentSmall Deployment

  37. Server A Server B Client Report Manager Report Server Report Designer Report Server Database Architecture and DeploymentMedium Deployment

  38. Report Server Web Farm Clients Report Server Report Server Report Server Data Sources SQL Server Cluster Report Server Database Report Server Database Architecture and DeploymentLarge Deployment

  39. Creating ReportsVisual Studio .NET Integration

  40. Creating ReportsReport Definition Language (RDL) • Report Definition Language – XML schema contains • Data source information • Layout • Report Properties • Definitions stored in Report Server Database • Can create tools that use RDL

  41. Managing ReportsReport Manager • View, Search and Subscribe to Reports • Create and manage: • Folders • Linked reports • Report history • Schedules • Data source connections • Subscriptions • Set properties and report parameters • Manage role definitions and assignments

  42. Managing ReportsReport Manager

  43. Managing ReportsViewing Reports • Reports can be rendered in: • HTML • HTML with Office Web Components • XML • CSV • Excel • Image – such as TIFF • Acrobat PDF file

  44. Managing ReportsReport Subscriptions

  45. Managing ReportsReport Execution Processing • On Demand • On Demand From Cache • Removed from cache at defined intervals • From Snapshots • Query is separate from render

  46. Managing ReportsSecuring the Report Server and Reports • Reporting Server Security • Uses IIS security • Windows security • User Permissions • Windows security • Role-based authorization

  47. demonstration • Creating, Customizing and Importing Reports • Creating a Report • Customizing a Report

  48. Reporting Services Summary • Supports the full reporting life cycle • Highly scalable • You do not need to know RDL to create a report definition • Supports several data sources • Reports can be rendered in a variety of formats • Reports can be executed on demand or cached for quicker access

  49. Integrate Analyze Report SQL Server Business Intelligence • Data enrichment, with business logic, hierarchical views • Data discovery via data mining • Data acquisition from source systems and integration • Data transformation and synthesis • Data presentation and distribution • Data access for the masses

  50. For More Information • Visit TechNet at www.microsoft.com/technet • For additional information on books, courses, and other community resources that support this session, visit blogs.technet.com/Barnaby_Jeans

More Related