Hamburger Haven Data Warehouse Case Study
Background
In 1992 Paula Englehart conceived of the idea of a chain of high quality
fast food restaurants that would appeal to a clientele that, like
herself, was tired of the usual strip of fast food chains that seemed to
dominate every highway and mall. It didn't seem to make any difference
where she was; the same names, such as McDonalds's, Burger King, Pizza
Hut, Taco Bell, KFC and so forth, were sure to be found.
Realizing that a chain of full service quality restaurants would not be
practical for time conscious travelers, she decided upon a limited menu
of high quality entrees and beverages. They would be served in a
tastefully decorated gallery-like setting with light classical music
or jazz in the background instead of Top 40 selections. It was Paula's
belief that middle class and professional people would see this concept
as a kind of oasis in the midst of mediocrity, and was convinced that
they would be willing to pay accordingly. After considerable thought
and consultation with friends, she decided to capture this idea with
the brand name of Hamburger Haven, which would capitalize on
the popularity of hamburgers and the "oasis" idea. Hamburger Havens
would be cafeteria style and drive-in service, but tables would be
bused.
Paula received initial financing for the new restaurant venture in 1994
and had opened her first twenty "Havens" by the summer of 1995 in several
test markets in the northeast. These first twenty restaurants were well
received by the public and this provided the leverage for Paula to seek
further financing. Initially all restaurants were managed by company
trained employees, hand picked by Paula herself. To encourage quality
management and strong sales, Paula established a generous manager
incentive plan for meeting and surpassing sales targets. Paula soon
realized, however, that the use of franchises was the only route to fast
expansion. Paula screened potential franchisees very carefully to
ensure consistency of quality across all restaurants and a focus on
customer service.
Today, there are 600 Hamburger Havens spread across the U.S. They are
organized into six regions: Northeast (NE), Middle Atlantic (MA),
Southeast (SE), Midwest (MW), Southwest (SW), and Pacific Coast (PC).
Company employees manage one hundred and fifty, while the remaining 450
are run by franchisees. Most restaurants are open from 7:00am to
11:00pm.
The Menu
All restaurants offer six standard meals (the haven hamburger, lemon
chicken, eggplant parmesan, salmon Florentine, macaroni and cheese
(for the kids), and the manger's option. The manager's option is
usually a regional favorite selected by the manager.
There are three beverage groups, sodas, coffee-tea, and dairy (milk,
shakes, and hot chocolate). Desserts consist of ice cream or frozen
yogurt, key lime pie, and fresh fruit.
Restaurants
There are three basic restaurant styles as follows:
- Large with "kids play area" with seating of at least 100;
- Medium with seating of 60-99;
- Small with seating of less than 60.
Locations
Restaurants can be found in two locations:
- Along highways or high-traffic local streets;
- Large, usually somewhat upscale malls and shopping centers.
In-Restaurant versus Drive-in Service
All restaurants, except those located in malls, serve both seated
customers and drive-in customers. A customer who requests a take-out
order is counted as a "drive-in" customer, since they do not occupy
table space.
Paula's Problem
Hamburger Haven's have proved to be very successful, but the company is
experiencing some "growing pains." Paula and her team of Regional
managers have been so busy just expanding the business that they have
had relatively little time to manage it. Paula feels that she does not
have enough information regarding sales broken down by region, day of
week, size of restaurant, type of management (company managed versus
franchise), restaurant location. She also would like to learn more
about meal preferences by region. How is salmon Florentine doing in
the southeast for example.
The Assignment
Design a sales data mart for the Hamburger Haven's chain. This will
require you to:
- Determine the grain of the fact table;
- Decide what dimensions will be required;
- Select the attributes that should go into each dimension;
- Design the primary key of the fact table;
- Select the fact table attributes;
- Evaluate the additivity of each fact in the fact table;
- Determine whether the fact table should hold any degenerate dimensions.
Hint
Once you have developed a preliminary design, go back and review Paula's
information needs. Based on your design, can you form SQL queries that
will support Paula's information needs? If you cannot, or if the required
queries are unusually complex, you probably need to rethink your design.