- To create a Data Warehouse conceptual design using Star Schema Modelling
- To document allsteps during the design process
1. What type of entertainment events are the most popular? Has this always been the case or has there been a shift in recent years?
2. Are there certain times of the year when more events are held? Are certain types of events more common at particular times of the year? Do we have more event organizations during school holidays comparing with normal school days?
3. How much the consultation income has been received for organizing the event in past 5 years? What is the average time spent for consultations on each event?
4. Which area of venues used for organizing events is most popular in past 10 years?
5. Whichage group of children is most likely hire toys across nation? Does thisvary across different location or times of the year?
6. Which category of toys is most popular across nation, what is the total sales in past 5 years?
7. Does the promotion activity provide the benefits to the company? The promotion period in Christmas month has increased the sales comparing with the same period in last year? Any toys have not been rented out during the promotion period?
8. Who are the most loyal clients (top 5) to our business? What is the main location of those people (clients) purchasing event/party organization?
9. Do customers/clientsprefer to go online rent or see local officers?
10. Do VIP members bring more business to our company?
How much penalty income in past 5 years for late return of the toys? What percentage of toys is damaged during the hires in past 5 years?
Your task is to design a data warehouse for Toys Hire & Children's Partyusing multidimensional Modelling. Your design needs to encompass the following steps:
(i) First construct a Data Warehouse Bus Matrix to identify the company's business processes and any likely Data Marts.
(ii) Designthe star schema for any Data Marts you have identified, ensuring your Dimensions are conformed, primary and foreign keys are clearly labelled, and that yourattributes are named using verbose textual descriptions.
(iii) 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.
|
Fact table name
|
Fact granularity
|
Fact table type
|
Brief justification
|
|
|
|
|
|
(iv) 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).
|
Dimension table name
|
Brief justification
|
Attribute hierarchies
|
|
|
|
|
(v) Create the following table with a row for each design featureyou 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, 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) and a justification for its use.
|
Design feature
|
Brief description
|
Brief justification
|
|
|
|
|
(vi) Identify which fields from your facts/dimensions are required to answer each of the business questions listed above.