Reference no: EM131053254

Assignment - Business Intelligence and Data Warehouse Design

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.

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 Part A (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 multi-dimensional 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 Part B (Data Warehouse Design):

1. Design a data warehouse schema using the star-schema  approach based on your Thomsen diagrams in assignment part a.

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 plain-language 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 part a 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 part a.

The Case - Stewart Brothers Wines Pty Ltd.

Attachment:- Assignment.rar