Proiect 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: 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" Fact Table: You should have only ONE fact table for this business process. The challenge will be . Temporal Dimensions: Don't forget to include date and time; Hint #2: The executives at Sakila probably do not care which copy of a film was rented dimension (hint: you will need at a maximum only one of these in this model); to decide what the relevant facts are versus what you can derive from the dimension tables. Hint #1 : Your final dimensional model should NOT have as many tables as the original ERD; Proiect 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: 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" Fact Table: You should have only ONE fact table for this business process. The challenge will be . Temporal Dimensions: Don't forget to include date and time; Hint #2: The executives at Sakila probably do not care which copy of a film was rented dimension (hint: you will need at a maximum only one of these in this model); to decide what the relevant facts are versus what you can derive from the dimension tables. Hint #1 : Your final dimensional model should NOT have as many tables as the original ERD
