Practical Business Intelligence with SQL Server 2005

Practical Business Intelligence with SQL Server 2005

by John Hancock, Roger Toren

NOOK Book(eBook)

$35.99 $47.99 Save 25% Current price is $35.99, Original price is $47.99. You Save 25%.

Available on Compatible NOOK Devices and the free NOOK Apps.
WANT A NOOK?  Explore Now


Design, Build, and Manage High-Value BI Solutions with SQL Server 2005


In this book, two of Microsoft’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 Microsoft’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 customer’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. You’ll translate each model into a working system and learn how to deploy it into production, maintenance, and efficient operation.


Whether you’re a decision-maker, architect, developer, or DBA, this book brings together all the knowledge you’ll need to derive maximum business value from any BI project. 

     •  Leverage SQL Server 2005 databases, Integration Services, Analysis Services, and Reporting Services

     •  Build data warehouses and extend them to support very large databases

     •  Design effective Analysis Services databases

     •  Ensure the superior data quality your BI system needs

     •  Construct advanced enterprise scorecard applications

     •  Use data mining to segment customers, cross-sell, and increase the value of each transaction

     •  Design real-time BI applications

     •  Get hands-on practice with SQL Server 2005’s BI toolset

Related collections and offers

Product Details

ISBN-13: 9780321614872
Publisher: Pearson Education
Publication date: 08/28/2006
Series: Microsoft Windows Server System Series
Sold by: Barnes & Noble
Format: NOOK Book
Pages: 432
File size: 9 MB

About the Author

John C. Hancock is a Senior Consultant with Microsoft Consulting Services in

Toronto, Canada, specializing in Business Intelligence technologies. He has

worked with some of Microsoft’s largest and most strategic clients, and his consulting

experience has included architectural consulting, project team lead positions,

performance optimization, and development of customized training courses and

materials. Recently he has worked extensively in the field of intelligence systems

for law enforcement. Prior to Microsoft, he worked as an independent consultant in

the United Kingdom and South Africa. He holds a Bachelor of Science (Honors)

degree in mathematics and computer science.


Roger Toren is a Principal Consultant with MCS, based in Vancouver, Canada,

focusing on guiding customers in the design and implementation of Business Intelligence

solutions with SQL Server 2005. He was the lead author on the SQL Server

2000 High Availability guide. He has more than 35 years of experience in IT, covering

a wide variety of industries, including banking, insurance, retail, education,

health care, geo-spatial analysis, and nuclear research. He holds a Bachelor of Science

degree in physics and a Masters of Science degree in computing science. Prior

to joining Microsoft, he taught undergraduate courses in computing science,

worked as an independent consultant, and served as Associate Director in the technology

practice of a major global consulting firm.

Table of Contents

Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvi

About the Author. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xvii

Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xviii

Chapter 1 Introduction to Business Intelligence . . . . . . . . . . 11

Chapter 2 Introduction to SQL Server 2005 . . . . . . . . . . . . . 29

Chapter 3 Building a Data Warehouse. . . . . . . . . . . . . . . . . . 63

Chapter 4 Building a Data Integration Process . . . . . . . . . . . 99

Chapter 5 Building an Analysis Services Database. . . . . . . 141

Chapter 6 Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .179

Chapter 7 Data Quality . . . . . . . . . . . . . . . . . . . . . . . . . . . . .215

Chapter 8 Managing Changing Data . . . . . . . . . . . . . . . . . . 245

Chapter 9 Scorecards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271

Chapter 10 Data Mining. . . . . . . . . . . . . . . . . . . . . . . . . . . .  309

Chapter 11 Very Large Data Warehouses . . . . . . . . . . . . .  343

Chapter 12 Real-Time Business Intelligence. . . . . . . . . . . . .379

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .399


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 warehousing and dimensional modeling, and is recommended both for readers who are new to BI as well as experienced practitioners who can see how and where we apply standard BI techniques to our recommended solutions. The second chapter, "Introduction to SQL Server 2005," gives a high-level overview of all the components of the Microsoft BI platform and is intended to serve as an orientation for the technology so that later chapters are free to focus on solutions.

The Business Focus

In all the remaining chapters, we followed the same pattern. Every chapter starts in the same place that your projects will be starting: with a business problem. We give you an overview of the solution we are proposing at roughly the same level of detail that you might have in mind at the beginning of a BI project and explain some of the business benefits that the customer might expect to get from the solution.

The next section of the chapter walks you through the data model for the solution. We believe that more BI projects get into trouble in the data modeling stage than anywhere else (except possibly data quality—more on this later in the book), so we typically cover the data model in detail for each solution. Data modeling is fundamentally a practical discipline; if you are new to it, you will only get any good through practice, so we have tried to take you through our thought processes as we design the data models. Experienced data modelers can have fun reading the section and second-guessing our designs.

The technical solution section of the chapter describes how you can use the technology to build the solution we outlined in the early part of the chapter. We describe the relevant SQL Server features and give some advice on things to be aware of, by referring to the particular business solution we are building in the chapter. We cover the major technology areas that you can expect to encounter in most BI projects and provide some pointers to other interesting technical features that you might want to investigate further.

It would be nice if we could stop work at the point when the solution has been developed, but in the real world, you are going to need to deploy the solution from development into a production environment, maintain it by making changes and enhancements, and know how to operate it on an ongoing basis. The section on managing the solution covers all these topics with specific reference to the particular technology areas from the chapter.

We focus our attention on specific areas of the business problem and technologies to describe a working solution, so we end each chapter with some ideas for next steps to enhance the solution by adding new business areas or using other SQL Server features.

Quick Start Sections

Each chapter contains some Quick Start sections, which are detailed exercises that you can follow along to get practice using SQL Server tools to implement key sections of the solution. The Quick Starts are not intended as a step-by-step guide to all the features in SQL Server 2005, but rather cover in detail the steps you need to take to get a particular aspect of a working solution to the point where you can use what you have just built to further explore all the ideas presented in the chapter. Practical experience with the technology is crucial for you to understand the platform fully, even if your role is in architecture or operations.

When we tested this book with readers, they all had slightly different ways of using the Quick Start sections. Some people prefer to read through a whole chapter, and then when they have chance to sit in front of a computer, they work through all the Quick Starts in the chapter and start trying out their own ideas. Others read the book and try out both the detailed Quick Starts and other technical areas we mention in the chapter as they come across them. Regardless of the approach you take, remember that the Quick Starts are there to get you started: Trying out your own ideas is the best way to master what is, after all, a large and comprehensive BI platform.

You can find sample files and setup instructions for the Quick Starts on the Addison-Wesley Web site. Go to the book's Web page (, and in the More Information section, click the Quick Starts link.


The audience for this book includes all members of teams involved in BI projects. Technical decision makers will benefit from the business-focused sections and information on vertical market solutions to be able to envision the kinds of solutions that are possible. Architects and IT professionals will use this book to learn about the SQL Server BI platform and how to use the various components in their environment (and prepare for the kinds of problems that BI projects face). Developers and database administrators who will be building and managing the solutions will benefit from the industry-specific solution descriptions and will gain hands-on experience with the technology through the technology walkthroughs.

Customer Reviews