CSE5DWD Data Warehouse Concepts and Design - Individual Assignment

Assignment: Dimensional Modelling Business Cases

Your task is to design a data warehouse for Heathcote Winery Group using multidimensional Modelling. Your design needs to encompass the following steps:

1. First construct a Data Warehouse Bus Matrix to identify the company's business processes and any likely Data Marts. (10 marks)
2. Identify the grain of each star/snowflake schema to design the dimensional model for business processes you have identified, ensuring your dimensions are conformed, primary and foreign keys are clearly labelled, and that your attributes are named using verbose textual descriptions. You also need to provide three rows of the fact table your designed to explain the meaning of the rows. (40 marks)
3. Create the following table with a row for each fact table in your design, indicating the granularity of each fact and a brief justification for choosing that granularity. (12 marks).
4. Create the following table with a row for each dimension table in your design, giving a brief justification for choosing that dimension, and indicating any attribute hierarchies that exist within the dimension. (12 marks).
5. Create the following table with a row for each design feature you have used, such as handling of possible null foreign keys, and the inclusion of any fact-less fact tables, degenerate dimensions, role playing dimensions, junk dimensions, outriggers, mini-dimensions, bridge dimens or any other design techniques discussed in the lectures. Provide a brief description of each design feature used (how and where it is used – not the theory behind the concept), any possible problems & solutions with a justification for their use (16 marks).
6. Identify which fields from your facts/dimensions are required to answer 7 business questions listed as the Bold words and marked with (*) above. (10 marks)