Help With Prolog| R Programming|Help With SPSS|Help With Processing
The University of Melbourne
Department of Computing and Information Systems
Overhill Winery Case Study
Introduction
Overhill Winery is a medium sized boutique winery located on the Bellarine Peninsula, about 150 km south
west of Melbourne. The wines produced are sold locally in Melbourne, interstate in Australia and
internationally in the United Kingdom. They specialise in cool climate wines and produce three varieties,
pinot noir and merlot (red) and pinot grigio (white). Overhill Winery production and sales have grown
dramatically in the last 3 years, and the managing director, Sane Yardman, believes that any future growth
needs to be better managed, and based on a better understanding of sales trends. He has heard from
vendors that data warehousing and business analytics will provide him with the capability to make more
informed decisions. You have been hired as a consultant to propose a data warehousing solution to Sane.
The operations of Overhill Winery include growing the grapes, producing the wine and selling it. Currently
there are 20 hectares of the pinot noir, 20 hectares of merlot and 15 hectares of the pinot grigio. Additional
planting is possible but involves a long lead‐time. Wine is produced by the winemaker, Rags Khamseen, who
focuses particularly on quality. Overhill Winery wines can therefore be sold at premium prices. Three types
of pinot noir are bottled and sold under separate brand names. Two types of pinot grigio, and two types of
merlot are similarly produced. To increase production volume, Rags purchases additional high-quality grapes
from surrounding vineyards. She Amido is the sales manager at the overhill winery. The winery works with
sales agents to sell their products. A small amount of wine is sold directly through the cellar door, but almost
all sales are to customers (wine merchants) in Melbourne, interstate and increasingly to the United Kingdom.
Sales to wine merchants are in cases of 12 bottles.
The winery has two separate information systems to help manage production and sales to wine merchants.
Information from these systems is difficult to aggregate as they have different data formats and use
different database management systems.
The Business Problems
Decisions about the growth of the business, in terms of the production volumes of the red and white wine,
which customers are important, profit maximization and which markets to focus on have been made by
Sane, with input from She and Rags. The decisions have been made based on experience and “gut feel”. This
has been effective previously but now that the business has grown, Sane wants evidence based on data to
better inform decisions about customers, products, sales agents, sales time periods and markets.
1. Which products are the most profitable?
The data warehouse should provide information about unit sales and dollar sales, cost and margin for each
product (base product and wine type), for various time periods (including year, season and month).
2. Who are the key customers?
The data warehouse should provide information about unit sales, dollar sales, cost and margin for each
customer (only merchants), for each product (base product and wine type), for various time periods
(including year and season – Autumn, Winter, Spring, Summer).
[Cost = average cost of production of a carton of wine]
[Margin = Dollar sales – Cost]
3. Which market is the most profitable?
The data warehouse should provide information about unit sales and dollar sales for each market for each
month of the previous year. Currently there are three markets (Victoria, The rest of Australia, International)
but this may change in the future.
4. Which time periods are the most profitable?
The data warehouse should provide information about the unit sales, dollar sales and margin for various time
periods including Weekly, Monthly, Quarterly, and Yearly.
5. Who are the Key sales agents?
The data warehouse should be able to provide information about sales agent sales, including the amount of
commission they earn for various time periods.
The Current Information Systems
Data for the data warehouse will be sourced from two of the existing operational systems, the production
system and the merchant sales system. Direct sales have low volumes and will not be included in the data
warehouse.
1. The Production System
The production system is a package based on an Oracle database and runs of a windows‐based computer. A
product code is used to identify base level products. Product groups may also be defined. The system also
supports product cost history. Examples of data in relevant Oracle tables are shown below.
PRODUCT (sample data)
Code Description Group
1 Bellarine Pinot Grigio White
2 Bellarine Pinot Noir Red
3 Downunder Merlot Red
4 Downunder Pinot Grigio White
5 Downunder Pinot Noir Red
6 Overhill Merlot Red
7 Overhill Pinot Noir Red
PRODUCTION HISTORY (sample data, incomplete)
Code Product Year Production Volume $Cost per Dozen
1 1 2010 1120 80
2 2 2010 1090 45
3 3 2010 1349 65
4 4 2010 423 41
5 5 2010 1422 60
6 6 2010 1187 58
7 7 2010 700 50
8 1 2011 3700 84
9 2 2011 3243 51
10 3 2011 4655 63
2. The Merchant Sales System
The merchant sales system is a package based on an SQL‐Server database also running on a windows‐based
computer. Sales order, Product and Customer data are included in the system. Examples of data in relevant
SQL‐Server tables are shown below.
CUSTOMER (sample data, incomplete)
Customer
ID
Name Address Mkt
1 Zelas Wines Archway Road, London , London , N6 5AX Int
2 Oz Wines Little St., Richmond, Melbourne, 3121 Vic
3 London Wines Eco Avenue, The Strand, London, SW1A 1LZ Int
4 The Sussex Wine Company Birdham Road, Chichester, West Sussex, PO20 7DU Int
5 Merchant's Lair Nepean Highway, Mentone, Melbourne, 3194 Vic
6 Australia Wines Direct High St., Stourbridge, West Midlands, DY8 1TA Int
PRODUCT (sample data, incomplete)
Product ID Description Group Year Unit Price (Doz)
18 Downunder Pinot Grigio White 2012 87
21 Overhill Pinot Noir Red 2012 125
22 Bellarine Pinot Grigio White 2013 151
28 Overhill Pinot Noir Red 2013 114
29 Bellarine Pinot Grigio White 2014 167
36 Bellarine Pinot Grigio White 2015 164
SALES ORDER (sample data, incomplete)
Sales Order Customer Date Sales Agent
1 2 2/01/2012 D2
2 3 2/01/2012 D1
3 8 2/01/2012 S1
4 11 2/01/2012 B1
SALES ORDER LINE (sample data, incomplete)
Sales Order Line Product Qty (Doz) Price (Doz)
227 1 19 50 $160
228 1 13 82 $110
229 1 19 29 $110
230 1 10 69 $100
231 1 8 96 $150
231 2 20 94 $100
232 1 15 40 $100
SALES AGENT (sample data, incomplete)
ID Name Commission rate
B2 Arit Arubne 19%
S1 Willy Wonka 9%
B3 Flame Blower 3%