Data Warehouse Concept and Design Report: Chemist Warehouse Group

This report details the design of a data warehouse for Chemist Warehouse, a large Australian pharmacy retailer. It outlines the business case, which involves both retail sales and patient services. The report focuses on the retail sales data mart, employing a star schema with a central salesFact table and ten dimension tables (product, customer, supplier, store, location, date, time, paymentType, advertising, promotion). The report defines fact table granularity, detailing additive and non-additive facts like sales revenue, discounts, and quantities. It also provides an overview of the dimension tables and their hierarchies. Furthermore, the report includes business questions designed to analyze sales trends, product popularity, customer behavior, and promotion effectiveness. Finally, it references the sources used for this data warehouse design.

Data Warehouse Concept and Design
April 27
201
9
Student ID:
Student Name:

Report
Data Warehouse Bus Matrix
Data Marts
Business Processes
Serve
Patients
Retail Sales
Pay purchases by cash, cheque, bank
card, visa card and master card
x x
Pay purchases by American Express
Card
x x
Monitoring patient drug therapies x
Online Purchase x
Check balance of products x x
Observe hot selling products x
Notify central warehouse for orders of
products
x x
Receive delivered products x x
Update inventory database x x
Send weekly sales report to HQ x x
Store POS transaction x
Store receipt file for sales of products x
Send monthly sales report to HQ x x
Store VIP buyer card information of
customers
x x
Assign VIP classes to customers x x
Store product detail x x
Store promotion detail x
Analyse sales revenue x x
Maintain supply chains x x
Seek cost saving opportunities x
Negotiate agreements x x
Issue purchase requisitions x x
Issue purchase order x x
Track receipts x x
Authorize payments x x
Agreements for discount on goods x x
HQ distributes list of suppliers and
products to stores
x x

Generate business revenue x x
Analyse income for different products x x
Analyst customer behaviours x x
Analyse location performance x
Analyse business performance x x

Star Schema for Retail Sales Data Mart
( Dynamic News 2015)
(Kirpes D. n.d.)

The Retail Sales data mart is used to do all required calculations on sale of products in
Chemist Warehouse Groups. It also calculates the total sales revenue. There is one
fact table- salesFact and ten dimensions table- product, customer, supplier, store,
location, date, time, paymentType, advertising, promotion.
The customer dimension table contains the customer’s detail and the primary key is
customerID.
The supplier dimension table contains the supplier’s detail and the primary key is
supplierID.
The product dimension table contains the product’s detail and the primary key is
productID.
The store dimension table contains the store’s detail and the primary key is storeID.
The time dimension table contains the time’s detail and the primary key is timeID.
The date dimension table contains the date’s detail and the primary key is dateID.
The paymentType dimension table contains the detail of types of payments supported
in Chemist Warehouse Group and the primary key is typeCode.
The promotion dimension table contains the detail about promotions held in Chemist
Warehouse Group and the primary key is promotionID.
The location dimension table contains the detail about locations of customers, stores
etc. of Chemist Warehouse Group and the primary key is locationID.
The advertising dimension table contains the detail about advertisements, type of
advertisements etc.and the primary key is advertisementID.

Fact Granularity
Fact Table
Name
Fact Granularity Fact Table
Type
Brief Justification
salesFact lineItemPrice
Fact – ‘$100’
Commulative It holds the price of line
item. It is an additive
fact because it can be
summed up.
salesFact lineItemDiscount
Fact – ‘$2’
Cumulative It holds the discount on
line item. It is an
additive fact because it
can be summed up
salesFact lineItemQty
Fact – ‘2’
Cumulative It holds the available
quantity of line item. It
is an additive fact
because it can be
summed up
salesFact lineItemTotalAmount
Fact – ‘$210’
Cumulative It holds the total amount
of line item. It is an
additive fact because it
can be summed up
salesFact lineItemTax
Fact
– ‘$10’
Cumulative It holds the tax on line
item. It is an additive
fact because it can be
summed up
salesFact lineItemAvgAmt
Fact– ‘$100’
Cumulative It holds the average
amount of line item. It is
not an additive fact.
salesFact lineItemAvgDiscount
Fact –‘$2’
Cumulative It holds the average
discount on line item. It
is an additive fact.
salesFact requestShipDate -
‘#04/04/2019#’
Cumulative It holds the requested
shipping date of line
item by customer.
salesFact fillRate Fact – ‘4’ Cumulative It holds the fill rate of
the supplier.
salesFact purchaseCost Fact
–‘$90’
Cumulative It holds the cost of
purchase of line item. It
is an additive fact.
salesFact negotiableCost Fact
–‘$92’
Cumulative It holds the negotiable
cost of purchase of line
item with supplier.

