Data warehouse and design

Contents

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 Sales
  • Prescribed Medicine Sales
  • Order Management
  • Inventory Management
  • Marketing Analysis

Task 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 sold Price $ 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 sold Price $ 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 quantity Order 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 capacity Qty sold Quantity in hand Qty required

Marketing 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 sold Price ($)
Discount Total amount ($) Cost of media used ($) Net profit ($)

Task 3:

Fact table features and techniques

Fact table name Fact granularity Fact table type Brief justification
Retail Sales The 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 table It 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 Sales The 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 table It 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 Management This accumulated snapshot fact table provides detail of order shipment from supplier to warehouse on negotiating favorable price. Accumulated snapshot fact table This 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 Management The granularity of the inventory management fact table is to track daily inventory available of health related and medication products by store. Periodic snapshot fact table A periodic inventory snapshot can be helpful to see the quantity of products available or shortage in store.
Marketing Analysis Grain 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 table By 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 name Brief justification Attribute hierarchies
Date In 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, Year
Store Chemist 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, email
Customer Customer 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. email
Sales Representative Sales rep is an entity who is selling general and healthy products to customers or patient. Sales Rep ID, Name, Contact Number, Email
Pharmacist Pharmacist 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, Email
Warehouse In chemist warehouse business case, warehouse is storing product prior to its distribution to stores. Warehouse ID
Warehouse Manager Name, Location, Address, Contact number, email
General/Health related Products A primary entity in this business case which contains information about General/Health related Products available in store for sale. Retail Product Name, Description, Brand, Category
Package name, Package Size. Unit price, Date of valid period, Manufacturing date
Prescribed medicine A 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 date
Deal An 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 description
Supplier Supplier 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, email
Promotion Stores 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 Date

Task 5

Design feature Brief description Brief justification
Payment Method – Junk Dimension Applied to: Payment method used for health related and prescribed medicine online or in store It is used to better understand the data model and reduce the number of dimensions in fact table.
Customer Demographics – Mini Dimension Attributes 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 Dimension

It 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 key Applied to: An attribute in Customer Dimension – VIP Member class To handle modifications in specific attributes in dimension table surrogate keys are required.
Role playing Dimension _ Date Dimension Applied to: Date Dimension A 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 Dimension Applied to: Media type group Dimension Various 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 table Applied to: Retail Sales transaction fact table Grain: one row per retail product sale transaction at one time A 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 table Applied to: Marketing analysis transaction fact table Grain: one row per product on promotion sale transaction at one time It 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 Dimensions Order 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 table This is a dimension key without any dimension table in star schemas.
Null Foreign keys Applied to: Retail Sales transaction fact table Marketing analysis transaction fact table Not 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?

Select Retail Product Brand, Retail Product category, Prescribed Medicine Brand, Prescribed Medicine Category, Total Sales Dollar amount ($), Qty Sold, Year
From Business 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 Sold
Note: 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?

Select Name of days, Week
From Business 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 day
Note: Select the quantity Sold of all products per day in a week and then compare the quantity sold of all products with weekdays and weekends

Question 3:

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

Select Quantity in hand, Quantity required, Month
From Business Process: Inventory Management Fact table: Inventory Management periodic snapshot Dimensions: Date, Health related product, Prescribed medicine Required Measure: Quantity in hand, Quantity required
Note: 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?

Select customer/patient, number of visits, month
From Business 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.