Project Overview: Creating a Dimension Model for the Sakila Database You will design a basic star dimension model of a portion of the Sakila database for later analytic analysis. I have reduced the original ERD of the database a bit, and you will find it on the last page. Your basic task is to: Design the dimension tables needed for an eventual data warehouse; Design the fact table needed for the data warehouse; Several items to remember and consider when doing your dimension design: Don't forget to include your surrogate (aka 'technical') keys; De-normalize tables where appropriate. You will have third normal form violations in your dimension tables; that is expected in dimension modeling. Converting Look-up Tables: A few heuristics about de-normalizing: o When a table has a 1:M relationship where a look-up table is the 'l' side, put the look-up value in the 'many side' table and get rid of the look-up table; o When a data table has a M:N relationship with a look-up table, and the look-ups have a finite number of possible values, consider putting a field in the data table for each possible look-up value, and make that field a boolean (i.e., Y/N) field; o When a data table has a M:N relationship with another data table, consider using a 'bridge' dimension (hint: you will need at a maximum only one of these in this model); Fact Table: You should have only ONE fact table for this business process. The challenge will be to decide what the relevant facts are versus what you can derive from the dimension tables. Temporal Dimensions: Don't forget to include date and time; Hint #1: Your final dimensional model should NOT have as many tables as the original ERD; Hint #2: The executives at Sakila probably do not care which copy of a film was rented. Project Overview: Creating a Dimension Model for the Sakila Database You will design a basic star dimension model of a portion of the Sakila database for later analytic analysis. I have reduced the original ERD of the database a bit, and you will find it on the last page. Your basic task is to: Design the dimension tables needed for an eventual data warehouse; Design the fact table needed for the data warehouse; Several items to remember and consider when doing your dimension design: Don't forget to include your surrogate (aka 'technical') keys; De-normalize tables where appropriate. You will have third normal form violations in your dimension tables; that is expected in dimension modeling. Converting Look-up Tables: A few heuristics about de-normalizing: o When a table has a 1:M relationship where a look-up table is the 'l' side, put the look-up value in the 'many side' table and get rid of the look-up table; o When a data table has a M:N relationship with a look-up table, and the look-ups have a finite number of possible values, consider putting a field in the data table for each possible look-up value, and make that field a boolean (i.e., Y/N) field; o When a data table has a M:N relationship with another data table, consider using a 'bridge' dimension (hint: you will need at a maximum only one of these in this model); Fact Table: You should have only ONE fact table for this business process. The challenge will be to decide what the relevant facts are versus what you can derive from the dimension tables. Temporal Dimensions: Don't forget to include date and time; Hint #1: Your final dimensional model should NOT have as many tables as the original ERD; Hint #2: The executives at Sakila probably do not care which copy of a film was rented
