What is estimation of the potential business

Assignment Help Database Management System
Reference no: EM131029326

Data Warehouse Concepts and Design

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 particular product 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.

Reference no: EM131029326

Questions Cloud

Consequences of disruptions : Risk management consists of a series of steps that should be followed to reduce the consequences of disruptions. Briefly discuss these steps.
What are consumers really buying : What are consumers really buying into when buying an 'Australian' brand? Or, do they really care about the Australian-ness of a brand? What do you think?
Creative and innovative management : Understand the importance of creative and innovative management in organizations. Be able to support creative and innovative management processes for an organisation
How and why is the market broken down into segments : How and why is the market broken down into segments?
What is estimation of the potential business : Which products and what brands are most frequently purchased by customers in recentmonth? How many vendorssupply these products?
What can be said about the attained significance level : The observations reported were the number of uncontaminated eggplant cuttings after the 4 weeks of storage. Relevant data is given in the following table. Are you willing to assume that the underlying population variances are equal?
What was the revenue 2 quarters after 1999 : In the following table R(t) is the quarterly revenue for an international tortilla producer t quarters after December 1999. Use the table to answer the following questions.
Differences between visual and verbal supporting materials : Give an overview of why supporting materials are important for students to use when turning in powerpoint assignments. Discuss the differences between visual and verbal supporting materials.
Explain the importance of brainstorming : Explain the importance of brainstorming. What are the three most important approaches to brainstorming? Explain a situation in which brainstorming could achieve innovative results

Reviews

Write a Review

 

Database Management System Questions & Answers

  Explain it project to coordinate and maintain records

The system should support notifications to management personnel whenever their direct reports have submitted documentation. The system should also notify employees if their deadline to complete professional-development requirements is approaching ..

  Define data collection takes a lot of time

One of the major topics deals with the data collection for requirements gathering for a new proposed system. Welcome to week 3. Five characteristics that will come in handy during the requirements determination stage are impertinence, impartialit..

  One of the needs that your client has is a databaseto

one of the needs that your client has is a database.to design a database a database planner should spend a considerable

  Could a relation be stored as a sequential file

Suppose a sequential file contains 50,000 records, and 5 milliseconds are required to interrogate an entry. How long should we expect to wait when retrieving a record from the middle of the file?

  Describe the process of normalization and why it is needed

Describe the process of normalization and why it is needed. Contrast and compare logical and physical database design. Critique SQL as a user-friendly query language.

  Create 3 rows of data for each table ensuring

Write SELECT statements for the following (include a screenshot of the SQL and its execution, including the resulting data).

  Create an entity-relationship diagram

Create an Entity-Relationship (E-R) Diagram relating the tables of your database schema through the use of graphical tools in Microsoft Visio or an open source alternative such as Dia

  The cio asks you to describe why you believe it is

you are the information security officer at a medium-sized company 1500 employees. the cio asks you to explain why you

  What is cold backup and hot backup in case of oracle

What is cold backup and hot backup (in case of Oracle)? What are Armstrong rules? How do we say that they are complete and/or sound?

  Order results by customers last name in ascending order

Only demonstrate those customers that have the word/partial word "mail" somewhere in the EmailAddress. Order the results by the customer's last name in ascending order.

  What physical and logical topologies are recommend

Draw a simple floor plan for New Cenrury Wellness group and include the placement of all network nodes including the placement of a server, and network equipment. How many ports will your switch need to accommodate?

  Database modeling and normalization

Database Modeling and Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd