Data Warehouse Design Report: Retail Sales Data Mart - CSE5DWD, 2019

This report presents a comprehensive data warehouse design for a retail sales data mart, adhering to the principles of star schema modeling. The design encompasses various aspects, including the identification of business processes, the creation of a bus matrix, and the establishment of fact and dimension tables. The fact table, named salesFactTable, is central to the design, storing cumulative facts such as lineItemDiscountFact, lineItemQtyFact, lineItemTotalAmountFact, and others. Dimension tables such as customerDim, productDim, storeDim, and dateDim provide contextual information for analysis. The report also details design features like role-play dimensions and mini-dimensions, along with example business questions. These questions are designed to leverage the data warehouse's structure for insights into product popularity, sales trends, customer behavior, and promotion effectiveness, demonstrating how the design supports informed decision-making. The report concludes with references to the sources used in the design.

Report
Data Warehouse Bus Matrix
Data Marts
Business Processes
Patients
Service
Retail Sales
Monitor drug therapies of patients x
Pay amount physically by American
Express Card
x x
Pay amount physically by cash, cheque,
bank card, visa card and master card
x x
Purchase online by credit card x
Check product quantity for inventory
management
x x
Monitor hot selling products in specific
periods
x
Inform central warehouse regarding
product orders
x x
Accept delivered products x x
Send weekly, monthly sales reports to
Head Quarter
x x
Accumulate all POS transactions x
Accumulate receipt files of products x
Give and update VIP buyer card
information of customers
x x
Assign and update VIP classes of
customers
x x
Accumulate detail of products x x
Accumulate detail of promotions x
Analyse sales revenue on month, yearly,
event basis etc.
x x
Manage chains of supply by suppliers x x
Sign agreements and negotiate the cost
of products with suppliers
x x
Issue purchase requisitions x x
Issue purchase order x x
Track receipts of sales x x
Approve payments x x
Produce business revenue x x

Analyse profit from different products x x
Analyst customer behaviours x x
Analyse performance of products on the
basis of locations
x
Analyse business performance on
monthly, yearly basis
x x
Head Quarter shares out the list of
suppliers and products to all stores
x x

Star Schema for Retail Sales Data Mart
(Datawarehouse4u.info 2019)
(Geeksforgeeks.org n.d.)

The Retail Sales Data Mart is used to store sales records and to analyze sales revenue
etc. the complete sales information about Chemist Warehouse Groups is being stored
in Retail Sales Data Mart. These dimension tables are being used in the Retail Sales
Data Mart-
The customerDim table stores the information related to customers.
The supplierDim table stores the information related to suppliers.
The productDim table stores the information related to products.
The storeDim table stores the information related to stores.
The dateDim table stores the information related to dates like orderDate,
requestShipDate.
The paymentTypeDim table stores the information related to types of payments. The
type of payments like online, physical etc. in online it will be by credit card, in
physical it will be by cash, by debit card etc.
The promotionDim table stores the information related to promotions.
The advertisingDim table stores the information related to advertisements.
The locationDim table stores the information related to locations of customers etc.

Fact Granularity
Fact Table
Name
Fact Granularity Fact Table
Type
Brief Justification
salesFactTable lineItemPriceFact –
‘$200’
Commulative This fact contains the
item’s price. It is the
additive fact because
its addition may be
required for analysis
in future.
salesFactTable lineItemDiscountFact –
‘$4’
Cumulative This fact contains the
item’s discount. It is
the additive fact
because its addition
may be required for
analysis in future.
salesFactTable lineItemQtyFact – ‘4’ Cumulative This fact contains the
item’s quantity. It is
the additive fact
because its addition
may be required for
analysis in future.
salesFactTable lineItemTotalAmountFact
– ‘$310’
Cumulative This fact contains the
item’s total amount.
It is the additive fact
because its addition
may be required for
analysis in future.
salesFactTable lineItemTaxFact
– ‘$20’
Cumulative This fact contains the
tax imposed on
items.
salesFactTable lineItemAvgAmtFact–
‘$200’
Cumulative This fact contains the
item’s average price.
salesFactTable lineItemAvgDiscountFact
–‘$4’
Cumulative This fact contains the
item’s average
discount.
salesFactTable requestShipDateFact -
‘#01/01/2019#’
Cumulative This fact contains the
shipping date
requested by the
customer.
salesFactTable fillRateFact – ‘6’ Cumulative This fact contains the
fill rate of supplier.
salesFactTable purchaseCostFact
–‘$180’
Cumulative This fact contains the
cost of purchase of
items. It is the
additive fact because

