Faculty of Information Technology, Monash University FIT5195 Business Intelligence & Data Warehousing
Assignment 2 – Business Intelligence and Data Warehouse Design
Semester 1, 2016
Assignment Overview:
You have been engaged by Crowne Estate to design a business intelligence application and supporting data warehouse. Using the attached case study documents, you will identify the reporting requirements of the senior management users. You will produce prototype report designs and supporting multi-dimensional model in the form of Thomsen diagrams for a planned business intelligence application. You will then produce a multidimensional model specifying a data warehouse database schema in the form of a star-schema, plan an ETL process and provide recommendations for a technical architecture to support the business intelligence application.
The assignment is split into two submissions: the first for the application design, the second expanding on the first to include the data warehouse design.
Assignment Due Dates:
1. Assignment 2a: 4pm AEST, Friday Week 8 (29 April, 2016)
2. Assignment 2b: 4pm AEST, Friday Week 11 (20 May, 2016)
Assignment Value:
1. Assignment 2a: 20% of your overall result for the unit.
2. Assignment 2b: 15% of your overall result for the unit.
Submission Procedure:
Turnitin
Turnitin is an online tool to assist students and staff in understanding and supporting the ethical and appropriate use of materials. Students are required to submit an electronic version (in .doc format and WITHOUT the cover page) of each submission through the Moodle site of this unit.
Turnitin generates the initial originality report quickly. However, if you resubmit your assignment for analysis, there will be a 24-hour delay before a new report is generated.
You are permitted multiple submissions to Turnitin until the due date of the relevant assignment. You may want to make improvements based on the report and resubmit it through the same Turnitin submission link. Each submission will overwrite the previous submission and only the last submission / originality report will be viewed.
Further details can be found at: http://vle.monash.edu/supporttraining/learnbytech/turnitin/index-student.html Submission Procedure
On-campus students are required to submit both a hard copy and an electronic copy of their submissions. Off-campus students are only required to submit an electronic copy.
Electronic Copy
All students should submit their final submissions via the relevant links on Moodle. Submissions for must be in either Microsoft Word or PDF format.
Hard Copy
On-campus students must also submit a printed and bound copy of their submissions. Binding must be spiral or comb binding, available at photocopying services like Office Works (in Australia), or the MAS service desk on Caulfield campus. Malaysia students should enquire with their lecturer regarding suitable locations to purchase this service.
Caulfield students should note that there can be delays in binding with the MAS service desk. Submit the printed copy, with an assignment coversheet, in the assignment submission box on Level 6 of Building H.
Malaysia students should submit directly to their lecturer.
Learning Objectives:
The assignment will allow you to demonstrate and assess your ability to:
• In assignment 2a:
o analyse a typical business intelligence design problem to identify key decision requirements for senior managers;
o select appropriate data visualisations to provide effective decision support;
o design a multi-dimensional model to support the identified requirements;
o draw Thomsen diagrams.
• In assignment 2b:
o develop a physical database schema based on a star-schema approach to provide data warehouse support for multi-dimensional data analysis;
o consider appropriate technical architecture options; o plan an ETL process to populate a data warehouse with data;
Tasks:
For both assignments:
1. Read through the case study files.
2. You will also be able to ask questions of the stakeholders in the case via a dedicated discussion forum on Moodle.
For assignment 2a (Business Intelligence Application Design):
1. Using the information provided, design a series of reports and/or dashboards for the key users to help them find answers to their business questions. You may design these using any software tool you wish. The reports are prototypes only and will be submitted in hard copy, so they do not need to actually work as interactive reports.
2. Design a multi-dimensional schema using Thomsen diagrams to support your prototype design
3. Present your designs in a design specification report. Make sure to present your report professionally. You should include a proper cover-page, table of contents, and an introductory overview of your design that outlines your understanding of the key business requirements and provides an overview of your solution.
This section should also justify any key design decisions you’ve made, both in regard to the data visualisations chosen and the design of the multidimensional schema.
This should be followed by your prototype reports (annotated if necessary to explain your design concepts) and then the multi-dimensional model. You may add further sections to the report if you feel it necessary.
For assignment 2b (Data Warehouse Design):
1. Design a data warehouse schema using the star-schema approach based on your Thomsen diagrams in assignment 2a.
2. Consider options for the technical architecture for the data warehouse, recommending a structure with justifications.
3. Prepare a data dictionary for your star schema design that includes plainlanguage definitions for all entities and attributes. Further, for each attribute, you should indicate how the data for that attribute is obtained or derived, where relevant making reference to the source systems’ database schemas (see attached transaction processing system database schemas).
4. Supplement your design report from assignment 2a by adding in your data warehouse design. You may wish to amend your introductory overview to take into account the data warehouse content of the report. You should present your data warehouse schema, discussion of technical architecture options and data dictionary with mapping to the source systems as sections following the material already submitted in assignment 2a.
Deliverables:
Both submissions must be presented as spiral-bound hardcopy design specification reports, along with an attached, signed Monash University assignment cover page.
Your assignments must be appropriately bound (spiral or comb binding is available at the student service desk or from places like Office Works) and include a signed assignment coversheet or it will not be accepted for submission
Assessment Criteria:
Assignment 2a:
1. Quality of support for the business requirements
2. Quality of the prototype reports, in particular the appropriateness of the data visualisations chosen.
3. Correctness of the Thomsen diagrams in providing an appropriate multidimensional structure to support the included prototypes.
4. Quality and professionalism of presentation, including layout, structure and grammar.
Assignment 2b:
1. Correctness of schema construction, including data sourcing
2. Quality of design and justification
3. Identification of business requirements
4. Quality of discussion of architectural options
5. Quality and professionalism of presentation
Special Consideration
Applications for special consideration must be made to your lecturer using the form linked to on Moodle. All applications must be supported by documentation. Extensions will not be granted for assignment workload in other units, employment workload (except in the case of a sudden, unanticipated change of circumstance), or any other situation which could reasonably said to have been foreseen.
Penalty for Late Submission
Assignments submitted after 5pm on the due date without an approved special consideration application will attract a penalty of 10 marks per day, with weekends counting as a single day. No assignment will be accepted after 5pm of the Friday on the following week of the due date.
Plagiarism and Collusion
Note that these are individual assignments. While general collaboration between students in terms of understanding of modelling concepts is acceptable, the assignment and the solution contained in it must be your own work.
You must not:
• Use another student’s work (current or past) as the basis for your own.
• Use another student’s work to help ‘give you ideas’
• Steal, appropriate or make use of the work of another student without their knowledge.
• Lend your work to another student for any reason
• Use the ideas, words or other intellectual property of anyone without proper attribution.
Penalties for plagiarism and/or collusion can include formal reprimands, notes being attached to your student file, failure in the assignment, failure in the unit or even suspension or exclusion from the university.
See the Unit Guide for more information on plagiarism and cheating, and for links to Faculty and University policies on this topic.
The Case – Stewart Brothers Wines Pty Ltd
In 1841, Herbert Stewart of Claire, Scotland was transported for seven years to the penal colony of New South Wales (NSW). His crime was larceny. Because he could read and write he served his time as a Government Clerk in the Lands Office. On receiving his freedom he decided to settle in Australia and received a land grant in the lower Hunter Valley. In 1853 the British Government paid for the passage of his wife, Elizabeth, and their three sons to NSW.
Elizabeth and her sons arrived at Herbert’s farm, called “Nangarry”, to find a thriving sheep farm with a substantial homestead. Herbert Stewart had become a successful grazier. Two of the sons, William and Bruce, had spent seven years working in the vineyards and wineries of Bordeaux and Burgundy. The countryside around Nangarry greatly reminded them of the French wine making areas. They pressed their father for permission to plant a few acres of vines and experiment with wine making. Herbert was sceptical but agreed to their plan.
By 1878 wine sales had surpassed wool as the major source of income for the Stewart family. The wines were of poor to average quality, exactly what the convicts and settlers of the new colony could afford. The family boast was that every man and woman in NSW drank Stewart Brothers wine.
Stewart Brothers Pty Ltd
Over one hundred and fifty years after the first vintage, Stewart Brothers Pty Ltd is one of the largest private companies in Australia. The company remains wholly owned by the Stewart family. The company grew though a series of acquisitions of vineyards and wineries that expanded and improved the product offering. The most recent acquisition was Birubi Plains Wines Pty Ltd. Stewart Brothers now grows grapes and makes wine in NSW, Victoria, South Australia, Western Australia, New Zealand and Chile. The original vineyard at Nangarry is still in production. The major labels owned by Stewart Brothers and their market focus is shown in Exhibit 1.
In many ways the company has remained close to its roots. Lower quality tables of the “Stewart Brothers” label still generate the bulk of the company’s revenue. These wines are sold in bulk to hotels and other hospitality companies, as well as in casks and bottles to the general public. In addition there are a number of medium and high quality labels that are mainly sold to restaurants. Birubi Plains produces a range of very high quality table wines. Stewart Brothers’ premier wine is named after the founders, William & Bruce. It is a low volume red wine that sells for over $190 a bottle.
Stewart Brothers’ corporate headquarters is located in Sydney on eight floors of the
Chiefly Square building. There are sales offices in every major Australian city as well as Auckland, London, Paris, Rome, New York, San Francisco, Mexico City, Santiago, New Delhi, Singapore and Tokyo. Around 40% of Stewart Brothers’ revenue comes from exports.
A partial view of Stewart Brothers’ organizational structure is shown in Exhibit 2. The structure shows the strong family control of the enterprise. The Board of Directors is elected by over 100 family members. All Directors are family members. The company is divided into three major divisions: Operations, Corporate Services and International Affairs. In order to provide non-family input to Board decision-making, an Advisory Board was formed in 1982. Members of the Advisory Board have no financial responsibility and include the general mangers of all Stewart Brothers wine labels, representatives of the hotel and restaurant sectors, the President of the Australian Wine Association, a banker, and a trade unionist.
The Executive Committee is the most important formal committee at Stewart Brothers. The Committee comprises the Chairman and CEO, and the Directors of the three divisions. It meets every Monday morning.
The Operations Division is responsible for all wine making and viticulture activities. The Chief Wine Maker and Chief Viticulturist have Group General Manager status and are responsible for general standards and practices in every label. Each label is a private company wholly owned by Stewart Brothers Pty Ltd. A General Manager heads each label which also employs wine makers, viticulturists and other winery workers. Some of the larger labels have their own marketing and administrative staff.
The Corporate Services Division is located in Sydney and covers all finance, treasury, marketing, administration, personnel, legal, logistics, and IT functions. Corporate Services employs 328 staff.
The International Affairs Division was formed four years ago. It was established with a mission to dramatically increase the sales of Stewart Brothers wine overseas. Each major wine-consuming region has a General Manger who lives permanently in the region and is able to build a large network of contacts. This policy of closely integrating overseas sales staff with their market has been a considerable success. Sales are up 60% on the previous regime. Some tension remains between the International Affairs Division and the Group Marketing and Operations areas.
William Stewart, the great-great-great-grandson of Herbert, is the Chairman and Chief Executive Officer of Stewart Brothers. At 48 he has been CEO for almost five years. All of the Divisional Directors are considerably older. His appointment by the Board to replace the legendary Henry Stewart, who died in the office at 72, was controversial within the company and the family. William completed a BA (Hons) in philosophy and linguistics at the University of Sydney and an MBA at Harvard University. He has worked in the company all his working life, first as an assistant wine maker, then as General Manager of Eastern Vales. He gained experience in marketing and administration at head office. Before his appointment as CEO he was the Group General Manager of Finance.
On appointment, William strengthened the membership of the Advisory Board and created the International Affairs Division. He vaguely floated the idea of abolishing the division structure but never took a proposal to the Board. His often-stated goals are to significantly increase export sales and the quality of Stewart Brothers wine. He was the driving force behind the Birubi Plains acquisition. William stunned the Board at its last meeting by suggesting that Stewart Brothers should diversify into beers and mineral waters. Many on the Board (including some who voted for his appointment) have criticized the rising level of debt that has been used to fund overseas expansion.
Gemma Reynolds was personally appointed by William Stewart. She is the Group
General Manager of Marketing for Stewart Brothers. The previous Group General Manager of Marketing resigned in protest over the formation of the International Affairs Division. Gemma has a BBus (Marketing) from Monash University. Previously she was the Marketing Director for a French perfume house. Gemma is passionate about the wine industry - her father was a noted wine maker for a Stewart Brothers competitor and she grew up playing around the winery. She has been criticized by some in the company for ‘Gen Y-ing’ Stewart Brothers’ image.
Birubi Plains
Birubi Plains is a boutique winery and vineyard in the King Valley in north-eastern Victoria. It produces very high quality cool-climate table wines. For the past decade it has been the most awarded medium size winery in Australia. Its wines are served in the best restaurants and the label has a cult following amongst food and wine lovers. Birubi Plains wines command premium prices.
Birubi Plains was founded in 1976 by Dr Chris Morrison, a noted pathologist. Tired of the “daily grind” of post mortem examinations and laboratory work, Dr Morrison searched for four years for land with the desired soil, rainfall, drainage, and temperature range. The first vintage was in 1981. In 1998 Dr Morrison sold Birubi Plains to Stewart Brothers for an undisclosed sum. At 70 he was unable to manage the physical demands of the winery and his son and daughter, both surgeons, had no interest in wine making. Several large wine companies bid for Birubi Plains and the general business opinion is that Stewart Brothers paid an excessive amount for the acquisition.
Scott Mason is the General Manager and Wine Maker of Birubi Plains. Scott was the wine maker/owner of Bay of Islands Wines before selling the winery and vineyard to Stewart Brothers in 1994. After the sale he entered semi-retirement and was retained by Stewart Brothers as a cool-climate wine consultant. Scott studied wine making in New Zealand and France and is probably New Zealand’s most awarded wine maker. He is also an international wine judge. Many in the industry were surprised that Scott gave up his retirement to take the job, but he had become bored and the challenge and prestige of running Birubi Plains was very appealing. Scott has a reputation for “run ins” with Head Office (especially with the Chief Wine Maker) although he is a longterm friend of William Stewart.
The wines currently produced by Birubi Plains, their volume and prices, are shown in Exhibit 3.
The Information Technology Environment
The IT Department is located at Head Office in Sydney. It employs 43 people including ten short-term contractors who are working on new systems development. The organization chart of the Department is shown in Exhibit 4. The Department is divided into three sections: Operations, Systems and Special Projects. Each section is further divided into a number of teams or projects that range in size from one to eight people. The Operations Section is concerned with the information technology infrastructure of the company while the Systems Section is responsible for the information systems applications that support the business. Special Projects has one team that is studying new technologies; the other is the business intelligence project. The technical architecture environment is described in Exhibit 5.
Tim Rawson, the long-term Group General Manager of IT, has just resigned. For many years Tim pushed a strong centralist line on information technology provision, insisting that virtually all data processing occurred within the IT Department. Two years ago the Executive Committee reluctantly approved a major upgrade of core systems. The Executive Committee now is searching for a new IT leader and wants this new appointment to consider outsourcing much of Stewart Brothers’ IT.
Integrating Birubi Plains with Stewart Brothers
For the first year under Stewart Brothers’ ownership, the operation of Birubi Plains was basically unchanged. The only personnel change was the replacement of Chris Morrison with Scott Mason. William Stewart pushed the acquisition through the
Board as a key strategy towards his goal of raising the quality image of Stewart Brothers. While the Birubi Plains purchase earned immediate kudos for Stewart, it was never William Stewart’s intention to leave the label unchanged. His plan was to use both the product and the brand name to reposition Stewart Brothers. This strategy was not canvassed at the Board meeting that considered the acquisition.
The Executive Committee is now considering how to best operate Birubi Plains as a Stewart Brothers asset. They have decided that Scott Mason and Gemma Reyolds be commissioned to write reports on the future of Birubi Plains’ operation and marketing. A memo from Gemma is shown in Exhibit 6 and a letter from Scott in Exhibit 7. They show that Scott is extremely protective of Birubi Plains’ quality and that Gemma is bullish on export and expansion. William Stewart is concerned about both extremes and hopes that the Business Intelligence Project will help them to arrive at a suitable strategy.
The Business Intelligence Project
The Business Intelligence Project was formed two months ago and has the brief of building systems to support the activities of management, particularly the senior executives. This team presently has one member, Sally Heinz, an experienced systems analyst with a Master of Business Information Systems from Monash. An analyst/programmer from the Retail Systems team has been assigned to assist Sally and will join Management Support in a week’s time. This analyst/programmer is a recent information systems graduate with a passion for Big Data.
The Steering Committee for the BI Project includes William Stewart, Craig Williams (Director, Corporate Services), the new Group General Manager, IT (when they are appointed), and Gail Khan (Manager, Special Projects, MIS Department). To date the committee has decided that the first BI application will focus on Birubi Plains. Both Gemma Reynolds (Group General Manager, Marketing) and Scott Mason (General Manager, Birubi Plains) have agreed to devote reasonable amounts of time to the development.
Many senior executives (directors, group general managers and general managers) are suspicious of the BI Project. Bill Stewart, General Manager, Stewart Brothers Wines who is responsible for 65% of all Stewart Brothers revenue, has been a particularly vocal opponent of the project and sees it as an attempt to exert greater management control over his company. The choice of Gemma Reynolds and Scott Mason as the first users has reinforced this view because both are recent appointments with no Stewart family ties and both are strongly linked to William Stewart.
Others see the project as a long overdue development as they have been hearing about decision support systems and business intelligence systems at conferences and trade shows for many years. A group of General Managers have written to William Stewart supporting the project, requesting that a representative of their management level join the steering committee.
The Data Warehouse Project
The need for a data warehouse has been identified as part of the Business Intelligence project. Although the focus of the project is currently the operations at Birubi Plains, the requirements for the data warehouse extend to supporting a rollout of the BI system to all of Stewart Brothers’ wineries.
In discussions with members of the BI project steering committees, as well as Gemma Reynolds and Scott Mason, it has been determined that the data in the warehouse should be updated on a daily basis. Reports will give a weekly breakdown of sales of all wine in each market (with the current month reporting on sales up to the previous business day). Wholesale, cellar door and retail prices are reviewed on a monthly basis. Even though Birubi Plains wine isn’t currently exported overseas, the data warehouse will need to support reporting on sales to international markets.
The initial requirements from Gemma and Scott stem from a need to monitor how well sales perform for the various wines as a result of different marketing campaigns devised by Gemma. Scott is particularly interested in being able to predict trends in consumer tastes for particular grape varieties to assist with planning each year’s crop allocation.
A copy of the database schema for a marketing database developed for Birubi Plains is provided as Exhibit 8. This marketing database was developed prior to Birubi’s acquisition by Stewart Brothers, and has continued in operation at the winery, independent of head office but with support from the IT department. An extract of the database schema for the sales management system is shown in Exhibit 9. This sales management system is a central IT system for managing sales at all of Stewart Brothers’ wineries, and Birubi Plains sales data from prior to the acquisition was ported to the platform twelve months ago.


