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.
Excerpt. © Reprinted by permission. All rights reserved.
Practical Business Intelligence with SQL Server 2005
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 warehousesthis 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 qualitymore 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 (http://www.awprofessional.com/title/0321356985), 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.