its addition may be
required for analysis
in future.
salesFactTable negotiableCostFact
–‘$102’
Cumulative This fact contains the
cost after negotiation
of items.
salesFactTable salesRevenueFact
–‘$50000’
Cumulative This fact contains the
total sales revenue. It
is an additive fact.
Dimension Table’s Overview
Dimension Table Name Brief Justification Attribute Hierarchies
ProductDim It is the Product dimension
table of Retail Sales Data
Mart that encloses the
products detail.
CustomerDim It is the Customer
dimension table of Retail
Sales Data Mart that
encloses the customer
detail.
StoreDim It is the Store dimension
table of Retail Sales Data
Mart that encloses the
store detail.
LocationDim It is the Location
dimension table of Retail
Sales Data Mart that
encloses the locations of
customers, store’s detail
etc.
DateDim It is the Date dimension
table of Retail Sales Data
Mart that encloses the
dates of orders, requested
date detail etc.
SupplierDim It is the Supplier
dimension table of Retail
Sales Data Mart that
encloses the supplier’s
detail.
PaymentTypeDim It is the PaymentType
dimension table of Retail
Sales Data Mart that
encloses the type of
payments detail like
online, physical etc. in

online it will be by credit
card, in physical it will be
by cash, by debit card etc.
PromotionDim It is the Promotion
dimension table of Retail
Sales Data Mart that
encloses the promotion’s
detail.
AdvertisingDim It is the Advertising
dimension table of Retail
Sales Data Mart that
encloses the advertisement
detail.
Design features
Design Feature Brief Description Brief Justification
Role Play Dimensions When a dimension has
multiple relationships with
a fact table.
Date dimension has
multiple relationships with
saleFact table. One for
orderDate and one for
requestShip date.
Null Foreign Keys There are some facts in
salesFact table which are
foreign keys and may be
null.
PromotionIDFact may be
null because every sale
does not contain
promotions.
Mini Dimensions Mini dimensions are used
to decrease the large size
of a very big dimension.
Promotion and advertising
are mini dimensions
because one combined
dimension will be very
large to handle.
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?
ProductIDField, UPCField, brandField, categoryField of productDim table and
productIdFact, salesRevenueFact of SalesFact table are needed to reply to this
question.
Are there certain times of the year when more products are sold? Which day of the
week more products sold?
calenderDateField, dayofWeekField of DateDim table and productIdFact,
salesRevenueFact fields of SalesFact table are needed to reply to this question.
What products are short of supply in this month? Has this always been the case in
whole year?
The totalQtyfield of the product table is needed to reply to this question.
What areas of customers are frequently buyers at our stores in last 12 months?
calenderDateField of DateDim table, customerIDField and customerNameField of
customerDim table and nameField in the locationDim table are needed to reply to this
question.
What are top 3 stores have the highest sales across the country?
StoreIDField, storeNamefield of storeDim table and salesRevenueFact of salesFact
tables are needed to reply to this question.
.
How many patients are regular using our medications? Which medication is the most
selling product?
CustomerIdfield of customer table, UPCfield of productDim table and
customerIdFact, productIdfact of salesFact tables are needed to reply to this 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?
UPCField, categoryfield of productDim table, customerNamefield of the
CustomerDim table are needed to reply to this question.
Does the promotion activity provide the benefits to the business?

PromotionIdField, promotionNameField of the promotionDim table and
salesRevenuefact of the salesFact tables are needed to reply to this question.
The promotion period in Christmas month has increased the sales comparing with the
same period in last year?
The startDateField, endDateField of the promotionDim table, dateIDFact and
salesRevenueFact of salesFact table are needed to reply to this question.
Any products have not been sold out during the promotion period?
The startDateField, endDateField of the promotionDim table, productIdFact of
salesFact table are needed to reply to this 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 customerIDField, customerNameField of the customerDim table,
locationIDField, name fields of the locationDim table and salesRevenueFact of
salesFact table are needed to reply to this question.
Do customers prefer to go online purchases or buy product physically at local store?
The typeDetailField of the paymentTypeDim table is needed to reply to this question.
Do VIP members bring more business to our company?
VIPClassField of the customerDim table and salesRevelueFact of the salesFact table
is needed to reply to this question.

References
Datawarehouse4u.info (2019). Star Schema. Retrieved from:
https://www.datawarehouse4u.info/Data-warehouse-schema-architecture-star-
schema.html
Geeksforgeeks.org (n.d.), Star Schema in Data Warehouse modeling. Retrieved from:
https://www.geeksforgeeks.org/star-schema-in-data-warehouse-modeling/