Practical Business Intelligence with SQL Server 2005

by Hancock, John C.; Toren, Roger

• ISBN13: 9780321356987
• ISBN10: 0321356985
• Edition: 1st
• Format: Paperback
• Copyright: 2006-08-28
• Publisher: Addison-Wesley Professional

Summary

Design, Build, and Manage High-Value BI Solutions with SQL Server 2005 In this book, two of Microsoftrs"s leading consultants illustrate how to use SQL Server 2005 Business Intelligence (BI) technologies to solve real-world problems in markets ranging from retail and finance to healthcare. Drawing on extensive personal experience with Microsoftrs"s strategic customers, John C. Hancock and Roger Toren offer unprecedented insight into BI systems design and step-by-step best practices for implementation, deployment, and management. Hancock and Toren introduce practical BI concepts and terminology and provide a concise primer on the Microsoft BI platform. Next, they turn to the heart of the book-constructing solutions. Each chapter-length case study begins with the customerrs"s business goals, and then guides you through detailed data modeling. The case studies show how to avoid the pitfalls that derail many BI projects. Yours"ll translate each model into a working system and learn how to deploy it into production, maintenance, and efficient operation. Whether yours"re a decision-maker, architect, developer, or DBA, this book brings together all the knowledge yours"ll need to derive maximum business value from any BI project. bull; Leverage SQL Server 2005 databases, Integration Services, Analysis Services, and Reporting Services bull; Build data warehouses and extend them to support very large databases bull; Design effective Analysis Services databases bull; Ensure the superior data quality your BI system needs bull; Construct advanced enterprise scorecard applications bull; Use data mining to segment customers, cross-sell, and increase the value of each transaction bull; Design real-time BI applications bull; Get hands-on practice with SQL Server 2005rs"s BI toolset

Author Biography

John C. Hancock, Senior Consultant with Microsoft Consulting Services (MCS) in Toronto, Canada Roger Toren, Principal Consultant with MCS in Vancouver

Table of Contents

