Welcome to the Most Reliable Essay writing Service Online

Data warehouse and design

by | May 22

Dimensional modeling for Chemist warehouse business caseData warehouse and designsubmitted by: Sana Naz Ashraf 19568938Contents

Case Study: 2

Defining Business Process: 3

Task 1: 4

Data Warehouse Bus Matrix: 4

Task 2: 5

Designing Star Schemas 5

Retail Sale Business Process 5

Prescribed Medicine Sales Business Process 6

Order Management Business Process 7

Inventory Management Business Process 8

Marketing Analysis Business Process 9

Task 3: 10

Fact table features and techniques 10

Task 4: 12

Task 5 15

Task 6 17

Business Questions 17

Question 1: 17

Question 2: 17

Question 3: 18

Question 4: 18

Question 5: 19

Question 6: 19

Question 7: 20

Question 8: 20

Question 9: 21

Question 10: 21

Question 11: 22

Question 12: 22

Question 13: 23

Case Study:

Chemist warehouse is an Australian company known for pharmaceutical goods for customer in store and online. This retail store also focuses on patients who require regular prescribed medication. As Chemist Warehouse is divided in to two divisions, the first one focuses on monitoring patient’s requirements and giving pharmacological information. This part of the retail is controlled by employees who act as professionals in their fields. The second division emphases on healthy related products such as baby care, hair products, dental.

Defining Business Process:

Retail SalesPrescribed Medicine SalesOrder ManagementInventory ManagementMarketing AnalysisTask 1:

Data Warehouse Bus Matrix:

Key business processes of chemist warehouse that have significance to run the chemist warehouse store. Here is the Bus matrix with most significant business processes and conformed dimension:

Task 2:

Designing Star Schemas

Retail Sale Business Process

Fact table row:

Date key (FK)Customer key (FK)Customer demo key (FK)Health related product (FK)Sales Rep (FK)Promotion (FK)Store (FK)Payment method (JD)Sales transaction ID (DD)Receipt number (FK)Quantity soldPrice $Sales $Net profit ($)Prescribed Medicine Sales Business Process

Fact table row:

Date key (FK)Customer key (FK)Customer demo key (FK)Prescribed med (FK)Pharmacist (FK)Promotion (FK)Store (FK)Payment method (JD)Sales transaction ID (DD)Receipt number (FK)Quantity soldPrice $Sales $Net profit ($)Order Management Business Process

Fact table row:

Date key (FK)Order date key (FK)Requested ship date key (FK)Order received date (FK)Pharmacist (FK)Sales rep (FK)Product key (FK)Deal key (JFK)Supplier key (FK)Warehouse (FK)Order no (DD)Order quantityOrder Discount $Net order ($)Net profit ($)Inventory Management Business Process

Fact table row:

Date key (FK)Store key (FK)Health related product key (FK)Prescribed medicine key (FK)Total capacityQty soldQuantity in handQty requiredMarketing Analysis Business Process

Fact table row:

Date key (FK)Customer key (FK)Customer demo key (FK)Prescribed med (FK)Retail product (FK)Promotion (FK)Store (FK)Media group key (FK)Qty soldPrice ($)DiscountTotal amount ($)Cost of media used ($)Net profit ($)Task 3:

Fact table features and techniques

Fact table nameFact granularityFact table typeBrief justificationRetail SalesThe grain is the retail sale transaction (one row per retail product sale transaction) and a fact row relates to a SINGLE measurement at point in one time which is indicating Retail products Sold. The granularity of the retail sales fact table provides detail of a product sale in a store to a customer by sales rep on daily basis to see the most popular product by highest profit.Transaction fact tableIt can help Manager to know the most popular brand and category of product by profit, this can be done by Retail sale Transaction fact table which has health related product dimension and fact named as profit and quantity sold.Prescribed Medicine SalesThe grain is the prescribed medicine sale transaction (one row per prescribed medicine transaction line) and a fact row relates to a SINGLE measurement at point in one time which is indicating prescribed medicine Sold. The granularity of the Prescribed Medication sales fact table provides detail of medication sale in a store to a customer by pharmacist on daily basis to see the most popular product by highest profit.Transaction fact tableIt can help Manager to know the most popular brand and category of product by profit, this can be done by Prescribed Medicine sale Transaction fact table which has prescribed medicine dimension and fact named as profit and quantity sold.Order ManagementThis accumulated snapshot fact table provides detail of order shipment from supplier to warehouse on negotiating favorable price.Accumulated snapshot fact tableThis order accumulated snapshot fact table can be helpful to see the performance of supplier and products supplied either on negotiable favorable price or contract purchase variance price.Inventory ManagementThe granularity of the inventory management fact table is to track daily inventory available of health related and medication products by store.Periodic snapshot fact tableA periodic inventory snapshot can be helpful to see the quantity of products available or shortage in store.Marketing AnalysisGrain is the single promotion record (one row per promotion record). This fact table provides all detail related to the sale of products/medicine by which promotion group.Transaction fact tableBy using Marketing Analysis Transaction fact table, a manager can be solved queries related to effective media type used for boosting the sales in a Chemist Warehouse store.Task 4:

