Reference no: EM131034019
Objectives
- To create a Data Warehouse conceptual design using Star Schema Modelling
- To document allsteps during the design process
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 servicesinclude 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 storeis made up of two divisionsas 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 storesThe 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, receivingdelivered 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:
- A receiptfile 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.
- A customer file containinga list of all customers captured by stores with their VIP buyer card information.
o Customers 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?
- A product 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, thename 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 salesactivities. 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 centralmanagement is looking for determining whether the promotion was effective:
The need to analyze sales revenue that includes the promotions were applied for the productson sales. The sale records contain information about which promotion affected the actual price of the product item.
In order to compute the effectivenessof the promotion, several factors have to be considered:
- The total cost of the promotion
- The sale revenues generated for the targeted products (lift)
- The impact on revenues for other products within the same category during that same period (cannibalization and/or upsell)
- Whether the products under promotion experienced an increase during the promotional period?
- Whether the products under promotion showed a drop on sale just prior to or after the promotion thereby cancelling any gain
- Whether the promotion was profitable?
- Which stores have more sales during the promotions? Does this vary across different months or event types?
- What products were on promotion but did not sell?
The central management at HQ maintainsthe supply chains running smoothly and efficiently.The procurementmanagement seeks the significantcost savings opportunities,and negotiates agreements with preferred suppliers. Procurement's goal is to source the appropriate products inthe most economical manner. Procurement involves a wide range of activities fromnegotiating contracts to issuing purchase requisitions and purchase orders (POs)to tracking receipts and authorizing payments. Some of common analytic requirements are listed as follows:
- Which products and what brands are most frequently purchased by customers in recentmonth? How many vendorssupply these products? At what prices?
- Looking at demand across all stores, are there opportunitiesto negotiate favourable pricing by consolidating supplier?
- Are you receiving the negotiated pricing from your supplier or is there supplier contract purchase price variance?
- How are your vendors performing? What is the supplier's fill rate? On-timedelivery performance? Late deliveries outstanding? Percent back ordered?
The order management is composed of a series of business activities. Each order needs is associated with anorder date, requested ship date, product, sales rep, and deal. A deal is an agreement between Chemist Warehouse group and supplier for their goods related discount during a fixed period.Chemist Warehouse objective is to enhance the customers healthcare outcome via the most effective and efficient means, resulting in great savings to the end user.
Every week, HQ distributes a central list to all stores with available products from the different suppliers, including price, availability information, the ratings and categories ofproducts for despatching.
The revenue of the business is generated by POS including preparing medications. Retail price may vary between sessions, promotion periods or special events. The central management collects all local store's weekly sales and product inventory information fordata analysis, demand forecasting, and decision making. The management needs to analyse the income from different types of product for purchase decisions. They also need data for their expansion plan. Marketing managers need to analysis customer behaviours and location performance for better planning and promotion decisions.
The central management wishes to perform detailed analysis of their business performance efficiently and effectively and has 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.
Therefore, a centralized data warehouseis required to assist mangers in addressing the aforementioned queries and thefollowing business questions:
1. What category and brand of products are the most popular in this year? 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 products are sold? Whichday of the week more productssold?
3. What products are short of supply in this month? Has this always been the case in whole year?
4. What areas of customers are frequently buying atour stores in last 12 months?
5. What are top 3 stores have the highest sales across the country?
6. How many patients are regular using our medications? Which medication is the most sell product?
7. Whichage group of customers ismost likely use our Vitamins across nation? Does thisvary across different location or times of the year?
8. What particularproduct is most popular across nation, what is the total sales in past 3 years?
9. Does the promotion activity provide the benefits to the business?
10. The promotion period in Christmas month has increased the sales comparing with the same period in last year?
11. Any products have not beensold out during the promotion period?
12. Who are the most loyal customers (top 5) to our business? What is the main location of those people purchasing the most expensive cosmetics products?
13. Do customers prefer to go online purchases or buy product physically at local store?
14. Do VIP members bring more business to our company?
Your Task:
Your task is to design a data warehouse for Chemist Warehouse 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) Designthe star schema for any Data Marts you have identified, ensuring your Dimensions are conformed, primary and foreign keys are clearly labelled, and that yourattributes 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 featureyou 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.
It is related to data warehouse and design.