Acknowledgments	p. xvi
About the Author	p. xvii
Preface	p. xviii
Introduction to Business Intelligence	p. 11
What Is Business Intelligence?	p. 1
Transaction Systems and the Search for Information	p. 2
Why OLTP Reporting and Analysis Fails to Deliver	p. 3
Data Warehouses	p. 5
The Data Warehouse Design	p. 5
Time and the Data Warehouse	p. 7
Getting Data into the Data Warehouse	p. 8
OLAP to the Rescue	p. 10
Loading Information into OLAP Databases	p. 11
Getting Information out of OLAP Databases	p. 11
Why Is OLAP So Fast?	p. 12
Dimensional Modeling Concepts	p. 14
Hierarchies	p. 15
Stars and Snowflakes	p. 16
Choosing Between Star and Snowflake for a Dimension	p. 17
Using Surrogate Keys	p. 18
A Practical Approach to Dimensional Modeling	p. 19
Designing a Dimensional Data Model	p. 19
Making a List of Candidate Attributes and Dimensions	p. 20
Making a List of Candidate Measures	p. 21
Grouping the Measures with the Same Grain into Fact Tables	p. 21
Fitting the Source Data to the Model	p. 22
Business Intelligence Projects	p. 23
A Business Value-Based Approach to BI Projects	p. 23
Business Intelligence Project Pitfalls	p. 24
Summary	p. 28
Introduction to SQL Server 2005	p. 29
SQL Server Components	p. 29
Development and Management Tools	p. 29
Deploying Components	p. 31
SQL Server Database Engine	p. 31
Management	p. 32
Scheduled Execution of Processes	p. 33
Security	p. 33
Availability	p. 34
Scalability	p. 35
Support for Very Large Databases	p. 35
Integration Services	p. 35
Designing Packages	p. 37
Data Quality	p. 41
Deploying and Configuring Packages	p. 41
Analysis Services	p. 42
Analysis Services Architecture	p. 42
Development Environment	p. 44
Managing and Securing Analysis Services	p. 44
The Unified Dimensional Model	p. 46
Support for Large and Mission-Critical BI Solutions	p. 47
Reporting Services	p. 48
Reporting Architecture	p. 48
Reporting Services Features	p. 51
Managing and Securing Reports	p. 54
Data Mining	p. 55
Data Mining Architecture	p. 56
Data Mining Features	p. 59
Other Technologies	p. 60
Full-Text Search	p. 60
Notification Services	p. 61
Service Broker	p. 61
Summary	p. 61
Building a Data Warehouse	p. 63
Business Problem	p. 63
Problem Statement	p. 63
Solution Overview	p. 64
Business Requirements	p. 64
High-Level Architecture	p. 65
Business Benefits	p. 65
Data Model	p. 66
What Process Will We Be Focusing On?	p. 66
What Level of Detail Do We Need?	p. 67
What Are the Ways of Looking At the Information?	p. 68
What Are We Measuring?	p. 73
Technical Solution	p. 77
Building Dimension Tables	p. 78
Building Fact Tables	p. 83
Securing the Data Warehouse	p. 87
Managing the Solution	p. 90
Deployment	p. 90
Maintenance	p. 92
Operations	p. 93
Next Steps	p. 95
Extending the Manufacturing Solution	p. 96
Using the BI Development Studio to Generate the Data Warehouse	p. 96
Summary	p. 97
Building a Data Integration Process	p. 99
Business Problem	p. 100
Problem Statement	p. 100
Solution Overview	p. 100
Business Requirements	p. 101
High-Level Architecture	p. 101
Business Benefits	p. 102
Data Model	p. 103
The Budget Data	p. 103
Working with Extended Measures	p. 104
Technical Solution	p. 105
Getting Started with Integration Services	p. 106
Data Sources and Destinations	p. 106
Loading the Dimensions	p. 108
Loading the Fact Table	p. 116
Loading the Budget Information from Excel	p. 119
Loading Multiple Sets of Data	p. 125
Managing the Solution	p. 128
Protecting Your Source Code	p. 128
Deployment	p. 128
Deploying Packages to a New Server	p. 133
Security	p. 134
Maintenance	p. 134
Operations	p. 135
Next Steps	p. 137
Data Quality	p. 137
Scaling Your Solution	p. 138
Other Transformations	p. 138
Control Flow Tasks	p. 139
Summary	p. 139
Building an Analysis Services Database	p. 141
Business Problem	p. 141
Problem Statement	p. 141
Solution Overview	p. 142
Business Requirements	p. 142
High-Level Architecture	p. 143
Business Benefits	p. 145
Data Model	p. 145
How Do We Handle "On-Time" Shipments?	p. 145
How Late Were These Shipments, Anyway?	p. 147
What Doesn't Work Well in a Cube?	p. 148
Technical Solution	p. 148
Getting Started with Analysis Services	p. 148
Building the Initial Cube	p. 151
Setting Up the Dimensions	p. 157
Modifying the Cube	p. 161
Managing the Solution	p. 169
Deployment	p. 169
Maintenance	p. 172
Operations	p. 173
Next Steps	p. 176
Extending the Manufacturing Solution	p. 176
Accessing the Information	p. 176
Using BI Development Studio in Online Mode	p. 176
Summary	p. 177
Reporting	p. 179
Business Problem	p. 179
Problem Statement	p. 179
Solution Overview	p. 180
Business Requirements	p. 180
High-Level Architecture	p. 181
Business Benefits	p. 184
Data Model	p. 184
Who Does the Work?	p. 185
What Are They Working On?	p. 186
How Do We Measure What Work They Are Doing?	p. 187
How Much Work Will They Be Doing in the Future?	p. 188
Technical Solution	p. 189
Getting Started with Reporting Services	p. 189
Accessing Reports	p. 193
Giving Users What They Want	p. 195
Presenting Information	p. 197
Securing the Information	p. 202
Accessing the Database	p. 204
Subscribing to Reports	p. 205
Managing the Solution	p. 208
Deployment	p. 209
Maintenance	p. 210
Operations	p. 211
Next Steps	p. 212
Adding Code to Reports Using .NET	p. 212
Using Report Builder for End-User Reporting	p. 212
Supporting Multiple Languages in a Report	p. 213
Summary	p. 213
Data Quality	p. 215
Business Problem	p. 215
Problem Statement	p. 216
Solution Overview	p. 216
Business Requirements	p. 217
High-Level Architecture	p. 217
Business Benefits	p. 218
Data Model	p. 218
Technical Solution	p. 220
Merging and De-duplicating Customer Data	p. 220
Dealing with Missing Dimension Members	p. 229
Adding Row Counts for Auditing	p. 239
Preventing Bad Data at the Source	p. 240
Managing the Solution	p. 241
Deployment	p. 241
Operations	p. 241
Next Steps	p. 242
Summary	p. 243
Managing Changing Data	p. 245
Business Problem	p. 245
Problem Statement	p. 246
Solution Overview	p. 246
Business Requirements	p. 246
High-Level Architecture	p. 247
Business Benefits	p. 247
Data Model	p. 248
Managing Dimensions When History Doesn't Matter	p. 249
Managing Dimensions to Preserve History	p. 250
Technical Solution	p. 253
Updating Dimensions with Changing Attributes	p. 253
Preserving Information for Dimensions with Historical Attributes	p. 256
Detecting Changes in Source Dimension Data	p. 260
Inserting Facts with Surrogate Key Lookups from Changing Dimensions	p. 261
Dealing with Very Wide Dimension Tables	p. 263
Analysis Services Dimension Changes	p. 263
Managing the Solution	p. 266
Deployment	p. 267
Operations	p. 267
Next Steps	p. 269
Dealing with Updated Facts	p. 269
Historical Snapshots for Reporting	p. 270
Summary	p. 270
Scorecards	p. 271
Business Problem	p. 271
Problem Statement	p. 272
Solution Overview	p. 272
Business Requirements	p. 272
High-Level Architecture	p. 273
Business Benefits	p. 275
Data Model	p. 275
How Good Are We at Handling Calls?	p. 276
How Are Our Crime Reduction Initiatives Going?	p. 278
How Are We Improving Professional Standards?	p. 279
What Does the Public Think?	p. 280
Technical Solution	p. 281
Analysis Services Database	p. 281
Building the Reports	p. 290
Building the Scorecard Portal	p. 295
Managing the Solution	p. 302
Deployment	p. 302
Security	p. 303
Maintenance	p. 305
Operations	p. 306
Next Steps	p. 306
Microsoft Office Tools for Scorecards	p. 307
Supporting Multiple Languages with Analysis Services	p. 307
Summary	p. 308
Data Mining	p. 309
Business Problem	p. 309
Problem Statement	p. 309
Solution Overview	p. 310
Business Requirements	p. 310
High-Level Architecture	p. 311
Business Benefits	p. 314
Data Model	p. 314
How Often Are Users Visiting the Web Site?	p. 315
Who Is Using the Web Site?	p. 317
What Interesting Attributes Can We Track?	p. 318
Technical Solution	p. 319
Adding Visit Information to the Data Warehouse	p. 319
How We Will Be Using Data Mining	p. 321
Approaching the Customer-Segmentation Problem	p. 322
Getting Started with Data Mining	p. 323
Analyzing with Data Mining Information	p. 329
Creating a Model for Product Recommendations	p. 330
Add Data Mining Intelligence into a Web Application	p. 334
Managing the Solution	p. 337
Deployment	p. 337
Maintenance	p. 338
Operations	p. 338
Next Steps	p. 339
Sequence Clustering to Build Smarter Web Sites	p. 339
Other Data Mining Possibilities	p. 339
Using Data Mining in Integration Services to Improve Data Quality	p. 340
Summary	p. 340
Very Large Data Warehouses	p. 343
Business Problem	p. 343
Problem Statement	p. 344
Solution Overview	p. 345
Business Requirements	p. 345
High-Level Architecture	p. 346
Business Benefits	p. 349
Data Model	p. 349
Technical Solution	p. 352
Partitioned Tables	p. 352
Loading Large Volumes of Data	p. 358
Partitioning Analysis Services Cubes	p. 362
Aggregation Design	p. 366
Large Dimension Support in SQL Server 2005	p. 367
Managing the Solution	p. 368
Initial Loading of the Data Warehouse	p. 368
Managing Table Partitions	p. 368
Managing Cube Partitions	p. 372
Next Steps	p. 375
Automating the Process	p. 375
Partitioned Views	p. 376
Scaling out Using Analysis Services Database Synchronization	p. 376
Summary	p. 377
Real-Time Business Intelligence	p. 379
Business Problem	p. 379
Problem Statement	p. 380
Solution Overview	p. 380
Business Requirements	p. 380
High-Level Architecture	p. 380
Business Benefits	p. 381
Data Model	p. 382
Technical Solution	p. 383
Cube Design for Real Time	p. 384
Real-Time ETL-Working with the Data Source	p. 393
Managing the Solution	p. 394
Operations	p. 394
Next Steps	p. 396
Maintaining a Consistent View of the Source Data	p. 396
Loading Data Directly into Analysis Services Using Integration Services	p. 396
Notification Services	p. 397
Summary	p. 397
Index	p. 399
Table of Contents provided by Ingram. All Rights Reserved.

