McGraw-Hill - Delivering Business Intelligence with Microsoft SQL Server 2008 (2009)01

Chia sẻ: Hoang Nhan | Ngày: | Loại File: PDF | Số trang:40

1
125
lượt xem
41
download

McGraw-Hill - Delivering Business Intelligence with Microsoft SQL Server 2008 (2009)01

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

McGraw-Hill - Delivering Business Intelligence with Microsoft SQL Server 2008 (2009)01

Chủ đề:
Lưu

Nội dung Text: McGraw-Hill - Delivering Business Intelligence with Microsoft SQL Server 2008 (2009)01

  1. Microsoft® SQL Server™ 2008 Delivering Business intelligence
  2. About the Author Brian Larson is a Phi Beta Kappa graduate of Luther College in Decorah, Iowa, with degrees in physics and computer science. Brian has 23 years of experience in the computer industry and 19 years experience as a consultant creating custom database applications. He is currently the Chief of Technology for Superior Consulting Services in Minneapolis, Minnesota, a Microsoft Consulting Partner for Reporting Services. Brian is a Microsoft Certified Solution Developer (MCSD) and a Microsoft Certified Database Administrator (MCDBA). Brian served as a member of the original Reporting Services development team as a consultant to Microsoft. In that role, he contributed to the original code base of Reporting Services. Brian has presented at national conferences and events, including the SQL Server Magazine Connections Conference, the PASS Community Summit, and the Microsoft Business Intelligence Conference, and has provided training and mentoring on Reporting Services across the country. He has been a contributor and columnist for SQL Server Magazine. In addition to this book, Brian is the author of Microsoft SQL Server 2008 Reporting Services, also from McGraw-Hill. Brian and his wife Pam have been married for 23 years. Pam will tell you that their first date took place at the campus computer center. If that doesn’t qualify someone to write a computer book, then I don’t know what does. Brian and Pam have two children, Jessica and Corey. About the Technical Editor Robert M. Bruckner is a senior developer with the SQL Server Reporting Services (SSRS) product group at Microsoft. Prior to this role at Microsoft, he researched, designed, and implemented database and business intelligence systems as a scientific researcher at Vienna University of Technology, and as a system architect at T-Mobile Austria. Robert joined the Reporting Services development team in early 2003 and has been specializing on the data and report processing engine that is running inside server and client components of Reporting Services. Ever since the initial beta release of SSRS 2000, Robert has been sharing insights, tips, tricks, and expert advice about RDL, data and report processing, and SSRS in general, helping people learn about, understand, and succeed with SSRS (e.g., by posting on newsgroups and MSDN forums, publishing whitepapers, and speaking at conferences). Robert holds Master and PhD degrees with highest distinctions in Computer Science from Vienna University of Technology, Austria.
  3. Microsoft® SQL Server™ 2008 Delivering Business intelligence Brian Larson New York Chicago San Francisco Lisbon London Madrid Mexico City Milan New Delhi San Juan Seoul Singapore Sydney Toronto
  4. Copyright © 2009 by The McGraw-Hill Companies. All rights reserved. Except as permitted under the United States Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of the publisher. ISBN: 978-0-07-154945-5 MHID: 0-07-154945-5 The material in this eBook also appears in the print version of this title: ISBN: 978-0-07-154944-8, MHID: 0-07-154944-7. All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a trademarked name, we use names in an editorial fashion only, and to the benefit of the trademark owner, with no intention of infringement of the trademark. Where such designations appear in this book, they have been printed with initial caps. McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in corporate training programs. To contact a representative please visit the Contact Us page at www.mhprofessional.com. Information has been obtained by McGraw-Hill from sources believed to be reliable. However, because of the possibility of human or mechanical error by our sources, McGraw-Hill, or others, McGraw-Hill does not guarantee the accuracy, adequacy, or completeness of any information and is not responsible for any errors or omissions or the results obtained from the use of such information. TERMS OF USE This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors reserve all rights in and to the work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store and retrieve one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon, transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without McGraw-Hill’s prior consent. You may use the work for your own noncommercial and personal use; any other use of the work is strictly prohibited. Your right to use the work may be terminated if you fail to comply with these terms. THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WAR- RANTIES AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE WORK, INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTHERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUD- ING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or guarantee that the functions contained in the work will meet your requirements or that its operation will be uninterrupted or error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless of cause, in the work or for any damages resulting there- from. McGraw-Hill has no responsibility for the content of any information accessed through the work. Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential or similar damages that result from the use of or inability to use the work, even if any of them has been advised of the possibility of such damages. This limitation of liability shall apply to any claim or cause whatsoever whether such claim or cause arises in contract, tort or otherwise.
  5. This book is dedicated to my parents. To my father, Robert, who even after 40-plus years as a junior high mathematics teacher and computer instructor, has a love of teaching. He has shown me a real commitment to sharing knowledge with others. To my mother, Beverly, who was my first editor, coaching me through elementary school papers on this state or that president. She taught me the value of sticking with a job and seeing it through to the end. I owe them both a debt of love, caring, and support that can never be adequately repaid.
  6. This page intentionally left blank
  7. Contents at a Glance Part I Business Intelligence Chapter 1 Equipping the Organization for Effective Decision Making 3 Chapter 2 Making the Most of What You’ve Got—Using Business Intelligence 13 Chapter 3 Seeking the Source—The Source of Business Intelligence 25 Chapter 4 One-Stop Shopping—The Unified Dimensional Model 43 Chapter 5 First Steps—Beginning the Development of Business Intelligence 61 Part II Defining Business Intelligence Structures Chapter 6 Building Foundations—Creating Data Marts 91 Chapter 7 Transformers—Integration Services Structure and Components 135 Chapter 8 Fill ’er Up—Using Integration Services for Populating Data Marts 233 Part III Analyzing Cube Content Chapter 9 Cubism—Measures and Dimensions 295 Chapter 10 Bells and Whistles—Special Features of OLAP Cubes 331 Chapter 11 Writing a New Script—MDX Scripting 389 Chapter 12 Pulling It Out and Building It Up—MDX Queries 433 Part IV Mining Chapter 13 Panning for Gold—Introduction to Data Mining 469 Chapter 14 Building the Mine—Working with the Data Mining Model 495 Chapter 15 Spelunking—Exploration Using Data Mining 529 vii
  8. viii Delivering Business Intelligence with Microsoft SQL Server 2008 Part V Delivering Chapter 16 On Report—Delivering Business Intelligence with Reporting Services 561 Chapter 17 Falling into Place—Managing Reporting Services Reports 643 Chapter 18 Let’s Get Together—Integrating OLAPwith Your Applications 683 Chapter 19 Another Point of View—Excel Pivot Tablesand Pivot Charts 723 Index 741
  9. Contents Acknowledgments xvii The Maximum Miniatures Databases and Other Supporting Materials xviii Part I Business Intelligence Chapter 1 Equipping the Organization for Effective Decision Making 3 Effective Decision Making 4 Who Is a Decision Maker? 4 What Is an Effective Decision? 5 Keys to Effective Decision Making 6 Are We Going Hither or Yon? 6 Is Your Map Upside-Down? 8 Panicked Gossip, the Crow’s Nest, or the Wireless 9 Business Intelligence 11 Business Intelligence and Microsoft SQL Server 2008 12 Chapter 2 Making the Most of What You’ve Got—Using Business Intelligence 13 What Business Intelligence Can Do for You 14 When We Know What We Are Looking For 14 Discovering New Questions and Their Answers 15 Business Intelligence at Many Levels 16 The Top of the Pyramid 16 Mid-Level 19 The Broad Base 19 Maximum Miniatures, Inc 20 Business Needs 20 Current Systems 21 Building the Foundation 23 Chapter 3 Seeking the Source—The Source of Business Intelligence 25 Seeking the Source 26 Transactional Data 26 ix
  10. x Delivering Business Intelligence with Microsoft SQL Server 2008 The Data Mart 29 Features of a Data Mart 30 Data Mart Structure 32 Snowflakes, Stars, and Analysis Services 40 Chapter 4 One-Stop Shopping—The Unified Dimensional Model 43 Online Analytical Processing 44 Building OLAP—Out of Cubes 45 Features of an OLAP System 48 Architecture 50 Disadvantages 52 Read-Only 52 The Unified Dimensional Model 53 Structure 53 Advantages 58 Tools of the Trade 60 Chapter 5 First Steps—Beginning the Development of Business Intelligence 61 The Business Intelligence Development Studio 62 Visual Studio 62 Navigating the Business Intelligence Development Studio 64 Business Intelligence Development Studio Options 78 The SQL Server Management Studio 82 The SQL Server Management Studio User Interface 82 Don Your Hardhat 88 Part II Defining Business Intelligence Structures Chapter 6 Building Foundations—Creating Data Marts 91 Data Mart 92 Who Needs a Data Mart Anyway? 92 Designing a Data Mart 95 Decision Makers’ Needs 95 Available Data 96 Data Mart Structures 97 Creating a Data Mart Using the SQL Server Management Studio 109 Creating a Data Mart Using the Business Intelligence Development Studio 117
  11. Contents xi Table Compression 130 Types of Table Compression 131 The Benefits of Integration 134 Chapter 7 Transformers—Integration Services Structure and Components 135 Integration Services 136 Package Structure 136 Package Items 149 Control Flow 149 Data Flow 183 Getting Under the Sink 231 Chapter 8 Fill ’er Up—Using Integration Services for Populating Data Marts 233 Package Development Features 234 Give It a Try 234 Programming in Integration Services Packages 241 Package Development Tools 250 Migrating from SQL Server 2000 DTS Packages 262 Putting Integration Services Packages into Production 263 Deploying Integration Services Packages 263 Change Data Capture 267 Change Data Capture Architecture 267 Loading a Data Mart Table from a Change Data Capture Change Table 272 Loading a Fact Table 277 Meanwhile, Back at the Unified Dimensional Model (UDM) 292 Part III Analyzing Cube Content Chapter 9 Cubism—Measures and Dimensions 295 Building in Analysis Services 296 Creating a Cube 296 Measures 302 Measure Groups 303 Made-up Facts—Calculated Measures 305 It Doesn’t Add Up—Measure Aggregates Other Than Sum 309 Dimensions 314 Managing Dimensions 314 Relating Dimensions to Measure Groups 320
  12. xii Delivering Business Intelligence with Microsoft SQL Server 2008 Types of Dimensions 321 Slowly Changing Dimensions 324 You Are Special 329 Chapter 10 Bells and Whistles—Special Features of OLAP Cubes 331 Where No Cube Has Gone Before 332 Deploying and Processing 333 Deploying from the Business Intelligence Development Studio 334 Deploying from the Analysis Services Deployment Wizard 340 Additional Cube Features 351 Linked Objects 351 The Business Intelligence Wizard 353 Key Performance Indicators 355 Actions 362 Partitions 365 Aggregation Design 380 Perspectives 385 Translations 386 More Sophisticated Scripting 387 Chapter 11 Writing a New Script—MDX Scripting 389 Terms and Concepts 390 Where Are We? 390 Getting There from Here 409 Putting MDX Scripting to Work 416 Cube Security 416 This Year to Last Year Comparisons and Year-to-Date Rollups 426 Extracting Data from Cubes 431 Chapter 12 Pulling It Out and Building It Up—MDX Queries 433 The MDX SELECT Statement 434 The Basic MDX SELECT Statement 435 Additional Tools for Querying 446 Additional Dimensions 457 Additional MDX Syntax 458 Operators 459 Functions 461 Can You Dig It? 465
  13. Contents xiii Part IV Mining Chapter 13 Panning for Gold—Introduction to Data Mining 469 What Is Data Mining? 470 Order from Chaos 470 Tasks Accomplished by Data Mining 474 Steps for Data Mining 480 Data Mining Algorithms 483 Microsoft Decision Trees 483 Microsoft Linear Regression 484 Microsoft Naïve Bayes 485 Microsoft Clustering 487 Microsoft Association Rules 488 Microsoft Sequence Clustering 490 Microsoft Time Series 491 Microsoft Neural Network 493 Microsoft Logistic Regression Algorithm 494 Grab a Pick Axe 494 Chapter 14 Building the Mine—Working with the Data Mining Model 495 Data Mining Structure 496 Data Columns 496 Data Mining Model 497 Training Data Set 497 Mining Model Viewer 512 Microsoft Decision Trees 513 Microsoft Naïve Bayes 517 Microsoft Clustering 521 Microsoft Neural Network 523 Microsoft Association Rules 524 Microsoft Sequence Clustering 526 Microsoft Time Series 527 Reading the Tea Leaves 528 Chapter 15 Spelunking—Exploration Using Data Mining 529 Mining Accuracy Chart 530 Column Mapping 531 Lift Chart 532
  14. xiv Delivering Business Intelligence with Microsoft SQL Server 2008 Profit Chart 537 Classification Matrix 538 Cross Validation 539 Mining Model Prediction 541 A Singleton Query 541 A Prediction Join Query 545 Data Mining Extensions 550 Prediction Query Syntax 550 Types of Prediction Queries 552 Special Delivery 558 Part V Delivering Chapter 16 On Report—Delivering Business Intelligence with Reporting Services 561 Reporting Services 562 Report Structure 563 Report Delivery 565 Report Serving Architecture 566 Report Server 566 The Parts of the Whole 568 Reporting Services Installation Considerations 571 Creating Reports Using the Tablix Data Region 573 A Tablix Data Region Created with the Table Template 574 A Tablix Data Region Created with the Matrix Template 591 A Tablix Data Region Created with the List Template 607 The Chart Data Region 617 The Gauge Data Region 634 Get Me the Manager 642 Chapter 17 Falling into Place—Managing Reporting Services Reports 643 Report Manager 644 Folders 644 The Report Manager 644 Deploying Reports Using the Report Designer 645 Uploading Reports Using Report Manager 647 Printing from Report Manager 653
  15. Contents xv Managing Reports on the Report Server 654 Security 654 Linked Reports 662 Report Caching 663 Execution Snapshots 666 Report History 667 Standard Subscriptions 668 Data-Driven Subscriptions 668 Ad Hoc Reporting 670 Report Model 670 Report Builder Basics 678 Putting It All Together 682 Chapter 18 Let’s Get Together—Integrating OLAPwith Your Applications 683 ADOMD NET 684 ADOMD NET Structure 684 ADOMD NET Example 687 Using Reporting Services Without the Report Manager 693 URL Access 693 Web Service Access 710 The Report Viewer Control 715 Ready-Made Solution 721 Chapter 19 Another Point of View—Excel Pivot Tablesand Pivot Charts 723 Excel 724 Creating Pivot Tables and Pivot Charts 724 Pivot Table 725 Pivot Chart 735 Great Capabilities, Great Opportunities 738 Index 741
  16. This page intentionally left blank
  17. Acknowledgments N o project of this size is the work of a single person. I need to thank a number of people for their assistance, professionalism, dedication, and support. So, a gianormous thank you … To Wendy Rinaldi, who allowed me to lean on her as part editor, part coach, part literary agent, and part psychoanalyst. Her professionalism, humor, understanding, and faith truly made this project possible. To Madhu Bhardwaj, who put up with my temperamental author moments and kept me on track and organized through two simultaneous book projects. To Robert Bruckner, who provided vital insight and product knowledge. To the rest of the McGraw-Hill Professional staff, who saw it through to the end and made sure there really was a book when all was said and done. To John Miller, who founded Superior Consulting Services as a place where people can grow and learn, produce solid technology solutions, serve customers, and have a good time to boot. To Jessica and Corey, my children, who allowed me time to pursue this passion. To my wife, Pam, who continues to be gracious in her understanding of my affliction with the writing bug. She has given generously of her time to proof and review this book and its Learn By Doing exercises. Her incredible attention to detail has made this a better product. Last, but certainly not least, to you, the reader, who plunked down your hard-earned cash for this purchase. I hope you view this as a helpful and informative guide to all of the truly exciting business intelligence features in SQL Server 2008. All the best, Brian Larson blarson@teamscs.com xvii
  18. xviii Delivering Business Intelligence with Microsoft SQL Server 2008 The Maximum Miniatures Databases and Other Supporting Materials All of the samples in this book are based on business scenarios for a fictional company called Maximum Miniatures, Inc. You can download the data, image files, and other supporting materials from the book’s web page on the McGraw-Hill Professional website. This download also includes the complete source code for all of the Learn By Doing activities and the applications demonstrated in the book. The download is found on this book’s web page at www.mhprofessional.com. Search for the book’s web page using the ISBN, which is 0071549447. Use the “Code” link to download the zip file containing the book’s material. Follow the instructions in the individual zip files to install or prepare each item as needed.
  19. Part # I Business Intelligence
Đồng bộ tài khoản