Case Study For Data Mining 591252

CASE STUDY : DATA WAREHOUSE AND OLAP FOR THE FAST FOOD INDUSTRY

CSE- 502

essential. Summerwood Corporation is a fast Abstract: Data mining is an evolving field.

food franchisee operating approximately 80

Data Mining is gaining popularity as an

Taco Bell and Kentucky Fried Chicken

effective tool for increasing profits in a

restaurants in and around Philadelphia. They

variety of industries. It is widely used in

implemented

many of the applications. Few of the

reporting structures through Exclusive ore,

examples include academic medical centre,

which

fast food industries Etc. The importance of

corporation.

accurate, accessible data is paramount. A

implemented in Microsoft SQL Server 2000,

well designed data warehouse can greatly

and data is incorporated from two principal

enhance the effectiveness of the data

sources: One is the Daily sales information

mining process. In this case study, the

automatically polled by the TACO system

importance of

data warehouse and the

De-Pol utility and other source is Period

OLAP operations in the fast food industry

based ing information from the

is discussed. it also discuss the importance

Dynamics

of some aspects of the physical design and

ing database.

the

designed The

data it

for data

(Microsoft

warehouse

and

Summerwood warehouse

Great

is

Plains)

maintenance to the data mining process. 2. OLAP CUBES The data in the warehouse is processed into

1. INTRODUCTION

OLAP Cubes for reporting and analysis One of the most critical steps in any data mining project is obtaining good data. Good data can mean many things: clean, accurate, predictive,

timely,

accessible

and/or

actionable. The Fast Food industry is highly competitive, one where a very small change in operations can have a significant impact on the bottom line. For this reason, quick access to comprehensive information for both standard and on-demand reporting is

purposes. Data can be analyzed by store, by company, by zone and area, by ing year, quarter and period and by brand and concept. • PL Cube: Contains Profit & Loss,

Cash Flow and EBIDTA statements for Summerwood. Amounts can be viewed for any period as a period, quarter-to-date, year-to-date.

• BS Cube: Contains the Balance

sliced and diced by a comparable

Sheet for Summerwood. Balances

indicator, by calendar year, month

can be viewed as of any period, and

and week, and by pricing tier. Gross

can be compared to the preceding

sales, taxable sales, non-tax sales,

period or the corresponding period

manual over/under, deletions, labor,

in the prior year.

cash

• Sales Mix Cube: Contains daily sales of all menu items in all stores. In

•

over/short,

deposits

and

average check are available. •

Report Card Cube. Contains the

addition to the standard analysis

daily report card amounts. Some of

parameters, this data can also be

these are also in the Sales Ops cube.

sliced and diced by brand, by item

In

category or by menu item, by

contains speed-of-service and peak

calendar year, month and week, and

–hour information.

by pricing tier. This cube can be

The Summerwood data warehouse

used to compute sales amounts and

and the resulting OLAP cubes

counts, costs and variance from list

permit investigation along any of

price.

these corporate hierarchies – i.e., by

Sales Day Part Cube: Contains sales

operating company, by zone or area,

amounts and counts at 15 minute

or by brand or concept. It is easy to

intervals. In addition to the standard

do area-to-area comparisons, or

analysis parameters, the data in this

zone-to-zone comparisons, or to

cube can also be analyzed by

view the performance of all stores

calendar year, month and week, and

within an area.

addition,

the

Report

Card

by eight-hour, four-hour, two-hour, one-hour and 15 minute intervals, or

•

3. REPORTS GENERATED WITH

by specific meal e.g., lunch, dinner,

THE

HELP

breakfast, between-meals, etc.

WAREHOUSE

OF

DATA

Sales Ops Cube. Contains daily

There are few examples of the reports

sales summary for each store. In

generated by the Summerwood corporation.

addition to the standard analysis

Each report can be generated for a particular

parameters, this data can also be

store or store aggregation, i.e., zone, area,

company, concept or brand. In addition,

•

MENU MIX AS PERCENT OF

each report can be generated for a specific

SALES: This report compares sales

time period or sequence, i.e., day, week,

percentages of different menu item

period, quarter, or year. When a particular

categories e.g. Beverages, KFC and

aggregation is displayed in a row or column,

menu items e.g., Chalupa, Combos

a can double click on that aggregation

across different concepts and brands.

to drill down to the next lower level e.g., the

s can drill down on any row or

stores in that area, or the periods in the

column simply by double clicking.

quarter, thereby revealing what is there in

•

QUARTERLY NET PROFIT BY

the next lower level of detail. Few examples

CONCEPT: This report shows how

are below:

net profit as a percent of revenue

•

•

DAILY REPORT CARD: The

change over certain period of time.

daily report card is intended to show,

This chart is easily changed on-the-

each day, how a store or concept

fly, simply by answering prompts, to

performed on that date. The Report

a chart that shows any amount from

Card is also generated for each store,

the Profit and Loss, either as a dollar

so that a store or area manager can

amount or as a percent of revenue, or

quickly see how that store compares

even as a percent of budget, for any

to the concept, in general, and how

range of time as years, quarters or

that store compares to itself, last

periods, and for any concept, area or

year.

zone.

TB PL 20-20: This report uses charts to show the top 20 and bottom

4. CONCLUSION

20 Taco Bell stores with respect to a specified in the Profit

In this case study, the Fast Food industry

and Loss statement. Instead of year-

is depicted using the data warehouse and

to-year change, the chart can be

OLAP as a part of their istrative

easily modified to show the actual

work. Here the example used for this is

amounts or amounts as a percentage

Summerwood. They have developed the

of sales.

data warehouse through Exclusive ore.

The various reports generated through the data warehouse are listed.