Page 21 - DCAP603_DATAWARE_HOUSING_AND_DATAMINING

Unit 1: Data Warehouse Practice

(d) Roll Up: Less detail, Quarter->Year notes
(e) Drill Down: More detail, Quarter->Month

(f) Slice/Dice: Selection, Year=1999
(g) Drill Across: “Join”
These are the various methods used for getting multidimensional data out of the data
warehouse.



Case Study fast food

he 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
Taccess to comprehensive information for both standard and on-demand reporting
is essential.
Exclusive Ore designed and implemented a data warehouse and reporting structure to
address this requirement for Summerwood Corporation, a fast food franchisee operating
approximately 80 Taco Bell and Kentucky Fried Chicken restaurants in and around
Philadelphia. The Summerwood Data Warehouse now provides strategic and tactical
decision support to all levels of management within Summerwood.
The data warehouse is implemented in Microsoft SQL Server 2000, and incorporates data
from two principal sources:
1. Daily sales information automatically polled by the TACO system DePol utility.
2. Period based accounting information from the Dynamics (Microsoft Great Plains)
accounting database.
This data is automatically refreshed periodically (or on-demand if required) and is
maintained historically over several years for comparative purposes.

For reporting and analysis purposes, the data in the warehouse is processed into OLAP
Cubes. The cubes are accessed through Excel by using BusinessQuery MD. Data can be
analyzed (sliced and diced) by store, by company, by zone and area, by accounting year,
quarter and period (as far back as 1996), and by brand and concept. The available cubes
and some example analyses are shown below. While each represents an area of analytical
focus, cross cube analysis is also possible.
1. 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, or
rolling 13 period amount, and can be compared to either of two budgets, compared
to the corresponding period from the prior year, or as a percent of sales.
2. BS Cube. Contains the Balance Sheet for Summerwood. Balances can be viewed as
of any period, and can be compared to the preceding period or the corresponding
period in the prior year.
3. SalesMix Cube. Contains daily sales of all menu items in all stores. In addition to
the standard analysis parameters, this data can also be sliced and diced by brand, by
item category or by menu item, by calendar year, month and week, and by pricing
tier. This cube can be used to compute sales amounts and counts, costs and variance
from list price.
Contd...

LoveLy professionaL university 15