[meteor_slideshow slideshow=”arp1″]
Data Warehouse Concepts and Design
Assignment, Semester 1 2012
Dimensional Modelling Business Case
Objectives
• To create a Data Warehouse conceptual design using Star Schema Modelling
• To document all steps during the design process
This is an INDIVIDUAL assignment. You are not permitted to work in a group when writing this assignment.
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.
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.
No extensions will be given:
Penalties are applied to late assignments (5% of total assignment mark given is deducted per day, accepted up to 4 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.
Submission Guidelines:
• Hard-copy of the assignment is to be submitted to the relevant submission box
• Do not forget to attach a Statement of Authorship to your assignment, sign and date it.
• 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.
MovieRentalOZ Data Warehouse
MovieRentalOZ is a large chain of home entertainment rental over 100 stores distributed over all states in Australia. MovieRentalOZ lends entertainment products such as movies, TV shows and games on videotape & DVD. Customers can rent products online via the MovieRentalOZ website, as well as from retail stores. The stores also sell drinks, candies, posters, second-hand videos, second-hand DVDs, blank tapes, Prepay Cards etc. The revenue of the company is generated by rental fees, possible overdue fees (collected as part of returns), sales revenues, sales of gift cards, prepay cards that can be used to rent or purchase items. Rental price may vary between sessions, promotion periods or special events.
Each store has roughly 90,000 individual products on its shelves. The individual products are identified as follows:
• Universal Product Codes (UPCs) are assigned by the product suppliers to all of their products, whether they are catalogue items (movies) or sales items.
• In addition to that, the stores label the rental units with store keep unit (SKU). This code is scanned whenever rental unit is rented or sold as a second-hand item.
• Each PrePay card also has its own ID, referred to as a SKU.
Internally, the following terms are used to distinguish between the different types of products:
– Rental Item is the actual DVD/tape being rented out.
– Catalog Item refers to the product as it is purchased from the suppliers (for example, Avatar on DVD with Chinese subtitles as listed on Movie RentalOZ ‘s movie catalog).
– Sales Item refers to the secondary products sold in the stores, such as candies, blank tapes, and so on.
– PrePay Cards.
Every week, HQ sends a central product document with all available catalog items from the different suppliers, including price, availability information, the ratings and categories a movie. Each movie is specified for a main category which may contain additional categories. Examples are:
• Comedy (main), Characters, country, decade
• Drama (main), Romance, Historical/Biographical
• Action (main), Adventure, fiction, War, Martial arts
• The Shining–Thriller (main), Crime, Horror
• The Exorcist–Horror (main), Mystery, Religion, Drama
The PrePay Cards transactions are different than pay cash/credit transaction.
• Each time a movie is rented, a certain amount (5% less than the regular rental price) is deducted from the card balance.
• Customers can put more money on their cards with top-ups to increase the card amount by reloading.
HQ management also analyses the usage of the cards for all customer classifications by store.
They want the following information about each card (monthly figures):
• Starting balance
• Ending balance
• Average transaction amount
• Average top-up amount
• Number of top-ups during the month
• Number of rentals during the month
Each store has a local operational database to capture their day-to-day rental and sales and sends the following files to headquarter (HQ) at middle night of every day:
• A receipts file containing all the individual receipts with their line items.
Every day, the detail line items are incorporated into the summary rows for the current month
• A customer file containing
• A list of all customer data (address, marital status, and so on) 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 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 x last 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?
Management wishes to perform detailed analysis of their company’s performance and have decided to build a data warehouse to assist their business analysis and decision making.
Rental and Sales Analysis:
Requirements were gathered from all interested business departments as follows:
• Historical analysis of rentals and sales volumes
• Historical analysis of rentals and sales profit (revenue, costs)
• Optimize human resource utilization at the POS (Point Of Sale= Cashier’s desk)
Therefore, it is required to build a monthly/quarterly/yearly top 10 list of
• individual catalog items (Eg., Avatar DVD)
• Categories of movies (Eg., Comedy, Horror, Action)
• Per individual store and per region of stores (for example, Bundoora, Melbourne). Regions are sets of zip codes.
• Per class of customers (Regular, Gold, Silver, …) or across the entire customer base
The business questions are often asked as follows:
• What categories of movies are less popular?
• What movie categories most often rent to full capacity? What are the best ratios of the different movie categories for sustained profit maximisation?
• 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?
• What is the main location of those people (customers) renting online? What event types are they attending?
Revenue analysis:
In order to get a complete picture of the revenue this business is generating, the following revenue streams must be analyzed:
• Rentals
• Returns (overdue fees)
• Second-hand unit sales
• Sales Item sales.
The revenue streams will be performed using only the actual price of individual line items as indicated in the transaction records. That price indicates the price including any promotions.
The management compare the different revenue stream:
• How much do we make on overdue fees in last 5 years?
• How much do we make on Second-hand unit sales in the last financial year? How does this compare to the past 5 years?
• For which customers have we provided the most products? How much do we make a year out of our top 5 customers?
• Which categories of movies have made the highest profit?
Promotion Revenue analysis:
In order to compute the effectiveness of the promotion, several factors have to be considered:
• The total cost of the promotion
• The rental revenues generated for the targeted items (lift)
• The impact on revenues for other items within the same category during that same period (cannibalization and/or upsell)
The need to analyze rental/sales revenue that includes the promotions were applied for the movies that were rented. The Rental and Sales Line Item records contain information about which promotion affected the actual price of the rented or sold item.
The promotions can be decided centrally or locally, depending on the cases. Each promotion is specified with a promotion_id and the applied region. It is then the responsibility of the store manager to apply promotions during the sales/rental activities. Promotion files contain information about the different promotions.
– Subject (on which objects)
– Media campaign type (for example, TV Ad, Coupon, Local Newspaper Ad)
– Start and end dates (that is, for seasonal promotions)
– Overall cost Movie
Line items will refer to the applied promotions using the promotion_id.
The subject of the promotion describes its scope (which products are affected
• a list of specific movies (for example, all Kubrick movies),
• a specific catalog item (for example, Avatar Special Edition), a category of movies (all horror movies during Halloween)
• and which customers may benefit from it
• all customers or a specific class (Gold and Platinum)).
The management is looking for determining whether the promotion was effective:
• Whether the products under promotion experienced an increase in sales during the promotional period?
• Whether the products under promotion showed a drop in sales just prior to or after the promotion thereby cancelling any gain
• Whether the promotion was profitable
• Which stores have rented out more movies during the promotions? Does this vary across different months or event types?
• What products were on promotion but did not sell?
Your Task: (100 marks total)
Your task is to design a data warehouse for Movie RentalOZ using Star Schema/Snowflake 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. (10 marks)
(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. (40 marks)
(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.
(12 marks).
Fact table name Fact granularity 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. (12 marks).
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 (16 marks).
Design feature Brief description Brief justification
(vi) Identify which fields from your facts/dimensions are required to answer each of the business questions listed below. (10 marks)
• Which stores have rented out more movies during the promotions? Does this vary across different months or event types?
• What products were on promotion but did not sell?
• For which customers have we provided the most products? How much do we make a year out of our top 5 customers?
• Which categories of movies have made the highest profit?
• What is the main location of those people (customers) renting online? What event types are they attending?
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: MovieRentalOZ Operational Database Tables
PrePay Transactions:
Item Table:
Promotions:
Store Table
Rental Table
Place your order of custom research paper With us NOW. The assignment will be written from scratch by our qualified and experienced writers.
[meteor_slideshow slideshow=”arp2″]
A-Research-Paper.com is committed to deliver a custom paper/essay which is 100% original and deliver it within the deadline. Place your custom order with us and experience the different; You are guaranteed; value for your money and a premium paper which meets your expectations, 24/7 customer support and communication with your writer. Order Now
Use the order calculator below and get started! Contact our live support team for any assistance or inquiry.
[order_calculator]
