Overview of the Assignment:
In this exercise we will review data warehouse concepts, specifically modeling the dimensional schema. We first outline the directions, and then provide guidance on which you will base your solution.
Directions:
Create an initial design for a dimensional data mart for an online DVD rental business that is similar to a simplification of the business pioneered by NetFlix. Make sure to review the Online DVD Rental Business Overview and the examples of the data warehouse requirements described below.
1. Outline 5 to 7 key business questions your dimensional data mart will focus on.
2. Create an ERD of the data warehouse dimensional schema that you envision. Your design will need to include dimension and fact tables. You need to show constellation schema of at least two fact tables. Fact tables will need to include measures. For dimension tables, consider slowly changing dimensions.
3. Design three indexes and describe why you consider these. Part of your consideration should be based on the DW analysis defined in this assignment.
Your Deliverables will include:
1. 5 to 7 key business questions your dimensional data mart will focus on.
2. The ERD representing your dimensional schema design (constellation).
3. Briefly explain your design choice for any slowly changing dimensions. (one paragraph)
4. Briefly explain your design choice for the measures you have selected. (one paragraph. Look to link to your business questions)
5. Three indexes and brief explanation of your choices as to why you want to index that particular piece of the schema and what type of index you would use (one paragraph).
Additional:
The facts in this business are the main value-chain events customers updating their wish lists, and the enterprise receiving and shipping DVDs. Supporting events and their related facts involve updates to Member data, and plans, and financial transactions such as billing a customer. You need to decide whether to represent all of these in one fact table (a star or snowflake schema) or to design a constellation schema with multiple fact tables. Remember that joining multiple fact tables is not scalable, so your design should be able to support the business analysis needs using only one fact table per DSS query.
You can choose the time grain, based on what roles you want the warehouse to serve. Because workflow management is an increasing role of data warehouses you may consider an hourly time grain, or you can use the traditional one-day time grain.
The attributes of the time dimension should include the basic properties such as the day of the week, month, calendar year, and whether this is a holiday. Feel free to add attributes which you think would be useful in decision support queries.
