XYZ is a large chain of home entertainment rental over 100 stores distributed over all states in Australia. XYZ lends entertainment products such as movies, TV shows and games on videotape & DVD. Customers can rent products online via the XYZ 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.

oCustomers 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 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)

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:

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

Your task is to design a data warehouse for Movie RentalOZusing 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.

(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.