Systems留学生代做、SQL程序语言调试、SQL设计代做

日期：2020-01-12 09:40

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%