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

No1AssignmentHelp.Com is world 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 fee, rental fees, and possible overdue fees (collected as part of returns).  Rental price may vary between sessions, promotion periods or special events.  The company deals with two types of people: one is called Client who requires for event organization with toys & equipment’s hire, and another type is called Customer 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 toys 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 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 Company needs 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 renting are either using online website or seeing the staff at the local offices.  For children event/party planning, it may need venues for toys such as 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 pay membership fee $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 headquarter (HQ) at 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 demographics information about the neighbourhoods people live in. Classes are identified by a number of 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 centralised 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 organization and hire transactions. The managers need to analyse 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 customer for budgeting.  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 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 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. Which age 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/clients prefer 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 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 labelled, and that 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).
Fact tabl nameFact granularityFact table type Brief justification
  • 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 nameBrief justificationAttribute hierarchies
  • 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).
Design featureBrief descriptionBrief justification
  • 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 recognisable 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 semester assignment on data ware hours and modeling assessment. Contact to our team of expert who are available 24×7 online to assist college or university students.