Exhibit 3: Birubi Plains Wines
Wine Style Wine Volume Wholesale Cellar Retail
Rating1 (‘000 Bottles) Price Door Price Price
Semillon Dry white table 9 42 11.81 19.01 21.90
Chardonnay Dry white table 10 39 14.85 25.28 26.50
Reisling Dry white table 8 48 10.49 18.11 19.90
Cabernet Sauvignon Red table 8 52 12.80 18.43 20.40
Pinot Noir Red table 7 20 13.66 20.67 23.70
Late Picked Semillon2 Dessert
10 8 8.88 12.78 14.45
1 Ratings in the Guide to the Best Australian Wine (10=best of its kind) 2 Half bottles only (375ml)
Exhibit 6
Exhibit 7
BIRUBI PLAINS
Fine Wine from Stewart Brothers Wines Muster Lane, Oxley VIC 3678
(03) 5314 1234
Mr W. Stewart
Chairman and Chief Executive Officer
Stewart Brothers Wines Pty Ltd
PO Box 187
Sydney NSW 2001
Dear Bill,
About six months ago you asked me to prepare a report on the future of Birubi Plains. Stewart Brothers seems to thrive on reports - I don’t know how you stand it. Anyway, I sent the report to you last week but I’d thought I’d drop you a line to make sure you knew where I stand.
When you offered me Birubi Plains I was a bit apprehensive as the ethos of Birubi was so different to the Stewart Brothers giant. Bay of Islands has kept its original focus and quality since I sold it to Stewart Brothers so I thought a jewel like Birubi would continue on as before - that “before” gets more medals than the rest of Stewart Brothers’ labels combined. What I said in my report was that if anything Birubi has improved in quality over the past couple of years. I’m sure you’ve tried the ‘13 Chardonnay I sent you in September. Forget medals, this one’s a trophy!
Gemma Reynolds has been down here talking about increased production and hitting the European market. She wants to change the bottle labels. She’s even got me on “The Project”. Fortunately she knows a bit about wine. I trust her marketing nous but I’m not sure about this expansion talk. I am dead against trucking grapes in from other vineyards. I’m not being snobby, it’s just that the Birubi taste comes mainly from the fruit. The secret is not to muck about with it too much in the making.
If volume is a problem we could pull back on the pinot and boost the cab. That could give us about another 15,000 per year, although the lead time to reach that would be about 5 years. The shiraz that was planted 3 years ago could produce something next year but you wouldn’t want to have “Birubi Plains” on the bottle. I am happy to truck out!
I reckon that if quality across Stewart Brothers needs a lift we have to have better wine-making standards - particularly in the Hunter. How about coming to the King Valley, giving a hand with the ‘15 semillon and having a talk about it?
Regards to Sue and the kids,
Scott Mason
General Manager and Wine Maker 21 February, 2016.
Exhibit 8
Birubi Plains Marketing Database v.1.3

Exhibit 9
Stewart Brothers Wines
Partial Extract of Sales Management System