Page 22 - DCAP603_DATAWARE_HOUSING_AND_DATAMINING

Data Warehousing and Data Mining

notes 4. SalesDayPart Cube. Contains sales amounts and counts at 15 minute intervals.
In addition to the standard analysis parameters, the data in this cube can also be
analyzed by calendar year, month and week, and by eight-hour, four-hour, two-
hour, one-hour and 15 minute intervals, or by specific meal (e.g., lunch, dinner,
breakfast, between-meals, etc.).
5. SalesOps Cube. Contains daily sales summary for each store. In addition to the
standard analysis parameters, this data can also be sliced and diced by a comparable
indicator, by calendar year, month and week, and by pricing tier. Gross sales, taxable
sales, non-tax sales, manual over/under, deletions, labor, cash over/short, deposits
and average check are available.
Many amounts can be viewed optionally as variances, as a percent of sales, or summarized
as week-to-date, period-to-date, year-to-date, or rolling 52-week amountsReportCard
Cube. Contains the daily report card amounts. Some of these are also in the SalesOps cube.
In addition, the Report Card contains speed-of-service and peak -hour information.

The data structure implemented for Summerwood allows them to maintain several distinct
organizational structures in order to properly represent each store in (1) the corporate
structure, i.e. the subsidiary to which they belong, (2) the operations structure, i.e. the zone/
area and (3) the concept structure, i.e., KFC, TB-PH (a Taco Bell – Pizza Hut combination
restaurant), etc.
The Summerwood data warehouse and the resulting OLAP cubes permit investigation
along any of these corporate hierarchies – i.e., by operating company, by zone or area, or by
brand or concept. This permits comparisons between concepts, say, or of all stores within
a concept. Similarly, it is easy to do area-toareacomparisons, or zone-to-zone comparisons,
or to view the performance of all stores within an area.
The data warehouse also supports a time dimension based on the 13 period calendar under
which Summerwood operates. This calendar has been built into the warehouse, permitting
easy comparison of any period to the prior period or to the same period in a prior year.
Instead of comparing at the period level, comparisons and trends can be done at quarterly
or annual levels. Lower level examination is also possible, e.g., comparing week -to-week
or even day-to-day.

The PL and BS cubes contain the full Profit and Loss and Balance Sheet statements for each
period during the last five years (65 periods in all), down to the account level. This makes
it easy for Summerwood to evaluate trends in any expense category, comparing store-to-
store, period-to-period, zone-to-zone, or concept-to-concept.
The SalesOps and SalesMix cubes are updated overnight and contain up-to-the-minute
(through yesterday) information captured by the cash registers (POS) in each store. This
enables managers to evaluate and compare trends in speed of service, labor usage, over/
under rings, employee food purchases, etc., by store, zone, area, concept, subsidiary, etc.
Because sales and counts are recorded in 15-minute intervals, called day parts, managers
can use this to find strange sales patterns, possibly suggestive of employee theft, during
the midnight hours.

1.9 summary

l z Data warehousing is the consolidation of data from disparate data sources into a single
target database to be utilized for analysis and reporting purposes.
l z The primary goal of data warehousing is to analyze the data for business intelligence
purposes. For example, an insurance company might create a data warehouse to capture
policy data for catastrophe exposure.

16 LoveLy professionaL university