Business Requirements Example: Adventure Works Cycles

The sample business intelligence databases in SQL Server 2005 are based on a fictitious company called Adventure Works Cycles (AWC), a multinational manufacturer and seller of bicycles and accessories. According to the descriptions in Books Online, the company is based in Bothell, Washington, USA and has regional sales offices in several countries . In this section we provide an example of the business requirements gathering process based on the Adventure Works Cycles business. Assuming the DW/BI system already has good business sponsorship, the first major step in the Lifecycle is requirements definition, and the first task in requirements definition is preparation.

Interview Preparation at Adventure Works Cycles

Typically, youd carefully review all the information about Adventure Works Cycles that you could find, reading through strategy documents, annual reports , marketing plans, competitive analyses, and presentations from senior managements annual offsite planning meeting. Because Adventure Works Cycles is a fictitious company, you cant really do the kind of research you should. SQL Server Books Online (BOL) provides some background information about Adventure Works Cycles in the section Sample Databases and Business Scenarios. You may want to review the materials in BOL to get a general sense for Adventure Works Cycles and the AdventureWorks transaction system database. In this section, we have enhanced the Books Online content by doing some analyses of data from the database itself. We also provide you with business requirements information from our imaginations.

Adventure Works Cycles Basic Business Information

There is an incredible amount of information about your organization buried in your transaction systems. Buried is the key word herenot many people can get at it, which is why you are building a DW/BI system in the first place. However, as a competent systems professional, you should be able to get at this data. A few queries against the transaction database can reveal much about the dynamics of the business and the nature of the data at the same time.

The Adventure Works Cycles transaction database is called Adventure-Works and is installed as part of the SQL Server samples. It holds data from January, 2001 through June 30, 2004. A few queries on this data reveal that Adventure Works Cycles is doing well, at least in terms of orders and growth. The Grand Total line in Table 1.1 shows that orders have been increasing rapidly , tripling in 2002 and increasing almost 50 percent in 2003. Based on the first six months, 2004 looks like another banner year. Table 1.1 also shows that Adventure Works Cycles sells products in four major product categories: bikes, components , clothing, and accessories. Bicycles account for more than 80 percent of orders, with clothing and accessories making up about 4 percent.

Table 1.1: Adventure Works Cycles Product Orders by Category

CATEGORY	2001	2002	2003	2004 (YTD)
Bikes	10,985	28,854	38,026	24,160
Components	708	4,230	6,418	2,477
Clothing	35	501	1,031	589
Accessories	19	88	549	531
Grand Total	11,746	33,673	46,023	27,757
All figures in U.S. dollars

THE HISTORY OF ADVENTURE WORKS

The Adventure Works database has been evolving at Microsoft for several years . Its been used in various forms as a demo database for Microsofts CRM solution, as a training database for .NET architecture courses, as an application to demonstrate Microsoft Transaction Server, and as an example to demonstrate online shopping using Commerce Server. In these last two incarnations, Adventure Works was a camping gear retailer.

 

Adventure Works Cycles sells a lot of bikes. Additional database queries tell us where their bike orders come from. Table 1.2 reveals that AWC has sales in six countries, with about 60 percent of orders coming from the United States. The percentage of orders coming from outside the U.S. has increased from about 25 percent in 2001 to close to 40 percent so far in 2004.

Table 1.2: Adventure Works Cycles Product Orders by Country/Region

COUNTRY	2001	2002	2003	2004 (YTD)
United States	8,980	23,717	27,265	14,631
United Kingdom	467	2,348	5,683	4,232
Canada	1,340	4,359	4,721	2,278
France	27	1,542	3,915	2,576
Australia	814	1,186	2,142	1,917
Germany	119	521	2,298	2,123
Grand Total	11,746	33,673	46,023	27,757
All figures in U.S. dollars

In terms of sales channels, Table 1.3 shows that the bulk of orders come from bicycle stores and distributors , also known as Resellers. This channel accounts for about 70 percent of orders and is handled by a direct sales force of 18 people. Adventure Works Cycles broadened its business during the late 1990s by opening up a direct sales channel to consumers on the Internet. Oddly, Internet orders have been fairly constant at around 30 percent of sales across the four years of data we have available. While we dont know why this is so, we predict the VP of Sales will take credit for being able to grow the direct sales channel as fast as the Internet has grown.

Table 1.3: Adventure Works Cycles Product Orders by Sales Channel

SALES CHANNEL	2001	2002	2003	2004 (YTD)
Reseller	9,119	27,992	37,318	18,715
Internet	2,627	5,681	8,705	9,041
Total	11,746	33,673	46,023	27,757
All figures in U.S. dollars

