Lesson 3: Business Scenarios

One of the most critical factors in the success of a data warehouse project is the clarity of the goals. For example, if an inventory data warehouse does not track all the necessary facts about inventory, it may not meet the needs of the organization. Another critical factor is the determination of the facts that should be in the warehouse. A data warehouse concerned with customer satisfaction might track different information than one concerned with cost control. This lesson presents business scenarios that provide you with an opportunity to walk through the process of determining goals of a data warehouse. Determining which facts a data warehouse will store is covered in greater detail in a later chapter.

After this lesson, you will be able to:

• Understand how data warehousing meets the needs of various business scenarios
• Infer data warehousing goals based on business scenarios

Estimated lesson time: 25 minutes

Business Scenarios

There are many reasons to build data warehouses, as illustrated by the following two business scenarios. This next section examines how data warehouses could be used to solve business problems and enhance analysis.

Sales Tracking

In this first scenario, a company wants to gain an understanding of its sales data.

Business Methods

The company Wide World Importers has the following business methods:

• It sells over 50 products worldwide.
• The products are in four categories: nuts, bolts, screws, and nails.
• Wide World Importers divides the world into four large regions.
• Each region is divided into multiple zones.
• Each zone is divided into territories.
• Each customer is located in a territory.
• There is at least one manager per territory.
• Territory managers report to zone managers, who report to regional managers.

Wide World Importers records every sale of every product to every customer. This detailed data storage generates hundreds of millions of records per year, and the storage requirements mean that only one year of raw data can be stored at a time. Query response time is also too slow.

Goals

Wide World Importers has decided to build a data warehouse in order to store more than one year of data at a summarized level.

In order to achieve these goals, Wide World Importers hopes to be able to

• Spot trends that it has been unable to identify
• Perform queries to spot trends
• Start with a product group at the region level and drill down by either product or region, in various time increments

If one product or group shows a large drop in sales in one zone or territory, it may indicate a new competitor has entered the market.

Parts Tracking

In this scenario, a company wants to have a common repository for parts.

Business Methods

The company Tasmanian Traders has the following business methods:

• It has a number of factories around the world.
• Each factory has its own parts buyers.
• The buyers purchase parts from preapproved vendors as needed.
• Each factory tracks parts using its own internal part numbers.
• There is no central database of parts.

Goals

Tasmanian Traders wants to have a common repository for parts. In a departure from normal data warehousing, one facet of the repository will be an OLTP inventory system. However, there will also be an OLAP system that shows historical buying trends by part and by supplier for each factory and for the enterprise as a whole.

In order to achieve these goals, Tasmanian Traders must ascertain

• How many of each part it is buying in a certain time period
• What parts it is buying, and how much business is being done with each supplier.
• Part usage trends

Tasmanian Traders also wants to explore the idea of a central purchasing system that buys parts for all factories. To do this, it must understand historical purchasing.

For the company to perform this analysis, part numbers must be consistent across factories.

Lesson Summary

Different businesses will have different goals in setting up a data warehouse. In fact, one business could even have different goals for different data warehouses. For example, a mail-order business might wish to track shipping efficiency and long-term customer buying trends. This may be done best with either one or two data marts or even data warehouses. The important thing when evaluating business scenarios is to try to determine the overall goal of the data warehouse. Also, pay attention to what is being measured and how the data is organized. This awareness will help in later design decisions.