How This Book Is Organized

Weve divided the book into five parts :

1. Requirements, Realities, and Architecture

2. Developing and Populating the Databases

3. Developing the BI Applications

4. Deploying and Managing the DW/BI System

5. Extending the DW/BI System

Part I : Requirements, Realities, and Architecture

Part I sets the stage for the rest of the book. Most of you are eager to get your hands on the Microsoft toolset. Thats fine while youre experimenting and learning about the technology, but its the kiss of death for a project. Stop, back away from the keyboard, and think about what youre setting out to do.

Chapter 1 : Defining Business Requirements

The book begins with a brief summary of the Business Dimensional Lifecycle. We drill down on the most important step, gathering the business requirements, and briefly present the business requirements for the Adventure Works Cycles case study used throughout the book. Chapter 1 refers to the Business Requirements Definition box in Figure 1.

Readers who are very familiar with the Kimball Method can skip the first part of the chapter but should read the case study.

Chapter 2 : Designing the Business Process Dimensional Model

We present a brief primer on how to develop a dimensional model. This chapter presents terminology and concepts used throughout the book, so its vital that you understand this material. This chapter refers to the Dimensional Modeling box in Figure 1.

Readers who are very familiar with the Kimball Method can skim most of this material and review the Adventure Works case study at the end of the chapter.

Chapter 3 : The Toolset

The Architecture and Product Selection tasks are straightforward for a Microsoft DW/BI system. In this short chapter we talk in more detail about how and where to use the various components of SQL Server 2005, other Microsoft products, and even where youre most likely to use third-party software in your system. This chapter provides a brief overview of the Technical Architecture Design and Product Selection & Installation boxes in Figure 1.

Even readers who are very familiar with SQL Server 2000 should review this chapter, as it contains information about the new features of SQL Server 2005, some of which are significantly different.

Part II : Developing and Populating the Databases

The second part of the book presents the steps required to effectively build and populate the data warehouse databases. Most Microsoft DW/BI systems will implement the dimensional data warehouse in both the relational database and the Analysis Services database.

Chapter 4 : Setup and Physical Design

Chapter 4 describes how to install and configure the various components of SQL Server 2005. We talk about system sizing and configuration, and howand whyyou might choose to distribute your DW/BI system across multiple servers.

The physical data model in the relational database should be virtually identical to the dimensional model we discuss in Chapter 2. There are some physical design issues to consider, notably whether to partition the fact tables. You also need to develop the initial indexing plan for the relational database.

Chapter 4 is focused on the Product Selection & Installation and the Physical Design boxes of Figure 1, but it doesnt complete that discussion. The physical design issues for Analysis Services are postponed to Chapter 7.

Chapter 5 : Designing the ETL System

The ETL portion of the DW/BI system is always a design challenge. In this chapter we introduce SQL Servers new ETL technology, Integration Services. Then we talk about how to write the design specification for your ETL system.

Chapters 5 and 6 cover most of the tasks in the ETL Design & Development box in Figure 1, although we return to these issues several times in the book.

Chapter 6 : Developing the ETL System

Finally its time to start moving data. This chapter talks about the basic design for your ETL system in Integration Services. We walk through the details of loading dimension tables, starting with a fairly detailed example for a simple table. The chapter isnt a tutorial, however, and soon we pick up speed and address the key dimension management issues such as surrogate key assignment and attribute change management.

Next , we explain how to load fact tables. We illustrate the surrogate key pipeline and discuss some advanced topics such as maintaining snapshot fact tables and handling late-arriving data.

Chapter 7 : Designing the Analysis Services OLAP Database

We recommend that your Microsoft DW/BI system use Analysis Services as the main database for users to query. The more closely the relational database and ETL process are designed to meet your business requirements, the easier it is to design the Analysis Services database. Analysis Services includes lots of features for building an OLAP database on top of a poorly constructed database, but your results will be better if youve followed our instructions and have a clean and conformed relational data warehouse as your starting point for the OLAP database.

The Analysis Services wizards are easy to use, and with a small system, you dont need to worry very much about advanced settings. However, if you have large data volumes or a lot of users, you need to develop a deep understanding of the OLAP engine. Much of this chapter is focused on helping you learn enough to implement Analysis Services across your enterprise.

The Analysis Services chapter takes on the Physical Design and ETL boxes of Figure 1, this time from the perspective of the OLAP database engine.

Part III : Developing the BI Applications

The third part of the book presents the steps required to present the data to the business users. We start with a chapter that clearly defines what we mean by BI applications. Then we talk about how to use Reporting Services to deliver the initial set of predefined reports .

Data mining can deliver huge value to your business, by looking for hidden relationships and trends in the data. Chapter 10 talks about how to use Analysis Services to build data mining models.

Chapter 8 : Business Intelligence Applications

Some people seem to think that the job of developing a data warehouse begins and ends in Part II, Developing and Populating the Databases. But please remember our definition of the data warehouse: It is the complete system all the way to the user s screen. You are building a DW/BI system, not just a database. Weve argued forcefully that if you skimp on the early phases in Part I of the Lifecycle, the databases you develop wont be very useful. Similarly, you need to deliver some applications in Part III, or the business users wont be able to use the system.

Most users of your DW/BI system are simply going to consume predefined reports. They wont create new reports, much less perform the kind of complex analysis thats possible with the system. Instead, theyll run and review reports, perhaps parameterized reports that use picklists. You need to provide a reasonably complete set of reports when the system goes live. Most often, these reports are delivered within a BI portal.

The other kind of BI application is an analytic application. An analytic application is centered on a specific business process and encapsulates a certain amount of domain expertise about how to analyze and interpret that business process. It may go so far as to include complex, code-based algorithms or data mining models that help identify underlying issues or opportunities.

