CSE5DWD Data Warehouse Concepts and Design-Dimensional Modelling Business Case

Objectives

• To create a Data Warehouse conceptual design using Star Schema Modelling
• To document all steps during the design process
  

Weight of the assessment

• 30% of total assessment.

1 The Instructions
1.1 Due Date:
1.2 This is an INDIVIDUAL assignment. You ARE NOT permitted to work in a group when writing this assignment.
1.3 Submission Guidelines:

• No hard copy of the assignment is required.
  

• Please do not forget to attach a Statement of Authorship to your assignment, sign and date it.

• You can zip all files in one for the submission since the system does not accept multiple files.
• Assignments should be typed, not written/drawn by hand. Use the Word Drawing toolbar (or another tool of your choice) to draw figures in your assignment.
  

1.4 Copying, Plagiarism:

Plagiarism is the submission of somebody else’s work in a manner that gives the impression that the work is your own. The Department of Computer Science and Computer Engineering treats plagiarism very seriously. When it is detected, penalties are strictly imposed. Penalties are normally a mark of ZERO for the assignment.

Students are referred to the Department of Computer Science and Computer Engineering's Handbook and policy documents with regard to plagiarism and assignment return, and also to the section on ‘Academic Misconduct’ in the unit outline distributed in the first lecture and available on LMS/WebCT.

1.5 No extensions will be given:

Penalties are applied to late assignments (5% of total assignment mark given is deducted per day, accepted up to 5 days after the due date only). If there are circumstances that prevent the assignment from being submitted on time, an application for special consideration may be made. See the departmental Student Handbook for details. Note that delays caused by computer downtime cannot be accepted as a valid reason for a late submission without penalty. Students must plan their work to allow for both scheduled and unscheduled downtime.

1.6 Marking Scheme The marking scheme is described in section 3 Your Task.
1.7 Queries Regarding Assignment

Any queries can be addressed to the Lecturer during the normal consultation time. You will have consultation helping time in the week before this assignment due date: 8 consultation hours in the week. The specific time will be announced on LMS.

2 Business description

Business of Toys Hire & Children's Party Company

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's events and major occasions. Toys Hire & Children’s Party has 100 offices across Australia in different cities. It manages all types of entertainment events specializing in entertainment for major events, festivals, toy shows, private parties, new launches & more. Its services include professional consultation, full event planning, and children's party and toy hires. It offers bespoke events throughout Australia.

Assignment Help is the world's No.1 Trusted online assignment helper for college students in Engineering, MBA/Management, and Medical. Avail our experienced expert for your semester paper such as "Data Warehouse Concepts and Design Assessment help".

The revenue of the company is generated by event organization fees, rental fees, and possible overdue fees (collected as part of returns). Rental prices may vary between sessions, promotion periods, or special events. The company deals with two types of people: one is called Client who requires event organization with toys & equipment hire, and another type is called Customers for hiring toys only. Each event organization may need consultation. The Clients will pay the consultation fee for their event planning and organization on top of toy hire.

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 as 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 its popularity Toys Hire & Children’s Party Company needs to do the advertising in different types of advertisements such as through the distribution of flyers or posters and also through other media such as newspapers, the 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 renting are either using the online website or seeing the staff at the local offices. For children's event/party planning, it may need venues for toys such as a playground, tent, activity gym, junior slide, and some big equipment 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 to pay a membership fee of $40 per year. Therefore, those members can have 10% discounts on all services provided by Toy Hire. Members will also receive regular mailings including Play Matters magazine, the Good Toy Guide, Help sheets and promotions, keeping members up to date.

Each store has a local operational database to capture their day-to-day rental and send the following files to headquarters (HQ) in the middle night of every day:

• A receipt file containing all the individual receipts with their hired toys.

Every day, the detail rental items are incorporated into the summary rows for the current month

• A customer file containing
• A list of all customer data captured by stores when new customers apply for membership as well as changes that existing customers communicate (such as change of address or changes in family composition).
• The identification of the class they are currently assigned to.
  • Customers are assigned to VIP classes (Platinum, Gold, Silver, Regular) based on all sorts of personal (age, occupation, and so on) and demographic information about the neighborhoods people live in. Classes are identified by several thresholds (LOW and HIGH values) for the following three attributes:

1. Recency: How long ago did the customer visit one of our stores?
2. Frequency: During the last x months, how often did the customer visit us?
3. LTV (Life Time Value): What is our estimation of the potential business this customer could generate over the next x months?

The company has a centralized operational database to capture its day-to-day toy hire and party equipment sales.

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

The company wants to work out the revenue based on event organization and hire transactions. The managers need to analyze the income from different types of toys for purchase decisions. They also need data about the cost of toy replacement, the penalties of toy damages from customers for budgeting. All contracts’ bills may not reflect the true revenue since the return date may be earlier or late. Marketing managers need to analyze customer behaviors, etc. Therefore, the executive has decided to commission a data warehouse to assist them in addressing the following 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 compared with normal school days?
3. How much consultation income has been received for organizing the event in the past 5 years? What is the average time spent for consultations on each event?
4. Which area of venues used for organizing events has been most popular in past 10 years?
5. Which age group of children is most likely to hire toys across the nation? Does this vary across different locations or times of the year?
6. Which category of toys is most popular across the nation, what are the total sales in the past 5 years?
7. Does the promotion activity provide benefits to the company? The promotion period in Christmas month has increased sales compared 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/clients prefer to go online to rent or see local officers?
10. Do VIP members bring more business to our company?
11. How much penalty income in the 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 Party has also decided they need the data warehouse to support data analysis by ad-hoc groupings.

3 Your Task: (100 marks total)

Your task is to design a data warehouse for Toys Hire & Children’s Party using multidimensional Modelling. Your design needs to encompass the following steps:

• First, construct a Data Warehouse Bus Matrix to identify the company's business processes and any likely Data Marts. (10 marks).
• Design the star schema for any Data Marts you have identified, ensuring your Dimensions are conformed, primary and foreign keys are clearly labeled, and your attributes are named using verbose textual descriptions. (40 marks)
• 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).

• 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).

• 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, or any other design techniques discussed in the lectures. Provide a brief description of each design feature ued (how and where it is used not the theory behind the concept) and a justification for its use (16 marks).

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

Lecturer’s Notes:

• It is not enough to just draw the shapes of the facts and dimensions in the data warehouse/ data marts. Each fact/dimension should be completely defined, with keys and facts/attributes clearly described;
• All information from the given business problem should be recognizable in your final data warehouse;
• Your final data warehouse should be able to answer all business questions listed. If one question cannot be answered using your proposed solution, it means the solution is not completely correct and you have to rethink it;

Now do not worry about the semester assignment on data ware hours and modeling assessment. Contact our team of expert Assignment writers who are available 24x7 online to assist college or university students.