Business of Toys Hire & Children's PartyCompany

Toys Hire & Children's Party is an innovative Event Management & Entertainment Agency that showcases world-class Toys and equipment to create exciting entertainment for Children events and major occasions.Toys Hire & Children's Party has 100 offices across Australia in different cities. It manages all types of entertainment events specialising in entertainment for major events, festivals, toys shows, private parties, new launch & more. Its services include professional consultation, full event planning, and children party and toys hires. It offers bespoke events throughout Australia.

Toys Hire & Children's Party keeps a list of toys and equipment that may be used/rented for entertainment events and private usage. Toys have been classified by children's age group such Infants, Toddlers, ‎Preschoolers,‎Middle Childhood, Young Teens and Teenagers. The toys have categories such as Creative toys, Dolls, Education toys, model building, games, puzzles etc. (Refer to http://en.wikipedia.org/wiki/List_of_toys).

To increase the popularity Toys Hire & Children's Party Companyneeds to do the advertising in different types of advertisements such as through distribution of flyers or posters and also through other media such as newspapers, internet, television or radio. One of the business questions that Toys Hire & Children's Party wants to address is if an advertisement type is more cost effective than another.

The event booking and toys rentingare either using online website or seeing the staff at the localoffices. For children event/party planning, it may need venues for toys such as playground, tent, activity gym,junior slide, and some bigequipment for various groups of children. Therefore, the company keeps a list of venues & their cost in the database that will be used for event organization.

Toy Hire provides the services to members and non-members. Members need pay membership fee $40 per year. Therefore, those members can have 10% discounts onall services provided by Toy Hire. Members will also receive regular mailings including Play Matters magazine, theGood Toy Guide, Help sheets and promotions, keeping members up to date.

The company has acentralised operational database to capture their day-to-day toys hire and party equipment sales.

Toys Hire & Children's Party wishes to perform detailed analysis of their company's performance and have decided that a simple reporting feature built on top of their operational database will not be adequate. Not only will it compromise the efficiency of their operational database, they also wish to incorporate their flat file data into their analysis.

The company wants to work out the revenue based on event and hire transactions. The managers need to analyse the income from different types of toys for purchase decisions. They also need dataabout the cost of toy replacement, the penalties of toy damages from customer forbudgeting. All contracts' bills may not reflect the true revenue since the return date may be earlier or late. Marketing managers need to analysis customer behaviours, etc. Therefore, the executive has decided to commission a data warehouse to assist them in addressing thefollowing business questions:

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?

11. 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?

Toys Hire & Children's Partyhas also decided they need the data warehouse to support data analysis by ad-hoc groupings.

Your Task:

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.

(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..

(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.

(vi) Identify which fields from your facts/dimensions are required to answer each of the business questions listed above.