The Microsoft Data Warehouse Toolkit

With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset

Joy Mundy, Warren Thornthwaite, Ralph Kimball

• Publisher: Wiley
• Year: 2011
• ISBN:
  9781118067956
• Edition: 1
• Topic: Computer Science
• Subtopic: Data Warehousing
• Index: Computer Science

Best practices and invaluable advice from world-renowned data warehouse experts

In this book, leading data warehouse experts from the Kimball Group share best practices for using the upcoming "Business Intelligence release" of SQL Server, referred to as SQL Server 2008 R2.In this new edition, the authors explain how SQL Server 2008 R2 provides a collection of powerful new tools that extend the power of its BI toolset to Excel and SharePoint users and they show how to use SQL Server to build a successful data warehouse that supports the business intelligence requirements that are common to most organizations. Covering the complete suite of data warehousing and BI tools that are part of SQL Server 2008 R2, as well as Microsoft Office, the authors walk you through a full project lifecycle, including design, development, deployment and maintenance.

• Features more than 50 percent new and revised material that covers the rich new feature set of the SQL Server 2008 R2 release, as well as the Office 2010 release
• Includes brand new content that focuses on PowerPivot for Excel and SharePoint, Master Data Services, anddiscusses updated capabilities of SQL Server Analysis, Integration, and Reporting Services
• Shares detailed case examples that clearly illustrate how to best apply the techniques described in the book
• The accompanying Web site contains all code samples as well as the sample database used throughout the case studies

The Microsoft Data Warehouse Toolkit, Second Edition provides you with the knowledge of how and when to use BI tools such as Analysis Services and Integration Services to accomplish your most essential data warehousing tasks.

Part 1: Requirements, Realities, and Architecture

Chapter 1: Defining Business Requirements

Chapter 2: Designing the Business Process Dimensional Model

Chapter 3: The Toolset

Chapter 4: System Setup

This first part of the lifecycle is where you lay the foundation for your success. Working with the business folks to understand and prioritize their requirements for analytics as we describe in Chapter 1 helps you set specific goals for your first pass through the lifecycle that are both valuable to the organization and achievable in a reasonable timeframe. Your understanding of the business requirements becomes the basis for designing a flexible, usable, high-performing dimensional model in Chapter 2.

What you learn in the first two chapters helps you tackle the architecture and technology track at the top of the Lifecycle. Your business understanding helps you determine what architectural components are important for your DW/BI system. Once you know the problem, you can identify the specific functionality you need, and where that functionality will come from in the Microsoft SQL Server toolset. This, in turn, allows you to make decisions on the server configurations and disk subsystems that will form the basic infrastructure of your DW/BI system.

Part 1 is about getting the lay of the land before you decide what you are going to build and where you will build it. Your primary focus here is on identifying the most promising business opportunities and designing the data structures and system architectures needed to deliver them. By the end of this section, you should have all the pieces in place for you to dig into the development work of creating the DW/BI system database. Skip this section at your peril.

The Kimball Lifecycle steps covered in Part 1

Chapter 1

Defining Business Requirements

Building the foundation.

Business requirements are the bedrock of the successful data warehouse/business intelligence (DW/BI) system. Business requirements guide the development team in making the biggest strategic choices, such as prioritizing subject areas for implementation, and in making the smallest tactical design decisions, such as how to present key performance indicators on the users’ screens. In this chapter, we cover the process of gathering business requirements and converting them into a DW/BI system strategy. We describe the process of interviewing business and IT representatives and mapping their analytic requirements back to the core business processes (such as orders, page views, or account transactions) that generate the needed data. These business processes are the building blocks of the DW/BI system. After the requirements are documented, we offer a technique for working with senior management to prioritize the implementation of those business-process–based projects. We also illustrate these tasks with an example based on Microsoft’s sample database business, Adventure Works Cycles.

As Figure 1-1 illustrates, the Business Requirements Definition step is the foundation of the Kimball Lifecycle methodology. Business requirements and their associated business value give you the guidance you need to make decisions in all three downstream tracks. As you’ll see, they influence the project scope and plan, too.

RESOURCES

If you skipped the Introduction to this book, you should at least go back and read the overview of the Kimball Lifecycle because it is the organizing framework for this book and for implementing a successful DW/BI system.

Figure 1-1: The Business Requirements Definition step of the Kimball Lifecycle

This chapter is primarily about resisting temptation. Gathering business requirements is often outside a technical person’s comfort zone. The overall success of the project is largely determined by your understanding of the business requirements and your relationships with the business people. Resist the temptation to just start loading data.

In this chapter you learn the following:

• The importance of understanding business requirements and securing solid business sponsorship
• The steps used to define enterprise-level business requirements, including the interview process, synthesizing requirements into their underlying business processes, developing the enterprise analytic data framework called the data warehouse bus matrix, and prioritizing business processes with senior management
• How to plan the initial business process dimensional model implementation and gather project-level business requirements
• What goes into a typical requirements summary document and how it links to business requirements for analytics and business process implementations

RESOURCES

Throughout the book, we provide specific references to the various titles in the Kimball Toolkit library to help you find more details on the concept or technique described. Each book in the Toolkit series is described in the Introduction to this book.

The Most Important Determinant of Long-Term Success

