MSc ANALYTICS
DATA ENGINEERING PLATFORMS (MSCA 31012)
ASSIGNMENT 3
Part A (team): Data modelling and design considerations ( final project )
1. Add slides to the Assignment 2 presentation which addresses the following.
• Design considerations ( include any data preparation steps / platform considerations ).
• Enhanced Entity Relationship (EER) model or Dimensional model for your project.
Submissions
• Single submission per team.
• Submissions should be made in the form of a powerpoint presentation in canvas Part B (Individual): Dimensional modeling( sakila ) , Insights
Source Data ( Sakila dataset )
➢ We will use the Sakila database schema which can be found at: http://dev.mysql.com/doc/index-other.html
➢ Full documentation: http://dev.mysql.com/doc/sakila/en/
Submissions
• Solution can be submitted via .sql file and a word document (for diagrams, comments)
• Tableau workbook should be submitted for Reporting & Dashboards.
Assignment 3
1. Download Sakila Snowflake dimensional data model (DDL) from canvas
a. Run the DDL and DML provided for the Sakila dimension model b. For the missing Fact table fact_rental create the DDL and the DML
scripts to pull data from the Sakila ER model. Modify/improve the fact table script if you need more metrics for the Tableau report below.
c. Generate the EER diagram for the final dimensional model.
2. Using Tableau, create 5 different reports that provides insights into the sakila dataset. You may use any of the below use cases:
d. Identify any scripts errors and areas of improvement in the data model.
a. Create a report that helps visualize list of movies titles grouped by categories
b. Create a report that provides the list of overdue rentals grouped by customers
c. Create a report that list the Labels and language for all DVDs
d. Create visualization with payments for each customer grouped by city
e. Create charts that gives insight into the number of customers, ranged by number of rentals made
f. Create charts that gives insight into the number of rentals for each store and name of store manager
g. Create table that lists which 3 actors’ films have the highest demand for rentals
h. Build charts that gives insight into the number of rentals per month for each store
3. Create a dashboard based on the report set that you have created (For example: Store level dashboard that shows inventory, number of customers per store, revenue, etc.)
Note: Make sure the context within the dashboard can be switched using filters.