Assignment 2: Data Warehouse
Due Date and time:
23:00pm, Sun, 03Jun2020
Submission: a word document containing all solutions.
- Preliminary information
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.
- Problem statement
Based on the database you designed in Assignment 1, design a warehouse star schema and its associated operations. More specifically you complete the following tasks.
- Copy the conceptual designfor the operational database you achieved in Assignment 1 and paste it in thisassignment.Correct errors and make necessary changes to the design in your assignment 1as necessary. 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 analyticsubjectfor a warehouse and justify why such a subject is interesting to the decision making of your application. The subjectwould be related to key performance indicators in the application.
Discuss how the warehouse is subject oriented, integrated, nonvolatile, and time variant.
Note that as you have a specific database and have identified a specific subject, your discussion must refer to the concepts/tables in the application and their relationships to the subject.
- Design a star schema for thewarehouseto support the analysis of the subject and the dimension tables of the schema. Draw the schema in a diagram. Your diagram maybe simple, but the concepts in the diagram must be correct.
Justify the reasons why the attributes in the dimensional tables and the fact table are necessary for analyzing the subject.
Show sample data for the tables in the schema. The amount of sample data should be enough to demonstrate some concepts and logics and to help your description.
The names of your warehouse tables should start with “wh_”.
List correct SQL statements that create your warehouse tables. These must NOT be screenshots.
- Design SQL statements (3.1) for loading data from your operational database (what you had designed in assignment 1) at the start of the warehouse and (3.2) for maintaining the warehouse data during the warehouse operation. For each statement, define when the statement shall be executed. The statements should also cover both the dimension tables and the fact table.
- Requirements
- There is no unique solution to this assignment. It is quite 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 mustbe cohesive and logical.
- Marking
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 5
Schema justification and data 10
SQL statements for loading and updating 10
- 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.
The post Assignment 2: Data Warehouse appeared first on My Assignment Online.
