Assignment 2: Data Warehouse
Due Date and time:
23:00h, Sun, 13 Jun 2021
Submission: a pdf document containing all solutions.
This assignment aims for you to apply your knowledge to a data warehouse application. The assignment covers most areas of a warehouse application. You work individually on the project.
Based on the database you designed in Assignment 1, design a warehouse star schema and its associated implementations. More specifically you complete the following tasks.
Copy the conceptual design for the operational database you achieved in Assignment 1 and paste it in this assignment. Correct errors in and make changes to the design of Assignment 1 as necessary to support the star schema (see next task) of Assignment 2. No mark is awarded to this step, but you must not miss this step as it sets up the context for your warehouse design. Without this context, the marker will not understand your solutions in the following steps.
Identify an analytic subject in your application and justify why such a subject is interesting to the decision making. The subject must be measured with at least one key performance measurement. You also describe how the measurement(s) can be derived from your source data.
Design a star schema for the warehouse to support the analysis of the subject by identifying dimension tables for the schema. Draw the schema in a diagram. Your diagram may be simple, but the concepts in the diagram must be correct.
Show the reasons why the dimension tables are needed in the analysis, that is, how the dimension tables affect the measurements in the fact table.
List SQL statements that create your warehouse tables. The names of your warehouse tables should start with “wh_”. The statements must be included in the report as text and have been tested without any error.
Design and insert sample data for the tables in the schema. The amount of sample data should be enough to demonstrate multiplicities of relationships. Show the sample data as screenshots. No insertion statements are needed in the report.
Design SQL statements to load and update data from the source tables to the warehouse tables. (3.1) Design SQL statements for loading data from your operational database (what you had designed in assignment 1) at the first running of the warehouse. (3.2) Design SQL statements for maintaining the warehouse data during the running of the warehouse. For each statement, define when it shall be executed. The statements should also cover both the dimension tables and the fact table.
There is no unique solution to this assignment. It is very open.
To make your presentation clear, you may need to use tables, figures, and itemization.
The presentation of concepts and operations in individual sections needs to be correct. The writing must be cohesive and logical.
Same will be done to detect plagiarism as in Assignment 1.
The assignment will be marked based on whether solutions are justified, correct and complete, concepts are clear, and presentation is easy to follow. Following is the mark distribution in marking.
Subject and justification 4
Star schema and dimension table justification 6
Warehouse table creation and sample data 8
SQL statements for loading and update 7
Penalty on Late Submission
For each day of lateness, a 25% of deduction is applied unless an extension is given.
If an extension is needed, you must apply for it reasonably before the due date unless the circumstances are exceptional. Note that we will not give extensions on the due date for reasons such as catching a flu/cold or having headache.