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