Now you have a sense for what Adventure Works Cycles sells, where they sell it, and how they sell it. The next question is to whom do they sell? Table 1.4 gives a snapshot of customers broken down by sales channel. This split is vital to understanding AWCs customers because the two channels are very different.

Table 1.4: Adventure Works Cycles Customers by Sales Channel Snapshot

	TOTAL CUSTOMERS	ACTIVE CUSTOMERS	$US SALES (OOO)	ORDER COUNT	AVG $ PER ORDER
Reseller	701	467	18,715	901	23,751
Internet	18,484	11,377	9,041	13,050	827
Total	19,185	11,844	27,757	13,951	2,308

The Reseller channel has a total of 701 customers, 467 of whom are active (defined as having placed an order in the first six months of 2004). The Internet channel is much larger, with a total of 18,484 customers, 11,377 of whom have placed an order in 2004. The Reseller channel justifies a direct sales force in part because the average order is close to $24,000 compared with an average of $827 for the Internet. On the other hand, the Internet business should be significantly more profitable because the price would be closer to retail rather than wholesale, and the cost of selling would be much lower without a direct sales force. You may hear more about this during the business requirements interviews.

As the DW/BI system manager, you should continue from here, researching top reseller customers and their historical buying patterns ( seasonality , product lifecycle, and so on). Investigate the Internet customers as well because you have demographic information on them that tells you who are they, what they buy, and where they come from.

These few reports demonstrate the power of analysis. They give you a general sense for the size and shape of Adventure Works Cycles. However, they do not give you enough information to build a DW/BI system. There is much more to learn in terms of the business, its strategies and plans, competitive environment, and key players. This is where you depart from the facts and venture into the fuzzy-edged land of organization and politics.

Interview Planning

The Adventure Works Cycles documentation doesnt include an org chart, but the HumanResources.Employee table in the AdventureWorks database lists a total of 290 employees. The Employee table is self-referencing with a ManagerID field that allows us to generate the companys org chart. The 290 employees are distributed across departments according to the org chart based on the Employee table shown in Figure 1.6. The org chart shows seven direct reports to the CEO.

Figure 1.6: The Adventure Works Cycles organization chart

The org chart should raise a few questions for you, such as why James Hamilton, the VP of Production, has only one direct report, and why Facilities and Maintenance (with seven people) is the only group reporting to him. Meanwhile, why does Peter Krebs, the Production Control Manager (not a VP) have all the rest of the 200-person manufacturing organization reporting to him? Why is David Bradley, the Marketing Manager, only a manager when Brian Welker is the Sales VP? If this were your organization, these would be important questions to get answered . This is why examining the org chart early on is generally a good idea.

Given the number of senior executives and the size of the organization, you would probably plan on more than 10 but fewer than 20 interviews at Adventure Works Cycles. Depending on availability, expect this to take a minimum of a week, more likely one and a half to two weeks.

The rest of this chapter highlights key parts of the requirements definition process in the context of the Adventure Works Cycles case study.

Once youve completed the project requirements definition step, you will be ready to begin designing your dimensional model, as covered in Chapter 2.

Adventure Works Cycles Enterprise Business Requirements

Begin with a series of enterprise requirements interviews to get a broad sense of the important business processes and their business value, and to identify potential sponsors. Summarize each interview by grouping the individual requirements into common analytic themes. This brings a useful structure to what is often a fairly scattered interaction. While the same analytic requirement may come up several times in the conversation, you need to summarize it only once. The typical summary for an hour -long interview takes about three to four pages. Each summary should include the business narrative along with example analyses and potential data problems. The narrative should capture an estimate of the value of each business process.

Weve included an example interview summary for Brian Welker, the VP of Sales. The following summary gives you much of the information you need to understand the business processes and the dimensional modeling decisions made in the next chapter. To get a more complete understanding, read through the additional abbreviated interview summaries at the books web site, www.MsftDWToolkit.com, for the following people:

• Ken Sanchez, CEO

• Peter Krebs, Production Control Manager

• David Bradley, Marketing Manager

• Mary Gibson, Internet Channel Analyst

• David Liu, Finance Manager

Adventure Works Cycles Vice President of Sales, Brian Welker, is a big believer in the power of information. He and his group have taken over one of the IT organizations analysts full time just to generate reports and analyses. The team decided to interview Brian early on to get some positive reinforcement.

After youve discussed roles and responsibilities, you might start the interview by asking Brian: How do you tell when youre doing a great job? If hes as smart as you think, his answer might be: When my sales planning is accurate, when my sales grow, when I can leverage special offers, and when I have good customer satisfaction. That would be quite an answer, but its a gold mine to drill down into his information needs. Your job is to react to his answer point by point, drawing out more detail. The following summary captures the results of that drill-down.

