Learning outcomes and skills tested
M1 An in-depth knowledge of data warehousing concepts and techniques
M2 A comprehensive and critical understanding of the process of creating effective data warehousing solutions
M4 Hands-on experience of building a data visualization dashboard using a state of the art visualization system and how such visualizations may be used to support decision making
S1 Communication skills in electronic as well as written form
S2 Self-direction and originality in tackling and solving problems
S3 An ability to act autonomously and professionally when planning and implementing solutions to computer science problems
Background
For this assignment you are to take on the role of a specialist tasked with setting up a data warehouse for the Gravity bookstore marketing department who would like to look into how a recent marketing campaign has affected international book sales and particularly the sales of books in languages other than English.
The structure of the operational database used by the bookstore is shown below.
They also recently conducted a survey of their customers to find out how they first heard about the bookstore, how they prefer to buy books, and why they prefer to use the Gravity bookstore rather than any other supplier. The results of the survey are held in a CSV text file.
The business questions presented in your initial meeting with the marketing department representatives are as follows.
Where are the customers who buy are books and how has the location of customers changed over time?
Where do our customers prefer to buy non-English language books and how has the demand for non-English language books changed over time?
Tasks
In order to meet the requirements of this assignment you will need to complete the following tasks.
Use the sql files to create the Gravity bookstore operational database.
Design a data warehouse schema based on the Gravity bookstore operational database that can be used to answer the marketing department business questions. For simplicity, it is recommended that this is a star schema.
Create staging tables for the transfer of the data from the operational database to the data warehouse.
Develop control-flow and data-flow procedures to extract the data from your operational database to your staging tables.
Extract the data to your staging tables.
Create the schema for your data warehouse.
Develop control-flow and data-flow procedures to transform and load the data to move it from your staging tables to your data warehouse.
Perform data cleansing to check for any issues with the data. Due to the limited time-scale of the project, more advanced checking involving automated scripts etc. is likely not feasible. However, you should at least consider which checks should be prioritized and how these could be implemented.
Develop a visualization interface that uses the target tables of your data warehouse to answer the marketing department’s business questions. This can combine more than one visual representation of the data.
Write a report of 1000-1500 words to describe the above process of data warehousing and data visualization. The content of the report is described below.
Report
The report to be submitted for this assignment should contain the following.
ER diagrams showing the schema for your staging tables and the target tables of your data warehouse.
Diagrams describing how the data is extracted from the operational database to the staging tables.
Diagrams describing how the data is transformed and loaded into the target tables of your data warehouse.
Screenshots of your visualization demonstrating how they can be used to answer the business questions from the end users.
A description of the design process for your data warehouse schema and ETL process including an explanation of key design decisions such as how you interpret the user requirements and relate these to the data. You should include any assumptions made for the design and discuss the alternative design options considered.
A description of the development process including any steps made to debug your design and any changes to the design made during the development process.
A description of the data cleansing process including the checks made, issues found, and how the process could be improved with automated checking etc.
A summary of the lessons learned during the process with ideas on how the it could be improved in future.
To complete this assignment
Write a report – 1500 words – in MS-Word or PDF format. You are encouraged to use this template.
Submit your report, by clicking on the submission button below.
All references and in-text citations in the report should follow the Harvard style of referencing.
Please, rename the file to submit as Yourname_MMA.docx/pdf
The maximum assessment length will relate to the final report of 1500 words. As per the assessment length policy, penalties will be applied to assessments which exceed this length.