Chapter 8 addresses the tasks in the BI Application Specification box in Figure 1.

Chapter 9 : Building the BI Application in Reporting Services

In this chapter, we begin by describing the criteria for a report development and delivery system, and discuss how SQL Server Reporting Services meets those criteria. The online documentation does a good job of showing you how to create simple reports, so we focus our attention on harder problems. We walk you through an example of creating a moderately complex report from the relational database. We demonstrate how to source a similar report from Analysis Services.

Chapters 9 and 10 describe the tasks in the Lifecycle box called BI Application Development.

Chapter 10 : Incorporating Data Mining

Data mining is perhaps the most powerfuland certainly the least understood technology in the BI toolkit. This chapter defines data mining, and provides examples of how it can be used. We talk about Microsofts data mining technology, including the algorithms that are included with SQL Server Analysis Services. We provide practical guidance on how to build a data mining model and how to incorporate the results of data mining into your systems. To make this theoretical discussion more concrete, we work through two case studies.

Part IV : Deploying and Managing the DW/BI System

The fourth section of the book includes information about how to deploy and operate your DW/BI system.

Chapter 11 : Working with an Existing Data Warehouse

Although the primary audience for this book is a DW/BI project team thats working on a new system, we recognize thats not always going to be the case. This chapter describes how to work with an existing system, beginning with an evaluation of whats workingand not workingin your current systems.

We talk about how to upgrade a SQL Server 2000 DW/BI system to SQL Server 2005 and strongly recommend that most components be selectively rewritten rather than upgraded. SQL Server technologies are designed to work well in a heterogeneous environment. We discuss the components of the DW/BI system that you can implement in non-Microsoft technology, and highlight the (relatively few) features that dont work as well in a heterogeneous environment as they do in an allSQL Server implementation.

Chapter 11 doesnt map exactly to one of the boxes in Figure 1, although its closest to the technology track at the top.

Chapter 12 : Security

We start our discussion of the DW/BI systems security by encouraging you to develop an open access policy for information. Sensitive data must of course be protected, but we think most contents of the data warehouse should be available to most authenticated users.

Authentication is an important concept: Its really important that only authenticated users have access to the system. And access control begins with the physical servers. Theres no point in putting a complex security system in place if anyone can walk into the server closet and access the machine directly.

Recognizing that not everyone can implement an open security policy, we describe how to control access in the various components of SQL Server: Reporting Services, the relational database, and Analysis Services. We also discuss the separate issues of security in the back room development area of the data warehouse.

The discussion of security is most closely related to the Deployment and Maintenance boxes of Figure 1.

Chapter 13 : Metadata Plan

Lots of people talk about metadata, but weve seen few examples of it being implemented thoroughly and successfully. Wed like to have seen an integrated metadata service in Microsoft SQL Server 2005, which we could simply describe to you, but thats not the case. Instead, we spend most of this chapter detailing the metadata that we think is most important, and describing the steps to maintain and publish that information.

Metadata is related to the Deployment and Maintenance boxes of Figure 1.

Chapter 14 : Deployment

Deploying the DW/BI system consists of two major sets of tasks. First, you need to deploy the system. This effort consists primarily of testing: testing of data, processes, performance, and deployment scripts themselves . The deployment scripts should include a playbook , with step-by-step instructions for how to deploy the system changes.

The other major set of deployment activities is focused more on the business users than on the technology. You need to develop and deliver training and documentation materials. You need to pull together the BI portal that we describe in Chapters 8 and 9. And you need to develop a plan for supporting the business users, who will inevitably have questions.

Chapter 15 : Operations and Maintenance

As business people begin to use the warehouse to answer their questions on a regular basis, they will come to rely on it. If users dont believe the warehouse is reliable, they will go back to their old ways of getting information. This reliance is a kind of trust, and you must do everything you can to build and keep that trust. You need to monitor usage and performanceboth for data loads and user queries. Track system resources and make sure you dont run out of disk space. In short, maintain the warehouse as the production system it now is. You must be meticulous in your attention to the quality of the data thats loaded into the data warehouse. Once a business user loses trust in the accuracy of the data, that trust is nearly impossible to regain.

The training, documentation, BI portal, reports, and support systems that we talk about in Chapter 14 need care and feeding, too.

Part V : Extending the DW/BI System

A key characteristic of the Kimball Method is that your DW/BI system development is iterative. Start with the most valuable business process, and keep looping back to bring in data to support additional business processes.

Chapter 16 : Managing Growth

Maintenance is a critical task, but its not the only ongoing effort. Growing the warehouse is really about adding value to the business. The more business information in the warehouse, the more valuable it becomes.

There is a common misconception among business and information systems management that developing a data warehouse is like most systems projects, with a large upfront development effort, followed by a small, ongoing maintenance task. This is not true because the data warehouse lifecycle is an iterative process. Once you finish with the highest priority business process subject area, you start on the next most important one, passing through the entire lifecycle again. Generally, the data warehouse team doesnt go away. In fact, it has more work to do maintaining and growing the data warehouse after the initial implementation, not less. We talk about longer term maintenance and growth issues in Chapter 16.

Chapter 17 : Real-Time Business Intelligence

Chapter 17 takes on the topic of real-time business intelligence, discussing how to bring real-time dataloosely defined as data refreshed more frequently than dailyinto the DW/BI system. SQL Server 2005 contains many features to enable real-time business intelligence. We talk about how to use these features, and the inevitable tradeoffs you face when implementing real-time BI.

Chapter 18 : Present Imperatives and Future Outlook

Chapter 18 reviews the major phases of the DW/BI project and highlights where the most significant risks are to the overall success of the project. We finish the book with a wish list of features and functionality that we hope to see in the Microsoft BI toolset in the years to come.