Tài liệu Professional SQL Server 2000 Data Warehousing with Analysis Services docx

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 been 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 when 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 mbenko@atgmn.com.
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-in-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-in-law Susie
and future brother-in-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 and 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
nd
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 pastime, 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+, Network+ 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 certifications 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-consulting-
inc.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 development, 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 Access 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 design 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-to-
business (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 long-
distance swimming and watching sporting events.
Summary of Contents
Introduction 1
Chapter 1: Analysis Services in SQL Server 2000 – An Overview 9
Chapter 2: Microsoft Analysis Services Architecture 35
Chapter 3: Analysis Services Tools 57
Chapter 4: DataMarts 75
Chapter 5: The Transactional System 97
Chapter 6: Designing the Data Warehouse and OLAP Solution 123
Chapter 7: Introducing Data Transformation Services (DTS) 159
Chapter 8: Advanced DTS Topics 203
Chapter 9: Building OLAP Cubes with Analysis Manager 229
Chapter 10: Introduction to MDX 287
Chapter 11: Advanced MDX Topics 317
Chapter 12: Using the PivotTable Service 349
Chapter 13: OLAP Services Project Wizard in English Query 365
Chapter 14: Programming Analysis Services 395
Chapter 15: English Query and Analysis Services 425
Chapter 16: Data Mining – An Overview 455
Chapter 17: Data Mining: Tools and Techniques 471
Chapter 18: Web Analytics 523
Chapter 19: Securing Analysis Services Cubes 555
Chapter 20: Tuning for Performance 585
Chapter 21: Maintaining the Data Warehouse 619
Index 659

Table of Contents
Introduction 1
Is This Book For You? 2
What Does the Book Cover? 3
What Do You Need to Use to Use This Book? 3
Conventions 3
Customer Support 4
How to Download the Sample Code for the Book 4
Errata 5
E-mail Support 5
p2p.wrox.com 5
Chapter 1: Analysis Services in SQL Server 2000 – An Overview 9
What is OLAP? 10
What are the Benefits of OLAP? 11
Who Will Benefit from OLAP? 12
What are the Features of OLAP? 13
Multidimensional Views 13
Calculation-Intensive 13
Time Intelligence 14
What is a Data Warehouse? 14
Data Warehouse vs. Traditional Operational Data Stores 15
Purpose and Nature 16
Data Structure and Content 17
Data Volume 18
Timeline 19
How Data Warehouses Relate to OLAP 19
Data Warehouses and Data Marts 19
Data Mining 22
Overview of Microsoft Analysis Services in SQL Server 2000 23
Features of Microsoft Analysis Services 25
New Features to Support Data Warehouses and Data Mining 25
The Foundation: Microsoft SQL Server 2000 26
Data Transformation Services (DTS) 26
Data Validation 27
Data Scrubbing 27
Data Migration 27
Data Transformation 28
DTS Components 28
Table of Contents
ii
Meta Data and the Repository 28
Decision Support Systems (DSS) 29
Analysis Server 29
PivotTable Service 29
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 41
Microsoft Repository in Data Warehousing 43
The Data Source 43
Operational Data Sources 43
Data Transformation Services 46
DTS Package Tasks 46
Defining DTS Package Components 47
The Data Warehouse and OLAP Database – The Object Architecture in
Analysis Services 49
Dimensional Databases 49
OLAP Cubes 51
Cube Partitions 52
Linked Cubes 52
OLAP Storage Architecture 53
MOLAP 53
ROLAP 53
HOLAP 54
OLAP Client Architecture 54
Summary 55
Chapter 3: Analysis Services Tools 57
Analysis Manager 57
Data Sources 59
Cubes 61
Shared Dimensions 63
Mining Models 63
Database Roles 63
Analysis Manager Wizards 64
Cube Editor 64
Dimension Editor 66
Table of Contents

iii
Enterprise Manager 68
DTS Package Designer 69
Query Analyzer 71
SQL Server Profiler 72
Summary 73
Chapter 4: Data Marts 75
What is a Data Mart? 76
How Does a Data Mart Differ from a Data Warehouse? 78
Who Should Implement a Data Mart Solution? 78
Development Approaches 79
Top-Down Approach 79
Bottom-Up Approach 80
Federated Approach 82
Managing the Data Mart 83
Selecting the Project Team 83
Data Mart Planning 84
Construction 84
Pilot Phase (Limited Rollout) 84
Initial Loading 84
Rollout 85
Operations and Maintenance 85
Data Mart Design 85
Design Considerations – Things to Watch For 85
Minimize Duplicate Measure Data 85
Allow for Drilling Across and Down 85
Build Your Data Marts with Compatible Tools and Technologies 86
Take into Account Locale Issues 86
Data Modeling Techniques 87
Entity Relation (ER) Models 87
Dimensional Modeling 88
Fact 88
Dimension 88
Data Cubes 90
Data Mart Schema 91
Star Schema 92
Snowflake Schema 93
Microsoft Data Warehousing Framework and Data Marts 93
Summary 94
Table of Contents
iv
Chapter 5: The Transactional System 97
The Relational Theory 97
Database 98
Table 98
Indexes 98
Views 99
Transactions 100
Relationships 100
One-to-Many Relationships 100
Many-to-Many Relationships 101
Normalization 101
First Normal Form (1NF) 101
Second Normal Form (2NF) 103
Third Normal Form (3NF) 104
Structured Query Language (SQL) 106
Data Definition Language (DDL) 106
Data Manipulation Language (DML) 107
Data Analysis Support in SQL 107
Online Transaction Processing (OLTP) 107
OLTP Design 108
Normalization 108
Transactions 110
Data Integrity 110
Indexing 110
Data Archiving 111
OLTP Reporting 111
Online Analytical Processing (OLAP) 112
OLTP vs. OLAP 112
FoodMart 2000 113
FoodMart – An Overview 114
The FoodMart OLTP Database 114
The Need for the Data Warehouse 115
The FoodMart Sample 115
Upgrading to SQL Server 2000 115
Summary 121
Chapter 6: Designing the Data Warehouse and OLAP Solution 123
Pre-requisites for a Successful Design 124
Customer Management 125
The Project Team 125
The Tools 127
Hardware 127
Software 127
Table of Contents

v
Designing the Data Warehouse 128
Analyzing the Requirements 129
Business Requirements 130
Architect's Requirements 131
Developer's Requirements 132
End-user Requirements 132
Design the Database 132
Be Aware of Pre-Calculations 133
Dimension Data Must Appropriately Exist in Dimension Tables 134
Indexed Views 135
Use Star or Snowflake Schema 135
How About Dimension Members? 136
Designing OLAP Dimensions and Cubes 138
Member Properties 139
Virtual Dimensions and Virtual Cubes 140
Designing Partitions 140
Meta Data and the Microsoft Repository 141
Data Source 141
OLAP Cubes 142
Dimensions 143
Individual Dimensions 143
Cube Partitions 143
Sample Model Meta Data 143
Data Loading and Transformation Strategy 150
Capturing the Data 150
Transforming the Data 153
Populating the Data Warehouse 154
OLAP Policy and Long-Term Maintenance and Security Strategy 154
What is the OLAP Policy, After All? 154
What Rules Does the OLAP Policy Contain? 154
User Interface and Querying Tools 157
Summary 157
Chapter 7: Introducing Data Transformation Services (DTS) 159
DTS Overview 160
How Will DTS Help Me? 160
Data Import and Export 163
Data Transformation 164
Database Objects Transfer 164
DTS Packages 165
Package Contents 165
Support for Multiple Data Sources 166
Data Transformations 168
Data Validation 169
Simple Validation 169
Complex Validation 170
Data Scrubbing 171

Xem chi tiết: Tài liệu Professional SQL Server 2000 Data Warehousing with Analysis Services docx