Introduction Puglia Winery is a medium sized boutique winery located in Temecula, California, about 85 miles southeast of Los Angeles. The wines produced are sold locally in California, interstate in the US and internationally in the United Kingdom. They specialize in hot climate wines and produce three varieties, pinot noir and merlot (red) and pinot grigio (white). Puglia Winery production and sales have grown dramatically in the last 3 years, and the managing director, Jack Gillespie, 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 Puglia Winery include growing the grapes, producing the wine and selling it. Currently there are 50 acres of the pinot noir, 50 acres of merlot and 35 acres of the pinot grigio. Additional planting is possible but involves a long lead-time. Wine is produced by the winemaker, Tommy Siragusa, who focuses particularly on quality. Puglia 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, Tommy purchases additional high-quality grapes from surrounding vineyards. Stacie Giano is the sales manager. A small amount of wine is sold directly through the cellar door, but almost most all sales are to customers (wine merchants) in California, 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 and which markets to focus on have been made by Jack, with input from Stacie and Tommy. 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 concerning three important business problems, about customers, products 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 (California, The rest of the US, International) but this may change in the future. 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