Dimension table nameBrief justificationAttribute hierarchiesDateIn our business case, users of the data want to see date in daily, monthly and yearly format to make important decisions. Date dimension is very important aspect for every dimensional model because it provides user an efficient way to analyses data across various aspect of time and space. Date is always the key and primary dimension of every business case.Date, Name of Day, Week, Month, YearStoreChemist warehouse has over 500 retail stores, to check store performance, inventory level to run and fulfil stock requirement per store.Store ID, Store Manager Name, Location, Address, Contact number, emailCustomerCustomer is a crucial entity in our business case and the chemist warehouse management want to see the data about its customers.Customer ID, Customer Name, Member class, Customer address, City, State, Country, phone number. emailSales RepresentativeSales rep is an entity who is selling general and healthy products to customers or patient.Sales Rep ID, Name, Contact Number, Email PharmacistPharmacist Dimension is playing an important role in our business case as they are the qualified professionals to prepare medicines.Pharmacist ID, Pharmacist Name, Contact Number, EmailWarehouseIn chemist warehouse business case, warehouse is storing product prior to its distribution to stores.Warehouse IDWarehouse Manager Name, Location, Address, Contact number, emailGeneral/Health related ProductsA primary entity in this business case which contains information about General/Health related Products available in store for sale.Retail Product Name, Description, Brand, CategoryPackage name, Package Size. Unit price, Date of valid period, Manufacturing datePrescribed medicineA primary entity in this business case which contains information about prescribed medicines available in store for sale.Medicine ID, Medicine Description, Package name, Package Size, Date of valid period, Manufacturing dateDealAn entity which exhibits an agreement between Chemist Warehouse Group and supplier for discount during a fixed period.Deal ID, Deal Description, Deal terms description, Deal terms type description, Special Incentive description, Special Incentive type descriptionSupplierSupplier dimension is an important entity who supplied product to warehouse to maintain adequate level of stock for stores.Supplier Name, license number, City, State, Country, Contact Number, emailPromotionStores of Chemist warehouse offer discounts to create substantial increase in the number of products sold. Promotion dimension helps in answering promotion related queries.Promotion ID, Promotion Name, Promotion type, Promotion Cost, Begin Date, End DateTask 5