salesFact salesRevenue Fact
–‘$20000’
Cumulative It holds the sales
revenue. It is an additive
fact.
Dimension Table’s Overview
Dimension Table Name Brief Justification Attribute Hierarchies
Product It is the dimension table of
the Retail Sales Data Mart
which contains the data
about products.
Customer It is the dimension table of
the Retail Sales Data Mart
which contains the data
about customers.
Store It is the dimension table of
the Retail Sales Data Mart
which contains the data
about stores.
Location It is the dimension table of
the Retail Sales Data Mart
which contains the data
about locations of
customers and stores etc.
Date It is the dimension table of
the Retail Sales Data Mart
which contains the data
about order dates, request
ship dates etc.
Time It is the dimension table of
the Retail Sales Data Mart
which contains the data
about timings of order
dates, request ship dates
etc.
Supplier It is the dimension table of
the Retail Sales Data Mart
which contains the data
about suppliers.
PaymentType It is the dimension table of
the Retail Sales Data Mart
which contains the data
about types of payments
e.g. online, physically by
cash, cheque, master card
etc.
debitCardNumber –
Debit Card ID and Debit
Card Name
Promotion It is the dimension table of
the Retail Sales Data Mart

which contains the data
about promotions.
Advertising It is the dimension table of
the Retail Sales Data Mart
which contains the data
about advertisements,
types of advertisement etc.
(Dan Kirpes n.d.)
(1keydata 2019)
Design features
Design Feature Brief Description Brief Justification
Confirmed Dimensions When a dimension table is
used with number of fact
tables, that is called
confirmed dimensions.
Dimension tables
paymentType, customer,
product etc. will be used
with salesFact tact table of
Retail Sales data mart and
serverPatient fact table of
Server Patients data mart.
Null Foreign Keys In fact table there may be
some null foreign keys.
PromotionID will be null
if the sale is without nay
promotion or event.
Junk Dimensions When there are number of
miscellaneous indicators
etc. then we make junk
dimensions for all of them
in spite of making separate
tables.
Date and Time are the junk
dimensions.

Business Questions
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 particular
product is most popular across nation, what is the total sale in past 3 years?
ProductID, UPC, brand, category fields of product dimension table and productId,
salesRevenue fields of SalesFact table are required to answer the above question.
Are there certain times of the year when more products are sold? Which day of the
week more products sold?
calenderDate, dayofWeek fields of Date dimension table and productId, salesRevenue
fields of SalesFact table are required to answer the above question.
What products are short of supply in this month? Has this always been the case in
whole year?
The totalQty field of the product table is required to answer the above question.
What areas of customers are frequently buyers at our stores in last 12 months?
calenderDate of Date dimension table, customerID and name of customer dimention
table and name in the location table are required to answer the above question.
What are top 3 stores have the highest sales across the country?
Storied, storeName fields of store dimension table and salesRevenue field of salesFact
tables are required to answer the above question.
How many patients are regular using our medications? Which medication is the most
selling product?
CustomerId field of customer table, UPC field of product table and customerId,
productId fields of salesFact tables are required to answer the above question.
Which age group of customers is most likely using our Vitamins across nation? Does
this vary across different location or times of the year?
UPC, category fields of product dimension table, customerName field of the
Customer dimention table are required to answer the above question. Yes, it may vary
across different location or times of the year.

Does the promotion activity provide the benefits to the business?
Yes, it provides benefits to the business because it increases the sale of products.
promotionId, promotionName of the promotion dimension table and salesRevenue
field of the salesFact tables are required to answer the above question
The promotion period in Christmas month has increased the sales comparing with the
same period in last year?
The startDate, endDate fields of the promotion dimension table, dateId and
salesRevenue fields of salesFact table are required to answer the above question
Any products have not been sold out during the promotion period?
The startDate, endDate fields of the promotion dimension table, productId field of
salesFact table are required to answer the above question
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?
The customerID, customerName fields of the customer dimension table, locationId,
name fields of the location dimension table and salesRevenue field of salesFact table
are required to answer the above question
Do customers prefer to go online purchases or buy product physically at local store?
The typeDetail field of the paymentType dimention table is required to answer the
above question.
Do VIP members bring more business to our company?
VIPClass field of the customer dimension table and salesRevelue field of the
salesFact table is required to answer the above question.

References
Dynamic News (2015). Designing Star Schema. [Online] Available from:
http://www.learndatamodeling.com/star.php#.UZIFLaKSA_Y
Kirpes D. (n.d.), Dimensional Model Data Warehouse: An Introduction (What).
[Online] Available from:
http://www.lexjansen.com/wuss/2003/DatabaseManagement/c-
dimensional_model_data_warehouse_introduction.pdf
TechTarget (2019). Star Schema in Database: Guide to Construction and
Composition. [Online] Available from:
http://searchbusinessintelligence.techtarget.in/tutorial/Star-schema-in-database-Guide-
to-construction-and-composition
1keydata (n.d.). Fact Table Granularity. [Online] Available from:
http://www.1keydata.com/datawarehousing/fact-table-granularity.html