Example Interview Summary

Interviewee: Brian Welker, VP of Sales

Date: 7/25/2005

Interviewer: Joy Mundy

Scribe: Warren Thornthwaite

Additional attendees: Stuart Ozer, Carolyn Chau, Joy Byrd, Dave Wong

Roles and Responsibilities

Brian Welker is head of the sales organization. Hes responsible for sales to Resellers, which was $37 million last year, or about 70 percent of total sales. He has 17 people who report to him, including 3 regional sales mangers. Brian is excited about his team and eager for them to be successful. They are all bike freaks who love to ride bikes and love to talk about themperfect bike sales people. Brian is measured on achievement of the total Reseller sales target for the year.

Information Requirements

Brian is particularly frustrated with how difficult it is to get information out of the companys systems. When he asks for a report, it can take days or weeks to get the information. Often hes told It cant be done. The major analytic areas that Brian works with are as follows :

• Sales planning: Planning for the year begins in the fall of the previous year with the Sales planning process. Sales territories are based on geography. All new customers are assigned to a sales territory when they place their first order based on where they are located. Sales planning includes looking at the following:

  • Growth analysis: Overall market, new products, new geographies, new sales people.

  • Customer analysis: Who are the top customers, how have they changed over the last year?

  • Territory analysis: Where are top customers located, what are the current sales territories, and how balanced are they? How does this map to sales regions ?

  Brian and Ramesh Meyyappan (the analysts who work in IT) also look at sales by sales regions, which are groupings of customers based on the state where theyre located. Regions overlap sales territories and are based on seasonal buying patterns and regional preferences. Being able to group historical sales like this helps the sales team do a better job of forecasting monthly sales. They usually look at regional sales a lot during the sales planning cycle and then compare actuals to the forecast during the year. Every year, they change the regions a bit to line up with changes in buying patterns.

  Ramesh does all of this data analysis for the Sales forecasting and quota assignment process in a spreadsheet. The spreadsheet includes territory growth factors, allocations , and manual adjustments. The planning process is totally manual and takes a couple of months in the fall, and maybe a week per month to do the reporting during the year.

  During the annual planning process, Brian wants to be able to see reseller customer orders by year by customer territory, regardless of the sales rep assigned to the territory. In previous jobs, Brian would adjust the size of the territories by moving customers from one territory to another with the goal of making the territories more even. He has not done this at AWC yet, so all customers are still assigned to their original territory. Sales reps can be reassigned to different territories, usually when a sales rep leaves .

• Sales performance: Once the planning process is done, Brian wants to see sales according to the new territory assignments, all the way back through history so he can compare with actuals as they come in. At any time, Sales must be able to re-create historical sales and commission reports based on what happened at the time of the order, not which territory gets credit today.

  Brian also wants to look at orders from a sales rep perspective. The first thing he wants to see at the start of the week is how his sales reps are doing year to date. If Brian sees a problem in the higher level data, he wants to be able to drill down to detailed orders for individual reps. Of course, Brian has other reports he would like to see: for example, top 20 customers and orders by Reseller versus online.

• Basic sales reporting: Brian wants to take better advantage of the customer information buried in the orders transaction system. The sales reps would really appreciate it if they could get a list of the customers in their territory ranked by orders. Because most sales go to a small percentage of Resellers, the sales reps would concentrate on making sure those important customers are happy.

  Beyond this, Brian knows that 17 percent of 2002 customers did not reorder in 2003. And to date in 2004, he still has not heard from an additional 17 percent or so. His sales people could use this information to bring the best of these customers back to the business.

• Price lists: The fact that the sales reps are out in the field most of the time makes it difficult for them to keep their price lists current. The price list changes fairly often, but only a few things on the list change. It would be great to get a report to the sales reps that flagged changes and special offers, and maybe even highlighted the relevant customers.

• Special offers: The special offers could be a great sales tool. AWC just finished an inventory clearance sale on the silver Mountain 500s. The color didnt sell as well as others, resulting in too many in stock at model changeover. Mary Gibson, the marketing assistant for mountain bikes and David Liu in Finance put their heads together to come up with ideas to stimulate demand and came up with a 40 percent off offer. This is something the sales people can work with, but its a random process. Theyd like a report that shows which of their customers bought a lot of the product that is on special offer to see if theyre interested in more at a great price.

  Brian would like his sales people to start with the biggest potential customers first and keep selling down the list until they run out. Actually, Brian thinks the business would be better served if they contacted the more profitable customers about special offers first. Some of the biggest customers are big because they scoop up specials, which dont make a lot of money for AWC. Thats another thing: The sales reps need to know when out of stocks occur on special offers.

