Data Warehouse Concepts and Design
Assignment
Dimensional Modelling Business Case
Objectives
• To create a Data Warehouse conceptual design using Star Schema Modelling
• To document all steps during the design process
1- 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.
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 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 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.
o 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 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 this vary 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:
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:
(i) First construct a Data Warehouse Bus Matrix to identify the company's business processes and any likely Data Marts.
(ii) 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.
(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.
Dimension table name Brief justification Attribute hierarchies
(v) 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.
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.
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 of toys
Attribute Name Attribute Description
ToyID (PK) This is the operational database's primary key for the toys table.
Toys-desc The name of the toys.
ToysCategoryID The Category ID 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) the primary key of the Toy-Category table.
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 of Toys 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.