Tài liệu Wrox-professional sql server 2000 data warehousing with anal

Professional SQL Server 2000 Data Warehousing with Analysis Services Tony Bain Mike Benkovich Robin Dewson Sam Ferguson Christopher Graves Terrence J. Joubert Denny Lee Mark Scott Robert Skoglund Paul Turley Sakhr Youness Wrox Press Ltd.  Trademark Acknowledgements Wrox has endeavored to provide trademark information about all the companies and products mentioned in this book by the appropriate use of capitals. However, Wrox cannot guarantee the accuracy of this information. Credits Authors Tony Bain Mike Benkovich Robin Dewson Sam Ferguson Christopher Graves Terrence J. Joubert Denny Lee Mark Scott Robert Skoglund Paul Turley Sakhr Youness Technical Architect Catherine Alexander Technical Editors Alessandro Ansa Victoria Blackburn Allan Jones Gareth Oakley Douglas Paterson Author Agent Avril Corbin Project Administrator Chandima Nethisinghe Category Manager Sarah Drew Illustrations Natalie O'Donnell Index Fiona Murray Technical Reviewers Christine Adeline Sheldon Barry Michael Boerner Jim W. Brzowski James R. De Carli Michael Cohen Paul Churchill Chris Cr ane Edgar D'Andrea John Fletcher Damien Foggon Hope Hatfield Ian Herbert Brian Hickey Terrence J. Joubert Brian Knight Don Lee Dianna Leech Gary Nicholson J. Boyd Nolan, PE Sumit Pal Ryan Payet Tony Proudfoot Dan Read Trevor Scott Charles Snell Jr. John Stallings Chris Thibodeaux Maria Zhang Cover Dawn Chellingworth Production Manager Liz Toy Proof Reader Chris Smith Production Coordinator Emma Eato About the Authors Tony Bain Tony Bain (MCSE, MCSD, MCDBA) is a senior database consultant for SQL Services in Wellington, New Zealand. While Tony has experience with various database platforms, such as RDB and Oracle, for over four years SQL Server has been the focus of his attention. During this time he has b een responsible for the design, development and administration of numerous SQL Server -based solutions for clients in such industries as utilities, property, government, technology, and insurance. Tony is passionate about database technologies especially w hen they relate to enterprise availability and scalability. Tony spends a lot of his time talking and writing about various database topics and in the few moments he has spare Tony hosts a SQL Server resource site (www.sqlserver.co.nz ). Dedication I must thank Linda for her continued support while I work on projects such as this, and also our beautiful girls Laura and Stephanie who are my motivation. Also a big thank -you to Wrox for the opportunity to participate in the interesting projects that have been thrown my way, with special thanks in particular to Doug, Avril, and Chandy. Mike Benkovich Mike Benkovich is a partner in the Minneapolis -based consulting firm Applied Technology Group. Despite his degree in Aerospace Engineering, he has found that developing software is far more interesting and rewarding. His interests include integration of relational databases within corporate models, application security and encryption, and large-scale data replication systems. Mike is a proud father, inspired husband, annoying brother, and dedicated son who thanks his lucky stars for having a family that gives freely their support during this project. Mike can be reached at [email protected]. Robin Dewson Robin started out on the Sinclair ZX80 but soon progressed and built the basis of a set of programs for his father's post office business on later Sinclair computers. He ended up studying computers at the Scottish College of Textiles where he was instilled with the belief that mainframes were the future. After many sorry years, he eventually saw the error of his ways, and started to use Clipper, FoxPro, and then Visual Basic. Robin is currently working on a system called "Vertigo", replacing the old trading system called "Kojak", and is glad to be able to give up sucking lollipops and looking forward to allowing his hair to grow back on his head. He has been with a large US Investment bank in the City of London for over five years and he owes a massive debt to Annette "They wouldn't put me in charge if I didn't know what I was doing" Kelly, Daniel "Dream Sequence" Tarbotton, Andy "I don't really know, I've only been here for a week", and finally, Jack "You will never work in the City again" Mason. Thanks to everyone at Wrox, but especially Cath Alexander, Cilmara Lion, Sarah Drew, Douglas Paterson, Claire Brittle, Ben Egan, Avril Corbin, Rob Hesketh, and Chandy Nethisinghe for different reasons throughout the time, but probably most importantly for introducing me to Tequila slammers (!). Also thanks to my mum and dad for finding and sending me to the two best colleges ever and pointing me on the right road, my father -i n-law who until he passed away was a brilliant inspiration to my children, my mother -in-law for once again helping Julie with the children. Also a quick thank-you from my wife, to Charlie and Debbie at Sea Palling for selling the pinball machine!!! But my biggest thanks as ever go to Julie, the most perfect mother the kids could have, and to Scott, Cameron, and Ellen for not falling off the jet -ski when I go too fast. 'Up the Blues' Sam Ferguson Sam Ferguson is an IT Consultant with API Software, a growing IT Solutions company based in Glasgow, Scotland. Sam works in various fields but specializes in Visual Basic, SQL Server, XML, and all things .Net. Sam has been married to the beautiful Jacqueline for two months and happily lives next door to sister -i n-law Susie and future brother -i n-law Martin. Dedication I would like to dedicate my contribution to this book to Susie and Martin, two wonderful people who will have a long and happy life together. Christopher Graves Chris Graves is President of RapidCF, a ColdFusion development company in Canton Connecticut (www.rapidcf.com). Chris leads projects with Oracle 8i a nd SQL Server 2000 typically coupled to web-based solutions. Chris earned an honors Bachelor of Science degree from the US Naval Academy (class of 93, the greatest class ever), and was a VGEP graduate scholar. After graduating, Chris served as a US Marine Corps Officer in 2 n d Light Armored Reconnaissance Battalion, and 2 nd ANGLICO where he was a jumpmaster. In addition to a passion for efficient CFML, Chris enjoys skydiving and motorcycling, and he continues to lead Marines in the Reserves. His favorite pas time, however, is spending time with his two daughters Courtney and Claire, and his lovely wife Greta. Terrence J. Joubert Terrence is a Software Engineer working with Victoria Computer Services (VCS), a Seychelles -based IT solutions provider. He also works as a freelance Technical Reviewer for several publishing companies. As a developer and aspiring author, Terrence enjoys reading about and experimenting with new technologies, especially the Microsoft .Net products. He is currently doing a Bachelor of Science degree by correspondence and hopes that his IT career spans development, research, and writing. When he is not around computers he can be found relaxing on one of the pure, white, sandy beaches of the Seychelles or hiking along the green slopes of its mountains. He describes himself as a Libertarian –he believes that humans should mind their own business and just leave their fellow brothers alone in a culture of Liberty. Dedication This work is the starting point of a very long journey. I dedicate it to: My mother who helped me get started on my first journey to dear life, my father who teaches me independence, and motivation to achieve just anything a man wills along the path of destiny, and Audrey, for all the things between us that are gone, the ones are here now, and those that are to come. Thanks for being a great friend. Denny Lee Denny Lee is the Lead OLAP Architect at digiMine, Inc. (Bellevue, WA), a leading analytic services company specializing in data warehousing, data mining, and business intelligence. His primary focus is delivering powerful, scalable, enterprise-level OLAP solutions that provide customers with the business intelligence insights needed to act on their data. Before joining digiMine, Lee was as a Lead Developer at the Microsoft Corporation where he built corporate reporting solutions utilizing OLAP services against corporate data warehouses, and took part in developing one of the first OLAP solutions. Interestingly, he is a graduate of McGill University in Physiology and prior to Microsoft, was a Statistical Analyst at the Fred Hutchison Cancer Research Center in one of the largest HIV/AIDS research projects. Dedication Special thanks to my beautiful wife, Hua Ping, for enduring the hours I spend of working and writing...and loving me all the same. Many thanks to the kind people at Wrox Press to produced this book. Mark Scott Mark Scott serves as a consultant for RDA, a provider of advanced technology consulting services. He develops multi-tier, data-centric web applications. He implements a wide variety of Microsoft-based technologies, with special emphasis on SQL Server and Analysis Services. He is a Microsoft Certified System Engineer + Internet, Solution Developer, Database Administrator, and Trainer. He holds A+, N etwork+ and CTT+ certifications from COMPTIA. Robert Skoglund Robert is President and Managing Director of RcS Consulting Services, Inc., a Business Intelligence, Database Consulting, and Training Company based in Tampa, Florida, USA. Robert has over 10 years experience developing and implementing a variety of business applications using Microsoft SQL Server (version 1.0 through version 2000), and is currently developing data warehouses using Microsoft’s SQL Server and Analysis Services. Robert’s certificat ions include Microsoft’s Certified Systems Engineer (1997), Solution Developer (1995), and Trainer (1994). He is also an associate member of The Data Warehousing Institute. Additionally, Robert provides certified training services to Microsoft Certified Technical Education Centers nationwide and internationally. Robert also develops customized NT and SQL courses and presentations for both technical and managerial audiences. Robert is proud to be an Eagle Scout and an avid chess player. He can be reached at rskoglund@rcs -consultinginc.com or by visiting www.rcs-consulting-inc.com. Paul Turley Paul is a Senior Instructor and Consultant for SQL Soft+ Training and Consulting in Beaverton, Oregon and Bellevue, Washington. He specializes in database solution d evelopment, software design, programming, and project management frameworks. He has been working with Microsoft development tools including Visual Basic, SQL Server and Access since 1994. He was a contributing author for the Wrox Press book, Professional A ccess 2000 Programming and has authored several technical courseware publications. A Microsoft Certified Solution Developer (MCSD) since 1996, Paul has worked on a number of large-scale consulting projects for prominent clients including HP, Nike, and Microsoft. He has worked closely with Microsoft Consulting Services and is one of few instructors certified to teach the Microsoft Solution Framework for solution design and project management. Paul lives in Vancouver, Washington with his wife, Sherri, and four children –Krista, 4; Sara, 5; Rachael, 10; and Josh, 12; a dog, two cats, and a bird. Somehow, he finds time to write technical publications. He and his family enjoy camping, cycling and hiking in the beautiful Pacific Northwest. He and his son also d esign and build competition robotics. Dedication Thanks most of all to my wife, Sherri and my kids for their patience and understanding. To the staff and instructors at SQL Soft, a truly unique group of people (I mean that in the best possible way). It's good to be part of the team. Thanks to Douglas Laudenschlager at Microsoft for going above and beyond the call of duty. Sakhr Youness Sakhr Youness is a Professional Engineer (PE) and a Microsoft Certified Solution Developer (MCSD) and Product Specialist ( MCPS) who has extensive experience in data modeling, client-server, database, and enterprise application development. Mr. Youness is a senior software architect at Commerce One, a leader in the business-tobusiness (B2B) area. He is working in one of the largest projects for Commerce One involving building an online exchange for the auto industry. He designed and developed or participated in developing a number of client-server applications related to the automotive, banking, healthcare, and engineering industries. Some of the tools used in these projects include: Visual Basic, Microsoft Office products, Active Server Pages (ASP), Microsoft Transaction Server (MTS), SQL Server, Java, and Oracle. Mr. Youness is a co-author of SQL Server 7.0 Programming Unleashed which was published by Sams in June 1999. He also wrote the first edition of this book, Professional Data Warehousing with SQL Server 7.0 and OLAP Services . He is also proud to say that, in this edition, he had help from many brilliant authors who helped write numerous chapters of this book, adding to it a great deal of value and benefit, stemming from their experiences and knowledge. Many of these authors have other publications and, in some cases, wrote books about SQL Server. Mr. Youness also provided development and technical reviews of many books for MacMillan Technical Publishing and Wrox Press. These books mostly involved SQL Server, Oracle, Visual Basic, and Visual Basic for Applications (VBA). Mr. Youness loves learning new technologies and is currently focused on using the latest innovations in his projects. Mr. Youness enjoys his free time with his lovely wife, Nada, and beautiful daughter, Maya. He also enjoys longdistance swimming and watching sporting events. Table of Contents Introduction Is This Book For You? What Does the Book Cover? What Do You Need to Use to Use This Book? 1 2 3 3 Conventions 3 Customer Support 4 How to Download the Sample Code for the Book Errata E- mail Support p2p.wrox.com Chapter 1: Analysis Services in SQL Server 2000 – An Overview What is OLAP? What are the Benefits of OLAP? Who Will Benefit from OLAP? What are the Features of OLAP? 4 5 5 5 9 10 11 12 13 Multidimensional Views 13 Calculation-Intensive Time Intelligence 13 14 What is a Data Warehouse? Data Warehouse vs. Traditional Operational Data Stores Purpose and Nature Data Structure and Content Data Volume Timeline How Data Warehouses Relate to OLAP Data Warehouses and Data Marts Data Mining Overview of Microsoft Analysis Services in SQL Server 2000 Features of Microsoft Analysis Services New Features to Support Data Warehouses and Data Mining 14 15 16 17 18 19 19 19 22 23 25 25 The Foundation: Microsoft SQL Server 2000 26 Data Transformation Services (DTS) 26 Data Validation Data Scrubbing Data Migration Data Transformation DTS Components 27 27 27 28 28 Table of Contents Meta Data and the Repository 28 Decision Support Systems (DSS) 29 Analysis Server PivotTable Service Analysis Manager 30 Client Architecture 31 Summary 32 Chapter 2: Microsoft Analysis Services Architecture 35 Overview 35 The Microsoft Repository 39 Architecture of the Microsoft Repository Microsoft Repository in Data Warehousing The Data Source Operational Data Sources Data Transformation Services DTS Package Tasks Defining DTS Package Components The Data Warehouse and OLAP Database – The Object Architecture in Analysis Services Dimensional Databases OLAP Cubes Cube Partitions Linked Cubes OLAP Storage Architecture 41 43 43 43 46 46 47 49 49 51 52 52 53 MOLAP ROLAP 53 53 HOLAP 54 OLAP Client Architecture 54 Summary 55 Chapter 3: Analysis Services Tools 57 Analysis Manager 57 Data Sources Cubes 59 61 Shared Dimensions Mining Models Database Roles 63 63 63 Analysis Manager Wizards Cube Editor Dimension Editor ii 29 29 64 64 66 Table of Contents Enterprise Manager 68 DTS Package Designer Query Analyzer 69 71 SQL Server Profiler 72 Summary Chapter 4: Data Marts What is a Data Mart? How Does a Data Mart Differ from a Data Warehouse? Who Should Implement a Data Mart Solution? Development Approache s Top-Down Approach Bottom-Up Approach Federated Approach Managing the Data Mart Selecting the Project Team Data Mart Planning Construction Pilot Phase (Limited Rollout) Initial Loading Rollout Operations and Maintenance Data Mart Design Design Considerations – Things to Watch For... 73 75 76 78 78 79 79 80 82 83 83 84 84 84 84 85 85 85 85 Minimize Duplicate Measure Data 85 Allow for Drilling Across and Down Build Your Data Marts with Compatible Tools and Technologies Take into Account Locale Issues 85 86 86 Data Modeling Techniques Entity Relation (ER) Models Dimensional Modeling Fact Dimension Data Cubes 87 87 88 88 88 90 Data Mart Schema 91 Star Schema Snowflake Schema 92 93 Microsoft Data Warehousing Framework and Data Marts 93 Summary 94 iii Table of Contents Chapter 5: The Transactional System The Relational Theory Database Table Indexes Views Transactions Relationships One-to-Many Relationships Many -to-Many Relationships Normalization First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) 97 98 98 98 99 100 100 100 101 101 101 103 104 Structured Query Language (SQL) 106 Data Definition Language (DDL) Data Manipulation Language (DML) Data Analysis Support in SQL 106 107 107 Online Transaction Processing (OLTP) OLTP Design 107 108 Normalization Transactions Dat a Integrity 108 110 110 Indexing Data Archiving 110 111 OLTP Reporting 111 Online Analytical Processing (OLAP) 112 OLTP vs. OLAP 112 FoodMart 2000 113 FoodMart – An Overview The FoodMart OLTP Database The Need for the Data Warehouse The FoodMart Sample Upgrading to SQL Server 2000 Summary Chapter 6: Designing the Data Warehouse and OLAP Solution Pre-requisites for a Successful Design Customer Management The Project Team The Tools Hardware Software iv 97 114 114 115 115 115 121 123 124 125 125 127 127 127 Table of Contents Designing the Data Warehouse 128 Analyzing the Requirements 129 Business Requirements Architect's Requirements 130 131 Developer's Requirements End-user Requirements 132 132 Design the Database 132 Be Aware of Pre-Calculations Dimension Data Must Appropriately Exist in Dimension Tables 133 134 Indexed Views Use Star or Snowflake Schema 135 135 How About Dimension Members? Designing OLAP Dimensions and Cubes Member Properties 136 138 139 Virtual Dimensions and Virtual Cubes Designing Partitions 140 140 Meta Data and the Microsoft Repository 141 Data Source 141 OLAP Cubes Dimensions Individual Dimensions 142 143 143 Cube Partitions Sample Model Meta Data 143 143 Data Loading and Transformation Strategy 150 Capturing the Data 150 Transforming the Data Populating the Data Warehouse 153 154 OLAP Policy and Long-Term Maintenance and Security Strategy What is the OLAP Policy, After All? What Rules Does the OLAP Policy Contain? User Interface and Querying Tools Summary Chapter 7: Introducing Data Transformation Services (DTS) DTS Overview 154 154 154 157 157 159 160 How Will DTS Help Me? 160 Data Import and Export Data Transformation 163 164 Database Objects Transfer 164 DTS Packages Package Contents Support for Multiple Data Sources Data Transformations Data Validation Simple Validation Complex Validation Data Scrubbing 165 165 166 168 169 169 170 171 v Table of Contents Data Transformation Planning your Transformations Data Migration Using the DTS Package Anatomy of the DTS Package 172 173 173 174 DTS Connection 175 DTS Task DTS Step/Workflow 175 178 Storing the DTS Package 179 How DTS Packages are Stored in SQL Server 179 DTS Package Storage in the Repository DTS Package Storage in Visual Basic Files DTS Package Storage in COM-Structured Files 180 180 181 Creating a DTS package Package Settings Building Tasks Saving the Package Executing the package Using the dtsrun utility 182 183 186 196 197 199 Completing the FoodMart package 201 Summary 201 Chapter 8: Advanced DTS Topics Data Driven Query (DDQ) DTS Lookup The Analysis Services Processing Task How Can You Use It? Benefits of Using the Analysis Services Processing Task 203 204 209 211 211 214 Data Mining Prediction Task 214 OLTP to Star Schema through DTS 217 OLTP/Star Package Design Multiple Connections and Single Connections Package Transactions 218 219 220 Loading the Customer Dimension Data 220 Building the Time Dimension Building the Geography Dimension Building the Product Dimension 221 222 222 Building the Sales Fact Data 223 DTS Performance Issues vi 171 224 Using ActiveX Scripts Using Ordinal Values when Referencing Columns Using Data Pump and Data Transformations 224 224 224 Using Data Driven Queries versus Transformations Using Bulk Inserts and BCP 224 224 Using DTS Lookups Other SQL Server Techniques 224 225 Table of Contents DTS Security Owner password User Password 225 225 225 Viewing Package Meta Data 225 Summary 227 Chapter 9: Building OLAP Cubes with Analysis Manager Basic Topics Create a New OLAP Database Data Sources Building Dimensions 229 230 230 231 232 Regular Dimensions Virtual Dimensions 232 233 Parent-Child Dimensions Dimension Wizard 233 234 Regular Dimension with Member Properties Building a Virtual Dimension Building a Parent -Child dimension 238 240 241 Viewing Dimension Meta Data Browsing a Dimension 241 242 Processing Dimensions 243 Processing 243 Building a Cube 244 Design Storage and Processing 246 More on Processing Cubes Viewing your Cube Meta Data Browsing your Cubes 247 248 249 Advanced Topics 250 Dimension Editor 251 Dimension Tree Pane Schema 251 253 Data Calculations at the Member Level Grouping Levels 253 254 258 Cube Editor 261 Schema Tab 262 Data Tab Cube Pane 263 263 Dimension Measures Calculated Members 263 264 267 Calculated Cells Actions 269 272 Named Sets Drillthrough Virtual Cubes 273 274 275 Partitions 278 vii Table of Contents Dimension Properties 279 Dimension Level Properties 282 Summary 285 Chapter 10: Introduction to MDX How Good is SQL? Could SQL Tricks Do the Job? Basic MDX Definitions 287 288 288 292 Tuple 292 Axis Cellset Cell 292 293 293 Slicer 293 MDX Basics 294 Notes on the Syntax 294 On MDX Functions 295 On Language Syntax 295 A Simple MDX Query 296 MDX Representation of OLAP Schema 298 Using Square Brackets Using the Period in Schema Representation Establishing Unique Names 298 299 299 Dimensions and Measures Hierarchies 299 299 Levels Members Member Properties 299 300 300 More On MDX Queries 300 Constructing MDX Sets 300 Separation of Set Elements (The Comma) Identifying Ranges (Colon) Identifying the Set Members with the .Members Operator 300 301 302 CrossJoin() The * (asterisk) Operator 302 304 Filter() Function The Order() Function 305 306 Dimensional Calculations in MDX Query -Defined Calculated Members (With Operator) Non Query -Defined Calculated Members (CREATE MEMBER) Named Sets Axis Numbering and Ordering Selecting Member Properties Summary viii 308 309 312 312 313 314 315 Table of Contents Chapter 11: Advanced MDX Topics 317 Advanced MDX Statement Topics 317 Retrieving Cell Properties The Format String MDX Cube Slicers Beefing Up MDX Cube Slicers Joining Cubes in the FROM Clause Empty FROM Clause Using Outer References in an MDX Query Using Property Values in MDX Queries Overriding the WHERE Clause Default Hierarchy and Member Empty Cells 317 319 323 324 324 325 325 326 326 327 328 NULLs, Invalid Members, and Invalid Results 328 The COALESCEEMPTY Function Counting Empty Cells 330 331 Empty Cells in a Cellset and the NON EMPTY Keyword More on Named Sets and Calculated Members MDX Expressions 331 332 333 Set Value Expressions 334 Drilling by Member 335 Drilling by Level Preserving State During UI Operations 337 339 Conditional Expressions 339 If Clause 340 Simple Case Expression Searched Case Expression 341 342 The MDX Sample Application 343 Summary 346 Chapter 12: Using the PivotTable Service 349 Introducing the PivotTable Service 349 Quick Primer on Data Access Technologies 350 Usage of the PivotTable Service OLE DB For OLAP Multidimensional Expressions 351 351 352 Data Retrieval 352 ActiveX Data Objects, Multi Dimensional ADO MD 353 353 The ADO MD Object Model 353 The Database Structural View Example Working Through a Structural View How It Is Done 353 354 354 ix Table of Contents The PivotTable View PivotTable Service and Excel Implementing OLAP- Centric PivotTables in Excel Implementing OLAP- Centric PivotTables in Excel VBA The Code Summary Chapter 13: OLAP Services Project Wizard in English Query What is the Project Wizard? Development and User Installation Requirements 356 356 360 360 363 365 366 367 Before You Begin 368 Creating a Model 369 Entities Integrated Development Environment Features 370 371 Relationships Synonyms Semantics 372 372 372 FoodMart Sales Project The Model Test Window 374 377 Model Test Window Features Regression Tests 378 379 Analysis Page Suggestion Wizard 380 380 Follow-up Questions Adding and Modifying Phrases Test the Query 381 382 385 Check IIS Server Extensions 387 Building the Application Deployment Test the Solution Summary Chapter 14: Programming Analysis Services ADO: The History and Future of Data Access Case Study 388 388 391 393 395 395 396 User Audience 396 Business Requirements and Vision Development Tools and Environment Proposed Solution 396 397 397 Data Storage and Structure 398 Programming Office Web Components 399 Programming the PivotTable Control Programming the Chart Control 401 403 Programming with ADO MD x 356 405 Cellset Object 405 CubeDef Object 412 Table of Contents Managing OLAP Objects with DSO Meta Data Scripter Utility Summary Chapter 15: English Query and Analysis Services 414 423 423 425 Programming English Query 426 English Query Engine Object Model 426 Solution Components Question Builder Object Model 429 433 The Question Builder Control 433 Building the English Query Application 437 Submitting a Question 440 Starting a New Session List Item Form 446 446 Executing a Query Using the Question Builder Tying Up Loose Ends 447 447 448 Test the Solution 448 Submit a Question 449 Execute the Query Clarify a Request 450 450 Build Questions 451 Summary Chapter 16: Data Mining – An Overview Data Mining Historical Perspective Why is Data Mining Important? Why Now? 453 455 456 456 457 458 Inexpensive Data Storage 458 Affordable Processing Power Data Availability Off -the-Shelf Data Mining Tools 459 459 459 Definition 459 Operational Data Store vs. Data Warehousing 460 OLAP vs. Data Mining Data Mining Models 460 460 Data Mining Algorithms Hypothesis Testing vs. Knowledge Discovery Directed vs. Undirected Learning 461 463 463 How is Data Mining Used? How Data Mining Works 463 464 The Cycle of Data Mining Understand the Situation 464 465 Select and Build a Model Run the Analysis 465 465 xi Table of Contents Take Action Measure the Results Repeat Tools for Data Mining 465 465 465 465 Decision Trees Clustering Analysis OLE DB for Data Mining 466 466 466 Third Party Tools 466 Success Factors for Data Mining Projects The situation Create a plan Delivering on the plan Summary Chapter 17: Data Mining: Tools and Techniques Data Mining Approaches 467 468 468 469 469 471 472 FoodMart 2000 472 Employees Customers Product 472 473 473 Sales Promotions 474 475 Stores 475 What Can We Learn? 475 Customer Sales Focus 476 Store Performance Focus Price Performance Focus 476 476 Practical Data Mining 476 Clustering 478 Strengths Weaknesses 479 479 Decision Trees 480 How Decision Trees Work 480 Strengths Weaknesses 481 481 The Setup Building An OLAP Clustering Data Mining Model xii 477 How Clustering Analysis Works 482 482 Open Analysis Services Manager Select The Source Of Data For Our Analysis Select The Source Cube 482 484 484 Choose The Algorithm For This Mining Model Define The Key To Our Case 485 486 Select Training Data Save The Model Process The Model 486 487 488 Analyze The Results What We Learned 488 490