• Customer satisfaction: Brian would like to create some measures of customer satisfaction and has been trying to get more information out of the customer care system lately, with limited success. He would love to be able to track calls by complaint type, product, sales region, and customer to get a sense for customer satisfaction and product quality. He also thinks comparing order date and ship date in the sales data to identify late orders, and determining the percentage of returned items might be indicators of customer satisfaction. This would make a great start at a customer satisfaction scorecard.

• International support: The company has been growing internationally, but the transaction systems havent kept up. The systems do take orders in multiple currencies, but none of the descriptions has been translated from English. This is a problem for the sales people, who have split up the product list and done the translations themselves . This doesnt work in the long run because no one knows if theyve translated the information correctly. All materials must be bilingual to comply with Canadian law. The product tags and documents are already bilingual, but the sales materials are not.

Additional Issues

Brian expressed a frustration on the part of his sales force about the difficulty they have using existing reports. It is our sense that Brian would like an analytic system that provides his sales reps most of the information they need in a standard format with just a few keystrokes. The time zone differences make it hard for some of them to get live support from headquarters. If they need to get custom information, he would like it to be easy for them to get it themselves.

Success Criteria

Brian would like the system to provide him and his team with:

• Easy access to basic sales data for the whole field organization

• Flexible reporting and analysis tools

• All the data in one place ( especially sales and forecast data)

Requirements Summaries: Whats Missing?

The short answer to whats missing is a lot. This particular summary doesnt include much indication of business value. And one summary will never provide the range of business requirements you get from a full set of interviews. In addition, you would need IT and business analyst representation to get a sense for the level of effort or even availability of some of the data requested . However, there is enough information here to help you make sense of the next few steps.

Analytic Themes and Business Processes

The interview summaries have already grouped similar requirements into common analytic themes. Weve found that its easiest to identify the business processes that support each analytic theme as you write the summary. For example, Sales Planning is an analytic theme that is supported by information from the orders business process. Figure 1.7 is an example of how you might distill Adventure Works Cycles business processes from the analytic themes based on Brian Welkers interview summary. Your final list will include many more themes and business processes.

Analytic Theme	Inferred or Requested Analyses	Supporting Business Process	Comments
Sales Planning	- Reseller historical orders analyses	- orders	By customer, by territory, by sales region (from state)
	- Sales forecast	- orders	Forecast is a business process that uses orders data as an input
Sales Performance	- Orders by current territory	- orders
	- Orders by original territory	- orders
	- Sales rep performance report	- orders - forecast	Orders and forecast by sales rep
Sales Reporting	- Resellers ranked by orders in a given territory	- orders
	- Churned customer list	- orders	Customers who have not ordered in X months
Price Lists	- Current price list	- orders	This is a connectivity issue, not a data warehouse issue
Special Offers	- Relevant customers by territory based on orders history	- orders
	- Inventory status (out of stock)	- inventory
Customer (Reseller) Satisfaction	- Calls by complaint type, product and customer attributes	- call tracking
	- Order metrics of satisfaction	- orders	e.g. due date versus ship date
	- Returns by reseller by return reason	- returns
International Support	- Local language translations of Product descriptions	- n/a (product dimension)	This is a transaction system problem. We need to make sure we can handle multiple languages in the DW/BI system, but the source system has to capture them when new products are created.

Figure 1.7: Analytic themes and supporting business processes from the interview summary

The Supporting Business Process column in Figure 1.7 allows you to group themes that rely on data from the same business process and to identify themes that require data from multiple business processes. Recall that themes that rely on data from multiple business processes, or second level themes, are more difficult to implement. Look for the highly leveraged opportunities where several themes can be delivered with data from a single business process. In most organizations, this opportunity is the sales business process, which Adventure Works Cycles calls orders .

Once you fill in the complete list of themes, re-sorting it by business process will reveal that many of the business requirements rely on data from a few business processes. For Adventure Works Cycles, data from the orders business process is all that is needed to enable a wide range of analytic requirements, many of which are not on Brians list in Figure 1.7. As the full set of requirements would reveal, Sales is not the only department interested in orders data. This is important because it adds to the overall business value of implementing the orders business process. All or most of the following requirements could be met with data from the orders business process:

• Sales planning input

• Basic sales reporting

• Special offers

• Production forecast input

• Product planning and monitoring

• Internet customer demographics (with a well-designed customer dimension)