Design featureBrief descriptionBrief justificationPayment Method – Junk DimensionApplied to: Payment method used for health related and prescribed medicine online or in storeIt is used to better understand the data model and reduce the number of dimensions in fact table.Customer Demographics – Mini DimensionAttributes such as age, income level are extensively used in customer dimension so that such attributes can form as MINI DIMENSION as Customer Demographics.Applied to: Customer DimensionIt can be helpful to separate frequently changing attributes and frequently analyzed attributes by unique primary keys to reduce the number of combinations of frequently changing attributes. Both primary keys which includes base dimension key and mini dimension key stored in associated fact table.VIP Member class Customer _ surrogate keyApplied to: An attribute in Customer Dimension – VIP Member classTo handle modifications in specific attributes in dimension table surrogate keys are required.Role playing Dimension _ Date DimensionApplied to: Date DimensionA single date can be used/referred to order date, requested ship date and order received date to avoid multiple time same dimensions in a fact table. But each date has its own foreign key in fact table for separate view of the dimension.Multivalued DimensionApplied to: Media type group DimensionVarious media types used for promotion of products are unknown, to avoid multiple dimensions of media type we created multivalued dimension. Bridge table is linked with media type group and media dimension to know the number of media type used for promotion of product, moreover, used to exhibit many to many relationships.Retail Sales fact less tableApplied to: Retail Sales transaction fact table Grain: one row per retail product sale transaction at one timeA fact less table is required to know the items available in store but did not sell? Which has same foreign keys without facts or measures.Products on Promotion fact less tableApplied to: Marketing analysis transaction fact table Grain: one row per product on promotion sale transaction at one timeIt will help manager to know brand, category and quantity of products that were on discount / promotion but did not sell. For this, a fact less table is required without measures.Degenerate DimensionsOrder Number (DD) applied to Order management accumulated snapshot fact table Point of Sales Transaction Number (DD) applied to Retail Sales and prescribed medicine transaction fact table Receipt number (DD) applied to Retail Sales and prescribed medicine transaction fact tableThis is a dimension key without any dimension table in star schemas.Null Foreign keysApplied to: Retail Sales transaction fact table Marketing analysis transaction fact tableNot all products available in store come under promotion, so a row can be added in promotion dimension as “no promotion” which has its own unique key to avoid the misunderstanding why the Promotion key (FK) is null.Task 6

Business Questions

Question 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? What product is most popular across nation, what is the total sales in past 3 years?

SelectRetail Product Brand, Retail Product category, Prescribed Medicine Brand, Prescribed Medicine Category, Total Sales Dollar amount ($), Qty Sold, YearFromBusiness Process: Retail sales, Prescribed medicine sales Fact table: Retail sales transaction fact table, Prescribed medicine sales transaction fact table Dimensions: Health related product, Date, Prescribed medicine product Required Measure: Total Sales Dollar amount ($), Qty SoldNote: First calculate the total sales of all the individual product brand and category of this year, then rank all products in ascending order to find out the most popular product of this year. Is that product always at first rank? Compare the total sales of first rank product of this year with the total sales of first ranked product of previous 3 years.Question 2:

Are there certain times of the year when more products are sold? Which day of the week more products sold?

SelectName of days, WeekFromBusiness Process: Retail sales, Prescribed medicine sales Fact table: Retail sales transaction fact table, Prescribed medicine sales transaction fact table Dimensions: Date Required Measure: Qty Sold of all products per dayNote: Select the quantity Sold of all products per day in a week and then compare the quantity sold of all products with weekdays and weekendsQuestion 3:

What products are short of supply in this month? Has this always been the case in whole year?

SelectQuantity in hand, Quantity required, MonthFromBusiness Process: Inventory Management Fact table: Inventory Management periodic snapshot Dimensions: Date, Health related product, Prescribed medicine Required Measure: Quantity in hand, Quantity requiredNote: The sum of quantity in hand is less than the expected quantity required of products in a month in store gives the short of supply Local store Management keep track of all the products available in store and notify warehouse about the short of supply of any product if required.Question 4:

What areas of customers are frequently buyers at our stores in last 12 months?

Selectcustomer/patient, number of visits, monthFromBusiness Process: Retail sales, Prescribed medicine sales Fact table: Retail sales transaction fact table, Prescribed medicine sales transaction fact table Dimensions: Customer VIP member class Mini Dimensions: Customer demographics Required Measure: Frequency (no. of visit)Note: Select customer ID and number of visits of a customer to get the frequency of a customer in last 12 months.Question 5:

What are top 3 stores have the highest sales across the country?

SelectStore ID, Sales ($), Net profitFromBusiness Process: Retail sales, Prescribed medicine sales Fact table: Retail sales transaction fact table, Prescribed medicine sales transaction fact table Dimensions: Store Required Measure: Net profit, Sales ($)Note: Order by: Net profit in highest to lowest order (descending order)Question 6:

How many patients are regular using our medications? Which medication is the most selling product?

SelectCustomer/patient ID, Prescribed medicine ID, Prescribed medicine Prescribed medicine brand, prescribed medicine category, quantity soldFromBusiness Process: Prescribed medicine sales Fact table: Prescribed medicine sales transaction fact table Dimensions: Prescribed medicine, Customer Required Measure: quantity soldNote:Question 7:

Which age group of customers is most likely using our Vitamins across nation? Does this vary across different location or times of the year?

SelectAge, health related product – Vitamins, dateFromBusiness Process: Retail sales Fact table: Retail sales transaction fact table Dimensions: Customer demographics (age), Date, Health related ProductGroup by:Age band to seeQuestion 8:

Does the promotion activity provide the benefits to the business?

Step1: benefit from the total number of products on promotion

SelectQuantity sold, Promotion key, health related product, prescribed medicine, (sales less promotion cost) = net profitFromBusiness Process: Retail sales, Prescribed medicine Fact table: Retail sales transaction fact table, Prescribed medicine sales transaction fact table Dimensions: Promotion, health related product, prescribed medicine Where: Promotion key ≠ “xxxx” (considers all products which are under promotion)Step2: benefit from the total number of products without promotion

SelectQuantity sold, Promotion key, health related product, prescribed medicine, (sales less promotion cost) = net profitFromBusiness Process: Retail sales, Prescribed medicine Fact table: Retail sales transaction fact table, Prescribed medicine sales transaction fact table Dimensions: Promotion, health related product, prescribed medicine Where: Promotion key = “xxxx” (considers all products which are not under promotion)Note:Compare the two sets to see whether the promotion activity benefits to the business or not.Question 9:

The promotion period in Christmas month has increased the sales comparing with the same period in last year?

SelectQuantity sold, Promotion key, Promotion IDFromBusiness Process: Retail sales, Prescribed medicine Fact table: Retail sales transaction fact table, Prescribed medicine sales transaction fact table Dimensions: Promotion, DateWhere:Promotion period: Christmas month Promotion key Note: Compare this Christmas month sales with the last year Christmas month salesQuestion 10:

Any products have not been sold out during the promotion period?

SelectDate, Promotion, Health related product, Prescribed medicineFromBusiness Process: Retail sales, Prescribed medicine Fact table: Retail sales transaction fact table, Prescribed medicine sales transaction fact table Dimensions: Promotion, Date, Health related product, Prescribed medicineThis query can be answered by factless table of Health-related product, Prescribed medicine.Question 11:

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?

SelectCustomer ID, Retail product ID,FromBusiness Process: Retail sales Fact table: Retail sales transaction fact table Dimensions: customer, Health related product Measure: Net profitNote: Order by: Net profit in highest to lowest order to get the top 5 customersQuestion 12:

Do customers prefer to go online purchases or buy product physically at local store?

SelectCustomer, payment type (online/offline)FromBusiness Process: Retail sales, Prescribed medicine Fact table: Retail sales transaction fact table, Prescribed medicine sales transaction fact table Dimensions: Customer Junk Dimension: Payment methodPayment type = Online (by credit card) Customer dimension purchase activity helps in identifying the customer preference, Junk dimension for the payment indicator as credit card is used for this.Question 13:

Do VIP members bring more business to our company?

SelectVIP member class, threshold value, quantity sold, Net profitFromBusiness Process: Retail sales, Prescribed medicine Fact table: Retail sales transaction fact table, Prescribed medicine sales transaction fact table Dimensions: Customer – VIP member class Measure Used: Qty sold, net profitNote: Customer ID, VIP class type, and the total quantity of products purchase to calculate the net profit earn by one customer.The post Data warehouse and design appeared first on My Assignment Online.

  

CLIENT TESTIMONIALS

The Service Is Okay. I won’t Complain

The writers on the website are courteous and the customer service responds quickly. My paper was handled well; They promised...

Writers here are very professional and are native British

Thanks a lot for helping me out with my PhD Thesis, Writers here are very professional and are native British!!

Jack, The United Kingdom

Very Very Helpful, and On time.

Very Very Helpful, and On time.

Adelio M, Spain

I scored complete A’s in 3 out of four courses

I scored complete A’s in 3 out of four courses

Anonymous, Illinoi Chicago

CLICK HERE  To order your paper

About Essaysmiths Assignment writing service

We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.

How It Works

To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.

Are there Discounts?

All new clients are eligible for upto 20% off in their first Order. Our payment method is safe and secure.

 CLICK HERE to Order Your Assignment

 

Recently Posted Questions.

Order your Assignment today and save 15% with the discount code ESSAYHELP

X