Data Warehouse Concepts and Design
Dimensional Modelling Business Case :
Business description
Chemist Warehouse Group
Chemist Warehouse is Australia’s Pharmacy store. The Chemist Warehouse group employs over 10,000 staff members and is Australia’s largest pharmacy retailer. It has over 500 retail stores nationally and been growing at a rapid rate. The Chemist Warehouse business model is not only providing price competition and volume sales, but also maintaining the highest standard of professional service and health advice to consumers. Chemist Warehouse is owned and operated by Australian registered pharmacists and all products have been approved for sale in Australia by the relevant authorities.
Chemist Warehouse’s services include sales of healthy products in store and online; preparing medication prescribed for patients throughout Australia. Therefore, the business deals with two types of customers: the customers who purchase products in store or online, and another type is patient who needs prescribed medications either occasionally or regularly.
Each store is made up of two divisions as follows:
1. Serves patients by preparing medications; giving pharmacological information to multidisciplinary health care team; monitoring patient drug therapies. The service staff are professional pharmacists.
2. Retail sales on healthy related products coming from different suppliers. The products are managed by related departments such as Vitamins; Beauty; Fragrances, Baby Care; Medicines; Dental; Household; Hair Care; Protein etc.
Customers or patients can pay their purchases by cash, cheque, bank card, visa card and master cards etc in local stores The payment by American express card will be charged 2% extra. Online purchases must use credit cards.
Each store has got a local manager who maintains the local business processes. The store keeps the local inventory at a satisfactory level by checking the balance of products daily and observing the hot selling products. The inventory management will notify the central warehouse for their orders of products, receiving delivered products and updating the inventory database. The local management sends their weekly sales to the central management at headquarters (HQ).
Each store has a local operational database to capture their day-to-day business processes such as POS transactions and backend inventory. Each store records the following data in their database:
Areceipt file containing all the individual receipts for their sales of products.
Every day, the detail items are incorporated into the summary rows for the current month send to HQ.
Acustomer file containing a list of all customers captured by stores with their VIP buyer card information.
oCustomers are assigned to VIP classes (Platinum, Gold, Silver, Regular) based on the number of thresholds (LOW and HIGH values) for the following two attributes:
1. Frequency: During the last x months, how often did the customer visit us?
2. LTV (Life Time Value): What is our estimation of the potential business this customer could generate over the next x months?
Aproduct file containing
o Product’s Universal Product Code (UPC), the descriptions of the product, the functionality of the product, the total quantity, the unit price, the brand, the category, the department, the supplier, the name of package, the size of the package, the date of valid period and its manufacture date.
At end of the day, the inventory level need to checked, new order may be made by the management at local stores.
Promotion is one important activity to improve the business. Promotions include temporary price reductions, newspaper and other media ads, store displays and coupons. Heavy price reductions are the most effective way to create substantial increases in the number of products sold.
The promotions can be decided centrally or locally, depending on the cases. Each promotion is specified with a promotion_id and the description. It is then the responsibility of the store manager to apply promotions during the sales activities. Promotion files contain information about the different promotions.
the promotion describes its scope (which products are affected) such as:
a list of specific products and their brand
a specific category of products
the types of customers may benefit from it (eg., all customers or a specific class (Gold and Platinum)).
Start and end dates (that is, for seasonal promotions)
Discount level
The central management is looking for determining whether the promotion was effective:
The need to analyze sales revenue that includes the promotions were applied for the products on sales. The sale records contain information about which promotion affected the actual price of the product item.
In order to compute the effectiveness of the promotion, several factors have…
Assignment status: Already Solved By Our Experts
(USA, AUS, UK & CA PhD. Writers)
Source: Get Help