• Customer profiling

• Customer loyalty program

• Standardized currency reporting

THE NAME GAME IN ACTION

The business processes listed in Figure 1.7 do not necessarily match up with the terms we heard from Brian Welker in the interview. Hes the VP of Sales, and tends to use the word sales to describe the information hed like to track. As it turns out, the term sales has a specific meaning carefully defined by the accounting department based on order dates, ship dates, and accounts receivables. Brian is not actually measured on sales; he is measured on orders. The process of determining business process, table, and attribute names starts here. You need to be as precise as possible in your use of terms. In fact, as soon as you understand the distinction between sales and orders, you should rewrite the interview summary to reflect it. We left the ambiguity in our summary to make the point.

 

Brian also discussed a few consolidated analytic themes that require data from more than one business process (from Figure 1.7):

• Sales performance: Orders and forecast

• Customer satisfaction: Call tracking, orders, and returns

The prioritization process focuses on business processes because these are the coherent units of work for the DW/BI system. In describing each business process, you need to tie it back to the analytic themes it supports, so senior management can assess its business value. The business processes from Brians interview are shown in Table 1.5. Brians supported analytic themes are underlined ; the rest came from other interviews.

Table 1.5: Business Processes Derived from Brian Welkers Interview

LETTER	BUSINESS PROCESS	SUPPORTED ANALYTIC THEMES
A	Orders	Orders reporting and analysis, orders forecasting , advertising effectiveness, customer satisfaction , production forecasting, product profitability, customer profitability
B	Orders forecast	Sales performance , business planning, production forecast
C	Call tracking	Call center performance, customer satisfaction, product quality, customer profitability, product profitability
D	Returns	Customer satisfaction , product quality, customer profitability, product profitability, net sales

By the time youre finished with the interviews and write-ups, you should have a list of business processes that represents the major activities of your organization: 25 to 50 is a reasonable count at this point. Youll leave some items off the list, even though they will be discussed in the document and with senior management. In Brians case, price lists and international support are important issues to his organization, but they are transaction system problems because they involve enhancing transaction systems or building new IT infrastructure. You can help with better reporting, but you shouldnt be dealing with connectivity and data capture issues if you can avoid it.

Adventure Works Cycles Bus Matrix

Business processes are the units of work in building the information layer of the BI system. The bus matrix shows the business processes and the dimensions needed to support them. The more you know about how the business and its supporting systems work the easier it is to define the bus matrix. Your full version of Table 1.5 would be the starting point for an initial version of your bus matrix. Figure 1.8 shows a high-level version of the Adventure Works Cycles bus matrix for the business processes from Table 1.5.

	Dimensions
Business Process	Date	Product	Employee	Customer (Reseller)	Customer (Internet)	Sales Territory	Currency	Channel	Promotion	Call Reason	Facility
Sales Forecasting	X	X	X	X	X	X	X
Orders	X	X	X	X	X	X	X	X	X
Call tracking	X	X	X	X	X	X				X
Returns	X	X		X	X	X	X		X		X

Figure 1.8: The Adventure Works Cycles bus matrix

The bus matrix gets another level of detail during the dimensional modeling process when you add in the different types of fact tables. Youll get more on this in Chapter 2.

The Adventure Works Cycles Prioritization Process

As we described earlier in this chapter, the prioritization process is a facilitated session where the DW/BI team presents the preliminary project findings and facilitates discussion with key business representatives. The primary goals of the prioritization process are to ensure a common understanding of the requirements findings and business processes, and prioritize the business processes based on business value and feasibility. The top priority business process becomes the focus of the initial DW/BI project.

Figure 1.9 shows the resulting grid from the Adventure Works Cycles prioritization session. It includes a few more business processes than we listed in Table 1.5, but its still not a full set.

Figure 1.9: The Adventure Works Cycles prioritization grid

Note that there are two items on the grid that are not actually business processes: Customer and product profitability are consolidated themes that senior management has expressed significant interest in analyzing. These have been included on the grid to show their importance, but they are far over to the left to indicate the difficulty involved in building all the needed business processes. Given the number of analyses supported by data from the orders business process, it should come as no surprise that orders is the top priority theme. The team should get to work on this right away!

Business Requirements for the Orders Project

Getting to work on the orders business process requires holding an additional set of interviews to drill down on orders- related analyses. The team needs to understand several issues that were raised in the enterprise requirements process, such as what the different kinds of regions that people described are. The team would get more specific about the kinds of new reports and analyses people want to see as input to the BI Application track.

All of this information becomes the grist for the Adventure Works Cycles business dimensional modeling process case study in Chapter 2.