Excerpts

Practical Business Intelligence with SQL Server 2005 Practical Business Intelligence with SQL Server 2005 Preface Each of the areas in the Microsoft Business Intelligence (BI) platform could take a whole book to describe in detail. Why then are we attempting to cover all aspects of BI solutions in one book? The answer is that people who design, build, and manage a BI solution need to have a good understanding of how all the BI components in SQL Server 2005 can work together, from relational databases to store the data, Integration Services to move and transform the data, Analysis Services to provide a platform to analyze and query the data, and Reporting Services to present the information to users. We really wanted to avoid just giving a superficial or "marketing" view of all the different aspects of the BI platform, so we have designed this book in a practical way. If you were starting out to build a BI solution for a business problem and had a clearly defined budget and deliverables, you would not necessarily learn every technical aspect of all the SQL Server components, just those areas you need to deliver business value. Books are available that cover specific technologies in depth, such as On Line Analytical Processing (OLAP) or data mining, and other books describe areas such as how to properly design data warehouses--this book combines the essentials of both areas to teach you how to design good solutions and describes the concrete steps and best practices to build a working solution in SQL Server 2005, without trying to cover every technology in depth. Structure of This Book Instead of structuring this book around the technology and trying hard to list and explain every feature in the platform, we pick a specific business issue for each chapter and show you how a complete solution could be built, touching on all the key technologies and design challenges along the way. We selected the business areas from various vertical industries that we have worked with, such as health care or financial services. Although all these vertical industries might not be applicable to your job, you can apply the lessons you learn to your own environment. In some cases, we have taken the liberty of simplifying the business problem to convey more clearly a specify point in a BI solution to a broader audience. It is not our intent to turn readers into industry experts. The authors have both worked as BI consultants for many years and have a fundamental belief that you cannot learn to build effective solutions from a technology reference book or a step-by-step guide to using technical features. Instead, we share our thoughts on design decisions at every point along the way to building a working solution and explain the tradeoffs and consequences in the real world. Each chapter focuses on some specific technology areas and builds on your knowledge from previous chapters, so we recommend that you read through them in sequence. The first chapter, "Introduction to Business Intelligence," introduces the terms and concepts behind BI, including data wareho