There is one common factor in successful business intelligence projects: delivering business value. Your DW/BI team must embrace the goal of enhancing business value as its primary purpose. This seems like an obvious statement, but most DW/BI folks are technologists at heart. We like the certainty of computers and programming and shy away from the vague uncertainties of the business side.

You can’t deliver business value unless you work closely with business people. You need to understand their language and learn to see the world from their points of view. You’ll be working in a non-technical, highly ambiguous, politically sensitive environment. Are you feeling queasy yet? This unsettled environment is what the DW/BI system is all about. You must develop the business knowledge and people skills right along with your technical skills to meet the needs of your business users. We realize the entire team will not become smooth-talking MBAs. However, someone on the team must have strong business and communications skills, and everyone will be more effective if they learn more about the business.

NOTE

Perhaps your organization uses the “agile” development methodology. If so, then you have heard this story already! In the agile approach, projects are owned and driven by the business users. To learn more about this approach, see “agile software development” on www.wikipedia.org. For guidance relating agile to DW/BI system development, see The Kimball Group Reader, pp. 109–112.

So, while many DW/BI teams and consultants pay lip service to business value, the reality of their day-to-day behavior is that technology rules. Do not let this happen to you. Technology is important; business value is mandatory. We understand you bought this book to learn about the SQL Server DW/BI toolset, but SQL Server is just a tool. Your success in using that tool in your organization depends on your understanding of the organization’s unique requirements and priorities for business intelligence.

As you read this book, you’ll encounter recommendations that may seem unnecessarily complicated or just plain unnecessary. Every time you’re tempted to dismiss the authors as overly fond of their design methodology or just overzealous, consider whether your reactions are driven by your technical convenience or by the business users’ needs. Never lose sight of the business.

Adventure Works Cycles Introduction

It always helps to see new concepts in the context of a specific example. Since everyone’s organization is different, we’ll use some of the business requirements for Microsoft’s demo database company to illustrate the process of defining business requirements described in this chapter.

The current SQL Server sample business intelligence databases are based on a fictitious company called Adventure Works Cycles, a multinational manufacturer and seller of bicycles and accessories. The database and associated samples are not part of the software distribution set. Instead, you download them from the Microsoft code-sharing site called Codeplex (Search for “SQL Server Samples Database” at http://www.codeplex.com) or download the database from the Wiley web site at www.wiley.com/go/MsftDWToolkit2E. You will need to download and install the SQL Server 2008R2 version of the sample databases to follow the examples later in this book.

DOWNLOADS

You can find several detailed documents illustrating what the business requirements-gathering process might look like at a company such as Adventure Works Cycles on the book’s web site (http://kimballgroup.com/html/booksMDWTtools.html). These include interview summaries and additional background information.

Uncovering Business Value

If you’re going to be driven by business value, you need to go out and identify, understand, and prioritize the needs of the business. This is easier said than done if your focus has historically been on technology. Fortunately, the Kimball Lifecycle provides the tools to work through an entire development iteration of a data warehouse, beginning with business requirements.

Where do you start with your business intelligence system? What is the first step? Well, it depends on a host of factors, such as how your organization works, what you already know about the business, who is involved in the project at this point, what kinds of DW/BI efforts came before, and many other factors.

Let’s talk about the most common scenario first, and then we’ll address a few exceptions. More often than not, the DW/BI system starts as a project hosted by the Information Technology (IT) department of the organization. The IT-driven DW/BI project gets cranked up because the CIO decides the company needs a data warehouse, so people and resources are assigned to build one. This is a dangerous situation. Please refer to the first point in this chapter: Focusing on business value is the most important determinant of long-term success. The problem with the IT-driven DW/BI system is that it almost always centers on technology. The team has been assigned the task of building a “warehouse,” so that’s exactly what they do. They get some hardware and some software and start extracting data.

We know some of you are thinking, “Oops, I already bought the ETL server and the user reporting tools.” That’s probably okay, but put those tools aside for the moment. Step away from the keyboard. If you get sucked into the technology, you’re missing the whole point. You can build a technically great DW/BI system that provides very little business value. As a result, your project will fail. You have to start with business value, and identifying business value involves several major steps:

• Recruiting strong business sponsorship
• Defining enterprise-level business requirements
• Prioritizing business requirements
• Planning the project
• Defining project-level business requirements

We’ll run through each of these steps in the following sections.

Obtaining Sponsorship

Developing solid business sponsorship is the best place to start the DW/BI project. Your business sponsors (it is generally good to have more than one) will take a lead role in determining the purpose, content, and priorities of the DW/BI system. You will call on them to secure resources and to evangelize the DW/BI system to the rest of the organization. This includes activities such as arranging for a planning meeting with senior staff, speaking to a room full of business users at the project kick-off, and getting spending approval for your new server. You need to find at least one person in the organization who scores well in each of the following areas:

• Visionary: Someone who has a sense for the value and potential of information and some clear, specific ideas on how to apply it.
• Resourceful: Someone who is able to obtain the necessary resources and facilitate the organizational change the data warehouse will bring about.
• Reasonable: Someone who can temper his or her enthusiasm with the understanding that it takes time and resources to build a major information system.

If you’ve been with your company for a while, you already know who these people are. In this case, your task is to recruit them onto the project. However, if you’re new to the company, or you don’t get out of the IT group much, you’ll need to investigate and find your business sponsors. In either case, the best way to find and recruit these people is by conducting an enterprise business requirements gathering project. Obtaining business s...