Data Warehouse Concepts and Design (Dimensional Modelling Business Case)

Objectives

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

 

Weight of the assessment

• 30% of total assessment.

1          The Instructions

                     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. Only electronic copy of your assignment is to be submitted to CSE5DWD subject LMS web site.

 

• 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 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 https://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 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 sends 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 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/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 table name	Fact granularity	Fact 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 name	Brief justification	Attribute 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 used (how and where it is used – not the theory behind the concept) and a justification for its use (16 marks).

 

Design feature	Brief description	Brief 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;

Appendix A: Toys Hire & Children’s Party  Operational Database Tables

 

Table: Event

This table holds data that describes the entertainment event.

Attribute Name	Attribute Description
eventID (PK)	This is the operational database’s primary key for the Event table.
eventName	The name of the event.
eventDescription	A description of the event.
typeOf Event	The type of the event (birthday party, Comedy, school parties, private festival, etc)
EventBudget	The client’s budget
EventPeriod	Days for running the event

 

Table: Event-organizing

This table holds data relating to each event organization

Attribute Name	Attribute Description
EventoranizationNo (PK)	This is the transaction number for the sale.
eventID (FK)	Refer to the primary keyof the Event table.
BookDate	This is the date that the event is booked
EventDate	This is the date that the event will take place
FinshingDate	This is the date that the event to be finished
ClientID (FK)	This is the id of the client who wants the event. It is a foreign key linking to the Client table.
EmployeeID(FK)	Employee or consultant  of   Toy  Hire – for organizing the event
ConsultationCost	To be earned by Toy Hire
ToytCategory(FK)	Toys needed from the Toys Hire company
venueID (PK)	This is the key of the venue. Toys Hire will find this venue for client
paymentTypeID (FK)	This foreign key links to TransactionPaymentType.
Event cost-estimation

 

 

Table: Toys

This table holds names oftoys

Attribute Name	Attribute Description
ToyID (PK)	This is the operational database’s primary key for thetoys table.
Toys-desc	The name of the toys.
ToysCategoryID	The CategoryID of the Toys
Toy-ageGroup	The toys for suitable age of children
ToyMaterials	Materials (metal, plastic, paper, cloth, fluff , woody)
Manufacture	the company’s name -made
PriceHirePerday

 

Table: Toy-Category

This table holds data relating to toys type

Attribute Name	Attribute Description
ToysCategoryID (PK)	theprimary keyof the Toy-Categorytable.
Category-name	The Category of the Toys (construction, creative, Dolls, puzzle, games etc)
Category-description

 

 

Table: Client

This table holds data relating to clients, who need the event organisers.

Attribute Name	Attribute Description
clientID (PK)	This is the operational database’s primary key for the Client table.
clientCompanyName	The name of the client’s company.
streetAddress	The street number, street name, and street type (St, Rd, etc) of the company’s address.
suburb	The suburb component of the company’s address.
postcode	The postcode component of the company’s address.
clientContactName	The name of the client’s contact.
clientContactPhoneNo	The contact phone number of the client’s contact
clientContactFaxNo	The fax number of the client’s contact.
EmailAddress

 

Table: Venue

This table holds data relating to the venues where the entertainment events are held.

Attribute Name	Attribute Description
venueID (PK)	This is the operational database’s primary key for the Venue table.
venueName	The name of the venue.
streetAddress	The street number, street name, and street type (St, Rd, etc) of the venue’s address.
suburb	The suburb component of the venue’s address.
postcode	The postcode component of the venue’s address.
venueCapacity	The number of people the venue can hold.
venueManager	The name of the manager of the venue.
managerPhoneNo	The phone number of the venue manager.
VenuePrice	The renting price of venue per day

 

Table: Promotion

This table holds data relating to list of toys for promotion

Attribute Name	Attribute Description
promotion (PK)	Primary key
categoryName	The name of the category.
Promotion Begin Date	StartDate to the promotion
Promotion End Date	FinishDate to the promotion
Price Reduction Type	(Coupon code, 2-for-1 sale, 50% off, etc)
Promotion Media Type	Television,Radio,Print Publications,Internet,Direct Mail
Promotion Cost

 

Table: ToyHireTransaction

This table holds data relating to each transaction for toy hire

Attribute Name	Attribute Description
ToyHireTransactionNo (PK)	This is the transaction number for the sale.
TakeAwayDateTime	This is the date and time that the transaction took place
ToyID (FK)
customerID (FK)	This is the id of the customer who hire the toy. It is a foreign key linking to the Customer table.
VIP_ID	May be empty
DiscountPercentage	May be zero if the customer is not a VIP
paymentTypeID (FK)	This foreign key links to TransactionPaymentType.
ReturnDate	Planned return date
ToyQuantity
OnlinePayment	Yes/no
DelayedDays	>=0
DamagePayment	>=0
TotalPayment

 

Table: Customer

This table holds data relating to those people who rent toys online from Toys Hire& Children’s Party  .

Attribute Name	Attribute Description
customerID (PK)	This is the operational database’s primary key for the Customer table.
customerFirstName	The first name of the customer.
customerLast Name	The last name of the customer.
VIP_memberID
streetAddress	The street number, street name, and street type (St, Rd, etc) of the customer’s address.
suburb	The suburb component of the customer’s address.
postcode	The postcode component of the customer’s address.
state	The state component of the customer’s address.
country	The country component of the customer’s address.
emailAddress	The customer’s email address.
contactPhoneNo	The customer’s contact phone number.

 

Table: Employee

This table holds data relating to Employees ofToys Hire & Children’s Party  .

Attribute Name	Attribute Description
EmployeeID(PK)	This is the operational database’s primary key for the Customer table.
EmployeeID FirstName	The first name of the customer.
EmployeeID Last Name	The last name of the customer.
Position
Store-Name	.
Store-address	The street number, street name, and street type (St, Rd, etc) of the store’s address.
suburb	The suburb component of the address.
postcode	The postcode component of the address.
state	The state component of the address.
country	The country component of the address.
emailAddress	The email address of company
contactPhoneNo	The contact phone number of the employee.

 

 

Table: TransactionPaymentType

This table holds data relating to the transaction payment type.

Attribute Name	Attribute Description
paymentTypeID (PK)	This is the primary key of the TransactionPaymentType table
paymentCategory	‘credit’, ‘cash’
creditCardTypeID(FK)	This foreign key links to the CreditCardType table. It will be null if the payment was cash.

 

Table: CreditCardType

This table holds data relating to the type of credit card used in the transaction, if credit card used

Attribute Name	Attribute Description
creditCardTypeID (PK)	This is the primary key of the CreditCardType table
creditCardTypeName	‘Mastercard’, ‘Bankcard’, ‘VISA’, ‘American Express’ etc
creditCardFee	This is the fee charged to Prestige Events per credit card transaction for this type of card.

 

To get answer chat with online assignment adviser